Mysql索引原理与优化

如何查询数据？

在没有索引的情况下，如果执行select * from t where age = 22，那么要找到age = 22的数据，则是从上往下一个一个比较，直到第6行才能找到，并且数据库的文件是存在磁盘上的文件中，所以每次比较都算做一次IO操作，也就是6次IO操作，如果数据量大，可以想象查询成本将会非常大，这种查询方式被称为 全表扫描。索引的出现就是解决这个问题的。

索引的核心——数据结构

二叉树

二叉树有以下特点：
1）每个结点最多有两颗子树，所以二叉树中不存在度大于2的结点。
2）左子树和右子树是有顺序的，次序不能任意颠倒。
3）即使树中某结点只有一棵子树，也要区分它是左子树还是右子树。
4）左节点要小于父节点，右节点大于或等于父节点

现在是加了一种基于二叉树的实现的索引，再次执行select * from t where age = 22。
这次走索引步骤：
1.比较22和30，22小于30，根据二叉树特性，走左子树继续查询。
2.比较22和22，相等，拿到22对应的磁盘指针地址0XA1，从磁盘指针对应的数据返回。
数据其他同理，通过上面的操作可以发现，仅仅只进行了2次的查询IO操作，就得到了数据，即使出现最坏的可能性，要查询18，也只用走3次IO。可见索引对查询效率的帮助非常大。

存在的问题：
上图的二叉树索引是以age作为索引列的，但是如果使用id作为索引列呢？
因为二叉树的特性：右节点大于或等于父节点。在极端情况下会导致 树的倾斜，退化成链表了，查询和全表扫描没什么区别了，所以mysql并没有使用这种数据结构

红黑树（二叉平衡树）

改善了二叉树在极端情况下会导致 树的倾斜的缺陷。
红黑树的特性：
（1）每个节点或者是黑色，或者是红色。
（2）根节点是黑色。
（3）每个叶子节点（NIL）是黑色。 [注意：这里叶子节点，是指为空(NIL或NULL)的叶子节点！]
（4）如果一个节点是红色的，则它的子节点必须是黑色的。
（5）从一个节点到该节点的子孙节点的所有路径上包含相同数目的黑节点。[这里指到叶子节点的路径]

红黑树有个问题，是如果深度太大，依然效率不高

B树（多路平衡二叉树）

特性：
B- Tree
叶节点具有相同的深度，叶节点的指针为空
所有索引元素不重复
节点中的数据索引从左到右递增排列

B树的数据存储

B树的问题是：如果数据量大的话单个节点存储数量过多，效率依然不高

B+树

MYSQL 给单个节点设置的是16KB大小

为什么用B+而不是B树？
答：如果用B树，那么每个节点上都存了索引和数据，因为每个节点（一页）的默认长度是16K，介绍页的官方文档。所以每个节点的长度存储数量就少了，既然少了，那么就会往下一层存，这样树的高度就增加，查询效率下降。
mysql的B+树，每个非叶子节点都只存储索引，这样一个非子节点就可以存很多索引了，树的高度自然比B数那种方式低了，效率就提上来了，并且把数据只放在最后的节点，这样的话非叶子节点可以存放更多的索引，如下图

MYSQL事实上的对B+树进行了一些小改造，标准的B+树在叶子节点上的单向的指向下一个节点，mysql则对其改造成为双向指向，并且头尾相连。
特点：
1.非叶子节点不存储data,只存储索引(冗余)，可以放更多的索引
2.叶子节点包含所有索引字段
3.叶子节点用指针连接，提高区间访问的性能

存储引擎

InnoDB（聚集 / 聚簇）

innoDB的介绍和优点官方文档
聚集索引和辅助索引官方文档
表数据文件本身就是按B + Tree组织的一个索引|结构文件
聚集索引吐节点包含了完整的数据记录

上图中有个问题，非叶子节点指向下一个节点的指针是双向的，图中画成了单向的
InnoDB存储了2个文件

**t1.frm:**存储了表的组成结构
t1.idb: 存储了数据和索引

MyISAM（非聚集）

MyISAM的介绍和优点官方文档
MyISAM索引文件和数据文件是分离的（非聚集）

这种存储引擎在大部分情况下的查询效率比innoDB要高，如果项目中有表不需要事务且查询操作多，则可以考虑使用MyISAM。
查询效率高的原因是
1.因为MyISAM是非聚集索引，根据非聚集索引的特性索引是与实际数据分开的，可以理解为多个索引树对应一张数据表。查询操作只用走一次索引树。而innoDB则是聚集索引，使用主索引和辅助索引，如果使用辅助索引查询会再走一次主索引(当然，这个并不是绝对的)。

t2表存储的文件，3个

t2.frm: 存储了表的组成结构
t2.MYD:（MyISAM Data）表具体的数据

t2.MYI: （MyISAM Index）表索引相关的信息

图中有个问题，非叶子节点指向下一个节点的指针是双向的，图中画成了单向的

两种的查询区别

索引

为什么使用索引，可以避免全表扫描查找索引
在MYSQL中支持2中索引的类型

索引方法

btree和hash索引的比较官方文档

1.B Tree (默认)

select * from t where t.id > 6
过程：通过根节点找到6所在的节点，然后返回6这个节点后面的所有数据。因为根据b-tree的特性，索引是有序的。

2.HASH

对索引进行hash运算，直接去查询，一次就可以查到
select * from t where t.id = 6
这种查询语句在hash索引时非常有优势，数据量大也是同样
但是下面这种就范围查询，性能非常差
select * from t where t.id > 6

联合索引

上图绿色代表索引，紫色代表数据(没有索引)
绿色的索引为联合索引

问题

1.为什么InnoDB表必须有主键,并且推荐使用整型的自增主键?

1.1问：为什么说必须有主键？但是建没有主键的又可以建成功呢？
是因为MySql在你没有指定主键的时候，会去每列中找列中没有重复数据的列，把他作为主键。
但是如果在所有的列中都没找到没有重复数据的列，那么MYSQL会在最后一列再添加一列作为索引列(隐藏的)，由MYSQL去维护这一列

1.2问：那为什么推荐使用整型的自增主键?
因为在使用索引定位数据时，会进行索引的比较，确定范围。
原因1：
使用UUID，那么会先把字符进行转换，再去比对，消耗了时间。
使用整型，那么就直接进行比对，没有字符转换的过程。
原因2：
UUID占用空间比整型大，大数据量大的情况下，差距就明显了

1.3为什么推荐使用自增主键？
如果使用UUID这种非自增主键，那么在放与一条数据到一个非叶子节点是时候，需要要叶子节点里面插入，那如果这个叶子节点满了呢？那么就会打散这个叶子节点，还有可能触发重新树平衡。
而使用自增主键，那么会很明确的直接在索引索引的最后面区插入，不存在打破其他的节点，因为根据b-tree的特性，索引是有序排列的。所以推荐使用自增主键

2.联合索引底层的数据结构是什么样

如果是排序查询的画，先比较第一个字段，依次再比较后面的字段
3.重复率高的字段到底该不该建索引
一个数据表保存的聚簇索引如下：包含了 id ,姓名，性别字段(性别0/1表示)

假如给性别字段建立了索引，下图为性别字段的辅助索引

现在 发送一条sql : select * from table where sex=0;
4秒返回数据
过程：通过sex辅助索引找到所有满足sex=0的聚簇索引的id，在通过id去主索引表中找完整的数据。假如找到了50W条数据，从第一条开始，第一条的主索引的id为2，去主索引中找id=2的数据，然后返回，这样反复操作50W次就返回了上面那条SQL的查询结果。

再发送一条禁用性别索引的sql：select * from table ignore index(sex_index) where sex=0;
2秒返回数据
过程：全表扫描匹配sex=0,就是那种最笨的方式，一条一条的比。

经过测试，在50W的数据量中，禁用索引反而比加了索引的查询速度快了不是。
为什么？
通过上面2条SQL的执行过程就可以看出来，使用了索引的会先走辅助索引，然后走主索引，增加了大量的IO开销，而没有使用索引的，直接查询主索引，虽然会多出无用的对比过程，但是相比增加IO开销，是值得的。

SQL优化

解析过程
from>on> join> where> group by> having> select> dinstinct> order by >limit
sql优化主要就是优化索引

1.定位慢sql

SHOW VARIABLES LIKE "%query%"

打开慢sql记录

SET GLOBAL slow_query_log = ON

方便测试修改配置，把超过1秒的查询，认定为慢sql，这个配置需要重连数据库后生效

SET GLOBAL long_query_time = 1

下面这条sql查询 慢sql查询的次数

SHOW STATUS LIKE "%slow_queries%"

打开慢日志文件C:Program Filesmysql-5.7.28-winx64dataHaseeNotBook-slow.log

# Time: 2020-05-31T07:55:03.875274Z
# User@Host: root[root] @ localhost [::1]  Id:    44
# Query_time: 7.427691  Lock_time: 0.000089 Rows_sent: 1000  Rows_examined: 1071001
SET timestamp=1590911703;
select age from t1 order by age LIMIT 0, 1000;

2.EXPLAIN分析SQL

explan输出格式官方文档

EXPLAIN SELECT NAME FROM t1 ORDER BY NAME

type列

