【宇哥带你玩转MySQL】索引篇(一)索引揭秘，看他是如何让你的查询性能指数提高的

时间 2020-04-06 标签宇哥带你玩转MySQL 索引篇一索引揭秘看他如何让你查询性能指数提高

场景复现，一个索引提升600倍查询速度？

首先准备一张books表mysql

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条数据sql

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;

（图一）性能

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

create index idx_books_name on books(name);

建立索引后咱们再看看查询的速度优化

 
   set profiling = 1;
select * from books where name = 'name900000';
show profiles;
set profiling = 0; 
  

（图二）spa

能够发现，只须要6ms，索引为咱们带来600倍的速度提高，那么为何索引能够带来这么大的查询速度提高呢？3d

索引揭秘

想象一下，如今咱们有100w条数据，如何快速的经过name找到符合条件的数据code

若是这100w条数据是按照name有序排列的，那么咱们就可使用二分搜索，这样每次能够排除一半数据。那么100w数据最多只须要查询~= 20次就能够找到blog

运行过程类型下图

（图三）

这里能够发现一个问题，在比较过程当中，咱们只用到了name字段，可是却须要把name和其余字段一块儿加载到内存，这样显然会浪费不少内存，因此咱们能够修改结构为下图

（图四）

咱们把原来表中的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排序，如图六

（图六）

经过图六的数据结构咱们能够学习到索引使用的一个原则和一个优化

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

以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'; 
 

小结

经过本章学习，咱们了解到索引其实就是一个有序排列的表，咱们经过有序排列的优点来加快查询。也正是因为索引是有序排列的，若是想有效使用索引，咱们就须要要遵循最左匹配原则。咱们还了解到覆盖索引，若是查询的字段全在索引上，能够减小一次回表查询，利用该特性在大批量查询时能够大幅度优化性能。

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