Mysql联合索引
write by donaldhan, 2018-03-28 22:04目录
引言
本文不打算介绍Mysql索引的使用原理，只是记录当使用联合索引的时候，什么情况使用索引，什么情况下不用索引，以及模糊查询什么情况下走索引， 什么情况下不走索引。
测试联合索引
首先创建用户表：
CREATE TABLE `userx` (
`id` int(11) NOT NULL auto_increment COMMENT 'id',
`name` varchar(10) default NULL,
`age` int(11) default NULL,
`registerTime` timestamp NULL default NULL on update CURRENT_TIMESTAMP,
KEY `user_id_name` (`id`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
注意，表中用的id和name的联合索引。使用EXPLAIN查看SQL的执行过程，Explain命令可以参考：MySQL中EXPLAIN解释命令。 首先插入测试数据如下：
mysql> select * from userx;
+----+--------+-----+---------------------+
| id | name | age | registerTime |
+----+--------+-----+---------------------+
| 1 | jamel | 27 | 2018-03-07 22:21:33 |
| 2 | donald | 29 | 2018-03-28 22:21:54 |
+----+--------+-----+---------------------+
2 rows in set
mysql>
执行如下sql：
mysql> EXPLAIN SELECT * FROM userx WHERE id = 1 AND name = 'jamel';
+----+-------------+-------+------+---------------+--------------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+--------------+---------+-------------+------+-------------+
| 1 | SIMPLE | userx | ref | user_id_name | user_id_name | 37 | const,const | 1 | Using where |
+----+-------------+-------+------+---------------+--------------+---------+-------------+------+-------------+
1 row in set
mysql> EXPLAIN SELECT * FROM userx WHERE name = 'jamel' AND id = 1;
+----+-------------+-------+------+---------------+--------------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+--------------+---------+-------------+------+-------------+
| 1 | SIMPLE | userx | ref | user_id_name | user_id_name | 37 | const,const | 1 | Using where |
+----+-------------+-------+------+---------------+--------------+---------+-------------+------+-------------+
1 row in set
mysql> EXPLAIN SELECT * FROM userx WHERE id = 1;
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-------+
| 1 | SIMPLE | userx | ref | user_id_name | user_id_name | 4 | const | 1 | |
+----+-------------+-------+------+---------------+--------------+---------+-------+------+-------+
1 row in set
mysql> EXPLAIN SELECT * FROM userx WHERE name = 'jamel';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | userx | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set
mysql>
从上面可以看出，在联合索引的条件下，如果使用联合索引中的所有索引列做查询条件和第一个联合索引列做查询，都会使用索引， 而使用第二个联合索引列做查询，不会使用索引。
测试左连接操作
我们再来测试一下左连接： 创建用户表：
CREATE TABLE `user` (
`id` int(11) NOT NULL auto_increment COMMENT 'id',
`name` varchar(10) default NULL,
`age` int(11) default NULL,
`registerTime` timestamp NULL default NULL on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
创建用户邮件表:
CREATE TABLE `user_email` (
`user_id` int(11) NOT NULL,
`emailInfo` varchar(100) default NULL,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
用户表的id和用户邮件表的用户id我们都设为主键。
执行如下sql
mysql> EXPLAIN SELECT * FROM user u LEFT JOIN user_email e ON u.id = e.user_id ;
+----+-------------+-------+--------+---------------+---------+---------+-----------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-----------+------+-------+
| 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 3 | |
| 1 | SIMPLE | e | eq_ref | PRIMARY | PRIMARY | 4 | test.u.id | 1 | |
+----+-------------+-------+--------+---------------+---------+---------+-----------+------+-------+
2 rows in set
mysql> EXPLAIN SELECT * FROM user u LEFT JOIN user_email e ON u.id = e.user_id ORDER BY e.user_id;
+----+-------------+-------+--------+---------------+---------+---------+-----------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-----------+------+---------------------------------+
| 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 3 | Using temporary; Using filesort |
| 1 | SIMPLE | e | eq_ref | PRIMARY | PRIMARY | 4 | test.u.id | 1 | |
+----+-------------+-------+--------+---------------+---------+---------+-----------+------+---------------------------------+
2 rows in set
mysql>
从上面的执行结果来看，在执行左连接的时候，左表不会使用到索引，但我们加上Order左表id时，也不会使用索引。
模糊查询索引问题
我们在模糊查询的情况，先前我们在用户表中已经给name字段加了一般非聚集索引，执行如下sql：
mysql> EXPLAIN SELECT * FROM user WHERE name = 'jamel';
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| 1 | SIMPLE | user | ref | name | name | 33 | const | 1 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
1 row in set
mysql> EXPLAIN SELECT * FROM user WHERE name like '%jam';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | user | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set
mysql> EXPLAIN SELECT * FROM user WHERE name like 'jamel%';
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | user | range | name | name | 33 | NULL | 1 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set
mysql> EXPLAIN SELECT * FROM user WHERE name like '%jamel%';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | user | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set
从上面可以出，对于模糊查询，左模糊和全模糊不走一般索引，右模糊走一般索引，针对不走索引的情况，我们可以创建对应的全文索引。 具体参考mysql全文索引。
总结
在联合索引的条件下，如果使用联合索引中的所有索引列做查询条件和第一个联合索引列做查询，都会使用索引， 而使用第二个联合索引列做查询，不会使用索引。创建复合索引时，应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时，复合索引非常有用； 仅对后面的任意列执行搜索时，复合索引则没有用处。在执行左连接的时候，左表不会使用到索引，我们加上Order左表的连接字段强制走索引，也不会走索引。 对于模糊查询，左模糊和全模糊不走一般索引，右模糊走一般索引，针对不走索引的情况，我们可以创建对应的全文索引。