MySQL查询优化（分析、索引、配置等）

数据库的优化包括两个方面，一是SQL语句的优化，二是数据库服务器和配置的优化。下面先讲查询语句的优化。

查询语句优化主要涉及两个方面：一些普遍遵循的原则和怎么对查询语句进行性能分析。

一、索引与性能分析

通过以下两条语句可以查看SQL性能报告，针对性地定位性能瓶颈。

-- 查看SQL性能报告
show profiles;
-- 查看指定query执行计划的详细报告（通过上一条语句获得query序号）
show profile for query 4;

MySQL执行计划就是在一条SELECT语句前加EXPLAIN关键词。

explain select * from `user` where id = 1;

可以得到如下结果：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	user	ALL	NULL	NULL	NULL	NULL	13

• type：联合查询使用的类型
• possible_keys：告诉你MySQL能使用哪些索引找到改行，如果没有，表明没有相关索引。这时候想提高性能可以看where条件子句，看看是否引用了索引字段或者适合创建索引。
• key：MySQL实际使用的键。
• Extra：
  1. Only index，意味着信息只能用索引树中的信息检索，这比扫描全表要快；
  2. where used，表示是使用了where限制，但是用索引还不够；
  3. impossible where，则表示通过收集到的统计信息判断出不可能存在的结果；
  4. Using filesort，表示包含orderby且无法使用索引进行派讯操作；
  5. using temporary，使用了临时表，常见于orderby和group by；
  6. 其他。。。

type显示的访问类型是较重要的指标，结果从好到坏依次是：system(系统表) > const(读常量) > eq_ref(最多一条匹配结果，通常是主键访问) > ref(被驱动表索引引擎) > fulltext(全文索引检索) > ref_or_null(带空值的索引查询) > index_merge(合并索引结果集) > unique_subquery(子查询中返回的字段是唯一组合或索引) > index_subquery(子查询返回的是索引，但非主键) > range(索引范围扫描) > index(全索引扫描) > ALL(全表扫描)，Extra中的第4或5项可能需要在后端逻辑中权衡一下是先过滤再排序还是先排序再过滤。

一般来说，保证查询至少达到range级，最好能达到ref级。

MySQL索引建立和使用原则：

• 合理设计和使用索引，在关键字段上建立索引
• 不在结果单一的列上建索引，如性别字段
• 索引并非越多越好，维护索引需要成本，尽量在5个以下，合理利用部分索引和联合索引
• 索引字段的结果集最好分布均匀

二、SQL不走索引的情况

1. where子句参与了计算或者使用了函数（包括正则函数）

SELECT `username` FROM `user` WHERE `age`+10 = 30;
SELECT `username` FROM `user` WHERE LEFT(`birthday`, 4) < 1990;

2. LIKE匹配前面有%

SELECT * FROM `user` WHERE `username` LIKE "%bruce%";

3. 存在隐式转换

假设字段id(int)和username(varchar)均有索引，int型字段隐式转换不影响索引，其他类型字段隐式转换会影响索引。

-- 走索引
SELECT * FROM `user` WHERE `id` = 111;
-- 走索引
SELECT * FROM `user` WHERE `id` = '111';
-- 走索引
SELECT * FROM `user` WHERE `username` = '111';
-- 不走索引
SELECT * FROM `user` WHERE `username` = 111;

4. where子句有OR

where子句有OR时不走索引，可以用union（有distinct效果）或者union all来优化SQL。

SELECT * FROM `user` WHERE `username` LIKE "bruce%" OR  `username` LIKE "wu%";

5. where子句中使用复合索引没有遵循最左原则

譬如有表t，其中对abc三个字段建立了复合索引，根据B+树搜索顺序或者最左原则相当于创建了a、ab、ac、abc三个索引，查询的时候mysql会一直向右匹配直到遇到了>、<、between、like等。

-- 走索引
SELECT * FROM t WHERE a = 'test1';
-- 走索引
SELECT * FROM t WHERE a = 'test1' AND b = 'test2';
-- 走索引
SELECT * FROM t WHERE a = 'test1' AND b = 'test2' AND c = 'test3';
-- 部分走索引，匹配到b的时候停止匹配，c用不到索引，这种查询多的话索引应该改成acb，可以全部走索引
SELECT * FROM t WHERE a = 'test1' AND b LIKE 'test%' AND c = 'test3';
-- 走索引
SELECT * FROM t WHERE a = 'test1' AND c = 'test3';
-- 不走索引
SELECT * FROM t WHERE b = 'test2' AND c = 'test3';
-- 不走索引
SELECT * FROM t WHERE b = 'test2';
-- 不走索引
SELECT * FROM t WHERE c = 'test3';

6. 在where子句中IN使用了子查询

假设有test_table1表对pay_id建立了索引，如果IN查询是直接的值，则可以正常使用索引：

select * from test_table1 where pay_id in(63999,78000,98877,123000,140000);

如果IN里使用子查询，则外层可能要进行全表扫描：

select * from test_table1 
where pay_id in (
    select pay_id from test_table1 
    where pay_time >= "2022-06-01 00:00:00"
    and pay_time <= "2022-07-03 12:59:59"
    group by pay_id 
    having count(pay_id) > 1
);

这里建议尽量将IN子查询语句改成join查询，这样外层就能走索引：

select t1.* from test_table1 t1, (
    select pay_id from test_table1 
    where pay_time >= "2022-06-01 00:00:00"
    and pay_time <= "2022-07-03 12:59:59"
    group by pay_id
    having count(pay_id) > 1
) t2
where t1.pay_id = t2.pay_id;

7. mysql估计使用全表扫描比使用索引快

SELECT * FROM `user`;

三、服务器和配置优化

MySQL中存在多种存储引擎，每种引擎都有各自的特色，对比如下。

-	MyISAM	InnoDB	Memory
用途	快读	完整的事务支持	内存数据
锁	表锁	多种隔离界别的行锁、表锁	表锁
持久性	基于表恢复	基于日志的恢复	无磁盘I/O，不可恢复
事务特性	不支持	支持	不支持
支持索引类型	B-tree/FullText/R-tree	Hash/B-tree	Hash/B-tree

1. 合理选择引擎

一般来说理想的读写比（R/W）为100:1，当读写比达到10:1的时候就认为是以写为主的数据库了，一般这个值在30:1左右。选择引擎的原则如下：
1）选择MyISAM

• R/W > 100:1且update较少；
• 并发不高，不需要事务；
• 数据量小；
• 硬件资源有限。

2）选择InnoDB

• R/W较小，频繁更新大字段；
• 数据量超过1000万，并发高；
• 安全性和可用性要求高。

3）选择Memory

• 有足够的内存；
• 对数据一致性要求不高，如在线人数和Session等；
• 需要定期归档的数据。

2. MySQL服务器调整和优化措施

• 1. 关闭不必要的二进制日志和慢查询日志，仅在内存足够或需要调试的时候打开。

-- 查看是否开启慢查询日志
show variables like '%slow%';
-- 查看慢查询条数
show global status like '%slow%';

• 2. 适度增加Query Cache。
• 3. 增加MySQL允许的最大连接数。

-- 查看MySQL允许的最大连接数;
show variables like 'max_connections';

• 4. 对MyISAM表增加key_buffer_size，这需要根据key_cache命中率计算：

show variables like 'key_read%';

计算公式为：key_cache_miss_rate = Key_reads / Key_read_requests * 100%。
当key_cache_miss_rate值大于1%时就需要适当增加key_buffer_size了。

• 5. 从表中删除大量行后，可运行OPTIMIZE TABLE TableName进行碎片整理。