MySQL 日常优化优化 SQL优化一般步骤通过慢查日志等定位那些执行效率较低的SQL语句 explain 分析SQL

优化

SQL优化一般步骤

通过慢查日志等定位那些执行效率较低的SQL语句
explain 分析SQL的执行计划

需要重点关注type、rows、filtered、extra。type由上至下，效率越来越高
- ALL 全表扫描
- index 索引全扫描
- range 索引范围扫描，常用语<,<=,>=,between,in等操作
- ref 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中
- eq_ref 类似ref，区别在于使用的是唯一索引，使用主键的关联查询
- const/system 单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询
- null MySQL不访问任何表或索引，直接返回结果
虽然上至下，效率越来越高，但是根据cost模型，假设有两个索引idx1(a, b, c),idx2(a, c)，SQL为select * from t where a = 1 and b in (1, 2) order by c;如果走idx1，那么是type为range，如果走idx2，那么type是ref；当需要扫描的行数，使用idx2大约是idx1的5倍以上时，会用idx1，否则会用idx2

Extra
- Using filesort：MySQL需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行。
- Using temporary：使用了临时表保存中间结果，性能特别差，需要重点优化
- Using index：表示相应的 select 操作中使用了覆盖索引（Coveing Index）,避免访问了表的数据行，效率不错！如果同时出现 using where，意味着无法直接通过索引查找来查询到符合条件的数据。
- Using index condition：MySQL5.6之后新增的ICP，using index condtion就是使用了ICP（索引下推），在存储引擎层进行数据过滤，而不是在服务层过滤，利用索引现有的数据减少回表的数据。
- Extra为Using where：SQL使用了where条件过滤数据。
show profile 分析

了解SQL执行的线程的状态及消耗的时间。

# 默认是关闭的，开启语句
set profiling = 1; 
SHOW PROFILES ;
SHOW PROFILE FOR QUERY  #{id};

trace

trace分析优化器如何选择执行计划，通过trace文件能够进一步了解为什么优惠券选择A执行计划而不选择B执行计划。

set optimizer_trace="enabled=on";
set optimizer_trace_max_mem_size=1000000;
select * from information_schema.optimizer_trace;

确定问题并采用相应的措施
- 优化索引
- 优化SQL语句：修改SQL、IN 查询分段、时间查询分段、基于上一次数据过滤
- 改用其他实现方式：ES、数仓等
- 数据碎片处理

SQL优化

分页优化(深度分页优化)

select * from table where type = 2 and level = 9 order by id asc limit 190289,10;
select * from table where type = 2 and level = 9 order by id asc limit 20,1000000000;

优化方案：

对于大分页的场景，可以优先让产品优化需求

延迟关联

先通过where条件提取出主键，在将该表与原数据表关联，通过主键id提取数据行，而不是通过原来的二级索引提取数据行
```
select a.* from table a, (select id from table where type = 2 and level = 9 order by id asc limit 190289,10 ) b where a.id = b.id
```

书签方式

书签方式说白了就是找到limit第一个参数对应的主键值，再根据这个主键值再去过滤并limit

select * from table where id > (select * from table where type = 2 and level = 9 order by id asc limit 190289, 1) limit 10;

between...and...

可以将limit查询转换为已知位置的查询，这样MySQL通过范围扫描between...and，就能获得到对应的结果。如果知道边界值为100000，100010后，就可以这样优化：
```
select  id,name,balance FROM account where id between 100000 and 100010 order by id;
```
把上一次的最后一条数据，也即id传过来，然后做“id < xxx”处理，但是这种一般需要改接口协议，并不一定可行。

索引优化

正确使用索引

假如没有添加索引，那么在查询时就会触发全表扫描，因此查询的数据就会很多，并且查询效率会很低，为了提高查询的性能，就需要给最常使用的查询字段上，添加相应的索引，这样才能提高查询的性能。

正确使用联合索引

使用了 B+ 树的 MySQL 数据库引擎，比如 InnoDB 引擎，在每次查询复合字段时是从左往右匹配数据，因此在创建联合索引的时候需要注意索引创建的顺序

create index idx_test_c1234 on test(c1,c2,c3,c4);

explain select * from test where c1='a1' and c2='a2' and c3='a3' and c4='c4';
explain select * from test where c1='a1' and c4='c4' and c2='a2' and c3='a3';

创建联合(复合)索引的顺序，上述explain执行结果是一样的：type=ref，key_len=132，ref=const，const，const，const。结论：在执行常量等值查询时，改变索引列的顺序并不会改变explain的执行结果，因为MySQL底层优化器会优化，但是推荐按照顺序编写sql语句。

explain select * from test where c1='a1' and c2='a2' and c3>'a3' and c4='c4';

当出现范围的时候，type=range，key_len=99，其中c4上的索引失效。结论：范围右边索引列失效，但是范围位置c3的索引有效。

explain select * from test where c1='a1' and c2='a2' and c4>'a4' and c3='c3';

key_len=132，说明用上所有索引，MySQL底层优化器进行优化，范围右边索引失效(c1,c2,c3,c4)，c4右边无索引列。

explain select * from test where c1>'a1' and c2='a2' and c3>'a3' and c4='c4';

