MySQL索引

栏目: 数据库 · 发布时间: 6年前

内容简介:MySQL是一个关系型数据库管理系统,由瑞典MySQL AB 公司开发,目前属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一,在各大互联网公司中,MySQL即其变种是最好的使用最多的关系型数据库。MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策,分为社区版和商业版。因为License的问题,还有兼容MySQL的MariaDB分支可选。因为业务上对于数据库的要求其实是比较高,主要是速度上的,经常是几毫秒的。在这种情况下硬盘IO就

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


以上所述就是小编给大家介绍的《MySQL索引》,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持!

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

企业应用架构模式

企业应用架构模式

Martin Fowler / 人民邮电出版社 / 2009 / 79.00元

随着信息技术的广泛应用,系统需要处理的数据量越来越大,企业级软件开发已经渐成主流,而开发人员面临的困难与挑战也是显而易见的。更糟糕的是,这一领域的资料一直非常缺乏。 本书是软件开发大师Martin Fowler的代表作,采用模式的形式系统总结了业界多年积累的经验,被称为“企业级应用开发领域的圣经”,出版以来一直畅销不衰,至今仍然无可替代。作 者在精彩地阐述了企业应用开发和设计中的核心原则基础......一起来看看 《企业应用架构模式》 这本书的介绍吧!

XML 在线格式化
XML 在线格式化

在线 XML 格式化压缩工具

正则表达式在线测试
正则表达式在线测试

正则表达式在线测试

HEX CMYK 转换工具
HEX CMYK 转换工具

HEX CMYK 互转工具