索引及执行计划

第1章 索引

1.1 索引是什么

索引是一种有序的快速查找数据结构

1.2 索引的作用

提供了类似书的目录的作用，目的是为了优化查询

1.3 索引的优点和缺点

优点

1.通过创建唯一性索引，可以保证数据库表中的每一行数据的唯一性。
2.可以加快数据的检索速度
3.可以加速表与表之间的连接
4.在使用分组和排序进行检索的时候，可以减少查询中分组和排序的时间

缺点

1.创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
2.索引需要占用物理空间，数据量越大，占用空间越大
3.会降低表的增删改的效率，因为每次增删改索引，都需要进行动态维护

1.4 什么时候需要创建索引

 1.主键自动建立唯一索引
 2.频繁作为查询条件的字段应该创建索引
 3.查询中排序的字段创建索引将大大提高排序的速度（索引就是排序加快速查找)
 4.查询中统计或者分组的字段；

1.5 什么时候不需要创建索引

1. 频繁更新的字段不适合创建索引，因为每次更新不单单是更新记录，还会更新索引，保存索引文件where条件里用不到的字段，不创建索引；
2. 表记录太少，不需要创建索引；
3. 经常增删改的表；
4. 数据重复且分布平均的字段，因此为经常查询的和经常排序的字段建立索引。注意某些数据包含大量重复数据，因此他建立    索引就没有太大的效果，例如性别字段，只有男女，不适合建立索引。

1.6MySQL中索引的种类

1. B树(默认使用的索引)
2. R树
3. Hash
4. GIS
5. FullText

第2章 MySQL中索引是怎么构建B树的

1.1 辅助索引构建BTree过程

1. 将索引列值提取出来进行排序
2. 将排好序的值，有序的存入叶子节点，进而生成枝节点和根节点
3. 叶子节点会存储对应的聚集索引的主键值

1.2 聚集索引构建BTree过程

1. MySQL会自动选择主建作为聚集索引，如果没有会选择唯一键，如果2者都没有会自动生成一个隐藏的
2. MySQL存储数据时，会按照聚集索引的顺序有序的存储
3. innodb会将数据行所在的数据页作为叶子节点，进而生成枝节点和根节点

1.3 聚集索引和辅助索引的区别

1. 一张表中只能有一个聚集索引，非空且唯一，一般为主键
2. 辅助索引可以有多个，只要有需求，名字不同即可
3. 辅助索引叶子节点存储的是索引列值+对应的主键值
4. 聚集索引叶子节点存储的是数据行所在的数据页
5. MySQL存储数据时按照聚集索引组织表存储的（所谓的聚集索引组织表就是表的数据时会按照聚集索引的顺序存储到数据      页上）

第3章 辅助索引的细分

1.1 分类

1. 普通的单列辅助索引
2. 联合索引           
多个列作为索引条件,生成索引树,理论上设计的好的,可以减少大量的回表查询
3.唯一索引
索引列的值都是唯一的

第4章 影响索引树的高度因素

1.1 注意事项

索引树的高度一般越低越好，一般维持在3~4层

1.2 数据量级

如果数据量级很大，解决办法是：
1.分表,定期归档数据
2.分库分表(分片),分布式架构

1.3 字段长度

解决办法:
1.如果业务允许，尽量在字段长度短的列上建立索引
2.业务不允许,采用前缀索引

1.4 数据类型

变长长度字符串,使用了char,解决方案:变长字符串使用varchar
enum类型的使用enum ('山东','河北','黑龙江','吉林','辽宁','陕西'......)
                                       1      2      3

第5章 索引的命令操作

1.1 查询表中的索引

1.1.1 第一种方法

mysql>show index from 表名;

1.1.2 第二种方法

mysql>desc 表名;

1.2 建立索引

1.2.1 单列索引的创建

mysql>create table 表名 add index 索引名(列名)；
alter table 表名 add index 索引名(要创建索的列名);

1.2.3 联合索引的创建

mysql>create table 表名 add index 索引名(列1，列2，列3.....);

1.2.4 唯一索引的创建

要在哪一列上创建唯一索引，首先要判断这一列是否能创建唯一索引
第一种判断方法:
思路=====直接创建，如果不报错则能创建
mysql>alter table 表名 add unique index 索引名(要创建索引的列)
第二种判断方法:
思路=====先统计一下这一列的个数，然后去重复，看2个结果是否一样，如果一行表示没有重复值，则可以建
mysql> select count(district) from city;
mysql> select count(distinct district) from city;

1.2.3 前缀索引的创建

