来自 科技 2020-04-11 15:57 的文章

带你玩转MySQL，索引揭秘，看我是如何让你的查询

一个索引提高600倍查询速度？

首先准备一张books表

create table books(
    id int not null primary key auto_increment,
    name varchar(255) not null,
    author varchar(255) not null,
    created_at datetime not null default current_timestamp,
    updated_at datetime not null default current_timestamp on update current_timestamp
)engine=InnoDB;

然后插入100w条数据

drop procedure prepare_data;
delimiter //
create procedure prepare_data()
begin
    declare i int;
    set i = 0;
    while i < 1000000
        do
            insert into books(name, author) value (concat('name', i), concat('author', i));
            set i = i + 1;
        end while;
end //
delimiter ;
call prepare_data();

那么问题来了，现在我们要在这100w本书中找到name为name9000000的书，来看看大概需要多久。

set profiling = 1;
select * from books where name = 'name900000';
show profiles;
set profiling = 0;
带你玩转MySQL，索引揭秘，看我是如何让你的查询

（图一）

大概在400ms左右，我不是很满意这个查询的速度，那么如何提升查询速度呢？建个索引吧！

create index idx_books_name on books(name);

创建索引后我们再看看查询的速度

set profiling = 1;
select * from books where name = 'name900000';
show profiles;
set profiling = 0;
带你玩转MySQL，索引揭秘，看我是如何让你的查询

（图二）

可以发现，只需要6ms，索引为我们带来600倍的速度提升，那么为什么索引可以带来这么大的查询速度提升呢？

索引揭秘

想象一下， 现在我们有100w条数据，如何快速的通过name找到符合条件的数据

如果这100w条数据是按照name有序排列的，那么我们就可以使用二分搜索，这样每次可以排除一半数据。那么100w数据最多只需要查询

带你玩转MySQL，索引揭秘，看我是如何让你的查询

~= 20次就可以找到

运行过程类型下图

带你玩转MySQL，索引揭秘，看我是如何让你的查询

（图三）

这里可以发现一个问题，在比较过程中，我们只用到了name字段，但是却需要把name和其他字段一起加载到内存，这样显然会浪费很多内存，所以我们可以修改结构为下图

带你玩转MySQL，索引揭秘，看我是如何让你的查询

（图四）

我们把原来表中的name和id字段进行一份复制形成了一个新的表，这样的话，当我们根据name来查询数据时，只需要把name和id两个数据加载到内存就行了，当找到数据后再根据id找到对应行的其他数据。

其实这个冗余表就是我们常说的索引，索引表会把我们指定的列的数据进行拷贝形成一个新的表，这个表中的数据是有序排列的，如果有多列，则是按声明的前后关系依次比较。

例如，有一个商品表items，其中有名称、价格、创建日期等字段

create table items
(
    id int not null primary key auto_increment,
    title varchar(255) not null,
    price decimal(12,2) not null,
    created_at datetime not null,
    updated_at datetime not null
) engine = innodb;

（图五）

由于用户喜欢按价格和创建时间查找商品，我们可以创建一个idx_items_price_created_at(price, created_at)的索引，那么他的数据结构就是这样的：先按price排序，再按created_at排序，如图六

带你玩转MySQL，索引揭秘，看我是如何让你的查询

（图六）

通过图六的数据结构我们可以学习到索引使用的一个原则和一个优化

一个原则：最左匹配原则：如果要触发索引使用，需要按索引字段的声明顺序来添加条件过滤

以items表中的idx_items_price_created_at索引使用举例：

# sql1：price + created_at条件，可以使用索引
select * from items where price = "20" and created_at = '2020-01-04';

# sql2：created_at + price条件，可以使用索引，注意虽然此处查询条件顺序和索引顺序不一样，但其实mysql在执行sql前，会先对sql进行语法分析，最终的结果是和sql1一样的。但是我不推荐这种写法，因为对于看代码的人来说没有sql1直观。
select * from items where created_at = "2020-01-04" and price = "20";

# sql3：price 可以使用索引，因为索引表即使只考虑price字段，顺序也是有序的
select * from items where price = "20";

# sql4：crated_at 不可以使用索引，因为索引中如果只考虑craeted_at字段，顺序不能保证有序
select * from items where created_at = "2020-01-04";

一个优化：覆盖索引：如果要查询的字段全在索引上，那么不需要回表

以items表中的idx_items_price_created_at索引使用举例：

# sql1：由于需要所有的字段，该查询在根据idx_items_price_created_at找到id后，还需要根据id再找items表中该条记录的其他字段的值
select * from items where price = "20" and created_at = '2020-01-04';
​
# sql2: 由于需要的字段在索引上都有，该查询只需要在idx_items_price_created_at索引表找到记录直接返回即可
select price, created_at, id  where price = "20" and created_at = '2020-01-04';
小结

通过本章学习，我们了解到索引其实就是一个有序排列的表，我们通过有序排列的优势来加快查询。也正是由于索引是有序排列的，如果想有效使用索引，我们就需要要遵循最左匹配原则。我们还了解到覆盖索引，如果查询的字段全在索引上，可以减少一次回表查询，利用该特性在大批量查询时可以大幅度优化性能。

带你玩转MySQL，索引揭秘，看我是如何让你的查询

本章所讲的内容全是以数据全在内存中为前提的，但是真实场景中数据都是在硬盘中保存，如果一个表中的数据可能有好几G，我们不可能把所有的数据都加载到内存然后进行二分搜索，所以下次我会讲一讲索引和硬盘的关系。

最后

多说一句，很多人学Python过程中会遇到各种烦恼问题，没有人解答容易放弃。小编是一名python开发工程师，这里有我自己整理了一套最新的python系统学习教程，包括从基础的python脚本到web开发、爬虫、数据分析、数据可视化、机器学习等。想要这些资料的可以关注小编，并在后台私信小编：“01”即可领取。

热门文章