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)。
二、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字段值导致整体数据过大,出现数据丢失问题,因此需要调大该值。