Mysql 必备知识(持续更新...)

唯一索引和普通索引的选择

## create table
create table t1(
`id` bigint(20) primary key,
`no` bigint(10)
);
## query1
select id from t1 where no =1;

查询过程区别

1. no 为唯一索引时， 执行查询query1时，只需要加载no为1所在的数据页到内存中（如果已经在内存中，则不需要加载过程），然后定位到no=1的记录即完成查询.
2. no为普通索引时，在执行完上述过程后，由于不是唯一索引，需要查找下一条记录是否满足查询条件，直到不满足才停止查询，都是基于内存遍历的，如果在数据页边界时可能会涉及到加载 下一个数据页到内存中（概率比较小）。

更新过程区别

1. 唯一索引，在执行update 或者insert时，需要判断当前的变更值是否唯一，需要加载数据页到内存中。
2. 普通索引，在执行update 或者insert时，因为不需要判断值的唯一性，直接将更新记录 记录在change buffer中。不需要立马将数据页加载到内存中，从而执行效率要高。

change buffer ： 在执行更新操作时，如果需要唯一性判断，可以先将操作记录下来，如果用户下次发起查询，将数据页加载到内存中，然后在内存中将change buffer中的变记录应用到数据页上，从而保证数据一致性。
在写多读少的场景下使用change buffer收益会比较高（类似日志）。如果查询比较多的情况下，会导致频繁的发生数据页加载到内存，然后应用change buffer，这样就会比在没有change buffer时，多了额外的维护change buffer的过程。

总结

在查询上来看，唯一索引和普通索引的区别很小，只有在跨数据页查询时会产生多余的消耗过程(但是概率很低），从更新的角度来看，普通索引的效率会高于唯一索引。
如果在更新和查询都比较频繁的表中考虑关闭change buffer。

如何给字符串字段加索引

前缀索引

如果字符串内容过长，建立索引时就会占用过多的空间，可以使用前缀索引的方式减小索引占用空间。

example给邮箱的前六位建立索引.

alter table `t` add index index_name(email(6);

• 弊端

1. 如果出现重复的前六位时就会出现增加扫描行数的风险，为了降低这种事情发生的频率，再建立索引的时候要考虑最合适的长度，和重复率最低之间的权衡。
2. 如果查询的结果中出现主键之外的字段时，会出现覆盖索引失效，增加回表次数。

特殊情况

如果字段内容类似居民身份证号码这种数据，因为身份证号码的前缀部分区分度很低。对于这种数据可以考虑其他处理方式：

1. 将数据倒置存储。
2. 创建字符串字段的hash字段。

alter table t add id_card_crc int unsigned, add index)id_card_crc);

通过crc32()函数得到校验码填到这个新字段，由于存在重复风险，再查询时还是要判断结果是否精准。
由于倒置和hash以后的新字段的顺序和原字段的书序不一致，所以以上两种方式都是不允许范围查询的，只能支持等值查询。

总结

1. 直接在字符串字段上创建索引可能会比较占用空间。
2. 直接使用前缀索引，能节省空间，但是会增加扫描行数，并且不能使用覆盖索引。
3. 通过倒序存储，再使用前缀索引可以增加特殊情况下的区分度。不支持范围查询。
4. 通过hash字段索引，会增加额外的hash消耗，但是和不支持范围扫描。

计数

• 耗时统计
  count(字段)<count(主键 id)<count(1)≈count(*)

排序

explain select * from t where a<5000 order by b;

using filesort表示需要排序。

前置条件： 先通过查询把数据加载到sort_buffer中，sort_buffer： Mysql 会为每一个线程分配一块内存用于排序，称为sort_buffer.

排序语句执行过程分析
select a, b, c from t where a =xxx order by b
假设在字段a上有二级索引，执行过程如下：

1. 初始化sort_buffer, 确定要放入a, b, c三个字段。
2. 从a索引树上找到第一个满足xxx条件的节点，然后获取到主键id， 这一步是回表的过程。
3. 在主键索引树上获取整行信息，然后取出字段a, b, c 存入sort_buffer中。
4. 从a索引树上取下一个满足条件xxx的主键id；
5. 重复3，4 两步，直到条件不满足为止。
6. 对sort_buffer中的数据按照字段b进行排序。
7. 然后把排序结果返回.

参数 sort_buffer_size 的大小决定了第6步的排序过程是发生在内存中还是临时使用硬盘空间。
可以通过查看OPTIMIZER_TRACE结果来查看是否使用了临时文件来进行排序
属性：number_of_temp_files: xx, 如果xx>0 表示使用了磁盘空间，会出现多个文件的原因是使用了归并排序算法。如果sort_buffer能够满足排序需求，那么这个xx就会是0，就不会用到临时文件。

单行数据长度太大时的排序
如果单行数据很大，占用内存空间超过sort_buffer_size时，就会产生过多的临时文件，降低排序效率。
可以通过设置参数max_length_for_sort_data，如果用于排序的行数据单行数据的长度超过这个属性值，Mysql就会认为是超长字段，会改变排序策略。
这里在第三步中只保存排序字段和主键id，在完成第六步之后，再根据主键进行一次回表，获取需要查询的其他字段信息。从而减少内存消耗。
利用覆盖索引
通过创建复合索引避免回表过程，可以不需要再通过主键去寻找满足条件的其他字段信息。索引本身有序，加上复合索引包含要查询的所有数据可以明显提升排序效率。但是使用场景单一，无法满足查询索引之外的字段情况。

总结

合理控制参数避免临时文件,复合索引减少回表次数。

随机消息处理

从大的数据表中随机筛选几个数据
常用做法
select xx from t order by rand() limit i;
会使用临时文件，性能很差

TODO: