索引的使用

1 驗證索引提升查詢效率
2 索引的使用
- 2.1 準備環境
- 2.2 避免索引失效
- - 1). 全值匹配，對索引中所有列都指定具體值，
  - 2). 最左前綴法則（復合索引）
  - 3). 范圍查詢右邊的列，不能使用索引
  - 4). 不要在索引列上進行運算操作，索引將失效
  - 5). 字串不加單引號，造成索引失效
  - 6). 盡量使用覆寫索引，避免select *
  - 7). 用or分割開的條件，如果or前的條件中的列有索引，而后面的列中沒有索引，那么涉及的索引都不會被用到
  - 8). 以%開頭的Like模糊查詢，索引失效
  - 9). 如果MySQL評估使用索引比全表更慢，則不使用索引
  - 10). is NULL ， is NOT NULL 有時索引失效
  - 11). in 走索引， not in 索引失效
  - 12). 單列索引和復合索引
3 查看索引使用情況

關于MySQL索引一些具體特性在 MySQL索引中有具體的介紹，這里主要介紹索引的使用
索引是資料庫優化最常用也是最重要的手段之一, 通過索引通常可以幫助用戶解決大多數的MySQL的性能優化問題，

1 驗證索引提升查詢效率

在我們準備的表結構tb_item 中，一共約存盤了250萬記錄；具體的創建程序參見SQL優化步驟（explain等）

查看tb_item表中元素的個數：

mysql> select count(*) from tb_item;
+----------+
| count(*) |
+----------+
|  2499695 |
+----------+
1 row in set (2.60 sec)

使用id欄位和name欄位進行精確查詢，

mysql> select * from tb_item where id=1800;
+------+---------------+----------+-------+------------+--------+------------+---------------------+---------------------+
| id   | title         | price    | num   | categoryid | status | sellerid   | createtime          | updatetime          |
+------+---------------+----------+-------+------------+--------+------------+---------------------+---------------------+
| 1800 | 貨物1800號    | 63271.23 | 56516 |          9 | 1      | 5435343235 | 2019-04-20 22:37:15 | 2019-04-20 22:37:15 |
+------+---------------+----------+-------+------------+--------+------------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> select * from tb_item where title='貨物1000號';
+------+---------------+---------+-------+------------+--------+------------+---------------------+---------------------+
| id   | title         | price   | num   | categoryid | status | sellerid   | createtime          | updatetime          |
+------+---------------+---------+-------+------------+--------+------------+---------------------+---------------------+
| 1000 | 貨物1000號    | 6610.28 | 95953 |          5 | 1      | 5435343235 | 2019-04-20 22:37:15 | 2019-04-20 22:37:15 |
+------+---------------+---------+-------+------------+--------+------------+---------------------+---------------------+
1 row in set (3.68 sec)

我們可以看到，使用id欄位精確查詢資料非常快，0秒就完成，而是用title欄位精確查詢資料需要3.68秒，因為id欄位是有索引的，因為在資料表中，只要某個欄位被定義了主鍵，那么這個欄位就具有主鍵索引，

處理方案，針對title欄位，創建索引：

create index idx_item_title on tb_item(title);

我們發現，創建索引的時間也需要51s，
索引創建完成之后，再次進行查詢：

mysql> select * from tb_item where title='貨物1000號';
+------+---------------+---------+-------+------------+--------+------------+---------------------+---------------------+
| id   | title         | price   | num   | categoryid | status | sellerid   | createtime          | updatetime          |
+------+---------------+---------+-------+------------+--------+------------+---------------------+---------------------+
| 1000 | 貨物1000號    | 6610.28 | 95953 |          5 | 1      | 5435343235 | 2019-04-20 22:37:15 | 2019-04-20 22:37:15 |
+------+---------------+---------+-------+------------+--------+------------+---------------------+---------------------+
1 row in set (0.07 sec)

此時執行時間僅0.07s，這樣也就驗證了索引對查詢效率的提升，

2 索引的使用

創建了索引也不一定會極大的提高效率，只有好好的利用了索引，才會改善效率，