性能排序
依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL
一般查询达到range级别，最好达到ref

通过Explain分析SQL type为All ，结合性能排序，发现all是排在最后的最小的，all是一个全表扫描，

NULL：mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在 索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表

select min(id) from test_table; 

const, system：mysql能对查询的某部分进行优化并将其转化成一个常量（可以看show warnings 的结果）。用于 primary key 或 unique key 的所有列与常数比较时，所以表最多 有一个匹配行，读取1次，速度比较快。system是const的特例，表里只有一条元组匹配时为 system

select * from test_table where id = 1

eq_ref：primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合 条件的记录。这可能是在 const 之外最好的联接类型了，简单的 select 查询不会出现这种 type。

select * from test_table  
left join test1_table
on test_table.id = test1_table.id

ref：相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要 和某个值相比较，可能会找到多个符合条件的行。

# age 有辅助索引
select * from test_table where age= 19

range：范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定 范围的行。

# age 有辅助索引
select * from test_table where age> 19

index：扫描全表索引，这通常比ALL快一些。 一般在辅助索引中存在

# age 有辅助索引
select age from test_table

ALL：即全表扫描，意味着mysql需要从头到尾去查找所需要的行。通常情况下这需要增加索引来进行优化

# sex 没有任何索引
select * from test_table where sex = 1

Extra列

3.修改SQL，尽量让SQL走索引

1.添加索引

ALTER TABLE t1 ADD INDEX idx_name(NAME)

现在查询走索引了，因为是B+树，所有的叶子节点都是链表连起来的，所以这种排序查询可以完美走链表

2.测试索引
默认sql查询优化器会自动选择效率最高的索引，但也有例外，所以可以使用强制使用某个索引去测试哪个索引效率更高。

SELECT NAME FROM t1  FORCE INDEX(idx_name)

SQL索引匹配

最左前缀法则

一定要按照联合索引的顺序去查

上图建了一个名称为 idx_name_age_position 的联合索引，包含字段name、age、position

使用explain 分析3条sql.
第一条不会走索引，全表扫描
第二条不会走索引，全表扫描
第三条会走索引

不在索引列上做任何操作（计算、函数、类型转换）

MYSQL只要看到字段上有函数直接不走索引

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei'; 
EXPLAIN SELECT * FROM employees WHERE left(name,3) = 'LiLei';

//给 hire_time 建一个索引 ,date()函数计算不会走索引
EXPLAIN select * from employees where date(hire_time) ='2018-09-30';
//去掉date()函数计算，该外范围，会走索引
EXPLAIN select * from employees where hire_time >='2018-09-30 00:00:00' and hire_time <='2018-09-30 23:59:59';

存储引擎不能使用索引中范围条件右边的列

// position ='manager' 这个将不会走索引，只有name、age走索引了
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age > 22 AND position ='manager';

尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少select *语句

create table person(
    id int not null auto_increment,
    name varchar(50) not null,
    job1 varchar(50) not null,
    job2 varchar(50) not null,
    job3 varchar(50) not null,
    
    primary key id,
    key p_idx_j1(job1,job2,job3)
);

数据是：

1,ermao,programmer,doctor,teacher
2,haha,engineer,boss,star

这种数据结构，如果想查询job1，job2，job3，用 select * from person where job2 = 'boss’是不会走二级索引的，只能在聚集索引的那颗B+树上做全表扫描，也就是最慢的一种查询方式。

如果是select job1,job2,job3 from person where job2 = 'boss’这种方式查询呢？是不是认为job2在联合索引的中间，所以并不会走二级索引。其实它是会走二级索引的那个B+树的。我们知道在mysql中(Innodb引擎)聚集索引是一定存在的，如果表结构中定义了主键，聚集索引就根据主键建立，否则如果有唯一列就用唯一列，否则就会自动在每条记录中生成一个隐藏的ID列并以此建立聚集索引，聚集索引的叶子节点就是真实的数据。
比如此数据结构，就会有两棵B+树，一颗是聚集索引，另一颗是job1，job2，job3定义的联合索引（二级索引）。二级所索引的叶子节点是索引列加ID，所以走索引的大致流程分两步，一是先根据索引列在二级索引树中找到ID，然后根据ID在聚集索引中找到对应的记录（这一步也称为回表）。
有没有发现问题？也就是二级索引中其实已经包含了job1，job2，job3这三列的数据。那如果我查询语句中查询的就是这三列的数据，并且搜索条件中也是这三列中的一列，那我还有必要分两步操作吗？还有必要进行回表操作吗？我只需要在二级索引树中进行搜索即可。所以对于这种情况，如果用select job1,job2,job3 from person where job2 = 'boss’这种指定列的查询只需走二级索引，无需回表，并且二级索引的每条记录不含有隐藏列，加载内存的操作会更快。而如果用select * 的话，因为二级索引树中并没有name那一列，所以根本走不了二级索引，只能对聚集索引进行全表扫描，在数据两大的情况下，性能影响还是很可观的。

