MySQL索引与EXPLAIN查看执行计划与慢查询的开启

一. MySQL 索引概述

假设执行一条查询语句,在没有索引的情况下默认会通过全表搜秒,将获取到的数据与查询语句中的查询条件进行判断,假设表中的数据时百万级,效率会非常慢,所以创建索引,通过索引查询

1. 可以简单理解为为了提高查询效率,给添加一个或多个索引,创建一个像目录一样的索引文件,是存在硬盘上的
2. mysql 索引数据结构支持 :hash, 平衡二叉树, b树, b+树等,通常采用b+树实现
3. Linux查看索引文件: /var/lib/mysql
   

mysql 服务器查询数据的执行过程

二. 索引算法简单解释

1. hash

以Map集合hash为例来说明,调用hashCode()方法获取到一个唯一的hash值,根据该hash值确定当前数据在集合中的存放位置,数据库中当查询数据时直接通过hash值去获取,提高查询效率
问题: 在存储数据时发生hash冲突,需要全表hash,重新计算每个数据的hash值,重新排列,hash只满足"=","IN”和“<=>”查询,不能范围查询,因为经过hash算法处理后的hash值的大小关系，并不能保证与处理前的hash大小关系对应,由于这个原因,也无法通过hash对数据进行排序,并且无法进行组合索引

2. 平衡二叉树

1. 每个节点最多有两个子树的树结构,“左子树”和“右子树”,获取一个中间值为跟节点,后续按照数据的大小与中间值进行比对将数据放入二叉树的指定节点上,在查询时有点像二分查找,折半查找提高查询效率
2. 通过下图解释查询过程,假设查询10,会执行四次查询,有四次IO操作(io操作读取硬盘数据到内存中的动作,第一次读取硬盘数据到内存中4判断大于4,第二次读取到8判断大于8----->最终读取到10)
3. 平衡二叉树支持范围查询,但是有个回旋动作,假设查询大于5的数据,首先会查询到5,然后一个一个的回旋判断6,7,8----一直到获取到所有的大于5的数据,效率比较低
   

3. b树

在平衡二叉树基础上进行了改进,在平衡二叉树中,树的高度决定了查询时的io次数,b树的一个节点上可以存放多个数据,减少数据高度,减少io操作(下图中查询10只需要2次io操作),范围查询时与平衡二叉树相同需要回旋,效率较低

4. b+树

在b树的基础上区分叶子节点与非叶子节点,将数据分为key—value的形式,所有数据保存在叶子节点上,并且叶子节点中使用链表对所有数据进行了排序,在生成树时,非叶子节点上只存储了该数据该数据的key,通过该key可以直接在叶子节点上找到该数据,由于使用链表进行了排序在范围查询时不需要回旋,该范围以前的就是大于,以后的就是小于

三. MyISAM 与 InnoDB

1. mysql5.5之前使用MyISAM 作为搜索引擎,5.5以后使用InnoDB 做为搜索引擎,两个都是使用B+树,做为索引数据结构,
2. 不同的是,

• MyISAM 不支持事物,不支持外键,不支持行锁,InnoDB 支持事物,支持外键,支持行锁,在使用InnoDB时,会自动添加一个主键索引
• MyISAM 使用稀疏索引叶子节点value中存放的是指向当前数据的地址值,InnoDB使用密集索引叶子节点中value直接存放的是当前数据

3. 密集索引与稀疏索引

• 密集索引文件中的每一个搜索码值都对应着一个索引值 这就可以理解为叶子结点不但需要保存键值 还保存位于同一行记录的其他列信息。由于密集索引决定了表的物理排列顺序，一个表只有一个物理排列顺序，所以一个表只能创建一个密集索引。
• 稀疏索引文件中只为搜索码的某些值建立索引项 这可以理解为叶子结点只保存了键位信息以及该行数据的地址，有的稀疏索引只保存了键位信息主键
• 两种索引的优缺点: a 稀疏索引占用的索引存储空间比较小，但是查找时间较长；b 稠密索引查找时间较短，索引存储空间较大。
• 在MyISAM中 不管是主键索引 唯一键索引 普通索引 其索引都属于稀疏索引
• 在Innodb中 有且仅有一个密集索引 Innodb中选取规则：
  (1)、如果一个主键被定义了 则该主键作为密集索引
  (2)、若该主键没有被定义 则该表的第一个唯一非空索引作为密集索引
  (3)、若不满足上述条件 则innodb内部会生成一个隐藏主键(密集索引)
  (4)、非主键索引存储相关键位和其对应的主键值 包含两次查找

