MySQL 索引 +explain

為準備面試，複習下。

一、索引的介紹

在mysql中,索引就是資料結構,已經在檔案中按照索引進行排序好的結構.
使用索引可以加快我們的查詢速度,但是對我們的資料增刪改效率會降低.
因為一個網站大部分都是查詢,我們主要最佳化select語句.

二、MySQL中索引的分類

普通索引 key
唯一索引 unique key unique key 別名別名可忽略別名可忽略
主鍵索引 primary key(欄位)
全文索引myisam引擎支援(只對英文進行索引,mysql版本5.6也支援),sphinx(中文搜尋)
混合索引 多個欄位組成的索引.如 key key_index(title,email)

三、索引的基本操作

1、給表新增索引

create table t_index(
    id int not null auto_increment,
    title varchar(30) not null default '',
    email varchar(30) not null default '',
    primary key(id),
    unique key uni_email(email) ,
    key key_title(title)
)engine=innodb charset=utf8;

檢視錶

desc tablename

mysql> desc t_index;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| title | varchar(30) | NO   | MUL |         |                |
| email | varchar(30) | NO   | UNI |         |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

檢視錶的建立語句

show create table tbalename/G

mysql> show create table t_index/G;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/G' at line 1
mysql> show create table t_index\G;
*************************** 1. row ***************************
       Table: t_index
Create Table: CREATE TABLE `t_index` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(30) NOT NULL DEFAULT '',
  `email` varchar(30) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uni_email` (`email`),
  KEY `key_title` (`title`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR: 
No query specified

2、刪除索引

刪除主鍵索引

alter table table_name drop primary key;

注意：

mysql> alter table t_index drop primary key;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

主鍵不一定是自增長，但是自增長一定是主鍵。

刪除逐漸之前先要把主鍵索引的自增長去掉。

mysql> alter table t_index modify  id int not null;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

再來刪除主鍵

mysql> alter table t_index drop primary key;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

刪除普通和唯一的索引

alter table table_name drop key ‘索引的別名’

實際操作

mysql> alter table t_index drop key uni_email;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t_index drop key key_title;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

3、新增索引

alter table t_index add key key_title(title);
alter table t_index add key uni_email(email);
alter table t_index add primary key(id);

4、有無索引對比

create table article(
id int not null auto_increment,
no_index int,
title varchar(30) not null default '',
add_time datetime,
primary key(id)
);

插入資料

mysql> insert into article(id,title,add_time) values(null,'ddsd1212123d',now());

mysql> insert into article(title,add_time) select title,now() from article;
Query OK, 10 rows affected (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> update article set no_index=id;

有無索引查詢資料對比

mysql> select * from article where no_index=1495298;
+---------+----------+-----------+---------------------+
| id      | no_index | title     | add_time            |
+---------+----------+-----------+---------------------+
| 1495298 |  1495298 | ddsd1123d | 2019-05-15 23:13:56 |
+---------+----------+-----------+---------------------+
1 row in set (0.28 sec)

mysql> select * from article where id=1495298;
+---------+----------+-----------+---------------------+
| id      | no_index | title     | add_time            |
+---------+----------+-----------+---------------------+
| 1495298 |  1495298 | ddsd1123d | 2019-05-15 23:13:56 |
+---------+----------+-----------+---------------------+
1 row in set (0.01 sec)

表結構

mysql> show create table article\G;
*************************** 1. row ***************************
       Table: article
Create Table: CREATE TABLE `article` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `no_index` int(11) DEFAULT NULL,
  `title` varchar(30) NOT NULL DEFAULT '',
  `add_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1572824 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR: 
No query specified

四、explain分析

使用explain可以對sql語句進行分析到底有沒有使用到索引查詢,從而更好的最佳化它.

我們只需要在select語句前面加上一句explain或者desc.

1、語法

explain|desc select * from tablename \G;

2、分析

用剛才的兩個有無索引對比看看

mysql> mysql> explain select * from article where no_index=1495298\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE//單表查詢
        table: article//查詢的表名
   partitions: NULL
         type: ALL//索引的型別，從好到壞的情況是：system>const>range>index>All
possible_keys: NULL//可能使用到的索引
          key: NULL//實際使用到的索引
      key_len: NULL//索引的長度
          ref: NULL
         rows: 1307580//可能進行掃描表的行數
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

