MySQL索引深入剖析-索引使用原则、索引创建

以你之姓@ 2022-11-05 08:39 282阅读 0赞

## 索引使用原则 ##

在使用索引的时候容易有一个误区，就是在经常使用的查询条件上都建立索引，索引越多越好，那到底是不是这样呢？  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25pdWdhbmcwOTIw_size_16_color_FFFFFF_t_70_pic_center]

### 列的离散（sàn） 度 ###

第一个叫做列的离散度，我们先来看一下列的离散度的公式：

count(distinct(column\_name)) : count(\*)，列的全部不同值和所有数据行的比例。数据行数相同的情况下，分子越大，列的离散度就越高。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25pdWdhbmcwOTIw_size_16_color_FFFFFF_t_70_pic_center 1]  
简单来说，如果列的重复值越多，离散度就越低，重复值越少，离散度就越高

在 name 上面建立索引和在 gender 上面建立索引有什么区别

在 gender 上建立的索引去检索数据的时候，由于重复值太多，需要扫描的行数就更多。例如，我们现在在 gender 列上面创建一个索引，然后看一下执行计划。

ALTER TABLE user_innodb DROP INDEX idx_user_gender;
    ALTER TABLE user_innodb ADD INDEX idx_user_gender (gender); -- 耗时比较久
    EXPLAIN SELECT * FROM `user_innodb` WHERE gender = 0;

![在这里插入图片描述][20210304193014316.png_pic_center]

show indexes from user_innodb;

而 name 的离散度更高，比如“青山”的这名字，只需要扫描一行。

ALTER TABLE user_innodb DROP INDEX idx_user_name;
    ALTER TABLE user_innodb ADD INDEX idx_user_name (name);
    EXPLAIN SELECT * FROM `user_innodb` WHERE name = '青山';

![在这里插入图片描述][20210304193320424.png_pic_center]  
![在这里插入图片描述][20210304193335604.png_pic_center]  
查看表上的索引，\*\*Cardinality \[kɑ:dɪ’nælɪtɪ\] 代表基数，代表预估的不重复的值的数量。索引的基数与表总行数越接近，列的离散度就越高。 \*\*  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25pdWdhbmcwOTIw_size_16_color_FFFFFF_t_70_pic_center 2]  
如果在 B+Tree 里面的重复值太多，MySQL 的优化器发现走索引跟使用全表扫描差不了多少的时候，就算建了索引，也不一定会走索引。  
https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html  
![在这里插入图片描述][20210304193411966.png_pic_center]  
这个给我们的启发是什么？建立索引，要使用离散度（选择度）更高的字段

### 联合索引最左匹配 ###

前面说的都是针对单列创建的索引，但有的时候我们的多条件查询的时候，也会建立联合索引。单列索引可以看成是特殊的联合索引

比如在 user 表上面，给 name 和 phone 建立了一个联合索引

ALTER TABLE user_innodb DROP INDEX comidx_name_phone;
    ALTER TABLE user_innodb add INDEX comidx_name_phone (name,phone);

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25pdWdhbmcwOTIw_size_16_color_FFFFFF_t_70_pic_center 3]  
联合索引在 B+Tree 中是复合的数据结构，它是按照从左到右的顺序来建立搜索树的（name 在左边，phone 在右边）

从这张图可以看出来，**name 是有序的，phone 是无序的**。当 name 相等的时候，phone 才是有序的。

这个时候我们使用 where name=‘青山’ and phone = '136xx '去查询数据的时候，B+Tree 会优先比较 name 来确定下一步应该搜索的方向，往左还是往右。如果 name相同的时候再比较 phone。但是如果查询条件没有 name，就不知道第一步应该查哪个节点，因为建立搜索树的时候 name 是第一个比较因子，所以用不到索引。

#### 什么时候用到联合索引 ####

所以，我们在建立联合索引的时候，一定要把最常用的列放在最左边。  
比如下面的三条语句，能用到联合索引吗？

