【Mysql】Mysql优化案例总结篇

本文主要参考文章：SQL优化最干货总结 - MySQL（2020最新版）
后续不断补充
索引失效场景文章：索引失效场景

（一）Mysql优化的思路

【1】硬件配置

【2】Mysql配置

并发数是指同一时刻数据库能处理多少个请求，由max_connections和max_user_connections决定。max_connections是指MySQL实例的最大连接数，上限值是16384，max_user_connections是指每个数据库用户的最大连接数。

MySQL会为每个连接提供缓冲区，意味着消耗更多的内存。如果连接数设置太高硬件吃不消，太低又不能充分利用硬件。一般要求两者比值超过10%，计算方法如下：

max_used_connections / max_connections * 100% = 3/100 *100% ≈ 3%
1

查看最大连接数与响应最大连接数：

show variables like '%max_connections%';show variables like '%max_user_connections%';
1

在配置文件my.cnf中修改最大连接数

[mysqld]max_connections = 100max_used_connections = 20
1

【3】数据表设计

【1】监控获取慢sql

监控sql，开启慢日志获取那些执行比较慢的sql，showProfile，查看sql的执行时间，耗费的资源
set global slow_query_log=1局部开启慢日志
set global long_query_time=3设置超时阈值
cat localhost-slow.log查看慢查询日志
结合mysqldumpslow工具排查慢日志中访问次数最多的sql

【2】查看数据库连接

连接，查看数据库的连接，有时候mysql连接的太多也会导致很慢
例如在for循环中写sql，每个sql在执行的时候都要去连接一遍数据库，这是个很耗时的操作，mysql可以通过配置参数来修改最大连接数

【3】合理的设计数据库

（1）合理的设计表

（1）尽量选择用InnoDB引擎，而不用MyISAM引擎
（2）将字段很多的表分解成多个表
对于字段比较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。
（3）适当增加中间表
对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，把需要经常联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询，以此来提高查询效率。

（2）选择合适的字段类型

用尽量少的存储空间来存数一个字段的数据
（1）能使用int就不要使用varchar、char，能用varchar(16)就不要使用varchar(256)；
（2）IP地址最好使用int类型；
（3）固定长度的类型最好使用char，例如：邮编、工号等等；
（4）如果长度能够满足，能使用tinyint就不要使用smallint，int；
（5）比避免空值，最好给每个字段一个默认值，最好不能为null；
（6）精度要求较高的，比如金额，使用decimal类型，也可以使用BIGINT，比如精确两位小数就乘以100后保存。
DECIMAL类型是一种存储方式，它可以用于精确计算。在计算过程中它会转化成double类型，但是DECIMAL的精确计算代价很高。为了减少这种高消耗我们可以在保留精度的条件下使用BIGINT来存储数据。举例来说，比如要保留6位小数，那么我们在存储数据的时候就可以给每一个数乘以100万，然后在取数据的时候在给每一个数除以100万。以减少代价。
（7）存储时间尽量采用timestamp而非datetime。
datetime时间范围更广，但是占8字节；时间戳timestamp时间范围小，但是只占4字节

【4】合理的使用索引

（二）sql优化三点原则
最大化利用索引；
尽可能避免全表扫描；
减少无效数据的查询；

所以优化的主要内容就是 1-如何合理的创建索引，2-如何合理的使用创建的索引，3-如何避免索引失效

索引失效的底层原理：创建索引会生成B+树结构，所有的数据放在叶子结点中，每次查询时使用“二分查找法”，二分查找最终的原则就是保证要查的数据是有序的，要保证有序，就要保证每个索引查出来的值是唯一确定的，这样下一个索引对应的查找值才能是有序的。如果一个索引断掉，或者一个索引成了范围查找等等，就会导致后面索引面对的是无序的数值，自然索引也就会失效了。

（1）创建合理的索引

1-合理使用覆盖索引

select login_name, nick_name from member where login_name = ?
1

login_name，nick_name两个字段建立组合索引，比login_name简单索引要更快。

2-单表索引数不超过5个、单个索引字段数不超过5个

（2）合理的使用创建的索引

（3）避免索引失效的场景

