MySQL索引原理（索引、约束、索引实现、索引失效、索引原则）以及SQL优化

MySQL索引原理（索引、约束、索引实现、索引失效、索引原则）以及SQL优化 二、约束 三、索引实现 最左匹配原则覆盖索引 四、索引失效五、 索引原则六、优化器成本分析 七、优化SQL

一、索引

索引分类：主键索引、唯一索引、普通索引、组合索引、以及全文索引（）

主键索引

非空唯一索引，一个表只有一个主键索引；在 中，主键索引的 B+ 树包含表数据信息

PRIMARY KEY(key)

唯一索引

不可以出现相同的值，可以有NULL值

UNIQUE(key)

主键索引和唯一索引的差别：

唯一索引可以有一个NULL值，只要整体不重复就行了。而主键索引，是非空唯一索引。

普通索引

允许出现相同的索引内容

INDEX(key)
-- OR
KEY(key[,...])

组合索引

对表上的多个列进行索引

INDEX idx(key1,key2[,...]);
UNIQUE(key1,key2[,...]);
PRIMARY KEY(key1,key2[,...]);

比如有(key1,key2,key3)这个组合索引

先比较key1，如果key1相等再就比较key2,…

其他是合单个 索引是类似的。非叶子节点，就存储 组合索引（3个值），叶子节点存储(3个值+数据/主键索引)

全文索引

将存储在数据库当中的整本书和整篇文章中的任意内容信息查找出来的技术；关键词 ; 在短字符串中用 LIKE %

在全文索引中用 match 和

也就是说，全文索引通过关键字，可以将整篇文章给搜索出来

主键选择

中表是索引组织表，每张表有且仅有一个主键；

如果显示设置 KEY ，则该设置的key为该表的主键；如果没有显示设置，则从非空唯一索引中选择；只有一个非空唯一索引，则选择该索引为主键；有多个非空唯一索引，则选择声明的第一个为主键；没有非空唯一索引，则自动生成一个 6 字节的 作为主键； 所有的索引都会创建一个B+树，用key值，来保证有序。 二、约束

为了实现数据的完整性，对于，提供了以下几种约束， key， key，

key， , not null

外键约束（事务性）

外键用来关联两个表，来保证参照完整性；存储引擎本身并不支持外键，只起到注释作

用；而完整支持外键；

create table parent (
id int not null,
primary key(id)
) engine=innodb;
create table child (
id int,
parent_id int,
foreign key(parent_id) references parent(id) ON DELETE CASCADE ON UPDATE
CASCADE
) engine=innodb;
-- 被引用的表为父表，引用的表称为子表；
-- 外键定义时，可以设置行为 ON DELETE 和 ON UPDATE，行为发生时的操作可选择：
-- CASCADE 子表做同样的行为
-- SET NULL 更新子表相应字段为 NULL
-- NO ACTION 父类做相应行为报错
-- RESTRICT 同 NO ACTION
INSERT INTO parent VALUES (1);
INSERT INTO parent VALUES (2);
INSERT INTO child VALUES (10, 1);
INSERT INTO child VALUES (20, 2);
DELETE FROM parent WHERE id = 1;

外键在项目中不推荐使用。外键是为了保证数据的完整性，当这张表中数据删除的时候，外键关联的另一张也要删除这一行，这个事务由我们自己去定义实现。

比如下图中，代表删除，表示和父表做同样的行为，当前父表中删除这行，子表也会删除，因此外键是具备事务性的。

外键不建议使用的原因：要实现父表删除某行，通过外键关联事务，子表对应行也要进行删除。但是后来参与项目的人可能对事务联动不了解，进行了重复的删除，会导致bug。因此建议在项目中，删除父表某行，如果子表某行也想进行相应删除，那就进行手动写，（删除子表某行的）删除语句。

比如下图中，父表中删除id=1的行，子表中也会删除。

当不适用外键关联时，最后一句删除SQL可以等效为下面两句

DELETE FROM parent WHERE id = 1;
DELETE FROM child where parent_id=1;

约束与索引的区别

创建主键索引或者唯一索引的时候同时创建了相应的约束；但是约束时逻辑上的概念；索引是一个

数据结构既包含逻辑的概念也包含物理的存储方式；

三、索引实现 索引实现

由段、区、页组成；段分为数据段、索引段、回滚段等；区大小为 1 MB（一个区由64个

连续页构成）；页的默认值为16k；页为逻辑页，磁盘物理页大小一般为 4K 或者 8K；为了保证区

