5.索引优化实战

苹水相峰

已于 2023-05-25 15:20:22 修改

阅读量215

点赞数 1

分类专栏： Mysql 文章标签：数据库 sql mysql

于 2022-08-08 16:57:42 首次发布

本文链接：https://blog.csdn.net/Mao_yafeng/article/details/126231049

版权

Mysql 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

5.索引优化实战

文章目录

1.分页查询优化详解

select * from test limit 10000,10;

上面的SQL执行过程是先查出来10010条数据，舍弃前10000条数据的。

1）根据自增且连续的主键排序的分页查询优化

select * from test limit 10000,10;
--优化后
select * from test where id>10000 limit 10;

分析：前提是根据主键查询并且主键连续，优化主要就是让SQL走索引，快速查找目标数据。

2）根据非主键字段排序的分页查询

test 表有一个联合索引 name,age，position

select * from test order by name 90000,5;

分析怎么优化，上面的SQL的执行计划结果可以看到SQL没有走索引，并且Extra结果是Using filesort

扫描整个索引并查找到没有索引的列（可能要遍历多个索引树，先去二级索引查找，在去主键索引回表查出所有列的值）的成本比扫描全表的成本更高，所以优化器放弃是用索引。

优化方案：让排序时返回的字段尽可能少，所以可以让排序和分页的操作先查出来主键，然后根据主键查到对应的记录，SQL改写如下：

select e.* from test e inner join (select id from test order by name limit 90000,5) ed on e.id=ed.id;

优化后SQL走索引，并且使用的是索引排序。

2.表join关联原理详解及优化

1）、嵌套循环连接Nested-Loop join(NLJ)算法

一次一行循环地从第一张表（驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表）里取出满足条件的行，然后去除两表的结果合集。

例子1：

explain select * from t1 inner join t2 on t1.id =t2.id

t1总共10000行，t2总共100行，执行结果如下
在这里插入图片描述
从执行计划中看到的信息：

驱动表t2，被驱动表t1.先执行的就是驱动表（执行计划结果的id如果一样按照从上到下的顺序执行SQL）；优化器一般优先选择小表做驱动表。所以使用inner join 时，排在前面的表并不一定就是驱动表。
当使用left join 时，左表是驱动表，右表是被驱动表，当使用right join时，右表是驱动表，左表是被驱动表，当使用join 时，mysql 会选择数据量比较小的表作为驱动表，大表作为被驱动表。
使用了NLJ算法。一般join语句中，如果执行计划Extra 中出现Using join buffer 则表示使用 join算法就是NLJ。

上面SQL的大致流程如下：

从表t2中读取一行数据（如果t2表有查询过滤条件的，会从过滤结果里取出一行数据）；
从第1步的数据中，取出关联字段id,到表t1中查找；
取出t1中满足条件的行，跟t2中获取到的结果合并作为记过返回给客户端；
重复上面3步。

整个过程读取表t2的所有数据（扫描100行），然后宾利这没行数据中字段id的值，根据t2表中的id的值索引扫描t1表中的对应（扫描100次t1表的索引，1次扫描可以认为最终只扫描t1表一行完成的数据，也就是总共t1表也扫描了100行）。因此整个过程扫描了200行。

如果被驱动表没有索引，使用NLJ算法性能会比较低。

2）基于块的嵌套循环连接Block Nested-Loop Join(BNL)算法

把驱动表的数据读入到join_buffer 中，然后扫描被驱动表(关联关系没有索引)，把被驱动表每一行取出来跟join_buffer中的数据做对比。

select * from t1 inner join t2 on t1.id =t2.id

执行计划结果如下：
在这里插入图片描述
Extra 中Using join buffer（Block Nested-Loop）说明该关联查询使用的是BNL算法。

上面SQL的大致流程如下：

把t2的所有数据放入到join_buffer中
把表t1中每一行取出来，跟join_buffer中的数据做对比
返回满足join条件的数据

整个过程对表t1和t2都做了一次全表扫描，因此扫描的总行数为10000(表t1的数据总量)+100(表t2的数据总量)=10100.并且join_buffer里的数据是无序的，因此对表t1中的每一行，都要做100次判断，所以内存中的判断次数是100*10000=100万次。

如果例子中的t2是一个大表，join_buffer 放不下怎么办呢？

join_buffer的大小是有参数join_buffer_size设定的，默认值是256K.如果放不下t2所有数据，策略很简单，就是分段放。

比如：t2表有1000行记录，join_buffer只能放800行数据，那么执行过程就是先往join_buffer里放800行记录，然后从t1表里去数据跟join_buffer中的数据对比得出部分结果，然后清空join_buffer,再放入t2表剩余200行记录，再次从t1表里数据join_buffer中数据对比。索引就多扫了一次t1表。