1-【避免1】避免前后都有%的模糊查询

SELECT * FROM t WHERE username LIKE '%陈%'
1

【优化方案1】尽量在字段后面使用模糊查询

【补充说明】如果需求是要在前面使用模糊查询（待研究）
去除了前面的%查询将会命中索引，但是产品经理一定要前后模糊匹配呢？全文索引fulltext可以尝试一下，但Elasticsearch才是终极武器。

1.使用MySQL内置函数INSTR(str,substr) 来匹配，作用类似于java中的indexOf()，查询字符串出现的角标位置，可参阅《MySQL模糊查询用法大全（正则、通配符、内置函数等）》
2.使用FullText全文索引，用match against 检索
3-数据量较大的情况，建议引用ElasticSearch、solr，亿级数据量检索速度秒级
4-当表数据量较少（几千条儿那种），别整花里胡哨的，直接用like ‘%xx%’。

2-【避免2】避免使用in 和not in，会导致引擎走全表扫描

（1）用between替换in

SELECT * FROM t WHERE id IN (2,3)
1

【优化方案2】如果是连续数值，可以用between代替（用or也会失效）

SELECT * FROM t WHERE id BETWEEN 2 AND 3
1

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

-- 不走索引
select * from A where A.id in (select id from B);
-- 走索引
select * from A where exists (select * from B where B.id = A.id);
1
2
3
4

IN适合主表大子表小，EXIST适合主表小子表大。由于查询优化器的不断升级，很多场景这两者性能差不多一样了。

（3）用join替换in

select id from orders where user_id in (select id from user where level = 'VIP');
1

select o.id from orders o left join user u on o.user_id = u.id where u.level = 'VIP';
1

3-【避免3】避免使用or，会导致数据库引擎放弃索引进行全表扫描

SELECT * FROM t WHERE id = 1 OR id = 3
1

【优化方案3】可以用union代替or

SELECT * FROM t WHERE id = 1
   UNION
SELECT * FROM t WHERE id = 3
1
2
3

4-【避免4】避免进行null值的判断，会导致数据库引擎放弃索引进行全表扫描

SELECT * FROM t WHERE score IS NULL
1

【优化方案4】可以给字段添加默认值0，对0值进行判断

SELECT * FROM t WHERE score = 0
1

5-【避免5】避免在where条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描

SELECT * FROM T WHERE score/10 = 9
1

【优化方案5】可以将表达式、函数操作移动到等号右侧

SELECT * FROM T WHERE score = 10*9
1

6-【避免6】避免查询条件用 <> 或者 !=

使用索引列作为条件进行查询时，需要避免使用<>或者!=等判断条件。如确实业务需要，使用到不等于符号，需要在重新评估索引建立，避免在此字段上建立索引，改由查询条件中其他索引字段代替。

7-【避免7】避免违背最佳左前缀原则，会导致后续的索引失效

复合（联合）索引包含key_part1，key_part2，key_part3三列，如果开头的索引或者中间的索引顺序断掉，会导致断掉的索引后面的索引全部失效

select col1 from table where key_part2=1 and key_part3=2
1

8-【避免8】避免使用隐式“类型转换”造成不使用索引（传varchar转int可以，传int转varchar不可以）

如下SQL语句由于索引对列类型为varchar，但给定的值为数值，涉及隐式类型转换，造成不能正确走索引。

select col1 from table where col_varchar=123; 
1

对于Int类型的字段，传varchar类型的值是可以走索引，MySQL内部自动做了隐式类型转换；相反对于varchar类型字段传入Int值是无法走索引的，应该做到对应的字段类型传对应的值总是对的。

#user_id是bigint类型，传入varchar值发生了隐式类型转换，可以走索引。
select id, name , phone, address, device_no from users where user_id = '23126';
#card_no是varchar(20)，传入int值是无法走索引
select id, name , phone, address, device_no from users where card_no = 2312612121;
1
2
3
4

【优化方案8】实在不行就放到后端代码里进行处理

9-【避免9】避免 order by 条件与 where 中条件不一致，否则 order by 不会利用索引进行排序