2.1 準備環境

create table `tb_seller` (
	`sellerid` varchar (100),
	`name` varchar (100),
	`nickname` varchar (50),
	`password` varchar (60),
	`status` varchar (1),
	`address` varchar (100),
	`createtime` datetime,
    primary key(`sellerid`)
)engine=innodb default charset=utf8mb4; 

insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('alibaba','阿里巴巴','阿里小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('baidu','百度科技有限公司','百度小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('huawei','華為科技有限公司','華為小店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('itcast','傳智播客教育科技有限公司','傳智播客','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('itheima','黑馬程式員','黑馬程式員','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('luoji','羅技科技有限公司','羅技小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('oppo','OPPO科技有限公司','OPPO官方旗艦店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('ourpalm','掌趣科技股份有限公司','掌趣小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('qiandu','千度科技','千度小店','e10adc3949ba59abbe56e057f20f883e','2','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('sina','新浪科技有限公司','新浪官方旗艦店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('xiaomi','小米科技','小米官方旗艦店','e10adc3949ba59abbe56e057f20f883e','1','西安市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('yijia','宜家家居','宜家家居旗艦店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');


create index idx_seller_name_sta_addr on tb_seller(name,status,address);

注意：這里對name,status,address創建了聯合索引，

mysql> select * from tb_seller;
+----------+--------------------------------------+-----------------------+----------------------------------+--------+-----------+---------------------+
| sellerid | name                                 | nickname              | password                         | status | address   | createtime          |
+----------+--------------------------------------+-----------------------+----------------------------------+--------+-----------+---------------------+
| alibaba  | 阿里巴巴                             | 阿里小店              | e10adc3949ba59abbe56e057f20f883e | 1      | 北京市    | 2088-01-01 12:00:00 |
| baidu    | 百度科技有限公司                     | 百度小店              | e10adc3949ba59abbe56e057f20f883e | 1      | 北京市    | 2088-01-01 12:00:00 |
| huawei   | 華為科技有限公司                     | 華為小店              | e10adc3949ba59abbe56e057f20f883e | 0      | 北京市    | 2088-01-01 12:00:00 |
| itcast   | 傳智播客教育科技有限公司             | 傳智播客              | e10adc3949ba59abbe56e057f20f883e | 1      | 北京市    | 2088-01-01 12:00:00 |
| itheima  | 黑馬程式員                           | 黑馬程式員            | e10adc3949ba59abbe56e057f20f883e | 0      | 北京市    | 2088-01-01 12:00:00 |
| luoji    | 羅技科技有限公司                     | 羅技小店              | e10adc3949ba59abbe56e057f20f883e | 1      | 北京市    | 2088-01-01 12:00:00 |
| oppo     | OPPO科技有限公司                     | OPPO官方旗艦店        | e10adc3949ba59abbe56e057f20f883e | 0      | 北京市    | 2088-01-01 12:00:00 |
| ourpalm  | 掌趣科技股份有限公司                 | 掌趣小店              | e10adc3949ba59abbe56e057f20f883e | 1      | 北京市    | 2088-01-01 12:00:00 |
| qiandu   | 千度科技                             | 千度小店              | e10adc3949ba59abbe56e057f20f883e | 2      | 北京市    | 2088-01-01 12:00:00 |
| sina     | 新浪科技有限公司                     | 新浪官方旗艦店        | e10adc3949ba59abbe56e057f20f883e | 1      | 北京市    | 2088-01-01 12:00:00 |
| xiaomi   | 小米科技                             | 小米官方旗艦店        | e10adc3949ba59abbe56e057f20f883e | 1      | 西安市    | 2088-01-01 12:00:00 |
| yijia    | 宜家家居                             | 宜家家居旗艦店        | e10adc3949ba59abbe56e057f20f883e | 1      | 北京市    | 2088-01-01 12:00:00 |
+----------+--------------------------------------+-----------------------+----------------------------------+--------+-----------+---------------------+
12 rows in set (0.00 sec)

2.2 避免索引失效

1). 全值匹配，對索引中所有列都指定具體值，

改情況下，索引生效，執行效率高，

例如：

mysql> select * from tb_seller where name ='小米科技' and status='1' and address='北京市';
Empty set (0.01 sec)

索引生效

mysql> explain select * from tb_seller where name ='小米科技' and status='1' and address='北京市';
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------------------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys            | key                      | key_len | ref               | rows | filtered | Extra |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | tb_seller | NULL       | ref  | idx_seller_name_sta_addr | idx_seller_name_sta_addr | 813     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

explain的各個引數的具體解釋可以參考文章SQL優化步驟，這里的simple表示的是簡單的select查詢，查詢中不包含子查詢和union查詢，type中的ref表示非唯一性索引掃描，索參考的是idx_seller_name_sta_addr，ref中的const,const,const表示我們
是按照常量查詢，

2). 最左前綴法則（復合索引）

如果索引了多列，要遵守最左前綴法則，指的是查詢從索引的最左前列開始，并且不跳過索引中的列，
我們創建了符合索引name,status,address.比如我們在進行查詢的時候，需要從最左邊的列開始，即查詢的條件中必須包含最左邊的列name,并且不能跳過索引當中的列，
我們以下面的案例來演示一下

匹配最左前綴法則，走索引：
只查詢最左邊的列name,可以看到走了索引，key的內容idx_seller_name_sta_addr，key_len為403

mysql> explain select * from tb_seller where name ='小米科技';
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys            | key                      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tb_seller | NULL       | ref  | idx_seller_name_sta_addr | idx_seller_name_sta_addr | 403     | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.03 sec)

查詢左邊的兩個name和status，也走索引，索引的長度變為410

mysql> explain select * from tb_seller where name ='小米科技' and status='1';
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys            | key                      | key_len | ref         | rows | filtered | Extra |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | tb_seller | NULL       | ref  | idx_seller_name_sta_addr | idx_seller_name_sta_addr | 410     | const,const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

違法最左前綴法則，索引失效：
如果我們跳過了name,查詢status和address，就不走索引了

mysql> explain select * from tb_seller where status='1' and address='北京市';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_seller | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   12 |     8.33 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

如果三者的順序發生變化，也是走索引的，

mysql> explain select * from tb_seller where status='1' and address='北京市' and name='小米科技';
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------------------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys            | key                      | key_len | ref               | rows | filtered | Extra |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | tb_seller | NULL       | ref  | idx_seller_name_sta_addr | idx_seller_name_sta_addr | 813     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

只要我們查詢條件中包含最左邊的列，并且沒有跳躍，這個時候就會走索引，
有疑問的地方在于，跳過了中間的status，也走了索引，我們可以看到索引長度為403，說明只走了name欄位的索引，但是address的索引并沒有走，
如果符合最左法則，但是出現跳躍某一列，只有最左列索引生效：

mysql> explain select * from tb_seller where name='小米科技' and address='北京市';
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table     | partitions | type | possible_keys            | key                      | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | tb_seller | NULL       | ref  | idx_seller_name_sta_addr | idx_seller_name_sta_addr | 403     | const |    1 |    10.00 | Using index condition |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

3). 范圍查詢右邊的列，不能使用索引

這里我們先注意一下，由上面的分析，走1列、2列、3列索引時，索引的長度分別為403,410,413，
status用到了范圍查詢：

mysql> explain select * from tb_seller where name='小米科技' and status > '1' and address='北京市';
+----+-------------+-----------+------------+-------+--------------------------+--------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys            | key                      | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+--------------------------+--------------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | tb_seller | NULL       | range | idx_seller_name_sta_addr | idx_seller_name_sta_addr | 410     | NULL |    1 |    10.00 | Using index condition |
+----+-------------+-----------+------------+-------+--------------------------+--------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

根據前面的兩個欄位name ， status 查詢是走索引的，但是最后一個條件address 沒有用到索引，

4). 不要在索引列上進行運算操作，索引將失效

注意：以下例子中，substring為取子串，其中字串從1開始

mysql> select * from tb_seller where substring(name,3,2)='科技';
+----------+--------------------------------+-----------------------+----------------------------------+--------+-----------+---------------------+
| sellerid | name                           | nickname              | password                         | status | address   | createtime          |
+----------+--------------------------------+-----------------------+----------------------------------+--------+-----------+---------------------+
| baidu    | 百度科技有限公司               | 百度小店              | e10adc3949ba59abbe56e057f20f883e | 1      | 北京市    | 2088-01-01 12:00:00 |
| huawei   | 華為科技有限公司               | 華為小店              | e10adc3949ba59abbe56e057f20f883e | 0      | 北京市    | 2088-01-01 12:00:00 |
| luoji    | 羅技科技有限公司               | 羅技小店              | e10adc3949ba59abbe56e057f20f883e | 1      | 北京市    | 2088-01-01 12:00:00 |
| ourpalm  | 掌趣科技股份有限公司           | 掌趣小店              | e10adc3949ba59abbe56e057f20f883e | 1      | 北京市    | 2088-01-01 12:00:00 |
| qiandu   | 千度科技                       | 千度小店              | e10adc3949ba59abbe56e057f20f883e | 2      | 北京市    | 2088-01-01 12:00:00 |
| sina     | 新浪科技有限公司               | 新浪官方旗艦店        | e10adc3949ba59abbe56e057f20f883e | 1      | 北京市    | 2088-01-01 12:00:00 |
| xiaomi   | 小米科技                       | 小米官方旗艦店        | e10adc3949ba59abbe56e057f20f883e | 1      | 西安市    | 2088-01-01 12:00:00 |
+----------+--------------------------------+-----------------------+----------------------------------+--------+-----------+---------------------+
7 rows in set (0.00 sec)

mysql> explain select * from tb_seller where substring(name,3,2)='科技';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_seller | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   12 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

5). 字串不加單引號，造成索引失效

