sql常用优化

+------+-------------+-------+--------+------------------------------+---------------+---------+--------------+-----+-------------+
| id | select_type | table | type | possible_keys
MariaDB [rxzq]> show warnings; +-------+------+----------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------+ | Level | Code | Message | +-------+------+----------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------+ | Note | 1003 | select `rxzq`.`s`.`id` AS `id`,`rxzq`.`s`.`name` AS `name` from `rxzq`.`Student` `s` semi join (`rxzq`.`SC` `sc`) where `rxzq`.`sc`.`c_id` = 0 and `rxzq`.`sc`.`score` = 100 | +-------+------+----------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------+ 1 row in set (0.000 sec) MariaDB [rxzq]> Mysql竟然不是先执行里层的查询，而是将sql优化成了exists子句，并出现了EPENDENT SUBQUERY，mysql是先执行外层 查询，再执行里层的查询，这样就要循环70007*11=770077次。那么改用连接查询呢？ SELECT s.* from Student s INNER JOIN SC sc on sc.s_id = s.s_id where sc.c_id=0 and sc.score=100; 为了重新分析连接查询的情况，先暂时删除索引sc_c_id_index，sc_score_index，看下查询时间
Biblioteka Baidu| |
1 | SIMPLE 1 | SIMPLE
| sc |s
| ref
| sc_c_id_index,sc_score_index | sc_c_id_index | 5 | PRIMARY |4
| const
| 1|
1 | Using |
where | | eq_ref | PRIMARY | rxzq.sc.s_id | +------+-------------+-------+--------+------------------------------+---------------+---------+--------------+-----+-------------+ 2 rows in set (0.001 sec) MariaDB [rxzq]>
案例
例如一个月生成绩的数据库 三张表，分别为Course课程表、Student学生表、成绩表SC.造70万条数据
create table Course(c_id int PRIMARY KEY,name varchar(10)); create table Student(id int PRIMARY KEY,name varchar(10));
CREATE table SC( sc_id int PRIMARY KEY,s_id int,c_id int,score int);
比如查询语文成绩为100分的学生 select s.* from Student s where s.s_id in (select s_id from SC sc where sc.c_id = 0 and sc.score = 100 ) ； 如果查询比较慢那么看下查询计划 EXPLAIN select s.* from Student s where s.s_id in (select s_id from SC sc where sc.c_id = 0 and sc.score = 100 ); Explan中注意type，type全是ALL，那么首先想到的就是建立一个索引，建立索引的字段当然是在where条件的字段。 先给sc表的c_id和score建个索引 CREATE index sc_c_id_index on SC(c_id); CREATE index sc_score_index on SC(score); 再次查询时间可能会快很多。 select s.* from Student s where s.s_id in (select s_id from SC sc where sc.c_id = 0 and sc.score = 100 ) ； 但是联合查询时间好事相对较慢，在此看下最终执行计划 MariaDB [rxzq]> explain extended select s.* from Student s where s.id in (select s_id from SC sc where sc.c_id = 0 and sc.score = 100 ); +------+--------------+-------------+--------+------------------------------+---------------+---------+-------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filter ed | Extra | +------+--------------+-------------+--------+------------------------------+---------------+---------+-------+------+----------+-------------+ | 1 | PRIMARY | s | ALL | PRIMARY | NULL | NULL | NULL | 1 | 100. 00 | | | 1 | PRIMARY | <subquery2> | eq_ref | distinct_key | distinct_key | 4 | func | 1 | 100. 00 | | | 2 | MATERIALIZED | sc | ref | sc_c_id_index,sc_score_index | sc_c_id_index | 5 | const | 1 | 100. 00 | Using where | +------+--------------+-------------+--------+------------------------------+---------------+---------+-------+------+----------+-------------+ 3 rows in set, 1 warning (0.001 sec)
SQL优化实例
2019年3月18日 11:30
标准sql执行顺序
1:form 组装来自不同表的数据，如 form user或者，form user as u join goodsOrder as r on u.id= r.userid 2:where 过滤符合查询条件的数据，如：id>1000 3:group by 将查询数据进行分组 4:使用sum等聚合函数进行计算。 5:使用having 进行筛选分组。 6:执行select语种 7:执行排序语句 如：select count(gid),gname from shopping_goods where gcid=1 group by gname having count(gid)>1 order by count(gid) desc 1:首页查询shopping_goods 表，得到表中的数据 2:执行where,过滤出gcid=1的商品。 3:对gname进行分组。 4:使用聚合函数count(),计算出商品类型为1，不同商品名称的数量. 5:使用having,过滤出类型为1，商品统计数量大于1的商品 6:执行select语句 7:执行order by ,按照商品数量降序排列。
-----+-------------+
| key
| key_len | ref
| rows | Extra
|
+------+-------------+-------+--------+------------------------------+---------------+---------+--------------+-
看下现在的执行计划
• SELECT s.* FROM (SELECT * FROM SC sc WHERE sc.c_id = 0 AND sc.score = 100) t INNER JOIN Student s ON t.s_id = s.id; 先执行sc表的过滤，再进行表连接，记录执行时间 查看执行计划 MariaDB [rxzq]> explain SELECT s.* FROM (SELECT * FROM SC sc WHERE sc.c_id = 0 AND sc.score = 100) t INNER JOIN Student s ON t.s_id = s.id;
分区 新分区 4 的第 1 页
为了重新分析连接查询的情况，先暂时删除索引sc_c_id_index，sc_score_index，看下查询时间 这里有连表的情况出现，我猜想是不是要给sc表的s_id建立个索引
CREATE index sc_s_id_index on SC(s_id);
show index from SC; 再次执行连接查询，时间还变长了，什么原因？查看执行计划
先提取sc再连表，这样效率就高多了，现在的问题是提取sc的时候出现了扫描表，那么现在可以明确需要建立相关索 引
CREATE index sc_c_id_index on SC(c_id);
CREATE index sc_score_index on SC(score);
再次执行查询 • SELECT s.* FROM (SELECT * FROM SC sc WHERE sc.c_id = 0 AND sc.score = 100) t INNER JOIN Student s ON t.s_id = s.id; 查看执行计划先提取sc，再连表，都用到了索引。 • SELECT s.* from Student s INNER JOIN SC sc on sc.s_id = s.s_id where sc.c_id=0 and sc.score=100; 查询语句执行计划，先执行了where过滤，再执行连接操作，且都用到了索引 总结： 1.mysql嵌套子查询效率确实比较低 2.可以将其优化成连接查询 3.建立合适的索引