在c1使用范围查询，则type=ALL，key=NULL，索引失效，全表扫描，违背最左前缀法则。如查询字段刚好包含查询条件应使用覆盖索引。结论：在最左前缀法则中，如果最左前列(带头大哥)的索引失效，则后面的索引都失效。

explain select * from test where c1>'a1' and c2='a2' and c4='c4' order by c3;

key_len=66，ref=const，const，c1，c2使用索引(查找)，c3索引用于排序。

# 索引(c1,c2,c3,c4)
# 无using filesort
explain select * from test where c1='a1' and c5='a5' order by c2,c3;
# 无using filesort c5未创建索引
explain select * from test where c1='a1' and c5='a5' order by c2,c3;
# 使用using filesort 违背原创建索引(c1,c2,c3,c4)
explain select * from test where c1='a1' and c5='a5' order by c3,c2;
# 无using filesort 由于c2为常量，在排序中被优化
explain select * from test where c1='a1' and c2='a2' order by c3,c2;
# 使用using filesort 跳过索引c3
explain select * from test where c1='a1' and c2='a2' order by c4;

# 范围查询阻断，后续字段不能走索引
explain select * from test where c1>'a1' order by c1;
# 优化
explain select * from (select id from test where c1>'a1' order by c1);

c1使用范围查询导致索引失效，全表扫描，type=ALL，ref=NULL。使用c1进行排序出现using filesort，采取覆盖索引进行优化。

# 用到c1索引，根据最左匹配，c4索引未用到，key_len=33 ref=const
explain select * from test where c1='a1' and c4='a4' group by c2,c3;
# group by c2，c3调换位置出现using temporary和using filesort
explain select * from test where c1='a1' and c4='a4' group by c3,c2;

# asc和desc混用
explain select * from test order by c1 asc, c2 desc;

虽然排序字段与索引顺序一样，默认是升序，由于c2的降序导致出现using filesort。

# in + order by 对于排序来说，多个等值条件也是范围查询
explain extended select c1 from test where c1 in ('a1','b1') order by c2,c3;

in查询在MySQL底层是通过n*m的方式去搜索，类似union，但是效率比union高。in查询在进行cost代价计算时(代价 = 元组数 * IO平均值)，是通过将in包含的数值，一条条去查询获取元组数的，效率低下，MySQL设置了个临界值(eq_range_index_dive_limit)，5.6之后超过这个临界值后该列的cost就不参与计算了。因此会导致执行计划选择不准确。默认是200，即in条件超过了200个数据，会导致in的代价计算存在问题，可能会导致Mysql选择的索引不准确。

优化方向：硬要解决需结合业务查看是否需要更改索引中字段的顺序。

总结：

MySQL支持两种方式的排序filesort和index。Using index利用索引本身完成排序，效率高。
order by满足两种情况下会使用Using index
- order by语句使用索引最左前列
- 使用where子句与order by子句条件列组合满足索引最左前列
尽量在索引列上完成排序，遵循索引的最左前缀法则
如果order by的条件不在索引列上则产生Using filesort
group by和order by类似，其本质就是先排序后分组，同样遵循创建的索引最左前缀法则。where高于having，能写在where的限定条件不要去having限定
- group by：Using temporary Using filesort
- order by：Using filesort

如何建立索引：

在where从句，group by从句，order by从句，on从句中出现的列
索引字段越小越好

离散度大的列放在联合索引的前面

# 如何建立索引
explain select * from test where staff_id=2 and customer_id=584;

# 查看离散程度
select count(distinct customer_id),count(distinct staff_id) from test;
# count(distinct customer_id):788 count(distinct staff_id):4
# 由于customer_id的离散程度更大(重复率低，可选择性更大)，所以应该使用index(customer_id,staff_id)

建立覆盖索引

InnoDB使用辅助索引查询数据时会回表，但是如果索引的叶节点中已经包含要查询的字段，那它没有必要再回表查询了，这就叫覆盖索引

select name from test where city='莆田'

我们将被查询的字段建立到联合索引中，这样查询结果就可以直接从索引中获取

alter table test add index idx_city_name (city, name);

适当使用前缀索引

MySQL 是支持前缀索引的，也就是说我们可以定义字符串的一部分来作为索引

我们知道索引越长占用的磁盘空间就越大，那么在相同数据页中能放下的索引值也就越少，这就意味着搜索索引需要的查询时间也就越长，进而查询的效率就会降低，所以我们可以适当的选择使用前缀索引，以减少空间的占用和提高查询效率

比如，邮箱的后缀都是固定的“@xxx.com”，那么类似这种后面几位为固定值的字段就非常适合定义为前缀索引

alter table test add index index2(email(6));

使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本

需要注意的是，前缀索引也存在缺点，MySQL无法利用前缀索引做order by和group by 操作，也无法作为覆盖索引

避免在 where 查询条件中使用 != 或者 <> 操作符

SQL中，不等于操作符会导致查询引擎放弃索引索引，引起全表扫描，即使比较的字段上有索引

在索引上，避免使用NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE等

解决方法：通过把不等于操作符改成or，可以使用索引，避免全表扫描

例如，把column<>’aaa’，改成column>’aaa’ or column<’aaa’，就可以使用索引了

查询具体的字段而非全部字段

要尽量避免使用select *，而是查询需要的字段，这样可以提升速度，以及减少网络传输的带宽压力

隐式类型，不要在列上进行运算操作

不要在列字段上进行算术运算或其他表达式运算，否则可能会导致查询引擎无法正确使用索引，从而影响了查询的效率

select * from test where id + 1 = 50;
select * from test where month(updateTime) = 7;

一个很容易踩的坑：隐式类型转换：

select * from test where skuId=123456

skuId这个字段上有索引，但是explain的结果却显示这条语句会全表扫描。原因在于skuId的字符类型是varchar(32)，比较值却是整型，故需要做类型转换

优化器选择不使用索引的情况

如果要求访问的数据量很小，则优化器还是会选择辅助索引，但是当访问的数据占整个表中数据的蛮大一部分时（一般是20%左右），优化器会选择通过聚集索引来查找数据。

select * from _order where  order_status = 1

查询出所有未支付的订单，一般这种订单是很少的，即使建了索引，也没法使用索引。

适当增加冗余字段

增加冗余字段可以减少大量的连表查询，因为多张表的连表查询性能很低，所有可以适当的增加冗余字段，以减少多张表的关联查询，这是以空间换时间的优化策略

Join优化

MySQL的join语句连接表使用的是nested-loop join算法，这个过程类似于嵌套循环，简单来说，就是遍历驱动表(外层表)，每读出一行数据，取出连接字段到被驱动表(内层表)里查找满足条件的行，组成结果行。要提升join语句的性能，就要尽可能减少嵌套循环的循环次数

优化方式

对被驱动表的join字段建立索引，利用索引能快速匹配到对应的行，避免与内层表每一行记录做比较，极大地减少总循环次数。
连接时用小结果集驱动大结果集，在索引优化的基础上能进一步减少嵌套循环的次数。如果难以判断哪个是大表，哪个是小表，可以用inner join连接，MySQL会自动选择小表去驱动大表

在 MySQL 5.0 之前的版本尽量避免使用or查询

在 MySQL 5.0 之前的版本要尽量避免使用 or 查询，可以使用 union 或者子查询来替代，因为早期的 MySQL 版本使用 or 查询可能会导致索引失效，在 MySQL 5.0 之后的版本中引入了索引合并。

继续优化：如业务可使用组合索引来代替索引合并，强制走单索引。

优化子查询

尽量使用 Join 语句来替代子查询，因为子查询是嵌套查询，而嵌套查询会新创建一张临时表，而临时表的创建与销毁会占用一定的系统资源以及花费一定的时间，同时对于返回结果集比较大的子查询，其对查询性能的影响更大

小表驱动大表

我们要尽量使用小表驱动大表的方式进行查询，也就是如果 B 表的数据小于 A 表的数据，那执行的顺序就是先查 B 表再查 A 表(借助索引提高此表的搜索速度)，具体查询语句如下：

select name from A where id in (select id from B);

避免使用JOIN关联太多的表

对于 MySQL 来说，是存在关联缓存的，缓存的大小可以由join_buffer_size参数进行设置

在 MySQL 中，对于同一个 SQL 多关联（join）一个表，就会多分配一个关联缓存，如果在一个 SQL 中关联的表越多，所占用的内存也就越大

如果程序中大量的使用了多表关联的操作，同时join_buffer_size设置的也不合理的情况下，就容易造成服务器内存溢出的情况，就会影响到服务器数据库性能的稳定性

UNION优化

MySQL处理union的策略是先创建临时表，然后将各个查询结果填充到临时表中最后再来做查询，很多优化策略在union查询中都会失效，因为它无法利用索引

最好手工将where、limit等子句下推到union的各个子查询中，以便优化器可以充分利用这些条件进行优化

此外，除非确实需要服务器去重，一定要使用union all，如果不加all关键字，MySQL会给临时表加上distinct选项，这会导致对整个临时表做唯一性检查，代价很高

慢查询日志

出现慢查询通常的排查手段是先使用慢查询日志功能，查询出比较慢的 SQL 语句，然后再通过 Explain 来查询 SQL 语句的执行计划，最后分析并定位出问题的根源，再进行处理

慢查询日志指的是在 MySQL 中可以通过配置来开启慢查询日志的记录功能，超过long_query_time值的 SQL 将会被记录在日志中

我们可以通过设置“slow_query_log=1”来开启慢查询

需要注意的是，在开启慢日志功能之后，会对 MySQL 的性能造成一定的影响，因此在生产环境中要慎用此功能

大数据量

对于推送业务的数据存储，可能数据量会很大，如果在方案的选择上，最终选择存储在MySQL上，并且做7天等有效期的保存。那么需要注意，频繁的清理数据，会照成数据碎片，需要联系DBA进行数据碎片处理。

复杂查询

select sum(amt) from _t where a = 1 and b in (1, 2, 3) and c > '2020-01-01';

select * from _t where a = 1 and b in (1, 2, 3) and c > '2020-01-01' limit 10;

如果是统计某些数据，可能改用数仓进行解决；

如果是业务上就有那么复杂的查询，可能就不建议继续走SQL了，而是采用其他的方式进行解决，比如使用ES等进行解决。

设计优化

