MySQL中索引+explain的使用示例

171次閱讀

沒有評論

共計 17586 個字符，預計需要花費 44 分鐘才能閱讀完成。

自動寫代碼機器人，免費開通

這篇文章給大家分享的是有關 MySQL 中索引 +explain 的使用示例的內容。丸趣 TV 小編覺得挺實用的，因此分享給大家做個參考。一起跟隨丸趣 TV 小編過來看看吧。

一、索引的介紹

在 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% 
*************************** 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% 
*************************** 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)

感謝各位的閱讀！關于 MySQL 中索引 +explain 的使用示例就分享到這里了，希望以上內容可以對大家有一定的幫助，讓大家可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！

向 AI 問一下細節

丸趣 TV 網 – 提供最優質的資源集合！

正文完