阅读本文大概需要2.6分钟。
在日常开发中我们经常需要根据某个字段来对数据进行排序,假如我们用到的表的结构如下
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`birth_city` varchar(16) NOT NULL,
`name` varchar(16) NOT NULL,
`age` int(11) NOT NULL,
`addr` varchar(128) DEFAULT NULL,
`hobby` varchar(128) DEFAULT NULL,
`score` varchar(128) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `birth_city` (`birth_city`)
) ENGINE=InnoDB;
现在有这样一个查询语句,
select birth_city, name, age from t where birth_city = '西安' order by name desc limit 1000;
这个查询语句是如何执行的呢?
sort_buffer 是 MySQL 为线程排序分配的一块内存。
根据已有的知识,birth_city 字段出现在where条件中,我们在该字段上建立索引能加快访问速度。那么该语句的查询过程如下:
上述的查询过程称为全字段索引排序。
在进行步骤6的过程中,会根据数据量的大小,安排在不同位置进行排序,有可能是内存或者硬盘。
如果上述查询语句select 中出现的字段比较多,那么要占用的sort_buffer 的空间就会变大,此时MySQL会采取另一种策略。
select birth_city, name, age,hobby,score from t where birth_city = '西安' order by name desc limit 1000;
在初始化sort_buffer的时候,只会放入id和name,然后在查询基础上增加一次回表,完整流程如下所示:
上述过程称为rowid排序
全字段排序会占用较多的内存,而rowid排序虽然降低了内存使用,但是会多一次回表,增加磁盘的IO操作。至于孰优孰劣,需要根据自己的业务场景,作出自己的选择。
select birth_city, name, age from t where birth_city = '西安' order by name desc limit 1000;
在上述语句的执行过程中,我们发现需要对name字段进行排序,那么我们能不能利用索引有序的的特点,省略对name字段排序的过程呢?答案是可以的。
alter table t add index city_user(birth_city, name);
我们在birth_city 和 name 字段上建立 联合索引,那么birth_city 和 name 就是有序的,我们再重新分析一下查询流程,以全字段排序为例。
如果再优化一下,根据覆盖索引,我们建立索引如下
alter table t add index city_user_name_age(birth_city, name,age);
这样的话就省去了步骤2,减少了一次回表。
但是呢,架构的设计没有银弹,索引字段的增加必然会带来空间的增加以及维护成本的提升,作为开发人员的你还是需要根据业务场景作出自己的选择。
感谢大家的阅读,如果对MySQL想要有深入的了解,可以购买下书。
如果对你有帮助,分享,收藏,赞,在看走一波。