数据库-深度剖析mysql索引原理（下）（四）

目录

一、索引使用原则

1、列的离散（sàn）度

2、联合索引最左匹配

3、什么时候用到联合索引？

4、如何创建联合索引

5、索引覆盖

6、索引条件下推（ICP）

7、前缀索引

8、索引类型

二、索引的创建与使用

三、什么时候用不到索引？

四、优化方案

五、注意

---

一、索引使用原则

我们容易有以一个误区，就是在经常使用的查询条件上都建立索引，索引越多越好， 那到底是不是这样呢？

1、列的离散（sàn）度

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

count(distinct(column_name)) : count(*)，列的全部不同值和所有数据行的比例。 数据行数相同的情况下，分子越大，列的离散度就越高。

简单来说，如果列的重复值越多，离散度就越低，重复值越少，离散度就越高。

 了解了离散度的概念之后，我们再来思考一个问题，我们在 name 上面建立索引和 在age上面建立索引有什么区别。

如果在 B+Tree 里面的重复值太多，MySQL 的优化器发现走索引跟使用全表扫描差 不了多少的时候，就算建了索引，也不一定会走索引。

这个给我们的启发是什么？

建立索引，要使用离散度（选择度）更高的字段

2、联合索引最左匹配

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

 ALTER TABLE user add INDEX comidx_name_age (name,age);

联合索引在 B+Tree 中是复合的数据结构，它是按照从左到右的顺序来建立搜索树的 （name 在左边，age 在右边）。 从这张图可以看出来，name 是有序的，age 是无序的。当 name 相等的时候， age 才是有序的。

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

3、什么时候用到联合索引？

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

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

EXPLAIN SELECT * FROM user WHERE name= 'zhangsan' and age = '20 ';

2、使用左边的 name 字段，可以用到联合索引：

EXPLAIN SELECT * FROM user WHERE name= 'zhangsan'

3、使用右边的 age 字段，无法使用索引，全表扫描：

EXPLAIN SELECT * FROM user_innodb WHERE age = '20 '

4、如何创建联合索引

有一天我们的 DBA 找到我，说我们的项目里面有两个查询很慢。

SELECT * FROM user WHERE name= ? AND age= ?;

SELECT * FROM user WHERE name= ?;

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

CREATE INDEX idx_name on user(name);

CREATE INDEX idx_name_age on user(name,age)

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

如果我们创建三个字段的索引 index(a,b,c)，相当于创建三个索引：

index(a)

index(a,b)

index(a,b,c)

注意查询时候：

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

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

5、索引覆盖

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

例如：select * from user where name = 'zhangsan';

在辅助索引里面，不管是单列索引还是联合索引，如果 select 的数据列只用从索引 中就能够取得，不必从数据区中读取，这时候使用的索引就叫做覆盖索引，这样就避免 了回表。

我们先来创建一个联合索引：

-- 创建联合索引

ALTER TABLE user DROP INDEX comixd_name_age;

ALTER TABLE user add INDEX `comixd_name_age` (`name`,`age`);

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

EXPLAIN SELECT name,age FROM user WHERE name= 'aa' AND age= ' 12';

EXPLAIN SELECT name FROM user WHERE name= 'aa' AND age= ' 12';

EXPLAIN SELECT age FROM user WHERE name= 'aa' AND age= ' 12';

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

 注意：覆盖索引是查询得属性只能是索引对应的字段。

select * ，用不到覆盖索引

如果改成只用 where age= 查询呢？动手试试？

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

6、索引条件下推（ICP）

再来看这么一张表，在 name 和 age上面创建联合索引

select * from user 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 层。

索引下推，就是在第一个条件，过滤一次，得到二级索引数据，然后我们不回表，我们直接在这个二级索引数据中直接用我们第二个条件在去过滤，缩小数据范围，然后再去主键索引去中去检索我们值

很明显，第二种方式到主键索引上查询的数据更少

，索引的比较是在存储引擎进行的，数据记录的比较是在 Server 层进行的

而当 first_name 的条件不能用于索引过滤时，Server 层不会把 first_name 的条件传递 给存储引擎，所以读取了两条没有必要的记录。 这时候，如果满足 last_name='wang'的记录有 100000 条，就会有 99999 条没有 必要读取的记录。

