MySQL -- 问题记录1

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

这里讨论当一个字段比如身份证号码，我们已经确定他是唯一了，该给他创建唯一索引还是普通索引

查询
- 唯一索引的查询只要查到第一个符合条件的数据后就会返回
- 普通索引查询到符合条件的数据后还会往后查询直到找到第一条不符合条件的数据后才返回，其实就是要把所有符合条件的数据都找到，当数据已经是唯一的时候就相当于指针会往后多查找一次消耗基本可忽略
- 结论：对于查询来说两者差距可以忽略不计
- 疑问：==对于已知是唯一的数据手动做limit 1是不是能解决查询上的差异==
插入
- 唯一索引由于要去做数据唯一性校验所以不能用上change buffer机制
- 普通索引可以直接用change buffer，且不用做唯一性校验
- 结论：change buffer的内容不在这里详细讲，插入时普通索引是要优于唯一索引的
案例
- 将普通索引改成唯一索引后大量的插入操作导致数据库的内存命中率降低
结论：尽量使用普通索引，将唯一性校验放到业务代码里面去做，不要把压力转移到数据库

二、为什么优化器会选错索引

有时候我们创建了索引，但优化器最终没有去使用

优化器的工作原理（粗略）
- 判断本次操作需要扫描多少行数据，这个是主要的依据
  - 对于索引来说判断需要依赖一个叫cardinality（基数）的参数，这个参数就是指这个索引中有多少个不同的值，基数越大索引性能越好
  - 可以查看表中每个索引的基数情况
    1
    show index from aliexpress_product_info
- 考虑索引需要回表操作
- 考虑排序因素
场景
- 频繁修改数据的场景中，需要频繁的进行基数的预估统计，可能导致不准确
- 当使用的索引在通过基数预估来计算出要扫描的行和直接在主键索引上进行扫描的差距不大时，为了节约反表的成本，优化器直接到主键上全表扫描（这里问题出在基数估算错误），可以让数据库重新对索引基数进行估算
  1
  2
  
  analyze table aliexpress_product_info
- 如果有两个索引a、b，语句中同时使用到两个字段做过滤，索引a需要扫描1k行而索引b需要1w，但是最终要用b字段做排序的话，优化器还是会使用b，对一些差距特别大的情况我们可以强制指定使用索引
  1
  select * from t force index(a) where ...
结论
- 学会分析索引的基数来判断索引的性能
- 手动更新索引基数
- 强制指定使用索引，它有一个坏处如果后续索引修改了名称那么需要改SQL，所以一般不会在业务上用

三、如何优化字符串类型数据的索引

直接给字符串创建索引，这样的话会增加存储空间的占用
使用前缀索引，可以节省空间，但是从索引上取到的数据不一定符合条件需要回到主键索引上进行核对，增加了扫描次数
- 这个地方给出的案例是邮箱，只存储前几个字符，但实际上感觉实用性一般，为了节省大概一半的字段索引空间查询的时候性能会有降低，整体收益不是很高，但对于一些比较长的字符串来说如果能通过头部的一些字符就能有比较好的区分度的话，这个时候对空间占用的优化就比较明显了
- 另一个案例给出了身份证号码，进行倒置存储，因为后面的6位是标识性的编号，对倒置后的前6个字符设置前缀索引，这个时候查询需要用到reverse函数
- 将不能使用覆盖索引，覆盖索引就是指比如业务查询只需要主键ID和索引字段信息时，全量的索引其实就不需要做回标操作了，但前缀索引必须要回去做整个字段数据的核对
- 判断前缀数据重复度select count(distinct left(product_url_md5,16)) as L from shopify_product
使用hash字段，将一个长的字符串进行crc32()计算后会得到一个int类型的数值，为这个数值创建一个字段，然后给这个字段添加索引，这样来达到减少索引空间占用的目的
- 注意这个crc32函数计算出来的结果可能会有重复（实际几率很小），所以业务侧在进行查询的时候还是要对原字段进行匹配，相当于是使用hash进行进行索引定位，然后判断数据是否匹配

四、自增主键为什么出现不连续的现象

表中有字段被设置了唯一性，数据插入的时候会先为数据分配自增主键（如果没有显式指定的话），这个时候自增主键的计数器已经进行了累加，再之后进行数据插入的时候如果检测到唯一字段冲突会报错，但是自增主键的计数器已经被累加了，后续的插入会在这个数字基础上继续累加，于是就出现了数据的空洞
事务回滚也会导致不连续，原理和上面一条差不多

五、order by的实现

MySQL会为每个线程分配一片内存空间来对数据进行排序，叫做sort_buffer，MySQL有两种排序的方式，全字段排序和rowid 排序

第一种，将查询结果中需要的数据都拿出来，然后对order by的字段进行排序，排序方式是快速排序，这种方式叫做全字段排序
第二种，如果查询的字段很多，全都放到内存里面的话，对内存的占用会很大，所以可以只将需要排序的字段和id加载到内存中，排序完成后再通过ID取到需要的别的字段数据，这种方式叫做rowid 排序

MySQL排序的时候可能是在内存中进行，也可能写入磁盘进行，主要看分配的内存空间是否能够放下需要排序的数据，核心的思想仍然是能在内存中处理就在内存中进行处理

如果一个字段本身就已经完成了排序，那么这个时候就不再需要虚拟表来进行排序，直接就能取到需要的数据