1.MySQL针对子查询select的优化
EXPLAIN SELECT * from user_workshop_role where role_id in
(SELECT role_id from user_role where user_id = 417849709665865730);
看下直接用=的执行计划
EXPLAIN SELECT * from user_workshop_role where role_id = 432772745738485760
5.7之前用 in 时就算有索引也会走全表扫描,有子查询的时候会先扫描外表
2. MySQL针对Union all的优化
在5.7以前union all会生成临时表,5.7之后结果直接返回而无需生成临时表
EXPLAIN
SELECT user_id,role_id FROM user_role
UNION ALL
SELECT user_id,role_id FROM user_workshop_role
3.transaction_isolation
mysql的事务隔离级别默认是repeatable-read,在5.7中通过 gap-lock(间隙锁)解决了幻读的问题
4. innodb_buffer_pool_size
调优参数计算方法:
val
=Innodb_buffer_pool_pages_data
/Innodb_buffer_pool_pages_total
100%
如果val>75%
则增大innodb_buffer_pool_size
,建议占用物理内存的75%
如果val<75%
,则减小innodb_buffer_pool_size
,建议值
Innodb_buffer_pool_pages_data
*Innodb_page_size
* 1.05/(10241024*1024)
show global STATUS like 'Innodb_buffer_pool_pages_data';
show global STATUS like 'Innodb_buffer_pool_pages_total';
show global STATUS like 'Innodb_page_size';
show global STATUS like 'innodb_buffer_pool_size';
set global innodb_buffer_pool_size = xxx kb
mysql在5.7版本后支持动态修改参数,也可以在my.cnf配置文件中修改