Detailed explanation of MySQL index+explain

mysql video tutorial column today focuses on the index explain to prepare for interviews.

##Free recommendation: mysql video tutorial

1. Introduction to index

In mysql, the index is the data structure, the structure that has been sorted according to the index in the file.
1. Using the index can speed up our Query speed, but the efficiency of adding, deleting and modifying our data will be reduced.
2. Because most of a website is query, we mainly optimize the select statement.

2. Index in MySQL Classification

##Normal index
• key
Unique key
• unique key unique key Alias ​​Alias ​​can be ignored Alias ​​can be ignored
Primary key index
• primary key(field)
Full text index
• myisam engine support (only English indexing, mysql version 5.6 also supports), sphinx (Chinese search)
Mixed index
• An index composed of multiple fields. Such as key key_index(title,email)
3. Basic operations of index

1. Add an index to the table

create table t_index(
    id int not null auto_increment,
    title varchar(30) not null default '',
    email varchar(30) not null default '',
    primary key(id),
    unique key uni_email(email) ,
    key key_title(title)
)engine=innodb charset=utf8;

View the table

desc tablename

<pre class="brush:php;toolbar:false">mysql&gt; desc t_index; +-------+-------------+------+-----+---------+----------------+ | Field | Type        | Null | Key | Default | Extra          | +-------+-------------+------+-----+---------+----------------+ | id    | int(11)     | NO   | PRI | NULL    | auto_increment | | title | varchar(30) | NO   | MUL |         |                | | email | varchar(30) | NO   | UNI |         |                | +-------+-------------+------+-----+---------+----------------+ 3 rows in set (0.01 sec)</pre>

View the creation statement of the table

show create table tbalename/G

<pre class="brush:php;toolbar:false">mysql&gt; show create table t_index/G; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/G' at line 1 mysql&gt; show create table t_index\G; *************************** 1. row ***************************        Table: t_index Create Table: CREATE TABLE `t_index` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `title` varchar(30) NOT NULL DEFAULT '',   `email` varchar(30) NOT NULL DEFAULT '',   PRIMARY KEY (`id`),   UNIQUE KEY `uni_email` (`email`),   KEY `key_title` (`title`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) ERROR:  No query specified</pre>2. Delete index

Delete primary key index

alter table table_name drop primary key;

Note:

mysql> alter table t_index drop primary key;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

The primary key is not necessarily self-increasing, but self-increasing must be the primary key.

Before deleting the index, you must first remove the auto-increment of the primary key index.

mysql> alter table t_index modify  id int not null;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

Delete the primary key again

mysql> alter table t_index drop primary key;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

Delete ordinary and unique indexes
##alter table table_name drop key ' Index alias '

Actual operation

mysql> alter table t_index drop key uni_email;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t_index drop key key_title;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

3. Add index

alter table t_index add key key_title(title);
alter table t_index add key uni_email(email);
alter table t_index add primary key(id);

4. Compare whether there is an index

create table article(
id int not null auto_increment,
no_index int,
title varchar(30) not null default '',
add_time datetime,
primary key(id)
);

Insert data

mysql> insert into article(id,title,add_time) values(null,'ddsd1212123d',now());

