Skip to main content

STech 数据库优化

获取发货员sql优化​

  • 出现问题：users表的 group索引 有时候没生效

    • 统计到一天没生效的次数

    image-20210608141759421

    • 未生效时 explain结果

    image-20210608141836222

    • 生效时 explain 结果

    image-20210608142027961

# 原始脚本
explain 
SELECT `u`.`id`, `u`.`name`, `ug`.`title` FROM `users` AS `u` INNER JOIN (select id,title,position from user_groups where group_type='system') AS `ug` ON u.group_id=ug.id LIMIT 0, 1000;

# 改进方案1：增加强制索引 代码改动小
explain 
SELECT `u`.`id`, `u`.`name`, `ug`.`title` FROM `users` AS `u` FORCE INDEX (`group`) INNER JOIN (select id,title,position FROM user_groups WHERE group_type='system') AS `ug` ON ug.id=u.group_id LIMIT 0, 1000;

# 改进方案2: 分开查询 先查system的 group id ｜ 最后查询 需要的数据
explain
SELECT `u`.id,`u`.name,`ug`.title FROM users u LEFT JOIN user_groups ug ON u.group_id = ug.id WHERE `u`.group_id in (SELECT id FROM user_groups WHERE group_type='system');

# 改进方案3: 分开查询 先查system的 group id ｜ 再查 用户id ｜ 最后查询 需要的数据
explain
SELECT `u`.id,`u`.name,`ug`.title FROM users u LEFT JOIN user_groups ug ON u.group_id = ug.id WHERE `u`.id in (SELECT id FROM users WHERE group_id in (SELECT id FROM user_groups WHERE group_type='system'));

# 改进方案4: 再上面的基础上 增加缓存

获取发货员sql优化​

  • 出现问题：users表的 group索引 有时候没生效

    • 统计到一天没生效的次数

    image-20210608141759421

    • 未生效时 explain结果

    image-20210608141836222

    • 生效时 explain 结果

    image-20210608142027961

# 原始脚本
explain 
SELECT `u`.`id`, `u`.`name`, `ug`.`title` FROM `users` AS `u` INNER JOIN (select id,title,position from user_groups where group_type='system') AS `ug` ON u.group_id=ug.id LIMIT 0, 1000;

# 改进方案1：增加强制索引 代码改动小
explain 
SELECT `u`.`id`, `u`.`name`, `ug`.`title` FROM `users` AS `u` FORCE INDEX (`group`) INNER JOIN (select id,title,position FROM user_groups WHERE group_type='system') AS `ug` ON ug.id=u.group_id LIMIT 0, 1000;

# 改进方案2: 分开查询 先查system的 group id ｜ 最后查询 需要的数据
explain
SELECT `u`.id,`u`.name,`ug`.title FROM users u LEFT JOIN user_groups ug ON u.group_id = ug.id WHERE `u`.group_id in (SELECT id FROM user_groups WHERE group_type='system');

# 改进方案3: 分开查询 先查system的 group id ｜ 再查 用户id ｜ 最后查询 需要的数据
explain
SELECT `u`.id,`u`.name,`ug`.title FROM users u LEFT JOIN user_groups ug ON u.group_id = ug.id WHERE `u`.id in (SELECT id FROM users WHERE group_id in (SELECT id FROM user_groups WHERE group_type='system'));

# 改进方案4: 再上面的基础上 增加缓存

​