专栏首页若是烟花MySQL--索引及优化查询

MySQL--索引及优化查询

根据 MySQL索引原理及慢查询优化 整理

索引相关

索引的目的

索引的目的在于提高查询效率。

索引的原理

通过不断的缩小要查询的数据的范围来筛选出最终想要的结果,同时将随机的事件变成顺序事件。

数据库则复杂得多,不仅需要面对等值查询,还有范围插叙(<, >, between, in)、模糊查询(like)、并集查询(or)等等。 简单的搜索树难以满足复杂的应用场景。

磁盘的IO与预读

磁盘的IO是非常昂贵的操作。计算机操作系统做了一些优化,当一次IO时,不光读取当前磁盘地址的数据,而是把相邻的数据也都读取到内存缓冲区。每次IO读取的数据我们称之为一页(page)。一页一般为4kb或者8kb。

索引的数据结构

我们需要的数据结构:每次查找数据时,都要把磁盘IO次数控制在一个很小的数量级,最好是常数数量级。一个高度可控的多路搜索树是否能够满足需求呢?B+树应运而生。

详解B+树

B+树是一种树数据结构,通常用于数据库和操作系统的文件系统中。B+树的特点是能够保持数据稳定有序,其插入与修改拥有较稳定的对数时间复杂度。B+树元素自底向上插入,这与二叉树恰好相反。

真实数据存储于叶子节点,非叶子节点不存储真实数据,只存储指引搜索方向的数据项。

B+树的查找过程

内存查询时间非常短(相比于磁盘的IO)可以忽略不计。真实的情况是3层B+树,可以表示上百万的数据。如果上百万的数据查找只需要三次IO,性能提高将是巨大的。

B+树的性质

  1. 通过上面的分析,我们知道IO的次数取决于树的高度H,假设当前数据表的数据为N,每个磁盘块的数据项的数量是M,则有H=log(M+1)N。当数据量N一定的情况下,M越大,H越小;而M=磁盘块的大小/数据项的大小,磁盘块的大小也就是一个数据页的大小,是固定的。如果数据项占用的空间越小,数据项就越多,树的高度就会越低。这就是为什么每个数据项,即索引字段要尽量小。这也是为什么B+树要求把真实数据存储到叶子节点而不是内层节点,一旦放到内层节点,磁盘块的数据项会大幅度下降,导致树增高。当数据项等于1时,将会退化成线性表。
  2. 当B+树的数据项是复合的数据结,比如(name, age, sex)时,B+树是按照从左到右的顺序来简历搜索树的。索引的最左匹配特性。

慢查询优化

建立索引的几大原则

  1. 最左前缀匹配原则。 MySQL会一直想有匹配直到遇到范围查询(<, >, between, like)就停止匹配。
  2. =和in可以乱序。MySQL查询优化器可以帮你优化成索引可以识别的形式。
  3. 尽量选择区分度高的列作为索引。区分度公式:COUNT(DISTINCT col)/COUNT(*),表示字段的不重复比例,比例越大扫描的表就越少,唯一键的缺乏难度为1。根据使用场景的不同,这个值也很难确定,一般需要join的字段我们都要求是0.1以上。
  4. 索引列不能参与计算。保持列“干净”。
  5. 尽量的扩展索引,不要新建索引。

查询优化神器 -- explain命令

慢查询优化基本步骤

  1. 先运行看下是否真的很慢,注意设置 SQL_NO_CACHE
  2. wnere条件单表查,锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录最小的表开始查起,单表每个字段分别查询,看哪个字段的区分度最高
  3. explain查看执行计划,是否与1预期一致(从锁定记录较少的表开始查询)
  4. order by limit形式的sql语句,让排序的表优先查询
  5. 了解业务方使用场景
  6. 增加索引时,参照索引的几大原则
  7. 观察结果,不符合预期继续从0分析

写在后面的话

任何数据库层面的优化都抵不上应用系统的优化。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 七日Python之路--第二天

    由于之前使用过一段Ubuntu,所以基本命令啥的都还可以。装差利器Vim也是使用过一段时间的。

    lpe234
  • 七日Python之路--第五天(之Django官方文档)

    之前一直使用Django自带的SQLite3数据库,感觉挺爽的,啥都不用管。但是,学习岂能贪图便利。遂开始使用MyQL。但是似乎不太顺利。首先在新建的项...

    lpe234
  • RabbitMQ 学习

    一个不错的入门教程: http://blog.csdn.net/linvo/article/details/5750987 写的挺好的,只是刚开始看可能不太懂...

    lpe234
  • SAP 查看更改历史记录信息表介绍(CDPOS/CDHDR)

    SAP中暂无事务代码可直接查询历史更改记录,例如交货协议的价格修改记录等,但是所有的凭证/业务数据的创建,删除和修改的历史都会保存到表CDPOS和CDH...

    用户5495712
  • nginx 实现基于basic用户认证机制

    在Ubuntu中可能apt找不到httpd-tools,可以安装apache2-utils,也有此工具

    DH镔
  • 博客首页加载的优化

    从刚做好这个博客不就第一次就发现了,发起 ajax 请求时,可以看到 content-download 的时间居然比 waiting 的时间还长,而且首页等待时...

    Krry
  • leetcode: 14. Longest Common Prefix

    JNingWei
  • python对比配置文件差异

    try: tx1 = sys.argv[1] tx2 = sys.argv[2] except Exception as e: print "error" + ...

    py3study
  • FW:面向方面开发

       面向对象技术很好地解决了软件系统中角色划分的问题。借助于面向对象的分析、设计和实现技术,开发者可以将问题领域的“名词”转换成软件系统中的对象,从而很自然地...

    阿敏总司令
  • Skype for Business 会议故障案例

    Lync/Skype for Business客户端创建会议报错,提示连接服务器错误,严重到“现在开会”选项消失。

    杨强生

扫码关注云+社区

领取腾讯云代金券