Mysql(linux版)索引及执行计划

1. 索引的作用
   类似于一本书中的目录,起到优化查询的作用

2. 索引的分类(算法)
   B树
   B+树 默认使用的索引类型
   B*树
   R树
   Hash
   FullText
   GIS 索引

3. BTREE索引算法演变(了解)
   看图说话.
   B树
   
   B+树（对范围查找更强大，走叶节点指针）
   
   B*树
   

4. Btree索引功能上的分类

4.1 辅助索引
(1) 提取索引列的所有值,进行排序
(2) 将排好序的值,均匀的存放在叶子节点,进一步生成枝节点和根节点
(3) 在叶子节点中的值,都会对应存储主键ID

4.2 聚集索引
(1)MySQL 会自动选择主键作为聚集索引列,没有主键会选择唯一键,如果都没有会生成隐藏的.
(2)MySQL进行存储数据时,会按照聚集索引列值得顺序,有序存储数据行
(3)聚集索引直接将原表数据页,作为叶子节点,然后提取聚集索引列向上生成枝和根

4.3 聚集索引和辅助索引的区别
(1) 表中任何一个列都可以创建辅助索引,在你有需要的时候,只要名字不同即可
(2) 在一张表中,聚集索引只能有一个,一般是主键.
(3) 辅助索引,叶子节点只存储索引列的有序值+聚集索引列值.
(4) 聚集索引,叶子节点存储的时有序的整行数据.
(5) MySQL 的表数据存储是聚集索引组织表
以上两种索引协同工作时，比如先用辅助索引查姓名为张三的人对应的id值，然后拿着该id去聚集索引树中再做一次遍历，找到张三的全部信息（原表的聚集索引树的叶子节点可理解为就是原表本身，因为id已经找到了）。

5. 辅助索引细分
   5.1 单列辅助索引
   5.2 联合索引(覆盖索引) *****
   5.3 唯一索引

6. 索引树高度
   索引树高度应当越低越好,一般维持在3-4最佳
   6.1 数据行数较多
   分表 : parttion 用的比较少了.
   分片,分布式架构.
   6.2 字段长度
   业务允许,尽量选择字符长度短的列作为索引列
   业务不允许,采用前缀索引.
   6.3 数据类型
   char 和 varchar
   enum

7. 索引的命令操作

7.1 查询索引
desc city;
PRI ==> 主键索引
MUL ==> 辅助索引
UNI ==> 唯一索引

mysql> show index from city\G

7.2 创建索引

单列的辅助索引:
mysql> alter table city add index idx_name(name);

多列的联合索引:
mysql> alter table city add index idx_c_p(countrycode,population);

唯一索引:
mysql> alter table city add unique index uidx_dis(district);
判断有无重复，对比去重前和去重后：
mysql> select count(district) from city;
mysql> select count(distinct district) from city;

前缀索引
mysql> alter table city add index idx_dis(district(5));

7.3 删除索引
mysql> alter table city drop index idx_name;

mysql> alter table city drop index idx_c_p;
mysql> alter table city drop index idx_dis;

8. 压力测试准备:
   mysql> use test
   mysql> source /tmp/t100w.sql（导入100w条数据）

8.1 未做优化之前测试
mysqlslap --defaults-file=/etc/my.cnf
–concurrency=100 --iterations=1 --create-schema=‘test’
–query=“select * from test.t100w where k2=‘MN89’” engine=innodb
–number-of-queries=2000 -uroot -p123 -verbose
100个用户连接，并做2000次查询。查k2=MN89的数据。

