查找最频繁出现的城市前缀，先从 5 字符前缀开始，发现比原来出现的次数更多，可以分别截取多个字符，查看城市出现的次数频率

mysql> select count(*) as cnt,left(city,5) as pref from citydemo group by pref order by cnt desc limit 10;
+-----+-------+
| cnt | pref  |
+-----+-------+
| 115 | South |
|  97 | Santa |
|  80 | Saint |
|  75 | Londo |
|  75 | Valle |
|  69 | San F |
|  69 | al-Qa |
|  67 | Shimo |
|  67 | Xiang |
|  63 | Chang |
+-----+-------+
10 rows in set (0.02 sec)
mysql> select count(*) as cnt,left(city,6) as pref from citydemo group by pref order by cnt desc limit 10;
+-----+--------+
| cnt | pref   |
+-----+--------+
|  97 | Santa  |
|  75 | London |
|  75 | Valle  |
|  69 | San Fe |
|  53 | Santia |
|  50 | Hanoi  |
|  48 | Deba H |
|  48 | La Pla |
|  46 | Saint  |
|  46 | Crdoba |
+-----+--------+
10 rows in set (0.02 sec)
mysql> select count(*) as cnt,left(city,7) as pref from citydemo group by pref order by cnt desc limit 10;
+-----+---------+
| cnt | pref    |
+-----+---------+
|  75 | Valle d |
|  75 | London  |
|  69 | San Fel |
|  53 | Santiag |
|  50 | Hanoi   |
|  48 | Deba Ha |
|  48 | La Plat |
|  46 | Bucures |
|  46 | Saint L |
|  46 | Crdoba  |
+-----+---------+
10 rows in set (0.02 sec)
mysql> select count(*) as cnt,left(city,8) as pref from citydemo group by pref order by cnt desc limit 10;
+-----+----------+
| cnt | pref     |
+-----+----------+
|  75 | Valle de |
|  75 | London   |
|  69 | San Feli |
|  53 | Santiago |
|  50 | Hanoi    |
|  48 | Deba Hab |
|  48 | La Plata |
|  46 | Bucurest |
|  46 | Saint Lo |
|  46 | Crdoba   |
+-----+----------+
10 rows in set (0.02 sec)

通过上述查询结果，可以发现，当前缀=7 时，前缀的选择性接近于完整列的选择性，只要比对它的 cnt 是否还有继续发生变化即可.

第二种方式有时并不那么准确能够计算出前缀，可以通过这种方式来进行判断，识别它的选择性占比率，如下：

mysql> select count(distinct left(city,3))/count(*) as sel3,    -> count(distinct left(city,4))/count(*) as sel4,    -> count(distinct left(city,5))/count(*) as sel5,
    -> count(distinct left(city,6))/count(*) as sel6,
    -> count(distinct left(city,7))/count(*) as sel7,
    -> count(distinct left(city,8))/count(*) as sel8 
    -> from citydemo;
+--------+--------+--------+--------+--------+--------+
| sel3   | sel4   | sel5   | sel6   | sel7   | sel8   |
+--------+--------+--------+--------+--------+--------+
| 0.0239 | 0.0293 | 0.0305 | 0.0309 | 0.0310 | 0.0310 |
+--------+--------+--------+--------+--------+--------+

因此，可以使用字符串前缀=7 来创建索引：

alter table citydemo add key(city(7));

此处创建好以后，当使用 city 索引列进行条件查询时会发现效率可以极大提升

注意：前缀索引是一种能够使索引更小更快的有效方法，但也有缺点：MySQL 无法使用前缀索引作 order by、group by

索引扫描排序

使用索引扫描作排序，MySQL 有两种方式可以生成有序的结果，通过排序操作或索引顺序进行扫描 > 若 explain 出来的 type=index，则说明 MySQL 使用了索引扫描来进行了排序

扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接着的下一条记录，但索引不能覆盖查询所需要的全部列，那么就不得不扫描一条索引记录就得回表查询一次对应的行数据；基础上都是随机 IO，因此按索引顺序读取数据的速度要比顺序扫描全表慢

MySQL 可以使用同一个索引既能满足排序，又可以用于查找行；若有可能的话，设计索引时应当尽可能地同时满足这两项任务

当索引列顺序跟 order by 子句顺序完全一致，并且所有列的顺序方式都一样的话，MySQL 才能使用索引来对结果进行排序；若查询需要关联多张表，则只有当 order by 子句引用的字段全部为第一张表时，才能使用索引作排序；order by 子句与查询的限制是一样的，要满足索引的最左匹配原则，否则，MySQL 都需要执行顺序操作，无法使用索引排序

举例如下，使用 sakila > rental 表 > rentail_data、inventory_id、customer_id 列上索引名：rentail_data

UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`)

使用 rental_data 索引为下面的查询作排序

mysql> explain select rental_id,staff_id from rental where rental_date='2005-05-25' order by inventory_id,customer_id;
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | rental | NULL       | ref  | rental_date   | rental_date | 5       | const |    1 |   100.00 | Using index condition |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+

如下查询不会触发索引排序，因为 rental_data 被重复使用了，一般 where、order by 是组合使用的

mysql> explain select rental_id,staff_id from rental where rental_date>'2005-05-25' order by rental_date,inventory_id;+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                       |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
|  1 | SIMPLE      | rental | NULL       | ALL  | rental_date   | NULL | NULL    | NULL | 16008 |    50.00 | Using where; Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+

如下查询使用了两种不同的排序方向 > 升序、降序，rental_data 使用了范围查询

mysql> explain select rental_id,staff_id from rental where rental_date>'2005-05-25' order by inventory_id desc,customer_id asc;
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                       |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
|  1 | SIMPLE      | rental | NULL       | ALL  | rental_date   | NULL | NULL    | NULL | 16008 |    50.00 | Using where; Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+

如下查询中引用了一个非索引列

mysql> explain select rental_id,staff_id from rental where rental_date>'2005-05-25' order by inventory_id,staff_id;
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                       |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
|  1 | SIMPLE      | rental | NULL       | ALL  | rental_date   | NULL | NULL    | NULL | 16008 |    50.00 | Using where; Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+

子查询

union all、in、or，推荐使用 in 关键字子查询，如下：

可以看到执行 in 时效率是较高的，当然这个没有绝对，要根据实际的执行情况来进行判断，绝大部分情况 in 是比较节省时间的，所以推荐使用 in 方式

or 关键字有时候会引起索引失效，会造成扫描表中大部分无效的行数据，比如： where a = x or b =y；要是 a、b 两列都加了索引，b 索引列就无法使用到，当表数据量增大时，这条 SQL 会造成扫描的条数据飙升，从而导致引发慢 SQL 查询

范围列查询

范围列可以使用索引，当使用范围列可以进行索引的匹配，但是范围列后面的列就无法用到索引，索引最多用于一个范围列

在创建复合、组合索引时，要结合所有的 SQL 一起观察，有出现列是范围查询的，最好将它放到最后面，以避免那些常量值的索引列无法使用索引去加快查询

强制类型转换

强制类型转换会触发全表扫描

create table user(id int,name varchar(10),phone varchar(11));
alter table user add index idx_phone(phone);

使用强转前、强转后作比对，如下：

mysql> explain select * from user where phone = '15980212312';
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ref  | idx_phone     | idx_phone | 36      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from user where phone = 15980212312;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | idx_phone     | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

通过对比可以发现，当发生类型转换以后会导致索引失效，所以尽量确保索引的类型

建立索引

更新十分频繁，数据区分度不高的字段上不宜建立索引

更新会变更 B+ 树，更新频繁的字段上建立索引会大大降低数据库的性能
类似于性别这种区分不大的字段，建立索引是没有任何意义的，不能有效的过滤数据
一般区分度在 80% 以上的就可以建立索引，区分度可以使用 count(distinct(列名))/count(*) 来计算

创建索引的列，不允许为 null，查询条件也不能为 null，否则会得到不符合预期的结果，造成 SQL 执行效率极速下降

区分度不高也有例外，一般在业务重试表，对业务消息进行重试时，一般会将需要重试的消息查询出来，进行重试，通过对消息的处理状态列 + 索引，然后结合 LIMIT 限制行数据，可以提高这部分的执行效率

JOIN 表

通过表 Join 连接时，最好不要超过三张表，因为需要 join 字段，数据类型必须保持一致 > 来自于阿里云编码规范；因为在进行多表联查时会造成查询较慢，小表（表数据比较少）JOIN 大表（表数据比较多）效率会相当高

MySQL 提供了三种 JOIN 算法，如下：

Simple Nested-Loop Join：每次把第一张表里面的数据行记录取出来，然后再去匹配第二张的每行记录

以上那种方式是一行一行去匹配，这种方式效率比较低，所以一般情况下不推荐使用这种方式
Index Nested-Loop Join：这种方式是使用表中的索引进行相关的匹配操作

1、要求匹配表 S 上有索引，可以通过索引来减少比较次数，加速查询

2、在查询时，驱动表 R 会通过关联字段的索引进行查找，当在索引上找到符合的值，再回表的进行查询，也就是只有当匹配到索引以后才会进行回表查询

3、若匹配表 S 关联键是主键的话，性能会非常高，若不是主键，要进行多次回表查询，先关联索引，然后通过二级索引的主键 ID 去进行回表操作，性能上比索引是主键要慢

Block Nested-Loop Join：表示每次查询时将 R 驱动表里面的一些数据优先放入到内存中，然后通过从内存中获取数据来进行匹配操作

若有索引，会选取第二种方式进行 JOIN，若 JOIN 列没有索引，就会采用 Block Nested-Loop Join，可以看到中间有个 JOIN BUFFER 缓冲区，将 R 驱动表的所有 JOIN 相关的列都先缓存到 JOIN BUFFER 中，然后批量与匹配表 S 进行匹配，将第一种的方式处理的多次合并为一次，降低了匹配表 S 访问频率；默认情况下 join_buffer_size=256k，查找时 MySQL 会将所有需要的列缓存到 JOIN BUFFER 当中，包括 SELECT 查询列，而不是仅仅只缓存关联列；在有 N 个 JOIN 关联 SQL，会在执行时分配 N-1 个 JOIN BUFFER
在使用第三种方式时，会消耗内存，所以在使用时有以下需要注意的点，如下：

1、JOIN BUFFER 会缓存所有参与查询的列而不是只有 JOIN 列，所以在查询时指定你需要查询的列，而不是 SELECT *

2、可以调整 join_buffer_size 缓存大小

3、join_buffer_size 默认值为 256K，join_buffer_size 最大值在 MySQL 5.1.22 版本前是 4G-1，而之后的版本在 64 位操作系统下申请大于 4G JOIN BUFFER 空间

4、使用 Block Nested-Loop Join 算法需要开启优化器管理配置，optimizer_switch > block_nested_loop=ON，默认是开启的

LIMIT 限制输出行

LIMIT：主要用来限制输出的行数据，在进行一系列 SQL 调优步骤后，其实最核心的就是减少数据 IO 量，因此在很多场景下能使用 LIMIT 尽量使用 LIMIT，这样能保证返回的数据量最少，数据量少了，查询数据的效率才会有提升

单表索引数量

单表索引的数量建议在 5 个以内，当我们给表创建索引时，并不是说每一个列都创建索引之后，在读取数据的时候就一定快，要通过实际的情况来决定，在很多的场景下，创建的索引越多，反而会导致数据的文件越大，那么在进行数据访问时效率就会降低，因此在 《高性能 MySQL 调优》强调了单表索引尽量控制在 5 个以内，当然在很多场景下，索引个数是可能超过 5 个的，根据实际的情况再决定

单索引字段数量

组合、复合索引字段数不允许超过 5 个，大部分应用场景下都需要创建组合索引，但组合索引的列个数不宜太多，列太多会导致占用太多的存储空间，从而会导致树深度变深，数据检索效率变低

避免错误概念

索引越多越好
过于早优化，在不了解系统的情况下进行优化

索引监控

索引监控信息，用于判别索引的使用情况

显示全局的索引读取记录

show global status like 'Handler_read%';

显示当前会话级别的索引读取记录

show status like 'Handler_read%';

对以上打印的参数描述如下：

Handler_read_first：索引中第一条被读取的次数
Handler_read_key：通过索引读取数据的次数，此选项数值如果很高，那么可以说明系统高效地使用到了索引，一切运转良好
Handler_read_last：通过索引读取最后一行的请求数
Handler_read_next：通过索引读取下一行的请求数，若查询语句中使用范围查询或索引扫描来查询索引列，该列增加
Handler_read_prev：通过索引顺序读取前一行的请求数，该读取方式主要用于优化 ORDER BY column DESC
Handler_read_rnd：从固定位置读取数据的次数，若你正执行大量查询并需要对结果进行排序该值就会比较高，那么你可能使用了大量全表扫描的查询或者没有正确使用索引
Handler_read_rnd_next：从数据文件读取下一行的请求数，如果你正在进行大量的表扫描，该值就会比较高，通常说明你的表索引不正确或写的 SQL 没有利用到索引

总结

该篇博文从零到一讲解了数据库索引使用到的数据结构以及它与存储引擎之前的关联关系，为什么要使用 B+ Trees 而不使用 B Trees？数据库表文件的存储方式：聚簇、非聚簇；说到了索引的类型以及这方面是如何去进行优化的，最重要的是，优化细节这个章节，不仅仅如何告知优化、生产如何调优、调优的细节如何处理、如何避免生产慢 SQL；最后，通过索引监控命令得知了系统使用 SQL 情况！希望你能够喜欢！后续的大数据量查询优化、海量数据解耦优化处理敬请期待～

如果觉得博文不错，关注我 vnjohn，后续会有更多实战、源码、架构干货分享！

推荐专栏：Spring、MySQL，订阅一波不再迷路

大家的「关注❤️ + 点赞👍 + 收藏⭐」就是我创作的最大动力！谢谢大家的支持，我们下文见！

MySQL 数据结构优化与索引细节解析：打造高效数据库的优化秘笈（三）

索引扫描排序

子查询

范围列查询

强制类型转换

建立索引

JOIN 表

LIMIT 限制输出行

单表索引数量

单索引字段数量

避免错误概念

索引监控

总结

热门文章

最新文章

相关课程

相关电子书

推荐镜像

探索云世界

热门

云计算

大数据

云原生

人工智能

数据库

开发与运维

活动广场

任务中心

训练营

直播

乘风者计划

下载

镜像站

技术资料

MySQL 数据结构优化与索引细节解析：打造高效数据库的优化秘笈（三）

索引扫描排序

子查询

范围列查询

强制类型转换

建立索引

JOIN 表

LIMIT 限制输出行

单表索引数量

单索引字段数量

避免错误概念

索引监控

总结

热门文章

最新文章

相关课程

相关电子书

推荐镜像