晋中市住房保障和城乡建设局网站,重庆工装公司,兰州新区农投建设网站,展示型网站案例MySQL 常用优化方式 sql 书写顺序与执行顺序SQL设计优化使用索引避免索引失效分析慢查询合理使用子查询和临时表列相关使用 日常SQL优化场景limit语句隐式类型转换嵌套子查询混合排序查询重写 sql 书写顺序与执行顺序 (7) SELECT
(8) DISTINCT select_list
(1) FROM select_list
(1) FROM main_table
(3) join_type JOIN join_table
(2) ON join_condition
(4) WHERE where_condition
(5) GROUP BY group_by_list
(6) HAVING having_condition
(9) ORDER BY order_by_condition
(10) LIMIT limit_numberSQL设计优化
使用索引
确保对经常作为查询条件的列创建索引对JOIN的列创建索引但要注意不要过度索引因为这会减慢写操作如INSERT、UPDATE、DELETE。
避免索引失效
匹配前缀如果在WHERE子句中使用LIKE操作符且匹配模式的开始部分是通配符例如LIKE ‘%xyz’将不会使用索引。但如果是’xyz%则使用索引。使用函数或表达式在列上使用函数或表达式例如WHERE YEAR(column) 2021会导致索引失效因为MySQL无法利用索引直接定位数据OR条件or表达式两边都必须有索引才会走索引否则将不会走索引。 反向条件不走索引 ! 、 、 NOT IN、IS NOT NULL数据类型不一致隐式转换可能导致索引失效【这点在隐式类型转换中有场景演示】
分析慢查询
使用EXPLAIN关键字可以帮助你分析SQL查询的执行计划。通过分析你可以发现潜在的性能瓶颈如全表扫描、没有使用索引等问题。
合理使用子查询和临时表
子查询和临时表如果不当使用会造成性能问题。在可能的情况下尝试使用JOIN来替代它们。
列相关使用 使用最适合数据的最小数据类型如INT、VARCHAR等这可以减少磁盘IO提高查询效率。 尽量避免使用SELECT *而是明确指定需要查询的字段。这不仅可以减少数据传输量还能提高查询效率。
日常SQL优化场景
limit语句
SELECT *
FROM operation
WHERE type SQLStatsAND name SlowLog
ORDER BY create_time
LIMIT 1000, 10;在优化上面SQL时如果数据量特别庞大除了在type, name, create_time 字段上加组合索引还可以记录上一次返回列表最后一条数据以它为开始优化后并不会根据数据量的增长而发生变化
SELECT *
FROM operation
WHERE type SQLStats
AND name SlowLog
AND create_time 2017-03-16 14:00:00
ORDER BY create_time limit 10;隐式类型转换
隐式转换就是不带转换类型的转换当一个字段类型为varchar但是在判断时SQL是用int去判断MySQL 就会对这个int进行隐式转换将其int类型转换为varchar
-- salecode 为varchar类型
explain select * from my_distribute where salecode898在上述例子中salecode为varchar类型其列有索引但是SQL并没有使用索引是因为SQL中发生了隐式转换导致了全表扫描那是不是所有隐式转换都会使索引失效
-- address 为int类型
explain select * from my_distribute where address22还是同一个表address类型为int其列有索引但是SQL却使用索引[address]以上可知隐式转换不一定会导致索引失效而是根据索引的类型变化如果是数值类型则右边无论是数值还是字符串都可以走索引但是我们在开发中一定要格外注意避免隐式转换索引失效
嵌套子查询
UPDATE operation o
SET status applying
WHERE o.id IN (SELECT idFROM (SELECT o.id,o.statusFROM operation oWHERE o.group 123AND o.status NOT IN ( done )ORDER BY o.parent,o.idLIMIT 1) t);上述例子中更新operation使用了子查询去做过滤并且使用了in条件子查询将会在检索operation每一条数据时都会执行一遍子查询并将结果集返回判断operation的o.id是否在结果集中效率非常低下我们在开发中也尽量使用join去替代子查询改良后的sql
UPDATE operation oJOIN (SELECT o.id,o.statusFROM operation oWHERE o.group 123AND o.status NOT IN ( done )ORDER BY o.parent,o.idLIMIT 1) tON o.id t.id
SET status applying混合排序
MySQL 不能利用索引进行混合排序。但在某些场景还是有机会使用特殊方法提升性能的。
SELECT *
FROM my_order o
INNER JOIN my_appraise a ON a.orderid o.id
ORDER BY a.is_reply ASC,a.appraise_time DESC
LIMIT 0, 20由于 is_reply 只有0和1两种状态可以按照下面的方法重写
SELECT *
FROM ((SELECT *FROM my_order oINNER JOIN my_appraise aON a.orderid o.idAND is_reply 0ORDER BY appraise_time DESCLIMIT 0, 20)UNION ALL(SELECT *FROM my_order oINNER JOIN my_appraise aON a.orderid o.idAND is_reply 1ORDER BY appraise_time DESCLIMIT 0, 20)) t
ORDER BY is_reply ASC,appraisetime DESC
LIMIT 20;使用表子查询将两个查询结果集UNION ALL 合并结果实现排序
查询重写 SELECTa.*,c.allocated
FROM(SELECT resourceid FROM my_distribute d WHERE isdelete 0 AND cusmanagercode 22353 ORDER BY salecode LIMIT 20 ) aLEFT JOIN ( SELECT resourcesid, sum( ifnull( allocated, 0 )* 12345 ) allocated FROM my_resources GROUP BY resourcesid ) c ON a.resourceid c.resourcesid 以上SQL中因为c表使用了全表聚合导致了数据全表扫描10w数据优化后
SELECTr.resourcesid,sum( ifnull( allocated, 0 ) * 12345 ) allocated
FROMmy_resources r,( SELECT resourceid, cusmanagercode FROM my_distribute d WHERE isdelete 0 AND cusmanagercode 22353 ORDER BY salecode LIMIT 20 ) a
WHEREr.resourcesid a.resourceid
GROUP BYresourceid
