MySQL索引原理与优化

存储引擎用于快速查找记录的一种数据结构。需要额外开辟空间和数据维护 工作。

索引是物理数据页存储，在数据文件中(InnoDB，ibd文件)，利用数据页(page)存储

存储

索引定义

数据结构

缺点

:左子树的键值小于根的键值，右子树的键值大于根的键值。 每个节点分别保存字段数据和一个指向对应数据记录物理地址的指针.

含义

二叉查找树

在符合二叉查找树的条件下，还满足任何节点的两个子树的高度最大差为1

结构图

旋转流程

AVL树的旋转方式

如何维持

叶子节点的层级减少

形态上能够保持平衡

优点

平衡二叉树 (AVL Tree)

思想设计

B-Tree的阶=B-Tree中所有节点的子树个数的最大值【B-Tree的每个节点的元素可以视为一次I/O读取，树的高度表示最多的I/O次数】

分支节点至少有(m/2)颗子树 (除去根节点和叶子节点其他都是分支节点)

满足条件

图

key为记录的键值，对应表中的主键值(聚 簇索引)

data为一行记录中除主键外的数据

模型图

结构存储索引的特点

查找步骤

优点: B树可以在内部节点存储键值和相关记录数据，因此把频繁访问的数据放在靠近根节点的位置将大大提高热点数据的查询效率。

使用场景: B树主要应用于文件系统以及部分数据库索引，如MongoDB，大部分关系型数据库索引则是使用B+树实现

总结

B-Tree

B+Tree是在B-Tree基础上的一种优化，使其更适合实现存储索引结构，InnoDB存储引擎就是用B+Tree 实现其索引结构。

非叶子节点只存储键值信息.

所有叶子节点之间都有一个链指针.

数据记录都存放在叶子节点中.

特征

图形模式

降低树的高度，增大节点存 储数据量

扫库和扫表能力更强【如果我们要根据索引去进行数据表的扫描，对B Tree进行扫描，需 要把整棵树遍历一遍，而B+Tree只需要遍历他的所有叶子节点即可(叶子节点之间有引用)】

磁盘读写能力更强【加载到磁盘的关键字信息更多】

排序能力更强，如上面的图中可以看出，B+Tree天然具有排序功能。【他的根节点和支节点不保存数据区，所有根节点和支节点同样大小的 情况下，保存的关键字要比B Tree要多】

查询效率更加稳定，每次查询数据，查询IO次数一定是稳定的

优势

一颗B+Tree可以存放多少数据【4292 8704(3阶)】

B+Tree

等值比较查询，而不包 含排序或范围查询的需求，都适合使用哈希索引

哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。

哈希索引只支持等值比较查询。不支持任何范围查询和部分索引列匹配查找。

哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。

Hash索引

树和Hash结构测试链接：https://www.cs.usfca.edu/~galles/visualization/BST.html

建议使用int类型的自增【方便排序并且默认会在索引树的末尾增加主键值，对索引树的结构 影响最小。而且，主键值占用的存储空间越大，辅助索引中保存的主键值也会跟着变大，占 用存储空间，也会影响到IO操作读取到的数据量。】

注意问题

聚簇索引

将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置【非主键/二级索引】

回表问题

使用覆盖索引来避免回表【索引包含了所有需要查询的字段的值 (不需要回表)，这个索引就是覆盖索引】

非聚簇索引

索引

MySQL查询过程

id值越大优先级越高，越先被执行

ID

简单的select查询，查询中不包含子查询或者UNION

simple

查询中若包含任何复杂的子部分，最外层查询被标记

primary

在select或where列表中包含了子查询

subquery

union连接的两个select查询，第一个查询是dervied派生表，除了第一个表外，第二个 以后的表select_type都是union

union

在from列表中包含的子查询被标记为derived(派生表)，MySQL会递归执行这些子 查询，把结果放到临时表中

derived

UNION 的结果

union result

select_type

system > const > eq_ref > ref > range > index > ALL【最佳类型到最坏类型进行排序】

system

const

对于前一个表中的每个一行，后表只有一行被扫描【当联接使用索引的部分都是主键或惟一非空索引】

eq_ref

ref

range

index

ALL

type

显示可能应用到这张表上的索引

possible_keys

实际使用的索引【查询中若使用了覆盖索引，则该索引仅出现在key列表中。】

key

key_len

索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值

估算的找到所需的记录所需要读取的行数

rows

