詳細介紹MySQL數據庫索引知識

下面講講關于MySQL數據庫索引知識，文字的奧妙在于貼近主題相關。所以，閑話就不談了，我們直接看下文吧，相信看完MySQL數據庫索引知識這篇文章你一定會有所受益。

MySQL數據庫------索引

一、索引的介紹

數據庫中專門用于幫助用戶快速查找數據的一種數據結構。類似于字典中的目錄，查找字典內容時可以根據目錄查找到數據的存放位置嗎，然后直接獲取。

二 、索引的作用

約束和加速查找

三、常見的幾種索引：

普通索引，唯一索引，主鍵索引（這幾個都是單列）

聯合索引（多列），比如：聯合主鍵索引、聯合唯一索引、聯合普通索引

無索引和有索引的區別以及建立索引的目的

無索引： 從前往后一條一條查詢
有索引：創建索引的本質，就是創建額外的文件（某種格式存儲，查詢的時候，先去格外的文件找，定好位置，然后再去原始表中直接查詢。但是創建索引越多，會對硬盤也是有損耗。

建立索引的目的：

a.額外的文件保存特殊的數據結構
b.查詢快，但是插入更新刪除依然慢
c.創建索引之后，必須命中索引才能有效

索引的種類

hash索引和BTree索引
（1）hash類型的索引：查詢單條快，范圍查詢慢
（2）btree類型的索引：b+樹，層數越多，數據量指數級增長（我們就用它，因為innodb默認支持它）

3.1 普通索引

作用：僅有一個加速查找

create table userinfo(
   nid int not null auto_increment primary key,
   name varchar(32) not null,
   email varchar(64) not null,
   index ix_name(name)      ###這里是索引
);

創建表+普通索引

create index 索引的名字 on 表名(列名)普通索引

刪除索引

drop index 索引的名字 on 表名

查看索引

show index from 表名

3.2 唯一索引

 唯一索引有兩個功能：加速查找和唯一約束（可含null）

創建表+唯一索引

create table userinfo(
   id int not null auto_increment primary key,
   name varchar(32) not null,
   email varchar(64) not null,
   unique  index  ix_name(name)     ###這里是索引
);

create unique index 索引名 on 表名(列名)唯一索引

刪除唯一索引

drop unique index 索引名 on 表名

3.3 主鍵索引

主鍵索引有兩個功能： 加速查找和唯一約束（不含null）

create table userinfo(
    id int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    unique  index  ix_name(name)
);
或者
create table userinfo(
    id int not null auto_increment,
    name varchar(32) not null,
    email varchar(64) not null,
    primary key(nid),
    unique  index  ix_name(name)
);

創建表+主鍵索引

alter table 表名 add primary key(列名);主鍵索引

刪除主鍵索引

alter table 表名 drop primary key;
alter table 表名  modify  列名 int, drop primary key;

3.4 組合索引

 組合索引是將n個列組合成一個索引

 其應用場景為：頻繁的同時使用n列來進行查詢，如：where name = 'John' and email = 'John@qq.com'。

create index 索引名 on 表名(列名1,列名2);

主鍵索引

alter table 表名 add primary key(列名);

刪除主鍵索引

alter table 表名 drop primary key;
alter table 表名  modify  列名 int, drop primary key;

3.4 組合索引

 組合索引是將n個列組合成一個索引

 其應用場景為：頻繁的同時使用n列來進行查詢，如：where name = 'john' and email = 'john@qq.com'。

create index 索引名 on 表名(列名1,列名2);

四、索引名詞

#覆蓋索引：在索引文件中直接獲取數據
例如：
select name from userinfo where name = 'john50000';
 #索引合并：把多個單列索引合并成使用
例如：
select * from  userinfo where name = 'john13131' and id = 13131;

數據庫表中添加索引后確實會讓查詢速度起飛，但前提必須是正確的使用索引來查詢，如果以錯誤的方式使用，則即使建立索引也會不奏效。五、正確使用索引的情況

使用索引，我們必須知道：

  （1）創建索引 

  （2）命中索引

  （3）正確使用索引

準備：

#1. 準備表
create table userinfo(
    id int,
    name varchar(20),
    gender char(6),
    email varchar(50)
);
 #2. 創建存儲過程，實現批量插入記錄
