MySQL总结 - 索引原理以及 SQL 优化

---

前言

---

一、索引

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

主键索引

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

 PRIMARY KEY(key1, key2)

唯一索引

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

UNIQUE(key)

普通索引

允许出现相同的索引内容；

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

组合索引

对表上的多个列进行索引

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

全文索引

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

在短字符串中用 LIKE %；在全文索引中用 match 和 against；

主键选择

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

1. 如果显示设置 PRIMARY KEY，则该设置的 key 为该表的主键；
2. 如果没有显示设置，则从非空唯一索引中选择；
   只有一个非空唯一索引，则选择该索引为主键；
   有多个非空唯一索引，则选择声明的第一个为主键；
3. 没有非空唯一索引，则自动生成一个 6 字节的 _rowid 作为主键；

二、约束

为了实现数据的完整性，对于 innodb，提供了以下几种约束，primary key，unique key，foreign key，default，not null；

外键约束

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

 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;

约束与索引的区别

创建主键索引或者唯一索引的时候同时创建了相应的约束；但是约束时逻辑上的概念；索引是一个数据结构既包含逻辑的概念也包含物理的存储方式；

三、索引实现

索引存储

innodb 由段、区、页组成；段分为数据段、索引段、回滚段等；区大小为 1 MB（一个区由 64 个连续页构成）；页的默认值为 16k；页为逻辑页，磁盘物理页大小一般为 4K 或者 8K；为了保证区中的页的连续，存储引擎一般一次从磁盘中申请 4~5 个区；

页

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

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

B+树

全称：多路平衡搜索树，减少磁盘访问次数；用来组织磁盘数据，以页为单位，物理磁盘页一般为 4K，innodb 默认页大小为 16K；对页的访问是一次磁盘 IO，缓存中会缓存常访问的页；

平衡二叉树（红黑树、AVL 树）

特征：非叶子节点只存储索引信息，叶子节点存储具体数据信息；叶子节点之间互相连接，方便范围查询；

每个索引对应着一个 B+ 树；

B+ 树层高问题

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

innodb 一个节点 16KB；

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

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

结论：
2 层 B+ 树叶子节点 1024 个，可容纳最大记录数为： 1024 * 16 = 16384；
3 层 B+ 树叶子节点 1024 * 1024，可容纳最大记录数为：1024 * 1024 * 16 = 16777216；
4 层 B+ 数叶子节点 1024 * 1024 * 1024，可容纳最大记录数为：1024 * 1024 * 1024 * 16 = 17179869184;

聚集索引

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

在这里插入代码片
# table  id  name
 select * from user where id >= 18 and id < 40;

辅助索引

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

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

四、innodb 体系结构

Buffer pool

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

Change buffer

Change buffer 缓存辅助（二级）索引的数据变更（DML 操作）这些数据并不在 buffer pool 中，Change buffer 中的数据将会异步 merge 到 buffer pool 中，当下次从磁盘当中读取非唯一索引的数据；同时会定期合并到索引页中。

free list 组织 buffer pool 中未使用的缓存页；flush list 组织 buffer pool 中脏页，也就是待刷盘的页；lru list 组织 buffer pool 中冷热数据，当 buffer pool 没有空闲页，将从 lru list 中最久未使用的数据进行淘汰；

最左匹配原则

对于组合索引，从左到右依次匹配，遇到 > < between like就停止匹配；

覆盖索引

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

索引下推

为了减少回表次数，提升查询效率；在 MySQL 5.6 的版本开始推出；

MySQL 架构分为 server 层和存储引擎层；

没有索引下推机制之前，server 层向存储引擎层请求数据，在 server 层根据索引条件判断进行数据过滤；

有索引下推机制之后，将部分索引条件判断下推到存储引擎中过滤数据；最终由存储引擎将数据汇总返回给 server 层；

索引失效

select … where A and B 若 A 和 B 中有一个不包含索引，则索引失效；

索引字段参与运算，则索引失效；例如：from_unixtime(idx) = ‘2021-04-30’; 改成 idx = unix_timestamp(“2021-04-30”)

索引字段发生隐式转换，则索引失效；例如：将列隐式转换为某个类型，实际等价于在索引列上作用了隐式转换函数；

LIKE 模糊查询，通配符 % 开头，则索引失效；例如：select * from user where name like ‘%ang’;

在索引字段上使用 NOT <> != 索引失效；如果判断 id <> 0 则修改为 idx > 0 or idx < 0；

组合索引中，没使用第一列索引，索引失效；

索引原则

查询频次较高且数据量大的表建立索引；索引选择使用频次较高，过滤效果好的列或者组合；

使用短索引；节点包含的信息多，较少磁盘 IO 操作；比如：smallint，tinyint；

对于很长的动态字符串，考虑使用前缀索引；

对于组合索引，考虑最左侧匹配原则和覆盖索引；

尽量选择区分度高的列作为索引；该列的值相同的越少越好；

尽量扩展索引，在现有索引的基础上，添加复合索引；最多 6 个索引

不要 select *； 尽量只列出需要的列字段；方便使用覆盖索引;

索引列，列尽量设置为非空；

可选：开启自适应 hash 索引或者调整 change buffer；

五、优化器成本分析

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

成本分析步骤

找出所有可能需要使用到的索引；
计算全表扫描的代价；
计算不同索引执行查询的代价；
对比找出代价最小的执行方案；

EXPLAIN

用来查看 SQL 语句的具体执行过程。

原理：模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何
处理 SQL 语句的。

id

select 查询的序列号，包含一组数字，表示查询中执行 select 子句或者操作表的顺序；

id 号分为三种情况：

1. id 相同，那么执行顺序从上到下；
2. id 不同，id 越大越先执行；
3. id 有相同的也有不同的，id 相同的按 1 执行，id 不同的按 2 执行；

select_type

主要用来分辨查询的类型，是普通查询还是联合查询还是子查询

table

对应行正在访问哪一个表，表名或者别名，可能是临时表或者 union 合并结果集;

1. 具体表名或者表的别名，从具体的物理表中获取数据；
2. 表明为 derivedN 的形式，表示 id 为 N 的查询产生的衍生表；
3. 当有 union result 的时候，表名是 union n1,n2 等的形式，n1,n2 表示参与 *union *的 id;

possible_keys

查询涉及到字段的索引，则这些索引都会列举出来，但是不一定采纳；

key

实际使用的索引，如果为 NULL，则没有使用索引

key_len

表示索引中使用的字节数；查询中使用的索引长度；在不损失精度的情况下长度越短越好。

rows

大致估算出找出所需记录需要读取的行数，反映了sql找了多少条数据，该值越小越好。

优化器选择过程

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

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';

---