中的页的连续，存储引擎一般一次从磁盘中申请 4~5 个区；

表空间–>段空间–>区–>页–>行

页

页是 磁盘管理的最小单位；默认16k，可通过 参数来修改

B+ 树的一个节点的大小就是该页的值

B+树

B+树全称：多路平衡搜索树，减少磁盘访问次数；用来组织磁盘数据，以页为单位，物理磁盘页一般为 4K， 默认页大小为16K

对页的访问是一次磁盘io，缓存中会缓存常访问的页；

特征：非叶子节点只存储索引信息，叶子节点存储具体数据信息；叶子节点之间互相连接，方便范 围查询 每个索引对应着一个 B+ 树

与红黑树的区别：

红黑树：平衡二叉搜索树 （是一种平衡二叉树，但平衡没有那么严格）

B+树：多路平衡搜索树（高度平衡：叶子节点都在同一层（每条链路的高度一致））

- 搜索树：按照中序遍历它是一个有序的结构

- 平衡：平衡是树的高度，提供一个稳定搜索时间复杂度

由于磁盘读取的操作比较耗费时间，相比内存操作，磁盘操作的时间代价更高，因此相比红黑树，使用层高较低的B+树更为合适，这样执行的磁盘读取操作次数就会非常少，更多的是在内存中对读取进来的数据进行查找。

B+树：

B+树中每个节点映射到磁盘都是16k的空间，也就是一个页（空间）。

B+树层高问题

B+树的一个节点对应一个数据页；B+树的层越高，那么要读取到内存的数据页越多，io次数越

多；

一个节点16kB；

假设：

key为且指针大小6byte，假设一行记录的大小为1kB；

那么一个非叶子节点可存下16kB/=1024个（key+point）；每个叶子节点可存储1024行数

据；

结论：

2层B+树叶子节点1024个，可容纳最大记录数为： 1024 * 16 = 16384；

3层B+树叶子节点1024 * 1024，可容纳最大记录数为：1024 * 1024 * 16 = ；

4层B+数叶子节点1024 * 1024 * 1024，可容纳最大记录数为：1024 * 1024 * 1024 * 16 =

;

如果一个表内的数据超过500w行，就要分表分库了，是为了保证B+树的层高，不能太高，否则会增加读取时磁盘操作的次数，从而影响性能。

关于自增id

超过类型最大值会报错；

类型 范围： ;

假设采用 1秒插入1亿条数据，大概需要5849年才会用完索引

聚集索引

按照主键构造的 B+ 树；叶子节点中存放数据页；数据也是索引的一部分

也就是说，叶子节点既有索引，又有数据

# table id name
select * from user where id >= 18 and id < 40;

比如查找上面的内容，记得我们要查找的范围是id >= 18 and id < 40

首先来到根节点，先看我们要查找的范围左边界，是18,正好属于p2指针指向的区域。

(注意：是左闭右开区间)

由于p2指针从页1来到页3

按之前的操作，进入p1指向的页

来到叶子节点，在这里查询数据采用二分查找方式，这里的结构相当于一个数组。

然后一个个开始查找，直到id >= 18 and id < 40中的40边界

由于b+树前后叶子节点可以直接访问，因而不需要通过回溯的方式，再去查找下一个位置。

如果当前叶子节点中访问完了，就向右边叶子节点继续去访问。

可以看到虽然页12里面不包含想要的数据，但是依然会进行访问里面第一个的索引，来判断。

因此总共进行了7次访问。

辅助索引

叶子节点不包含行记录的全部数据；辅助索引的叶子节点中，除了用来排序的 key 还包含一个

；该书签存储了聚集索引的 key；

-- 某个表 包含 id name lockyNum; id是主键，lockyNum存储辅助索引;
select * from user where lockyNum = 33;

聚集索引是主键创建的，其他索引是通过辅助索引去创建的。

聚集索引中叶子节点中是由 (索引+数据行) 组成

而辅助索引是通过中叶子节点是由 （索引+主键id）组成的。

也就是说辅助索引的叶子节点中并不包含数据本身，也就是没法直接读取到数据，但是它提供了主键id，可以通过这个id再去访问聚集索引，通过主键查询，获取数据，也叫做 回表查询。

下图中可以看到，一条线引向了（橙色）主键索引的B+树。

在中，通过主键查询使用的是聚集索引，只有辅助索引才使用回表查询。

在中，不管是主键还是非主键索引查询都要回表查询，只有 id from ..这种查询（id本身就是主键，而且只查询主键，因此不需要去堆表查询了，也就是覆盖索引）这种情况才不会回表。

中有三个文件：

后缀名用途

.frm

表信息文件

.myd

数据文件 （堆表进行组织的）

.myi

索引文件（B+树进行组织的）

.myi索引文件中的 B+树叶子节点 由（索引+行所在数据文件(.myd)的地址）组成。

通过行所在数据地址，去数据文件中读取数据，也就是说进行了回表操作。

中有两个文件：

后缀名用途

.frm

表信息文件

.ibd

数据文件 （B+树进行组织的）

聚集索引组织成B+树，对于辅助索引也通过一棵B+树来组织。

体系结构

pool

pool 缓存表和索引数据；采用 LRU 算法（原理如下图）让 pool 只缓存比较热的数

据 ；

The pool used data to be from , which

up (当访问磁盘io后，将数据放入内存的缓存中，下次访问的时候直接通过内存去访问，速度更加快)。

For of high- read , the pool is into pages that can

hold rows. For of cache , the pool is as a list

of pages; data that is used is aged out of the cache using a of the least used

(LRU) .(为了保证效率，将 pool分页存储，通过链表组织这些页，采用LRU算法，将不常使用数据的从 pool中删除)

从下图可以看出，新来的数据插入到5/8的位置,也就是old 的head，并不是所有数据都放入缓存中，经常使用的页会上升，不常使用的会下降，直到从内存中删除，也就是 pool只缓存比较热的数据。

Hash Index

自适应哈希索引，当注意到某些索引值被使用的非常频繁时，它会在内存中基于btree索引之上再创建一个哈希索引，这样就让btree索引也具有哈希索引的一些优点。（在不牺牲功能和稳定性的情况下，合理去使用工作负载和内存，来提升效率）

通过哈希直接指向 poll中的页。

缓存非唯一索引的数据变更（DML操作）， 中的数据将会异步 merge 到磁盘当中；

当比如查找一个 a=5的记录的时候，并不是只查找出这一条数据，它所在的整个数据页都会查找出来(每个数据页16KB)。

下次查找a=6的记录的时候，发现该页已经在内存中了，直接返回，不需要磁盘IO。

但是当时增、删、改操作时，并不会每一次操作都进行一次磁盘IO，使用 可以降低磁盘随机IO。

首先是可持久化的数据。当更新某个数据页时，该页在内存中，那么直接更新。如果该页不在内存中，那么先将更新操作记录在 中，这时不需要从磁盘中读出数据页。将操作记录到redo log中，防止机器意外关闭导致数据丢失。这时已经可以返回给客户端更新成功了，因为即使机器意外重启，也可以通过redo log找回数据。

使用的是 pool里的内存，不能无限增大，可以通过参数来动态设置，这个参数为50的时候，标识 的大小最多只能占用 pool 的50%。

MySQL中的

Log

The log is the area that holds data to be to the log files on disk. Log size

is by the ze . The size is 16MB. The of the

log are to disk. A large log large to run

the need to write redo log data to disk the . Thus, if you have

that , , or many rows, the size of the log saves disk I/O.

（并不是修改完的数据直接写入磁盘，而是暂存入Log ，然后周期性地刷入(flush)磁盘I/O）

最左匹配原则

在 MySQL 建立 联合索引（多列索引） 时会遵守最左前缀匹配原则，即 最左优先，在检索数据时从联合索引的最左边开始匹配。例如有一个 3

列索引（a,b,c），则已经对（a）、（a,b）、（a,b,c）上建立了索引。所以在创建 多列索引时，要根据业务需求，where 子句中

使用最频繁 的一列放在最左边。

根据最左前缀匹配原则，MySQL 会一直向右匹配直到遇到 范围查询（>、 3 and d = 4 时，如果建立（a,b,c,d）顺序的索引，d

是用不到索引的，如果建立（a,b,d,c）的索引则都可以用到，并且 where 子句中 a、b、d 的顺序可以任意调整。

如果建立的索引顺序是 （a,b） ，那么根据最左前缀匹配原则，直接采用查询条件 where b = 1 是无法利用到索引的。

对于创建的组合索引(‘name’,‘cid’)

使用语句where cid=1 and name='mark'

优化器会进行优化，交换‘name’和‘cid’顺序，从而走索引

覆盖索引

从辅助索引中就能找到数据，而不需通过聚集索引查找；利用辅助索引树高度一般低于聚集索引

树；较少磁盘 io；

不需要通过聚集索引查找，也就是说不需要回表查询