-- 不走age索引
SELECT * FROM t order by age;
1
2

【优化方案9】保持order by条件与where条件一致

-- 走age索引
SELECT * FROM t where age > 0 order by age;
1
2

对于上面的语句，数据库的处理顺序是：

• 第一步：根据where条件和统计信息生成执行计划，得到数据。
• 第二步：将得到的数据排序。当执行处理数据（order by）时，数据库会先查看第一步的执行计划，看order by 的字段是否在执行计划中利用了索引。如果是，则可以利用索引顺序而直接取得已经排好序的数据。如果不是，则重新进行排序操作。
• 第三步：返回排序后的数据。

当order by 中的字段出现在where条件中时，才会利用索引而不再二次排序，更准确的说，order by 中的字段在执行计划中利用了索引时，不用排序操作。

这个结论不仅对order by有效，对其他需要排序的操作也有效。比如group by 、union 、distinct等。

10-【避免10】避免在查询条件上进行列运算

（1）案例一

select id from order where date_format(create_time，'%Y-%m-%d') = '2019-07-01';
1

date_format函数会导致这个查询无法使用索引，改写后：

select id from order where create_time between '2019-07-01 00:00:00' and '2019-07-01 23:59:59';
1

【5】写规范的sql

（1）SELECT语句其他优化

1-【避免10】避免select *

使用select * 取出全部列，会让优化器无法完成索引覆盖扫描这类优化，会影响优化器对执行计划的选择，也会增加网络带宽消耗，更会带来额外的I/O,内存和CPU消耗。

建议提出业务实际需要的列数，将指定列名以取代select *。具体详情见《为什么大家都说SELECT * 效率低》：

2-【避免11】避免出现不确定结果的函数

特定针对主从复制这类业务场景。由于原理上从库复制的是主库执行的语句，使用如now()、rand()、sysdate()、current_user()等不确定结果的函数很容易导致主库与从库相应的数据不一致。另外不确定值的函数,产生的SQL语句无法利用query cache。

3-【避免12】（注意）多表关联查询时，小表在前，大表在后。（并且索引加在大表上时效果更好）

在MySQL中，执行 from 后的表关联查询是从左往右执行的（Oracle相反），第一张表会涉及到全表扫描，所以将小表放在前面，先扫小表，扫描快效率较高，在扫描后面的大表，或许只扫描大表的前100行就符合返回条件并return了。
【优化方案12】关联查询时如何加索引

3-【避免12】避免3表以上的join查询

4-【避免13】避免使用HAVING字句，用where语句替换

避免使用HAVING字句，因为HAVING只会在检索出所有记录之后才对结果集进行过滤，而where则是在聚合前刷选记录，如果能通过where字句限制记录的数目，那就能减少这方面的开销。HAVING中的条件一般用于聚合函数的过滤，除此之外，应该将条件写在where字句中。

where和having的区别：where后面不能使用组函数

5-【避免14】避免把过滤数据多的条件往后放

调整Where字句中的连接顺序，MySQL采用从左往右，自上而下的顺序解析where子句。根据这个原理，应将过滤数据多的条件往前放，最快速度缩小结果集。

6-【避免15】避免在查询中出现text类型的字段

如果select出现text类型的字段，就会消耗大量的网络和IO带宽，由于返回的内容过大超过max_allowed_packet设置会导致程序报错，需要评估谨慎使用。

#表request_log的中content是text类型。
select user_id, content, status, url, type from request_log where user_id = 32121;
1
2

7-【避免16】避免出现子查询

由于MySQL的基于成本的优化器CBO对子查询的处理能力比较弱，不建议使用子查询，可以改写成Inner Join。子查询构成的虚表是没有任何索引的。

select b.member_id,b.member_type, a.create_time,a.device_model from member_operation_log a inner join (select member_id,member_type from member_base_info where `status` = 1
and create_time between '2020-10-01 00:00:00' and '2020-10-30 00:00:00') as b on a.member_id = b.member_id;
1
2

（2）优化LIMIT分页

在系统中需要分页的操作通常会使用limit加上偏移量的方法实现，同时加上合适的order by 子句。如果有对应的索引，通常效率会不错，否则MySQL需要做大量的文件排序操作。

