小知识，大挑战！本文正在参与“程序员必备小知识”创作活动

Mysql专栏 - mysql索引（三）

前言

上一节我们详细解释了mysql的聚簇索引部分以及mysql的索引使用匹配规则，其中最重要的内容是最左匹配的规则，由此可以推导出很多规则的应用，所以需要重点进行关，而其他的内容只需要学习即可。

学习内容：

学习如何设计索引
设计索引如何避坑
如何让你的查询百分百能用上索引

建立索引的建议

下面是日常建立索引的一些建议：

经常被查询或者排序的字段
值比较多的字段，对于优化索引的查询价值越高
对于字段比较小的类型进行查询，比如tinyint , char 等等
尽量使用主键自增而不是使用uuid
索引不需要设计太多
**如果使用了范围查询，多数情况下是无法使用索引的，**所以应该把范围查询放到查询的最右边。
第一个范围查询可以用上索引，第二个无法用上索引，所以范围查询最好只有一个

索引的使用问题：

分页与排序

在上一节我们讨论过对于大多数的分页以及排序，其实都是没有办法使用索引的，因为联合索引必须按照最左侧的方式进行查找。

案例：

比如我们在查询省份城市和性别，有时候要按照不同的字段进行查询，所以很多情况下无法应用最左匹配的原则。

解决办法：

与其如此，还不如就把类似省份、城市和性别三个字段，都放在联合索引的最左侧，这样跟其他字段组合，联合索引后，让大部分的查询都可以直接通过索引树就可以把where条件指定的数据筛选出来了。

建议：在设计的时候可以按照多设计几个字段的索引并且按照从左到右的查询方式进行匹配，最后一个使用范围值进行处理，这样就可以使得整个查询都可以用得上索引了。

mysql执行计划

什么是执行成本?

首先是执行计划的成本，我们在计算的时候，会把CPU的成本，符合成本就是0.2，从磁盘读取到内存的成本被设置为1

如何计算成本：首先我们可以：show table status like "表名"，对于innodb来说，这个rows是估计值，下面是对应的估计值内容：

Rows: 就是表里的记录数，
data_length: 就是表的聚簇索引的字节数大小

页数量如何计算：

data_length除以1024就是kb为单位的大小，然后再除以16kb（默认一页的大小），就是有多少页
IO成本就是：数据页数量 * 1.0 + 微调值，CPU成本就是：行记录数 * 0.2 + 微调值

索引访问速度估算

首先需要估算计算的范围，比如按照NAME的范围值，如果只存在一个范围，通常在一个数据页进行扫描。
假设二级索引的效率为100页，然后使用0.2也就是20，这是二级索引的速度
然后二级索引需要回表操作，此时就需要回到聚簇索引的表进行查找。

常见的优化方式：

常量替换

在sql进行优化查询的时候，会把一些范围查询的值转为常量搜索，select * from t1 join t2 on t1.x1=t2.x1 and t1.id=1，经过替换后如下：select t1表中id=1的那行数据的各个字段的常量值，最终替换的结果为：t2.* from t1 join t2 on t1表里x1字段的常量值 =t2.x1

子查询

首先，一个子查询会分为两个语句，首先会根据主键的聚簇索引开始对于内容进行查找。对于上述的子查询，执行计划会被优化为，先执行子查询，也就是select x2 from t2 where x3=xxx这条SQL语句，把查出来的数据都写入一个临时表里，也可以叫做物化表，意思就是说，把这个中间结果集进行物化。

半连接的优化

首先，MYSQL没有半链接这种语法，这是底层对于JOIN以及IN查询的一种优化手段，select t1.* from t1 semi join t2 on t1.x1=t2.x2 and t2.x3=xxx，如果使用半链接的手段，其实可以只链接符合ON条件的半链接表即可。

执行计划和SQL语句的关系：虽然索引可以解决不太复杂的单表查询的情况，但是很多时候，统计，汇总，函数等SQL的使用还是会降低整个SQL的查询和使用速度。

性能指标：

下面是三条基本的原则：

