Mysql专栏 - mysql索引（三）
#Mysql专栏 - mysql索引（三）# 前言
上一节我们详细解释了mysql的聚簇索引部分以及mysql的索引使用匹配规则，其中最重要的内容是最左匹配的规则，由此可以推导出很多规则的应用，所以需要重点进行关，而其他的内容只需要学习即可。
## 学习内容：
1. 学习如何设计索引
2. 设计索引如何避坑
3. 如何让你的查询百分百能用上索引
# 建立索引的建议
下面是日常建立索引的一些建议：
- 经常被查询或者排序的字段
- 值比较多的字段，对于优化索引的查询价值越高
- 对于字段比较小的类型进行查询，比如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 + 微调值
### 索引访问速度估算
1. 首先需要估算计算的范围，比如按照NAME的范围值，如果只存在一个范围，通常在一个数据页进行扫描。
2. 假设二级索引的效率为100页，然后使用0.2也就是20，这是二级索引的速度
3. 然后二级索引需要回表操作，此时就需要回到聚簇索引的表进行查找 。
## 常见的优化方式：
### 常量替换
在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的查询和使用速度。
## 性能指标：
下面是三条基本的原则：
1. 主键索引查询肯定是`CONST`
2. 二级索引想要是是`CONST`，你的索引必须是唯一索引 才行 - `UNIQUE KEY`。但是如果使用 IS NULL这种查询方式，依然使用的REF的方式。
3. 另外，如果查询的是按照索引的顺序列进行查询，但是WHERE条件不是，一样可以使用索引直接找叶子节点的方式。
4. 类似于`select * from table where name=x and name IS NULL`，那么此时在执行计划里就叫做`ref_or_null`
5. 普通索引的查询方式是`REF`，类似`INDEX(NAME, AGE)`。
6. 范围查询的时候会使用`RANGE`的查询方式
7. 针对这种只要遍历二级索引就可以拿到你想要的数据，而不需要回源到聚簇索引的访问方式，就叫做index访问方式！INDEX的方式需要遍历某个二级索引，但是因为二级索引比较小，所以遍历性能也还可以的。
现在我们停一下脚步，思考一下，之前说的const、ref和range，本质都是基于索引树的二分查找和多层跳转来查询，所以性能一般都是很高的，然后接下来到index这块，速度就比上面三种要差一些了，因为他是走遍历二级索引树的叶子节点的方式来执行了，那肯定比基于索引树的二分查找要慢多了，但是还是比全表扫描好一些的。
# 驱动表和被驱动表
驱动表：指的是关联查询条件先需要进行筛选的表，通常位于表的前面
被驱动表：通常需要根据一个表的关联数据找到另一张表的内容进行关联，所以叫被驱动表。
## 驱动规则
循环嵌套规则：我们假设在驱动表里面找到了10条数据，通过驱动表的部分字段找到被驱动的数据，就意味着需要在被驱动表里面执行驱动表次数的查找。
比如驱动表为10次，被驱动表就需要扫描整个表10次。
# explain计划
## 基本的字段格式：
首先需要了解EXPLAIN的基本格式
1. 首先，一个select就会出现一个id, 通常在复杂的查询里面会包含多张表的查询，比如join, in等等
2. SelecT_TYPE：这个表示的是查询的类型
3. Table：表名称
4. Partitions：这个表示表空间，分区的概念
5. Type : 比如查询的优化等级,const, index, all，分别代表了聚簇索引，二级索引，全表扫描的查询搜索方式
6. PossiblEkeys：和type一样确定访问方式，确定有哪些索引可以选择，
7. Key：确定有哪些可以提供选择，同时提供索引的对应长度
8. Key_len： 表示的是索引的长度
9. Ref： 等值匹配的时候出现的一些匹配的相关信息
10. Rows： 预估通过所索引或者别的方式读取多少条数据
11. Filtered：经过搜索条件过滤之后的**剩余数据百分比。**
12. extra：额外的信息不重要。
下面为一个简单的案例：
```mysql
expain select * from (select x1,count(*) as cnt from t1 group by x1) as _t1 where cnt > 10
```
查询的结果如下：
DERIVED：表示子查询的结果会物化为一个内部的临时表，然后外层的查询针对临时表物化开始进行搜索分组聚合的时候，使用的索引的方式，所以是index的扫描速度。
## 关于查询等级：
1. const: 一般是主键查询的时候
2. Ref: 基于某个耳机索引的时候进行查询
3. Eq_ref: 表示连接查询是根据二级索引索引关联的
4. Eq_ref_null： 二级索引的关联的时候根据Null值允许进行关联查询
5. Index_merge: ×询可能会基于多个索引提取数据后进行合并
6. 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计划即可，这是一个十分强大好用的工具。 感谢楼主分享 学习 学习 等完贴。谢谢分享。 1. 较频繁的作为查询条件的字段应该创建索引
2. 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
3. 更新非常频繁的字段不适合创建索引
4. 不会出现在 WHERE 子句中的字段不该创建索引 感谢分享，索引对于查询来说很重要 学习了，感谢:lol
页:
[1]