它指返回结果的行占需要读到的行(rows列的值)的百分比

filtered

得到所需结果集，需要对所有记录进行文件排序【这类SQL语句性能极差，需要进行优化】

Using filesort

需要使用临时表来存储结果集，常见于排序和分组查询

Using temporary

Using where

Using index

Using join buffer

Using index condition

using index :使用覆盖索引的时候就会出现

using where :在查找使用索引的情况下，需要回表去查询所需的数据

using index condition :查找使用了索引，但是需要回表查询数据

using index & using where :查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

同时出现

extra

字段说明

Explain性能分析

场景

例子

首先会对联合索引最左边的字段进行排序

在第一个字段的基础之上 再对第二个字段进行排序

所以如果直接使用第二个字段 user_age 通常是使用不到索引的.

分析

分析图

底层原理

最佳左前缀法则

字符串不加单引号 (隐式类型转换)

不要在索引列上做任何计算

范围之后全失效

避免使用 is null 、 is not null、!= 、or

like查询为范围查询，%出现在左边，则索引失效。%出现在右边索引未失效.

解决方案

解决%出现在左边索引失效的方法

%号在右: 由于B+树的索引顺序，是按照首字母的大小进行排序，%号在右的匹配又是匹配首 字母。所以可以在B+树上进行有序的查找，查找首字母符合要求的数据。所以有些时候可以 用到索引.

%号在左: 是匹配字符串尾部的数据，我们上面说了排序规则，尾部的字母是没有顺序的， 所以不能按照索引顺序查询，就用不到索引.

两个%%号: 这个是查询任意位置的字母满足条件即可，只有首字母是进行索引排序的，其他 位置的字母都是相对无序的，所以查找任意位置的字母是用不上索引的.

like 失效的原理

like以%开头会使索引失效

优化原则详解

slow_query_log【默认OFF】

新版(5.6及以上版本)MySQL数据库慢查询日志存储路径。

slow-query-log-file

慢查询阈值，当查询时间多于设定的阈值时，记录日志。

long_query_time

慢查询参数

-- 编辑配置vim /etc/my.cnf-- 添加如下内容slow_query_log =1 slow_query_log_file=/var/lib/mysql/ruyuan-slow.log-- 重启MySQLservice mysqld restartmysql> show variables like '%slow_query%';

修改配置文件my.cnf

永久生效

set global slow_query_log=1

重启失效

开启了慢查询日志只对当前数据库生效

set global long_query_time=1;【重新连接或新开一个会话才能看到 修改值】

什么样的SQL才会记录到慢查询日志里面呢

log_output='FILE' 表示将日志存入文件，默认值 是'FILE'

。log_output='TABLE' 表示将日志存入数据库，这样日志信息就会被写入到 mysql.slow_log

指定日志的存储方式

set global log_queries_not_using_indexes=1;

未使用索引的查询也被记录到慢查询日志

慢查询配置方式

# Time: 2022-02-23T13:50:45.005959Z# User@Host: root[root] @ localhost [] Id: 3# Query_time: 3.724273 Lock_time: 0.000371 Rows_sent: 5 Rows_examined: 5000000 SET timestamp=1645624245;select * from test_index where hobby = '20009951' or hobby = '10009931' or hobby = '30009931' or dname = 'name4000' or dname = 'name6600';

日志分析

慢查询设置与测试

慢查询日志分析

锁表导致查询一直处于等待状态

等待时间长

1.查询语句写的烂2.索引失效 3.关联查询太多join 4.服务器调优及各个参数的设置

执行时间长

原因

2 定位优化对象的性能瓶颈(在优化之前了解性能瓶颈在哪)

3. 明确优化目标

4. 从explain执行计划入手

5. 永远用小的结果集驱动大的结果集

6. 尽可能在索引中完成排序

7. 只获取自己需要的列

8. 只使用最有效的过滤条件

9. 尽可能避免复杂的join和子查询

1.较为频繁的作为查询条件的字段应该创建索引.

2.唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件【当一条 Query所返回的数据超过了全表的15%的时候，不应该再使用索引扫描完成这个Query】

3.更新非常频繁的字段不适合创建索引

4.不会出现在WHERE子句中的字段不该创建索引.

创建索引

1.对于单键索引，尽量选择针对当前Query过滤性更好的索引.

选择合适索引

10. 合理设计并利用索引

优化思路

慢查询SQL优化

MySQL索引原理与优化