覆盖索引是一种数据查询方式，不是索引类型在索引数据结构中，通过索引值可以直接找到要查询字段的值，而不需要通过主键值回表查询，那么就叫覆盖索引查询的字段被使用到的索引树全部覆盖到

比如 name是一个辅助索引

此时 name fron talbe; 只访问这个键，那么就不需要进行回标查询。

四、索引失效

索引字段参与运算就会失效，因为在b+树中，通过idx可以比较索引，但是如果复杂运算就没法比了。

隐式转换也是这个原因，通过转换了，可以理解为参与运算了

在LIke模糊查询中，通配符%开头会索引失效，但是Mark%这样子是可以的，因为它至少还能比较前面几个字符。

没有使用第一列索引，就是没有符合最左前缀匹配原则，因为第二索引依靠前面一个索引去查询。

单独的in不会失效，但是 in后面再接or会失效，not in 肯定失效。

上面的一堆索引失效的情况，实际上就是没法通过b+树索引去查找。

五、 索引原则

有时候需要索引很长的字符串，这会让索引变的大且慢，通常情况下可以使用某个列开始的

部分字符串，这样大大的节约索引空间，从而提高索引效率，但这会降低索引的区分度，索

引的区分度是指不重复的索引值和数据表记录总数的比值。索引的区分度越高则查询效率越

高，因为区分度更高的索引可以让mysql在查找的时候过滤掉更多的行。对于 BLOB , TEXT ,

类型的列，必要时使用前缀索引，因为mysql 不允许索引这些列的完整长度，使用

该方法的诀窍在于要选择足够长的前缀以保证较高的区分度。

select count(distinct left(name,3))/count(*) as sel3,
count(distinct left(name,4))/count(*) as sel4,
count(distinct left(name,5))/count(*) as sel5,
count(distinct left(name,6))/count(*) as sel6,
from user;
alter table user add key(name(4));
-- 注意：前缀索引不能做 order by 和 group by

select count(distinct idx) / count(*) from table_name;
-- 或者
show index from student;
*************************** 1. row ***************************
Table: student
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 7
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.00 sec)
-- Cardinality 这个值代表 select count(distinct idx) / count(*) from
table_name;
-- 该值决定了优化器的执行计划的选择；
-- 立马更新 Cardinality 值
analyze table student;
-- 在非高峰时间段，对数据库中几张核心表做 analyze table 操作，这能使优化器和索引更
好的为你工作；

select @@innodb_adaptive_hash_index;
set global innodb_adaptive_hash_index=1; -- 默认是开启的
select @@innodb_change_buffer_max_size;
-- 默认值为25 表示最多使用1/4的缓冲池内存空间 最大值为50
set global innodb_change_buffer_max_size=30；

六、优化器成本分析

mysql 优化器主要针对 IO 和 CPU 会计算语句的成本；可能不会按照分析的原理来执行语句

成本分析步骤 七、优化SQL

用来查看SQL语句的具体执行过程。 原理：模拟优化器执行 SQL 查询语句，从而知道 mysql 是如何处理 sql 语句的。

慢日志查询

查看

SHOW GLOBAL VARIABLES LIKE 'slow_query%';
SHOW GLOBAL VARIABLES LIKE 'long_query%';

设置

SET GLOBAL slow_query_log = ON; -- on 开启 off 关闭
SET GLOBAL long_query_time = 4; -- 单位秒；默认10s；此时设置为4s

或者修改配置

slow_query_log = ON
long_query_time = 4
slow_query_log_file = D:/mysql/mysql57-slow.log

使用查找最近10条慢查询日志

mysqldumpslow -s t -t 10 -g 'select' D:/mysql/mysql57-slow.log

SHOW

# 查看是否开启
SELECT @@profiling;
# 设置开启
SET profiling = 1;
# 查看所有 profiles
show profiles;
# 查看query id 为 10 那条查询
show profile for query 10;
# 查看最后一条查询
show profile;
# 最后关闭
SET profiling = 0;

优化器的追踪

优化器根据解析树可能会生成多个执行计划，然后选择最优的的执行计划；

当和慢日志查询

都没法发现问题的时候，再使用 优化器的追踪

SHOW VARIABLES LIKE 'optimizer_trace';
-- 启用优化器的追踪
SET optimizer_trace='enabled=on';
-- 执行一条查询语句
SELECT * FROM information_schema.optimizer_trace;
-- 用完关闭
SET optimizer_trace="enabled=off";
SHOW VARIABLES LIKE 'optimizer_trace';

相关链接：

.7官方文档链接