被驱动表的关联字段没索引为什么要选择使用BNL算法而不使用Nested-Loop Join呢？

如果上面第二条SQL使用Nested-Loop Join,那么扫描行数为100*10000=100万次，这个是磁盘扫描。

很显然，用BNL磁盘扫描书少很多，相比于磁盘扫描，BNL的内存计算会快很多。

因此MySQL对于呗驱动表的关联字段没有索引的关联查询，一般都会使用BNL算法。如果有索引一般选择NLJ算法，有索引的情况下NLJ算法比BNL算法性能更高。

总结：对于关联SQL优化

关联字段添加索引，让mysql 的join操作尽量选择NLJ算法
小表驱动大表，写多表连接SQL时如果明确知道那张表小可以用straight_join写法固定连接驱动方式，省去MySQL优化器判断的时间。

straight_join解释：straight_join功能同join类似，但能让左边表来驱动右边表，能改表优化器对于联表查询的执行顺序。

straight_join 只适用inner join，并不是适用于left 、right join（eft 、right join 已经代表指定表的执行顺序）。
尽可能的让优化器去判断，因为大部分情况下mysql优化器比人要聪明。使用straight_join一定慎重，因为部分情况下人为的指定执行顺序并不一定比优化引擎要靠谱。

小表的定义：

在决定做驱动表的时候，应该是量个表按照各自的条件过滤，过滤完成之后，计算参与join的各个字段的总数据量，数据量小的那个表，就是“小表”，应作为驱动表

in和exsits优化

原则：小表驱动大表，即小的数据集驱动大的数据集

in:当B表的数据集小于A表的数据集时，in优先exists

select * from A where id in(select id from B)
-- 等价于
for(select id from B){
select * from A where A.id=B.id
}

exists:当A表的数据集小于B表的数据集时，exists优于in

j将住主查询A的数据，放到子查询B中做条件验证，根据验证结果(true或false)来决定主查询的数据是否保留。

select * from A where exists (select 1 from B where B.id=A.id)
--等价于
for(select * from A){
select * from B where B.id=A.id
}

EXISTS 只返回true或false，因此子查询中的select * 可以用select 1 替换。

EXISTS 子查询实际执行过程可能经过了优化而不是我们理解上的逐条对比。

EXISTS 子查询往往可以用join来代替，何种最优需要具体问题分析

3.表count查询优化

test 表有联合索引 name,age,postion

explain select count(1) from test;
explain select count(id) from test;
explain select count(name) from test;
explain select count(*) from test;

四个SQL的执行计划一样，说明这四个SQL的执行效率应该差不多

注意：count( * )会统计值为NULL的行，而count(列名)不会统计此列为NULL的值的行

字段有索引：count(*)≈count(1)>count(字段)>count(主键ID) ，字段有索引，count(字段)统计走二级索引，二级索引存储数据比主键索引少。

字段没有索引：count(*)≈count(1)>count(主键ID)>count(字段)

count(*) 是个例外，mysql 并不会把全部字段取出来，而是专门做了优化，不取值，按行累加，效率更高，所以不需要用count(列名)或者count(主键)替代。

为什么对count(id)，mysql 最终选择辅助索引而不是主键聚簇索引？因为二级索引存储的数据更少，检索性能应该搞，mysql内部做了点优化（应该是5.7版本才优化的）

常见优化方法：

1)、查询MySQL自己维护的总行数

对于mysam存储引擎的表不带where条件的count查询性能很高的，因为mysam存储引擎的表总行数会mysql存储在磁盘上，查询不需要计算；但是对于InnoDB存储引擎的表，mysql不会存储表的总记录行数（因为有MVCC机制）查询count需要实时计算。

2)、show table status

如果只需要表总行数的估计值，可以用如下SQL查询，性能很高

show  table status like 'test表明'

3)、将总数维护到Redis里

插入或删除表数据行的时候同时维护redis里的表总行数key的计数值（用incr 或decr 命令），但是这种方式可能不准，很难保证表操作和redis操作的事物一致性。

4)、增加数据库计数表

插入或删除表数据行的时候同时维护计数表，让他们在同一个事务里操作。

4.阿里MySQL规范解读

这是互联网的建议，看情况而定。

1)【推荐】单表行数超过500万行或者单表容量超过2GB，才推荐进行分库分表。说明：如果预计三年后的数量根本达不到这个级别，请不要在创建表时就分库分表。

索引规约：

2)【强制】业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。

说明：不要以为唯一索引影响insert速度，这个速度耗损可以忽略，但是查找速度明显；另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。

3)【强制】超过三个表禁止join。需要join的字段，数据类型保持绝对一致；多表关联查询时，保证被关联的字段需要有索引。

说明：即使双表join也要注音表索引、SQL性能。

