本文深入探讨了MySQL索引的结构，重点讲解了BTree和B+Tree的区别，以及为何InnoDB选择B+Tree。同时，介绍了索引分类如聚集索引和二级索引，以及索引的使用技巧，包括最左前缀法则、范围查询、索引列运算和数据分布的影响。此外，还讨论了SQL性能分析、慢查询日志和如何利用索引来提升查询效率。
目录
show profile for query query_id;
索引概述
索引结构
B Tree
- 5阶的B树，每一个节点最多存储4个key，对应5个指针。
- 一旦节点存储的key数量到达5，就会裂变，中间元素向上分裂。
- 在B树中，非叶子节点和叶子节点都会存放数据。
B+Tree
- 所有的数据都会出现在叶子节点。
- 叶子节点形成一个单向链表。
- 非叶子节点仅仅起到索引数据作用，具体的数据都是在叶子节点存放的。
B+Tree索引
Hash索引
思考 -- Innodb为什么选择 B+Tree
二叉树一个节点只能有2个子节点，所以层级深，所以查询销量低
对于与B 树，非叶子节点也能保存数据，每一组节点都保存在页上(一个页的大小只有16K)，如果节点既要保存数据也有指针，则会减少指针的数量，导致增加树的高度，降低了查询速度。
索引分类
聚集索引下面挂的是行数据， 二级索引挂的是 行的id。
当此时通过name查询行数据时，先在二级索引中查到id，再去聚集索引中根据id查询行数据，被称之为 回表查询
思考
根据Id直接查询聚集索引获取行数据，根据name，先查询二级索引获取id，在去聚集索引中查询行数据，前者更快
因为一个page 16K，一个主键bigint占8 byte，指针占6 byte, 指针数量比存放数量大1，
n * 8 + (n+1)* 6 = 16 * 1024 ，则一个page可以存储1170个节点，1171个指针，
高度为2时那就连接1171个 page，一个page 16k，一行数据为1k，则一个page可以存储16行数据，所以 1171 * 16 = 18736行数据
高度为3时，那2级就连接1171个 page，3级1171*1171个，一个page 16k，一行数据为1k，则一个page可以存储16行数据，所以 1171 * 1171 * 16 = 21939856行数据
索引的语法
create index idx_user_name on tb_user(name);
create unique index idx_user_phone on tb_user(phone);
create index idx_user_profession_age_status on tb_user(profession,age,status);
create index idx_user_email on tb_user(email);
show index from tb_user;
drop index idx_user_email on tb_user;
SQL 性能分析
查看此数据库是不是已查询为主，如果是已查询为主，才有优化的必要。
-- 查询服务状态 7个_
show global status like 'Com_______';
慢查询日志
-- 慢查询日志是否开启 默认是关闭的
show variables like 'slow_query_log';
# 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2慢查询日志路径：/var/lib/mysql/localhost-slow.log
profile
查看数据库是否支持show profiles
SELECT @@have_profiling ;查看profiling是否关闭，0表示关闭，需要将其设置成1
-- 查询profiling是否开启
select @@profiling;
-- 如果是0,,将其设置成1
set profiling = 1;
show profiles
show profiles;
show profile for query query_id;
show profile for query 16;
show profile cpu for query 16;
explain
desc select * from tb_user where id = 1;
explain select * from tb_user where id = 1;展现的列以及相关含义，主要关注高亮列信息。
| id |
select
查询的序列号，表示查询中执行
select
子句或者是操作表的顺序
(id
相同，执行顺序从上到下；
id
不同，值越大，越先执行
)
。
|
|
select_type
|
表示
SELECT
的类型，常见的取值有
SIMPLE
（简单表，即不使用表连接
或者子查询）、
PRIMARY
（主查询，即外层的查询）、
UNION
（
UNION
中的第二个或者后面的查询语句）、
SUBQUERY
（
SELECT/WHERE
之后包含了子查询）等
|
|
type
|
表示连接类型，性能由好到差的连接类型为
NULL
、
system
、
const
、 eq_ref、
ref
、
range
、
index
、
all
。
----------------------------------------------------------------------------------
NULL ： 一般是没有查询表的情况 例如：select curdate()
system : 表中只有一行数据或者空表，这是const类型的一个特例。且只能用于myisam和memory表。如果是innoDB引擎表，type列在这个情况下通常是all或者index。
const : 使用主键或者unique索引
ref ： 使用非唯一索引
range ：索引范围查询，常见于使用=，<>，>=，<，<=，is null，between，in()或者like等运算符的查询中
Index ： 索引全表扫描，把索引从头到尾扫一遍
all ： 全表扫描，性能最差。
|
|
possible_key
|
显示可能应用在这张表上的索引，一个或多个。
|
|
key
|
实际使用的索引，如果为
NULL
，则没有使用索引。
|
|
key_len
|
表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长
度，在不损失精确性的前提下， 长度越短越好 。
|
|
rows
|
MySQL
认为必须要执行查询的行数，在
innodb
引擎的表中，是一个估计值， 可能并不总是准确的。
|
|
filtered
|
表示返回结果的行数占需读取行数的百分比，
filtered
的值越大越好。
|
| Extra | 额外信息 |
索引使用
最左前缀法则
在 tb_user 表中，有一个联合索引，这个联合索引涉及到三个字段，顺序分别为：profession， age，status。
对于最左前缀法则指的是，查询时，最左变的列，也就是profession必须存在，否则索引全部失效。 而且中间不能跳过某一列，否则该列后面的字段索引将失效。 接下来，我们来演示几组案例，看一下
思考
可以看到，是完全满足最左前缀法则的，索引长度54，联合索引是生效的。
范围查询
当范围查询使用> 或 < 时，走联合索引了，但是索引的长度为49，就说明范围查询右边的status字 段是没有走索引的。
索引列运算
当根据phone字段进行函数运算操作之后，索引失效。
字符串不加引号
我们会明显的发现，如果字符串不加单引号，对于查询结果，没什么影响，但是数据库存在隐式类型转换，索引将失效。
模糊查询
OR连接
由于age没有索引，所以即使id、phone有索引，索引也会失效。所以需要针对于age也要建立索引。
注意:虽然上例中OR两侧的索引都存在时，执行执行计划显示会使用到索引，但是这个是不一定，根据本地多次测试，优化器是否使用索引似乎和查询字段值的重复率有关,具体没有找到相关文档作证，只能列举一下情况，以供大家参考
当前表的索引情况: id主键，phone 唯一索引，age普通索引
当查询id or phone时，会走索引
当查询id or age=33时: 会走索引
age=33所占的比例为 :
当查询id or age=23时:不走索引
age=23所占的比例为:
由上可见，当OR两侧字段都存在索引时，是否使用索引查询或者全部扫描，优化器内部会根据数据分布情况来判断，不是像其他博客上面所说的以偏概全 OR一定使索引失效，或者一定会使用索引。
以上是字段都是单独索引时，如果OR查询条件
1.一侧是单独索引，一侧是联合索引，并且是最左前的字段
和两侧的单独索引情况一样。
2.当一个测单独索引，一侧是联合索引，非最左前字段
相当于 一侧是单独索引，一侧是没有索引，则一定不会走索引
3.当一侧是联合索引的最左前字段，一侧是联合索引的其他字段
可以发现都不会走索引
数据分布影响
SQL提示
执行SQL，创建profession的单列索引：create index idx_user_pro on tb_user(profession);
- use index ： 建议MySQL使用哪一个索引完成此次查询（仅仅是建议，mysql内部还会再次进行评估）。
explain select * from tb_user use index(idx_user_pro) where profession = '软件工 程';
-
ignore index ： 忽略指定的索引。
explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工 程';
-
force index ： 强制使用索引。
explain select * from tb_user force index(idx_user_pro) where profession = '软件工 程';
覆盖索引
尽量使用覆盖索引，减少select *。 那么什么是覆盖索引呢？ 覆盖索引是指 查询使用了索引，并
explain select id, profession from tb_user where profession = '软件工程' and age = 31 and status = '0' ;
explain select id,profession,age, status from tb_user where profession = '软件工程' and age = 31 and status = '0' ;
explain select id,profession,age, status, name from tb_user where profession = '软 件工程' and age = 31 and status = '0' ;
explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0';
从上述的执行计划我们可以看到，这四条SQL语句的执行计划前面所有的指标都是一样的，看不出来差 异。但是此时，我们主要关注的是后面的Extra，前面两天SQL的结果为 Using where; Using Index ; 而后面两条SQL的结果为: Using index condition 。
| Extra | 含义 |
|
Using where; Using Index
|
查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据
|
|
Using index condition
|
查找使用了索引，但是需要回表查询数据
|
因为，在tb_user表中有一个联合索引 idx_user_pro_age_sta，该索引关联了三个字段
profession、age、status，而这个索引也是一个二级索引，所以叶子节点下面挂的是这一行的主
键id。 所以当我们查询返回的数据在 id、profession、age、status 之中，则直接走二级索引
直接返回数据了。 如果超出这个范围，就需要拿到主键id，再去扫描聚集索引，再获取额外的数据了，这个过程就是回表。 而我们如果一直使用select * 查询返回所有字段值，很容易就会造成回表
查询（除非是根据主键查询，此时只会扫描聚集索引）。
图形实例
前缀索引
create index idx_xxxx on table_name(column(n)) ;
单列索引 与 联合索引
索引使用的原则
总结
扫一扫
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