mysql> explain select * from tb_seller where name='小米科技' and status =1;
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table     | partitions | type | possible_keys            | key                      | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | tb_seller | NULL       | ref  | idx_seller_name_sta_addr | idx_seller_name_sta_addr | 403     | const |    1 |    10.00 | Using index condition |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-----------------------+
1 row in set, 2 warnings (0.00 sec)

mysql> explain select * from tb_seller where name='小米科技' and status ='1';
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys            | key                      | key_len | ref         | rows | filtered | Extra |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | tb_seller | NULL       | ref  | idx_seller_name_sta_addr | idx_seller_name_sta_addr | 410     | const,const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

看來沒有加單引號也是有影響的，因為MySQL底層檢測到status是varchar型別，它就會對這一部分的值進行隱式型別轉換，之后這個索引欄位就失效了，

6). 盡量使用覆寫索引，避免select *

盡量使用覆寫索引（只訪問索引的查詢（索引列完全包含查詢列）），減少select * ，

mysql> explain select name,status,address from tb_seller where name='小米科技';
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------+-     -----+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys            | key                      | key_len | ref   |      rows | filtered | Extra       |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------+-     -----+----------+-------------+
|  1 | SIMPLE      | tb_seller | NULL       | ref  | idx_seller_name_sta_addr | idx_seller_name_sta_addr | 403     | const |         1 |   100.00 | Using index |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------+-     -----+----------+-------------+
1 row in set, 1 warning (0.00 sec)

