什么是联合索引?
联合索引又称组合索引或者复合索引,是建立在俩列或者多列以上的索引。
怎么来创建联合索引?
alter table 表名 add index(字段1,字段2,字段3);
mysql> desc kk;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| empno | int(11) | NO | | 0 | |
| ename | varchar(50) | YES | | NULL | |
| job | varchar(50) | YES | | NULL | |
| mgr | int(11) | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | decimal(7,2) | YES | | NULL | |
| deptnu | int(11) | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
7 rows in set (0.04 sec)
mysql> alter table kk add index(ename,job,deptnu);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc kk;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| empno | int(11) | NO | | 0 | |
| ename | varchar(50) | YES | MUL | NULL | |
| job | varchar(50) | YES | | NULL | |
| mgr | int(11) | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | decimal(7,2) | YES | | NULL | |
| deptnu | int(11) | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
7 rows in set (0.04 sec)
mysql> show create table kk;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------+
| kk | CREATE TABLE `kk` (
`empno` int(11) NOT NULL DEFAULT '0',
`ename` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
`job` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
`mgr` int(11) DEFAULT NULL,
`hiredate` date DEFAULT NULL,
`sal` decimal(7,2) DEFAULT NULL,
`deptnu` int(11) DEFAULT NULL,
KEY `ename` (`ename`,`job`,`deptnu`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+-------+---------------------------------------------------------------+
1 row in set (0.04 sec)
怎么删除联合索引?
alter table test drop index 索引名;
为什么要使用联合索引,而不使用多个单列索引?
联合索引的效率远远高于单列索引
联合索引的最左原则
只使用最左边的索引,当搜索的字段不包括联合索引中最左的字段,将不使用联合索引
mysql> explain select * from kk where ename='小乔' and job='文员' and deptnu='20';
+----+-------------+-------+------------+------+---------------+-------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------------------+------+----------+-------+
| 1 | SIMPLE | kk | NULL | ref | ename | ename | 311 | const,const,const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-------+---------+-------------------+------+----------+-------+
1 row in set (0.04 sec)
mysql> explain select * from kk where job='文员' and deptnu='20';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | kk | NULL | ALL | NULL | NULL | NULL | NULL | 8 | 12.50 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set (0.03 sec)
注意点总结:
索引并非越多越好,过多的索引会增加数据的维护速度还有磁盘空间的浪费。
• 当表的数据量很大的时候,可以考虑建立索引。
• 表中经常查数据的字段,可以考虑建立索引。
• 想要保证表中数据的唯一性,可以考虑建立唯一索引。
• 想要保证俩张表中的数据的完整性跟准确性,可以考虑建立外键约束。
• 经常对多列数据进行查询时,可以考虑建立联合索引。