Mysql optimization points section, mysql optimization Section

Mysql optimization points section, mysql optimization Section
1. Rationality of Database Table Design

1) three paradigms

 

1. paradigm: atomicity, attributes cannot be divided;

 

2. paradigm: No part of dependency,

Example: (student ID, course name) → (name, age, score, credits), partially dependent (student ID) → (name, age)

Split; (student ID, name, age), (Course name, credits), (student ID, course name, score ),

 

Three paradigms: No transmission dependency,

Example: (student ID) → (name, age, gender, Department, Department address, Department phone number)

Transfer dependency:

(Student ID) → (department) → (Department location, Department phone number)

Then split...

 

2) Inverse paradigm:

 

Photo table (photo id, name, number of clicks, album id, upload time)

Album table (album id, name, time)

If the number of album clicks is required frequently, you need to add the "Number of clicks" redundant field in the album table.

 

Rules for adding redundant fields:

One-to-multiple scenarios

Redundant fields should be on the "1" side whenever possible.

If you put the redundant field "album name" in the photo table, it is convenient to ask the album name. However, this results in a great waste of space and greatly increases the modification cost.

 

3) anti-foreign key

There is a foreign key relationship, but no foreign key constraint is added.

Disadvantages of Foreign keys: omitted

 

2. SQL statement Optimization

1) five types of SQL statements

Ddl

Dml

Select

Dtl transaction control statement commit \ rollback \ savepoint

Dcl data control statement grant \ revork

The core of SQL optimization is select. You know why.

 

2) show status Command

View the current status of the database. Several useful statuses include:

A) show status like 'com % '<=> show session status like 'com %' // current console status

B) show global status 'com % '; // The status of the database from startup to present

C) show status like 'connections': displays the number of times a database is connected.

D) show status like 'uptime' server working time (seconds)

E) show status like 'slow _ queries 'Slow Query Count (10 seconds by default)

 

3) Here we optimize slow queries.

 

A) show variables like 'long _ query_time'

The default value is 10 seconds. The value must be a little higher. We set it to 1 second.

Set long_query_time = 1

Create a sea scale to test performance.

Show status like 'slow _ queres'

It is found that the current slow query is 0 at this time.

 

B) You can customize functions and stored procedures to create a sea scale.

The custom function generates a random string:

Delimiter $

Drop function if exists rand_string;

Create function rand_string (n INT)

Returns varchar (255)

Begin

Declare chars_str varchar (100) default

'Abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxy ';

Declare return_str varchar (255) default '';

Declare I int default 0;

While I <n do

Set return_str = concat (return_str, substring (chars_str, floor (1 + rand () * 52), 1 ));

Set I = I + 1;

End while;

Return return_str;

End $

Delimiter; $

 

Stored Procedure:

Drop table if exists emp;

Create table emp (

Id int primary key,

Name varchar (255 ),

Descp varchar (255 ),

Gene varchar (16)

);

Delimiter $

Drop PROCEDURE if exists proc_insertemp;

Create procedure proc_insertemp (in start int (10), in max_num int (10 ))

Begin

Declare I int default 0;

Set autocommit = 0;

Repeat

Set I = I + 1;

Insert into emp values (start + I), rand_string (6), 'salesman', 'man ');

Until I = max_num

End repeat;

Commit;

End $

Delimiter; $

 

Use stored procedures

Call proc_insertemp (10000,200 00 );

 

C) mysql supports recording slow query statements into logs for analysis by programmers.

It is disabled by default.

Go to the mysql installation directory and start -- slow-query-log.

 

D) Index

Show indexes from tb

Primary key Index alter table tb add primary key (keyname );

The unique index unique indicates that the column is unique and is also an index.

Normal index

Full-text index fullindex (only supported by mylsam)

Composite indexes (with multiple columns together and associated indexes) are ordered from left to right.

 

Chinese Index

Sphsf-+ Chinese Word Segmentation coreseek

 

4) explain command

Explain select * from tb where id = 2000

Select_type: simple

Table: tb

Type: all // search type

Possible_keys: primary // possible Indexes

Key: primary // actually used Index

Key_len:

Ref:

Rows: 1 // The number of records retrieved. Because of the index, it is 1.

Extra: using where // using temporary using filesort, etc.

 

The logic for querying a full table is unreasonable in a real project, and the paging logic is inevitable. The page must have an index.

 

5) index addition scenario

A) frequently used fields as query conditions should be indexed

B) fields with poor uniqueness are not suitable for independent index creation, even if they are frequently used as query conditions.

Select * from tb where sex = 'n' Male ';

C) frequently updated fields are not suitable for adding indexes.

 

6) No index is used.

A) like % does not use the index before and after the edge is placed in the middle and back;

B) For composite indexes, indexes are generally used as long as the leftmost column is used for the query conditions. If only the column on the right is used, it is not used.

C) if mysql estimates that full table scan is faster than indexing, no indexing is used.

 

7) Notes for using Indexes

A) how to check whether the index is valid?

Show status like 'handler _ read %'

B) The higher the handler_read_key value, the more times the index is queried.

C) The higher the handler_read_key value, the lower the query efficiency.

 

8) common skills

Insert data in large batches

