Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to optimize sql in MySql, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

1. Brief introduction of explain return column 1. Common keywords of type

System > const > eq_ref > ref > range > index > all.

System: the table has only one row and is basically useless.

Const: table with up to one row of data, which is triggered more frequently when querying the primary key.

Eq_ref: for each combination of rows from the previous table, read a row from that table. This is probably the best join type, except for the const type

Ref: for each row combination from the previous table, all rows with matching index values will be read from this table

Range: only retrieve a given range of rows, using an index to select rows. When using =, >, > =, slow log queries

Slow log is a kind of logging provided by MySQL, which is used to record SQL statements whose MySQL response time exceeds the threshold (long_query_time, default is 10 seconds).

Slow logging is turned off by default, turned on during development tuning, and closed at final deployment.

1. Slow query log

(1) check whether slow query log is enabled:

Show variables like'% slow_query_log%'

(2) temporary opening:

Set global slow_query_log = 1

(3) restart MySQL:

Service mysql restart

(4) permanent opening:

Additional configuration in / etc/my.cnf:

Put it under [mysqld]:

Slow_query_log=1slow_query_log_file=/var/lib/mysql/localhost-slow.log2, threshold

(1) View the default threshold:

Show variables like'% long_query_time%'

(2) temporarily modify the default threshold:

Set global long_query_time = 5

(3) permanently modify the default threshold:

Additional configuration in / etc/my.cnf:

Put it under [mysqld]:

Long_query_time = 5

(4) sleep in MySQL:

Select sleep (5)

(5) check the sql for which the execution time exceeds the threshold:

Show global status like'% slow_queries%'; VIII, slow query log-- > mysqldumpslow tool 1, mysqldumpslow tool

The sql of the slow query is recorded in the log, and you can view the specific slow sql through the log.

Cat / var/lib/mysql/localhost-slow.log

Check the slow sql through the mysqldumpslow tool, and you can quickly find out the slow sql that needs to be located through some filtering conditions.

Mysqldumpslow-help

A brief description of the parameters:

S: sort by

R: reverse order

L: lock time

G: regular matching pattern

2. Query slow sql under different conditions

(1) return the 3 SQL with the most records

Mysqldumpslow-s r-t 3 / var/lib/mysql/localhost-slow.log

(2) obtain the 3 SQL with the most visits

Mysqldumpslow-s c-t 3 / var/lib/mysql/localhost-slow.log

(3) sorted by time, the first 10 SQL containing left join query statements

Mysqldumpslow-st- t 10-g "left join" / var/lib/mysql/localhost- data. Log IX, Analysis of massive data 1, show profiles

Turn on this feature: set profiling = on

Show profiles records the time spent on all SQL query statements after all profileing calls.

The disadvantage is that it is not precise enough to determine which part of the execution takes time, such as CPU, IO.

2. Accurate analysis and sql diagnosis

The query_id queried in the previous step by show profile all for query.

3. Global query log

Show variables like'% general_log%'

Open the global log:

Set global general_log = 1

Set global log_output = table

10. Detailed explanation of locking mechanism 1. Operation classification

Read and write: for the same data, multiple read operations can be carried out at the same time without interference with each other.

Write lock: if the current write operation is not finished, no other read and write operations can be performed.

2. Operation range

Table lock: lock a table as a whole at once.

For example, the MyISAM storage engine uses table locks with low overhead, fast locking and no deadlock, but the scope of locks is large, prone to conflicts and low concurrency.

Row lock: lock one piece of data at a time.

For example, the InnoDB storage engine uses row locks, which are expensive, slow to add locks, and prone to deadlocks; the scope of locks is small, lock conflicts are not easy to occur, and the concurrency is high (very low probability of high concurrency problems: dirty reads, phantom reads, non-repeatable reads)

Lock table Table 1 read/write, Table 2 read/write,...

View locked tables:

Show open tables

3. Add a read lock, code instance session 0:lock table student read;select * from student;-- check. You can delete from student where id = 1 political from user;-add, delete and change, but not select * read-check, delete from user where id = 1 politics-add, delete, change, no

If a session adds a read lock to table A, the session can read from table A but not write to it. That is, if a read lock is added to table A, the current session can only read table A, but no other tables can be operated.

Session 1:select * from student;-- check, you can delete from student where id = 1 switch 1:select-add, delete, modify, will "wait" for session 0 to release the lock, session 1:select * delete-- check, you can check, you can add, delete, modify, etc.

Session 0 locks the A table, and the operation of other sessions ① can read and write to other tables. ② to A table: read can, write need to wait for lock release.

4. Write lock session 0:lock table student write

The current session can add, delete, modify and query tables with write locks, but cannot manipulate other tables.

Other sessions:

For the tables with write locks in session 0, the premise that you can add, delete, modify and check is to wait for session 0 to release the write lock.

5. Lock mode of MyISAM table-level lock

Before executing the query statement, MyISAM automatically adds read locks to all tables involved, and automatically adds write locks to the tables involved before adding, deleting and modifying.

So when you manipulate the MyISAM table, the following happens:

(1) the read operation on the MyISAM table (adding a read lock) will not block the read requests of other sessions (processes) to the same table. But it blocks writes to the same table. Only when the read lock is released will the write operations of other processes be performed.

(2) the write operation to the MyISAM table (adding a write lock) will block the read and write operations of other sessions (processes) to the same table, and the read and write operations of other processes will be performed only when the write lock is released.

6. MyISAM analysis table lock

See which tables are locked:

The show open tables;1 representative is locked.

Analyze the severity of the table lock:

Show status like 'table%'

Table_locks_immediate: number of locks that may be acquired

Table_locks_waited: the number of table locks to wait (the higher the value, the greater the lock competition)

General recommendation: Table_locks_immediate/Table_locks_waited > 5000, InnoDB engine is recommended, otherwise MyISAM engine is recommended.

7. InnoDB analysis table lock

To study row locks, turn off automatic commit temporarily, set autocommit = 0

Show status like'% innodb_row_lock%'

Innodb_row_lock_current_waits: number of locks currently waiting

Innodb_row_lock_time: total waiting time. The total waiting time since the system was started

Innodb_row_lock_time_avg: average waiting time. The total waiting time since the system was started

Innodb_row_lock_time_max: maximum waiting time. The total waiting time since the system was started

Innodb_row_lock_waits: the number of waits. The total waiting time since the system was started

8. An example of adding line lock code

(1) query student

Select id,name,age from student

(2) Update student

Update student set age = 18 where id = 1

(3) add row lock

Add row locks to the query through select id,name,age from student for update;.

The modification is still successful because MySQL is auto-committed by default, so you need to turn off automatic commit temporarily

Set autocommit = 0

9. Matters needing attention for row locks

(1) if there is no index, the row lock is automatically converted to a table lock.

(2) Row locks can only be unlocked by transactions.

(3) InnoDB adopts row locks by default

Advantages: strong concurrency, high performance and high efficiency

Disadvantages: greater performance loss than watch lock

Use InnoDb for high concurrency, otherwise use MyISAM.

Thank you for reading this article carefully. I hope the article "how to optimize sql in MySql" shared by the editor will be helpful to everyone. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.