1）使用两个字段，可以用到联合索引：

EXPLAIN SELECT * FROM user_innodb WHERE name= '权亮' AND phone = '15204661800';

![在这里插入图片描述][20210304193458533.png_pic_center]  
2）使用左边的 name 字段，可以用到联合索引：

EXPLAIN SELECT * FROM user_innodb WHERE name= '权亮

![在这里插入图片描述][20210304193518360.png_pic_center]  
3）使用右边的 phone 字段，无法使用索引，全表扫描：

EXPLAIN SELECT * FROM user_innodb WHERE phone = '15204661800'

![在这里插入图片描述][20210304193537399.png_pic_center]

#### 如何创建联合索引 ####

有一天我我项目里面有两个查询很慢。

SELECT * FROM user_innodb WHERE name= ? AND phone = ?;
    SELECT * FROM user_innodb WHERE name= ?;

按照想法，一个查询创建一个索引，所以针对这两条 SQL 创建了两个索引，这种做法觉得正确吗？

CREATE INDEX idx_name on user_innodb(name);
    CREATE INDEX idx_name_phone on user_innodb(name,phone);

当创建一个联合索引的时候，按照最左匹配原则，用左边的字段 name 去查询的时候，也能用到索引，所以第一个索引完全没必要。  
相当于建立了两个联合索引(name),(name,phone)。  
如果创建三个字段的索引 index(a,b,c)，相当于创建三个索引：

index(a)

index(a,b)  
index(a,b,c)

用 where b=? 和 where b=? and c=? 和 where a=? and c=?是不能使用到索引的。不能不用第一个字段，不能中断。

这里就是 MySQL 联合索引的最左匹配原则。

### 覆盖索引 ###

回表：

非主键索引，先通过索引找到主键索引的键值，再通过主键值查出索引里面没有的数据，它比基于主键索引的查询多扫描了一棵索引树，这个过程就叫回表。

例如：select \* from user\_innodb where name = ‘青山’;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25pdWdhbmcwOTIw_size_16_color_FFFFFF_t_70_pic_center 4]  
\*\*在辅助索引里面，不管是单列索引还是联合索引，如果 select 的数据列只用从索引中就能够取得，不必从数据区中读取，这时候使用的索引就叫做覆盖索引，这样就避免了回表。 \*\*

先来创建一个联合索引：

-- 创建联合索引
    ALTER TABLE user_innodb DROP INDEX comixd_name_phone;
    ALTER TABLE user_innodb add INDEX `comixd_name_phone` (`name`,`phone`)

这三个查询语句都用到了覆盖索引：

EXPLAIN SELECT name,phone FROM user_innodb WHERE name= '青山' AND phone = ' 13666666666';
    EXPLAIN SELECT name FROM user_innodb WHERE name= '青山' AND phone = ' 13666666666';
    EXPLAIN SELECT phone FROM user_innodb WHERE name= '青山' AND phone = ' 13666666666';

Extra 里面值为“Using index”代表使用了覆盖索引

![在这里插入图片描述][20210304193629252.png_pic_center]  
select \* ，用不到覆盖索引。

很明显，因为覆盖索引减少了 IO 次数，减少了数据的访问量，可以大大地提升查询效率。

### 索引条件下推（ICP） ###