用到前缀索引就是当前要创建索引列的字段过长所以就用前缀索引
例子:
mysql> alter table city add index idx_dis(district(5));

1.3 删除索引

msyql>alter table 表名 drop index 索引名;

第6章 执行计划

1.1 作用

将优化器选择后的执行计划截取出来,便于管理管判断语句得执行效率

1.2 如何获取执行计划

1.2.1 第一种方法

mysql>explan SQL语句;

1.2.2 第二种方法

mysql>desc SQL语句;

第7章 分析执行计划

1.1 介绍

(1)
获取到的是优化器选择完成的,它认为代价最小的执行计划.
作用: 语句执行前,先看执行计划信息,可以有效的防止性能较差的语句带来的性能问题.
如果业务中出现了慢语句，我们也需要借助此命令进行语句的评估，分析优化方案。
(2) select 获取数据的方法
1. 全表扫描(应当尽量避免,因为性能低)
2. 索引扫描
3. 获取不到数据

1.2 执行计划的获取

mysql>  explain select * from t100w where id=9000G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t100w        --->查询的表名
   partitions: NULL
         type: ALL          --->查询的类型
possible_keys: NULL         --->可能走的索引
          key: NULL         --->走的索引名
      key_len: NULL         --->应用索引长度
          ref: NULL
         rows: 1027638
     filtered: 10.00
        Extra: Using where  --->额外信息
1 row in set, 1 warning (0.00 sec)

1.3 需要重点关注的信息

1.3.1 表名

对于多表连接查询的时候，有时候就是因为其中一张表有问题，所以我们要先查看是哪张表出了问题

1.3.2 查询的类型

1.3.2.1 MySQL查询只有2大类

第一种：全表扫描(all来表示)----全表扫描,不走索引
第二种：索引扫描(分为以下一些级别)
index--->range--->ref--->eq_ref--->const(system)--->null(从左到有性能依次编号)

1.3.2.2 查询类型之----index(全索引扫描)

mysql> explain select countrycode  from cityG
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: city
   partitions: NULL
         type: index
possible_keys: NULL
          key: CountryCode
      key_len: 3
          ref: NULL
         rows: 4188
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)
--说明
这种级别相对于全表扫描好一点，只是去遍历了索引列，但是也还是比较慢

1.3.2.3 查询类型之----range(范围扫描:> ,< ,>=, <= , between and ,or,in,like)

--说明
生产中索引优化的最低级别
mysql> eplain select * from city where id>2000;
mysql> explain select * from city where countrycode like 'CH%';
*****************************************************************************************************
--说明：以下语句需要改写的原因
mysql> desc select  * from city where countrycode='CHN' or countrycode='USA';
mysql> desc select  * from city where countrycode in ('CHN','USA');
--原因
由于B*树的叶子节点之间是双向指针，所以对于连续的值是有优化效果的，因此> < >= <= between and like 这种我们是可以接受的，但是in or 这种查询的值一般不是连续的，所以我们要改写为 union all
--改写后
select  * from city where countrycode='CHN' 
union all 
select  * from city where countrycode='USA';
--说明:
对于辅助索引来讲not in 、!= 是不走索引的
对于聚集索引来讲not in 、!=是走索引的，级别是range

1.3.2.4 查询类之----ref(辅助索引等值查询)

mysql> desc select * from city where countrycode = 'CHN';

1.3..2.5 查询类型之----eq_ref(只会出现在多表连接查询)

多表查询,右表中的连接列是主键或者唯一键索引.（只会出现在多表连接查询中）
mysql> desc select city.name,country.name  from city 
    -> join country
    -> on city.countrycode=country.code
    -> where city.population<100;

1.3.2.6 查询类型之---const(system):主键或者唯一键的等值查询

system:表只有一行记录，这是const类型的特例，平时不会出现，可以忽略不计
const:表示通过索引一次就找到了，通常出现在主键索引和唯一索引中
mysql> desc select * from  city where id=100;

1.3.3 possible_key

可能会用到的索引

1.3.4 key

真正用到的索引

1.3.5 Extra:包含不适合在其它列显示但十分重要的信息

第一种情况：USING  filesort(出现这种情况9死一生)
结论:
1．当我们在Extra位置看到using filesort,说明出现文件排序
2．观察需要排序(order by,group by ,distinct)有没有索引
3．根据子句的执行书序去创建联合索引
第二种情况：USING  temporary(出现这种情况，可能会导致数据库直接夯死)
使用了临时表保存中间结果，mysql在对查询结果排序时使用临时表，常见于排序order by 和分组查询group by
第三种情况：USING idex（出现这种情况，表名索引使用的非常好，查找数据很快）
表示相应的select操作中使用了覆盖索引，避免访问了表的数据行，效率不错
如果同时出现using where,表示索引被用来执行索引键值的查找
如果没有同时出现using where,表示索引用来读取数据而非执行查找动作

