5 SQL语句的优化

本文围绕MySQL查询优化展开,介绍使用EXPLAIN查看可优化点,如type包含system、const等多种情况,Extra包含using filesort等信息。还阐述常见优化策略,单表优化有加复合索引、调整索引顺序等,双表优化有加索引,遵循小表驱动大表原则。

1 使用EXPLAIN查看可以优化的点

2 EXPLAIN出现了哪些问题,我们需要去优化

我们能优化的点由type,Extra提供

2.1 type的包括以下内容

(1)system

只有一条记录的系统表 或 衍生表只有一条数据的主查询

系统表我们模拟不了,我们能模拟衍生表

(2)const

仅仅能查到一条数据的SQL,必须是Primary Key 或unique

 (3)eq_ref

对于每个索引键的查询,返回匹配唯一行数据(有且只有一个,不能多也不能少也不能为0)。此种情况常见于主键唯一和索引唯一。

eq_ref要求两个表的的记录条数必须一致,很难满足。

(4)ref

非唯一性索引,对于每个索引键拆线,返回匹配的所有行(0个或多个)

(5)range

检索指定范围的行,where后面是一个范围(between,and,><=)

in有时候会使索引失效

(6)index

查询所有索引中顺序

(7)all

查询所有的数据

2.2 Extra

Extra:包含不适合在其它列中显示但十分重要的额外信息:
(1)using filesort(出现这个东西不好):说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取,mysql中无法利用索引完成的排序操作称为"文件排序"。需要额外的一次排序和查询。用一个索引查却用另一个索引排序,往往会导致多一次排序。

(2)using temporary(出现这个东西更不好,使用到了临时表):使用了临时表保存中间结果,Mysql在对查询结果排序时使用临时表,常见于排序order by和分组查询group by.

(3)using index:表示相应的select操作中使用了覆盖索引(Covering Index),避免了访问了表的数据行,效率不错!

(4)如果没有同时出现using where,表明索引用来读取数据而非执行查找操作;也就是回表查询。

3 常见的优化策略

3.1单表优化

CREATE TABLE `book` (
  `bid` int(4) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `authorid` int(4) DEFAULT NULL,
  `publicid` int(4) DEFAULT NULL,
  `typeid` int(4) DEFAULT NULL,
  PRIMARY KEY (`bid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

对type进行优化,把ALL改成ref

优化1:我们加一个复合索引

优化2:根据SQL解析的实际顺序,调整索引的顺序

没有什么优化

优化3:因为根据左前缀原则,左边的失效会导致右边的也失效。我们要把in放到最后

 小节:

(1)最佳左前缀,索引顺序要和SQL语句的查询顺序一致,索引不能跨列使用

(2)IN的范围查询要放到SQL语句的最后,因为IN可能会导致索引失效,根据最佳左前缀,导致失效放右边。

3.2 双表优化

优化1:加索引

两个表那么索引加在哪个表呢?

(1)小表驱动大表,小表放左边,大表放右边

(2)给经常使用的字段加索引,左外连接加左表,右外连接加右表

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值