一个非常令人头疼问题就是当偏移量非常大的时候，例如可能是limit 10000,20这样的查询，这是mysql需要查询10020条然后只返回最后20条，前面的10000条记录都将被舍弃，这样的代价很高。

优化此类查询的一个最简单的方法是尽可能的使用索引覆盖扫描，而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列。对于偏移量很大的时候这样做的效率会得到很大提升。

对于下面的查询：

select id,title from collect limit 90000,10;
1

该语句存在的最大问题在于limit M,N中偏移量M太大（我们暂不考虑筛选字段上要不要添加索引的影响），导致每次查询都要先从整个表中找到满足条件 的前M条记录，之后舍弃这M条记录并从第M+1条记录开始再依次找到N条满足条件的记录。如果表非常大，且筛选字段没有合适的索引，且M特别大那么这样的代价是非常高的。 试想，如我们下一次的查询能从前一次查询结束后标记的位置开始查找，找到满足条件的100条记录，并记下下一次查询应该开始的位置，以便于下一次查询能直接从该位置 开始，这样就不必每次查询都先从整个表中先找到满足条件的前M条记录，舍弃，在从M+1开始再找到100条满足条件的记录了。

（1）方法一：先查询出主键id值

select id,title from collect where id>=(select id from collect order by id limit 90000,1) limit 10;
1

原理：先查询出90000条数据对应的主键id的值，然后直接通过该id的值直接查询该id后面的数据。

【6】适当的时机使用mysql集群

《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB，才推荐分库分表。性能由综合因素决定，抛开业务复杂度，影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考，并非铁律。

分库分表是个周期长而风险高的大活儿，应该尽可能在当前结构上优化，比如升级硬件、迁移历史数据等等，实在没辙了再分。

（二）select语句执行顺序

FROM
<表名> # 选取表，将多个表数据通过笛卡尔积变成一个表。
ON
<筛选条件> # 对笛卡尔积的虚表进行筛选
JOIN <join, left join, right join…>
<join表> # 指定join，用于添加数据到on之后的虚表中，例如left join会将左表的剩余数据添加到虚表中
WHERE
<where条件> # 对上述虚表进行筛选
GROUP BY
<分组条件> # 分组
<SUM()等聚合函数> # 用于having子句进行判断，在书写上这类聚合函数是写在having判断里面的
HAVING
<分组筛选> # 对分组后的结果进行聚合筛选
SELECT
<返回数据列表> # 返回的单列必须在group by子句中，聚合函数除外
DISTINCT
#数据除重
ORDER BY
<排序条件> # 排序
LIMIT
<行数限制>

（三）sql优化策略实例

【1】select查询添加索引实例（需要继续补充）

（1）【优化案例1】多表关联查询

select sql_no_cache  * from user left join article on(user.id = article.user_id)
where  user.name like 'user_4%';
1
2

没有使用缓存，user表的id是主键，article表除主键外没有任何索引，这种情况下，百万级数据查询情况如下

sql> select sql_no_cache  * from user left join article on(user.id = article.user_id)
     where  user.name like 'user_4%'
[2020-05-17 13:24:45] 500 rows retrieved starting from 1 in 4 s 681 ms (execution: 1 s 312 ms, fetching: 3 s 369 ms)
1
2
3

【添加索引】
1-重点在on后面和where后面添加索引，那就是考虑给article表的user_id和user表的name加个索引（user表的id已经是主键了，默认有索引）
2-记得小表放前面，大表放后面，使用left join

CREATE INDEX user_id ON article (user_id);
1

执行效果

sql> select sql_no_cache  * from user left join article on(user.id = article.user_id)
     where  user.name like 'user_4%'
[2020-05-17 13:27:22] 500 rows retrieved starting from 1 in 142 ms (execution: 112 ms, fetching: 30 ms)
1
2
3

