MySQL索引

MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。那我们就来详细了解一下索引

索引是什么

MySQL官方对索引的定义为:索引是帮助 MySQL 高效获取数据的数据结构。

在数据之外,数据库系统还维护着满足特定查找算法的数据结构**,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法。这种数据结构,就是索引。下图就是一种可能的索引方式示例:

索引示意图
索引的出现就是为了提高查询效率,就像书的目录。其实说白了,索引要解决的就是查询问题。

查询,是数据库所提供的一个重要功能,我们都想尽可能快的获取到目标数据,因此就需要优化数据库的查询算法,选择合适的查询模型来实现索引。

优势

  1. 索引提高数据检索的效率,降低数据库的IO成本。
  2. 通过索引对数据进行排序,降低数据排序的成本,降低了CPU的消耗。

劣势

  1. 实际索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录,索引列也要占空间。
  2. 提高查询速度,降低更新表的速度。保存表数据,还要保存索引文件每次更新添加索引列字段。

索引的数据结构

B+树

任何一种数据结构都不是凭空产生的,一定会有它的背景和使用场景,我们现在总结一下,我们需要这种数据结构能够做些什么,其实很简单,那就是:每次查找数据时把磁盘IO次数控制在一个很小的数量级,最好是常数数量级。那么我们就想到如果一个高度可控的多路搜索树是否能满足需求呢?就这样,b+树应运而生。

B+树

如上图,是一颗b+树,关于b+树的定义可以参见B+树,这里只说一些重点,浅蓝色的块我们称之为一个磁盘块,可以看到每个磁盘块包含几个数据项(深蓝色所示)和指针(黄色所示),如磁盘块1包含数据项17和35,包含指针P1、P2、P3,P1表示小于17的磁盘块,P2表示在17和35之间的磁盘块,P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点不存储真实的数据,只存储指引搜索方向的数据项,如17、35并不真实存在于数据表中。

b+树的查找过程

如图所示,如果要查找数据项29,那么首先会把磁盘块1由磁盘加载到内存,此时发生一次IO,在内存中用二分查找确定29在17和35之间,锁定磁盘块1的P2指针,内存时间因为非常短(相比磁盘的IO)可以忽略不计,通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存,发生第二次IO,29在26和30之间,锁定磁盘块3的P2指针,通过指针加载磁盘块8到内存,发生第三次IO,同时内存中做二分查找找到29,结束查询,总计三次IO。真实的情况是,3层的b+树可以表示上百万的数据,如果上百万的数据查找只需要三次IO,性能提高将是巨大的,如果没有索引,每个数据项都要发生一次IO,那么总共需要百万次的IO,显然成本非常非常高。

建索引的几大原则

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

  5. 尽量的扩展索引,不要新建索引。比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可。

索引优化

explain命令

explain命令的具体用法和字段含义可以参考官网explain-output。rows是核心指标,绝大部分rows小的语句执行一定很快,所以优化语句基本上是在优化rows。

explain结果列说明

id列

id为SELECT的标识符。它是在SELECT查询中的顺序编号。如果这一行表示其他行的union结果,这个值可以为空。在这种情况下,table列会显示为形如,表示它是id为M和N的查询行的联合结果。

注意:id列数字越大越先执行,如果说数字一样大,那么就从上往下依次执行。

select_type

  • SIMPLE: 简单的 select 查询,查询中不包含 子查询 或者 UNION。
  • PRIMARY: 查询中若包含任何复杂的子部分,最外层查询则被标记为 PRIMARY。
  • SUBQUERY: 在 SELECT 或 WHERE 列表中包含了子查询。
  • DERIVED: 在FROM列表中包含的子查询被标记为DERIVED(衍生)MySQL会递归执行这些子查询,把结果放在临时表里。
  • UNION:
    • 若第二个SELECT出现在UNION之后,则被标记为UNION;
    • 若UNION包含在FROM子句的子查询中,外层SELECT将被标记为:DERIVED。
  • UNION RESULT: 从 UNION 表获取结果的 SELECT。

table

显示的查询表名,如果查询使用了别名,那么这里显示的是别名,如果不涉及对数据表的操作,那么这显示为null,如果显示为尖括号括起来的就表示这个是临时表,后边的N就是执行计划中的id,表示结果来自于这个查询产生。如果是尖括号括起来的,与类似,也是一个临时表,表示这个结果来自于union查询的id为M,N的结果集。如果是尖括号括起来的,这个表示子查询结果被物化,之后子查询结果可以被复用。

type