主键索引查询肯定是CONST
二级索引想要是是CONST，你的索引必须是唯一索引才行 - UNIQUE KEY。但是如果使用 IS NULL这种查询方式，依然使用的REF的方式。
另外，如果查询的是按照索引的顺序列进行查询，但是WHERE条件不是，一样可以使用索引直接找叶子节点的方式。
类似于select * from table where name=x and name IS NULL，那么此时在执行计划里就叫做ref_or_null
普通索引的查询方式是REF，类似INDEX(NAME, AGE)。
范围查询的时候会使用RANGE的查询方式
针对这种只要遍历二级索引就可以拿到你想要的数据，而不需要回源到聚簇索引的访问方式，就叫做index访问方式！INDEX的方式需要遍历某个二级索引，但是因为二级索引比较小，所以遍历性能也还可以的。

现在我们停一下脚步，思考一下，之前说的const、ref和range，本质都是基于索引树的二分查找和多层跳转来查询，所以性能一般都是很高的，然后接下来到index这块，速度就比上面三种要差一些了，因为他是走遍历二级索引树的叶子节点的方式来执行了，那肯定比基于索引树的二分查找要慢多了，但是还是比全表扫描好一些的。

驱动表和被驱动表

驱动表：指的是关联查询条件先需要进行筛选的表，通常位于表的前面

被驱动表：通常需要根据一个表的关联数据找到另一张表的内容进行关联，所以叫被驱动表。

驱动规则

循环嵌套规则：我们假设在驱动表里面找到了10条数据，通过驱动表的部分字段找到被驱动的数据，就意味着需要在被驱动表里面执行驱动表次数的查找。

比如驱动表为10次，被驱动表就需要扫描整个表10次。

explain计划

基本的字段格式：

首先需要了解EXPLAIN的基本格式

首先，一个select就会出现一个id, 通常在复杂的查询里面会包含多张表的查询，比如join, in等等
SelecT_TYPE：这个表示的是查询的类型
Table：表名称
Partitions：这个表示表空间，分区的概念
Type : 比如查询的优化等级, const, index, all，分别代表了聚簇索引，二级索引，全表扫描的查询搜索方式
PossiblEkeys：和type一样确定访问方式，确定有哪些索引可以选择，
Key：确定有哪些可以提供选择，同时提供索引的对应长度
Key_len：表示的是索引的长度
Ref：等值匹配的时候出现的一些匹配的相关信息
Rows：预估通过所索引或者别的方式读取多少条数据
Filtered：经过搜索条件过滤之后的剩余数据百分比。
extra：额外的信息不重要。

下面为一个简单的案例：

expain select * from (select x1,count(*) as cnt from t1 group by x1) as _t1 where cnt > 10

查询的结果如下：

DERIVED：表示子查询的结果会物化为一个内部的临时表，然后外层的查询针对临时表物化开始进行搜索分组聚合的时候，使用的索引的方式，所以是index的扫描速度。

关于查询等级：

const: 一般是主键查询的时候
Ref: 基于某个耳机索引的时候进行查询
Eq_ref: 表示连接查询是根据二级索引索引关联的
Eq_ref_null：二级索引的关联的时候根据Null值允许进行关联查询
Index_merge: ×询可能会基于多个索引提取数据后进行合并
Range:而查询方式是range的话就是基于二级索引进行范围查询

Using filesort

这个会在排序的时候见到，特别是分页的排序查询，这个需要极力的避免，因为不走索引的排序是非常非常慢的，需要使用Memory表进行数据的操作。

Using temprory

通常出现在大量的group union distinct 等等的时候会出现和filesort类似，也会出现数据量过大而产生临时表的问题。

结论

核心重点就是，尽量利用一两个复杂的多字段联合索引，抗下你80%以上的查询，然后用一两个辅助索引抗下剩余20%的非典型查询，保证你99%以上的查询都能充分利用索引，就能保证你的查询速度和性能！

其他

关于7天是否登录的表设计

7天是否登录过是一个比较常见的小需求了，最简单的方式其实并不是范围查询，而是使用增加一个是否7天登录的flag值并且通过定时任务定时刷新这个字段的值即可。如果要让这种查询用上索引，可以设计一个联合索引为：（province, city, sex, hobby, character,does_login_in_latest_7_days, age），然后搜索的时候，一定会在where条件里带上一个does_login_in_latest_7_days=1，最后再跟上age范围查询，这样就可以让你的where条件里的字段都用索引来筛选。

设计辅助索引的案例

使用辅助索引，比如加入一个辅助的索引来加快排序和筛选操作。

写在最后

索引的最后部分其实更多的是和执行计划进行结合，对于MYSQL来说优化的最好方式就是学习好MYSQL的explain计划即可，这是一个十分强大好用的工具。