這里extra欄位為using index,不需要回表查詢了，
如果查詢列，超出索引列，也會降低性能，
TIP :

using index ：使用覆寫索引的時候就會出現

using where：在查找使用索引的情況下，需要回表去查詢所需的資料

using index condition：查找使用了索引，但是需要回表查詢資料

using index ; using where：查找使用了索引，但是需要的資料都在索引列中能找到，所以不需要回表查詢資料

7). 用or分割開的條件，如果or前的條件中的列有索引，而后面的列中沒有索引，那么涉及的索引都不會被用到

or前的條件中的列有索引，后面的列中沒有索引，那么索引失效，

mysql> explain select * from tb_seller where name='小米科技' or nickname='小米官方旗艦店';
+----+-------------+-----------+------------+------+--------------------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys            | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+--------------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_seller | NULL       | ALL  | idx_seller_name_sta_addr | NULL | NULL    | NULL |   12 |    19.00 | Using where |
+----+-------------+-----------+------------+------+--------------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

如果換成and呢，可見and是可以的，

mysql> explain select * from tb_seller where name='小米科技' and nickname='小米官方旗艦店';
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys            | key                      | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | tb_seller | NULL       | ref  | idx_seller_name_sta_addr | idx_seller_name_sta_addr | 403     | const |    1 |    10.00 | Using where |
+----+-------------+-----------+------------+------+--------------------------+--------------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