mysql> explain select * from article where id=1495298\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: const//當對主鍵索引進行等值查詢的時候出現const
possible_keys: PRIMARY
          key: PRIMARY//實際使用到的所有primary索引
      key_len: 4//索引的長度4 = int佔4個位元組
          ref: const
         rows: 1//所掃描的行數只有一行
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

3、explain的type項分析

type項從優到差依次排序:

system:一般系統表只有一行記錄的時候才會出現
const:當對主鍵值進行等值查詢的時候會出現,如where id=666666
range:當對索引的值進行範圍查詢的時候會出現,如 where id<100000
index:當我們查詢的欄位恰好是我們索引檔案中的值,就會出現
All:最差的一種情況,需要避免.

實際測試

mysql> use mysql;
mysql> explain select * from user\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

mysql> use test;
mysql> explain select * from article where id=666666\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL

mysql> explain select * from article where id>666666\G;
mysql> explain select * from article where id<666666\G;

mysql> explain select id  from article \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 1307580
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

如果查詢的欄位在索引檔案存在，那麼就會直接從索引檔案中進行查詢，我們把這種查詢稱之為索引覆蓋查詢。

出現all，我們需要避免，因為進行全面掃描。

對於出現all的，可以給該欄位增加普通索引查詢

mysql> alter table article add key key_no_index(no_index);
Query OK, 0 rows affected (1.92 sec)
Records: 0  Duplicates: 0  Warnings: 0

type為ref，應該是關聯，但是ref是const
mysql> explain select * from article where no_index=666666\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: ref
possible_keys: key_no_index
          key: key_no_index
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

速度飛躍
mysql> select * from article where no_index=666666;
+--------+----------+-----------+---------------------+
| id     | no_index | title     | add_time            |
+--------+----------+-----------+---------------------+
| 666666 |   666666 | ddsd1123d | 2019-05-15 23:13:55 |
+--------+----------+-----------+---------------------+
1 row in set (0.00 sec)

4、使用索引的場景

1、經常出現在where後面的欄位,我們需要給他加索引

2、order by 語句使用索引的最佳化

mysql> explain select * from article order by id\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 1307580
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

mysql> explain select * from article where id >0  order by id\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 653790
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.01 sec)

ERROR: 
No query specified

可以看出，即使是使用了索引但是幾乎還是全表掃描。

加了where就少了一半

3、針對like的模糊查詢索引的最佳化

where title like ‘%keyword%’ ====>全表掃描

where title like ‘keyword%’ ===>會使用到索引查詢

給title加上鋪索引

