第四章 MySQL 索引及执行计划

1.SQL基础优化-索引及执行计划

• 1.1 什么是索引?
  相当于一本书中的目录,用来加快查询.
• 1.2 索引算法的演变

二叉树 – > 红黑树 – > BTREE – > B+TREE(B*TREE)

• 1.3 MySQL支持的索引类型
  B+TREE HASH RTREE FullTEXT GIS索引
• 1.5 BTREE查找算法介绍

(B-TREE)
(B+TREE): 叶子节点,有相邻指针
(B*TREE) :叶子节点和枝节点,有相邻的指针

• 1.6 RTREE如何构建
  • 聚簇索引:
    生成条件:
    自动选择主键列(PK),没有主键会自动选择UK,如果都没有自动生成隐藏列 InnoDB 才有聚簇索引.
    功能:
    1.数据存储时,按照聚簇索引列顺序在磁盘上有序的存储在连续数据页上(16K)---->索引组织表(IOT)
    构建:
    1.将有序的整表数据行所在数据页,作为叶子节点
    2.按照聚簇索引列值,向上生成枝节点和根节点
    查询:
    按照聚簇索引列作为查询条件时,等值查询,发生3次IO即可获得数据行
如果是范围查询,利用叶子节点双向指针继续优化查询.
  • 辅助索引: alter table t1 add index idx(name);
    构建:
    1.提取name列值+ID列值,按照name列值的升序排序
    2.将排好序的数据,均匀的,有序的存储到叶子节点中
    3.通过name的值向上生成枝节点和根节点.
    查询:
    1.按照name作为查询条件时,遍历辅助索引树,得到PK
    2.拿着PK的进行回表查询
• 总结:
  1.减少查询行数
  2.减少IO的次数
  3.等值,缩小范围
  4.尽量使用聚簇索引查询
  5.减少回表次数
  联合索引使用:减少ID的个数
  覆盖索引: 辅助索引中有所有要查询的值

2. 索引管理

• 2.1 索引的查询

  desc city;
  show index from city;
  

• 2.2 创建索引(辅助索引)

  (1) 单列索引
  创建: alter table city add index idx_na(name);
  删除: alter table city drop index idx_na;
  (2) 唯一索引
  增: alter table xxxx add unique index xxx(列);
  删: alter table xxxx drop index xxx;
  (3)前缀索引 
  增: alter table city add index idx_na(name(10));
  删: alter table city drop index idx_na;
  例如: city表中,name列要做前缀索引,判断多少个字符合适
  总行数:
  select count(*) from world.city ;   ----> 4079 
  提取name的前6个字符
  select count(distinct (left(name,6)) from world.city ; ---> 3775
  尽量取前缀最小
  

• 2.3 影响索引树的高度
  1.行数多
  解决: 拆分表: 归档表,分区表,分布式数据库
  2.索引列值过长.
  解决: 前缀索引
  3.数据类型影响
  解决:
  (1)简短数据类型替代长繁琐数据类型.
  (2)变长最好是varchar()类型.
  (3)enum()灵活应用.
  (5)联合索引

• 2.4 联合索引(a,b,c)
  1.构建时,取出id+a+b+c,依次a-->b--->c 的顺序,进行数据行的排序
  2.枝节点和根节点,只会保存最左列的索引值.
  3.联合索引查询时,遵循最左原则.最左列尽量使用重复值少的列.
  4.把控一个原则: 建立了联合索引,尽量应用完整

3. 执行计划获取及分析

• 目的: 在SQL执行之前,将优化器选择后的执行计划获取出来进行分析

  ####结构: explain
  mysql> desc select * from city where countrycode='CHN';
  +----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
  | id | select_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
  +----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
  |  1 | SIMPLE      | city  | NULL       | ref  | CountryCode   | CountryCode | 3       | const |  363 |   100.00 | NULL  |
  +----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
  1 row in set, 1 warning (0.00 sec)
    
  1. table            主要针对多表比较有意义.
  2. type             查询类型  *****
  3. possible_keys    可能会用到的索引. CBO(代价),RBO(规则)***  
  5. key              真正用到的索引
  6. key_len          索引覆盖长度,主要是用来确认联合索引覆盖长度 *****
  7. rows             查询结果集行数.
  8. Extra            额外信息  *****
  

4. 重要信息详解

• 4.1 type详解

  • ALL : 全表扫描
    (1)查询条件没有索引
    (2) 查询条件不满足索引应用规则 查询条件是不确认值时,不走索引

  • index : 全索引扫描

  • range: 索引的范围扫描

  • ref : 辅助索引等值查询

  • eq_ref : 多表连接时,右表中的on的连接条件是主键或唯一键

  • const(system): 聚簇索引或者唯一索引的等值查询

• 4.2 key_len 计算规则
  指的是在联合索引应用长度

数据类型	not null	null
tinyint	1bytes	1+1
int	4bytes	4+1
utf8
char(10)	30	30+1
varchar(10)	30+2	30+2+1
utf8mb4
char(10)	40	40+1
varchar(10)	40+2	40+2+1

• 4.3 联合索引应用规则:
  1.把唯一值多的放在最前面
  2.理论上要将不等值的列放在最后面
  3.key_len 覆盖长度越长越好.
  5.查询条件结果能够全部从辅助索引覆盖最好,减少回表次数.
  6.where配合groupby 或者order by ,按照子句执行顺序联合索引.
  
  

5. 建立索引的规则

1.建表时一定要有主键,一般是个无关列
2.选择唯一性索引,联合索引中放在最左侧
3.尽量使用前缀来索引 (a(10),b(5),c(8))
4.限制索引的数目,删除不再使用或者很少使用的索引(percona toolkit)
5.大表加索引,要在业务不繁忙期间操作.
6.尽量少在经常更新值的列上建索引

6. 不走索引的情况

1 没有查询条件，或者查询条件没有建立索引.

优化方法: select *from city where id> 或者 缓存

2.查询结果集是原表中的大部分数据，应该是25％以上。
3.索引本身失效，统计数据不真实
4.查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等)
5.隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.(字符类型要加引号)
6.<> ，not in 不走索引（辅助索引）,like '%a'
说明: 对于主键列,也是可以走range范围查询.

• 补充:

  压力测试
  mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k2='780P'" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose