Mysql 復合索引 誤區實踐
背景：很多dba在生產生活中經常會使用到
mysql的聯合索引，作者在工作中也經常遇到，本文講解下其中的一個誤區，sql語句中聯合索引必須在where條件后面按索引字段的先后順序寫嗎？下面案例將實際講解下
環境：os：centos7.4 mysql_version:mysql5.7.21
1、建表t2 創建了復合索引idx_con_update(realname,age)
CREATE TABLE `t2` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`realname` varchar(255) NOT NULL,
`age` tinyint(1) NOT NULL DEFAULT '0',
`createdAt` datetime NOT NULL,
`updatedAt` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_con_update` (`realname`,`age`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
2、插入數據
(root@localhost:mysql.sock) [test]>select * from t2;
+----+----------+-----+---------------------+---------------------+
| id | realname | age | createdAt | updatedAt |
+----+----------+-----+---------------------+---------------------+
| 1 | kitten | 20 | 2016-03-02 15:10:00 | 2016-03-08 16:28:00 |
| 2 | kitten1 | 21 | 2016-03-02 15:10:00 | 2016-03-08 16:28:00 |
| 3 | kitten2 | 22 | 2016-03-02 15:10:00 | 2016-03-08 16:28:00 |
| 4 | kitten2 | 22 | 2016-03-02 15:10:00 | 2016-03-08 16:28:00 |
| 5 | kitten3 | 23 | 2016-03-02 15:10:00 | 2016-03-08 16:28:00 |
| 6 | kitten4 | 24 | 2016-03-02 15:10:00 | 2016-03-08 16:28:00 |
| 7 | kitten5 | 25 | 2016-03-02 15:10:00 | 2016-03-08 16:28:00 |
| 8 | kitten6 | 26 | 2016-03-02 15:10:00 | 2016-03-08 16:28:00 |
| 9 | kitten7 | 27 | 2016-03-02 15:10:00 | 2016-03-08 16:28:00 |
| 10 | kitten8 | 28 | 2016-03-02 15:10:00 | 2016-03-08 16:28:00 |
| 11 | kitten9 | 29 | 2016-03-02 15:10:00 | 2016-03-08 16:28:00 |
+----+----------+-----+---------------------+---------------------+
11 rows in set (0.00 sec)
3、查看執行計劃
(root@localhost:mysql.sock) [test]>(root@localhost:mysql.sock) [test]>explain select * from t2 where realname='kitten5' and age=25\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t2
partitions: NULL
type: ref
possible_keys: idx_con_update
key: idx_con_update
key_len: 768
ref: const,const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
分析執行計劃:很明顯 查詢語句走了復合索引idx_con_update
把where條件順序反一下是否還會走idx_con_update索引呢？看下面
(root@localhost:mysql.sock) [test]>explain select * from t2 where age=25 and realname='kitten5'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t2
partitions: NULL
type: ref
possible_keys: idx_con_update
key: idx_con_update
key_len: 768
ref: const,const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.01 sec)
分析執行計劃:查詢條件順序更換之后 查詢依然走索引
總結:經常有同事、朋友咨詢我索引的事情、所以大膽使用復合索引、不用擔心索引字段的先后順序