8). 以%開頭的Like模糊查詢，索引失效

如果僅僅是尾部模糊匹配，索引不會失效，如果是頭部模糊匹配，索引失效，
查詢以科技開頭的字串，此時百分號在尾部，索引沒有失效，

mysql> explain select * from tb_seller where name like '科技%';
+----+-------------+-----------+------------+-------+--------------------------+--------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys            | key                      | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+--------------------------+--------------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | tb_seller | NULL       | range | idx_seller_name_sta_addr | idx_seller_name_sta_addr | 403     | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------------+-------+--------------------------+--------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

查詢以科技結尾的字串，此時百分號開頭，索引失效，

mysql> explain select * from tb_seller where name like '%科技';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_seller | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   12 |    11.11 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

解決方案：
通過覆寫索引來解決
在前面已經介紹，覆寫索引指的是索引列完全包含查詢列，只查詢包含索引的列，

如下面的例子，sellerid和name都建立了索引，所以使用%開頭的模糊查詢一樣走索引，

mysql> explain select sellerid,name from tb_seller where name like "%科技";
+----+-------------+-----------+------------+-------+---------------+--------------------------+---------+------+------+----------+--------------------------+
| id | select_type | table     | partitions | type  | possible_keys | key                      | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-----------+------------+-------+---------------+--------------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | tb_seller | NULL       | index | NULL          | idx_seller_name_sta_addr | 813     | NULL |   12 |    11.11 | Using where; Using index |
+----+-------------+-----------+------------+-------+---------------+--------------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

9). 如果MySQL評估使用索引比全表更慢，則不使用索引

根據前面的，我們在tb_seller表中建立了主鍵索引sellerid，和復合索引(name,status,address)

mysql> explain select * from tb_seller where address='北京市';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_seller | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   12 |    10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

根據最左前綴法則，上面的陳述句索引失效，
要想讓上面的陳述句在執行的時候使用索引，我們給address列建立一個單列索引

mysql> create index idx_seller_address on tb_seller(address);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> explain select * from tb_seller where address='北京市';
+----+-------------+-----------+------------+------+--------------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys      | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+--------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_seller | NULL       | ALL  | idx_seller_address | NULL | NULL    | NULL |   12 |    91.67 | Using where |
+----+-------------+-----------+------------+------+--------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from tb_seller where address='西安市';
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tb_seller | NULL       | ref  | idx_seller_address | idx_seller_address | 403     | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

但是在查詢北京市的時候，實際沒有走索引，在查詢西安市的時候，實際竟走了索引，

