文章目录
- 定义
- 操作背景
- 分类
- 普通单值索引
- 唯一索引
- 复合索引
- 索引DDL操作
- 创建索引
- 删除索引
- 查看索引
- 索引的优势
- 索引的劣势
- 什么时候需要建索引
- 哪些情况不宜建立索引
- 索引失效的情况
定义
MYSQL官方对索引的定义为：索引（Index）是一种帮助MYSQL高效获取数据的数据结构。一般来说，索引的本身也比较大，不可能全部存储在内存中，因此索引往往以文件的形式存储在磁盘上。
操作背景
下文中所有的操作都基于这两张表，student，class表，表结构和索引如下
mysql> desc student;
+---------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+----------------+
| id | bigint | NO | PRI | NULL | auto_increment |
| name | varchar(9) | YES | | NULL | |
| age | int | YES | | NULL | |
| classId | bigint | NO | | NULL | |
+---------+------------+------+-----+---------+----------------+
4 rows in set (0.02 sec)
mysql> desc class;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | bigint | NO | PRI | NULL | auto_increment |
| name | varchar(32) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.03 sec)
mysql>分类
普通单值索引
一个索引只包含单个列，一个表可以有多个单值索引
唯一索引
索引列的值必须唯一，但允许有空值
复合索引
一个索引有多个索引列，在使用时必须遵循最左匹配原则。
索引DDL操作
创建索引
- CREATE INDEX 方式
语法：CREATE [UNIQUE] INDEX INDEXNAME ON TABLE_NAME(COLUMNNAME(length));
tips: 如果是CHAR/VARCHAR类型，length可以小于字段实际长度，如果是BLOB和TEXT类型，必须制定length。
mysql> create index idx_classId_age on student(classId,age);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>- ALTER TABLE 方式
语法：ALTER TABLE [TABLE_NAME] ADD INDEX INDEX_NAME
mysql> alter table student add index idx_classId_age(classId,age);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>删除索引
- 方式一 ALTER TABLE
语法: ALTER TABLE [TABLE_NAME] DROP INDEX [INDEX_NAME];
mysql> alter table student drop index idx_classId_age;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0- 方式二: DROP INDEX
语法:DROP INDEX [INDEX_NAME] ON [TABLE_NAME]
mysql> DROP INDEX IDX_CLASSID_AGE ON STUDENT;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>查看索引
语法: SHOW INDEX FROM [TABLE_NAME]
mysql> show index from student;
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | | |
| student | 1 | idx_classId_age | 1 | classId | A | 3 | NULL | NULL | | BTREE | | |
| student | 1 | idx_classId_age | 2 | age | A | 4 | NULL | NULL | YES | BTREE | | |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.03 sec)
mysql>索引的优势
- 提高了数据的检索效率，降低了数据库的IO成本
- 降低了数据排序的成本，降低了CPU的消耗
索引的劣势
- 索引本质上也是一种数据表，该表保存了主键与索引字段，并指向实体表的记录，需要占用一定的空间。
- 降低了更新插入数据的效率，因为除了要保存数据意外，还需要额外的维护索引树。
什么时候需要建索引
- 1.主键会自动建立唯一索引
- 2.频繁作为查询条件的字段应该创建索引
- 3.查询中需要与其他表做关联的字段应当建立索引(外键关系)
- 4.如果可以创建复合索引的情况，优先创建复合索引
- 5.查询中需要排序的字段应当建立索引
- 6.查询中统计或者分组的字段应当建立索引
哪些情况不宜建立索引
- 1.表的记录太少，没有必要建立索引
- 2.频繁增删改的表没必要建立索引，原因见劣势（索引会降低更新表的速度），当然实际情况需要根据业务去寻找一个平衡。
- 数据重复且分布平均的表字段，例如性别,最多也就3个值，在这种字段上建立索引意义不大，没有必要建立索引浪费空间。
索引失效的情况
在以下情况下会导致我们建立的索引失效，变成全表扫描，从而大大偏离我们的预期结果，应当避免。
- 1.在索引上做任何操作(计算，函数，（显式或隐式）类型转换)，会导致索引失效而转向全表扫描。
1.索引字段上做运算操作，引起索引失效
mysql> explain select * from student where (age) = 2;
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | student | NULL | ref | idx_age | idx_age | 5 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------+
1 row in set (0.08 sec)
mysql> explain select * from student where (age-1) = 2;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set (0.06 sec)2.函数运算导致索引失效
mysql> explain select * from student where name = '张三';
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | student | NULL | ref | idx_name | idx_name | 30 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set (0.07 sec)
mysql> explain select * from student where left(name,2) = '张三';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set (0.07 sec)3.类型的隐式转换导致索引失效（如字符串类型字段，查询时值得带引号）
mysql> explain select * from student where name = 2;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | student | NULL | ALL | idx_name | NULL | NULL | NULL | 4 | 25.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set (0.07 sec)- 在存储索引擎中不能使用索引中范围条件右边的列（左值匹配原则）
mysql> show index from student;
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | | |
| student | 1 | idx_classId_age | 1 | classId | A | 3 | NULL | NULL | | BTREE | | |
| student | 1 | idx_classId_age | 2 | age | A | 4 | NULL | NULL | YES | BTREE | | |
| student | 1 | idx_name | 1 | name | A | 4 | NULL | NULL | YES | BTREE | | |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.03 sec)
mysql> explain select * from student where age = 3;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set (0.04 sec)
mysql>- 尽量使用覆盖索引，减少select *
例如我们查询student表中的某些数据项，如果使用select * 则无法使用索引
mysql> explain select * from student;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set (0.03 sec)更改为查询某一部分（select 后面列出字段）使用覆盖索引即可以解决问题。
mysql> explain select classId,age from student;
+----+-------------+---------+------------+-------+---------------+-----------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+-----------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | student | NULL | index | NULL | idx_classId_age | 9 | NULL | 4 | 100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+-----------------+---------+------+------+----------+-------------+
1 row in set (0.02 sec)
mysql>- 在查询中使用不等于(!=或<>)的时候会导致索引失效
mysql> show index from student;
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | | |
| student | 1 | idx_classId_age | 1 | classId | A | 3 | NULL | NULL | | BTREE | | |
| student | 1 | idx_classId_age | 2 | age | A | 4 | NULL | NULL | YES | BTREE | | |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.03 sec)
mysql> explain select * from student where classId <> 2;
+----+-------------+---------+------------+------+-----------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+-----------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | student | NULL | ALL | idx_classId_age | NULL | NULL | NULL | 4 | 75.00 | Using where |
+----+-------------+---------+------------+------+-----------------+------+---------+------+------+----------+-------------+
1 row in set (0.03 sec)
mysql>- is null,is not null 也会导致索引失效
mysql> explain select * from student where classId is not null;
+----+-------------+---------+------------+------+-----------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+-----------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | student | NULL | ALL | idx_classId_age | NULL | NULL | NULL | 4 | 75.00 | Using where |
+----+-------------+---------+------------+------+-----------------+------+---------+------+------+----------+-------------+
1 row in set (0.03 sec)
mysql>- 尽量少用or或者in，用它来连接时会导致索引失效
mysql> explain select * from student where classId = 1 or classId = 2;
+----+-------------+---------+------------+------+-----------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+-----------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | student | NULL | ALL | idx_classId_age | NULL | NULL | NULL | 4 | 100.00 | Using where |
+----+-------------+---------+------------+------+-----------------+------+---------+------+------+----------+-------------+
1 row in set (0.02 sec)
mysql>- 字符串不加单引号导致索引失效(本质上做了数据类型的隐式转换运算)
mysql> show index from student;
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | | |
| student | 1 | idx_classId_age | 1 | classId | A | 3 | NULL | NULL | | BTREE | | |
| student | 1 | idx_classId_age | 2 | age | A | 4 | NULL | NULL | YES | BTREE | | |
| student | 1 | idx_name | 1 | name | A | 4 | NULL | NULL | YES | BTREE | | |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.03 sec)
mysql> explain select * from student where name = 2;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | student | NULL | ALL | idx_name | NULL | NULL | NULL | 4 | 25.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set (0.02 sec)
mysql> explain select * from student where name = '2';
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | student | NULL | ref | idx_name | idx_name | 30 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set (0.01 sec)由上面明显可以看到，当以name作为查询条件时，如果不给后面的2加上单引号，就会导致全表扫描，而加上单引号以后，就会使用索引idx_name。
- like 以通配符开头(‘%abc…’')会导致索引失效，从而变成全表扫描
mysql> explain select * from student where name like '张%';
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | student | NULL | range | idx_name | idx_name | 30 | NULL | 2 | 100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
1 row in set (0.03 sec)
mysql> explain select * from student where name like '%张%';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set (0.05 sec)
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