使用!=、<>、is null,is not null的时候无法使用索引会导致全表扫描

EXPLAIN SELECT * FROM employees WHERE name != ‘LiLei’;
EXPLAIN SELECT * FROM employees WHERE name is null

like以通配符开头（’$abc…’）mysql索引失效会变成全表扫描操作

%通配符在后面不会走索引，在前面会走索引

EXPLAIN SELECT * FROM employees WHERE name like '%Lei'
//这条会走索引
EXPLAIN SELECT * FROM employees WHERE name like 'Lei%'

解决like’%字符串%'索引不被使用的方法？
使用覆盖索引，查询字段必须是建立覆盖索引字段

// name,age,position 为联合索引
 EXPLAIN SELECT name,age,position FROM employees WHERE name like '%Lei%';

字符串不加单引号索引失效

//会走索引
EXPLAIN SELECT * FROM employees WHERE name = '1000';
//不会走索引，name是字符串，1000是数字，被类型转换
EXPLAIN SELECT * FROM employees WHERE name = 1000;

少用or或in

用它查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例、 表大小等多个因素整体评估是否使用索引，详见范围查询优化

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' or name = 'HanMeimei';

范围查询优化

//给年龄添加单值索引
ALTER TABLE `employees` 
ADD INDEX `idx_age` (`age`) USING BTREE ;

//下面这条可能不会走索引
explain select * from employees where age >=1 and age <=2000;
//没走索引原因：mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索 引。
//比如这个例子，可能是由于单次数据量查询过大导致优化器最终选择不走索引 优化方法：
//可以讲大的范围拆分成多个小范围
explain select * from employees where age >=1 and age <=1000;

索引使用总结

MYSQL优化实战

一

//不走索引
EXPLAIN select * from employees where name > 'a';
//走索引
EXPLAIN select * from employees where name > 'zzz' ;

对于上面这两种 name>‘a’ 和 name>‘zzz’ 的执行结果，mysql最终是否选择走索引或者一张表涉及多个索引，mysql最 终如何选择索引，我们可以用trace工具来一查究竟，开启trace工具会影响mysql性能，所以只能临时分析sql使用，用 完之后立即关闭

二 limit

例如

EXPLAIN  
select * from zipkin_spans  limit 100000,10

可以看到limit 是全表扫描的，如果没有指定排序字段，则是按照插入顺序排序。因为没有任何一个索引去维护插入顺序，所以从主索引中进行全表扫描。
加order by

# start_ts 是一个辅助索引字段
#走start_ts 索引
EXPLAIN  
select * from zipkin_spans ORDER BY start_ts limit 10,100
#不走start_ts 索引，全表扫描
EXPLAIN  
select * from zipkin_spans ORDER BY start_ts limit 100000,100

上面这两条的区别仅仅是分页开始的位置不一样，为什么一个走索引一个不走索引呢？现在又回到上面那个问题 “重复率高的字段到底该不该建索引？”，和这个原因一样，mysql认为 不走索引反而效率更高。

那如何优化分页？
现在我们只是需要从莫一个位置开始后面的100条数据，也就是只要100条数据，并且是根据start_ts进行排序的。可以利用start_ts的辅助索引加上分页排序去查询主键索引返回100条记录，然后用100条记录的主键索引在去查询数据。这样就可走主索引进行分页，不再进行全表扫描。

# start_ts 是一个辅助索引字段
EXPLAIN  
select * from zipkin_spans z1
inner join(select id from zipkin_spans ORDER BY start_ts limit 200000,100) z2
on z1.id = z2.id

优化总结

1、MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index 效率高，filesort效率低。
2、order by满足两种情况会使用Using index。 ①order by语句使用索引最左前列。 ②使用where子句与order by子句条件列组合满足索引最左前列。
3、尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。
4、如果order by的条件不在索引列上，就会产生Using filesort。
5、能用覆盖索引尽量用覆盖索引
6、group by与order by很类似，其实质是先排序后分组，遵照索引创建顺 序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中 的限定条件就不要去having限定了。

MYSQL5.7官方文档

MYSQL5.7官方文档
聚集索引和辅助索引官方文档
优化和索引官方文档
btree和hash索引的比较官方文档
explan查询计划官方文档
explan输出格式官方文档

innoDB的介绍和优点官方文档
MyISAM的介绍和优点官方文档

转载于：https://blog.csdn.net/qq_40690648/article/details/106445034