一、索引介绍
1、索引作用
类似于书中目录的作用,目的是为了优化查询
(如果索引太多，就像每页都写好几条目录，可能会导致书的目录就有几十页，也会加重负担)
2、索引的种类
B树
hash
红黑树
二叉树
B树：每个节点都存储key和data
B+树：只有叶子节点才会存储data，可以让枝节点有更多的key值存储
每个叶子节点增加一个指向相邻叶子节点的指针，在范围查询方面提供了更好的性能
3、在功能上的分类
(1) 辅助索引
索引是基于表中,列(索引键)的值生成的B树结构
提取索引列的所有值，进行排序
将排好序的值,均匀的分布到索引树的叶子节点中(16K)
然后生成此索引键值所对应得后端数据页的指针
生成枝节点和根节点,根据数据量级和索引键长度,生成合适的索引树高度，进一步生成枝节点和根节点
(2) 聚集索引
mysql会自动选择主键作为聚集索引列，没有主键会选择唯一键，如果都没有会生成隐藏的
mysql进行存储数据时，会按照聚集索引列值的顺序，有序存储数据行
聚集索引直接将原表数据页，作为叶子节点，然后提取聚集索引列向上生成枝节点和根节点
(3)聚集索引和辅助索引的区别
表中任何一个列都可以创建辅助索引，在有需要的时候，只要名字不同即可
在一张表中，聚集索引只能有一个，一般是主键
辅助索引，叶子节点只存储索引列的有序值+聚集索引列值
聚集索引，叶子节点存储的是有序的整行数据
mysql的表数据存储是聚集索引组织表
4、辅助索引细分
(1)普通的单列辅助索引
(2)联合索引
多个列作为索引条件,生成索引树,理论上设计的好的,可以减少大量的回表查询
(3)唯一索引
索引列的值都是唯一的
5、索引树高度和字段长度
索引树高度应当越低越好，一般维持在3-4为佳
业务允许，尽量选择字符长度短的列作为索引列
索引列值过长，采用前缀索引
二、索引的基本管理
1、查看索引
show index from stu;
desc stu
key:有没有索引,索引类型
PRI: 主键索引
UNI: 唯一索引
MUL: 辅助索引(单列,联和,前缀)
2、创建索引
单列的辅助索引
alter table stu add index idx_name(sname);
多列的联合索引
alter table stu add index idx_name_age(sname,sage);
唯一索引
alter table stu add unique index idx_qq(qq);
前缀索引
alter table stu add index idx_weixin(weixin(5));
3、删除索引
alter table stu drop index idx_weixin;
三、分析执行计划
(1)执行计划
获取到的是优化器选择完成的,他认为代价最小的执行计划.
作用: 语句执行前,先看执行计划信息,可以有效的防止性能较差的语句带来的性能问题.
如果业务中出现了慢语句，我们也需要借助此命令进行语句的评估，分析优化方案。
(2) select 获取数据的方法
1. 全表扫描(应当尽量避免,因为性能低)
2. 索引扫描
3. 获取不到数据
create table test (
id int not null PRIMARY key auto_increment,
k1 char(2),
k2 varchar(2),
k3 char(4),
k4 varchar(4)
)
alter table test add index idx_k1(k1);
alter table test add index idx_k2(k2);
alter table test add index idx_k3(k3);
alter table test add index idx_k4(k4);
alter table test add index idx_1234(k1,k2,k3,k4);
insert into test values(1,"aa","a1","a2","a3");
insert into test values(2,"bb","b1","b2","b3");
insert into test values(3,"cc","c1","c2","c3");
insert into test values(4,"dd","d1","d2","d3");
insert into test values(5,"ee","e1","e2","e3");
分析执行计划
explain select * from test;
或者desc select * from test;
1、type类型
全表扫描：all
select * from test;
select * from test where 条件无索引;
select * from test where != not in like '%aa%';
索引扫描：index,range,ref,eq_ref,const(system),null
index 全索引扫描
desc select id from test;
range 索引范围扫描 (>,<,>=,<=,between and,or,in,like)
desc select * from test where id>2;
(对于辅助索引来讲，!=和not in等语句是不走缩印的
对于主键索引列来讲，!=和not in等语句是走range)
一般改写为union all
ref 辅助索引等值查询
desc select * from test where k1='aa';
eq_ref 多表连接时，子表使用主键列或唯一列作为连接条件
a jion b
on a.x=b.y
const(system) 主键或者唯一键的等值查询
desc select * from test where id=1;
null查询数值为空
2、possible_key 可能会用到的索引
3、key 真正选择了哪个索引
4、key_len 索引覆盖长度
utf8mb4 varchar(10)
10中文 40
10英文 10
10个数据 10
varchar(20)
能存20个任意字符
不管存储的是字符，数字，中文，每一个字符最大预留长度是4个字节
对于中文，1个占四个字节
对于数字，一个实际占用大小是1个字节
关于key_len的计算
k1 char(2), 则k1的key_len=2*最大预留长度(4)+1(空位)=9
若k1设置成not null，则为8
k2 varchar(2),则k1的key_len=2*最大预留长度(4)+1(空位)+2(varchar多了一个开头字节和结尾字节)=11
若k2设置成not null，则为10
联合索引key_len就是每个key_len的叠加
desc select * from test where k2='a1'and k1='aa' and k3='a2' and k4='a3';
如果试验的时候，key一直不走idx_1234，可以把走的那些索引删掉
desc select * from test where k2='a1'and k1='aa' and k4='a3';
k1+k2的key_len=9+11=20 和where等值条件的先后顺序无关
5、extra:
filesort ,文件排序
desc select * from test where k2='a1' group by k4;
alter table test add index idx_24(k2,k4); ##添加索引k2和k4的
当我们看到执行计划extra位置出现filesort,说明在查询中有关排序的条件列并没有合理的应用索引
观察需要排序(ORDER BY,GROUP BY ,DISTINCT )的条件,有没有索引
根据子句的执行顺序,去创建联合索引
6、explain使用场景
(1)应急性的慢：突然夯住
应急情况:数据库hang(卡了,资源耗尽)
处理过程:
show processlist; 获取到导致数据库hang的语句
explain 分析SQL的执行计划,有没有走索引,索引的类型情况
建索引,改语句
(2)一段时间慢(持续性的):
记录慢日志slowlog,分析slowlog
explain 分析SQL的执行计划,有没有走索引,索引的类型情况
建索引,改语句
四、索引应用规范
建立索引的原则（DBA运维规范）
建表必须要有主键，一般是无关列，自增长
经常作为where条件列，group by、order by、join on、distinct条件
最好使用唯一值多的列作为联合索引前导列，其他的按照联合索引优化细节来做
列值长度较长的索引列，建议使用前缀索引
降低索引条目，一方面不要建没用索引，不常使用的索引清理
索引维护要避开业务繁忙期
小表不建索引
不走索引的情况（开发规范）
(1)没有查询调价，或者查询条件没有建立索引
select * from test; 全表扫描。
select * from test where 1=1;
(2)查询结果集是原表中的大部分数据，应该是25％以上。
##查询的结果集，超过了总数行数25%，优化器觉得就没有必要走索引了
(3)索引本身失效，统计数据不真实
索引有自我维护的能力。
##对于表内容变化比较频繁的情况下，有可能会出现索引失效,一般是删除重建
(4)查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等)
错误的例子：select * from test where id-1=9;
正确的例子：select * from test where id=10;
(5)隐式转换导致索引失效
select * from tab where telnum=1333333;
select * from tab where telnum='1333333';
这样会导致索引失效. 错误的例子：
(6) <> ，not in 不走索引（辅助索引）
EXPLAIN SELECT * FROM teltab WHERE telnum <> '110';
EXPLAIN SELECT * FROM teltab WHERE telnum NOT IN ('110','119');
单独的>,<,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'
(7)like "%_" 百分号在最前面不走
EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '31%' 走range索引扫描
EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '%110' 不走索引
%linux%类的搜索需求，可以使用elasticsearch+mongodb 专门做搜索服务的数据库产品
(8)联合索引
