DBA整理的万字详解MySQL性能优化，值得收藏！

MySQL逻辑架构

MySQL查询过程

客户端/服务端通信协议

查询缓存

• 任何的查询语句在开始之前都必须经过检查，即使这条SQL语句永远不会命中缓存
• 如果查询结果可以被缓存，那么执行完成后，会将结果存入缓存，也会带来额外的系统消耗

• 用多个小表代替一个大表，注意不要过度设计
• 批量插入代替循环单条插入
• 合理控制缓存空间大小，一般来说其大小设置为几十兆比较合适
• 可以通过SQL_CACHE和SQL_NO_CACHE来控制某个查询语句是否需要进行缓存

语法解析和预处理

查询优化

mysql> select * from t_message limit 10;    
...省略结果集    
  
mysql> show status like 'last_query_cost';    
+-----------------+-------------+    
| Variable_name   | Value       |    
+-----------------+-------------+    
| Last_query_cost | 6391.799000 |    
+-----------------+-------------+    

• 重新定义表的关联顺序（多张表关联查询时，并不一定按照SQL中指定的顺序进行，但有一些技巧可以指定关联顺序）
• 优化MIN()和MAX()函数（找某列的最小值，如果该列有索引，只需要查找B+Tree索引最左端，反之则可以找到最大值，具体原理见下文）
• 提前终止查询（比如：使用Limit时，查找到满足数量的结果集后会立即终止查询）
• 优化排序（在老版本MySQL会使用两次传输排序，即先读取行指针和需要排序的字段在内存中对其排序，然后再根据排序结果去读取数据行，而新版本采用的是单次传输排序，也就是一次读取所有的数据行，然后根据给定的列排序。对于I/O密集型应用，效率会高很多）

查询执行引擎

返回结果给客户端

• 客户端向MySQL服务器发送一条查询请求
• 服务器首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段
• 服务器进行SQL解析、预处理、再由优化器生成对应的执行计划
• MySQL根据执行计划，调用存储引擎的API来执行查询
• 将结果返回给客户端，同时缓存查询结果

性能优化建议

Scheme设计与数据类型优化

• 通常来说把可为NULL的列改为NOT NULL不会对性能提升有多少帮助，只是如果计划在列上创建索引，就应该将该列设置为NOT NULL。
• 对整数类型指定宽度，比如INT(11)，没有任何卵用。INT使用16为存储空间，那么它的表示范围已经确定，所以INT(1)和INT(20)对于存储和计算是相同的。
• UNSIGNED表示不允许负值，大致可以使正数的上限提高一倍。比如TINYINT存储范围是通常来讲，没有太大的必要使用DECIMAL数据类型。即使是在需要存储财务数据时，仍然可以使用BIGINT。比如需要精确到万分之一，那么可以将数据乘以一百万然后使用TIMESTAMP使用4个字节存储空间，DATETIME使用8个字节存储空间。因而，TIMESTAMP只能表示1970 - 2038年，比DATETIME表示的范围小得多，而且TIMESTAMP的值因时区不同而不同。
• 大多数情况下没有使用枚举类型的必要，其中一个缺点是枚举的字符串列表是固定的，添加和删除字符串（枚举选项）必须使用ALTER TABLE（如果只只是在列表末尾追加元素，不需要重建表）。
• schema的列不要太多。原因是存储引擎的API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列，这个转换过程的代价是非常高的。如果列太多而实际使用的列又很少的话，有可能会导致CPU占用过高。
• 大表ALTER TABLE非常耗时，MySQL执行大部分修改表结果操作的方法是用新的结构创建一个张空表，从旧表中查出所有的数据插入新表，然后再删除旧表。尤其当内存不足而表又很大，而且还有很大索引的情况下，耗时更久。当然有一些奇淫技巧可以解决这个问题，有兴趣可自行查阅。

创建高性能索引

索引相关的数据结构和算法

页是计算机管理存储器的逻辑块，硬件及OS往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（许多OS中，页的大小通常为4K）。主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行。

高性能策略

CREATE TABLE People(    
    last_name varchar(50) not null,    
    first_name varchar(50) not null,    
    dob date not null,    
    gender enum(`m`,`f`) not null,    
    key(last_name,first_name,dob)    
);    