表结构设计

尽量避免使用NULL

NULL在MySQL中不好处理，存储需要额外空间，运算也需要特殊的运算符，含有NULL的列很难进行查询优化

应当指定列为not null，用0、空串或其他特殊的值代替空值，比如定义为int not null default 0

最小数据长度

越小的数据类型长度通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快

使用最简单数据类型

简单的数据类型操作代价更低，比如：能使用 int 类型就不要使用 varchar 类型，因为 int 类型比 varchar 类型的查询效率更高

尽量少定义 text 类型

text 类型的查询效率很低，如果必须要使用 text 定义字段，可以把此字段分离成子表，需要查询此字段时使用联合查询，这样可以提高主表的查询效率

适当分表、分库策略

分表是指当一张表中的字段更多时，可以尝试将一张大表拆分为多张子表，把使用比较高频的主信息放入主表中，其他的放入子表，这样我们大部分查询只需要查询字段更少的主表就可以完成了，从而有效的提高了查询的效率

分库是指将一个数据库分为多个数据库。比如我们把一个数据库拆分为了多个数据库，一个主数据库用于写入和修改数据，其他的用于同步主数据并提供给客户端查询，这样就把一个库的读和写的压力，分摊给了多个库，从而提高了数据库整体的运行效率

常见类型选择

整数类型宽度设置

MySQL可以为整数类型指定宽度，例如int(11)，实际上并没有意义，它并不会限制值的范围，对于存储和计算来说，int(1)和int(20)是相同的

VARCHAR和CHAR类型

char类型是定长的，而varchar存储可变字符串，比定长更省空间，但是varchar需要额外1或2个字节记录字符串长度，更新时也容易产生碎片

需要结合使用场景来选择：如果字符串列最大长度比平均长度大很多，或者列的更新很少，选择varchar较合适；如果要存很短的字符串，或者字符串值长度都相同，比如MD5值，或者列数据经常变更，选择使用char类型

DATETIME和TIMESTAMP类型

datetime的范围更大，能表示从1001到9999年，timestamp只能表示从1970年到2038年。datetime与时区无关，timestamp显示值依赖于时区。在大多数场景下，这两种类型都能良好地工作，但是建议使用timestamp，因为datetime占用8个字节，timestamp只占用了4个字节，timestamp空间效率更高

BLOB和TEXT类型

blob和text都是为存储很大数据而设计的字符串数据类型，分别采用二进制和字符方式存储

在实际使用中，要慎用这两种类型，它们的查询效率很低，如果字段必须要使用这两种类型，可以把此字段分离成子表，需要查询此字段时使用联合查询，这样可以提高主表的查询效率

范式化

当数据较好范式化时，修改的数据更少，而且范式化的表通常要小，可以有更多的数据缓存在内存中，所以执行操作会更快

缺点则是查询时需要更多的关联，冗余字段等优化手段破坏范式化

第一范式：字段不可分割，数据库默认支持

第二范式：消除对主键的部分依赖，可以在表中加上一个与业务逻辑无关的字段作为主键，比如用自增id

第三范式：消除对主键的传递依赖，可以将表拆分，减少数据冗余

硬件优化

MySQL 对硬件的要求主要体现在三个方面：磁盘、网络和内存

磁盘

磁盘应该尽量使用有高性能读写能力的磁盘，比如固态硬盘，这样就可以减少 I/O 运行的时间，从而提高了 MySQL 整体的运行效率

磁盘也可以尽量使用多个小磁盘而不是一个大磁盘，因为磁盘的转速是固定的，有多个小磁盘就相当于拥有多个并行运行的磁盘一样

网络

保证网络带宽的通畅（低延迟）以及够大的网络带宽是 MySQL 正常运行的基本条件，如果条件允许的话也可以设置多个网卡，以提高网络高峰期 MySQL 服务器的运行效率

内存

MySQL 服务器的内存越大，那么存储和缓存的信息也就越多，而内存的性能是非常高的，从而提高了整个 MySQL 的运行效率

索引的维护

查找重复及冗余索引

冗余索引是指多个索引的前缀相同，或是在联合索引中包含主键的索引。如index(name,id)

使用pt-duplicate-key-checker工具检查重复及冗余索引

使用：

pt-duplicate-key-checker -h127.0.0.1 -uroot -proot
# 指定数据库
pt-duplicate-key-checker -h127.0.0.1 -uroot -proot -dsakila

删除不用索引

在MySQL中可以通过慢日志配合pt-index-usage工具进行索引使用情况分析。

pt-index-usage -h127.0.0.1 -uroot -proot /data/mysql/hive-slow.log

慢查询

# 查看包含log的参数
show variables like '%log%'; 

# 查看慢查询日志是否开启
show variables like 'slow_query_log'; 

# 查看慢查询日志存储位置
show variables like 'slow_query_log_file'; 

# 开启慢查询日志
set global slow_query_log=on; 

# 指定慢查询日志存储位置
set global show_query_log_file='/data/mysql/hive-slow.log';

# 记录没有使用索引的sql 开启慢查询日志
set global log_queries_not_using_indexes=on; 

# 查看慢查询设置的时间 超过此时间记录到慢查询日志中
show variables like' long_query_time'; 

