MySQL 性能优化/常见问题

一、explain命令执行(列含义)

　　1. id：SELECT查询的标识符，每个SELECT都会分配一个唯一的标识符；

　　　　A. 当id相同时，sql的执行顺序是由上至下；

　　　　B. 当id不同时，id大的优先执行。

　　2. select_type：SELECT查询的类型

　　　　A. SIMPLE —— 表示此查询不包含 UNION 查询或子查询；

　　　　B. PRIMARY —— 表示此查询是最外层的查询；

　　　　C. SUBQUERY —— 子查询中的第一个 SELECT；

　　　　D. DERIVED —— 衍生，表示导出表的SELECT(FROM子句的子查询)；

　　　　E. UNION —— 表示此查询是 UNION 的第二或随后的查询。

　　3. table：查询所涉及的表或衍生的表

　　4. type：连接类型

　　　　A. 性能方面：ALL < index < range ~ indexmerge < ref < eq_ref < const < system；

　　　　B. 优化最少应该是range级别，最好达到ref最低级别；

　　5. possible_keys：表示此次查询中可能选用的索引；

　　6. key：表示此次查询中使用的索引；

　　7. key_len：表示使用索引的字节数，通过这个字段可以评估组合索引是否完全被使用；

　　8. ref：表示使用索引的列字段；

　　9. rows：结果集需要扫描读取的数据行数，是个估算值，原则上越少越好；

　　10. extra：表示额外的信息显示

　　　　A. using filesort —— 表示MySQL需额外的排序操作，不能通过索引顺序达到排序效果，这种是需要优化的地方；

　　　　B. using index —— 代表使用了索引覆盖扫描，往往说明性能不错；

　　　　C. using temporary —— 查询有使用临时表，一般表现为排序、分组和多表join，这种也是需要优化的地方；

　　　　D. using where —— 全表扫描并且where中针对该表的搜索条件；

　　　　E. using index condition —— 查找使用了索引，但是需要回表查询数据。

　　注意：通过以上可以得知表的读取顺序(id)、数据读取操作的操作类型(type)、哪些索引被实际使用(key)、表之间的引用(ref)、每张表有多少行被优化器查询(rows)。

可参考：MySQL 性能优化神器 Explain 使用分析

 

二、SQL调优

　　1. 优化insert语句

　　　　A. 单表多行数据一条条插入，改为使用多个值的一条insert语句一次性插入，注意bulk_insert_buffer_size默认值为8M；

INSERT INTO table_name values(1,2),(2,3),(3,4)

　　　　B. 在事务中进行数据插入，事务改为手动提交；

　　　　C. 批量数据有序插入，比如按主键递增；

　　　　D. 根据应用情况使用replace语句代替insert，使用ignore关键字忽略重复记录。

　　2. 优化order by语句

　　　　A. MySQL两种排序方式

　　　　　　filesort：通过对返回数据排序；

　　　　　　using index：通过有序索引顺序扫描直接返回有序数据的排序，这种操作效率高，因为不需要额外的排序；

　　　　B. 尽量减少额外的排序，通过索引直接返回有序数据，where条件和order by使用相同的索引，并且order by的顺序和索引顺序相同，并且order by字段都是升序或降序，否则会出现filesort；

　　　　C. 针对filesort有两种排序算法

　　　　　　二次扫描算法：该操作会导致大量随机IO；

　　　　　　一次扫描算法：排序时内存开销较大，因此排序效率高；

　　　　　　MySQL 4.1版本后到底选择哪种算法是根据变量max_length_for_sort_data的大小和query语句取出的字段总大小比较，如果前者大就采用一次扫描算法，因此可以适当提高sort_buffer_size和max_length_for_sort_data变量大小来增大排序区大小，从而达到提高排序效率。

　　可参考：order by语句优化

　　3. 优化group by语句

　　　　A. order by null：只想分组，不想排序，避免排序降低效率；

　　　　B. 创建索引提高效率，但先看group by创建临时表的情况，因为有临时表的存在会导致索引失效。

 

三、SQL优化总结

　　1. 索引列不要使用函数或者表达式，因为B+Tree中存的都是数据表中字段值；

　　2. 当select查询语句只需要使用一条记录时，需要带上LIMIT 1；

　　3. 不要直接使用SELECT *，需要什么字段就查询什么字段；

　　4. 每张表都设置ID字段；

　　5. 避免在where中使用!=操作符；

　　6. 针对要搜索的字段需要创建索引；

　　7. LIKE查询不要以%开头，否则不走索引，要使用最左前缀匹配；

　　8. 对于枚举类型的字段，建议使用enum，如：性别、类型等；

　　9. 选择合适的字段类型，选择标准是 尽可能小、尽可能定长、尽可能使用整数；

　　10. 字段设计尽可能使用not null；

　　11. 利用覆盖索引来优化SQL，避免回表查询。

 

四、性能压测工具

　　mysqlslap --no-defaults -h10.12.10.12 -P3306 –uroot -p123456 --create-schema=rhxy --query="SELECT iab.is_super from admin_base as iab where iab.user_id = 1" --concurrency=1300 --iterations=200

 

五、参数调优

　　1. 数据库连接数

　　　　A. 查看最大连接数：show variables like '%max_connections%'，最大连接数默认是151，其中一个是始终分配的管理员登录连接，最大可以达到16384，实际连接数是最大连接数85%左右较好，如果设置连接数超过1024，需要修改服务器文件描述符的上限;

　　　　B. 查看当前连接数：show full processlist，可以看到连接数、连接IP，用户，状态及时长等参数；

　　　　C. 查看客户端的连接数：show status like 'Threads%'；

　　　　　　Threads_cached：目前空闲的数据库连接数；

　　　　　　Threads_connected：当前数据库存活的数据库连接数；

　　　　　　Threads_created：MySQL运行至今，累计创建的连接数；

　　　　　　Threads_running：目前正在执行的数据库连接数。

 

六、常见问题

　　1. 信息包过大错误

　　　　A. max_allowed_packet参数作用是控制其通信缓冲区的最大长度；

　　　　B. 场景使用一：优化日志入库时，使数据批量插入，执行SQL中有longtext字段值导致整体数据过大，出现数据丢失问题，因此需要调大该值。