1、MySQL不会使用索引的情况：非独立的列

select * from where id + 1 = 5  

2、前缀索引

3、多列索引和索引顺序

select film_id,actor_id from film_actor where actor_id = 1 or film_id = 1 

select film_id,actor_id from film_actor where actor_id = 1    
union all   
select film_id,actor_id from film_actor where film_id = 1 and actor_id <> 1  

• 当出现多个索引做相交操作时（多个AND条件），通常来说一个包含所有相关列的索引要优于多个独立索引。
• 当出现多个索引做联合操作时（多个OR条件），对结果集的合并、排序等操作需要耗费大量的CPU和内存资源，特别是当其中的某些索引的选择性不高，需要返回合并大量数据时，查询成本更高。所以这种情况下还不如走全表扫描。

SELECT * FROM payment where staff_id = 2 and customer_id = 584  

select count(distinct staff_id)/count(*) as staff_id_selectivity,  
       count(distinct customer_id)/count(*) as customer_id_selectivity,  
       count(*) from payment  

select user_id from trade where user_group_id = 1 and trade_amount > 0  

4、避免多个范围条件

select user.* from user where login_time > '2017-04-01' and age between 18 and 30;  

5、覆盖索引

• 索引条目远小于数据行大小，如果只读取索引，极大减少数据访问量
• 索引是有按照列值顺序存储的，对于I/O密集型的范围查询要比随机从磁盘读取每一行数据的IO要少的多

6、使用索引扫描来排序

-- 最左列为常数，索引：(date,staff_id,customer_id)  
select  staff_id,customer_id from demo where date = '2015-06-01' ``order by staff_id,customer_id

7、冗余和重复索引

8、删除长期未使用的索引

特定类型查询优化

优化COUNT()查询

优化关联查询

• 确保ON和USING字句中的列上有索引。在创建索引的时候就要考虑到关联的顺序。当表A和表B用列c关联的时候，如果优化器关联的顺序是A、B，那么就不需要在A表的对应列上创建索引。没有用到的索引会带来额外的负担，一般来说，除非有其他理由，只需要在关联顺序中的第二张表的相应列上创建索引（具体原因下文分析）。
• 确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列，这样MySQL才有可能使用索引来优化。

SELECT A.xx,B.yy   
FROM A INNER JOIN B USING(c)  
WHERE A.xx IN (5,6)  

outer_iterator = SELECT A.xx,A.c FROM A WHERE A.xx IN (5,6);  
outer_row = outer_iterator.next;  
while(outer_row) {  
    inner_iterator = SELECT B.yy FROM B WHERE B.c = outer_row.c;  
    inner_row = inner_iterator.next;  
    while(inner_row) {  
        output[inner_row.yy,outer_row.xx];  
        inner_row = inner_iterator.next;  
    }  
    outer_row = outer_iterator.next;  
} 

优化LIMIT分页

SELECT film_id,description FROM film ORDER BY title LIMIT 50,5;  

SELECT film.film_id,film.description  
FROM film INNER JOIN (  
    SELECT film_id FROM film ORDER BY title LIMIT 50,5  
) AS tmp USING(film_id);  

SELECT id FROM t LIMIT 10000, 10;  

SELECT id FROM t WHERE id > 10000 LIMIT 10;  

优化UNION

结语

• 有非常多的程序员在分享时都会抛出这样一个观点：尽可能不要使用存储过程，存储过程非常不容易维护，也会增加使用成本，应该把业务逻辑放到客户端。既然客户端都能干这些事，那为什么还要存储过程？
• JOIN本身也挺方便的，直接查询就好了，为什么还需要视图呢？

参考资料

[1] 姜承尧 著；MySQL技术内幕-InnoDB存储引擎；机械工业出版社，2013
[2] Baron Scbwartz 等著；宁海元 周振兴等译；高性能MySQL（第三版）; 电子工业出版社， 2013
[3] 由 B-/B+树看 MySQL索引结构

作者：LanceToBigData
来源：cnblogs.com/zhangyinhua/p/7620964.html