# 记录查询超过1s的sql
set global long_query_time=1;

项目场景优化

count(*)

由于InnoDB对于count(*)是从索引上遍历一遍获取的数据，所以使用count()是比较费性能的。

在明细查询的时候会大量用到count函数，所以将那些无条件的查询且数据量较大的查询在展示的时候肯定会用到count。维护一张表将count()的值存进来，当有数据插入时利用事务同时更新表数据。参考13讲。

count()与max()

# 查询最后支付时间--优化max()函数
explain select max(payment_date) from payment;
# 给payment_date建立索引(覆盖索引)
create index idx_paydate on payment(payment_date);

# 在一条SQL中同时查出2006年和2007年电影的数量--优化count()函数，其实像这种历史静态数据的查询不应该由MySQL来执行计算，而是由其他方式提供如GraphQL
# count(‘任意内容’)都会统计出所有记录数，因为count只有在遇到null时不计数，即count(null)==0
explain select count(release_year=‘2006’ or null) as '2006电影数量',count(release_year='2007' or null) as '2007电影数量' from film;
# release_year建立索引
create index idx_release_year on film(release_year);

子查询优化

通常情况下需要把子查询优化为join查询，但在优化时要注意关键是否有一对多的关系，需注意重复数据。

# 查询sandra出演的所有电影片
explain select title,release_year,length from flim where flim_id in (select flim_id from flim_actor where actor_id= in (select actor_id from actor where frist_name='sandra'));

# 优化
explain select title,release_year,length from flim f join flim_actor fa on fa.flim_id=f.flim_id join actor a on fa.actor_id=a.actor_id where a.frist_name='sandra';

# 将frist_name设为索引
create index idx_frist_name on actor(frist_name);

group by的优化

先给分组字段创建索引
再对该表分组、分组后再和其他表关联查询

# 每个演员参与影片的数量
explain select a.frist_name,a.last_name,count(*) from flim_actor fa inner join actor a using(actor_id) group by fa.actor_id;

# 优化子查询索引
explain select a.frist_name,a.last_name,count(*) from actor a inner join (select actor_id,count(*) as cnt from flim_actor group by actor_id) as c using(actor_id);

in与exists

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

in：当B表的数据集必须小于A表的数据集时，in优于exists

select * from A where id in (select id from B);

explain select * from film where id in (select flim_id from film_actor);

exists：当A表的数据集小于B表的数据集时，exists优于in将主查询A的数据，放到子查询B中做条件验证，根据验证结果(true或false)来决定主查询的数据是否保留
```
# A表与B表的ID字段应建立索引
select * from A where exists (select 1 from B where B.id=A.id) 
```
```
explain select * from film where exists (select 1 from film_actor where film_actor where film_actor.film_id =film.id);
```
- exists(subquery) 只返回true或false，因此子查询中的select *也可以是select 1或select x，官方说法是实际执行时会忽略select清单，因此没有区别
- exists子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比
- exists子查询往往也可以用join代替，具体问题具体分析

join

对连接属性进行排序时，应当选择驱动表(B)的属性作为排序表中的条件

# using temporary，using filesort
explain select * from film join film_actor on film_actor.film_id=film.id order by film.id;
# using filesort
explain select * from film join film_actor on film_actor.film_id=film.id order by film_actor.id;
# 驱动表建立actor_id索引 using index
explain select * from film join film_actor on film_actor.film_id=film.id order by film_actor.id;

随机rand()优化

背景：一个单词表中，随机选择1个word值，可以怎么做呢？思路上是这样的：参考17讲。

通常写法就是order by rand()。这个语句需要Using temporary和 Using filesort，查询的执行代价往往是比较大的。所以，在设计的时候你要量避开这种写法。

随机算法1

取得这个表的主键id的最大值M和最小值N;
用随机函数生成一个最大值到最小值之间的数 X = (M-N)*rand() +N;
取不小于X的第一个ID的行。

select max(id),min(id) into @M,@N from t;
set @X=floor((@M-@N+1)*rand()+@N);
select 8 from t where id >=@X limit 1;

这个算法本身并不严格满足题目的随机要求，因为ID中间可能有空洞，因此选择不同行的概率不一样，不是真正的随机。

比如你有4个id，分别是1、2、4、5，如果按照上面的方法，那么取到 id=4的这一行的概率是取得其他行概率的两倍。

如果这四行的id分别是1、2、40000、40001呢？这个算法基本就能当bug来看待了。

空洞的问题，如果单词库不变，可以在上线前整理数据，把空洞处理掉。比如：原来单词存在A表，新建B表，执行 insert into B(word) selec t word from A. B的id是自增的，就会生成连续的主键。当然如果A表写比较频繁，且数据量较大，业务上禁用这种写法，RR的隔离级别会锁A表。
数据库还在设计阶段,可以增加一个主键字段,用来记录每行记录的rowid，这样一万行，那就是连续的一万，然后随机，用该随机rowid回表查询该行记录。就是确保连续，可以快速的得到C和几个偏移量。

随机算法2

取得整个表的行数，并记为C。
取得 Y = floor(C*rand())。 floor函数在这里的作用，就是取整数部分。
再用limit Y,1 取得一行。

 select count(*) into @C from t; 
 set @Y = floor(@C * rand()); 
 set @sql = concat("select * from t limit ", @Y, ",1"); 
 prepare stmt from @sql; 
 execute stmt; DEALLOCATE prepare stmt;

