MYSQL 索引及执行计划 - 《MySQL》

MYSQL 索引及执行计划
五索引
运行学习环境??学习环境.txt
或者直接下载 wget https://gitee.com/zheguabaoshu/my-sql/repository/archive/master.zip

5.1 索引作用
提供了类似于书中目录的作用,目的是为了优化查询

5.2 索引的种类(算法)
2.1 算法种类： B树索引 Hash索引 R树 Full text GIS 2.2 btree算法：遍历——>二叉树——->平衡二叉树——> Balance Tree2.2 Btree查找算法引入见图。
B树算法图解
MYSQL 索引及执行计划 - 图1
5.3 B树算法普及
B-tree —-> B+tree —-> B*Tree

B+ 树图解（ 在范围查询方面提供了更好的性能 (> < >= <= like) 在leaf节点和枝节点加入了双向指针 )
MYSQL 索引及执行计划 - 图2

建立索引效果：
MYSQL 索引及执行计划 - 图3
5.4 在功能上的分类
5.4.1 辅助索引(S)怎么构建B树结构的?
(1)辅助索引是基于表的列进行生成的 (2)取出索引列的所有值（取出所有键值） (3)进行所有键值的排序 (4)将所有的键值按顺序落到BTree索引的叶子节点上 (5)进而生成枝节点和根节点 (6)叶子节点除了存储键值之外，还存储了相邻叶子节点的指针，另外还会保存原表数据的指针

5.4.2 聚簇索引(C)怎么构建B树结构的?
(1) 建表时有主键列（ID） (2) 表中进行数据存储，会按照ID列的顺序，有序的存储一行一行的数据到数据页上(这个动作叫做聚集索引组织表) (3) 表的数据页被作为聚集索引的叶子节点 (4) 把叶子节点的主键值生成上层枝节点和根节点。

5.4.3 ??聚簇索引和辅助索引构成区别总结
聚集索引只能有一个,非空唯一,一般时主键辅助索引,可以有多个,是配合聚集索引使用的聚集索引叶子节点,就是磁盘的数据行存储的数据页 MySQL是根据聚集索引,组织存储数据,数据存储时就是按照聚集索引的顺序进行存储数据辅助索引,只会提取索引键值,进行自动排序生成B树结构

5.4.4 辅助索引细分
单列的辅助索引联合多列辅助索引(覆盖索引) 唯一索引

5.4.5 关于索引树的高度受什么影响
（1）数据行多，分表（2）索引列字符长度，前缀索引（3）char varchar ，表设计（4）enum 优化索引高度，能用则用。

5.5 索引的管理操作
alter table t100w add index idx_k2(k2);desc t100w show index from t100w\Galter table t100w add unique index idx_k1(k1);alter table city add index idx_name(name(5));alter table city add index idx_co_po(countrycode,population);alter table city drop index idx_co_po; #删除

5.6 执行计划
5.6.1 作用
上线新的查询语句之前，进行提前预估语句的性能在出现性能问题时，找到合理的解决思路

5.6.2 执行计划获取
mysql> desc select from oldboy.t100w where k2=’EF12’\G** 1. row * id: 1 select_type: SIMPLE table: t100w partitions: NULL type: ref possible_keys: idx_k2 key: idx_k2 key_len: 17 ref: const rows: 293 filtered: 100.00 Extra: NULL1 row in set, 1 warning (0.00 sec) table: t100w type: ref 索引的应用级别 possible_keys: idx_k2 可能会使用到的索引 key: idx_k2 实际上使用的索引 key_len: 17 联合索引覆盖长度 rows: 293 查询的行数（越少越好） Extra: NULL 额外的信息

5.6.3 执行计划的分析
type 索引的应用级别 ALL : 全表扫描，不走索引没建立索引！！建立索引不走的()！！！！ mysql> desc select from t100w; mysql> desc select from t100w where k1=’aa’; （辅助索引） mysql> desc select from t100w where k2 != ‘aaaa’; mysql> desc select from t100w where k2 like ‘%xt%’; Index ：全索引扫描 mysql> desc select k2 from t100w; range ：索引范围扫描辅助索引 : > < >= <= like , in or 主键： != mysql> desc select from world.city where countrycode like ‘C%’mysql> desc select from world.city where id!=3000; mysql> desc select from world.city where id>3000; mysql> desc select from world.city where countrycode in (‘CHN’,’USA’); 改写为：descselect from world.city where countrycode=’CHN’union all select from world.city where countrycode=’USA’; ref : 辅助索引等值查询 mysql> desc select from city where countrycode=’CHN’; eq_ref ：在多表连接查询是on的条件列是唯一索引或主键 mysql> desc select a.name,b.name ,b.surfacearea from city as a join country as b on a.countrycode=b.code where a.population <100; const,system ：主键或唯一键等值查询 mysql> DESC SELECT from city where id=10; Extra: NULL 额外的信息 using filesort mysql> alter table city add index idx_co_po(countrycode,population);