从最好到最差依次是:system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL一般来说,保证查询至少达到 range 级别,最好能达到 ref。除了all之外,其他的type都可以使用到索引,除了index_merge之外,其他的type只可以用到一个索引。

  • system: 表只有一行记录(等于系统表),这是const类型的特列,平时不会出现,也可以忽略不计。
  • const: 表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据,所以很快Dr如将主键置于where列表中,MySQL 就能将该查询转换为一个常量。
  • eq_ref: 唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一 索引扫描。
  • ref: 非唯一性索引扫描,返回匹配某个单独值的所有行。本质上也是一种索引访问,它返回所有匹配某个单独值的行,然而,它可能会找到多个符合条件的行,所以他应该属于查找和扫描的混合体。
  • fulltext:全文索引检索,要注意,全文索引的优先级很高,若全文索引和普通索引同时存在时,mysql不管代价,优先选择使用全文索引
  • ref_or_null:与ref方法类似,只是增加了null值的比较。实际用的不多。
    例如:
    SELECT * FROM ref_table
    WHERE key_column=expr OR key_column IS NULL;
  • index_merge:表示查询使用了两个以上的索引,最后取交集或者并集,常见and ,or的条件使用了不同的索引,官方排序这个在ref_or_null之后,但是实际上由于要读取所个索引,性能可能大部分时间都不如range
  • unique_subquery:用于where中的in形式子查询,子查询返回不重复值唯一值
  • index_subquery:用于in形式子查询使用到了辅助索引或者in常数列表,子查询可能返回重复值,可以使用索引将子查询去重。
  • range: 只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引,一般就是在你的where语句中出现了between、<、>、in等的查询这种范围扫描索引扫描比全表扫描要好,因为它只需要开始于索引的某一点, 而结束语另- -点,不用扫描全部索引。
  • index: Full Index Scan,index 与 ALL 区别是 index类型只遍历索引树。这通常比ALL快,因为索引文件通常比数据文件小。(也就是说虽然all和Index都是读全表,但index是 从索引中读取的,而all是 从硬盘中读的)
  • ALL: Full Table Scan,将遍历全表以找到匹配的行。

partitions

版本5.7以前,该项是explain partitions显示的选项,5.7以后成为了默认选项。该列显示的为分区表命中的分区情况。非分区表该字段为空(null)。

possible_keys

查询可能使用到的索引都会在这里列出来

key

查询真正使用到的索引,select_type为index_merge时,这里可能出现两个以上的索引,其他的select_type这里只会出现一个。

key_len

用于处理查询的索引长度,如果是单列索引,那就整个索引长度算进去,如果是多列索引,那么查询不一定都能使用到所有的列,具体使用到了多少个列的索引,这里就会计算进去,没有使用到的列,这里不会计算进去。留意下这个列的值,算一下你的多列索引总长度就知道有没有使用到所有的列了。要注意,mysql的ICP特性使用到的索引不会计入其中。另外,key_len只计算where条件用到的索引长度,而排序和分组就算用到了索引,也不会计算到key_len中。

ref

如果是使用的常数等值查询,这里会显示const,如果是连接查询,被驱动表的执行计划这里会显示驱动表的关联字段,如果是条件使用了表达式或者函数,或者条件列发生了内部隐式转换,这里可能显示为func

rows

这里是执行计划中估算的扫描行数,不是精确值

Extra

  • Using filesort:说明MySQL会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序成为“文件排序”。
  • Using temporary:使用了临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询 group by。
  • Using index:表示相应的select操作中使用了覆盖索引(Covering Index), 避免访问了表的数据行,效率不错!如果同时出现 usingwhere,表明索引被用来执行索引键值的查找;如果没有同时出现 usingwhere,表明索引用来读取数据而非执行查找动作。
  • Using where:表明使用了 where 过滤。
  • Using join buffer:使用了连接缓存。
  • Impossible where:where 字句的值总是false,不能用来获取任何元素。
  • select tables optimized away:在没有 group by 字句的情况下,基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作,不必等到执行阶段在进行计算,查询执行计划生成的阶段即完成优化。
  • distinct:优化 distinct 操作,在找到第一匹配的元素后即停止找同样值的动作。

慢查询日志

MySQL的慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阈值的语句,具体指运行时间超过 long_query_time 值 的SQL,则会被记录到慢查询日志中。

运行时间超过 long_query_time 值的SQL,会被记录到慢查询日志中,默认关闭,调优需要手动开启。
long_ query_ time的默认值为10,意思是运行10秒以上的语句。由他来查看哪些SQL超出了我们的最大忍耐时间值,比如一条sql执行超过5秒钟,我们就算慢SQL,希望能收集超过5秒的sql,结合之前explain进行全面分析。

slow_query_log

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<!--如何查看-->
SHOW VARIABLES LIKE '%slow_query_log%';

<!--如何开启 下面命令只对当前数据库有效-->
set global slow_query_log=1;

<!--永久生效 修改my.cnf[mysqld]下增加或修改参数-->
slow_query_log=1
slow_query_log_file=host_name-slow.log



<!--SQL语句健康查询-->
show global status like 'Slow_queries';

long_query_time

1
2
3
4
5
6
7
8
<!--是大于 > 10秒 而非 大于等于 >= 10-->
SHOW VARIABLES LIKE '%long_query_time%';

<!--设置时间-->
set global long_query_time=3;

<!--设置后可能时间看不到变化 需要用 global 或者重新连接或新开一个会话才能看到修改值-->
show global VARIABLES like 'long_query_time';

Slow_queries

1
2
<!--SQL语句健康查询-->
show global status like 'Slow_queries';

mysqldumpslow –help 慢查询工具

  • s:是表示按照何种方式排序。
  • c:访问次数。
  • l:锁定时间。
  • r:返回记录。
  • t:查询时间。
  • al:平均锁定时间。
  • ar:平均返回记录数。
  • at:平均查询时间。
  • t:即为返回前面多少条的数据。
  • g:后边搭配一个正则匹配模式,大小写不敏感的。

全局查询日志

注:生产环境不开启这个功能

1
2
3
4
5
6
7
8
<!--开启功能,默认关闭-->
set global general_log=1;

<!--输出到表-->
set global log_output='TABLE';

<!--查看-->
select * from mysql.general_log;
0%