mysql索引与慢查询优化
MySQL索引
什么是索引？
索引：简单的理解为可以帮助用户加快数据查询速度的工具，也可以把索引比喻成一个书的目录，字典的查字表等，更快的搜寻到用户想要的数据。
索引底部起始就是算法构造出来的
算法：解决事务的办法。
像二分法，快排，冒牌等算法都是为了更快的找到想要的数据，(我们在找数据的时候也可以从头到尾找，但是这样的效率是非常慢，就像我们在字典里找一个字的时候，如果没有目录索引，我们从头找一页一页的翻着找，那无疑是非常慢的)
将某个字段添加成索引其实就是相当于依据该字段建立了一颗b+树从而加快查询速度。
不同的存储引擎支持的索引类型也不一样
-
InnoDB存储引擎
支持事务，支持行级别锁定，支持 B-tree（默认）、Full-text 等索引，不支持 Hash 索引； -
MyISAM存储引擎
不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引； -
Memory存储引擎
不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
因为mysql默认的存储引擎是innodb，而innodb存储引擎的索引模型/结构是B+树，所以我们着重介绍B+树，那么大家最关注的问题来了：
B+树索引到底是如何加速查询的呢？我们往后一点一点的探索。
数据结构
数据结构是计算机存储、组织数据的方式。数据结构是指相互之间存在一种或多种特定关系的数据元素的集合。通常情况下，精心选择的数据结构可以带来更高的运行或者存储效率。数据结构往往同高效的检索算法和索引技术有关。
数据结构之树形结构
二叉树
二叉树：它是一种查找效率非常高的数据结构，它有三个特点。
（1）每个节点最多只有两个子树。
（2）左子树都为小于父节点的值，右子树都为大于父节点的值。
（3）在n个节点中找到目标值，一般只需要log(n)次比较。
二叉查找树的结构不适合数据库，因为它的查找效率与层数相关。越处在下层的数据，就需要越多次比较。极端情况下，n个数据需要n次比较才能找到目标值。对于数据库来说，每进入一层，就要从硬盘读取一次数据，这非常致命，因为硬盘的读取时间远远大于数据处理时间，数据库读取硬盘的次数越少越好。
b树
B树是对二叉查找树的改进。它的设计思想是，将相关数据尽量集中在一起，以便一次读取多个数据，减少硬盘操作次数。
b树的特点：
（1）一个节点可以容纳多个值。
（2）除非数据已经填满，否则不会增加新的层。也就是说，B树追求"层"越少越好。
（3）子节点中的值，与父节点中的值，有严格的大小对应关系。一般来说，如果父节点有a个值，那么就有a+1个子节点。
假设我们找 id=38的数据
select * from user where id=38;
如果我们不使用任何树形结构 我们要从1找到38 这样需要找38次
如果我们使用树形结构来找这个数据：
一切都从根节点出发：
1、先判断根节点5(这里不能确定是否在这个根节点下)
2、再判断根节点28(这里也不能确定是否在这个跟节点下)
3、再判断根节点65(这里可以确定 要找的38在28根节点下 因为65代表这个根节点下最小的值)
4、往根节点28的枝节点找(枝节点也分为三个节点(三枝))
5、这里可以把枝节点看作为根节点同上操作找到枝节点35
6、最后找到了38
我们可以看到通过b树我们只需要三步就可以找到想要的数据 对比从头找38次 显然是大大所见的查询速度
如果是范围查找数据要通过几次IO呢?
select * from user where id > 38 and id <73;
一切都从根节点出发：
1、先判断根节点5(这里不能确定是否在这个根节点下)
2、再判断根节点28(这里也不能确定是否在这个跟节点下)
3、再判断根节点65(这里就可以判断要找到范围数据在28和65根节点下)
4、找到根节点28的枝节点判断后在35和56枝节点下
5、找到根节点65的枝节点判断后在65枝节点下
6、在35枝节点下找到了符合数据38和50
7、再'回头'从28根节点出发往下找 35枝节点找过了 这里就找56枝节点 找到了符合数据56、60、63
8、再'回头'从65根节点出发往下找65枝节点下 然会找符合条件的叶子节点65、73
结论：
我们可以看到精确查看经过了3次IO,范围查找经过了9次IO
总结：
其实我们在通过b树查找数据的时候由树的高度决定，基于上图树结构，精确查找就是三次IO(找三次)，范围查找就是3的倍数(找几次就是3乘上几)上述找了3次则为9次IO
b+树
我们可以看到b+树和b树有不同的地方就是在叶子节点处添加了'指针'
那么这个指针的作用是什么呢 我们同样使用范围查找的例子来看一下：
select * from user where id > 38 and id <73;
一切都从根节点出发：
1、先判断根节点5(这里不能确定是否在这个根节点下)
2、再判断根节点28(这里也不能确定是否在这个跟节点下)
3、再判断根节点65(这里就可以判断要找到范围数据在28和65根节点下)
4、找到根节点28的枝节点判断后在35和56枝节点下
5、找到根节点65的枝节点判断后在65枝节点下
6、在35枝节点下找到了符合数据38和50
不同处 ： 7、这里不在'回头'找，直接通过指针往后找找到56、60、63再通过指针往后找65、73
结论：精确查找情况下b+树 和 b树是没有区别的,都是3次IO因为不需要走'指针'
范围查找数据:b+树只需要5次IO 在b树的基础上更加的提升的查找的效率。
b*树
我们可以看到b*树是在枝节点的时候就添加了指针，这样更大程度上的缩减了查询速度。
结论：
b树 枝节点和叶子节点没有指针
b+树 叶子节点添加了指针
b*树 枝节点添加了指针(叶子节点也有)
无论是树结构的每一次'进化'都是为了提升范围查看的效率，如果是精确查找那么只根树的'高度'有关，树有几层就查找几次
故：在mysql中，将某个字段添加成索引 就相当于依据该字段建立了一颗b+树(暂时还没有用到b*树)从而加快查询速度,如果某个字段没有添加索引 那么依据该字段查询数据就会非常的慢(一行一行的查找)，那么我们如何通过sql语句给字段添加索引呢？
sql语句如何创建索引
索引的分类
1、peimary key
主键索引 除了有加速拆卸你的效果之外，还具有一定的约束条件
2、unique key
唯一键索引 除了有加速查询的效果之外，还有一定的约束条件
3、index
普通索引 只有加速查询的效果，没有额外的约束
4、foreign key
注意：外键不是索引，仅仅是用来创建表与表之间的关系的
如何操作索引
准备工作：
1、创建一个表：
create table t1(id int,name char(32),pwd int);
2、插入数据：
insert into t1 insert into t1 values(1,'gary',123),(2,'jason',222),(3,'tony',333),(4,'jack',444);
1、查看当前表内部索引：
show index from t1;
2、创建索引：
结构：alter table 表名 add 索引类 索引名(给那个字段添加索引)
2.1、主键索引
alter table t1 add primary key pri_id(id); # 当不知道给索引起什么名字的时候建议见名知意的原则
# 这里将id字段添加为索引 我们在使用id字段(where id...)找数据的时候 就会以b+树的结构去查找会比较快。
# 但是如果使用name字段(where name...)去查找数据是不按照索引结构去查找的。
# 故：给那个字段添加索引，那个字段才安装索引的结构去查找数据。
2.2、唯一索引
alter table t1 add unique key uni_pwd(pwd);
# 在创建唯一键索引的时候 我们先插入一条相同pwd的数据
insert into t1 values(5,'tom',123);
# 我们可以看到某一个字段有重复数据的时候 是不允许创建唯一键索引的，那么如何判断是否有重复数据呢？
这里就补充两个方法：
统计数据有多少条：select count(字段) from t1
统计去重后后多少条：select count(distinct(字段)) from t1
2.3、普通索引
alter table t1 add index index_name(name);
所以我们在使用id字段和name字段查找数据的时候都会加快查询速度。
前缀索引
# 比如我们在搜索一个一首歌的歌词时 不太记清楚怎么唱的，我们只记住了歌词的前面四五个字，那么这里就用到了前缀索引，通过N个字符建立索引。
# 前缀索引都是普通索引
具体格式：
alter table 表名 add index 索引名(索引字段(前多少个字符));
比如：把t1表中的name字段前10个字符建立为索引
alter table t1 add index index_name(name(10));
我们日常生活中就有很多前缀索引，像上图我们在百度搜所关键字mysql的时候就会显示出不仅是只有mysql的标题，只要包含mysql字符就会显示出来，这里就用到了前缀索引。
联合索引
# 多个字段建立一个索引
格式：
alter table 表名 add index 索引名(字段,字段,字段)
比如：
alter table t1 add index index_all(gender,age,money)
场景：相亲平台，在寻找相亲对象的时候，肯定要筛选条件(性别呀，身高，薪资等等)，这时候如果想要同过多个不同字段寻找数据就要用到联合索引
# 但是联合索引有一个弊端：遵循最左匹配原则
例：
where a.女生 and b.身高 and c.体重 and d.身材好
index(a,b,c)
特点：前缀生效特性
a,ab,ac,abc,abcd 可以走索引或部分走索引
b bc bcd cd c d ba ... 不走索引
explain慢查询优化
全表扫描：不走索引，一行行查找数据，效率极低，生产环境下尽量不要书写类似sql
索引扫描：走索引，加快数据查询，建议书写该类型sql
explain则就是用来判断该条sql语句属于那种扫描
具体使用：在具体的sql语句前添加explain即可
# 如果返回结果type为all则表示没有走索引扫描 只要不为all就说明走索引扫描了。
索引扫描的类型：
1）index
2）range
3）ref
4）eq_ref
5）const
6）system
7）null
# 从上到下，性能从最差到最好，我们认为至少要达到range级别
index：Full Index Scan，index与ALL区别为index类型只遍历索引树。
range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行。显而易见的索引范围扫描是带有between或者where子句里带有<,>查询。
ref：使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值的记录行。
eq_ref：类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件A
const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。
如将主键置于where列表中，MySQL就能将该查询转换为一个常量
NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。
不走索引的情况
1.没有查询条件，或者查询条件没有建立索引
eg:select * from table;
在业务数据库中，特别是数据量比较大的表,是没有全表扫描这种需求。
2.查询结果集是原表中的大部分数据，应该是25％以上
如果业务允许，可以使用limit控制。
结合业务判断，有没有更好的方式。如果没有更好的改写方案就尽量不要在mysql存放这个数据了，放到redis里面。
3.索引本身失效，统计数据不真实
索引有自我维护的能力。
对于表内容变化比较频繁的情况下，有可能会出现索引失效。
重建索引就可以解决
4.查询条件使用函数在索引列上或者对索引列进行运算，运算包括(+，-，*等)
错误的例子：select * from test where id-1=9;
错误的例子：select * from test where id-1=9;
5.隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误
eg：
# 创建表
create table test (id int ,name varchar(20),telnum varchar(10));
# 插入数据
insert into test values(1,'zs','110'),(2,'l4',120),(3,'w5',119),(4,'z4',112);
# 判断是否走索引
explain select * from test where telnum=120;
# 创建普通索引
alter table test add index idx_tel(telnum);
# 再次判断是否走了索引
explain select * from test where telnum=120;
# 找出原因再次查找
explain select * from test where telnum='120';
# 这里是因为在创建字段telnum的时候字段类型为varchar索引在查询的时候如果类型不一样，也会使索引失效
6.<> ，not in 不走索引
单独的>,<,in 有可能走，也有可能不走，和结果集有关，尽量结合业务添加limit
or或in尽量改成union
eg:
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 "%_" 百分号在最前面不走
eg:
走索引：explain select * from teltab where telnum like '31%';
不走索引 ：explain select * from teltab where telnum like '%110';
8.单独引用联合索引里非第一位置的索引列
# 就相当于上文所表述联合索引的最左匹配原则。
隔离级别
在SQL标准中定义了四种隔离级别，每一种级别都规定了一个事务中所做的修改
InnoDB支持所有隔离级别
set transaction isolation level 级别
1.read uncommitted(未提交读)
事务中的修改即使没有提交，对其他事务也都是可见的，事务可以读取未提交的数据，这一现象也称之为"脏读"
2.read committed(提交读)
大多数数据库系统默认的隔离级别
一个事务从开始直到提交之前所作的任何修改对其他事务都是不可见的，这种级别也叫做"不可重复读"
3.repeatable read(可重复读) # MySQL默认隔离级别
能够解决"脏读"问题，但是无法解决"幻读"
所谓幻读指的是当某个事务在读取某个范围内的记录时另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录会产生幻行，InnoDB和XtraDB通过多版本并发控制(MVCC)及间隙锁策略解决该问题
4.serializable(可串行读)
强制事务串行执行，很少使用该级别
