[大数据]MySQL优化总结 一

测试的数据库配置

MySQL8.0 中文参考手册：MySQL8.0中文版参考手册-MySQL文档类资源-CSDN下载

测试数据表：

CREATE TABLE `user` (
  `id` int NOT NULL,
  `name` varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `sex` varchar(2) NOT NULL,
  `age` tinyint NOT NULL,
  `email` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `phone` varchar(13) NOT NULL,
  `address` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

加入500w测试数据，以下数据，均随机生成，如有雷同纯属巧合，生成工具类在这里面（http://t.csdn.cn/WNnNz）

优化成本：硬件>系统配置>数据库表结构>SQL及索引

优化效果：硬件<系统配置<数据库表结构<SQL及索引

SQL优化策略适用于数据量较大的场景下，如果数据较小没必要画蛇添足。一般以整型值为主的表在千万级以下，字符串为主的表在五百万以下是没有太大问题的。

优化遵守五个原则

1. 减少数据访问：设置合理的字段类型，启用压缩，通过索引访问等减少磁盘IO
2. 返回更少的数据：只返回需要的字段和数据分页处理 减少磁盘IO及网络IO
3. 减少交互次数：批量DML操作，函数存储等减少数据连接次数
4. 减少服务器CPU开销：尽量减少数据排序操作及其全表查询，减少CPU 内存占用
5. 利用更多资源：使用表分区，可以增加并行操作，更大限度利用CPU资源

总结到SQL优化中的三点：1.最大化利用索引；2.尽可能避免全表扫描；3.减少无效数据的查询；

SQL执行顺序

理解SQL优化原理，首先搞清楚SQL执行顺序：（从上往下看）

查询的过程

1. 客户端向 MySQL 服务器发送一条查询请求
2. 服务器首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段
3. 服务器进行 SQL 解析、预处理、再由优化器生成对应的执行计划
4. MySQL 根据执行计划，调用存储引擎的 API 来执行查询
5. 将结果返回给客户端，同时缓存查询结果
6. 注意：只有在8.0之前才有查询缓存，8.0之后查询缓存被去掉了

7. 

EXPLAIN 分析SQL的执行计划

explain的字段：id、select_type、table、type、possible_keys、key、key_len、ref、rows、fitered、extra ；其中最重要的字段为：id、type、key、rows、Extra

启动执行计划：

explain?select?投影列?FROM?表名?WHERE?条件

每个字段介绍：

ID：查询执行的顺序：ID值相同时表示从上向下执行，并且被视为一组；如果是子查询，ID值会递增，ID值越高，优先级越高；ID为NULL 为最后执行

select_type：

1.simple：表示查询中不包含子查询或者 union

2.primary：当查询中包含任何复杂的子部分，最外层的查询被标记成 primary

3.derived：在 from 的列表中包含的子查询被标记成 derived

4.subquery：在 select 或 where 列表中包含了子查询，则子查询被标记成 subquery

• • • 举例：1.set session optimizer_switch='derived_merge=off'; #关闭mysql5.7新特性对衍生表的合并优化
    • 2.explain select (select 1 from user where id = 1) from (select * from user where id = 1) user

• • • set session optimizer_switch='derived_merge=on'; #还原默认配置

5.union：两个 select 查询时前一个标记为 PRIMARY，后一个标记为 UNION。union 出现在 from 从句子查询中，外层 select 标记为 PIRMARY，union 中第一个查询为 DERIVED，第二个子查询标记为 UNION

explain select 1 union all select 1;

6.unionresult：从 union 表获取结果的 select 被标记成 union result 。

?table：显示这一行的数据是关于哪张表的。当 from 子句中有子查询时，table列是格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。当有 union 时，UNION RESULT 的 table 列的值为，1和2表示参与 union 的 select 行id。

?type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为 system > const > eq_reg > ref > range > index > ALL。一般来说，得保证查询达到range级别，最好达到ref

? ? ? ? 1.NULL：MySQL能在优化阶段分解查询语句，在执行阶段用不着访问表或者索引。例如：在索引列中取最小值，可以单独查找所有来完成，不需要执行时访问表 explain select min(id) from user;

? ? ? ? 2.system：表中只有一行数据，属于const的特例。如果物理表中就一行数据为ALL
? ? ? ? 3.const：查询结果最多有一个匹配行。因为只有一行，所有可以被视为常量。const查询速度非常快，因为只读一次。一半情况下把主键或者唯一索引作为唯一条件查询都是const ? ?explain select * from (select * from user where id = 1) tmp;

? ? ? ? ?4.eq_ref：查询时查询外键表全部数据、且只能查询主键或关联列。且外键表中外键列数据不能有重复数据，且这些数据都必须咋主键表中有对应数据(主键表中可以没有用到的)
? ? ? ? 5.ref：比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。

? ? ? ? ? ? ? ?○?简单 select 查询，name是普通索引（非唯一索引） explain select * from user wherename = '公全盛';

? ? ? ? ? ? ? ??○ 关联表查询，explain select * from student left join sc on sc.s_id = student.s_id

? ? ? ? ?6.range：把这个列当作条件只检索其中一个范围。常见 where 从句中出现 between、<、>、>=、in 等。主要应用在具有索引的列中explain select * from user where id > 1;

?????????7.index：扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，所以这种通常比ALL快一些 explain select * from user;
????????8.ALL：即全表扫描，扫描你的聚簇索引的所有叶子节点。通常情况下这需要增加索引来进行优化了。 ?explain select * from user;

?possible_keys

• 查询条件字段涉及到的索引，可能没有使用。
• explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。
• 如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能，然后用 explain 查看效果

key：实际使用的索引。如果为 NULL，则没有使用索引。如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 forceindex、ignore index。?

key_len：表示索引中使用的字节数，查询中使用的索引的长度（最大可能长度），并非实际使用长度，理论上长度越短越好。key_len 是根据表定义计算而得的，不是通过表内检索出的。explain select * from user WHERE id=3;?

?

ref：显示索引的哪一列被使用了，如果可能的话，是一个常量 const。?

rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。注意这个不是结果集里的行数。?

?fitered：显示了通过条件过滤出的行数的百分比估计值。

Extra：MYSQL 如何解析查询的额外信息。

? ? ? ? 1. Distinct：MySQL 发现第 1 个匹配行后,停止为当前的行组合搜索更多的行。

? ? ? ? 2. Not exists：MySQL 能够对查询进行 LEFT JOIN 优化,发现 1 个匹配 LEFT JOIN 标准的行后,不再为前面的的行组合在该表内检查更多的行。

????????3. range checked for each record (index map: #):MySQL 没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。

????????4. Using filesort:MySQL 需要额外的一次传递,以找出如何按排序顺序检索行。将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。

? ? ? ? ? ? ? ? 4.1 actor.name未创建索引，会浏览user整个表，保存排序关键字name和对应的id，然后排序name并检索行记录 explain select * from user order by name;

当查询语句中使用 order by 进行排序时，如果没有使用索引进行排序，会出现 filesort 文件内排序，这种情况在数据量大或者并发高的时候，会有性能问题，需要优化。filesort会出现的场景看下面的查询优化

?

? ? ? ? ? ? ?4.2??film.name建立了idx_name索引,此时查询时extra是using index

??????5. Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。(使用覆盖索引)，表示直接访问索引就足够获取到所需要的数据，不需要通过索引回表，通常是通过将待查询字段建立联合索引实现。

? ? ? ? 5.1?覆盖索引定义：mysql执行计划explain结果里的key有使用索引，如果select后面查询的字段都可以从这个索引的树中获取，这种情况一般可以说是用到了覆盖索引，extra里一般都有using index；覆盖索引一般针对的是辅助索引，整个查询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值 explain select id from user where id= 1;

?

?

?

? ? ? ? ?6. Using temporary:为了解决查询,MySQL 需要创建一个临时表来容纳结果。

? ? ? ? ? ? ? ? 6.1 name没有索引，此时创建了张临时表来distinct explain select distinct name from user;

????????????????6.2?name建立了idx_name索引，此时查询时extra是using index,没有用临时表

?

????????7. Using where: WHERE 子句用于限制哪一个行匹配下一个表或发送到客户，并且查询的列未被索引覆盖 ，表示优化器需要通过索引回表查询数据。 explain select * from user where name = 'a';

?

????????8. Using sort_union(…), Using union(…), Using intersect(…): 这 些 函 数 说 明 如 何 为index_merge 联接类型合并索引扫描。

????????9. Using index for group-by:类似于访问表的 Using index 方式,Using index for group-by 表示MySQL发现了一个索引,可以用来查 询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。

索引类型

创建索引语法：

CREATE TABLE table_name[col_name data type]
[unique|fulltext][index|key][index_name](col_name[length])[asc|desc]

字段解释：

1.unique|fulltext为可选参数，分别表示唯一索引、全文索引

2.index和key为同义词，两者作用相同，用来指定创建索引

3.col_name为需要创建索引的字段列，该列必须从数据表中该定义的多个列中选择

4.index_name指定索引的名称，为可选参数，如果不指定，默认col_name为索引值

5.length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度

6.asc或desc指定升序或降序的索引值存储

普通索引 normal

????????normal 是最基本的索引，它没有任何限制，唯一任务是加快对数据的访问速度，最经常出现在查询条件（WHERE column=）或者排序条件（ORDERBY column）中的数据列创建索引

#直接创建索引
CREATE INDEX index_name ON table(column(length))

#修改表结构的方式添加索引
ALTER TABLE table_name ADD INDEX index_name ON (column(length))

#创建表的时候同时创建索引
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    INDEX index_name (title(length))
)

#删除索引
DROP INDEX index_name ON table

唯一索引 unique

????????保证数据记录的唯一性,事实上，在许多场合，很多人创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复

与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

优点：

• 保证数据库表中每一行数据的唯一性。
• 大大提高检索的数据，以及减少表的检索行数。
• 在表连接的连接条件，可以加速表与表直接的相连。
• 在分组和排序字句进行数据检索，可以减少查询时间中分组和排序时所消耗的时间（数据库的记录会重新排序）。

#创建唯一索引
CREATE UNIQUE INDEX indexName ON table(column(length))

#修改表结构
ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))