由于limit 后面的参数不能直接跟变量，所以我在上面的代码中使用了prepare+execute的方法。你也可以把拼接SQL语句的方法写在应用程序中，会更简单些。解决了算法1里面明显的概率不均匀问题。

扫描主要是count的全局扫描+limit的Y+1条扫描，执行代价肯定会比order by rand()代价小。

算法2随机取三个值

取得整个表的行数，记为C；
根据相同的随机方法得到Y1、Y2、Y3；
再执行三个limit Y, 1语句得到三行数据。

select count(*) into @C from t; 
set @Y1 = floor(@C * rand()); 
set @Y2 = floor(@C * rand()); 
set @Y3 = floor(@C * rand()); 
select * from t limit @Y1，1； //在应用代码里面取Y1、Y2、Y3值，拼出SQL后执行 
select * from t limit @Y2，1； 
select * from t limit @Y3，1；

同样可以优化成

id1 = select * from t limit @Y1，1； 
id2= select * from t where id > id1 limit @Y2-@Y1，1； 
select * from t where id > id2 limit @Y3 - @Y2，1；

深度limit 案例

mp.weixin.qq.com/s/k90zSV_Tl…

背景

有一张财务流水表，未分库分表，目前的数据量为9555695，分页查询使用到了limit，优化之前的查询耗时16 s 938 ms (execution: 16 s 831 ms, fetching: 107 ms)，按照下文的方式调整SQL后，耗时347 ms (execution: 163 ms, fetching: 184 ms)；

操作： 查询条件放到子查询中，子查询只查主键ID，然后使用子查询中确定的主键关联查询其他的属性字段；

原理： 减少回表操作；

-- 优化前SQL
SELECT  各种字段 FROM `table_name` WHERE 各种条件 LIMIT 0,10;

-- 优化后SQL
SELECT  各种字段
FROM `table_name` main_tale
RIGHT JOIN
(
SELECT  子查询只查主键
FROM `table_name`
WHERE 各种条件
LIMIT 0,10;
) temp_table ON temp_table.主键 = main_table.主键

分析

MySQL:5.7

mysql> desc test;
+--------+---------------------+------+-----+---------+----------------+
| Field  | Type                | Null | Key | Default | Extra          |
+--------+---------------------+------+-----+---------+----------------+
| id     | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| val    | int(10) unsigned    | NO   | MUL | 0       |                |
| source | int(10) unsigned    | NO   |     | 0       |                |
+--------+---------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

id为自增主键，val为非唯一索引。

当limit offset rows中的offset很大时，会出现效率问题：

mysql> select * from test where val=4 limit 300000,5;
+---------+-----+--------+
| id      | val | source |
+---------+-----+--------+
| 3327622 |   4 |      4 |
| 3327632 |   4 |      4 |
| 3327642 |   4 |      4 |
| 3327652 |   4 |      4 |
| 3327662 |   4 |      4 |
+---------+-----+--------+
5 rows in set (15.98 sec)

一般会改写成如下语句：

mysql> select * from test a inner join (select id from test where val=4 limit 300000,5) b on a.id=b.id;
+---------+-----+--------+---------+
| id      | val | source | id      |
+---------+-----+--------+---------+
| 3327622 |   4 |      4 | 3327622 |
| 3327632 |   4 |      4 | 3327632 |
| 3327642 |   4 |      4 | 3327642 |
| 3327652 |   4 |      4 | 3327652 |
| 3327662 |   4 |      4 | 3327662 |
+---------+-----+--------+---------+
5 rows in set (0.38 sec)

优化前查询过程

select * from test where val=4 limit 300000,5;

查询到索引叶子节点数据。根据叶子节点上的主键值去聚簇索引上查询需要的全部字段值。

如图所示：需要查询300005次索引节点，查询300005次聚簇索引的数据，最后再将结果过滤掉前300000条，取出最后5条。MySQL耗费了大量随机I/O在查询聚簇索引的数据上，而有300000次随机I/O查询到的数据是不会出现在结果集当中的。

证明

为了证明，我们需要知道MySQL有没有办法统计在一个sql中通过索引节点查询数据节点的次数。先试了Handler_read_ *系列，很遗憾没有一个变量能满足条件。

只能通过间接的方式来证实：

InnoDB中有buffer pool。里面存有最近访问过的数据页，包括数据页和索引页。所以我们需要运行两个sql，来比较buffer pool中的数据页的数量。预测结果是运行select * from test a inner join (select id from test where val=4 limit 300000,5); 之后，buffer pool中的数据页的数量远远少于select * from test where val=4 limit 300000,5;对应的数量，因为前一个sql只访问5次数据页，而后一个sql访问300005次数据页。

mysql> select * from test where val=4 limit 300000,5;
+---------+-----+--------+
| id      | val | source |
+---------+-----+--------+| 
3327622 |   4 |      4 |
| 3327632 |   4 |      4 |
| 3327642 |   4 |      4 |
| 3327652 |   4 |      4 |
| 3327662 |   4 |      4 |
+---------+-----+--------+
5 rows in set (26.19 sec)

