【宇哥帶你玩轉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，咱們不可能把全部的數據都加載到內存而後進行二分搜索，因此下一章會咱們講一講索引和硬盤的關係。