mysql> alter table article  add key key_index(title);
Query OK, 0 rows affected (2.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table article\G;
*************************** 1. row ***************************
       Table: article
Create Table: CREATE TABLE `article` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `no_index` int(11) DEFAULT NULL,
  `title` varchar(30) NOT NULL DEFAULT '',
  `add_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `key_no_index` (`no_index`),
  KEY `key_index` (`title`)
) ENGINE=InnoDB AUTO_INCREMENT=1507299 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

因為%沒有出現在like關鍵字查詢的最左邊，所以可以使用到索引查詢

只要是like左邊出現了%，就是全表查詢

mysql> explain select * from article where title like 'a%'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: range//範圍查詢
possible_keys: key_index
          key: key_index
      key_len: 92//
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from article where title like '%a%'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: ALL//全表查詢
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1307580
     filtered: 11.11
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

4、limit語句的索引使用最佳化

針對於limit語句的最佳化，我們可以在它前面加order by 索引欄位

如果order by的欄位是索引，會先去索引檔案中查詢指定行數的資料

mysql> explain select sql_no_cache  * from article limit 90000,10 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: ALL//全表
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1307580
     filtered: 100.00
        Extra: NULL
1 row in set, 2 warnings (0.00 sec)

ERROR: 
No query specified

mysql> explain select sql_no_cache  * from article order by id  limit 90000,10 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: index
possible_keys: NULL
          key: PRIMARY//使用到了索引
      key_len: 4
          ref: NULL
         rows: 90010
     filtered: 100.00
        Extra: NULL
1 row in set, 2 warnings (0.00 sec)

ERROR: 
No query specified

另外一種針對於limit的最佳化方法:

索引覆蓋+延時關聯

原理:主要利用索引覆蓋查詢,把覆蓋索引查詢返回的id作為與我們要查詢記錄的id進行相關聯,

mysql> select sql_no_cache  * from article limit 1000000,10;
+---------+----------+----------------+---------------------+
| id      | no_index | title          | add_time            |
+---------+----------+----------------+---------------------+
| 1196579 |  1196579 | ddsd12123123ad | 2019-05-15 23:13:56 |
| 1196580 |  1196580 | ddsd121231ad   | 2019-05-15 23:13:56 |
| 1196581 |  1196581 | ddsd1212123d   | 2019-05-15 23:13:56 |
| 1196582 |  1196582 | ddsd1123123d   | 2019-05-15 23:13:56 |
| 1196583 |  1196583 | ddsd1123d      | 2019-05-15 23:13:56 |
| 1196584 |  1196584 | ddsd1123d      | 2019-05-15 23:13:56 |
| 1196585 |  1196585 | ddsd1123d      | 2019-05-15 23:13:56 |
| 1196586 |  1196586 | ddsd1123d      | 2019-05-15 23:13:56 |
| 1196587 |  1196587 | ddsd1123d      | 2019-05-15 23:13:56 |
| 1196588 |  1196588 | ddsd1123d      | 2019-05-15 23:13:56 |
+---------+----------+----------------+---------------------+
10 rows in set, 1 warning (0.21 sec)

mysql> select t1.* from article as t1 inner join (select id as pid from article  limit 10000,10) as t2 on t1.id=t2.pid;
+-------+----------+----------------+---------------------+
| id    | no_index | title          | add_time            |
+-------+----------+----------------+---------------------+
| 13058 |    13058 | ddsd12123123ad | 2019-05-15 23:13:49 |
| 13059 |    13059 | ddsd121231ad   | 2019-05-15 23:13:49 |
| 13060 |    13060 | ddsd1212123d   | 2019-05-15 23:13:49 |
| 13061 |    13061 | ddsd1123123d   | 2019-05-15 23:13:49 |
| 13062 |    13062 | ddsd1123d      | 2019-05-15 23:13:49 |
| 13063 |    13063 | ddsd1123d      | 2019-05-15 23:13:49 |
| 13064 |    13064 | ddsd1123d      | 2019-05-15 23:13:49 |
| 13065 |    13065 | ddsd1123d      | 2019-05-15 23:13:49 |
| 13066 |    13066 | ddsd1123d      | 2019-05-15 23:13:49 |
| 13067 |    13067 | ddsd1123d      | 2019-05-15 23:13:49 |
+-------+----------+----------------+---------------------+
10 rows in set (0.00 sec)

5、複合(多列)索引的最左原則(面試經常問)

只要查詢的時候出現複合索引的最左邊的欄位才會使用到索引查詢

把article表的no_index和title建立複合索引:

//給no_index和title建立一個複合索引
mysql> alter table article add key index_no_index_title(no_index,title);
Query OK, 0 rows affected (1.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

//檢視建立後的結構
mysql> show create table article\G;
*************************** 1. row ***************************
       Table: article
Create Table: CREATE TABLE `article` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `no_index` int(11) DEFAULT NULL,
  `title` varchar(30) NOT NULL DEFAULT '',
  `add_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `key_no_index` (`no_index`),
  KEY `key_index` (`title`),
  KEY `index_no_index_title` (`no_index`,`title`)
) ENGINE=InnoDB AUTO_INCREMENT=1507299 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

//刪除no_index和title的索引
mysql> alter table article drop key key_index;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table article drop key key_no_index;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table article\G;
*************************** 1. row ***************************
       Table: article
Create Table: CREATE TABLE `article` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `no_index` int(11) DEFAULT NULL,
  `title` varchar(30) NOT NULL DEFAULT '',
  `add_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_no_index_title` (`no_index`,`title`)
) ENGINE=InnoDB AUTO_INCREMENT=1507299 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

//複合索引使用情況
mysql> explain select * from article where title='ddsd1123d' and no_index=77777\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: ref
possible_keys: index_no_index_title
          key: index_no_index_title
      key_len: 97
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from article where  no_index=77777\G; 
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: ref
possible_keys: index_no_index_title
          key: index_no_index_title
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

五、慢查詢日誌

1、介紹

我們可以定義(程式設計師)一個sql語句執行的最大執行時間,如果發現某條sql語句的執行時間超過我們所規定的時間界限,那麼這條sql就會被記錄下來.

2、慢查詢具體操作

先開啟慢日誌查詢