mysql> insert into article(title,add_time) select title,now() from article;
Query OK, 10 rows affected (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> update article set no_index=id;

Comparison of query data with or without index

mysql> select * from article where no_index=1495298;
+---------+----------+-----------+---------------------+
| id      | no_index | title     | add_time            |
+---------+----------+-----------+---------------------+
| 1495298 |  1495298 | ddsd1123d | 2019-05-15 23:13:56 |
+---------+----------+-----------+---------------------+
1 row in set (0.28 sec)

mysql> select * from article where id=1495298;
+---------+----------+-----------+---------------------+
| id      | no_index | title     | add_time            |
+---------+----------+-----------+---------------------+
| 1495298 |  1495298 | ddsd1123d | 2019-05-15 23:13:56 |
+---------+----------+-----------+---------------------+
1 row in set (0.01 sec)

Table structure

mysql> show create table article\G;
*************************** 1. row ***************************
       Table: article
Create Table: CREATE TABLE `article` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `no_index` int(11) DEFAULT NULL,
  `title` varchar(30) NOT NULL DEFAULT '',
  `add_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1572824 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR: 
No query specified

4.

explain analysis

Use explain to perform sql statements Analyze whether the index query is used to better optimize it.

We only need to add an explain or desc in front of the select statement.

1. Syntax

explain|desc select * from tablename \G;

2. Analysis

Use the two just mentioned to compare whether there are indexes or not

mysql> mysql> explain select * from article where no_index=1495298\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE//单表查询
        table: article//查询的表名
   partitions: NULL
         type: ALL//索引的类型，从好到坏的情况是：system>const>range>index>All
possible_keys: NULL//可能使用到的索引
          key: NULL//实际使用到的索引
      key_len: NULL//索引的长度
          ref: NULL
         rows: 1307580//可能进行扫描表的行数
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

mysql> explain select * from article where id=1495298\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: const//当对主键索引进行等值查询的时候出现const
possible_keys: PRIMARY
          key: PRIMARY//实际使用到的所有primary索引
      key_len: 4//索引的长度4 = int占4个字节
          ref: const
         rows: 1//所扫描的行数只有一行
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

3. Analysis of type items of

explain

Type items are sorted from best to worst:

system

: Generally, the system table has only one row of records. It will only appear when
• const
: It will appear when performing an equivalent query on the primary key value, such as where id=666666
• range
: It will appear when performing a range query on the index value, such as where idindex: When the field we query happens to be the value in our index file, it will Appears
• All
: The worst situation, which needs to be avoided.
• Actual test

mysql> use mysql;
mysql> explain select * from user\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

mysql> use test;
mysql> explain select * from article where id=666666\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL

mysql> explain select * from article where id>666666\G;
mysql> explain select * from article where id<pre class="brush:php;toolbar:false">mysql> explain select id  from article \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 1307580
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

If the queried field is in the index file exists, then the query will be performed directly from the index file. We call this query an index coverage query.

All appears, we need to avoid it because of a full scan.

For all, you can add a normal index query to the field

mysql> alter table article add key key_no_index(no_index);
Query OK, 0 rows affected (1.92 sec)
Records: 0  Duplicates: 0  Warnings: 0

type为ref，应该是关联，但是ref是const
mysql> explain select * from article where no_index=666666\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: ref
possible_keys: key_no_index
          key: key_no_index
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

速度飞跃
mysql> select * from article where no_index=666666;
+--------+----------+-----------+---------------------+
| id     | no_index | title     | add_time            |
+--------+----------+-----------+---------------------+
| 666666 |   666666 | ddsd1123d | 2019-05-15 23:13:55 |
+--------+----------+-----------+---------------------+
1 row in set (0.00 sec)

4. Scenarios for using index

1. For fields that often appear after where, we need Add an index to it

2. Optimization of the index using the order by statement

mysql> explain select * from article order by id\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 1307580
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

mysql> explain select * from article where id >0  order by id\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 653790
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.01 sec)

ERROR: 
No query specified

It can be seen that even if an index is used, it is almost still a full table scan.

If you add where, you will lose half of it

3. Optimization of fuzzy query index for like

where title like '%keyword%' ====>Full table scan

where title like 'keyword%' ===>Index query

Add index to title

mysql> alter table article  add key key_index(title);
Query OK, 0 rows affected (2.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table article\G;
*************************** 1. row ***************************
       Table: article
Create Table: CREATE TABLE `article` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `no_index` int(11) DEFAULT NULL,
  `title` varchar(30) NOT NULL DEFAULT '',
  `add_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `key_no_index` (`no_index`),
  KEY `key_index` (`title`)
) ENGINE=InnoDB AUTO_INCREMENT=1507299 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Because % does not appear on the leftmost side of the like keyword query, you can use the index query

As long as It is % that appears on the left side of like, which is the full table query

mysql> explain select * from article where title like 'a%'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: range//范围查询
possible_keys: key_index
          key: key_index
      key_len: 92//
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from article where title like '%a%'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: ALL//全表查询
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1307580
     filtered: 11.11
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

4. Index usage optimization of the limit statementFor the optimization of the limit statement, we can add order by in front of it Index field

If the order by field is an index, it will first search for the specified number of rows of data in the index file

mysql> explain select sql_no_cache  * from article limit 90000,10 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: ALL//全表
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1307580
     filtered: 100.00
        Extra: NULL
