詳解MySQL 索引+explain

mysql視頻教程欄目今天著重介紹索引+explain，為需要面試的準備。

免費推薦：mysql視頻教程

一、索引的介紹

1. 在mysql中,索引就是數據結構,已經在文件中按照索引進行排序好的結構.
2. 使用索引可以加快我們的查詢速度,但是對我們的數據增刪改效率會降低.
3. 因為一個網站大部分都是查詢,我們主要優化select語句.

二、MySQL中索引的分類

• 普通索引 key
• 唯一索引 unique key unique key 別名 別名可忽略 別名可忽略
• 主鍵索引 primary key(字段)
• 全文索引myisam引擎支持(只對英文進行索引,mysql版本5.6也支持),sphinx(中文搜索)
• 混合索引 多個字段組成的索引.如 key key_index(title,email)

三、索引的基本操作

1、給表添加索引

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;

查看表

desc tablename

mysql> 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)

查看表的創建語句

show create table tbalename/G

mysql> 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> show create table t_indexG; *************************** 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

2、刪除索引

1. 刪除主鍵索引

alter table table_name drop primary key;

注意：

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

主鍵不一定是自增長，但是自增長一定是主鍵。

刪除逐漸之前先要把主鍵索引的自增長去掉。

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

再來刪除主鍵

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

1. 刪除普通和唯一的索引

alter table table_name drop key ‘索引的別名’

實際操作

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、添加索引

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、有無索引對比

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

插入數據

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;

有無索引查詢數據對比

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)

表結構

mysql> show create table articleG; *************************** 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

四、explain分析

使用explain可以對sql語句進行分析到底有沒有使用到索引查詢,從而更好的優化它.

我們只需要在select語句前面加上一句explain或者desc.

1、語法

explain|desc select * from tablename G;

2、分析

用剛才的兩個有無索引對比看看

mysql> mysql> explain select * from article where no_index=1495298G; *************************** 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=1495298G; *************************** 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、explain的type項分析

type項從優到差依次排序:

• system:一般系統表只有一行記錄的時候才會出現
• const:當對主鍵值進行等值查詢的時候會出現,如where id=666666
• range:當對索引的值進行范圍查詢的時候會出現,如 where id<100000
• index:當我們查詢的字段恰好是我們索引文件中的值,就會出現
• All:最差的一種情況,需要避免.

實際測試

mysql> use mysql; mysql> explain select * from userG; *************************** 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=666666G; *************************** 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>666666G; mysql> explain select * from article where id<666666G;

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

如果查詢的字段在索引文件存在，那么就會直接從索引文件中進行查詢，我們把這種查詢稱之為索引覆蓋查詢。

出現all，我們需要避免，因為進行全面掃描。

對于出現all的，可以給該字段增加普通索引查詢

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=666666G; *************************** 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、使用索引的場景

1、 經常出現在where后面的字段,我們需要給他加索引

2、order by 語句使用索引的優化

mysql> explain select * from article order by idG; *************************** 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 idG; *************************** 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

可以看出，即使是使用了索引但是幾乎還是全表掃描。

加了where就少了一半

3、針對like的模糊查詢索引的優化

where title like ‘%keyword%’ ====>全表掃描

where title like ‘keyword%’ ===>會使用到索引查詢

給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 articleG; *************************** 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)

因為%沒有出現在like關鍵字查詢的最左邊，所以可以使用到索引查詢

只要是like左邊出現了%，就是全表查詢

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、limit語句的索引使用優化

針對于limit語句的優化，我們可以在它前面加order by 索引字段

如果order by的字段是索引，會先去索引文件中查找指定行數的數據

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

另外一種針對于limit的優化方法:

索引覆蓋+延時關聯

原理:主要利用索引覆蓋查詢,把覆蓋索引查詢返回的id作為與我們要查詢記錄的id進行相關聯,

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、復合(多列)索引的最左原則(面試經常問)

只要查詢的時候出現復合索引的最左邊的字段才會使用到索引查詢

把article表的no_index和title建立復合索引:

//給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 articleG; *************************** 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 articleG; *************************** 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=77777G; *************************** 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=77777G;  *************************** 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)