#创建表的时候直接指定
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    UNIQUE indexName (title(length))
);

主键索引?

????????一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引，主键索引与唯一索引的唯一区别是：前者在定义时使用的关键字是PRIMARY而不是UNIQUE

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `name` char(255) NOT NULL ,
    PRIMARY KEY (`id`)
);

组合索引?

????????指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合

ALTER TABLE `table` ADD INDEX name_city_age (name,city,age); 

全文索引 fulltext

????????主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，先将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。

#创建表的适合添加全文索引
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    FULLTEXT (content)
);

#修改表结构添加全文索引
ALTER TABLE article ADD FULLTEXT index_content(content)

#直接创建索引
CREATE FULLTEXT INDEX index_content ON article(content)

空间索引 spatial

• 表示空间数值的数据类型
• 操作空间数值的函数
• 空间索引，用于提供访问空间列的速度
• mysql的空间索引的数据结构是R树，R树实际上就是多维的B树

缺点

1.虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert、update和delete。因为更新表时，不仅要保存数据，还要保存一下索引文件。

2.建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会增长很快。

索引只是提高效率的一个因素，如果有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

注意事项

使用索引时，有以下一些技巧和注意事项：

索引不会包含有null值的列

只要列中包含有null值都将不会被包含在索引中，复合索引中只要有一列含有null值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为null。

