4.1.1 MySQL索引原理及SQL优化

Linux C/C++服务器

MySQL索引原理及SQL优化

innodb

索引组成

一种有序的存储结构，按照单个或这多个列的值进行排序，目的是为了提升搜索效率
索引分类：主键索引、唯一索引、普通索引、组合索引、以及全文索引（elasticsearch）

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

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

• 主键索引
  非空唯一索引，一个表只有一个主键索引；在 innodb 中，主键索引的 B+ 树包含表数据信息(每行数据)，也叫聚集索引，其他的索引叫做辅助索引
  RIMARY KEY(key)

• 唯一索引
  不可以出现相同的值，可以有 NULL 值
  UNIQUE(key)

• 普通索引
  允许出现相同的索引内容
  INDEX(key)
KEY(key[,...])

• 组合索引
  对表上的多个列进行索引，多个列构成唯一KEY
  INDEX idx(key1,key2[,...]);
UNIQUE(key1,key2[,...]);
PRIMARY KEY(key1,key2[,...]);

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

• 索引的代价

1. 索引B+树比较占用空间，所以工程应用上一般有最多创建6个索引的限制
2. 修改索引，会修改B+树使增删改dml变慢

索引B+树

全称：多路平衡搜索树，减少磁盘访问次数；用来组织磁盘数据，以页为单位，物理磁盘页一般为 4K，innodb 默认页大小为16K；对页的访问是一次磁盘 IO，缓存中会缓存常访问的页
特征：非叶子节点只存储索引信息，叶子节点存储具体数据信息；叶子节点之间互相连接（双向链表），方便范围查询；每个索引对应着一个 B+ 树；
默认的叶子节点的大小为16k，每个叶子节点只存储mysql表中一行的数据，如果一行的数据大于16k，多出来的数据会存储在一个公共的固定区域

聚集索引B+树

按照主键构造的 B+ 树；叶子节点中存放数据页；数据也是索引的一部分；
通过主键查找数据，innodb通过去聚集索引B+树中一次查询，即可返回

辅助索引B+树

叶子节点不包含行记录的全部数据；
辅助索引的叶子节点中，除了用来排序的 key 还包含一个 bookmark ；该书签存储了聚集索引的 key（主键id）；
辅助索引包含唯一索引、普通索引、组合索引，通过辅助索引查找数据，会先去辅助索引B+树中找到主键索引，再用主键索引去聚集索引B+树中查找要找的那行数据（回表查询）

索引使用场景

只有我们使用这三个关键字where，group by，order by，并且为其后面的字段创建索引之后才会触发B+树查询

//1.对name, city, age创建索引
//2.使用where，group by，order by，对索引关键词name，city，age进行查询
select * from staff  where name = mark;
select * from staff group by city;
select * from staff order by age;

索引创建原则

什么样的数据适合创建索引？不同数据创建索引原则有什么不同？

• 尽量选择区分度高的列作为索引；该列的值相同的越少越好
  select count(distinct idx) / count(*) from table_name; //查看某列区分度语句
• 经常修改的列，不要创建索引，维护B+树的代价非常高
• 查询频次较高且数据量大的表建立索引；索引选择使用频次较高，过滤效果好的列或者组合；
• 尽量扩展索引，在现有索引的基础上，添加复合索引；最多6个索引

覆盖索引

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

索引下推(Using index condition)

优化组合索引数据查询速度
最左匹配原则:对于组合索引，从左到右依次匹配; 遇到 > < between like就停止匹配；
索引下推：为了减少回表次数，提升查询效率；在 MySQL 5.6 的版本开始推出；
MySQL 架构分为 server 层和存储引擎层；没有索引下推机制之前，server 层向存储引擎层请求数据，存储引擎按照第一个条件回传数据至server，server 层根据索引条件判断进行数据过滤；
有索引下推机制之后，将部分索引条件判断下推到存储引擎中过滤数据；最终由存储引擎将数据汇总返回给 server 层；

索引失效

• select ... where A and B 若 A 和 B 中有一个不包含索引，则索引失效；
• 索引字段参与运算，则索引失效；例如： from_unixtime(idx)= '2021-04-30';
• 索引字段发生隐式转换，则索引失效；例如：将列隐式转换为某个类型，实际等价于在索引列上作用了隐式转换函数；
• LIKE 模糊查询，通配符 % 开头，则索引失效；例如： select* from user where name like '%Mark';
• 在索引字段上使用 NOT <> != 索引失效；如果判断 id <> 0 则修改为 idx > 0 or idx < 0；
• 组合索引中，没使用第一列索引，索引失效；

SQL优化

优化器成本分析

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

• 成本分析步骤
  1.找出所有可能需要使用到的索引；
  2.计算全表扫描的代价；
  3.计算不同索引执行查询的代价；
  4.对比找出代价最小的执行方案；

EXPLAIN

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

innodb 体系结构

Buffer pool

Buffer pool 缓存表和索引数据，默认大小128M；采用 LRU 算法，让 Buffer pool 只缓存比较热的数据 ；已修改数据落盘
free list 组织 buffer pool 中未使用的缓存页；flush list 组织buffer pool 中脏页，也就是待刷盘的页；lru list 组织 bufferpool 中冷热数据，当 buffer pool 没有空闲页，将从 lru list 中最久未使用的数据进行淘汰；

Change buffer

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

慢日志查询

如何找到实际使用中比较慢的语句？

开启

查看

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

mysqldumpslow

查找最近10条慢查询日志

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

SHOW PROFILE

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

SHOW PROCESSLIST

查看连接线程；可以查看此时线上运行的 SQL 语句；
如果要查看完整的 SQL 语句： SHOW FULL PROCESSLIST; 然后优化该语句；