【关联查询总结】
1-在被驱动表上创建索引才会生效
当使用left join时，左表是驱动表（小表），右表是被驱动表（大表）。在sql优化中，永远是以小表驱动大表。驱动表有索引不会使用到索引，驱动表无论如何都会被全表扫描，被驱动表建立索引会使用到索引，所以关联查询的时候主要给【被驱动表/右表/大表】创建索引。之所以使用小表驱动大表是因为可以缩小结果集，如果两个表数量差不多则都可以作为驱动表。

select * from A a（驱动表/小表） left join B b（被驱动表/大表） on a.code=b.code
1

2-子查询不要放在被驱动表，因为子查询是虚表，没法创建索引，而索引只有在右表被驱动表上才有效。

SELECT ed.name '人物',c.name '掌门' 
FROM (SELECT e.name,d.ceo from t_emp e LEFT JOIN t_dept d on e.deptid=d.id) ed 
LEFT JOIN t_emp c on ed.ceo= c.id;
1
2
3

能够直接多表关联的尽量直接关联，不用子查询

3-关联查询join的时候需要distinct ，没有索引的话distinct消耗性能较大，所以在使用关联查询的时候一定注意添加索引。

（2）【优化案例2】单表查询添加索引

SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
1

【添加索引】
1-where后面的数据category_id和comments，但是因为comments字段后面跟的是不等号，会导致索引失效，所以排除掉
2-order by后面的字段views

所以创建一个category_id和views的联合索引

create index idx_article_cv on article(category_id,views);
1

（3）【优化案例3】order by添加索引

使用where子句与order by子句条件列组合满足索引最左前列
where子句中如果出现索引的范围查询（就是explain出现range）会导致order by索引失效

（4）【优化案例4】子查询转换成join方式

【查询目的】 查找语文考100分的考生
Student几十条数据，SC几万条数据
【sql版本一】

select s.* from Student s where s.s_id in (select s_id from SC sc where sc.c_id = 0 and sc.score = 100 )
1

效果非常的慢，主要是因为没有索引，在添加索引之前分析语句的格式
分析1：使用了范围查找in，
分析2：使用了子查询是虚表会导致索引失效

【sql版本二】
给子查询中的c_id和score添加索引（也可以是联合索引）

CREATE index sc_c_id_index on SC(c_id);
CREATE index sc_score_index on SC(score);
1
2

再次执行，缩短大表select s_id from SC sc where sc.c_id = 0 and sc.score = 100的查询速度

【sql版本三】
改用关联查询
清空之前的索引
给s_id创建索引，发现效率反而慢了，因为s_id添加索引后会先走on关联语句，然后再走where过滤语句
在where过滤之前就进行关联，肯定数据更多
应该先where过滤，再进行关联join，这样数据会少很多

SELECT s.* from Student s INNER JOIN SC sc on sc.s_id = s.s_id where sc.c_id=0 and sc.score=100
1

【sql版本四】
上面的索引s_id不变
添加索引

CREATE index sc_c_id_index on SC(c_id);
CREATE index sc_score_index on SC(score);
1
2

按照从左往右的执行顺序
1-实现子查询的过滤：SELECT * FROM SC sc WHERE sc.c_id = 0 AND sc.score = 100
2-关联join

SELECT s.* FROM (SELECT * FROM SC sc WHERE sc.c_id = 0 AND sc.score = 100 ) t INNER JOIN Student s ON t.s_id = s.s_id
1

【sql版本五】
保持上面的两个索引不变

CREATE index sc_c_id_index on SC(c_id);
CREATE index sc_score_index on SC(score);

SELECT s.* from Student s INNER JOIN SC sc on sc.s_id = s.s_id where sc.c_id=0 and sc.score=100
1
2
3
4

【总结】
1-mysql嵌套子查询效率确实比较低， 可以将其优化成连接查询
2-学会分析sql执行计划，mysql会对sql进行优化，所以分析执行计划很重要
3-内连接查询的时候，不管谁是左表右表，执行结果都一样。因为mysql会自动把小结果集的表选为驱动表（ 驱动表无论如何都会被全表扫描 ），大结果集的表选为被驱动表，被驱动表上的索引才生效。所以一般都是先执行where过滤，用到大表中的索引，然后再把小表和过滤后的大表关联到一起
4-简单来说就是小表驱动大表，大表索引过滤