mysql> select index_name,count(*) from information_schema.INNODB_BUFFER_PAGE where INDEX_NAME in('val','primary') and TABLE_NAME like '%test%' group by index_name;
+------------+----------+
| index_name | count(*) |
+------------+----------+
| PRIMARY    |     4098 |
| val        |      208 |
+------------+----------+
2 rows in set (0.04 sec)

可以看出，此时buffer pool中关于test表有4098个数据页，208个索引页。

清空buffer pool，重启mysql。

mysqladmin shutdown
/usr/local/bin/mysqld_safe &

mysql> select * from test a inner join (select id from test where val=4 limit 300000,5) b on a.id=b.id;
+---------+-----+--------+---------+
| id      | val | source | id      |
+---------+-----+--------+---------+
| 3327622 |   4 |      4 | 3327622 |
| 3327632 |   4 |      4 | 3327632 |
| 3327642 |   4 |      4 | 3327642 |
| 3327652 |   4 |      4 | 3327652 |
| 3327662 |   4 |      4 | 3327662 |
+---------+-----+--------+---------+
5 rows in set (0.09 sec)

mysql> select index_name,count(*) from information_schema.INNODB_BUFFER_PAGE where INDEX_NAME in('val','primary') and TABLE_NAME like '%test%' group by index_name;
+------------+----------+
| index_name | count(*) |
+------------+----------+
| PRIMARY    |        5 |
| val        |      390 |
+------------+----------+
2 rows in set (0.03 sec)

第一个sql加载了4098个数据页到buffer pool，而第二个sql只加载了5个数据页到buffer pool。

这会造成一个问题：加载了很多热点不是很高的数据页到buffer pool，会造成buffer pool的污染，占用buffer pool的空间。

为了在每次重启时确保清空buffer pool，我们需要关闭innodb_buffer_pool_dump_at_shutdown和innodb_buffer_pool_load_at_startup，这两个选项能够控制数据库关闭时dump出buffer pool中的数据和在数据库开启时载入在磁盘上备份buffer pool的数据。

order by limit案例

背景：联合索引(city,name)，执行语句

select *fromt where city in (“杭州”," 苏州 ") order by name limit 100;

于这条SQL语句同时查了"杭州"和" 苏州 "两个城市，因此所有满足条件的name就不是递增的了。也就是说，这条SQL语句需要排序。如何避免排序呢？

将语句分拆成两条

select *fromt where city=“杭州” order byname limit 100;
select *fromt where city=“苏州” order byname limit 100;

返回的两个有序数组可以进行归并排序。

“limit 100”改成“limit 10000,100”的话，就拆分成

select * from t where city="杭州" order by name limit 10100;
select * from t where city="苏州" order by name limit 10100;

如果数据单行数据量大，可变成

select id,name from t where city="杭州" order by name limit 10100;

是否需要这样改变根据性能需求和开发的复杂度做出权衡。

对字段使用函数计算

背景：一张表记录2016-2018是所有数据，现在要统计发生在所有年份中7月份的交易记录总数。执行很久

select count(*) from tradelog where month(t_modified)=7;

如果对字段做了函数计算，就用不上索引了，这是MySQL的规定。

从InnoDB的索引结构上分析，我们为什么能快速定位到where t_modified='2018-7-1’，是因为同一层兄弟节点的有序性。如果计算month()函数的话，你会看到传入7的时候，在树的第一层就不知道该怎么办了。

对索引字段作函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走书搜索功能。

放弃了树搜索功能，优化器可以选择遍历主键索引，也可以选择遍历索引t_modified，优化器对比索引大小后现，索引t_modified更小，遍历这个索引比遍历主键索引来得更快。因此最终还是会选择索引t_modified。

由于在t_modified字段加了month()函数操作，导致了全索引扫描。

树搜索就是同一层是有序的对比判断一下位置全索引搜索就是所有都过一遍。

优化：

select count(*) from tradelog where 
(t_modified >= '2016-7-1' and t_modified<'2016-8-1') or 
(t_modified >= '2017-7-1' and t_modified<'2017-8-1') or 
(t_modified >= '2018-7-1' and t_modified<'2018-8-1');

如果你的系统上线时间更早，或者后面又插入了之后年份的数据的话，你就需要再把其他年份补齐。

隐式类型转换

背景：由于上次知道在字段上使用函数会导致MySQL不走索引，这次需要提防隐式转换。

 select * from tradelog where tradeid=110717;

tradeid是字符串类型。

有两个问题：

数据类型转换的规则是什么？

进行简单的测试select “10” > 9的结果：
- 如果规则是“将字符串转成数字”，那么就是做数字比较，结果应该是1；
- 如果规则是“将数字转成字符串”，那么就是做字符串比较，结果应该是0。
结果是1。
为什么有数据类型转换，就需要走全索引扫描？

对于优化器来说，这个语句相当于：
```
select * from tradelog where CAST(tradidAS signed int) = 110717;
```

id的类型是int，如果执行下面这个语句，是否会导致全表扫描呢？使用了主键索引

select * from tradelog where id="83126";

隐式转换之字符类型不同

两个表tradelog与tradedetail

执行语句：

