Beckham _ mysql optimization learning _ MySQL

Mysql optimization: 1. Database Design Optimization 2. SQL statement optimization 3. table partitioning 4. read/write splitting Technology 1. Database Design Optimization 1. table design must comply with the three paradigms. of course, sometimes we also need an appropriate inverse paradigm 2. what is a three-paradigm-one paradigm: atomicity, which cannot be separated Mysql optimization Brief:
1. Database Design Optimization
2. SQL statement optimization
3. table segmentation
4. read/write splitting technology

I. Database Design Optimization

1. table design must comply with the three paradigms. of course, appropriate inverse paradigms are also required.

2. what is the three paradigm?

1. Paradigm: atomicity, which cannot be separated

2. Paradigm: on the basis of satisfying the 1 paradigm, we will consider whether or not the 2 paradigm is satisfied. As long as the table record satisfies the uniqueness, it is also said that the same table cannot have identical records. Generally, you can design a primary key in the table.

Three Paradigms: based on the fulfillment of the two paradigms, we consider whether or not the three paradigms are satisfied. As long as the table does not have redundancy.

II. SQL statement optimization

1. general SQL optimization steps

A. run the show status command to check the execution efficiency of various SQL statements.

B. locate SQL statements with low execution efficiency

C. analyze the execution of inefficient SQL statements through explain/desc

D. identify the problem and take corresponding optimization measures

2. showstatus command

This command can display the current status of the mysql database. it is mainly concerned with commands starting with 'com '.

Showstatus like 'com % ''' show session status like 'com % '// display the current console status

Showglobal status like 'com % '// displays the status of the database from startup to present

3. showvariables command

This command can view the current mysql variable settings, mainly concerned with the slow query time

4. how to find slow query SQL statements in mysql

(Note: mysql database supports recording slow query statements into logs for analysis by programmers. by default, mysql does not enable slow query logs)

Step: a. Start mysql slow query

A1. when starting the mysql service, specify-slow-query-log

A2. after logging on to mysql using the client, set the variable

B. View slow query time

The default value is 10 seconds.

C. modify the slow query time

Set to 1 second

(This can only take effect in the current environment. if you want to take effect every time, modify the mysql configuration file)

D. View slow query logs

E. optimize the SQL statements for slow queries. The cheapest way is to add an index.

F. after the index is added

5. index impact

A. increase disk space

B. inconvenience caused by addition, deletion, and modification

6. columns suitable for indexing

A. index should be created frequently as a query condition field

B. A field with poor uniqueness (that is, the value of this field does not change much) is not suitable for independent index creation, even if it is frequently used as a query condition.

C. It is not suitable for creating indexes for frequently updated fields.

D. fields that do not appear in the where clause should not be indexed.

7. Use of indexes

Test table:

Create Table: create table 'T2 '(

'Id' int (11) not null default '0 ',

'Name' char (5) default null,

'Age' tinyint (4) default null,

Primary key ('id ')

) ENGINE = MyISAM DEFAULTCHARSET = utf8;

Insert into t2 (name, age) values ('A', 2), ('A', 3), ('B', 4), ('C ', 3 );

The most important condition for an index to be used for a query is that the index must be used in the query condition.

Indexes may be used in the following situations:

A. For the created multi-column index, the index is generally used as long as the leftmost column is used for the query condition.

B. For queries using like, if '% aaa' is used, the index 'AAA %' is not used.

The following tables do not use indexes:

A. If there is or in the condition, it will not be used even if there is a condition with an index.

B. If multiple-column indexes are not the first part, they are not used.
C. The like query starts with %.
D. If the column type is a string, use quotation marks to reference the data in the condition. Otherwise, no index is used.
E. If mysql estimates that full table scan is faster than indexing, no index is used.

8. verify index usage

Showstatus like 'handler _ read % ';

Note:
Handler_read_key: the higher the value, the better. the higher the value indicates the number of times the index is queried.

Handler_read_rnd_next: a higher value indicates inefficient query.

9. common SQL optimization

A. Insert data in large batches:

For MyIsam:

1. alter table table_name disable keys;

2. loading data;

3. alter table table_name enable keys;

For Innodb:

1. sort the data to be imported by primary key

2. set unique_checks = 0. disable uniqueness verification.

3. set autocommit = 0. disable automatic submission.

(Note: the difference between myisam and innodb is:

A. myisam does not support external connections, and innodb does.

B. myisam does not support transactions, and innodb does)

B. optimize group

By default, mysql sorts the column names after group. If the query contains group by but you want to avoid consumption of sorting results, you can use order by null to disable sorting.

III. table segmentation

When the data in a table is large, other optimization methods have been taken into account. When it does not play a major role, we need to consider table sharding. Split a large table into multiple small tables.

Table sharding method:

A. vertical table sharding

At this time, there are many columns in the table. at this time, you can use the primary key to divide the columns in the table into multiple tables, and then associate the columns based on the primary key. (after splitting, the columns in each table are different)

Before table sharding: Personal information table

Id	Name	Age	Email	Intro
1	A	11	11@qq.com	Xxxx
2	B	22	22@qq.com	Yyyy

After table sharding: Personal information table

Id	Name	Age	Email
1	A	11	11@qq.com
2	B	22	22@qq.com

Personal Profile

Id	Intro
1	Xxxx
2	Yyyy

B. horizontal table sharding

You can perform table sharding by modulo. Therefore, you need to determine the number of small tables to be divided into, that is, the value of the modulo. In addition, the columns in each table are consistent after the split.

Before table sharding: Personal information table

Id	Name	Age	Email	Intro
1	A	11	11@qq.com	Xxxx
2	B	22	22@qq.com	Yyyy

Determine that the modulo value is 2. Therefore, we can divide this table into two small tables.

1. determine the id value. id/2 =?

After table sharding: Personal Information Table 0

Id	Name	Age	Email	Intro
1	A	11	11@qq.com	Xxxx

Personal information table 1

Id	Name	Age	Email	Intro
2	B	22	22@qq.com	Yyyy

IV. read/write splitting

Generally, a mysql server carries all database operations. However, when the traffic is high, the mysql server is prone to bottlenecks. To reduce the pressure on the mysql server (mysql itself supports master-slave replication)

You can perform read/write splitting.

1. before read/write splitting

2. read/write splitting