delimiter $$ #聲明存儲過程的結束符號為$$
create procedure auto_insert1()
BEGIN
    declare i int default 1;
    while(i<3000000)do
        insert into userinfo values(i,concat('john',i),'male',concat('jack',i,'@john.com'));
        set i=i+1;
    end while;
END$$ #$$結束
delimiter ; #重新聲明分號為結束符號

 #3. 查看存儲過程
    show create procedure auto_insert1\G
 #4. 調用存儲過程
    call auto_insert1();
 準備300w條數據

- like '%xx'
    select * from userinfo where name like '%al';
- 使用函數
    select * from userinfo where reverse(name) = 'john333';
- or
    select * from userinfo where id = 1 or email = 'john122@john.com';
    特別的：當or條件中有未建立索引的列才失效，以下會走索引
            select * from userinfo where id = 1 or name = 'john1222';
            select * from userinfo where id = 1 or email = 'john122@john.com' and name = 'john112'
- 類型不一致
    如果列是字符串類型，傳入條件是必須用引號引起來，不然...
    select * from userinfo where name = 999;
- !=
    select count(*) from userinfo where name != 'john'
    特別的：如果是主鍵，則還是會走索引
        select count(*) from userinfo where id != 123
- >
    select * from userinfo where name > 'john'
    特別的：如果是主鍵或索引是整數類型，則還是會走索引
        select * from userinfo where id > 123
        select * from userinfo where num > 123
- order by
    select email from userinfo order by name desc;
    當根據索引排序時候，選擇的映射如果不是索引，則不走索引
    特別的：如果對主鍵排序，則還是走索引：
        select * from userinfo order by nid desc;
  - 組合索引最左前綴
    如果組合索引為：(name,email)
    name and email       -- 使用索引
    name                 -- 使用索引
    email                -- 不使用索引

什么是最左前綴呢？

最左前綴匹配：
    create index ix_name_email on userinfo(name,email);
    select * from userinfo where name = 'john';
    select * from userinfo where name = 'john' and email='john@john.com';
    select * from userinfo where  email='john@john.com';
 如果使用組合索引如上，name和email組合索引之后，查詢
（1）name和email ---使用索引
（2）name        ---使用索引
（3）email       ---不適用索引
對于同時搜索n個條件時，組合索引的性能好于多個單列索引
******組合索引的性能>索引合并的性能*********

六、索引的注意事項

(1)避免使用select *
(2)count(1)或count(列) 代替count(*)
(3)創建表時盡量使用char代替varchar
(4)表的字段順序固定長度的字段優先
(5)組合索引代替多個單列索引（經常使用多個條件查詢時）
(6)盡量使用短索引 （create index ix_title on tb(title(16));特殊的數據類型 text類型）
(7)使用連接（join）來代替子查詢
(8)連表時注意條件類型需一致
(9)索引散列（重復少）不適用于建索引，例如：性別不合適

七、執行計劃

　explain + 查詢SQL - 用于顯示SQL執行信息參數，根據參考信息可以進行SQL優化　

mysql> explain select * from userinfo;
+----+-------------+----------+------+---------------+------+---------+------+---------+-------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows    | Extra |
+----+-------------+----------+------+---------------+------+---------+------+---------+-------+
|  1 | SIMPLE      | userinfo | ALL  | NULL          | NULL | NULL    | NULL | 2973016 | NULL  |
+----+-------------+----------+------+---------------+------+---------+------+---------+-------+
 mysql> explain select * from (select id,name from userinfo where id <20) as A;
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL |   19 | NULL        |
|  2 | DERIVED     | userinfo   | range | PRIMARY       | PRIMARY | 4       | NULL |   19 | Using where |
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
rows in set (0.05 sec)

select_type：
查詢類型
    SIMPLE          簡單查詢
    PRIMARY         最外層查詢
    SUBQUERY        映射為子查詢
    DERIVED         子查詢
    UNION           聯合
    UNION RESULT    使用聯合的結果
     table：
    正在訪問的表名
