MySQL索引

4 12月

MySQL是一个关系型数据库管理系统,由瑞典MySQL AB 公司开发,目前属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一,在各大互联网公司中,MySQL即其变种是最好的使用最多的关系型数据库。

MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策,分为社区版和商业版。因为License的问题,还有兼容MySQL的MariaDB分支可选。

磁盘IO与预读

因为业务上对于数据库的要求其实是比较高,主要是速度上的,经常是几毫秒的。在这种情况下硬盘IO就需要关注了。机械硬盘是需要机械运动的,比如常说的7200转。IO的消费是很大的,所以操作系统做了一些额外的优化,当执行一次IO时,不光读取当前磁盘地址的数据,也会把相邻数据都读取到内存缓冲区内,每一次IO读取的数据称之为一页,一般为4k或8k。

所以数据库在设计上有一个重要的方向就是减少IO。

索引

最容易的想到的方案就是记录一些数据的IO块的位置,比如自增ID为主键,前1000条在块N,后一千条在块N+1,当然这个方案有很多弊端,所以数据库专门抽象了索引这个概念。

索引按照官方文档的说法是帮助MySQL高效获取数据的数据结构,具体到不同的存储引擎层面,具体的数据结构是有区别的。

数据库查询是数据库的最主要功能之一,而应用都希望查询数据的速度能尽可能的快,因此数据库系统从设计层面会对查询进行优化。而不同的算法都只能应用于特定的数据结构之上,比如二叉树查找只能应用于二叉查找树上,但是数据本身的组织结构不可能完全满足各种数据结构。

所以,在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用或者指向的数据,同时在这些数据结构上实现高级查找算法。这种数据结构就是索引。

具体的索引结构支持又和引擎绑定,比如最常说B树或者B+树在MyISAM,InnoDB上都是支持的。

索引 MyISAM引擎 InnoDB引擎 Memory引擎
B-Tree 索引 支持 支持 支持
HASH 索引 不支持 不支持 支持
R-Tree 索引 支持 不支持 不支持
Full-text 索引 不支持 暂不支持 不支持

慢查询

慢查询就是字面的意思,查询请求运行时间比较长的就是慢查询,但是具体的时间阈值是可以变动的。比如默认的慢查询日志是10秒,而我司是100ms。

慢查询对于应用和数据库本身是有危害的,少量的慢查询影响业务的响应,而慢查询的积累会消耗掉连接池,进而影响整个数据库的响应能力,导致大面积故障。

MySQL是可以直接捕获慢查询,并将其记录在慢查询日志中,默认地址/var/lib/mysql/mysql-slow.log。利用show processlist也是可以监控到实时存在的慢查询。

优化

慢查询的优化有很多可以找到的资料,美团点评技术团队有一些总结:

  • 最左前缀匹配原则,非常重要的原则,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整;
  • =和in可以乱序,比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式;
  • 尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态、性别字段可能在大数据面前区分度就是0,那可能有人会问,这个比例有什么经验值吗?使用场景不同,这个值也很难确定,一般需要join的字段我们都要求是0.1以上,即平均1条扫描10条记录;
  • 索引列不能参与计算,保持列“干净”,比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引,原因很简单,b+树中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);
  • 尽量的扩展索引,不要新建索引。比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可。

当然自己熟练掌握这些技巧是可以的,但是处于快速解决问题的情况下借助一些工具无疑是更好的。

实用explain可以快速定位问题,但是需要经验才能实施优化。

老版本的PT包含了一个pt-query-advisor工具,也可以从慢查询日志给出建议,不过后面的版本下掉了。

美团点评开源的SQLAdvisor可以帮助分析SQL语句,同时提供优化建议。这个是非常实用的,我打了一个Docker版本的,可以快速实用。

参考

https://www.cnblogs.com/nixi8/p/4540813.html

https://www.jianshu.com/p/7529a0fbf088

https://www.jianshu.com/p/43091bfa8aa7

https://dev.mysql.com/doc/refman/8.0/en/slow-query-log.html

发表评论

电子邮件地址不会被公开。