05 | 深入浅出索引(下)

2019-07-12 15:49:03

本文的总结源于林晓斌在极客时间里面的课程,为避免产生商业问题,我只是用于个人学习总结。
原文地址:https://time.geekbang.org/column/article/69636

小结

  1. 覆盖索引:覆盖索引可以减少树的搜索次数,显著提升查询性能,所以使用覆盖索引是一个常用的性能优化手段
  2. 针对某些高频查询,要根据市民的身份证号查询他的姓名,则在(身份证号、姓名)上面创建一个联合索引,就可以在这个高频请求上用到覆盖索引,不再需要回表查整行记录,减少语句的执行时间
  3. B+树这种索引结构,可以利用索引的“最左前缀”,来定位记录
  4. 联合索引的理解:比如一个联合索引(a,b,c),其实质是按a,b,c的顺序拼接成了一个二进制字节数组,索引记录是按该字节数组逐字节比较排序的,所以其是先按a排序,再按b排序,再按c排序,至于其为什么是按最左前缀匹配的也就显而易见了。
  5. 给表创建索引时,应该创建哪些索引,每个索引应该包含哪些字段,字段的顺序怎么排列,这个问题没有标准答案,需要根据具体的业务来做权衡。不过有些思路还是可供参考的:
  • 既然是一个权衡问题,没有办法保证所有的查询都高效,那就要优先保证高频的查询高效,较低频次的查询也尽可能的使用到尽可能长的最左前缀索引。可以借助pt-query-digest来采样统计业务查询语句的访问频度,可能需要迭代几次才能确定联合索引的最终字段及其排序
  • 业务是在演进的,所以索引也是要随着业务演进的,并不是索引建好了就万事大吉了,业务发生变化时,我们需要重新审视当初建的索引是不是还依然高效,依然能满足业务需求
  • 业内流传的有一些mysql 军规,其实这些并不是真正的军规,只是典型场景下的最佳实践。真正的军规其实就一条:高效的效满足业务需求。比如有个军规规定一个表上的索引数不超过5个,但如果我们现在有一些历史数据表、历史日志表,我们很明确的知道这些表上不会再有数据写入了,但我们的查询需求很多也很多样化,那我们在这些表上的索引数能不能超过5个?当然是没有任何问题的。当然关于这份军规还是要认真看一下的,但看的重点不是去记住它,而是要弄明白每一条军规它为什么这么规定,它这样规定是基于什么考虑,适用的场景和前提是什么,这些都弄明白了,你记不记得住这些军规都无所谓了,因为你已经把它溶化到了你的血液中,具体到自己的具体业务时游刃有余将是必然

上期问题解答

《04 | 深入浅出索引(上)》
参考答案:
重建索引k的做法是合理的,可以达到省空间的目的。但是,重建主键的过程不合理。不论是删除主键还是创建主键,都会将整个表重建。所以连着执行这两个语句的话,第一个语句就白做了。这两个语句,你可以用这个语句代替:alter table T engine=InnoDB。

本期问题

05wen-ti

04 | 深入浅出索引(上)

本文的总结源于林晓斌在极客时间里面的课程,为避免产生商业问题,我只是用于个人学习总结。 原文地址:https://time.geekbang.org/column/article/69236 小结 索引的作用:提高数据查询效率 常见索引模型:哈希表、有序数组、搜索树 哈希表:键-值(key-value) 哈希思路:把值放在数组里,用一个哈希函数把key换算成一个确定的位置,然后把value放在数组的这个位置 哈希冲突的处理办法:链表 哈希表适用场景:只有等值查询的场景 有序数组:按顺序存储。查询用二分法就可以快速查询,时间复杂度是:O(log(N)) 有序数组查询效率高,更新效率低 有序数组的适用场景:静态存储引擎(比如往年数据,不会进行变动的数据) 二叉搜索树:每个节点的左儿子小于父节点,父节点又小于右儿子 二叉搜索树:查询时间复杂度O(log(N)),更新时间复杂度O(

06 | 全局锁和表锁:给表加个字段怎么有那么多阻碍?

本文的总结源于林晓斌在极客时间里面的课程,为避免产生商业问题,我只是用于个人学习总结。 原文地址:https://time.geekbang.org/column/article/69862 小结 根据加锁范围:MySQL里面的锁可以分为:全局锁、表级锁、行级锁 全局锁 对整个数据库实例加锁 MySQL提供全局读锁的方法:flush tables with read lock(FTWRL) 这个命令可以使整个库处于只读状态,使用该命令之后,数据更新语句、数据定义语句和更新类事务的提交语句等操作都会被阻塞。 使用场景:全库逻辑备份 风险: 如果在主库备份,在备份期间不能更新,业务停摆 如果在从库备份,备份期间不能执行主库同步的binlog,导致主从延迟 官方自带的逻辑备份工具Mysqldump,当Mysqldump使用参数--single-transaction的时候,会启动一个书屋,确保拿到一致性视图。而由于MVCC的支持,这个过程中数据是可以正常更新的。 一致性读是好,但是前提是引擎要支持这个隔离级别。 如果要全库只读,为什么不使用set global