在Mysql中使用索引

MySQL查询的优化是个老生常谈的问题，方法更是多种多样，其中最直接的就是创建索引.

这里通过一个简单的demo来实际用一下索引，看看索引在百万级别查询中速率的提升效果如何

所需数据可以从我前面的一篇博客中获取:https://www.cnblogs.com/wangbaojun/p/11154515.html

有一张salaries,

　　查看表结构如下：　

mysql> desc salaries;
+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| emp_no    | int(11) | NO   | PRI | NULL    |       |
| salary    | int(11) | NO   |     | NULL    |       |
| from_date | date    | NO   | PRI | NULL    |       |
| to_date   | date    | NO   |     | NULL    |       |
+-----------+---------+------+-----+---------+-------+

　　可以看到emp_no，from_date都是PRI（主键索引），这是在这个表中将这两个字段联合起来设置为主键，一张表中还是只能有一个主键

　　查看表的创建命令：

　　

mysql> show create table salaries;
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                                       |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| salaries | CREATE TABLE `salaries` (
  `emp_no` int(11) NOT NULL,
  `salary` int(11) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`,`from_date`),
  CONSTRAINT `salaries_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

 　　注意我标红的地方，将emp_no`,`from_date设置为组合主键,组合索引遵从左前缀原则，查询emp_no，或者查询(`emp_no`,`from_date`)会走索引，但是查from_date不会走索引,可以看一下用explain命令查看:

mysql> explain select * from salaries where emp_no=227694;
+----+-------------+----------+------+---------------+---------+---------+-------+------+-------+
| id | select_type | table    | type | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+----------+------+---------------+---------+---------+-------+------+-------+   
|  1 | SIMPLE      | salaries | ref  | PRIMARY       | PRIMARY | 4       | const |   18 | NULL  |               # key为PRIMARY 走了索引
+----+-------------+----------+------+---------------+---------+---------+-------+------+-------+
1 row in set (0.01 sec)

mysql>  explain  select * from salaries where from_date = '1986-06-26';
+----+-------------+----------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+----------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | salaries | ALL  | NULL          | NULL | NULL    | NULL | 2838426 | Using where |          key为Null，Extra 使用了where，没走索引
+----+-------------+----------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

mysql> explain  select * from salaries where from_date = '1986-06-26' and emp_no=75047;
+----+-------------+----------+-------+---------------+---------+---------+-------------+------+-------+
| id | select_type | table    | type  | possible_keys | key     | key_len | ref         | rows | Extra |
+----+-------------+----------+-------+---------------+---------+---------+-------------+------+-------+
|  1 | SIMPLE      | salaries | const | PRIMARY       | PRIMARY | 7       | const,const |    1 | NULL  |    # key为PRIMARY 走了索引

 +----+-------------+----------+-------+---------------+---------+---------+-------------+------+-------+ 
1 row in set (0.00 sec)

　　to_date字段没有设置索引，我们来测试一下加索引前后，该字段查询效率会不会有提升：

　　未加索引：

　　

mysql> select * from salaries where to_date = '1986-06-26';                                                                                                                                          +--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  25676 |  40000 | 1985-06-26 | 1986-06-26 |
|  28757 |  40000 | 1985-06-26 | 1986-06-26 |
|  30860 |  64620 | 1985-06-26 | 1986-06-26 |
|  69209 |  40000 | 1985-06-26 | 1986-06-26 |
|  80550 |  45292 | 1985-06-26 | 1986-06-26 |
|  91204 |  47553 | 1985-06-26 | 1986-06-26 |
|  96140 |  52908 | 1985-06-26 | 1986-06-26 |
| 208352 |  42989 | 1985-06-26 | 1986-06-26 |
| 213109 |  90133 | 1985-06-26 | 1986-06-26 |
| 217498 |  80247 | 1985-06-26 | 1986-06-26 |
| 219462 |  83880 | 1985-06-26 | 1986-06-26 |
| 223150 |  40000 | 1985-06-26 | 1986-06-26 |
| 227694 |  73897 | 1985-06-26 | 1986-06-26 |
| 232856 |  73126 | 1985-06-26 | 1986-06-26 |
| 237619 |  56982 | 1985-06-26 | 1986-06-26 |
| 244087 |  40000 | 1985-06-26 | 1986-06-26 |
| 253472 |  72004 | 1985-06-26 | 1986-06-26 |
| 257395 |  40000 | 1985-06-26 | 1986-06-26 |
| 261811 |  40000 | 1985-06-26 | 1986-06-26 |
| 268968 |  40000 | 1985-06-26 | 1986-06-26 |
| 269331 |  40000 | 1985-06-26 | 1986-06-26 |
| 274805 |  40000 | 1985-06-26 | 1986-06-26 |
| 279432 |  74530 | 1985-06-26 | 1986-06-26 |
| 285685 |  83198 | 1985-06-26 | 1986-06-26 |
| 286745 |  44082 | 1985-06-26 | 1986-06-26 |
| 290901 |  49876 | 1985-06-26 | 1986-06-26 |
| 400719 |  79168 | 1985-06-26 | 1986-06-26 |
| 401448 |  49600 | 1985-06-26 | 1986-06-26 |
| 427374 |  40000 | 1985-06-26 | 1986-06-26 |
| 432024 |  40000 | 1985-06-26 | 1986-06-26 |
| 432654 |  40000 | 1985-06-26 | 1986-06-26 |
| 438461 |  44451 | 1985-06-26 | 1986-06-26 |
| 446228 |  42733 | 1985-06-26 | 1986-06-26 |
| 447391 |  62381 | 1985-06-26 | 1986-06-26 |
| 448823 |  40000 | 1985-06-26 | 1986-06-26 |
| 452355 |  40000 | 1985-06-26 | 1986-06-26 |
| 453590 |  61615 | 1985-06-26 | 1986-06-26 |
| 456521 |  40000 | 1985-06-26 | 1986-06-26 |
| 464415 |  48955 | 1985-06-26 | 1986-06-26 |
| 467901 |  52349 | 1985-06-26 | 1986-06-26 |
| 472895 |  40000 | 1985-06-26 | 1986-06-26 |
| 476501 |  40000 | 1985-06-26 | 1986-06-26 |
| 477079 |  40000 | 1985-06-26 | 1986-06-26 |
| 478934 |  55054 | 1985-06-26 | 1986-06-26 |
| 480301 |  44177 | 1985-06-26 | 1986-06-26 |
| 484507 |  40000 | 1985-06-26 | 1986-06-26 |
| 486187 |  40000 | 1985-06-26 | 1986-06-26 |
| 491159 |  46034 | 1985-06-26 | 1986-06-26 |
| 493154 |  40000 | 1985-06-26 | 1986-06-26 |
| 498140 |  81909 | 1985-06-26 | 1986-06-26 |
| 498565 |  72853 | 1985-06-26 | 1986-06-26 |
+--------+--------+------------+------------+
51 rows in set (1.08 sec)

　　用explain分析一下:

　　

mysql> explain select * from salaries where to_date = '1986-06-26';
+----+-------------+----------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+----------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | salaries | ALL  | NULL          | NULL | NULL    | NULL | 2838426 | Using where |         # Extra使用where。key为Null，在2838426条数据中找51条记录用时1.08s
+----+-------------+----------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

　　

　　为to_date字段加索引：

mysql> create index to_date on salaries(to_date);
Query OK, 0 rows affected (5.31 sec)
Records: 0  Duplicates: 0  Warnings: 0 　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　创建索引会耗时，索然提升了查询速率，但是更新添加动作会效率降低

　现在看一下表结构：

　　

mysql> desc salaries;
+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| emp_no    | int(11) | NO   | PRI | NULL    |       |
| salary    | int(11) | NO   |     | NULL    |       |
| from_date | date    | NO   | PRI | NULL    |       |
| to_date   | date    | NO   | MUL | NULL    |       |            MUL表示非唯一索引
+-----------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> show create table salaries;
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                                                                    |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| salaries | CREATE TABLE `salaries` (
  `emp_no` int(11) NOT NULL,
  `salary` int(11) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`,`from_date`),
  KEY `to_date` (`to_date`),                                                                                # 创建了索引key为to_date
  CONSTRAINT `salaries_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

　　　再次查询：

　　　

mysql> select * from salaries where to_date = '1986-06-26';
+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  25676 |  40000 | 1985-06-26 | 1986-06-26 |
|  28757 |  40000 | 1985-06-26 | 1986-06-26 |
|  30860 |  64620 | 1985-06-26 | 1986-06-26 |
|  69209 |  40000 | 1985-06-26 | 1986-06-26 |
|  80550 |  45292 | 1985-06-26 | 1986-06-26 |
|  91204 |  47553 | 1985-06-26 | 1986-06-26 |
|  96140 |  52908 | 1985-06-26 | 1986-06-26 |
| 208352 |  42989 | 1985-06-26 | 1986-06-26 |
| 213109 |  90133 | 1985-06-26 | 1986-06-26 |
| 217498 |  80247 | 1985-06-26 | 1986-06-26 |
| 219462 |  83880 | 1985-06-26 | 1986-06-26 |
| 223150 |  40000 | 1985-06-26 | 1986-06-26 |
| 227694 |  73897 | 1985-06-26 | 1986-06-26 |
| 232856 |  73126 | 1985-06-26 | 1986-06-26 |
| 237619 |  56982 | 1985-06-26 | 1986-06-26 |
| 244087 |  40000 | 1985-06-26 | 1986-06-26 |
| 253472 |  72004 | 1985-06-26 | 1986-06-26 |
| 257395 |  40000 | 1985-06-26 | 1986-06-26 |
| 261811 |  40000 | 1985-06-26 | 1986-06-26 |
| 268968 |  40000 | 1985-06-26 | 1986-06-26 |
| 269331 |  40000 | 1985-06-26 | 1986-06-26 |
| 274805 |  40000 | 1985-06-26 | 1986-06-26 |
| 279432 |  74530 | 1985-06-26 | 1986-06-26 |
| 285685 |  83198 | 1985-06-26 | 1986-06-26 |
| 286745 |  44082 | 1985-06-26 | 1986-06-26 |
| 290901 |  49876 | 1985-06-26 | 1986-06-26 |
| 400719 |  79168 | 1985-06-26 | 1986-06-26 |
| 401448 |  49600 | 1985-06-26 | 1986-06-26 |
| 427374 |  40000 | 1985-06-26 | 1986-06-26 |
| 432024 |  40000 | 1985-06-26 | 1986-06-26 |
| 432654 |  40000 | 1985-06-26 | 1986-06-26 |
| 438461 |  44451 | 1985-06-26 | 1986-06-26 |
| 446228 |  42733 | 1985-06-26 | 1986-06-26 |
| 447391 |  62381 | 1985-06-26 | 1986-06-26 |
| 448823 |  40000 | 1985-06-26 | 1986-06-26 |
| 452355 |  40000 | 1985-06-26 | 1986-06-26 |
| 453590 |  61615 | 1985-06-26 | 1986-06-26 |
| 456521 |  40000 | 1985-06-26 | 1986-06-26 |
| 464415 |  48955 | 1985-06-26 | 1986-06-26 |
| 467901 |  52349 | 1985-06-26 | 1986-06-26 |
| 472895 |  40000 | 1985-06-26 | 1986-06-26 |
| 476501 |  40000 | 1985-06-26 | 1986-06-26 |
| 477079 |  40000 | 1985-06-26 | 1986-06-26 |
| 478934 |  55054 | 1985-06-26 | 1986-06-26 |
| 480301 |  44177 | 1985-06-26 | 1986-06-26 |
| 484507 |  40000 | 1985-06-26 | 1986-06-26 |
| 486187 |  40000 | 1985-06-26 | 1986-06-26 |
| 491159 |  46034 | 1985-06-26 | 1986-06-26 |
| 493154 |  40000 | 1985-06-26 | 1986-06-26 |
| 498140 |  81909 | 1985-06-26 | 1986-06-26 |
| 498565 |  72853 | 1985-06-26 | 1986-06-26 |
+--------+--------+------------+------------+
51 rows in set (0.00 sec)                                        # 创建索引后同样的查询条件从1.08s变为了0.00s，惊讶吧

　　explain分析：

　　

mysql> explain select * from salaries where to_date = '1986-06-26';
+----+-------------+----------+------+---------------+---------+---------+-------+------+-------+
| id | select_type | table    | type | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+----------+------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | salaries | ref  | to_date       | to_date | 3       | const |   51 | NULL  |       key从Null变味了索引字段to_date，row从两百多万变为了51
+----+-------------+----------+------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

　　这个demo从数据上直观的体现了索引带来的查询效率提升有多可观,但是索引也是有利必有害，更多索引的底层知识可以参考这位大牛的博客：https://www.cnblogs.com/Aiapple/p/5693239.html