[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf \

–concurrency=100 --iterations=1 --create-schema=‘test’
–query=“select * from test.t100w where k2=‘MN89’” engine=innodb
–number-of-queries=2000 -uroot -p123 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Running for engine rbose
Average number of seconds to run all queries: 755.861 seconds
Minimum number of seconds to run all queries: 755.861 seconds
Maximum number of seconds to run all queries: 755.861 seconds
Number of clients running queries: 100
Average number of queries per client: 20

8.2 索引优化后
[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema=‘test’ --query=“select * from test.t100w where k2=‘MN89’” engine=innodb --number-of-queries=2000 -uroot -p123 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Running for engine rbose
Average number of seconds to run all queries: 1.678 seconds
Minimum number of seconds to run all queries: 1.678 seconds
Maximum number of seconds to run all queries: 1.678 seconds
Number of clients running queries: 100
Average number of queries per client: 20

9. 执行计划分析
   9.1 作用
   将优化器 选择后的执行计划 截取出来.便于管理管判断语句得执行效率.
   9.2 获取执行
   desc SQL语句
   explain SQL 语句
   查看某语句的执行计划
   

9.3 分析执行计划
9.3.1 table
表名
9.3.2 type
查询的类型:
全表扫描 : ALL
索引扫描 : index,range,ref,eq_ref,const(system),NULL

index: 全索引扫描
mysql> desc select countrycode from city;

range: 索引范围扫描(> < >= <= , between and ,or,in,like )
mysql> desc select * from city where id>2000;
mysql> desc select * from city where countrycode like ‘CH%’;

对于辅助索引来讲,!= 和not in等语句是不走索引的
对于主键索引列来讲,!= 和not in等语句是走range
or和in效率很低。

===
mysql> desc select * from city where countrycode=‘CHN’ or countrycode=‘USA’;
mysql> desc select * from city where countrycode in (‘CHN’,‘USA’);

一般改写为 union all
desc
select * from city where countrycode=‘CHN’
union all
select * from city where countrycode=‘USA’;

ref: 辅助索引等值查询
desc
select * from city where countrycode=‘CHN’
union all
select * from city where countrycode=‘USA’;

eq_ref : 多表连接时,子表(被连接表)使用主键列或唯一列作为连接条件
A join B
on a.x = B.y

desc select b.name,a.name ,a.population
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=100;

NULL级别就是找不到数据的时候。

---

possible_key:可能会用到的索引

key: 真正选择了哪个索引

key_len 索引覆盖长度
varchar(20) utf8mb4，这里预留长度20*4+3

1. 能存20个任意字符（包括中文，中文一个字符占四个字节）
2. 不管存储的时字符,数字,中文,都1个字符最大预留长度是4个字节
3. 对于中文,1个占4个字节
4. 对于数字和字母,1个实际占用大小是1个字节

不管什么类型，只要没有指明是否非空，都会额外用一位来标识是否非空，对于varchar来说，除了有这一位，还有开头和结束的两个标识位。对于联合索引，key_len就是所有的索引长度相加。

Extra: Using filesort
出现Using filesort,说明在查询中有关排序的条件列没有合理的应用索引
order by
group by
distinct
union
关注key_len应用的长度

联合索引应用细节

联合索引如果只出现某一列是不走的，必须要全部出现联合索引当中的列。

只要我们将来的查询,所有索引列都是<等值>查询条件下,无关排列顺序
建索引时，唯一值多的列放在最左侧
abcd
acbd
adbc
acbd
等等

mysql> desc select * from test where k1=‘aa’ and k2=‘中国’ and k3=‘aaaa’ and k4=‘中国你好’;
mysql> desc select * from test where k2=‘中国’ and k3=‘aaaa’ and k4=‘中国你好’ and k1=‘aa’;

原因: 优化器,自动做查询条件的排列

不连续部分条件
cda ----> acd —> a -----> idx(c,d,a)
dba ----> abd —> ab ----> idx(d,b,a)

在where查询中如果出现> < >= <= like
(1)
mysql> desc select * from test where k1=‘aa’ and k3=‘aaaa’ and k4=‘中国你好’ and k2>‘中国’;
联合索引如果遇见不等值查询，就会停在不等值那里，于是我们要把不等值查询放最后，然后还要将索引新建成这个顺序，如下一步所示：
(2)
mysql> alter table test add index idx1(k1,k3,k4,k2);

多子句 查询,应用联合索引
建单列索引的话order by不能受到优化的效果：
mysql> desc select * from test where k1=‘aa’ order by k2;
按照子句的执行顺序建立联合索引：
mysql> alter table test add index idx3(k1,k2);

explain(desc)使用场景（面试题）
你做过哪些优化?
你用过什么优化工具?
你对索引这块怎么优化的?

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

索引应用规范
建立索引的原则（DBA运维规范）
(1) 建表必须要有主键,一般是无关列,自增长
(2) 经常做为where条件列 order by group by join on, distinct 的条件
(3) 最好使用唯一值多的列作为联合索引前导列,其他的按照联合索引优化细节来做
(4) 列值长度较长的索引列,我们建议使用前缀索引.
(5) 降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit(xxxxx)
(6) 索引维护要避开业务繁忙期
(7) 小表不建索引

不走索引的情况（开发规范）
(1) 没有查询条件，或者查询条件没有建立索引
select * from city;
select * from city where 1=1;
(2) 查询结果集是原表中的大部分数据，应该是25％以上。
(3) 索引本身失效，统计数据不真实
面试题:同一个语句突然变慢?
统计信息过旧,导致的索引失效
(4) 查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等)
mysql> desc select * from city where id-99=1;
(5) 隐式转换导致索引失效.
(6) <> ，not in 不走索引（辅助索引）
(7) like “%aa” 百分号在最前面不走
(8) 联合索引