1 row in set, 2 warnings (0.00 sec)

ERROR: 
No query specified

mysql> explain select sql_no_cache  * from article order by id  limit 90000,10 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: index
possible_keys: NULL
          key: PRIMARY//使用到了索引
      key_len: 4
          ref: NULL
         rows: 90010
     filtered: 100.00
        Extra: NULL
1 row in set, 2 warnings (0.00 sec)

ERROR: 
No query specified

Another optimization method for limit:

Index coverage delayed association

Principle: Mainly use index coverage query to associate the id returned by the covering index query with the id of the record we want to query,

mysql> select sql_no_cache  * from article limit 1000000,10;
+---------+----------+----------------+---------------------+
| id      | no_index | title          | add_time            |
+---------+----------+----------------+---------------------+
| 1196579 |  1196579 | ddsd12123123ad | 2019-05-15 23:13:56 |
| 1196580 |  1196580 | ddsd121231ad   | 2019-05-15 23:13:56 |
| 1196581 |  1196581 | ddsd1212123d   | 2019-05-15 23:13:56 |
| 1196582 |  1196582 | ddsd1123123d   | 2019-05-15 23:13:56 |
| 1196583 |  1196583 | ddsd1123d      | 2019-05-15 23:13:56 |
| 1196584 |  1196584 | ddsd1123d      | 2019-05-15 23:13:56 |
| 1196585 |  1196585 | ddsd1123d      | 2019-05-15 23:13:56 |
| 1196586 |  1196586 | ddsd1123d      | 2019-05-15 23:13:56 |
| 1196587 |  1196587 | ddsd1123d      | 2019-05-15 23:13:56 |
| 1196588 |  1196588 | ddsd1123d      | 2019-05-15 23:13:56 |
+---------+----------+----------------+---------------------+
10 rows in set, 1 warning (0.21 sec)

mysql> select t1.* from article as t1 inner join (select id as pid from article  limit 10000,10) as t2 on t1.id=t2.pid;
+-------+----------+----------------+---------------------+
| id    | no_index | title          | add_time            |
+-------+----------+----------------+---------------------+
| 13058 |    13058 | ddsd12123123ad | 2019-05-15 23:13:49 |
| 13059 |    13059 | ddsd121231ad   | 2019-05-15 23:13:49 |
| 13060 |    13060 | ddsd1212123d   | 2019-05-15 23:13:49 |
| 13061 |    13061 | ddsd1123123d   | 2019-05-15 23:13:49 |
| 13062 |    13062 | ddsd1123d      | 2019-05-15 23:13:49 |
| 13063 |    13063 | ddsd1123d      | 2019-05-15 23:13:49 |
| 13064 |    13064 | ddsd1123d      | 2019-05-15 23:13:49 |
| 13065 |    13065 | ddsd1123d      | 2019-05-15 23:13:49 |
| 13066 |    13066 | ddsd1123d      | 2019-05-15 23:13:49 |
| 13067 |    13067 | ddsd1123d      | 2019-05-15 23:13:49 |
+-------+----------+----------------+---------------------+
10 rows in set (0.00 sec)

5. The leftmost principle of compound (multi-column) index (often asked in interviews)

As long as the leftmost field of the compound index appears during the query, the index query will be used

Create a composite index on no_index and title of the article table:

//给no_index和title创建一个复合索引
mysql> alter table article add key index_no_index_title(no_index,title);
Query OK, 0 rows affected (1.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

//查看创建后的结构
mysql> show create table article\G;
*************************** 1. row ***************************
       Table: article
Create Table: CREATE TABLE `article` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `no_index` int(11) DEFAULT NULL,
  `title` varchar(30) NOT NULL DEFAULT '',
  `add_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `key_no_index` (`no_index`),
  KEY `key_index` (`title`),
  KEY `index_no_index_title` (`no_index`,`title`)
) ENGINE=InnoDB AUTO_INCREMENT=1507299 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

//删除no_index和title的索引
mysql> alter table article drop key key_index;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table article drop key key_no_index;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table article\G;
*************************** 1. row ***************************
       Table: article
Create Table: CREATE TABLE `article` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `no_index` int(11) DEFAULT NULL,
  `title` varchar(30) NOT NULL DEFAULT '',
  `add_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_no_index_title` (`no_index`,`title`)
) ENGINE=InnoDB AUTO_INCREMENT=1507299 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

//复合索引使用情况
mysql> explain select * from article where title='ddsd1123d' and no_index=77777\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: ref
possible_keys: index_no_index_title
          key: index_no_index_title
      key_len: 97
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from article where  no_index=77777\G; 
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: ref
possible_keys: index_no_index_title
          key: index_no_index_title
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

五、慢查询日志

1、介绍

我们可以定义(程序员)一个sql语句执行的最大执行时间,如果发现某条sql语句的执行时间超过我们所规定的时间界限,那么这条sql就会被记录下来.

2、慢查询具体操作

1. 先开启慢日志查询

   查看慢日志配置

   mysql> show variables like '%slow_query%';
   +---------------------+--------------------------------------------------+
   | Variable_name       | Value                                            |
   +---------------------+--------------------------------------------------+
   | slow_query_log      | OFF                                              |
   | slow_query_log_file | /usr/local/mysql/data/caredeMacBook-Pro-slow.log |
   +---------------------+--------------------------------------------------+
   2 rows in set (0.00 sec)

   开启慢日志查询

   mysql> set global slow_query_log=on;
   Query OK, 0 rows affected (0.00 sec)

   再次检查慢日志配置

   mysql> show variables like '%slow_query%';
   +---------------------+--------------------------------------------------+
   | Variable_name       | Value                                            |
   +---------------------+--------------------------------------------------+
   | slow_query_log      | ON                                               |
   | slow_query_log_file | /usr/local/mysql/data/caredeMacBook-Pro-slow.log |
   +---------------------+--------------------------------------------------+
   2 rows in set (0.00 sec)

2. 去mysql配置文件my.ini中指定sql语句的界限时间和慢日志文件的路径

   慢日志的名称，默认保存在mysql目录下面的data目录下面

   log-slow-queries = 'man.txt'

   设置一个界限时间

   long-query-time=5

   重启

六、profile工具

1、介绍

通过profile工具分析一条sql语句的时间消耗在哪里

2、具体操作

1. 开启profile

2. 执行一条SQL，(开启之后执行的所有SQL语句都会被记录下来

   ，以查看某条sql语句的具体执行时间耗费哪里)

3. 根据query_id查找到具体的SQL

实例：

//查看profile设置
mysql> show variables like '%profil%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| have_profiling         | YES   |
| profiling              | OFF   |//未开启状态
| profiling_history_size | 15    |
+------------------------+-------+
3 rows in set (0.00 sec)

//开启操作
mysql> set profiling = on;
Query OK, 0 rows affected, 1 warning (0.00 sec)

//查看是否开启成功
mysql> show variables like '%profil%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| have_profiling         | YES   |
| profiling              | ON    |//开启成功
| profiling_history_size | 15    |
+------------------------+-------+
3 rows in set (0.00 sec)

具体查询

mysql> select * from article where no_index=666666;
+--------+----------+-----------+---------------------+
| id     | no_index | title     | add_time            |
+--------+----------+-----------+---------------------+
| 666666 |   666666 | ddsd1123d | 2019-05-15 23:13:55 |
+--------+----------+-----------+---------------------+
1 row in set (0.02 sec)

mysql> show profiles;
+----------+------------+---------------------------------------------+
| Query_ID | Duration   | Query                                       |
+----------+------------+---------------------------------------------+
|        1 | 0.00150700 | show variables like '%profil%'              |
|        2 | 0.01481100 | select * from article where no_index=666666 |
+----------+------------+---------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql> show profile for query 2;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000291 |
| checking permissions | 0.000007 |
| Opening tables       | 0.012663 |//打开表
| init                 | 0.000050 |
| System lock          | 0.000009 |
| optimizing           | 0.000053 |
| statistics           | 0.001566 |
| preparing            | 0.000015 |
| executing            | 0.000002 |
| Sending data         | 0.000091 |//磁盘上的发送数据
| end                  | 0.000004 |
| query end            | 0.000007 |
| closing tables       | 0.000006 |
| freeing items        | 0.000037 |
| cleaning up          | 0.000010 |
+----------------------+----------+
15 rows in set, 1 warning (0.01 sec)

The above is the detailed content of Detailed explanation of MySQL index+explain. For more information, please follow other related articles on the PHP Chinese website!