5. 通过安装的mysql中my.ini配置文件配置搜索引擎: default-storage-engine=INNODB
   
6. 创建表时添加innodb搜索引擎: CREATE TABLE ‘表名’( ‘列明’ 数据类型 其它校验,…,primary key(‘主键字段’)) ENGINE=InnoDB DEAULT CHARSET=utf8;
7. 手动修改搜索引擎 : ALERT TABLE 表名 ENGINE=InnoDB

四. 索引的创建

索引分为主键引,唯一索引,全文索引, 多列组合索引, 普通索引,

1. 主键索引: ALTER TABLE 表明 ADD PRIMARY KEY ( 主键字段 )
2. 唯一索引: ALTER TABLE table_name ADD UNIQUE ( column )
3. 全文索引: ALTER TABLE table_name ADD FULLTEXT ( column)
4. 多列组合索引: ALTER TABLE table_name ADD INDEX index_name ( column1, column2, column3 )
5. 普通索引: ALTER TABLE table_name ADD INDEX index_name ( column )

索引的设计规范

1. 限制表中的索引数量,并不是越多越好,建议不超过5个(mysql在查询时首先会预估是否能命中,如果不能命中会使用优化器根据索引进行评估生成一个最好的执行计划,如果索引过多时,会增加mysql优化器生成执行计划的时间)
2. Innodb是按照主键索引的顺序来组织表的(要求每个Innodb表都有一个主键,如果没有主键Innodb会优先选择第一个非空,唯一的列作为主键,如果没有非空且唯一的列,Innodb默认会生成一个有六个字节的主键,但是该主键性能不是最好的)
3. 常见增加索引列建议(主键索引以外的)创建在select,update语句的where字段,或包含在order by, group by, distince 等字段上创建,或join的关联列上
4. 区分度最高的列放在联合索引的最左侧—>字段长度小的列放在联合索引的最左侧—>使用最频繁的列放在联合索引的最左侧(左前缀原则,原因是多个列排列创建联合索引,索引底层采用B+树数据结构,假设在查询时,where条件中左侧第一个列没有使用索引,或者使用了联合索引中非最左侧的列,通过该索引key找不到子节点,所以会走全表扫描)
5. 避免重复索引,冗余索引
6. 对于频繁查询的数据优先考虑使用覆盖索引
7. 尽量避免使用外键(通过业务去关联)

EXPLAIN 查询sql执行计划

1. EXPLAIN SELECT * FROM 表名 通过EXPLAIN+SQL语句,查看该语句的执行计划,执行顺序,可以获取到当前执行的SQL语句的查询耗时时间,索引是否生效,使用了几个索引等…,分析优进行优化,借用大神整理的EXPLAIN用法

#where条件中snapshot_id 为主键,通过EXPLAIN分析执行计划中的type为const,possible_keys为PRIMARY 使用主键索引
EXPLAIN SELECT * FROM express_customer_snapshot where snapshot_id='123456';
#where条件中'customer_name'字段添加了普通索引,EXPLAIN查看执行计划type为 ref, possible_keys 为index_name
EXPLAIN SELECT * FROM express_customer_snapshot where customer_name='bbb';
#查看EXPLAIN执行计划,possible_keys为'PRIMARY,index_name'使用了两个索引
EXPLAIN SELECT * FROM express_customer_snapshot where snapshot_id='123456' and customer_name='bbb';
#查看EXPLAIN执行计划,where条件字段中没有添加索引,type为ALL全表扫描
EXPLAIN SELECT * FROM express_customer_snapshot where operate_user_id='ccc';

2. 示例:
   