檢視慢日誌配置

mysql> show variables like '%slow_query%';
+---------------------+--------------------------------------------------+
| Variable_name       | Value                                            |
+---------------------+--------------------------------------------------+
| slow_query_log      | OFF                                              |
| slow_query_log_file | /usr/local/mysql/data/caredeMacBook-Pro-slow.log |
+---------------------+--------------------------------------------------+
2 rows in set (0.00 sec)

開啟慢日誌查詢

mysql> set global slow_query_log=on;
Query OK, 0 rows affected (0.00 sec)

再次檢查慢日誌配置

mysql> show variables like '%slow_query%';
+---------------------+--------------------------------------------------+
| Variable_name       | Value                                            |
+---------------------+--------------------------------------------------+
| slow_query_log      | ON                                               |
| slow_query_log_file | /usr/local/mysql/data/caredeMacBook-Pro-slow.log |
+---------------------+--------------------------------------------------+
2 rows in set (0.00 sec)

去mysql配置檔案my.ini中指定sql語句的界限時間和慢日誌檔案的路徑

慢日誌的名稱，預設儲存在mysql目錄下面的data目錄下面

log-slow-queries = 'man.txt'

設定一個界限時間

long-query-time=5

重啟

六、profile工具

1、介紹

透過profile工具分析一條sql語句的時間消耗在哪裡

2、具體操作

開啟profile
執行一條SQL，(開啟之後執行的所有SQL語句都會被記錄下來

，以檢視某條sql語句的具體執行時間耗費哪裡)
根據query_id查詢到具體的SQL

例項：

//檢視profile設定
mysql> show variables like '%profil%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| have_profiling         | YES   |
| profiling              | OFF   |//未開啟狀態
| profiling_history_size | 15    |
+------------------------+-------+
3 rows in set (0.00 sec)

//開啟操作
mysql> set profiling = on;
Query OK, 0 rows affected, 1 warning (0.00 sec)

//檢視是否開啟成功
mysql> show variables like '%profil%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| have_profiling         | YES   |
| profiling              | ON    |//開啟成功
| profiling_history_size | 15    |
+------------------------+-------+
3 rows in set (0.00 sec)

具體查詢

mysql> select * from article where no_index=666666;
+--------+----------+-----------+---------------------+
| id     | no_index | title     | add_time            |
+--------+----------+-----------+---------------------+
| 666666 |   666666 | ddsd1123d | 2019-05-15 23:13:55 |
+--------+----------+-----------+---------------------+
1 row in set (0.02 sec)

mysql> show profiles;
+----------+------------+---------------------------------------------+
| Query_ID | Duration   | Query                                       |
+----------+------------+---------------------------------------------+
|        1 | 0.00150700 | show variables like '%profil%'              |
|        2 | 0.01481100 | select * from article where no_index=666666 |
+----------+------------+---------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql> show profile for query 2;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000291 |
| checking permissions | 0.000007 |
| Opening tables       | 0.012663 |//開啟表
| init                 | 0.000050 |
| System lock          | 0.000009 |
| optimizing           | 0.000053 |
| statistics           | 0.001566 |
| preparing            | 0.000015 |
| executing            | 0.000002 |
| Sending data         | 0.000091 |//磁碟上的傳送資料
| end                  | 0.000004 |
| query end            | 0.000007 |
| closing tables       | 0.000006 |
| freeing items        | 0.000037 |
| cleaning up          | 0.000010 |
+----------------------+----------+
15 rows in set, 1 warning (0.01 sec)

本作品採用《CC 協議》，轉載必須註明作者和本文連結

MySQL 索引 +explain

一、索引的介紹

二、MySQL中索引的分類

三、索引的基本操作

1、給表新增索引

2、刪除索引

刪除主鍵索引

刪除普通和唯一的索引

3、新增索引

4、有無索引對比

四、explain分析

1、語法

2、分析

3、explain的type項分析

4、使用索引的場景

1、 經常出現在where後面的欄位,我們需要給他加索引

2、order by 語句使用索引的最佳化

3、針對like的模糊查詢索引的最佳化

4、limit語句的索引使用最佳化

5、複合(多列)索引的最左原則(面試經常問)

五、慢查詢日誌

1、介紹

2、慢查詢具體操作

六、profile工具

1、介紹

2、具體操作

相關文章

1、經常出現在where後面的欄位,我們需要給他加索引