1.4 key_len(显示联合索引应用的长度)

1.4.1 key_len计算方式(计算长度和字符集有关)

1.4.2 索引覆盖长度(对于联合索引，越长越好)

例如：varchar(20) utf8mb4
表示意思如下：
1. 能存20个任意字符
2. 不管存储的是字符，数字，字母，1个字符预留的最大长度是4个字节
3. 对于中文1个字符占用4个字节
4. 对于数字和字母,1个实际占用大小是1个字节
可以通过select length(列名) from 表名；
如果此列中设置了非空
例：char(20) 此时长度应为：20*4
    varchar（20）此时长度为 ：20*4+2 （对于varchar而言，储存时varchar会有1个空间来存储字符的长度值，但是超过255个字符时会有2个空间来存储字符长度的数值，这里又是最大预留长度所以就要+2）
#计算时候那个1是来判断是否为非空的

第8章 联合索引应用细节

1.1 建立联合索引的前提原则

建立联合索引时,把握唯一值多的放在最左侧

1.2 当语句是等值查询时

这种情况下建立联合索引时不需要考虑的是条件的顺序，而是考虑哪个列的唯一值多，哪个放在最左
--说明
这种情况下，优化器会自动调整where的调整顺序

1.3 当语句中有不连续等值的条件查询时

cda   ----> acd   ---> a  -----> idx(c,d,a)  
dba   ----> abd   ---> ab ---->  idx(d,b,a)
查询这种不连续的时候，例如上面第一个:
索引只应用到了a，后面的都用不到，优化后idx(c.d.a)
对于此类不连续的，我们可以重新建索引来进行优化，但是这里要考虑的是新建的索引和原来的索引哪一个应用的更多，删除掉一个应用不是很多的索引

1.4 如果出现where条件中有不等值查询时

这种情况下索引只会走到不等值的条件位置，后面的都应用不到
优化建议:
1.调整语句的条件顺序
2.重新建立索引

1.5 对于多子句的查询，联合索引的应用

这种情况下，建立联合索引要按照子句的执行书序来建立联合索引
语句执行书序，如下：
      (4)           (1)
select.....from.......
      (2)           (3)
where....group by .....
      (5)           (6)
having.....order by.....
      (7)
limit....

1.6 explain(desc)使用场景

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

第9章 索引应用规范

1.1 建立索引的原则

(1) 必须要有主键,如果没有可以做为主键条件的列,创建无关列
(2) 经常做为where条件列  order by  group by  join on, distinct 的条件(业务:产品功能+用户行为)
(3) 最好使用唯一值多的列作为索引,如果索引列重复值较多,可以考虑使用联合索引
(4) 列值长度较长的索引列,我们建议使用前缀索引.
(5) 降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit(xxxxx)
(6) 列值经常变化,没必要建索引,小表可以不用建索引.
(7) 索引维护要避开业务繁忙期(pt-toolkit)

1.2 不走索引的情况

(1) 语句本身就是全表扫描
	select *  from city;
	select * from city where 1=1;
(2) 查询条件没建索引
mysql> desc select * from city where name='dalian';
(3) 查询条件不满足索引应用逻辑
mysql> desc select * from city where countrycode like '%CH%';
如果业务中有大量的需求,可以使用ES
<>,not in()
(5) 查询条件中出现计算
mysql> desc select * from city where id-1=9;
(6) 出现隐式转换
mysql> desc select * from t1 where telnum=110;
mysql> desc select * from t1 where telnum='110';
(7) 统计信息不真实,导致索引失效.
现象: 昨天查询功能 0.0.1秒,今天突然就慢了
原因1: 
解决: 重建索引,重新收集统计信息
mysql> optimize table city;
原因2: 
	缓存失效:缓存击穿和雪崩
(8) 查询结果集超过25%,有可能会出现全表扫描(read ahead)
解决方案: limit, between and.
(9) <> ，not in 不走索引（辅助索引）
(10) like "%_" 百分号在最前面不走
EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '31%'  走range索引扫描
EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '%110'  不走索引
%linux%类的搜索需求，可以使用elasticsearch+mongodb 专门做搜索服务的数据库产品