一個索引提高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;

（圖一）

大概在400ms左右，我不是很滿意這個查詢的速度，那么如何提升查詢速度呢？建個索引吧！

create index idx_books_name on books(name);

創建索引后我們再看看查詢的速度

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

（圖二）

可以發現，只需要6ms，索引為我們帶來600倍的速度提升，那么為什么索引可以帶來這么大的查詢速度提升呢？

索引揭秘

想象一下，現在我們有100w條數據，如何快速的通過name找到符合條件的數據

如果這100w條數據是按照name有序排列的，那么我們就可以使用二分搜索，這樣每次可以排除一半數據。那么100w數據最多只需要查詢

~=%2020次就可以找到

運行過程類型下圖

（圖三）

這里可以發現一個問題，在比較過程中，我們只用到了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';