5.6.4 explain(desc)使用场景（面试题）
题目意思: 我们公司业务慢,请你从数据库的角度分析原因1.mysql出现性能问题,我总结有两种情况: （1）应急性的慢：突然夯住应急情况:数据库hang(卡了,资源耗尽)处理过程:1.show processlist; 获取到导致数据库hang的语句2. explain 分析SQL的执行计划,有没有走索引,索引的类型情况3. 建索引,改语句（2）一段时间慢(持续性的):1. 记录慢日志slowlog,分析slowlog2. explain 分析SQL的执行计划,有没有走索引,索引的类型情况3. 建索引,改语句

5.7 索引应用规范
5.7.1 建立索引的原则（DBA运维规范）
业务1.产品的功能2.用户的行为”热”查询语句 —->较慢—->slowlog”热”数据 8.1 建立索引的原则（DBA运维规范）8.1.0 说明为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。那么索引设计原则又是怎样的?8.1.1 (必须的) 建表时一定要有主键,一般是个无关列略.回顾一下,聚集索引结构. 8.1.2 选择唯一性索引唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名现象，从而降低查询速度。优化方案:(1) 如果非得使用重复值较多的列作为查询条件(例如:男女),可以将表逻辑拆分(2) 可以将此列和其他的查询类,做联和索引select count() from world.city;select count(distinct countrycode) from world.city;select count(distinct countrycode,population ) from world.city; 8.1.3(必须的) 为经常需要where 、ORDER BY、GROUP BY,join on等操作的字段，排序操作会浪费很多时间。where A B C ——》 A B Cin where A group by B order by C A,B，C where A、GROUP BY B、ORDER BY C1. 联合索引 2. (A,B,C) 如果为其建立索引，优化查询注：如果经常作为条件的列，重复值特别多，可以建立联合索引。 8.1.4 尽量使用前缀来索引如果索引字段的值很长，最好使用值的前缀来索引。 8.1.5 限制索引的数目索引的数目不是越多越好。可能会产生的问题:(1) 每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。(2) 修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。(3) 优化器的负担会很重,有可能会影响到优化器的选择. percona-toolkit中有个工具,专门分析索引是否有用 8.1.6 删除不再使用或者很少使用的索引(percona toolkit)pt-duplicate-key-checker 表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。8.1.7 大表加索引,要在业务不繁忙期间操作8.1.8 尽量少在经常更新值的列上建索引 8.1.9 建索引原则(1) 必须要有主键,如果没有可以做为主键条件的列,创建无关列(2) 经常做为where条件列 order by group by join on, distinct 的条件(业务:产品功能+用户行为)(3) 最好使用唯一值多的列作为索引,如果索引列重复值较多,可以考虑使用联合索引(4) 列值长度较长的索引列,我们建议使用前缀索引.(5) 降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit(xxxxx)(6) 索引维护要避开业务繁忙期 8.10 关于联合索引 **(1) where A、GROUP BY B、ORDER BY C —-> (A,B,C)(2) where A B C (2.1) 都是等值 ,在5.5 以后无关索引顺序，把控一个原则唯一值多的列放在联合索引的最左侧 (2.2) 如果有不等值，例如以下情况 select where A= and B> and C= 索引顺序，ACB ，语句改写为 AC

