MySQL索引及优化原理_mysql 索引优化及具体原理

MySQL索引及优化原理

一 索引介绍

索引，是一种物理概念，是关系数据库中对某一列或多个列的值进行预排序的数据结构。通过使用索引，可以让数据库系统不必扫描全表，快速定位到符合条件的记录，这样就大大加快了查询速度。

索引分类：

1. 主键索引：非空唯一索引，一个表只有一个主键索引；innodb中表是索引组织表，每张表有且仅有一个主键：

PRIMARY KEY(key);
1

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

在innodb中，主键索引的B+树包含表数据信息。

2. 唯一索引：不可以出现相同的值，可以有NULL值。

UNIQUE(key)
1

3. 普通索引：可以出现相同的值。

INDEX(key)
-- OR
KEY(key[,...])
1
2
3

5. 组合索引：对表上的多个列进行索引

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

6. 全文索引：将存储在数据库当中的整本书和整篇文章中的任意内容信息查找出来的技术；

二 约束

约束，是指对表中数据的一种约束，能够帮助数据库管理员更好地管理数据库，并且能够确保数据库中数据的正确性和有效性。

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

约束分类：

1. primary key；
2. unique key；
3. foreign key；
4. default；在执行insert命令的时候，如果没有显示指定某个列的值，那么把默认约束值插入到该列中。每个列只能有一个default约束。
5. not null；某列不接受NULL值

其中MyISAM存储引擎本身并不支持外键，只起到注释作用；而innodb完整支持外键；

三 索引实现

3.1 索引存储方式：

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

3.2 页介绍

页是innodb磁盘管理的最小单元，默认16k，可通过innodb_page_size参数来修改。B+树的一个节点的大小就是该页的值。

3.3 MySQL中的B+树

索引在磁盘中是以B+树的方式存储的。

B+树全称是多路平衡搜索树，，它用来组织磁盘数据，能够减少磁盘访问次数。它以页为单位，对页的访问是一次磁盘io。缓存中会缓存常访问的页。
它的特征是：非叶子节点只存储索引信息，叶子节点存储具体数据信息。叶子节点之间互相连接，方便范围查询。
每个索引都对应着一个B+树。

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

自增id超过类型最大值会报错。如果自增id是bigint，假设一秒钟插入1亿条数据，大概需要5849年才会用完索引。

四 索引实现上的分类

从实现方式上，索引可以分为聚集索引和辅助索引。聚集索引里存储的有全部的行记录，而辅助索引里只有部分的行记录。
innodb两个索引都有，但是MyISAM只有辅助索引。

4.1 聚集索引

聚集索引是按照主键构造的B+树，叶子节点中存放数据页。这表明数据也是索引的一部分。其他节点则只存储了primary key，以提供索引功能。聚集索引示意图如下所示：

根据B+树的定义，一个节点至少要存储两行数据，如果某个表一行的数据就大于16K，那么会对行数据进行拆分，B+树的一个节点只会存储一行的6、7K的数据，其他的数据则存储在其他位置，并由指针指向那里。

4.2 辅助索引

叶子节点不包含行记录的全部数据，它只包含用来排序的key和bookmark。bookmark存储了聚集索引的key。其他节点也是只存储了key，只提供索引功能。

五 Innodb的体系结构

Innodb的体系结构如下图所示：

5.1 Buffer Pool

Buffer pool是用来缓存表和索引数据的。它采用LRU算法，让Buffer pool只缓存比较热的数据。
LRU（Least Recently Used）是一种最近最少使用的页面置换算法。Buffer Pool的LRU算法和其他的算法有些许的不同。当一页数据从此番中被查询到的时候，会将这个页面插入到Buffer Pool的中间位置，每当Buffer Pool中的页被访问到了，页就会被Buffer Pool中前移一个单位。当Buffer Pool满了之后，会将最后一页的内容移除。

5.2 Change Buffer

Change buffer缓存非唯一索引的数据变更（DML操作）,Change buffer中的数据将会异步merge到磁盘中。

5.3 最左匹配原则

对于组合索引，从左到右依次匹配，遇到范围查询（ ><between like）就停止匹配。

5.4 辅助索引

从辅助索引中就能找到的数据，就不需要通过聚集索引查找。辅助索引树高度一般低于聚集索引树，所以如果能够使用辅助索引，就使用辅助索引，这样能减少磁盘IO。

六 索引失效

1. select … where A and B 若 A 和 B 中有一个不包含索引，则索引失效；
2. 索引字段参与运算，则索引失效；例如： from_unixtime(idx) = ‘2021-04-30’;
3. 索引字段发生隐式转换，则索引失效；例如： ‘1’ 隐式转换为 1 ；
4. LIKE 模糊查询，通配符 % 开头，则索引失效；例如： select * from user where name like ‘%Mark’;
5. 在索引字段上使用 NOT <> != 索引失效；如果判断 id <> 0 则修改为idx > 0 or idx < 0 ；
6. 组合索引中，没使用第一列索引，索引失效；
7. in + or 索引失效；单独的in 是不会失效的；not in 肯定失效的；

七 索引原则

1. 查询频次较高且数据量大的表建立索引；索引选择使用频次较高，过滤效果好的列或者组合；
2. 使用短索引；节点包含的信息多，较少磁盘io操作；比如：smallint，tinyint；
3. 对于很长的动态字符串，考虑使用前缀索引；

• 有时候需要索引很长的字符串，这会让索引变的大且慢，通常情况下可以使用某个列开始的部分字符串，这样大大的节约索引空间，从而提高索引效率。
  但这会降低索引的区分度，索引的区分度是指不重复的索引值和数据表记录总数的比值。索引的区分度越高则查询效率越高，因为区分度更高的索引可以让mysql在查找的时候过滤掉更多的行。
  对于BLOB , TEXT ,VARCHAR 类型的列，必要时使用前缀索引，因为mysql 不允许索引这些列的完整长度，使用该方法的诀窍在于要选择足够长的前缀以保证较高的区分度。

4. 对于组合索引，考虑最左匹配原则和覆盖索引。
5. 尽量选择区分度高的列作为索引。该列的相同值越少越好。
6. 尽量扩展索引，在现有索引的基础上添加复合索引（复合索引关键字包含现有的索引），最多能有6个索引，因为索引太多会增加维护负担。
7. 不要select *，尽量只列出需要的列字段，方便使用覆盖索引。
8. 索引列，列尽量设置为非空。
9. 开启自适应hash索引或者调整change buffer。

八 优化器

8.1 优化器成本分析

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

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

8.2 开启优化器的方法

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';
1
2
3
4
5
6
7
8

九 EXPLAIN

explain是用来用来查看SQL语句的具体执行过程。
前面添加explain的SQL语句，不会真正执行SQL语句，只是会模拟优化器执行SQL查询语句，从而知道MySQL是如何处理SQL语句的。

分析结果的每个字符段如下表所示：

十 慢查询日志

MySQL的慢查询日志是用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time默认的是10s。
默认情况下，MySQL不会开启慢查询日志，它需要我们手动开启，因为开启它会带来一定的性能影响。如果不是调优需要，不建议启动该参数。

使用慢日志查询的代码如下所示：

-- 查看
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
1
2
3
4
5
6
7
8
9
10