浅入MySQL优化
前言
抛开过程谈结果的都是傻叉领导。。
在下不会做这种事，先来简单看一遍MySQL的运行机制
①、建立连接
通过客户端/服务器通信协议与MySQL建立连
接。MySQL 客户端与服务端的通信方式是 “ 半双工 ”。对于每一个 MySQL 的连接，时刻都有一个
线程状态来标识这个连接正在做什么。
你还记得锁表的时候会去查show processlist;吗？他能查到当前用户正在运行的线程信息。线程状态来自于此
②、查询缓存
如果开启了查询缓存且在查询缓存过程中查询到完全相同的SQL语句，则将查询结果直接返回给客户端；如果没有开启查询缓存或者没有查询到完全相同的 SQL 语句则会由解析器进行语法语义解析，并生成“解析树”
注意：
-
缓存Select查询的结果和SQL语句
-
执行Select查询时，先查询缓存，判断是否存在可用的记录集，要求是否完全相同（包括参数值），这样才会匹配缓存数据命中。
-
即使开启查询缓存，以下SQL也不能缓存
1 查询语句使用SQL_NO_CACHE
2 查询的结果大于query_cache_limit设置
3 查询中有一些不确定的参数，比如now() -
show variables like ‘%query_cache%’; //查看查询缓存是否启用，空间大小，限制等
-
show status like ‘Qcache%’; //查看更详细的缓存参数，可用缓存空间，缓存块，缓存多少等
③、解析器
将客户端发送的sql进行语法解析，生成解析树，预处理器根据一些MySQL规则检查合法性，解析别名如果没有歧义生成新的解析树
④、查询优化器
根据“解析树”生成最优的执行计划。MySQL使用很多优化策略生成最
优的执行计划，可以分为两类：静态优化（编译时优化）、动态优化（运行时优化）。
举例子：
- 等价变换策略
- 5=5 and a>5 改成 a > 5
- a < b and a=5 改成b>5 and a=5
- 基于联合索引，调整条件位置等
- 优化count、min、max等函数
- InnoDB引擎min函数只需要找索引最左边
- InnoDB引擎max函数只需要找索引最右边
- MyISAM引擎count(*)，不需要计算，直接返回
- 提前终止查询
- 使用了limit查询，获取limit所需的数据，就不在继续遍历后面数据
- in的优化
- MySQL对in查询，会先进行排序，再采用二分法查找数据。比如where id in (2,1,3)，变成 in (1,2,3)
⑤、查询执行引擎
查询执行引擎负责执行 SQL 语句，此时查询执行引擎会根据 SQL 语句中表的存储引擎类型，以
及对应的API接口与底层存储引擎缓存或者物理文件的交互，得到查询结果并返回给客户端。若开
启用查询缓存，这时会将SQL 语句和结果完整地保存到查询缓存（Cache&Buffffer）中，以后若有
相同的 SQL 语句执行则直接返回结果。
浅入性能优化
优化维度有四个：
1、硬件升级
2、系统配置
3、表结构设计
4、sql语句和索引
优化选择：
- 优化成本：硬件升级>系统配置>表结构设计>SQL语句及索引。
- 优化效果：硬件升级<系统配置<表结构设计<SQL语句及索引。
一、硬件升级
这个就不用我多解释了吧，氪金、氪金还是tmd氪金！，只要氪金。80C、640G让你写hello world，那真是盖了帽了我的老baby。或者氪金集群，主主主主主从从从从从从从从从从从从从从从从从从从！到这种，某从节点：“请求都打不到小爷身上”的程度。
当然，以上是笑话。在能力范围内尽量对硬件进行升级优化，是对MySQL性能提升有很大帮助的。
二、系统配置优化
【2.1】保证从内存中读取数据
MySQL会在内存中保存一定的数据，通过LRU算法（自己百度写个双链表就知道是什么类型的东西了）将不常访问的数据保存在硬盘文件中。
尽可能的扩大内存中的数据量，将数据保存在内存中，从内存中读取数据，可以提升MySQL性能。
扩大innodb_buffer_pool_size，能够全然从内存中读取数据。最大限度降低磁盘操作。
确定innodb_buffer_pool_size 足够大的方法：
mysql> show global status like 'innodb_buffer_pool_pages_%';
+----------------------------------+-------+
| Variable_name | Value |
+----------------------------------+-------+
| Innodb_buffer_pool_pages_data | 8190 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 12646 |
| Innodb_buffer_pool_pages_free | 0 | 0 表示已经被用光
| Innodb_buffer_pool_pages_misc | 1 |
| Innodb_buffer_pool_pages_total | 8191 |
+----------------------------------+-------+
innodb_buffer_pool_size默认为128M，理论上可以扩大到内存的3/4或4/5。
修改 my.cnf
innodb_buffer_pool_size = 750M
如果是专用的MySQL Server可以禁用SWAP
#查看swap
cat /proc/swaps
Filename Type Size Used Priority
/dev/sda2 partition 1048572 0 -1
#关闭所有交换设备和文件.
swapoff -a
【2.2】 数据预热
默认情况，仅仅有某条数据被读取一次，才会缓存在 innodb_buffer_pool。
所以，数据库刚刚启动，须要进行数据预热，将磁盘上的全部数据缓存到内存中。
数据预热能够提高读取速度。
1、对于InnoDB数据库，进行数据预热的脚本是:
SELECT DISTINCT
CONCAT('SELECT ',ndxcollist,' FROM ',db,'.',tb,
' ORDER BY ',ndxcollist,';') SelectQueryToLoadCache
FROM
(
SELECT
engine,table_schema db,table_name tb,
index_name,GROUP_CONCAT(column_name ORDER BY seq_in_index) ndxcollist
FROM
(
SELECT
B.engine,A.table_schema,A.table_name,
A.index_name,A.column_name,A.seq_in_index
FROM
information_schema.statistics A INNER JOIN
(
SELECT engine,table_schema,table_name
FROM information_schema.tables WHERE
engine='InnoDB'
) B USING (table_schema,table_name)
WHERE B.table_schema NOT IN ('information_schema','mysql')
ORDER BY table_schema,table_name,index_name,seq_in_index
) A
GROUP BY table_schema,table_name,index_name
) AA
ORDER BY db,tb;
注意：
查阅资料发现在mysql5.7.5及以上版本实现了对功能依赖的检测。默认启用了ONLY_FULL_GROUP_BY SQL模式，要通过命令关闭ONLY_FULL_GROUP_BY SQL模式
将该脚本保存为：loadtomem.sql
2、执行命令：
mysql -uroot -proot -AN < /root/loadtomem.sql > /root/loadtomem.sql
3、在需要数据预热时，比如重启数据库
执行命令：
mysql -uroot < /root/loadtomem.sql > /dev/null 2>&1
【2.3】 降低磁盘写入次数
- 增大redolog，减少落盘次数
- innodb_log_file_size 设置为 0.25 * innodb_buffer_pool_size
- 通用查询日志、慢查询日志可以不开 ，bin-log开
- 生产中不开通用查询日志，遇到性能问题开慢查询日志
- 写redolog策略 innodb_flush_log_at_trx_commit设置为0或2
- 如果不涉及非常高的安全性 (金融系统)，或者基础架构足够安全，或者事务都非常小，都能够用 0或者 2 来减少磁盘操作。
innodb_flush_log_at_trx_commit知识储备来了：
值为0 : 提交事务的时候，不立即把 redo log buffer 里的数据刷入磁盘文件的，而是依靠 InnoDB 的主线程每秒执行一次刷新到磁盘。此时可能你提交事务了，结果 mysql 宕机了，然后此时内存里的数据全部丢失。
值为1 : 提交事务的时候，就必须把 redo log 从内存刷入到磁盘文件里去，只要事务提交成功，那么 redo log 就必然在磁盘里了。注意，因为操作系统的“延迟写”特性，此时的刷入只是写到了操作系统的缓冲区中，因此执行同步操作才能保证一定持久化到了硬盘中。
值为2 : 提交事务的时候，把 redo 日志写入磁盘文件对应的 os cache 缓存里去，而不是直接进入磁盘文件，可能 1 秒后才会把 os cache 里的数据写入到磁盘文件里去。可以看到，只有1才能真正地保证事务的持久性，但是由于刷新操作 fsync() 是阻塞的，直到完成后才返回，我们知道写磁盘的速度是很慢的，因此 MySQL 的性能会明显地下降。如果不在乎事务丢失，0和2能获得更高的性能。
【2.4】提高磁盘读写性能
微氪，使用ssd或者更快的磁盘（经常听我已经读研的同学安利以后会出现超高速机械硬盘）
三、表结构设计优化
【3.1】 设计中间表
设计中间表，一般针对于统计分析功能，或者实时性不高的需求（OLTP、OLAP）
【3.2】 设计冗余字段
为减少关联查询，创建合理的冗余字段（创建冗余字段还需要注意数据一致性问题）
【3.3】拆表
对于字段太多的大表，考虑拆表（比如一个表有100多个字段）
对于表中经常不被使用的字段或者存储数据比较多的字段，考虑拆表
【3.4】 主键优化
每张表建议都要有一个主键（主键索引），而且主键类型最好是int类型，建议自增主键（不考虑分布
式系统的情况下 雪花算法）。
【3.5】字段的设计
数据库中的表越小，在它上面执行的查询也就会越快。
因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。
尽量把字段设置为NOTNULL，这样在将来执行查询的时候，数据库不用去比较NULL值。
对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，
ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。这样，我
们又可以提高数据库的性能。
能用数字的用数值类型
sex 1 0
四、SQL语句及索引优化
设计一个表：tbiguser
create table tbiguser(
id int primary key auto_increment,
nickname varchar(255),
loginname varchar(255),
age int ,
sex char(1),
status int,
address varchar(255)
);
向该表中写入很多数据，用存储过程方式，这里用navicat就能执行，试了下dbeaver好像不能执行。可以自行使用命令方式执行。
delimiter ;; #一定要加上，否则不能识别程序
drop procedure if exists test_insert; #判断删除已存在的程序
CREATE PROCEDURE test_insert () #创建程序
begin
declare i INT default 2550;
while i < 10000000
do
insert into tbiguser
values(null,concat('sh',i),concat('shenhang',i),23,'1',1,'chengdu'); set i=i+1;
end while;
commit;
end;;
call test_insert();
ps:在navicat上跑还是非常慢的，建议大家使用命令行方式导入大量测试数据。
本人机器性能不佳
跑了十多分钟
只插入了一万多条数据，不过已经够进行演示了。
【4.1】EXPLAIN查看索引使用情况
使用explain查看有问题的sql执行计划，重点查看索引使用该情况。
explain select * from tbiguser where loginname='shenhang1' and nickname='sh1';
重点说明：
type所显示的是查询使用了哪种类型，从最好到最差依次是：
system > const > eq_ref > ref > range > index > all
-
system 表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计
-
const 表示通过索引一次就找到了，const用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。
-
eq_ref 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
-
ref 非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
-
range 只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在你的where语句中出现between、< 、>、in等的查询，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。
-
index Full Index Scan，Index与All区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘读取的）
-
all Full Table Scan 将遍历全表以找到匹配的行
常见的索引建立：
- 在where从句，group by从句，order by从句，on从句中出现的列建立索引
- 组合索引(最左前缀)、覆盖索引(不回表)、索引下推
知识储备补充来了：
大部分索引我相信读者都知道了，我就光说明下索引下推吧：
- 记住这个规则：组合索引满足最左匹配，但是遇到非等值判断时匹配停止
举例子：
有一张用户表(user)，其中主要几个字段有：id、name、age、address。建立联合索引（name，age）。
有一条查询语句：
SELECT * from user where name like '游振%' and age=18
这是不是遇到非等值判断了呢。
所以在MySQL5.6版本之前，是没有索引下推这个优化的，所以会忽略age这个字段，只通过这个组合索引叶子节点的第一个点，name进行定位。
MySQL5.6版本和之后，增加了索引下推这个优化，存储引擎innoDB就不会忽略age字段，去完全利用了我们的组合索引。
【4.2】SQL语句中IN包含的值不应过多
MySQL对于IN做了相应的优化，即将IN中的常量全部存储在一个数组里面，而且这个数组是排好序
的。但是如果数值较多，产生的消耗也是比较大的。
【4.3】SELECT语句务必指明字段名称
SELECT * 增加很多不必要的消耗（CPU、IO、内存、网络带宽）；减少了使用覆盖索引的可能性；当
表结构发生改变时，前端也需要更新。所以要求直接在select后面接上字段名。
【4.4】当只需要一条数据的时候，使用 limit 1
limit 是可以停止全表扫描的
【4.5】排序字段加索引
【4.6】如果限制条件中其他字段没有索引，尽量少用or
or两边的字段中，如果有一个不是索引字段，会造成该查询不走索引的情况。
【4.7】尽量用union all代替union
union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，
增加大量的CPU运算，加大资源消耗及延迟。当然，union all的前提条件是两个结果集没有重复数据。
【4.8】不使用ORDER BY RAND()
ORDER BY RAND() 不走索引
【4.9】区分in和exists、not in和not exists
区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为
驱动表，先被访问，如果是IN，那么先执行子查询。所以IN适合于外表大而内表小的情况；EXISTS适合
于外表小而内表大的情况。
关于not in和not exists，推荐使用not exists，不仅仅是效率问题，not in可能存在逻辑问题。
【4.10】使用合理的分页方式以提高分页的效率
分页使用 limit m,n 尽量让m 小
利用主键的定位，可以减小m的值
【4.11】分段查询
一些用户选择页面中，可能一些用户选择的范围过大，造成查询缓慢。主要的原因是扫描行数过多。这
个时候可以通过程序，分段进行查询，循环遍历，将结果合并处理进行展示。
【4.12】不建议使用%前缀模糊查询
例如LIKE“%name”或者LIKE“%name%”，这种查询会导致索引失效而进行全表扫描。但是可以使用LIKE
“name%”。
【4.13】避免在where子句中对字段进行表达式操作
如：
select user_id,user_project from user_base where age*2=36;
中对字段就行了算术运算，这会造成引擎放弃使用索引，建议改成：
select user_id,user_project from user_base where age=36/2;
【4.14】避免隐式类型转换
where子句中出现column字段的类型和传入的参数类型不一致的时候发生的类型转换，建议先确定
where中的参数类型。 where age='18’就完了芭比q了，不走索引了。
【4.15】对于联合索引来说，要遵守最左前缀法则
举列来说索引含有字段id、name、school，可以直接用id字段，也可以id、name这样的顺序，但是
name;school都无法使用这个索引。所以在创建联合索引的时候一定要注意索引字段顺序，常用的查询
字段放在最前面。
【4.16】必要时可以使用force index来强制查询走某个索引
有的时候MySQL优化器采取它认为合适的索引来检索SQL语句，但是可能它所采用的索引并不是我们想
要的。这时就可以采用forceindex来强制优化器使用我们制定的索引。
【4.17】注意范围查询语句
对于联合索引来说，如果存在范围查询，比如between、>、<等条件时，会造成后面的索引字段失效。（如果索引不下推的话）
【4.18】使用join优化
jojo,我不做人了！
LEFT JOIN A表为驱动表，INNER JOIN MySQL会自动找出那个数据少的表作用驱动表，RIGHT JOIN B
表为驱动表。
注意：
1）MySQL中没有full join，可以用以下方式来解决：
select * from A left join B on B.name = A.namewhere B.name is null union all
select * from B;
2）尽量使用inner join，避免left join：
参与联合查询的表至少为2张表，一般都存在大小之分。如果连接方式是inner join，在没有其他过滤条
件的情况下MySQL会自动选择小表作为驱动表，但是left join在驱动表的选择上遵循的是左边驱动右边
的原则，即left join左边的表名为驱动表。
3）合理利用索引：
被驱动表的索引字段作为on的限制字段。
4）利用小表去驱动大表：
从原理图能够直观的看出如果能够减少驱动表的话，减少嵌套循环中的循环次数，以减少 IO总量及CPU运算的次数。
优化总结：
- 开启慢查询日志，定位运行慢的SQL语句
- 利用explain执行计划，查看SQL执行情况
- 关注索引使用情况：type
- 关注Rows：行扫描
- 关注Extra：没有信息最好
- 加索引后，查看索引使用情况，index只是覆盖索引，并不算很好的使用索引
- 如果有关联尽量将索引用到eq_ref或ref级别
- 复杂SQL可以做成视图，视图在MySQL内部有优化，而且开发也比较友好
- 对于复杂的SQL要逐一分析，找到比较费时的SQL语句片段进行优化
注意：尽量增加知识储备。才能更加合理的优化sql。【某次遇到慢查询，xx同事决策：加索引啊！！！，我打开一看，出自十年前的百十来行复杂多条件not in 终极实验报告式老代码语句，后面了解了不是实时使用，推荐同事开个新调度刷进中间表再查询使用】
此文只是浅析，文中若有错误请读者指正！避免误人子弟。
扫一扫
1184
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