把tradelog称为驱动表，把trade_detail称为被驱动表，把tradeid称为关联字段。

mysql> select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2; /*语句Q1*/

第一行显示优化器会先在交易记录表tradelog上查到id=2的行，这个步骤用上了主键索引，rows=1表示只扫描一行；
第二行key=NULL，表示没有用上交易详情表trade_detail上的tradeid索引，进行了全表扫描。

所以上面的写法等同于为：

select * from trade_detail where CONVERT(traideid USING utf8mb4)=$L2.tradeid.value;

由于这两个表的字符集不同，tradedetail是utf8，tradelog是 utf8mb4，所以做表连接查询的时候用不上关联字段的索引。其中字符集utf8mb4是utf8的超集，所以当这两个类型的字符串在做比较的时候，MySQL内部的操作是，先把utf8字符串转成utf8mb4字符集，再做比较。（按数据长度增加的方向”进行转换的）

CONVERT()函数，在这里的意思是把输入的字符串转成utf8mb4字符集。

连接过程中要求在被驱动表的索引字段上加函数操作，是直接导致对被驱动表做全表扫描的原因。

另一种需求：驱动表转换一下

mysql>select l.operator from tradelog l , trade_detail d where d.tradeid=l.tradeid and d.id=4;

这次的查询操作用上了被驱动表tradelog里的索引(tradeid)，扫描行数是1。

其实这个语句就是：

select operator from tradelog where traideid =CONVERT($R4.tradeid.value USING utf8mb4);

这里的CONVERT函数是加在输入参数上的，这样就可以用上被驱动表的traideid索引。

如何解决：

比较常见的优化方法是，把trade_detail表上的tradeid字段的字符集也改成utf8mb4，这样就没有字符集转换的问题了。
```
alter table trade_detail modify tradeid varchar(32) CHARACTER SET utf8mb4 default null;
```
如果能够修改字段的字符集的话，是最好不过了。但如果数据量比较大，或者业务上暂时不能做这个DDL的话，那就只能采用修改SQL语句的方法了。
```
mysql> select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2;
```
主动把 l.tradeid转成utf8，就避免了被驱动表上的字符编码转换

MySQL偷懒

对于select *from tradelog where id + 1 = 10000这个SQL语句，这个加1操作并不会改变有序性，但是MySQL优化器还是不能用id索引快速定位到9999这一行。应该手动改成 where id = 10000 -1才可以。

深度分页案例

假设现在有表结构如下，并且有200万数据。业务需求：获取最2021年的A类型账户数据，上报到大数据平台。

CREATE TABLE account (
 id varchar(32) COLLATE utf8_bin NOT NULL COMMENT '主键',
 account_no varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '账号'
 amount decimal(20,2) DEFAULT NULL COMMENT '金额'
 type varchar(10) COLLATE utf8_bin DEFAULT NULL COMMENT '类型A，B'
 create_time datetime DEFAULT NULL COMMENT '创建时间',
 update_time datetime DEFAULT NULL COMMENT '更新时间',
 PRIMARY KEY (id),
 KEY `idx_account_no` (account_no),
 KEY `idx_create_time` (create_time)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='账户表'

日常写法：

//查询上报总数量
Integer total = accountDAO.countAccount();

//查询上报总数量对应的SQL
<select id='countAccount' resultType="java.lang.Integer">
  select count(1) 
  from account
  where create_time >='2021-01-01 00:00:00'
  and  type ='A'
</selelct>
    
//计算页数
int pageNo = total % pageSize == 0 ? total / pageSize : (total / pageSize + 1);
//分页查询，上报
for(int i = 0; i < pageNo; i++){
 List<AcctountPO> list = accountDAO.listAccountByPage(startRow,pageSize);
 startRow = (pageNo-1)*pageSize;
 //上报大数据
 postBigData(list);
}

//分页查询SQL（可能存在limit深分页问题，因为account表数据量几百万）
<select id ='listAccountByPage' >
  seelct * 
  from account
  where create_time >='2021-01-01 00:00:00'
  and  type ='A'
  limit #{startRow},#{pageSize}
</select>

优化方案：

使用标签记录法，id主键不是连续也可以使用，id不是连续，我们可以通过order by让它连续嘛。优化方案如下：

// 采用逻辑分页，缺点是需要记录上次查询的id
//查询最小ID
String  lastId = accountDAO.queryMinId();

//查询最小ID对应的SQL
<select id="queryMinId" returnType=“java.lang.String”>
select MIN(id) 
from account
where create_time >='2021-01-01 00:00:00'
and type ='A'
</select>

//一页的条数
Integer pageSize = 100;

List<AcctountPO> list ;
do{
   list = listAccountByPage(lastId,pageSize);
   //标签记录法，记录上次查询过的Id
   lastId = list.get(list,size()-1).getId();
    //上报大数据
    postBigData(list);
}while(CollectionUtils.isNotEmpty(list));

<select id ="listAccountByPage">
  select * 
  from account 
  where create_time >='2021-01-01 00:00:00'
  and id > #{lastId}
  and type ='A'
  order by id asc  
  limit #{pageSize}
</select>

参考

mp.weixin.qq.com/s/AuDUJs35d…

MySQL 45讲

后续继续补充... ...