mysql核心知识之联合索引

最新推荐文章于 2024-05-15 01:34:38 发布

爱卿呀

最新推荐文章于 2024-05-15 01:34:38 发布

阅读量213

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/weixin_43432438/article/details/124935877

版权

mysql 专栏收录该内容

26 篇文章 0 订阅

订阅专栏

什么是联合索引？

联合索引又称组合索引或者复合索引，是建立在俩列或者多列以上的索引。

怎么来创建联合索引？

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)

注意点总结：

索引并非越多越好，过多的索引会增加数据的维护速度还有磁盘空间的浪费。

• 当表的数据量很大的时候，可以考虑建立索引。

• 表中经常查数据的字段，可以考虑建立索引。

• 想要保证表中数据的唯一性，可以考虑建立唯一索引。

• 想要保证俩张表中的数据的完整性跟准确性，可以考虑建立外键约束。

• 经常对多列数据进行查询时，可以考虑建立联合索引。