mysql sql优化,该如何解决
首先感谢各大神路过
explain SELECT * FROM (
SELECT
t.teacher_id,t.teacher_name,t.INDB_DATE, t1.schl_name school_name,
t1.porv_id province_code,
t2.attrName province_name,
t1.area_id area_code,
t3.attrName area_name,
t1.cnty_id country_code,
t4.attrName country_name,
t5.attrName role_name
FROM
T_DATA_TEACHER_INFO t
LEFT JOIN t_school_info t1 ON t.SCHL_ID = t1.SCHL_ID
LEFT JOIN t_sys_param t2 ON t1.PORV_ID = t2.attrValue AND t2.PARAMKEY='PROVINCE'
LEFT JOIN t_sys_param t3 ON t1.area_id = t3.attrValue AND t3.PARAMKEY='CITY'
LEFT JOIN t_sys_param t4 ON t1.cnty_id = t4.attrValue AND t4.PARAMKEY='TOWN'
LEFT JOIN t_sys_param t5 ON t.teacher_type = t5.attrValue AND t5.PARAMKEY='DICT_TEACHER_ROLE'
ORDER BY INDB_DATE DESC
) my
LIMIT 0,10;
explain
-+
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
|
----+-------------+------------+------+---------------+------+---------+------+-------------------+--------------------------------
-+
1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 14978861232656688 | NULL
|
2 | DERIVED | t | ALL | NULL | NULL | NULL | NULL | 12 | Using temporary; Using filesort
|
2 | DERIVED | t1 | ALL | PRIMARY | NULL | NULL | NULL | 5 | Using where; Using join buffer
|
2 | DERIVED | t2 | ALL | NULL | NULL | NULL | NULL | 4203 | Using where; Using join buffer
|
2 | DERIVED | t3 | ALL | NULL | NULL | NULL | NULL | 4203 | Using where; Using join buffer
|
2 | DERIVED | t4 | ALL | NULL | NULL | NULL | NULL | 4203 | Using where; Using join buffer
|
2 | DERIVED | t5 | ALL | NULL | NULL | NULL | NULL | 4203 | Using where; Using join buffer
|
------解决思路----------------------
联合查询都是比较烧cpu的。为什么不做成视图呢？
------解决思路----------------------
key
------解决思路----------------------
都知道exlpain了，没看到Key all null啊~~
------解决思路----------------------
如果t表的数据量很大的情况下，这个效率会很差
因为只取10行记录，可以拆分一下，先在t中把10条取出来，再分别到其它几个表去取相关信息
主要遵循的原则是以小结果集来驱动大数据
另外索引也很更要