4)【强制】在varchar字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本分度决定索引长度。

5)【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。

说明：索引文件具有B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。

6)【强制】不要使用count(列名)或count(常量)来替代count( * )，cunt( * )是SQL92定义的标准统计行数的语法，跟数据库无关，跟NULL和非NULL无关。

说明：count( * )会统计值为NULL的行，而count(列名)不会统计此列为NULL的值的行。

7)【推荐】in 操作能避免就避免，若实在避免不了，需要仔细评估in后面的集合元素数量(控制在1000个之内)。

8)【参考】因国际化需要，所有的字符存储与表示，均采用utf8字符集，那么字符计数方法需要注意。

说明：select length(“轻松工作”)；返回为12

select character_length(“轻松工作”)；返回为4

如果需要存储表情（是4B长度），那么选择utf8mb4来进行存储，utf8(3B长度)和utf8mb4大小有区别的。

5.MySQL数据类型选择分析

在MySQL中，选择正确的数据类型，对于性能至关重要。一般应该遵循下面两步：

1）确定合适的大类型：数字、字符串、时间、二进制；

2）确定具体的类型：有无符号、取值范围、变长定长等；

MySQL数据类型设置方面，尽量使用更小的数据类型，因为他们通常有更好的性能，花费更少的硬件资源。并且，尽量把字段定义为NOT NULL，避免使用 NULL

数字类型：

在这里插入图片描述

注音：数字类型正常使用时无符号（大于等于0），创建字段时勾下无符号选项。

优化建议：

如果整型数没有负数，如ID，建议指定为UNSIGNED无符号类型，容量可扩大一倍。
建议使用TINYINT代替ENUM、BITENUM、SET。
避免使用整数的显示宽度，不要用INT(10)类似的方法指定字段显示宽度，直接INT。
DECIMAL最适合保存准确度要求高，而且用于计算数据，比如价格。但是在是用DECIMAL类型的时候，注意长度设置。
建议使用整型类型来运算和存储实数，方法是，实数乘以相应的倍数后在操作。
整数通常是最佳的数据类型，因为它速度快，并且能使用AUTO_INCREMENT。

INT显示宽度

经常会使用命令来创建数据表，而且同时会指定一个长度，如下长度并不是TINYINT类型存储的最大长度，而是显示的最大长度。

CREATE TABLE ’user‘(
    'id' TINYINT(2) UNSIGNED 
)

这里表user表的id字段是TINYINT，可以存储最大数值是255.所以，在存储数据时，如果存入值小于等于255，虽然超过了2位，但是没有超出TINYINT类型长度，所以可以正常保存；如果存入值大于255，如600，那么MySQL会自动保存为TINYINT类型最大值。

在查询数据时，不管查询结果为何值，都按实际输出。这里TINYINT(2) 中的2的作用就是，当需要在查询结果前填充0时，命令中加上ZEROFILL就可以实现，如下

 'id' TINYINT(2) UNSIGNED ZEROFILL

这样，查询结果如果是5，那就输出就是05，其实实际存储的值还是5，只是MySQL输出数据时在前面填充了0

在MySQL字段类型长度TINYINT(2)不会影响数据的插入，只会在使用ZEROFILL时有用，让查询结果前填充0.

日期类型

在这里插入图片描述
优化建议：

MySQL能存储的最小时间粒度为秒。
建议用date数据类型保存日志。MySQL中默认的日期格式是yyyy-mm-dd.
用MySQL的内建类型DATE、TIME、DATETIME来存储时间，而不是字符串。
当数据类型格式为TIMESTAMP和DATETIME时，可以用CURRENT_TIMESTAMP作为默认(MySQL5.6以后)。
TIMESTAMP是UTC时间戳，与时区无关。
DATETIME 的存储格式是一个YYYYMMDD HH:MM:SS的整数，与时区无关，你存什么，读出来就是什么。
除非有特殊需求，一般公司建议使用TIMESTAMP，它比DATETIME更节省空间，但是到大公司一般用DATETIME，因为不用考虑时间上限的问题。

字符串

在这里插入图片描述
优化建议

字符串的长度相差较大用VARCHAR；字符串短，且所有值都接近一个长度的CHAR.
CHAR和VARCHAR适合用于包括人名、邮政编码、电话号码和不超过255个字符长度的任意字母数字组合。那些用来计算的数字不要用VARCHAR类型保存，因为可能会导致一些与计算相关的问题。可能会影响计算的准确性和完成性
尽量少用BLOB和TEXT，如果实在要用可以考虑BLOB和TEXT字段单独存一张表，用id关联。
BLOB系列存储二进制字符串，与字符集无关。TEXT系列存储非二进制字符串，与字符集相关。
BLOB和TEXT都不能有默认值。