Using Where 代表从存储引擎取回的数据不全部满足条件，需要在 Server 层过滤。 先用 last_name 条件进行索引范围扫描，读取数据表记录，然后进行比较，检查是 否符合 first_name LIKE '%zi' 的条件。此时 3 条中只有 1 条符合条件。

开启 ICP：

set optimizer_switch='index_condition_pushdown=on';

把 first_name LIKE '%zi'下推给存储引擎后，只会从数据表读取所需的 1 条记录

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

7、前缀索引

当字段值比较长的时候，建立索引会消耗很多的空间，搜索起来也会很慢。我们可 以通过截取字段的前面一部分内容建立索引，这个就叫前缀索引。

创建一张商户表，因为地址字段比较长，在地址字段上建立前缀索引：

create table shop(address varchar(120) not null);

alter table shop add key (address(12));

问题是，截取多少呢？截取得多了，达不到节省索引存储空间的目的，截取得少了， 重复内容太多，字段的散列度（选择性）会降低。怎么计算不同的长度的选择性呢？

先看一下字段在全部数据中的选择度（离散度）：

select count(distinct address) / count(*) 

通过不同长度去计算，与全表的选择性对比：

select count(distinct left(address,10))/count(*) as sub10,

count(distinct left(address,11))/count(*) as sub11,

count(distinct left(address,12))/count(*) as sub12,

count(distinct left(address,13))/count(*) as sub13

from shop;

只要截取前 13 个字段，就已经有比较高的选择性了（这里的数据只是举例

8、索引类型

单列

• 索引前缀
• 全文索引
• 空间索引
• 内存存储引擎中的索引

多列索引

联合索引

二、索引的创建与使用

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

索引的创建

1. 在用于 where 判断 order 排序和 join 的（on）字段上创建索引
2. 索引的个数不要过多。——浪费空间，更新变慢。
3. 区分度低的字段，例如性别，不要建索引。——离散度太低，导致扫描行数过多。
4. 频繁更新的值，不要作为主键或者索引。——页分裂
5. 组合索引把散列性高（区分度高）的值放在前面。
6. 创建复合索引，而不是修改单列索引
7. 过长的字段，怎么建立索引？

前缀索引

1. 为什么不建议用无序的值（例如身份证、UUID ）作为索引？

索引查找比较以及排序比较耗时

三、什么时候用不到索引？

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

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

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

ALTER TABLE user DROP INDEX comidx_name_age;

ALTER TABLE user add INDEX comidx_name_age (name,age);

explain SELECT * FROM `user` where name = 136;

explain SELECT * FROM `userb` 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 不能：

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

注意一个 SQL 语句是否使用索引，跟数据库版本、数据量、数据选择度都有关系

其实，用不用索引，最终都是优化器说了算。 优化器是基于什么的优化器？ 基于 cost 开销（Cost Base Optimizer），它不是基于规则（Rule-Based Optimizer）， 也不是基于语义。怎么样开销小就怎么来。

四、优化方案

1. 建立索引，要使用离散度（选择度）更高的字段，也就是重复值比较少的字段
2. 我们在建立联合索引的时候，一定要把最常用的列放在最左边，因为后面的可能不走索引，不执行。注意：用 where b=? 和 where b=? and c=? 和 where a=? and c=?是不能使用到索引 的。不能不用第一个字段，不能中断。 
3. select * ，用不到覆盖索引，覆盖索引就是查询的值在索引节点上，不用去数据区读取，就叫做覆盖索引，避免的回表。
4. 使用联合索引时候，可以开启索引条件下推（5.6以后完善的功能）：索引下推，就是在第一个条件，过滤一次，得到二级索引数据，然后我们不回表，我们直接在这个二级索引数据中直接用我们第二个调价在去过滤，缩小数据范围，然后再去主键索引去中去检索我们值。
5. 针对字段数据比较长，要加索引的时候比如地址，我们可以采用前缀索引。

五、注意

1、单列索引查询不存在数据，不走索引

2、联合索引查询不存在数据，走索引

数据库-深度剖析mysql索引原理（上）（三）_平凡之路无尽路的博客-CSDN博客