A) myisam first closes keys, and then starts the import;

Alter table table_name disable keys;

Loading data;

Alter table table_name enable keys;

B) Sort innodb data, disable uniqueness verification (not to insert one validation entry each time), and disable automatic submission.

Set unique_check = 0;

Set autocommit = 0;

 

Group by is sorted by default. order by null can be used to disable sorting;

The subquery generates a temporary table, which can be replaced by join;

In applications with high precision requirements, we recommend that you use a fixed number of points to store the decimal value, instead of a floating point to ensure the accuracy of the results. For example, 100000 million, the insert float (10000000.31) is.

The date type should be selected based on actual needs to meet the minimum storage type of the application. If the timestamp is used, you can easily search by range. For example, check the records of the previous three days. Note that the int-type Timestamp can only be expressed as of January 1, 2038.

Image Storage Uses path storage. Even dedicated image servers (image beds)

 

9) differences between MylSAM and Innodb

• MyISAM is non-transactional, while InnoDB is transactional.
• The granularity of MyISAM locks is table-level, while InnoDB supports row-level locks.
• MyISAM supports full-text indexes, while InnoDB does not.
• MyISAM is relatively simple, so it is more efficient than InnoDB. For small applications, you can consider using MyISAM.
• MyISAM tables are saved as files. Using MyISAM for cross-platform data transfer saves a lot of trouble.
• InnoDB tables are safer than MyISAM tables. when data is not lost, you can switch non-transaction tables to the transaction tables (alter table tablename type = innodb ).

The former has a storage cache and requires manual recovery of expired data. MyISAM creates a table, corresponds to three files, if Innodb only has one file *. frm

For the MyISAM database, it needs to be cleaned regularly.

Optimize table name.

Show engines; Field Support: Default indicates the Default storage engine. The default value is Innodb.

 

3. database parameter configuration

Set the cache to a greater value:

Innodb_additional_mem_pool_size = 64 M

Innodb_buff_pool_size = 1G

Key_buff_size

 

4. hardware configuration and Operating System

Memory exceeds 4 GB, with 64-bit System

 

5. Table sharding and read/write splitting

1) Table segmentation, horizontal segmentation (database/table sharding), and vertical segmentation (small table granularity)

2) read/write Splitting: relieves query pressure

A) Determine the SQL statement of the request and the dml statement, which will be processed by the master. The slave will regularly synchronize the master data.

B) if the read SQL statement is used, lvs can read the SQL statement from slave.

 

Best mysql optimization skills

1. select the most suitable field attribute

MySQL can support access to large data volumes, but generally, the smaller the table in the database, the faster the query will be executed on it. Therefore, when creating a table, we can set the field width in the table as small as possible to achieve better performance. For example, if you set it to CHAR (255) when defining the zip code field, it is obvious that unnecessary space is added to the database, and even the VARCHAR type is redundant, because CHAR (6) can well complete the task. Similarly, if possible, we should use MEDIUMINT instead of BIGIN to define integer fields.

Another way to improve efficiency is to set the field to not null whenever possible, so that the database does NOT need to compare NULL values during future queries.

Some text fields, such as "Province" or "gender", can be defined as ENUM. In MySQL, The ENUM type is processed as the numeric data, and the numeric data is processed much faster than the text type. In this way, we can improve the database performance.

2. Use JOIN instead of Sub-Queries)

MySQL supports SQL subqueries from 4.1. This technique can use the SELECT statement to create a single column query result, and then use this result as a filter condition in another query. For example, if you want to delete a customer who has no orders in the basic customer information table, you can use the subquery to retrieve the customer IDs of all orders from the sales information table, then pass the result to the primary query, as shown below:

Delete from customerinfo
WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo)

Subqueries can be used to complete SQL operations that require multiple logical steps at a time. At the same time, transactions or tables can be prevented from being locked and can be easily written. However, in some cases, subqueries can be replaced by more efficient JOIN. For example, if we want to retrieve all users without order records, we can use the following query:

SELECT * FROM customerinfo
WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo)

If you use JOIN... to complete this query, the speed will be much faster. Especially when the salesinfo table has an index on CustomerID, the performance will be better. The query is as follows:

SELECT * FROM customerinfo
Left join salesinfoON customerinfo. CustomerID = salesinfo.
CustomerID
WHERE salesinfo. CustomerID IS NULL

JOIN... it is more efficient because MySQL does not need to create a temporary table in the memory to perform the query in two steps.

3. Use UNION instead of creating a temporary table manually

MySQL 4.0 and later versions support UNION queries. It can merge two or more SELECT queries in a temporary table. When the query Session on the client ends, the temporary table is automatically deleted to ensure the database is neat and efficient. When using UNION to create a query, we only need to use UNION as the keyword to connect multiple SELECT statements. Note that the number of fields in all SELECT statements must be the same. The following example demonstrates a query using UNION.

SELECT Name, Phone FROM client
UNION
SELECT Name, BirthDate FROM author ...... remaining full text>

How to optimize mysql

Don't optimize it for the sake of optimization, which means optimizing it for the problem. It depends on the performance of your database, such as small bufferpool, large io, or sort overflow. Do not optimize several parameters at a time. The general principles of different databases are similar.