mysql> select * from tb_seller;
+----------+--------------------------------------+-----------------------+----------------------------------+--------+-----------+---------------------+
| sellerid | name                                 | nickname              | password                         | status | address   | createtime          |
+----------+--------------------------------------+-----------------------+----------------------------------+--------+-----------+---------------------+
| alibaba  | 阿里巴巴                             | 阿里小店              | e10adc3949ba59abbe56e057f20f883e | 1      | 北京市    | 2088-01-01 12:00:00 |
| baidu    | 百度科技有限公司                     | 百度小店              | e10adc3949ba59abbe56e057f20f883e | 1      | 北京市    | 2088-01-01 12:00:00 |
| huawei   | 華為科技有限公司                     | 華為小店              | e10adc3949ba59abbe56e057f20f883e | 0      | 北京市    | 2088-01-01 12:00:00 |
| itcast   | 傳智播客教育科技有限公司             | 傳智播客              | e10adc3949ba59abbe56e057f20f883e | 1      | 北京市    | 2088-01-01 12:00:00 |
| itheima  | 黑馬程式員                           | 黑馬程式員            | e10adc3949ba59abbe56e057f20f883e | 0      | 北京市    | 2088-01-01 12:00:00 |
| luoji    | 羅技科技有限公司                     | 羅技小店              | e10adc3949ba59abbe56e057f20f883e | 1      | 北京市    | 2088-01-01 12:00:00 |
| oppo     | OPPO科技有限公司                     | OPPO官方旗艦店        | e10adc3949ba59abbe56e057f20f883e | 0      | 北京市    | 2088-01-01 12:00:00 |
| ourpalm  | 掌趣科技股份有限公司                 | 掌趣小店              | e10adc3949ba59abbe56e057f20f883e | 1      | 北京市    | 2088-01-01 12:00:00 |
| qiandu   | 千度科技                             | 千度小店              | e10adc3949ba59abbe56e057f20f883e | 2      | 北京市    | 2088-01-01 12:00:00 |
| sina     | 新浪科技有限公司                     | 新浪官方旗艦店        | e10adc3949ba59abbe56e057f20f883e | 1      | 北京市    | 2088-01-01 12:00:00 |
| xiaomi   | 小米科技                             | 小米官方旗艦店        | e10adc3949ba59abbe56e057f20f883e | 1      | 西安市    | 2088-01-01 12:00:00 |
| yijia    | 宜家家居                             | 宜家家居旗艦店        | e10adc3949ba59abbe56e057f20f883e | 1      | 北京市    | 2088-01-01 12:00:00 |
+----------+--------------------------------------+-----------------------+----------------------------------+--------+-----------+---------------------+
12 rows in set (0.00 sec)

我們發現12條記錄，有11條都是北京市，而只有一條是西安市，MySQL底層決議器在執行計劃的時候，12條記錄11條是北京市，那還不如全表掃描來的更快，
如果資料量比較大，而某一條資料占用的比例特別大，基本上覆寫了所有的比例，這時候就不會再走索引了，而走全表掃描，

10). is NULL ， is NOT NULL 有時索引失效

此時我們已經為address欄位創建了單列索引，操作的時候我們以address為例，

mysql> explain select * from tb_seller where address is null;
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table     | partitions | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | tb_seller | NULL       | ref  | idx_seller_address | idx_seller_address | 403     | const |    1 |   100.00 | Using index condition |
+----+-------------+-----------+------------+------+--------------------+--------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from tb_seller where address is not null;
+----+-------------+-----------+------------+------+--------------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys      | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+--------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_seller | NULL       | ALL  | idx_seller_address | NULL | NULL    | NULL |   12 |   100.00 | Using where |
+----+-------------+-----------+------------+------+--------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

is null走了索引，is not null沒有走索引，而我們tb_seller表中，所有的值都不為空，說明值為空是少量的資料（也包括無），此時就走索引，而is not null走索引，
因為MySQL底層會自動判斷，使用索引有沒有必要，

11). in 走索引， not in 索引失效

mysql> explain select * from tb_seller where sellerid in('oppo','xiaomi','sina');
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_seller | NULL       | range | PRIMARY       | PRIMARY | 402     | NULL |    3 |   100.00 | Using where |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from tb_seller where sellerid not in('oppo','xiaomi','sina');
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_seller | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL |   12 |    83.33 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

12). 單列索引和復合索引

盡量使用復合索引，而少使用單列索引 ，

創建復合索引

create index idx_name_sta_address on tb_seller(name, status, address);

就相當于創建了三個索引 ： 
	name
	name + status
	name + status + address

創建單列索引

create index idx_seller_name on tb_seller(name);
create index idx_seller_status on tb_seller(status);
create index idx_seller_address on tb_seller(address);