CREATE TABLE `employees` (
    `emp_no` int(11) NOT NULL,
    `birth_date` date NULL,
    `first_name` varchar(14) NOT NULL,
    `last_name` varchar(16) NOT NULL,
    `gender` enum('M','F') NOT NULL,
    `hire_date` date NULL,
    PRIMARY KEY (`emp_no`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    alter table employees add index idx_lastname_firstname(last_name,first_name);
    INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (1,
    NULL, '698', 'liu', 'F', NULL);
    INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (2,
    NULL, 'd99', 'zheng', 'F', NULL);
    INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (3,
    NULL, 'e08', 'huang', 'F', NULL);
    INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (4,
    NULL, '59d', 'lu', 'F', NULL);
    INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (5,
    NULL, '0dc', 'yu', 'F', NULL);
    INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (6,
    NULL, '989', 'wang', 'F', NULL);
    INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (7,
    NULL, 'e38', 'wang', 'F', NULL);
    INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (8,
    NULL, '0zi', 'wang', 'F', NULL);
    INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (9,
    NULL, 'dc9', 'xie', 'F', NULL);
    INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (10,
    NULL, '5ba', 'zhou', 'F', NULL);

https://dev.mysql.com/doc/refman/5.7/en/index-condition-pushdown-optimization.html

关闭 ICP：

set optimizer_switch='index_condition_pushdown=off';

查看参数：

show variables like 'optimizer_switch';

现在要查询所有姓 wang，并且名字最后一个字是 zi 的员工，比如王胖子，王瘦子。查询的 SQL：

select * from employees where last_name='wang' and first_name LIKE '%zi' ;

这条 SQL 有两种执行方式：  
1、根据联合索引查出所有姓 wang 的二级索引数据，然后回表，到主键索引上查询全部符合条件的数据（3 条数据）。然后返回给 Server 层，在 Server 层过滤出名字以zi 结尾的员工。

2、根据联合索引查出所有姓 wang 的二级索引数据（3 个索引），然后从二级索引中筛选出 first\_name 以 zi 结尾的索引（1 个索引），然后再回表，到主键索引上查询全部符合条件的数据（1 条数据），返回给 Server 层。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25pdWdhbmcwOTIw_size_16_color_FFFFFF_t_70_pic_center 5]  
很明显，第二种方式到主键索引上查询的数据更少。

注意，索引的比较是在存储引擎进行的，数据记录的比较，是在 Server 层进行的。而当 first\_name 的条件不能用于索引过滤时，Server 层不会把 first\_name 的条件传递给存储引擎，所以读取了两条没有必要的记录。

这时候，如果满足 last\_name='wang’的记录有 100000 条，就会有 99999 条没有必要读取的记录

执行以下 SQL，Using where：

explain select * from employees where last_name='wang' and first_name LIKE '%zi' ;

![在这里插入图片描述][20210304193717910.png_pic_center]  
Using Where 代表从存储引擎取回的数据不全部满足条件，需要在 Server 层过滤。

先用 last\_name 条件进行索引范围扫描，读取数据表记录，然后进行比较，检查是否符合 first\_name LIKE ‘%zi’ 的条件。此时 3 条中只有 1 条符合条件

开启 ICP：

set optimizer_switch='index_condition_pushdown=on';

此时的执行计划，Using index condition：  
![在这里插入图片描述][20210304193740580.png_pic_center]  
把 first\_name LIKE '%zi’下推给存储引擎后，只会从数据表读取所需的 1 条记录

索引条件下推（Index Condition Pushdown），5.6 以后完善的功能。只适用于二级索引。ICP 的目标是减少访问表的完整行的读数量从而减少 I/O 操作。

## 索引的创建与使用 ##

因为索引对于改善查询性能的作用是巨大的，所以我们的目标是尽量使用索引

### 索引的创建 ###

1、在用于 where 判断 order 排序和 join 的（on）字段上创建索引  
2、索引的个数不要过多。

> 浪费空间，更新变慢。

3、区分度低的字段，例如性别，不要建索引。

> 离散度太低，导致扫描行数过多。

4、频繁更新的值，不要作为主键或者索引。

> 页分裂

5、组合索引把散列性高（区分度高）的值放在前面。  
6、创建复合索引，而不是修改单列索引。  
7、过长的字段，怎么建立索引？  
8、为什么不建议用无序的值（例如身份证、UUID ）作为索引？

### 什么时候用不到索引？ ###

1、索引列上使用函数（replace\\SUBSTR\\CONCAT\\sum count avg）、表达式、计算（+ - \* /）：

explain SELECT * FROM `t2` where id+1 = 4;

2、字符串不加引号，出现隐式转换

ALTER TABLE user_innodb DROP INDEX comidx_name_phone;
    ALTER TABLE user_innodb add INDEX comidx_name_phone (name,phone);

explain SELECT * FROM `user_innodb` where name = 136;
    explain SELECT * FROM `user_innodb` where name = '136';

3、like 条件中前面带%

where 条件中 like abc%，like %2673%，like %888 都用不到索引吗？为什么？

explain select *from user_innodb where name like 'wang%';
    explain select *from user_innodb where name like '%wang';

过滤的开销太大，所以无法使用索引。这个时候可以用全文索引。

4、负向查询  
NOT LIKE 不能：

explain select *from employees where last_name not like 'wang

!= （<>）和 NOT IN 在某些情况下可以：

explain select *from employees where emp_no not in (1)
    explain select *from employees where emp_no <> 1

注意一个 SQL 语句是否使用索引，跟数据库版本、数据量、数据选择度都有关系。  
其实，用不用索引，最终都是优化器说了算。  
优化器是基于什么的优化器？  
基于 cost 开销（Cost Base Optimizer），它不是基于规则（Rule-Based Optimizer），  
也不是基于语义。怎么样开销小就怎么来。

https://docs.oracle.com/cd/B10501\_01/server.920/a96533/rbo.htm\#38960  
https://dev.mysql.com/doc/refman/5.7/en/cost-model.html  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25pdWdhbmcwOTIw_size_16_color_FFFFFF_t_70]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25pdWdhbmcwOTIw_size_16_color_FFFFFF_t_70_pic_center]: /images/20221023/3189e49ca46b4f66addf50409574d4b5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25pdWdhbmcwOTIw_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221023/5f3fd33828634266a5403813d3e75c74.png
[20210304193014316.png_pic_center]: /images/20221023/fe1651a155d8402bb93f28c204e89bd8.png
[20210304193320424.png_pic_center]: /images/20221023/84a2c5a3e8544dd5982eef267c5b4f40.png
[20210304193335604.png_pic_center]: /images/20221023/a06ac833ed5543ad8289f2250d120653.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25pdWdhbmcwOTIw_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221023/a6e15615171b47aebb0e3332452122cd.png
[20210304193411966.png_pic_center]: /images/20221023/ce3c219267754855ba7e19f0d2fdb826.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25pdWdhbmcwOTIw_size_16_color_FFFFFF_t_70_pic_center 3]: /images/20221023/d8737e5a47554d4eb47de5c49d011b7d.png
[20210304193458533.png_pic_center]: /images/20221023/572709e4e0b34042885d7899885891cd.png
[20210304193518360.png_pic_center]: /images/20221023/0cb1436b8f3846e4a71cd056d183479c.png
[20210304193537399.png_pic_center]: /images/20221023/543452299384401f8bf31afc90e9c15a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25pdWdhbmcwOTIw_size_16_color_FFFFFF_t_70_pic_center 4]: /images/20221023/9ab95c75b16e40cc82cdbbd70e870aff.png
[20210304193629252.png_pic_center]: /images/20221023/5cd15eee45ee415f935fa35fb4189f26.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25pdWdhbmcwOTIw_size_16_color_FFFFFF_t_70_pic_center 5]: /images/20221023/09a449394296405a9905bef223e799ba.png
[20210304193717910.png_pic_center]: /images/20221023/4f22fcfc9f954492a7b2a2f8478b6801.png
[20210304193740580.png_pic_center]: /images/20221023/f99c8f986fbd44eb8ea2d3a0cdb3c5b5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25pdWdhbmcwOTIw_size_16_color_FFFFFF_t_70]: /images/20221023/4006fd34b3d8418b9759e3ef96f4df18.png