Correct use of indexes (SQL optimization), limit paging optimization, execution plan, slow log query

View table-related commands

- 查看表结构
   desc 表名
- 查看生成表的SQL
   show create table 表名
- 查看索引
   show index from  表名

Using indexes and not using indexes

由于索引是专门用于加速搜索而生，所以加上索引之后，查询效率会快到飞起来。

# 有索引

Mysql>Select* fromTb1whereName ='Zhangqiye';+-----+-------------+---------------------+----------------------------------+---------------------+| Nid | name | email | Radom | CTime |+-----+-------------+---------------------+----------------------------------+---------------------+|889| Zhangqiye | [Email protected] | 5312269e76a16a90b8a8301d5314204b | .- ,-Geneva  the: -: *|+-----+-------------+---------------------+----------------------------------+---------------------+1Rowinch Set(0.00Sec

# no index mysql>Select* fromTb1whereemail ='[email protected]';+-----+-------------+---------------------+----------------------------------+---------------------+| Nid | name | email | Radom | CTime |+-----+-------------+---------------------+----------------------------------+---------------------+|889| Zhangqiye | [Email protected] | 5312269e76a16a90b8a8301d5314204b | .- ,-Geneva  the: -: *|+-----+-------------+---------------------+----------------------------------+---------------------+1Rowinch Set(1.23Sec

Correct use of indexes

When you add an index to a database table, it does make the query take off, but the premise must be the correct query using the index, and if used in an incorrect way, even indexing will not work.

Even if the index is indexed, the index does not take effect:

-Like '%xx '
SELECT * from TB1 where name is like '%CN ';
-Using functions
SELECT * from TB1 where reverse (name) = ' Zhangqiye ';
-OR
SELECT * from tb1 where nid = 1 or email = ' [email protected] ';
Special: When the OR condition has an unindexed columns failure, the following index
SELECT * from tb1 where nid = 1 or name = ' seven ';
SELECT * from tb1 where nid = 1 or email = ' [email protected] ' and name = ' Zhangqiye '
-Inconsistent type
If the column is a string type, the incoming condition must be enclosed in quotation marks, otherwise ...
SELECT * from TB1 where name = 999;
- !=
SELECT * FROM TB1 where name! = ' Zhangqiye '
Special: If it is a primary key, the index will still go
SELECT * from TB1 where nid! = 123
->
SELECT * from TB1 where name > ' Zhangqiye '
Special: If the primary key or index is an integer type, then the index is still gone
SELECT * from tb1 where nid > 123
SELECT * from tb1 where num > 123
-ORDER BY
Select email from tb1 order BY name Desc;
When sorting by index, the selected mapping is not indexed if it is not.
Special: If the primary key is sorted, then the index is still gone:
SELECT * from Tb1 ORDER by nid desc;

-Combined index leftmost prefix
If the combined index is: (Name,email)
Name and email--Using the index
Name--Using the index
Email-Don't use index

Other precautions

- 避免使用select *
- count(1)或count(列) 代替 count(*)
- 创建表时尽量时 char 代替 varchar
- 表的字段顺序固定长度的字段优先
- 组合索引代替多个单列索引（经常使用多个条件查询时）
- 尽量使用短索引
- 使用连接（JOIN）来代替子查询(Sub-Queries)
- 连表时注意条件类型需一致
- 索引散列值（重复少）不适合建索引，例：性别不适合

Limit paging

Limit paging is a matter of concern, whether or not it has an index

MySQL Big data volume uses the limit paging, as the page number increases, the query efficiency is lower.

Direct with limit start, count paging statement

SELECT * from product limit start, count
When the start page is small, the query has no performance problems, we look at 10, 100, 1000, 10000 to start the paging execution time (20 per page), as follows:

select * from product limit 10, 20   0.016秒
select * from product limit 100, 20   0.016秒
select * from product limit 1000, 20   0.047秒
select * from product limit 10000, 20   0.094秒

We have seen that as the start record increases, the time also increases, which means that the paging statement limit is very much related to the starting page number, then we change the start record to 40w (that is, the record is about the general) select * from Product limit 400000, 20 3.229 seconds

And look at the time we took the last page to record.
SELECT * FROM Product limit 866613, 20 37.44 seconds

No wonder the search engine crawled our page often reported timeouts, such as the page of the largest page pages obviously this time
The room is unbearable.

There are two things we can sum up:
1) The query time of the limit statement is proportional to the position of the starting record
2) MySQL limit statement is very convenient, but for many records of the table is not suitable for direct use.

A performance optimization method for limit paging problem

Speed up paged queries with table overlay indexes
As we all know, if you only include that index column (overwriting index) in the statement that uses the index query, then this will query quickly.

Because the index is used to find the optimization algorithm, and the data on the query index, no need to find the relevant data address, which saves a lot of time. In addition, MySQL also has the relevant index cache, in high concurrency when the use of caching is better.

In our case, we know that the ID field is the primary key and naturally contains the default primary key index. Now let's look at how the query using the overlay index works:

This time we are querying the last page of data (using the overwrite index, which contains only the ID column), as follows:
Select ID from product limit 866613, 20 0.2 seconds
Increases the speed of about 100 times relative to 37.44 seconds of querying all columns

So if we also want to query all the columns, there are two ways, one is the form of id>=, the other is to use join, to see the actual situation:

SELECT * FROM Product WHERE ID > = (select ID from Product limit 866613, 1) limit 20
Query time is 0.2 seconds, is a qualitative leap ah, haha

Another way of writing select * from Product a joins (select ID from product limit 866613, +) b on a.id = b.ID query time is also very short, like!

In fact, both use a principle, so the effect is similar

Execution plan

Explain + query SQL-used to display SQL execution information parameters that can be optimized for SQL based on reference information

such as:mysql> explainSelect* from(SelectNid,name fromTb1whereNid <Ten) asB;+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+| ID | Select_type | Table | Type | Possible_keys | Key | Key_len |ref| Rows | Extra |+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+ |1| PRIMARY | <derived2> | All | NULL | NULL | NULL | NULL |9| NULL | |2| DERIVED | tb1 | Range | PRIMARY | PRIMARY |8| NULL |9| Usingwhere|+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+

Result field Description

ID: Query Order identification

Select_type

Query type

Simple query

PRIMARY Outermost Query

subquery mapping to Subqueries

DERIVED Sub-query

Union Union

Result of Union result using union

Table: Names of tables being accessed

Type

How to access the query, performance: All < index < range < Index_merge < Ref_or_null < ref < Eq_ref < System/const

All (must be optimized)

Full table Scan, find the data sheet from beginning to end

SELECT * from TB1;

Special: If there is a limit restriction, then no further downward scan will be found

SELECT * from tb1 where email = ' [email protected] '

SELECT * from tb1 where email = ' [email protected] ' limit 1;

Although both statements perform a full-table scan, and the second sentence uses limit, the scan is no longer resumed when one is found.

INDEX

Full index Scan, find the index from beginning to end

Select Nid from Tb1;

RANGE

Range Lookup for indexed columns

SELECT * from TB1 where name < ' Zhagsan ';

Between and

Inch

> >= < <= operation

NOTE:! = and > Symbols

Index_merge

Merge indexes, search using multiple single-column indexes

SELECT * from tb1 where name = Zhangsan ' or nid in (11,22,33);

REF

Find one or more values based on an index

SELECT * from tb1 WHERE name = ' seven ';

Eq_ref

Use primary key or unique type when connecting

Select Tb2.nid,tb1.name from TB2 left join tb1 on tb2.nid = Tb1.nid;

CONST

Constant

The table has a maximum of one matching row, because only one row, the column values in this row can be considered constants by the remainder of the optimizer, and the const table is fast because they are read only once.

Select Nid from tb1 where nid = 2;

SYSTEM

System

Table has only one row (= system table). This is a special case of the const join type.

SELECT * FROM (select Nid from tb1 where nid = 1) as A;

Possible_keys: Indexes that may be used

Key: real-world use

Use index byte length in Key_len:mysql

Rows

MySQL estimates the number of rows to read in order to find the desired rows------just a pre-estimate

Slow log Query

MySQL's slow query log is a log record provided by MySQL, which is used to record a statement in MySQL that has a response time exceeding the threshold, which refers to SQL that runs longer than the Long_query_time value, and is recorded in the slow query log. The default value of Long_query_time is 10, which means to run statements above 10S. By default, the MySQL database does not start the slow query log, we need to manually set this parameter, of course, if not tuning needs, it is generally not recommended to start this parameter, because the slow query log on more or less to bring some performance impact. The slow query log supports writing log records to a file, and also supports writing log records to a database table.

Explanation of parameters related to MySQL slow query

slow_query_log    ：是否开启慢查询日志，1表示开启，0表示关闭。
log-slow-queries  ：旧版（5.6以下版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
slow-query-log-file：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
long_query_time ：慢查询阈值，当查询时间多于设定的阈值时，记录日志。
log_queries_not_using_indexes：未使用索引的查询也被记录到慢查询日志中（可选项）。
log_output：日志存储方式。log_output=‘FILE‘表示将日志存入文件，默认值是‘FILE‘。log_output=‘TABLE‘表示将日志存入数据库，
这样日志信息就会被写入到mysql.slow_log表中。MySQL数据<br>库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output=‘FILE,TABLE‘。
日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需<br>要能够获得更高的系统性能，那么建议优先记录到文件。

Turn on Slow query

Locate the MySQL configuration file, my.cnf (Windows My.ini), and add the following lines under [Mysqld]:

slow_query_log = 1                          
long_query_time = 2                           
slow_query_log_file = /usr/slow.log       
log_queries_not_using_indexes = 1

Restarting the MySQL service

Location of MySQL configuration file

The windows:windows configuration file is My.ini, usually under the MySQL installation directory or under C:\Windows.

The Linux:linux configuration file is my.cnf, usually in/etc.

Identify the QR code in the graph and pick up the Python full video

Correct use of indexes (SQL optimization), limit paging optimization, execution plan, slow log query