資料庫會選擇一個最優的索引（辨識度最高索引）來使用，并不會使用全部索引，

我們先刪去原來的復合索引，然后再給原來的欄位添加三個單列索引

mysql> explain select * from tb_seller where name='小米科技' and status='0' and address='西安市';
+----+-------------+-----------+------------+------+---------------------------------------------+--------------------------+---------+-------------------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys                               | key                      | key_len | ref               | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------------------------------------+--------------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | tb_seller | NULL       | ref  | idx_seller_name_sta_addr,idx_seller_address | idx_seller_name_sta_addr | 813     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------------------------------------+--------------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> drop index idx_seller_name_sta_addr on tb_seller;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index idx_seller_name on tb_seller(name);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index idx_seller_status on tb_seller(status);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index idx_seller_address on tb_seller(address);
ERROR 1061 (42000): Duplicate key name 'idx_seller_address'

mysql> show index from tb_seller;
+-----------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name           | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tb_seller |          0 | PRIMARY            |            1 | sellerid    | A         |          11 |     NULL | NULL   |      | BTREE      |         |               |
| tb_seller |          1 | idx_seller_address |            1 | address     | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
| tb_seller |          1 | idx_seller_name    |            1 | name        | A         |          12 |     NULL | NULL   | YES  | BTREE      |         |               |
| tb_seller |          1 | idx_seller_status  |            1 | status      | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |               |
+-----------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.01 sec)

我們再來執行同樣的陳述句

mysql>  explain select * from tb_seller where name='小米科技' and status='0' and address='西安市';
+----+-------------+-----------+------------+------+------------------------------------------------------+--------------------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys                                        | key                | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+------------------------------------------------------+--------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | tb_seller | NULL       | ref  | idx_seller_address,idx_seller_name,idx_seller_status | idx_seller_address | 403     | const |    1 |     8.33 | Using where |
+----+-------------+-----------+------------+------+------------------------------------------------------+--------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

三個單列索引都有可能會用到，但實際上只用到了name這個欄位的索引，因為資料庫會選擇一個最優的索引（辨識度最高索引）來使用，并不會使用全部索引，這里面name欄位辨識度最高，因為小米科技這個值只出現了一次，在tb_seller表中，

3 查看索引使用情況

show status like 'Handler_read%';	

show global status like 'Handler_read%';

mysql> show status like 'Handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 8     |
| Handler_read_key      | 10    |
| Handler_read_last     | 0     |
| Handler_read_next     | 13    |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 117   |
+-----------------------+-------+
7 rows in set (0.03 sec)

mysql> show global status like 'Handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 16    |
| Handler_read_key      | 16    |
| Handler_read_last     | 0     |
| Handler_read_next     | 15    |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 859   |
+-----------------------+-------+
7 rows in set (0.01 sec)

Handler_read_first：索引中第一條被讀的次數，如果較高，表示服務器正執行大量全索引掃描（這個值越低越好），

Handler_read_key：如果索引正在作業，這個值代表一個行被索引值讀的次數，如果值越低，表示索引得到的性能改善不高，因為索引不經常使用（這個值越高越好），

Handler_read_next ：按照鍵順序讀下一行的請求數，如果你用范圍約束或如果執行索引掃描來查詢索引列，該值增加，

Handler_read_prev：按照鍵順序讀前一行的請求數，該讀方法主要用于優化ORDER BY ... DESC，

Handler_read_rnd ：根據固定位置讀一行的請求數，如果你正執行大量查詢并需要對結果進行排序該值較高，你可能使用了大量需要MySQL掃描整個表的查詢或你的連接沒有正確使用鍵，這個值較高，意味著運行效率低，應該建立索引來補救，

Handler_read_rnd_next：在資料檔案中讀下一行的請求數，如果你正進行大量的表掃描，該值較高，通常說明你的表索引不正確或寫入的查詢沒有利用索引，

轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/252107.html

標籤：其他

上一篇：nacos1.2的下載及CentOS7下安裝nacos1.2

下一篇：2021.1.24

MySQL高級-索引的使用及優化