type：
    查詢時的訪問方式，性能：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
    ALL 全表掃描，對于數據表從頭到尾找一遍
    select * from userinfo;
    特別的：如果有limit限制，則找到之后就不在繼續向下掃描
       select * from userinfo where email = 'john@john.com'
       select * from userinfo where email = 'john112@john.com' limit 1;
       雖然上述兩個語句都會進行全表掃描，第二句使用了limit，則找到一個后就不再繼續掃描。
 INDEX ： 全索引掃描，對索引從頭到尾找一遍
    select nid from userinfo;
 RANGE： 對索引列進行范圍查找
    select *  from userinfo where name < 'john';
    PS:
        between and
        in
        >   >=  <   <=  操作
        注意：!= 和 > 符號
  INDEX_MERGE： 合并索引，使用多個單列索引搜索
    select *  from userinfo where name = 'john' or nid in (11,22,33);
 REF： 根據索引查找一個或多個值
    select *  from userinfo where name = 'john112';
 EQ_REF： 連接時使用primary key 或 unique類型
    select userinfo2.id,userinfo.name from userinfo2 left join tuserinfo on userinfo2.id = userinfo.id;
 CONST：常量
    表最多有一個匹配行,因為僅有一行,在這行的列值可被優化器剩余部分認為是常數,const表很快,因為它們只讀取一次。
    select id from userinfo where id = 2 ;
 SYSTEM：系統
    表僅有一行(=系統表)。這是const聯接類型的一個特例。
    select * from (select id from userinfo where id = 1) as A;
 possible_keys：可能使用的索引
 key：真實使用的
 key_len： MySQL中使用索引字節長度
 rows： mysql估計為了找到所需的行而要讀取的行數 ------ 只是預估值
 extra：
    該列包含MySQL解決查詢的詳細信息
    "Using index"
        此值表示mysql將使用覆蓋索引，以避免訪問表。不要把覆蓋索引和index訪問類型弄混了。
    "Using where"
        這意味著mysql云服務器將在存儲引擎檢索行后再進行過濾，許多where條件里涉及索引中的列，當（并且如果）它讀取索引時，就能被存儲引擎檢驗，因此不是所有帶where子句的查詢都會顯示"Using where"。有時"Using where"的出現就是一個暗示：查詢可受益于不同的索引。
    "Using temporary"
        這意味著mysql在對查詢結果排序時會使用一個臨時表。
    "Using filesort"
        這意味著mysql會對結果使用一個外部索引排序，而不是按索引次序從表里讀取行。mysql有兩種文件排序算法，這兩種排序方式都可以在內存或者磁盤上完成，explain不會告訴你mysql將使用哪一種文件排序，也不會告訴你排序會在內存里還是磁盤上完成。
    "Range checked for each record(index map: N)"
        這個意味著沒有好用的索引，新的索引將在聯接的每一行上重新估算，N是顯示在possible_keys列中索引的位圖，并且是冗余的

八、慢日志記錄

參數說明：

開啟慢查詢日志，可以讓MySQL記錄下查詢超過指定時間的語句，通過定位分析性能的瓶頸，才能更好的優化數據庫系統的性能。

（1） 進入MySql 查詢是否開了慢查詢
    show variables like 'slow_query%';
    參數解釋：
     slow_query_log 慢查詢開啟狀態  OFF 未開啟 ON 為開啟
    slow_query_log_file 慢查詢日志存放的位置（這個目錄需要MySQL的運行帳號的可寫權限，一般設置為MySQL的數據存放目錄）
    
（2）查看慢查詢超時時間
    show variables like 'long%';
    ong_query_time 查詢超過多少秒才記錄   默認10秒

（3）開啟慢日志（1）（是否開啟慢查詢日志，1表示開啟，0表示關閉。
    set global slow_query_log=1;
    
（4）再次查看
    show variables like '%slow_query_log%';
    
（5）開啟慢日志（2）：（推薦）
    在my.cnf 文件中
    找到[mysqld]下面添加：
    slow_query_log =1
    slow_query_log_file=C:\mysql-5.6.40-winx64\data\localhost-slow.log    #linux這里路徑需要更改一下即可
    long_query_time = 1
     參數說明：
    slow_query_log 慢查詢開啟狀態  1 為開啟
    slow_query_log_file 慢查詢日志存放的位置
    long_query_time 查詢超過多少秒才記錄   默認10秒 修改為1秒

對于以上MySQL數據庫索引知識相關內容，大家還有什么不明白的地方嗎？或者想要了解更多相關，可以繼續關注我們的行業資訊板塊。