MySQL 数据存储和优化------MySQL索引原理和优化 ---- （架构---索引---事务---锁---集群---性能---分库分表---实战---运维）持续更新

最新推荐文章于 2024-08-08 23:24:21 发布

殷丿grd_志鹏

最新推荐文章于 2024-08-08 23:24:21 发布

阅读量3k

点赞数

分类专栏：数据库文章标签： mysql 数据库架构全文检索索引

本文链接：https://blog.csdn.net/grd_java/article/details/123991209

版权

数据库专栏收录该内容

17 篇文章 2 订阅

订阅专栏

Mysql架构体系全系列文章主目录(进不去说明还没写完）`https://blog.csdn.net/grd_java/article/details/123033016`

本文只是整个系列笔记的第二章：MySQL索引原理和优化，只解释索引相关概念。

文章目录

1. 索引类型和索引使用细节

索引可以提高查询效率，影响where查询和order by排序，它可以从多方面进行分类，但是实际创建时，是根据应用层次不同，有不同的创建索引的方式，创建语法关键字也不同

从索引存储结构划分（底层使用数据结构不同）：B tree索引、Hash索引、FULLTEXT全文索引（也是使用倒排索引机制）、R tree索引
从应用层次划分：普通、唯一、主键、复合索引
从索引键值类型(字段类型)划分：主键、辅助（二级）索引
从数据存储和索引键值逻辑关系划分：聚簇索引（聚集索引）、非聚簇索引（非聚集索引）

普通索引

最基本的索引类型，基于普通字段建立的索引，没有任何限制（唯一性，主键等限制都没有）
创建方法如下（通过关键字index）：

# 直接对指定表的指定字段建立索引，单独的建立，索引名不可省略
create index <索引名> on tablename(字段名);# tablename是表名
# 用修改表结构的方式，把索引给表加上，索引名可以不指定，会自动生成
alter table tablename add index [索引名字](字段名);
# 创建表的时候，顺便把索引加上，索引名可以省略，会自动生成
create table tablename([....表字段信息],index[索引名字](字段名))
# 删除指定索引
drop index <索引名> on tablename;
# 查看指定表的所有信息，不指定索引名创建索引，索引名会自动生成，那么如果要删除，没有名字很麻烦，可以通过这个命令查看
show index from tablename;

查看索引信息show index from tablename;其中Key_name Comment就是索引名,PRIMARY这个索引，是主键索引，自动创建的。name是普通索引

唯一索引

和普通索引类似，但是唯一索引的字段值必须唯一，允许有空值，创建或修改表时追加唯一约束，就会自动创建对应的唯一索引。
手动创建唯一索引方法如下（和普通索引相比，只是关键字变了,unique）：

create unique index <索引名> on tablename(字段名);
alter table tablename add unique index[索引名](字段名);
create table tablename([...],unique[索引名](字段名));

主键索引

特殊的唯一索引，不允许有空值，创建或修改表时追加主键约束就会自动创建，每个表只能有一个主键。
自动创建，方式如下（就是让表有主键）：

# 创建表时，指定主键，主键索引会自动创建
create table tablename([...],primary key(字段名))
# 如果原来表没有主键，修改表结构时，添加主键，会自动创建主键索引
alter table tablename add primary key(字段名);

复合(组合)索引

前面都属于单一索引（索引列为1列），用户可以在多列上建立索引，叫做复合索引。在数据库操作期间所需开销更小，可以代替多个单一索引。
有两个索引概念，窄索引，宽索引。设计索引时，能用窄索引就不要用宽索引，窄索引往往比宽索引(组合索引)有效

窄索引：索引列为1-2列
宽索引：索引列超过2列

创建方式如下（和普通索引一模一样，只是字段名由1个变为多个）

# 和普通索引语法一样，只不过字段名由1个变为多个
create index <索引名> on tablename(字段名1,字段名2,.....);
# 修改表结构的方式建立
alter table tablename add index [索引名](字段名1,字段名2,.....);
# 创建表的时候顺便
create table tablename([...],index[索引名字](字段名1,字段名2,.....));

注意事项

复合索引字段有顺序，查询时如果需要使用此索引，需要按照所有字段顺序使用。例如select * from user where name =xx and age = xx，只会匹配(name,age)组合索引，不匹配(age,name)
索引不是越多越好，根据where条件建立索引，过多使用索引对更新操作效率有很大影响。
如果表已经建立(name,age)组合索引，就没必要单独建立(name)索引了，但是(age)依然需要单独建立，因为组合索引有顺序。反之如果只有(name)，根据需要可以选择建立(name,age)组合索引，不懂的看下面的例子。

select * from user where age = xx，不会匹配(name,age)，只会匹配(age,name)，如果没有建立(age,name)的需要，可以单独建立(age)
反之select * from user where name = xx，会去匹配(name,age)，无需单独建立(name)

全文索引：应该都知道在文本或字符串这样的字段上建立吧，不会有人往数值类型字段上建立全文索引吧！

说白了，也就面试问全文索引了，平常根本用不到。早期会用lucene做全文检索，现在主流的是Solr和ElasticSearch搜索引擎

查询时，数据量如果较少，可以使用like模糊查询，但是如果数据量大（大量文本数据），效率非常低
此时使用全文索引，查询速度会比like快很多倍，MySQL5.6版本以前，只有MyISAM存储引擎支持全文检索，MySQL5.6开始InnoDB引擎也支持了全文检索。
创建方式如下，和普通索引比，只是关键字变成了fulltext而已

create fulltext index <索引名> on tablename(字段名);
alter table tablename add fulltext [索引名](字段名);
create table tablename([...],fulltext key [索引名字](字段名));

全文检索有自己的语法格式，使用match和against关键字，和like模糊查询不同

# 全文检索user表name字段中，值是aaa字符的数据
select 
	* 
from 
	user 
where
	match(name) against('aaa');

全文索引的细节和参数

通过show variables like '%ft%'命令查看和全文检索有关的参数
可以发现innodb_ft_max_token_size和innodb_ft_min_token_size这两个参数，表示InnoDB引擎全文检索的最大和最小字符数，这里指定的是3-84，也就是说，如果你匹配"a"或"aa"这样的不在3-84个字符范围内的，是匹配不到的（就算你表里有），因为不够3个字符，而"aaa"才可以匹配到

另外，上面的ft_boolean_syntax参数也很重要，它的值是"+“, " " ,”-"…这些符号，也就是说，全文检索有切词功能，只有用这些符号分隔才看做一个词，比如用aaa进行全文检索。可以匹配(aaa,aaa a,aaa+a,b+aaa…)，也就是将aaa+a切成aaa和a两个词。但是不可匹配(aaaa,baaa,aaa^a…)这种没有分隔符，或者不是指定分隔符的。

可以发现上面的匹配，aaa并没有匹配出aaaa，因为默认是等值匹配。我们可以通过布尔模式（in boolean mode）和一些符号进行非等值通配，例如*号

如果不清楚自己使用的是什么引擎，可以通过show create table 表名;查看指定表使用的引擎。

如果你想通过set命令改变这个值，需要修改配置文件，上一个章节有步骤这里不在赘述(Binlog文件操作，开启binlog哪里有写到)https://blog.csdn.net/grd_java/article/details/123879609

2. 索引原理

2.1 使用的数据结构和算法

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

使用物理数据页存储，在数据文件（InnoDB是在.ibd文件），利用数据页存储。
可以加快检索速度，但是会降低增删改速度，索引的维护也需要代价

用到的算法和数据结构有：二分查找、Hash、B+Tree

二分查找（折半查找）：有序数组中查找指定数据的搜索算法，优点：等值查询、范围查询性能优秀。缺点：增删改数据时维护成本高（数组，因为需要元素有序，修改时涉及元素移动和数组扩容）。不会的自己去学一学，很简单的算法。
Hash结构：底层通过Hash表实现，根据<key,value>键值对存储数据的数据结构，根据key查找对应value值，单个key查询（hash索引）和等值查询一样，但是范围查询就需要全表扫描了。
为什么是等值呢？因为哈希表是数组+链表，或者数组+链表+红黑树实现。它通过哈希算法，计算下标，获取到下标后，再去数组指定下标的地方存值或取值。那么如果多个key计算出的下标相同，就按照顺序，向当前数组元素指定的链表或红黑树中添加结点。所以使用哈希表，一定要尽可能的减少碰撞（算出相同下标的情况尽可能少），否则链表或红黑树层级过多，会影响效率。

Hash索引在Mysql的Hash结构的主要应用是，Memory原生Hash索引、InnoDB自适应hash索引。
InnoDB自适应哈希索引是为了提高查询效率，InnoDB存储引擎会监控表上各个索引页的查询，当某些索引值访问非常频繁时，会在内存中基于B+Tree索引再创建一个哈希索引，使内存中B+Tree索引具备哈希索引的功能，可以快速定位访问频繁的索引页。
InnoDB的自适应哈希索引，用户只能选择开启或关闭，不能进行人工干涉。
通过命令show engine innodb status \G;命令查看innodb 自适应hash的状态！可以发现Adaptive hash index是插入到Buffer中，也就是占用Buffer Pool的空间。其中0.00 hash searches/s，0.00 non-hash searches/s。分别表示使用了hash的效率和不使用hash的效率，如果使用hash的效率反而不如不使用hash的效率，请大家关闭自适应hash功能。

通过show variables like '%innodb_adaptive%'命令查看自适应hash的相关参数，可以通过set命令控制它们，其中innodb_adaptive_hash_index参数是控制自适应hash是否启用的，ON代表启用

2.2 B+树，为什么用B+树

Mysql索引底层使用B+树实现，B+树是B树的一种（升级版），所以很多人会说存储到B树中。就像，平衡二叉树是二叉树的一种，我们一般会说，用的是二叉树
根据官方文档的解释，大部分索引（PRIMARY KEY、UNIQUE、INDEX、FULLTEXT）存储到B-tree（B树中）。例外，空间数据类型上的索引使用R树，内存表也支持哈希索引，InnoDB使用反向列表作为FULLTEXT（全文检索）索引

使用B+树实现，是因为它对其它快速索引数据结构，更适合数据库场景

散列表（Hash表）的优点和缺点：虽然等值查询很快，但是数据库文件众多，且大部分数据不是等值查询，使用散列表会浪费大量内存空间，并且速度得不到体现.而且说白了算哈希，最后存储到的是链表，一旦链表长度过长，需要转换为红黑树，而红黑树的问题就是深度会过深，影响效率

Binary Search Tree二叉搜索树的优点缺点：有序，可以二分的快速检索目标，但是二叉搜索树，右子树一定比左子树大，这就有可能出现以下图片中的问题，深度太深，反而没有了树的优点，查找变成了和链表一样的效率。硬件层面，如果我要找7，需要磁盘预读6次数据页，也就是频繁IO6次。

AVL 平衡二叉树，它有一个旋转的效果，当最短分支和最长分支高度差超过1时，将发生旋转，让它平衡。这是优点，也是缺点，优点在于，查询效率的提升。缺点在于，大体量的数据，在插入，删除过程中，旋转操作会频繁发生，浪费资源，影响效率。而需要建立索引的数据库，动辄几十万条数据，非常浪费资源。

红黑树，AVL平衡二叉树的变种，对旋转要求降低，最长子树层数不超过最短子树2倍即可，当然还有其它限制，新插入结点必须是红色，任何结点，不能连续有两个红色等等。这样做，它确实在插入和查询方面做了平衡，但是二叉树这种形式本身就有一个问题。数据达到一定量级，树的深度会太深。影响查询效率。索引需要持久化到硬盘，红黑树深度太深，IO次数增多，也就是内存和磁盘数据交换就会多，IO是性能瓶颈，我们必须保证IO尽可能少，而且取数据次数要少（磁盘预读尽可能少发生）

B树，性能方面，搜索可能在非叶子结点结束，性能逼近二分查找。每个结点最多m个子树，根结点至少2个子树，分支结点至少拥有m/2课子树（除根和叶子结点都是分支结点）。所有叶子结点都在同一层，每个节点最多有m-1个key，升序排列

缺点：每个结点占用一个磁盘块，一个结点除了key，记录子树的指针，还有data数据，每页存储空间有限，如果data较大，每个节点存储key数量将变少
存储数据量很大时，会导致深度较大，增大磁盘IO，影响查询性能
假设查找关键字28，先根据根结点找磁盘块1，读入内存，然后比较28，发现在16-34之间，根据p2指针找到磁盘块3读入内存，继续比较，根据p2找磁盘块8读入内存。共IO3次。

B+树，只在叶子结点存储key+数据data的B树，非叶子结点只存储key，而不是在每一个结点都存储data。这样做，每个结点可以包含更多结点的指针key，降低树的高度，范围也变成多个区间，区间越多，数据检索越快。并且B+树叶子结点，两两指针相互连接（为了符合磁盘预读特性），顺序查询性能更高

为什么层级变少了，假设每个数据行（data）1KB，key为100bit，而一个结点，按一个磁盘块算4KB
那么3层的B树，一个结点需要存储data，一共可以存4个，4 * 4 * 4 = 64条数据
而3层的B+树，一个结点不需要data，只存key，一个key100bit，一共存400个，400 * 400 * 400 = 640000条索引，最后，通过索引再去检索数据即可（叶子结点相互连接，顺序查找，很快）
上面只是个例子，粗糙的表达一下意思。

2.3 聚簇索引和辅助索引（二级索引）

InnoDB引擎使用的索引。这两种索引其实是对B+树索引的存储形式的一种描述，并不是新的索引，我们知道B+树是叶子结点存储数据，非叶子结点只存储索引。而很多情况下，叶子结点也有不存储数据的情况，比如MySIAM引擎因为使用非聚簇索引。索引和数据文件分开存放，叶子结点存储的是地址。InnoDB引擎中，如果是非主键字段建立索引，叶子结点就存储主键索引。

请区分：聚簇索引和二级索引是InnoDB使用的，非聚簇索引是MyISAM使用的。二级索引和非聚簇索引不是一个东西。
聚簇索引：就是索引和数据放一起，非聚簇索引：就是索引和数据文件分开存放
二级索引：建立在聚簇索引之上的辅助索引，叶子结点存放的数据是聚簇索引的key。还有索引的字段值（比如对username做索引，会将username字段值和对应记录的主键key存储在叶子结点。）
主键索引：主键索引是InnoDB自动建立，采用的是聚簇索引的结构，所以说主键索引就是聚簇索引，但是不可以说聚簇索引就是主键索引，因为非空的唯一键也可以建立聚簇索引。就算表没有主键也没有唯一键，它也会生成6为row_id作为key生成聚簇索引。

以下都是针对InnoDB的概念，MyISAM是使用非聚簇索引

聚簇索引：一种数据存储方式，按照主键顺序构建B+Tree结构，B+Tree的叶子结点就是行记录（数据）（就是叶子结点存主键索引值和数据，非叶子结点，只存放主键索引值），行记录和主键值紧凑地存储在一起。意味着InnoDB的主键索引就是数据表本身，它按主键顺序存放了整张表的数据，占用空间就是整张表数据量的大小。通常说主键索引就是聚簇索引
辅助索引：建立在聚簇索引之上的索引。根据索引列构建B+Tree结构，B+Tree的叶子结点只存索引列和主键信息。二级索引占用空间会比聚簇索引小，通常创建辅助索引是为了提高查询效率，一个InnoDB表，只能创建一个聚簇索引，可以创建多个辅助索引。
InnoDB表要求必须有聚簇索引，InnoDB聚簇索引只有一个，表定义主键会自动生成主键索引（聚簇索引）。那表没有主键怎么建立聚簇索引呢？如果没有主键，会选择唯一键（必须是非空的unique，而且是选择第一个）。没有唯一键，生成一个6位row_id作为聚簇索引的key，或者说主键。
可以理解为，二级索引，是建立在聚簇索引之上的，它叶子结点，就是存储聚簇索引的主键，也就是说使用二级索引，需要先查询到主键，然后再根据查询到的主键，去聚簇索引中查数据。
所以二级索引比聚簇索引要慢是肯定的，但是对于非主键或唯一字段的查询提升是肯定的。一般对经常查询，但是又不是主键的字段，才会建立二级索引。

2.4 非聚簇索引

一定不要将非聚簇索引和二级索引混淆。聚簇索引是索引和记录存放在一起，也就是叶子结点即存放索引，也存放数据。而非聚簇索引是索引和记录分开存放。而二级索引是建立在聚簇索引之上的一个辅助索引。叶子结点存储的是聚簇索引的key。

非聚簇索引是MySIAM使用的，索引和数据分两个文件存放，而InnoDB是使用聚簇索引，索引和数据放一起（.ibd文件）

非聚簇索引：索引文件只存放索引，主键索引就存放主键key，辅助键索引就存放辅助键的key。叶子结点存放的不是记录，是数据的内存地址。
无论是主键索引还是辅助键索引，最后都会通过叶子结点存放的内存地址，去分开存放的数据文件中获取数据。

3. EXPLAIN查询分析

Explain select * from user where id < 3;

MySQL提供了一个EXPLAIN命令，可以对SELECT语句进行分析，输出SELECT执行的详细信息，供开发人员有针对性的优化。相当于是一个执行计划。

id：执行的一个序号，一个标识，主键
select_type:SIMPLE：查询的类型，下面介绍常用的，如果以后遇见没见过的，官方文档查就可以了。

SIMPLE：代表简单的查询，不包含子查询或union联合查询这种复杂元素
PRIMARY：表示此查询，是最外层的查询，不包含在嵌套查询中
UNION：表示此查询，不是最外层的，比如UNION联合查询的第二个或后续的查询（UNION操作符的作用是合并两个或多个 SELECT 语句的结果集）。
DEPENDENT UNION：表示依赖联合，就是UNION的第二层或后续的查询，依赖于（使用到了）外层的参数值等。
UNION RESULT：UNION的结果，联合查询的结果
SUBQUERY：子查询语句，子查询里面的sql的类型
DEPENDENT SUBQUERY：表示依赖子查询，就是子查询，依赖于外层的参数值等。
下图是使用UNION联合查询的各查询类型，外层是PRIMARY，第二个是UNION类型表示不是最外层的，最后会有一个临时的，类型是UNION RESULT，负责UNION的结果。

子查询的类型

子查询使用外层查询的类型

table:user：查询的表是哪个
partitions:NULL：分区
type:range：连接类型，存储引擎查询数据时采用的方式，可以判断出查询时，是全表扫描，还是基于索引的部分扫描，这些类型，查询效率由低到高，ALL查询效率最低，NULL查询效率最高。下面是常见的，以后遇到没见过的，去查官方文档就好了。

ALL：表示全表扫描，性能最差

index：基于索引的全表扫描，先扫描索引，再扫描全表数据。（先通过索引获取顺序，然后顺序检索，比直接全表扫描快得多）。但是条件字段必须有索引

range：使用索引进行范围查找，使用>、>=、<、<=、in等等，条件字段必须有索引，没索引还是ALL

ref：使用非唯一索引进行单值查询（普通索引）。

eq_ref：多数出现在多表join查询，前面表的每一个记录，只能匹配后面表的一行结果

const：常量查询，表示使用主键或唯一索引进行等值查询。内部会进行优化，优化成常量。

NULL：表示不用访问表，直接出结果。速度最快。

possible_keys:PRIMARY：此处查询可能使用的索引（因为这个表只有主键索引，所以只显示了PRIMARY），并不一定会使用，只是列出来，可能会使用它们，显示的是索引名。
key:PRIMARY：本次查询使用到的索引（实际用到的索引）
key_len:4：使用索引时，用到了索引中多少个字节(涉及到的一些算法和公式，后面的章节会讲)。可以判断使用复合索引时，是全部使用了索引，还是只用了索引的最左部分的部分字段值。key_len计算规则如下(下面列出各类型，1个元素的大小，比如TINYINT类型1个字节，key_len:4就代表用了4个TINYINT索引)：

字符串类型计算公式如下

字符串长度和字符集有关：latin1=1、gbk=2、utf-8=3、utf8mb4=4
char(n):n字符集长度。n表示char类型n这个字符你规定的长度。比如char(20)，utf-8字符集，那么一个字符就占203=60字节
varchar(n):n*字符集长度+2字节

数值类型计算公式

TINYINT：1个字节
SMALLINT：2个字节
MEDIUMINT：3个字节
INT、FLOAT：4个字节
BIGINT、DOUBLE：8个字节

时间类型

DATE：3个字节
TIMESTAMP：4个字节
DATETIME：8个字节

字段属性

NULL属性占用1个字节，如果一个字段设置了NOT NULL，则没有此项。

看个例子

ref:NULL：引用，有哪些常量、常数、字段与key一起被使用
rows:2：本次查询扫描了多少行，一定要保证这个值尽可能小。MySQL查询优化器会根据统计信息，估算SQL查询到结果要扫描多少行数据。
filtered:100.00：过滤，此处查询条件过滤的百分比
Extra:Using where：额外信息，会追加各种关键词，代表一些特定含义，这个遇到了去官方文档查就可以了，后面也会详细介绍一部分，以帮助我们调优。

Using where：表示查询需要通过回表，以获取其它数据。回表就是当前查到的东西不能满足，还需要去其它索引查，比如二级索引，查到主键，此时如果只需要主键，就不需要回表了，但是如果还需要其它字段，就得去主键索引获取了。
Using index：表示查询需要索引，但是不需要回表，当前索引包含的数据就可以满足，比如主键索引，或者通过二级索引，但只需要主键，例如查询用户名为张三的主键id值。
Using filesort：表示查询出的结果，需要额外的排序。数据量小可以在内存做，但是数据量大就得磁盘里做了。所以，如果调优时发现这个，建议赶紧优化。
Using temprorary：查询中使用到了临时表空间。一般出现在去重，分组等操作。效率也不高，建议能优化就优化。最好分组去重这种事，交给后端程序员，比如JAVA去处理。因为它们可以通过一写算法，快速的在内存中去重。
例子

4. 索引优化

4.1 回表查询和索引覆盖

InnoDB索引有聚簇索引和二级索引，聚簇索引必须有且只有一个，叶子结点存储行记录。二级索引可以有多个，建立在聚簇索引之上，叶子结点存储主键值（聚簇索引的key，默认是主键，没有主键用非空唯一键，还没有就生成6为row_id作为主键）和索引字段值。

通过二级索引，无法直接定位行记录，只能获取二级索引存储的聚簇索引key（主键值）和二级索引，索引的字段值。
所以此时如果我们通过二级索引，想要获取到行记录（需要主键和二级索引字段的其它信息），就需要通过二级索引叶子结点的主键key，去聚簇索引定位行记录。这就是回表查询。也就是一共扫描两遍索引（二级索引+聚簇索引）。
那么如果不需要回表，我们要查的就是聚簇索引的key，或者二级索引字段，就叫索引覆盖。
Mysql官方文档没有索引覆盖这个概念的详细说明，但是SQL-Server有，但是Mysql在explain查询优化章节（explain输出结果Extra字段为Using index时，可以触发索引覆盖），有和SQL-Server类似的描述。它们都表达了：“只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快”，这就叫索引覆盖。
实现索引覆盖的最常见方法是，将被查询字段，建立到组合索引。（如果直接通过聚簇索引就可以查询，可不要再建立组合索引。）
也就是说，优化时，尽量避免回表，而保证索引覆盖。实际上就是，尽量保证用Explain生成的执行计划，Extra字段的值为Using index，使用索引而不进行回表

很多面试者，在谈论优化时，总是说不要使用select * ，为什么？

主要就是回表问题，只要回表就是一次IO，select a,b,c 和select * 可不是一个量级
比如select a,b,c我们有(a,b,c)复合索引，那么我们可以where a = xxx and b = xxx这样只IO两次，甚至一次，最次是3次。
但是select * 可能会IO4次，甚至直接IO一次走全表扫描。因为包含很多其它字段，只要是索引叶子结点没有的，就需要回表。回表就得IO。
因此，不使用select * 大部分原因是为了可以索引覆盖

4.2 最左前缀（匹配）原则

复合索引建立时，会依次从左到右生成B+树，比如建立复合索引(a,b,c)。它会以a为入口，a的叶子结点为b的key和自己索引字段，b的叶子结点为c的key和自己的索引字段。

你可以用(a),(a,b),(a,b,c)来使用到这个复合索引，但是不能以(b),(b,c),( c)这样的来使用，因为它会找不到入口，因为入口是a
使用复合索引时，一定要从最左开始，并且连续，比如(a,b),(a,b,c),(a)，不可以是(a,c)这样，略过b的，因为b算是c的入口。使用(a,c)来匹配，则只会生效(a)而不生效( c)。会触发全表扫描（走了a的索引，还走了全表扫描，还不如直接全表扫描）。

如何优化复合索引？

尽量减少回表使用索引覆盖，比如我们要查询(a,b,c)，那么select a,b,c from table where a = 1 and b = 1;会减少一次回表（IO）。先走a，然后走b，b会保存c，所以无需再走c。但是如果条件必须要c，无法避免最后一次回表，也不要强求。
尽量避免使用模糊查询like；如果让like走索引，查询字符不能以通配符%开始，如果需要让like %abc走索引，需要使用reverse()函数来创建函数索引(不推荐)

select a,b,c from tablename where reverse(f) like reverse('%abc');

4.3 LIKE查询

MySQL使用like模糊查询时，索引是可以起作用的，但是有条件。

首先，我们建立索引，通过索引字段作为key生成B+树，此时如果我们模糊匹配，例如%abc或%abc%，这时候查索引，根本不知道哪个索引中间有abc（没有明确起点值），肯定需要全表扫描，也就没必要走索引。

但是如果是abc%，就会走索引，因为找abc开头的，是完全可以走索引而减少查询时间的。而且Mysql 5.7还有了新特性，会使用索引时，充分的进行过滤，下沉到存储引擎去执行。对应的Extra为Using index condition,表示进行了新机制的优化。

5.7版本后，这个选项可以选择开启或关闭，通过命令show variables like '%optimizer_switch%'查看,对应参数为index_condition_pushdown=on;

4.4 NULL查询

如果Mysql表的某一列含有Null值，那么包含该列的索引，5.5之后版本是可以生效的。老版本会让索引失效。

Null是特殊的，处理方式和其他值不同，比如不可以使用=,<,>这样的符号运算，对Null的算数运算结果都是Null，使用count函数时，不会包括Null行，Null比空字符串需要更多存储空间等等。
Null列需要增加额外空间记录其值是否为Null。另外MyISAM表中，每一个空列额外占用一位，四舍五入到最接近的字符。
虽然MySQL可以在含有NULL列上使用索引，但是Null和其它数据有区别，不建议设置，最好设置为Not null，并给一个默认值（0，空字符串等），如果是datetime类型，可以设置为当前系统时间或某个固定值。

4.5 索引和排序

MySQL支持filesort和index两种排序方式，filesort是先把结果查询出来，然后在缓存或磁盘进行排序，效率低。index是利用索引自动实现排序，不需要额外做排序操作，效率较高。

常见的使用index方式排序的情况,推荐。（通过explain分析得出的结论，Extra字段值为Using index。大家最好写sql时，都用explain来观察下执行方案。毕竟情况千千万，我下面给出的结论很可能不够用。）

# 假设只有(id),(id,name)两个索引
# Order By子句索引列组合满足索引最左匹配原则
explain select id from user order by id;//对应(id)、(id,name)等等这样的索引有效
# where + order by子句索引列组合满足索引最左匹配原则
explain select id from user where id = 3 order by name;//对应(id,name)索引

使用filesort方式排序的情况（Extra属性值为Using filesort），不推荐，可以考虑优化成index。

# 假设只有(id),(id,name)两个索引
# 对索引列同时使用asc和desc
explain select id from user order by id asc,name desc;//对应(id,name)索引，但是会使用filesort方式
# where + order by满足最左匹配，但where子句使用范围查询（<,>,in等）
explain select id from user where id > 3 order by name;//对应(id,name)索引，但使用范围查询，会使用filesort方式
# order by 或 where + order by索引列没有满足最左匹配
explain select id from user order by name;//不匹配(id)或(id,name).只能走filesort
# 使用不同的索引，假设我们有(name)和(age)两个索引。Mysql每次只能采用一个索引，例如下面order by涉及两个索引，会走filesort
explain select id from user order by name,age;
# 同理，where 和 order by 组合使用了不同索引，也会filesort
explain select id from user where name = 'tom' order by age;
# where 或者 order by使用表达式(包括函数表达式) 会 filesort
explain select id from user order by abs(age);

filesort的两种排序算法

双路排序（旧）：需要两次磁盘扫描读取，最终得到用户数据，第一次将排序字段取出，然后排序；第二次去读取其它字段数据。
单路排序（新）：从磁盘查询所有数据，然后在内存或缓存中排序将结果返回。如果出现内存空间不够的情况，会分批次的加载到内存，反而增大IO次数。
所以单路排序效率较高，但是如果内存不够，反而比双路排序慢，所以避免单路排序缓存空间不够，就是可以优化的点。
可以通过少使用select * ，或者增加sort_buffer_size、max_length_for_sort_data容量，让缓冲区不那么容易溢出。

4.6 索引失效情况总结

结合前面的讲解，我们可以总结出索引失效的7种情况。

记住七字口诀：模型，数空运，最快（意思是，运输"模型"，就"数"，“空运"的"最快”）

模（模糊）：模糊查询LIKE以%开头
型（类型）：数据类型错误
数（函数）：对索引字段使用内部函数
空（Null）：索引列是NULL，索引列不存储空值，如果索引列不设置not null，数据库会认为索引列存在null值，因此不会使用索引。最新版的数据库引擎，null这种情况，不会失效了
运（运算）：对索引列进行加减乘除等运算
最（最左）：复合索引不按索引列最左开始查找
快（更快）：全表查找预计比索引更快

5. 查询优化

5.1 慢查询定位

当然是通过慢查询日志喽，另外，慢查询日志，不是只记录查询SQL，insert之类的，只要执行够慢，也会记录的。只不过insert这种很少需要优化。

开启慢查询日志

# 查看MySQL数据库是否开启了慢查询日志和慢查询日志文件的存储位置
show variables like 'slow_query_log%'
# 设置慢查询相关参数
set global slow_query_log = ON;# 开启慢查询日志
set global slow_query_log_file = 'OAK-slow.log'; # 设置慢查询日志文件名
set global log_queries_not_using_indexes = ON; #开启记录没有使用索引的查询SQL功能。 
set long_query_time = 10; # 设置慢查询的时间阈值，这里是10s，表示记录执行时间超过10s的查询sql将被认为是慢查询，记录在日志。

测试（set long_query_time = 0.1;）我将阈值设置为了0.1秒

参数讲解

Time：日志的记录时间
User@Host：执行Sql的用户和主机
Query_time：执行时间，用了多长时间
Lock_time：锁表时间，锁表，其它读线程需要阻塞
Rows_sent：发送给请求方的记录数，结果数量
Rows_examined：语句扫描的记录条数
SET timestamp：语句执行的时间点（Time是日志记录时间，这个是Sql执行的开始时间）
select …：执行的SQL语句。

使用Perl语言开发的工具mysqldumpslow分析慢查询日志

MySQL提供的慢查询日志分析工具mysqldumpslow，可以通过工具分析慢查询日志内容，需要安装Perl语言环境才可以使用

在Mysql bin目录下执行命令，可以查看帮助信息。

perl mysqldumpslow.pl --help

查看慢查询日志信息

# 显示"C:\ProgramData\MySQL\Data\OAK-slow.log"日志文件，前5条记录
perl mysqldumpslow.pl -t 5 -s at "C:\ProgramData\MySQL\Data\OAK-slow.log"

还有第三方分析工具，例如pt-query-digest、mysqlsla等等。

5.2 慢查询优化

判断是否需要优化

不能瞎优化，需要优化再优化

如果一条SQL记录到了慢查询日志，说明它运行时间超过了你设定的阈值，可能需要优化。
通过explain命令，查看SQL是否生成理想的执行计划，比如where id > 0 这样的sql，是进行全表扫描的，可不走索引。而where id = 2这样的才能利用索引，有效减少扫描行数。

提高索引过滤性

索引过滤性和索引字段、表的数据量、表设计结构都有很大关系。我们通过一个案例来了解这个过程。

表结构（因为懒，只插入了5000多数据演示，大家插20万可以看到明显的效率提升）

# 表：
create table student(
	int id primary key auto_increment,
	varchar(20) name not null,
	char(1) sex not null,
	int age not null
)
# 造数据：还是造20万左右即可，我这里为了方便，只造了5000多
insert into student(name,sex,age) select name,sex,age from student;
# SQL案例：会进行全表扫描
explain select * from student where age = 18 and name like 'zhang%';

尝试进行第一次优化，先建立索引，因为索引最好不要太多，我们最好比较,抉择一下，建立(age)和(age,name)的优劣。

只建立(age)，扫描3072行，减少2000行的扫描量，Extra为Using where，也就是进行了回表操作。type为ref，也就是用索引等值查询，比较快。可以针对优化Extra，让回表变为索引覆盖。

建立复合索引，避免回表。扫描2048行。此时我们发现，type由ref变为range，range是使用索引进行范围查找，可以优化。

进一步优化，建立虚拟列（注意，5.7及以上版本才有的新特性）。因为我们进行了name like 'zhang%'的模糊查询，最好对%前面的字符，进行虚拟列的建立，可以加快我们的检索

为student添加虚拟列，这个列是name字段的前5个字符，不占用表空间。

alter table 
	student # 为student建立
add # 添加一个虚拟列，varchar(5) first_name
	first_name varchar(5) 
	generated always as (left(name,5)), # 总是从左到右的取name字段的前5个字符，比如zhang
add # 顺便添加一个索引
	index(first_name,age);

可见使用的是first_name这个虚拟字段的索引，扫描行数依然是2048，Extra没有了。type由range变为的ref，从索引范围查询，变成索引单值查询（等值查询）。

5.3 分页查询优化

一般的分页查询使用limit子句就可以实现

select * from tablename limit [offset,] rows;
# offset 可省，表示第一个需要返回的记录行的偏移量，从0开始算。不指定就默认从0开始
# rows 返回记录行的最大数目。也就是一页多少行。
select * from tablename limit 10,15; # 表示从下标为10的记录开始，返回15条数据。

测试执行时间，最好使用些工具参数，比如profiling，我们可以启动它

如果偏移量固定，返回记录的量对执行时间有什么影响？（下面我给出40960条数据的执行时间，100条以下差距不大，1000以上，会随着数据量增大，而耗时增大）

select * from tablename limit 10000,1;//0.005 sec
select * from tablename limit 10000,10;//0.005 sec
select * from tablename limit 10000,100;//0.005 sec
select * from tablename limit 10000,1000;//0.007 sec
select * from tablename limit 10000,10000;//0.02 sec

如果每页记录数固定，偏移量变化，对执行时间有什么影响？（可见偏移量越大，查询时间越长）

select * from tablename limit 1,100;
select * from tablename limit 10,100;
select * from tablename limit 100,100;
select * from tablename limit 1000,100;
select * from tablename limit 10000,100;

因为分页查询机制，每次都是从结果的第一行开始扫描，所以偏移量越大，或者数据量越多，都会让执行时间越长。但是整体上，偏移量大，比返回数据量大，执行时间更少。

优化

利用索引覆盖

# 将select * 变为select id，就可以不回表
select id from user limit 10000,100;

利用子查询。再没法用索引覆盖的话，比如只有聚簇索引，但不光光查询主键，就可以使用子查询

# 将 limit 10000,100;变成 limit 10000,1 和 limit 100;
select * from user where
	id >= (
		select id from user
			limit 10000,1 # 偏移量大，执行时间，没有数据量大花时间
	)
	limit 100;

殷丿grd_志鹏

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySQL 数据存储和优化------MySQL索引原理和优化 ---- （架构---索引---事务---锁---集群---性能---分库分表---实战---运维）持续更新

Mysql架构体系全系列文章主目录(进不去说明还没写完）https://blog.csdn.net/grd_java/article/details/123033016本文只是整个系列笔记的第二章：MySQL索引原理和优化，只解释索引相关概念。文章目录1. 索引类型和索引使用细节2. 索引原理2.1 使用的数据结构和算法2.2 B+树，为什么用B+树2.3 聚簇索引和辅助索引（二级索引）2.4 非聚簇索引3. EXPLAIN查询分析4. 索引优化4.1 回表查询和索引覆盖4.2 最左前缀...
复制链接

扫一扫