1.有一张表t_account
CREATE TABLE `t_account` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT '',
`age` int DEFAULT '0',
`phone` varchar(20) DEFAULT NULL,
`address` varchar(255) DEFAULT NULL,
`goods` int DEFAULT '0',
`update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_age_phone` (`age`,`phone`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2087 DEFAULT CHARSET=utf8;
2.里面有一些记录
3.执行分析sql【索引定位的记录很多且需要回表】
【SELECT * FROM t_account WHERE age >= 25;
】
走的是全表扫描,虽然有idx_age_phone这个索引,但是还是选择了ALL【全表扫描】
因为mysql引擎分析,走全表扫描更快,直接获得对应的数据即可,而走复合索引,首先要通过复合索引获取对应的所有主键,然后分别去回表查询,效率更低。
那么如果查询到的记录数量较少的情况呢,或者能唯一定位到一条记录呢?是否能走到索引?
或者说不需要回表,是否走索引的效率更高?
4.分析sql【索引定位的记录很少且需要回表】
【SELECT * FROM t_account WHERE age > 25;
】
走的是索引,因为查询的结果直接可以查到最终的一条,回表查询一次就可以了,比全表扫描的效率要高
这里我们看到,如果索引能定位到的记录数较少,则会走到索引。
5.分析sql【索引定位的记录很多但不需要回表】
【SELECT id,age,phone FROM t_account WHERE age >= 25;
】
使用了复合索引,因为直接通过复合索引就可以查询到所有的信息,不需要回表,比全表扫描的效率要高。
下面我们猜想,如果使用了排序字段,1.排序字段在索引中存在,2.是主键,3.不是索引也不是主键会出现什么情况?
6.分析sql【索引定位的记录很多但不需要回表,需要重排序】
【SELECT id,age,phone FROM t_account WHERE age >= 25 ORDER BY phone asc;
】
通过age>=25查询对应的结果,结果是没有按照phone事先排好序的,所有最后还要再按照phone进行排序,出现了Using filesort
7.分析sql【索引定位的记录很多但不需要回表,不需要重排序】
【SELECT id,age,phone FROM t_account WHERE age = 25 ORDER BY phone asc;
】
通过age=25查询对应的结果,查询出来的结果已经按照phone排序好了,所以不需要再排序了,没有出现Using filesort
8.分析sql【索引定位的记录很多但不需要回表,需要降序索引】
【SELECT id,age,phone FROM t_account WHERE age = 25 ORDER BY phone desc;
】
mysql8.0之后开始支持降序索引,这种情况不会再出现filesort,是一个很好的优化。
9.分析sql【索引定位的记录很多且需要回表,需要重排序】
【SELECT id,age,phone FROM t_account WHERE age = 25 ORDER BY name desc;
】
如果排序的字段不存在于当前使用的索引和主键中,则需要去回表查询该字段,并且在server层根据该字段进行排序,所以效率更低,mysql选择了全表扫描来进行查询。