使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个char(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

索引列排序

查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

不要在列上进行运算

这将导致索引失效而进行全表扫描，例如

SELECT * FROM table_name WHERE YEAR(column_name)<2022;

where? 及order by 建立索引

读表时候，尽量的避免全表扫描，在where及order by涉及的列建立索引。

操作符

or、union all、union

在 MySQL 5.0 之前的版本要尽量避免使用 or 查询，可以使用 union 或者子查询来替代，因为早期的 MySQL 版本使用 or 查询可能会导致索引失效，在 MySQL 5.0 之后的版本中引入了索引合并，索引合并简单来说就是把多条件查询，比如or或and查询对多个索引分别进行条件扫描，然后将它们各自的结果进行合并，因此就不会导致索引失效的问题了如果从Explain执行计划的type列的值是index_merge可以看出MySQL使用索引合并的方式来执行对表的查询

未加索引?

OR ：

SELECT * FROM `user` WHERE age =10 or age=30 #耗时：2.465S

union all：

SELECT * FROM `user` WHERE age=30

union all

SELECT * FROM `user` WHERE age =10 #耗时：4.175s

普通索引?

OR：

SELECT * FROM `user` WHERE age =10 or age=30 #耗时：1.292s

union all：

SELECT * FROM `user` WHERE age=30

union all

SELECT * FROM `user` WHERE age =10 #耗时：1.184s

union：

SELECT * FROM user WHERE age=30

union

SELECT * FROM user WHERE age =10 #1.944S

?

?总结：为什么使用 union all 因为 union 操作会对结果去重且排序，所以从速度来说， union all会更胜一筹。

1. union去重并排序，union all直接返回合并的结果，不去重也不排序；
2. union all比union性能好；

对于OR不走索引的情况分析：

1. userId(索引) OR age(没有索引)：假设走了userId的索引，但是走到age查询条件时，还得全表扫描，也就是三步：索引扫描+全表扫描+合并

2.如果一开始就走全表扫描，直接一遍就可以了

测试结果：索引 or 没有索引 的条件 结果 type=ALL ； 两个不同索引：type=index_merge;?

LIKE

注：当表数据较少(几千条)，直接like '%xxx%'；不要画蛇添足

like有三种情况：

1、AAA从字符串开始的某个固定位置出现，可以创建函数索引进行优化

2、AAA从字符串结尾的某个固定位置出现，可以创建函数组合索引进行优化

3、AAA在字符串中位置不固定，可以通过改写SQL进行优化

未加索引

SELECT *  FROM `user` WHERE `address` LIKE  '%福建省%'  #耗时：11.732s
SELECT *  FROM `user` WHERE `address` LIKE  '福建省%'   #耗时：11.807s
SELECT *  FROM `user` WHERE `address` LIKE  '%福建省'  #耗时：11.677s

普通索引

like '%AAA%'：

SELECT * FROM `user` WHERE `address` LIKE '%福建省%' #耗时：7.691s

like 'AAA%'：

SELECT * FROM `user` WHERE `address` LIKE '福建省%' #耗时：4.347s

like '%AAA'：

SELECT * FROM `user` WHERE `address` LIKE '%五街坊' #耗时：6.633s

?结论：只有普通索引 like 'aaa%' 走了索引，但是实际业务场景肯定会有like %aaaa%的情况，再看下面的测试?

?对 like '%AAA%' 普通索引-NORMAL 优化

1.instr():

select * from `user` where instr(address, "福建省") > 0; #耗时：5.259s

2.locate()：

select * from `user` where locate("福建省",address ) > 0; #耗时：5.246s

这两种方案只是比不优化快几秒，但是不排除会比优化器慢，网络IO、服务器配置、数据结构等都可能影响不绝对

3.in()：

select * from `user` where address in (select address from `user` where address like "%福建省%"); #耗时：14.588s

根据索引再回表的代价要看符合条件的记录数多少：如果in子查询返回的记录数很少，那么优化的效果就相当于效率提高了N倍；如果in子查询返回的记录数较多，两种SQL的性能区别就不是很明显

4.占位符：下划线代表一个字符，百分号表示任意字符

没有索引：SELECT * FROM `user` WHERE address LIKE '_%柳州市%' #耗时3.020S

普通索引：SELECT * FROM `user` WHERE address LIKE '_%柳州市%' #耗时3.331S

5.前缀法：

上面的SQL 的SQL总结来看只有： like AAA%" 花费时间比 like %AAA%" 快。所以可以取巧，在不用分词的方法的前提下，把存储address字段，加一个前缀，例如存储 address=柳州市xxxxx，那就可以address='福建省柳州市xxxxx'，福建省只是我现有数据做测试的前缀。实际查询出来去除掉前缀就可以了。

没有索引：SELECT * FROM `user` WHERE address LIKE '福建省%柳州市%' #耗时：2.666s

普通索引: SELECT * FROM `user` WHERE address LIKE '福建省%柳州市%' #耗时：0.656

小总结：

没加索引：

前缀耗时：2.666S

like '%AAA%'耗时：11.732S

Like占位符耗时：3.020S

普通索引：

前缀耗时：0.656S

like '%AAA%'耗时：7.691S

Like占位符耗时：3.331S

instr(address, "AAA") > 0耗时：5.259S

locate("AAA",address ) > 0耗时：5.246S

AND

-- 没有加索引
SELECT * FROM `user` WHERE age=10 AND sex='男' #耗时2.570S
-- 加普通索引
SELECT * FROM `user` WHERE age=10 AND sex='男' #耗时1.933S

?组合索引

ALTER TABLE user ADD INDEX age_sex(age,sex);
SELECT * FROM `user` WHERE age=10 AND sex='男' #耗时：1.377S

in、not in 、 between、exists

not in()：

SELECT * FROM `user` WHERE age NOT IN (1,2,3,4,5) #48.526S

in()：

SELECT a.* FROM user a WHERE age in(1,2,3,4,5) #2.863S

SELECT a.* FROM user a WHERE age in(SELECT 1 FROM userWHERE age in(1,2,3,4,5)) #0.842S

exists:

SELECT a.* FROM user a WHERE a.id IN (SELECT id FROM user where id=a.id and age=5) #0.764S

SELECT a.* from user a where exists(select 1 from user where id=a.id and age=5) #0.791S

between：

SELECT * FROM `user` WHERE age BETWEEN 1 AND 5 #2.766

?从以上结果中得出，只有NOT IN 才会全表扫描，但是网上很多博客说IN、NOT IN 都会导致全表扫描，但是我的测试结果只有NOT IN 才全表，不知道是不是我MySQL8.0的问题，希望有知道的大佬可以指教一下！

如果是子查询，可以用exists代替。详情见《MySql中如何用exists代替in》如下：

外查询表大，子查询表小，选择IN；外查询表小，子查询表大，选择EXISTS；若两表差不多大，则差不多。

?

未完... 还有续篇大家可以继续关注

这是小编在开发学习使用和总结的小Demo， ?这中间或许也存在着不足，希望可以得到大家的理解和建议。如有侵权联系小编！?