3. EXPLAIN 执行计划字段解释

• id: 当前sql语句的执行顺序,值越大越先执行,如果两个值相同按照从上向下顺序执行
• select_type; 当前sql语句的查询类型
  simple 表示不包含子查询或union的普通查询
  primary 表示当前查询中包含复杂查询,最外层查询被标记为primary
  subquery: 表示当前查询中where中包含子查询
  derived: 表示当前查询form中包含的子查询被标记为derived,会递归执行子查询,将数据放入一个临时表中
  union: 当前查询语句中包含union,union后的sql语句被标记为union
  union result: 从union表中获取结果的select语句
• table: 当前sql语句查询的是哪一张表的数据
• type: 表示当前执行的sql使用的是那种查询类型,从好到差: system>const>eq_ref>range>index>all,一般来至少保证能到到range
  system: 表示只有一行记录,是const特殊种类一般不会出现
  const: 表示通过索引一次就找到了数据,例如使用主键索引时,例如where主键字段
  eq_ref: 表示通过唯一索引执行
  ref: 表示通过非唯一索引执行
  range: 只检索给定范围的行,例如in,between adn
  index: 通过索引进行全表扫描
  all: 遍历全表数据进行查询
• possible_keys: 表示当前sql用到的索引个数(一个或多个,但不一定实际应用到)
• key: 当前执行sql实际应用到的索引,如果为null则表示没有用到
• key_len: 标识索引中使用的字节数,可通过该值计算当前使用的索引长度,在保证精度的情况下长度越小越好
• ref: 显示索引的那一列被使用了
• rows: 根据表统计信息及索引使用情况,估算找到所需数据要读取的数据行数
• extra: 包含不使用在其他列中显示但十分重要的额外信息
  using filesort: 说明在执行时会使用外部的索引排序,而不是按照表内的索引顺序进行读取
  using temproary: 使用了临时表保存中间结果,例如order by. group by
  using index: 表示相应的select操作中使用了覆盖索引,避免了访问数据时

4. 索引失效的几种情况

• like 模糊查询,以%开头的会造成索引失效,建议使用覆盖索引,select 覆盖索引字段
• ro 或条件的会造成索引失效,使用 union或union all
  索引失效: select id from t where num=10 or num=20
  修改为: select id from t where num=10 union all select id from t where num=20
• 在某些时刻in也会造成索引失效,使用 exists代替in
• where字符串字段,不添加引号会造成索引失效
• where条件中在使用不等于（!= 或 <>）时无法使用索引
• where条件中包含运算包括(+，-，，/，! 等) 会造成索引失效,例如查询年薪大于20000的员工信息
  索引失效sql示例where的条件字段进行了运算: select 员工信息字段 from 员工表 where 月薪12 > 20000;
  修改索引生效: select 员工信息字段 from 员工表 where 月薪>(20000/12)
• innodb引擎时is null会造成索引失效(is not null 不会)原因null值不会像其他取值一样出现在索引树的叶子节点上,使用, 修改为left join关联的方式减少对子表的查询

五. MySQL 慢查询

1. 什么是慢查询: 假设执行一条查询sql语句,在指定时间内没有返回执行结果,说明查询过慢,对这条执行语句进行日志记录,慢查询就是指记录的这个日志
2. 查询当前mysql服务器慢查询的配置(默认是关闭,开启后默认10未返回结果进行慢查询日志记录): show variables like ‘slow_query%’ 下方的’slow_query_log_file’ 文件路径就是记录慢查询日志的文件
   
3. 开启慢查询: SET GLOBAL SLOW_QUERY_LOG=‘ON’;
   
4. 查询慢查询记录日志时间: SHOW VARIABLES LIKE ‘long_query_time’;
   
5. 修改记录慢查询日志的触发时间(多长时间内未返回结果进行记录,注意点由于保持当前会话,修改完毕后需要重启数据库连接,再去查看才会生效): SET GLOBAL LONG_QUERY_TIME=5;
   
6. 开启慢查询以后可以使用pt-query-digest或Mysqdumpslow工具对慢查询日志进行分析,对sql进行优化处理