sql优化案例经验总结

背景：在做公司项目是遇到的大数据量查询缓慢问题，mysql中数据表数据量高达600万，接口请求时间缓慢，平均时间22s

sql语句如下：

 SELECT
		COUNT(*) AS mCount,
		m.id id,
		m.module_name moduleName,
		m.module_code moduleCode,
		m.module_father_id moduleFatherId,
		m.module_level moduleLevel,
		m.original_router_path originalRouterPath,
		m.module_router_path moduleRouterPath,
		m.module_icon moduleIcon,
		m.module_icon moduleIconArray,
		m.`icon_2px_path` icon2pxPath,
		m.`icon_3px_path` icon3pxPath,
		m.status STATUS,
		m.module_type moduleType,
		m.url_parameter urlParameter,
		m.`open_status` openStatus
		FROM `app_module_access` ama
        LEFT JOIN `app_module` m ON ama.module_id=m.id
        WHERE ama.module_id IN (SELECT id FROM `app_module`)
       AND m.`status`='E'
        GROUP BY ama.`module_id` ORDER BY mCount DESC  LIMIT 10

分析过程：

1.首先查询app_module_access表中有无索引发现在建表是并没有建立索引，于是给where条件中的module_id建立了索引，这里需要说明一般表中的外键都需要建立索引的

2.where条件后用到了子查询SELECT id FROM `app_module`，于是我把sql语句改成了以下：

SELECT
		COUNT(*) AS mCount,
		m.id id,
		m.module_name moduleName,
		m.module_code moduleCode,
		m.module_father_id moduleFatherId,
		m.module_level moduleLevel,
		m.original_router_path originalRouterPath,
		m.module_router_path moduleRouterPath,
		m.module_icon moduleIcon,
		m.module_icon moduleIconArray,
		m.`icon_2px_path` icon2pxPath,
		m.`icon_3px_path` icon3pxPath,
		m.status STATUS,
		m.module_type moduleType,
		m.url_parameter urlParameter,
		m.`open_status` openStatus
		FROM `app_module_access` ama,`app_module` m
        WHERE ama.module_id=m.`id`
       AND m.`status`='E'
        GROUP BY ama.`module_id` ORDER BY mCount DESC  LIMIT 10

3. EXPLAIN 查询语句执行情况，这是整条查询语句已经提升到了11秒，原因是因为用到了索引

4.分析新加的索引字段起了效果，但目前查询还是有10秒，还是很慢，于是又分析了以下sql做了如下改动：

        SELECT
        ama.mCount,
        m.id id,
        m.module_name moduleName,
        m.module_code moduleCode,
        m.module_father_id moduleFatherId,
        m.module_level moduleLevel,
        m.original_router_path originalRouterPath,
        m.module_router_path moduleRouterPath,
        m.module_icon moduleIcon,
        m.module_icon moduleIconArray,
        m.`icon_2px_path` icon2pxPath,
        m.`icon_3px_path` icon3pxPath,
        m.status STATUS,
        m.module_type moduleType,
        m.url_parameter urlParameter,
        m.`open_status` openStatus
        FROM (SELECT module_id, COUNT(*) mCount FROM `app_module_access` GROUP BY module_id) ama,`app_module` m
        WHERE ama.module_id=m.`id`
        AND m.`status`='E'
        ORDER BY mCount DESC  LIMIT 10

原因是因为我需要查的a表数据并不多，而a表的数据量又非常大，与是便用子查询新生成一个第三方表，这样减少了对标字段以达到效率增益。

最后查询时间只需要1秒左右即可，记录下来以便日后查看。

今天的不开心就至于此吧！明天依旧光芒万丈鸭宝贝！！！！^_^