5.7.2 不走索引的情况（开发规范）
88.2 不走索引的情况（开发规范） 8.2.1 没有查询条件，或者查询条件没有建立索引 select from tab; 全表扫描。 select from tab where 1=1; 在业务数据库中，特别是数据量比较大的表。是没有全表扫描这种需求。 1、对用户查看是非常痛苦的。 2、对服务器来讲毁灭性的。（1） select from tab; SQL改写成以下语句： select from tab order by price limit 10 ; 需要在price列上建立索引（2） select from tab where name=’zhangsan’ name列没有索引改： 1、换成有索引的列作为查询条件 2、将name列建立索引 8.2.2 查询结果集是原表中的大部分数据，应该是25％以上。查询的结果集，超过了总数行数25%，优化器觉得就没有必要走索引了。假如：tab表 id，name id:1-100w ，id列有(辅助)索引 select from tab where id>500000; 如果业务允许，可以使用limit控制。怎么改写？结合业务判断，有没有更好的方式。如果没有更好的改写方案尽量不要在mysql存放这个数据了。放到redis里面。 8.2.3 索引本身失效，统计数据不真实索引有自我维护的能力。对于表内容变化比较频繁的情况下，有可能会出现索引失效。一般是删除重建现象: 有一条select语句平常查询时很快,突然有一天很慢,会是什么原因 select? —->索引失效,，统计数据不真实 DML ? —->锁冲突 8.2.4 查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，，/，! 等) 例子：错误的例子：select from test where id-1=9; 正确的例子：select from test where id=10; 算术运算函数运算子查询 8.2.5 隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误. 这样会导致索引失效. 错误的例子： mysql> alter table tab add index inx_tel(telnum); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> mysql> desc tab; +————+——————-+———+——-+————-+———-+ | Field | Type | Null | Key | Default | Extra | +————+——————-+———+——-+————-+———-+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | telnum | varchar(20) | YES | MUL | NULL | | +————+——————-+———+——-+————-+———-+ 3 rows in set (0.01 sec) mysql> select from tab where telnum=’1333333’; +———+———+————-+ | id | name | telnum | +———+———+————-+ | 1 | a | 1333333 | +———+———+————-+ 1 row in set (0.00 sec) mysql> select from tab where telnum=1333333; +———+———+————-+ | id | name | telnum | +———+———+————-+ | 1 | a | 1333333 | +———+———+————-+ 1 row in set (0.00 sec) mysql> explain select from tab where telnum=’1333333’; +——+——————-+———-+———+———————-+————-+————-+———-+———+———————————-+ | id | selecttype | table | type | possible_keys | key | key_len | ref | rows | Extra | +——+——————-+———-+———+———————-+————-+————-+———-+———+———————————-+ | 1 | SIMPLE | tab | ref | inx_tel | inx_tel | 63 | const | 1 | Using index condition | +——+——————-+———-+———+———————-+————-+————-+———-+———+———————————-+ 1 row in set (0.00 sec) mysql> explain select from tab where telnum=1333333; +——+——————-+———-+———+———————-+———+————-+———+———+——————-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +——+——————-+———-+———+———————-+———+————-+———+———+——————-+ | 1 | SIMPLE | tab | ALL | inx_tel | NULL | NULL | NULL | 2 | Using where | +——+——————-+———-+———+———————-+———+————-+———+———+——————-+ 1 row in set (0.00 sec) mysql> explain select from tab where telnum=1555555; +——+——————-+———-+———+———————-+———+————-+———+———+——————-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +——+——————-+———-+———+———————-+———+————-+———+———+——————-+ | 1 | SIMPLE | tab | ALL | inx_tel | NULL | NULL | NULL | 2 | Using where | +——+——————-+———-+———+———————-+———+————-+———+———+——————-+ 1 row in set (0.00 sec) mysql> explain select from tab where telnum=’1555555’; +——+——————-+———-+———+———————-+————-+————-+———-+———+———————————-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +——+——————-+———-+———+———————-+————-+————-+———-+———+———————————-+ | 1 | SIMPLE | tab | ref | inx_tel | inx_tel | 63 | const | 1 | Using index condition | +——+——————-+———-+———+———————-+————-+————-+———-+———+———————————-+ 1 row in set (0.00 sec) mysql> 8.2.6 <> ，not in 不走索引（辅助索引） EXPLAIN SELECT FROM teltab WHERE telnum <> ‘110’; EXPLAIN SELECT FROM teltab WHERE telnum NOT IN (‘110’,’119’); mysql> select from tab where telnum <> ‘1555555’; +———+———+————-+ | id | name | telnum | +———+———+————-+ | 1 | a | 1333333 | +———+———+————-+ 1 row in set (0.00 sec) mysql> explain select from tab where telnum <> ‘1555555’; 单独的>,<,in 有可能走，也有可能不走，和结果集有关，尽量结合业务添加limit or或in 尽量改成union EXPLAIN SELECT FROM teltab WHERE telnum IN (‘110’,’119’); 改写成： EXPLAIN SELECT FROM teltab WHERE telnum=’110’ UNION ALL SELECT FROM teltab WHERE telnum=’119’ 8.2.7 like “%“ 百分号在最前面不走 EXPLAIN SELECT FROM teltab WHERE telnum LIKE ‘31%’ 走range索引扫描 EXPLAIN SELECT FROM teltab WHERE telnum LIKE ‘%110’ 不走索引 %linux%类的搜索需求，可以使用elasticsearch+mongodb 专门做搜索服务的数据库产品