MySql 索引
1 索引是什么
- 官方介绍索引是帮助MySQL高效获取数据的数据结构。更通俗的说,数据库索引好比是一本书前面的目录,能加快数据库的查询速度。
- 一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往是存储在磁盘上的文件中的(可能存储在单独的索引文件中,也可能和数据一起存储在数据文件中)。
- 我们通常所说的索引,包括聚集索引、覆盖索引、组合索引、前缀索引、唯一索引等,没有特别说明,默认都是使用B+树结构组织(多路搜索树,并不一定是二叉的)的索引。
2 索引的优缺点
2.1 索引优点
- 可以提高数据检索的效率,降低数据库的IO成本,类似于书的目录。
- 通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗。
被索引的列会自动进行排序,包括【单列索引】和【组合索引】,只是组合索引的排序要复杂一些。
如果按照索引列的顺序进行排序,对应order by语句来说,效率就会提高很多。
2.2 索引缺点
- 索引会占据磁盘空间。
- 索引虽然会提高查询效率,但是会降低更新表的效率。比如每次对表进行增删改操作,MySQL不仅要保存数据,还有保存或者更新对应的索引文件。
3 索引分类
3.1 单列索引
- 普通索引:MySQL中基本索引类型,没有什么限制,允许在定义索引的列中插入重复值和空值,纯粹为了查询数据更快一点。
- 唯一索引:索引列中的值必须是唯一的,但是允许为空值。
- 主键索引:是一种特殊的唯一索引,不允许有空值。
3.2 组合索引
- 在表中的多个字段组合上创建的索引。
- 组合索引的使用,需要遵循最左前缀原则(最左匹配原则,后面分享)。
- 一般情况下,建议使用组合索引代替单列索引(主键索引除外,具体原因后面分享)。
3.3 全文索引
只有在MyISAM引擎上才能使用,而且只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引。
3.4 空间索引
不做介绍,一般使用不到。
4 索引的使用
4.1 创建删除索引
- 单列索引-普通索引
CREATE INDEX index_name ON table_name(column) ;
ALTER TABLE table_name ADD INDEX index_name (column) ;
- 单列索引之唯一索引
CREATE UNIQUE INDEX index_name ON table_name(column);
ALTER TABLE table_name ADD UNIQUE INDEX index_name (column);
- 单列索引之全文索引
CREATE FULLTEXT INDEX index_name ON table(column(length)) ;
- 组合索引
ALTER TABLE table_name ADD INDEX index_name (column1,column2,...) ;
- 删除索引
DROP INDEX index_name ON table;
- 查看表索引信息
show index from table_name;
5 索引使用
5.1 为什么要优先使用组合索引?
- 为了节省mysql索引存储空间以及提升搜索性能,可建立组合索引(能使用组合索引就不使用单列索引)
创建组合索引(相当于建立了col1,col1 col2,col1 col2 col3三个索引):
ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3')
5.2 哪些情况需要创建索引?
-
主键自动建立唯一索引
-
多表关联查询中,关联字段应该创建索引
-
查询中排序的字段,应该创建索引
底层知识点:mysql创建组合索引的规则是首先会对组合索引的最左边的,也就是第一个name字段的数据进行排序,在第一个字段的排序基础上,然后再对后面第二个的cid字段进行排序。其实就相当于实现了类似 order by name cid这样一种排序规则。 -
查询中统计或者分组字段,应该创建索引
5.3 哪些情况不需要创建索引?
- 表记录太少
- 经常进行增删改操作的表
- 频繁更新的字段
- where条件里使用频率不高的字段
6 索引失效分析
MySql版本:5.6
- 表结构
CREATE TABLE `order_info` (
`id` bigint(20) NOT NULL AUTO_INCREMENT, // 唯一主键
`user_id` bigint(20) DEFAULT NULL, // 组合索引
`product_name` varchar(50) NOT NULL DEFAULT '',
`productor` varchar(30) DEFAULT NULL,
`info` varchar(32) DEFAULT NULL, // 普通索引
`name` varchar(32) DEFAULT NULL,
`type` char(1) DEFAULT NULL, // 普通索引
`pp` char(1) DEFAULT NULL,
`number` int(32) DEFAULT NULL, // 唯一索引
PRIMARY KEY (`id`),
UNIQUE KEY `index_num` (`number`),
KEY `user_product_detail_index` (`user_id`,`product_name`,`productor`),
KEY `indec_info` (`info`),
KEY `index_type` (`type`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
6.1 案例演示
6.1.1 全局匹配我最爱
mysql> explain select * from order_info where user_id = 1 and product_name = '123';
+----+-------------+------------+------+---------------------------+---------------------------+---------+-------------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------------------+---------------------------+---------+-------------+------+-----------------------+
| 1 | SIMPLE | order_info | ref | user_product_detail_index | user_product_detail_index | 161 | const,const | 1 | Using index condition |
+----+-------------+------------+------+---------------------------+---------------------------+---------+-------------+------+-----------------------+
1 row in set (0.00 sec)
6.1.2 最佳左前缀法则 - 针对组合索引
带头索引不能死,中间索引不能断,如果索引了多个列,要遵守最佳左前缀法则。指的是查询从索引的最左前列开始 并且不跳过索引中的列。
- 带头索引不能死
mysql> explain select * from order_info where user_id like '%1%' and product_name = '1';
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | order_info | ALL | NULL | NULL | NULL | NULL | 9 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
点拨:user_id带头索引失效,后面的索引则失效
- 中间索引不能断
# 索引断了
mysql> explain select * from order_info where product_name = '1';
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | order_info | ALL | NULL | NULL | NULL | NULL | 10 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from order_info where user_id = 1 and product_name = '1';
+----+-------------+------------+------+---------------------------+---------------------------+---------+-------------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------------------+---------------------------+---------+-------------+------+-----------------------+
| 1 | SIMPLE | order_info | ref | user_product_detail_index | user_product_detail_index | 161 | const,const | 1 | Using index condition |
+----+-------------+------------+------+---------------------------+---------------------------+---------+-------------+------+-----------------------+
1 row in set (0.00 sec)
# 中间索引断了,只使用到第一个索引
mysql> explain select * from order_info where user_id = 1 and productor = '1';
+----+-------------+------------+------+---------------------------+---------------------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------------------+---------------------------+---------+-------+------+-----------------------+
| 1 | SIMPLE | order_info | ref | user_product_detail_index | user_product_detail_index | 9 | const | 4 | Using index condition |
+----+-------------+------------+------+---------------------------+---------------------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
mysql> explain select * from order_info where product_name = '1' and productor = '1';
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | order_info | ALL | NULL | NULL | NULL | NULL | 10 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
- 有些同学会有这么个疑问:我把组合索引的顺序打乱,为什么索引没有失效? 原因,MySql在执行语句之前,有对Sql语句进行优化。
6.1.3 不要在索引上做计算或类型转换 - 针对类型的索引
不要进行这些操作:计算、函数、自动/手动类型转换,不然会导致索引失效而转向全表扫描
mysql> explain select * from order_info t where t.type = '1';
+----+-------------+-------+------+---------------+------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------------+---------+-------+------+-----------------------+
| 1 | SIMPLE | t | ref | index_type | index_type | 4 | const | 1 | Using index condition |
+----+-------------+-----
1 row in set (0.00 sec)
# type 为 char类型,查询字段与列类型不一致,会导致索引失效
mysql> explain select * from order_info t where t.type = 1;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t | ALL | index_type | NULL | NULL | NULL | 10 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
# 对索引使用函数 会导致索引失效
mysql> explain select * from order_info t where LEFT(t.type,1) = '1';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t | ALL | NULL | NULL | NULL | NULL | 10 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
6.1.4 范围条件右边的列失效 - 针对组合索引
- 不能继续使用索引中范围条件(<、in)右边的列
- 组合索引中 between 与 > 会导致索引失效
mysql> explain select * from order_info t where t.user_id = 1 and t.product_name = '1';
+----+-------------+-------+------+---------------------------+---------------------------+---------+-------------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------------------+---------------------------+---------+-------------+------+-----------------------+
| 1 | SIMPLE | t | ref | user_product_detail_index | user_product_detail_index | 161 | const,const | 1 | Using index condition |
+----+-------------+-------+------+---------------------------+---------------------------+---------+-------------+------+-----------------------+
1 row in set (0.00 sec)
# < 右边的列索引失效
mysql> explain select * from order_info t where t.user_id < 1 and t.product_name = '1';
+----+-------------+-------+-------+---------------------------+---------------------------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------------------+---------------------------+---------+------+------+-----------------------+
| 1 | SIMPLE | t | range | user_product_detail_index | user_product_detail_index | 9 | NULL | 1 | Using index condition |
+----+-------------+-------+-------+---------------------------+---------------------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)
# > 索引全部失效,结合<使用,可以触发索引不失效
mysql> explain select * from order_info t where t.user_id > 1 and t.product_name = '1';
+----+-------------+-------+------+---------------------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t | ALL | user_product_detail_index | NULL | NULL | NULL | 10 | Using where |
+----+-------------+-------+------+---------------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
# in 右变的列索引失效
mysql> explain select * from order_info t where t.user_id in (1, 2) and t.product_name = '1';
+----+-------------+-------+-------+---------------------------+---------------------------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------------------+---------------------------+---------+------+------+-----------------------+
| 1 | SIMPLE | t | range | user_product_detail_index | user_product_detail_index | 161 | NULL | 2 | Using index condition |
+----+-------------+-------+-------+---------------------------+---------------------------+---------+------+------+-----------------------+
1 row in set (0.01 sec)
# between 全部索引都失效
mysql> explain select * from order_info t where (t.user_id between 1 and 2) and t.product_name = '1';
+----+-------------+-------+------+---------------------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t | ALL | user_product_detail_index | NULL | NULL | NULL | 10 | Using where |
+----+-------------+-------+------+---------------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
注意: between 只有在组合索才会使索引失效。在普通索引,唯一索引,主键索引是不会导致索引失效的。 单独使用**>** 除了主键索引,其他类型的索引,都会导致索引失效;结合 **<**索引依然生效。
6.1.5 索引字段上不要使用不等
索引字段上使用(!= 或者 < >)判断时,会导致索引失效而转向全表扫描
注意:主键除外
mysql> explain select * from order_info t where t.id != 1;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | t | range | PRIMARY | PRIMARY | 8 | NULL | 10 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from order_info t where t.number != 1;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t | ALL | index_num | NULL | NULL | NULL | 10 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from order_info t where t.number <> 1;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t | ALL | index_num | NULL | NULL | NULL | 10 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
6.1.6 索引字段上使用 is null / is not null
索引使用:is null /is not null,每种类型的索引查询都有所出入,仅供参考。
- 组合索引
# 索引生效
mysql> explain select * from order_info t where t.user_id is null;
+----+-------------+-------+------+---------------------------+---------------------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------------------+---------------------------+---------+-------+------+-----------------------+
| 1 | SIMPLE | t | ref | user_product_detail_index | user_product_detail_index | 9 | const | 1 | Using index condition |
+----+-------------+-------+------+---------------------------+---------------------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
# 索引失效
mysql> explain select * from order_info t where t.user_id is not null;
+----+-------------+-------+------+---------------------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t | ALL | user_product_detail_index | NULL | NULL | NULL | 10 | Using where |
+----+-------------+-------+------+---------------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
- 普通索引
# 索引失效
mysql> explain select * from order_info t where t.type is null;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t | ALL | index_type | NULL | NULL | NULL | 10 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
# 索引生效
mysql> explain select * from order_info t where t.type is not null;
+----+-------------+-------+-------+---------------+------------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------------+---------+------+------+-----------------------+
| 1 | SIMPLE | t | range | index_type | index_type | 4 | NULL | 1 | Using index condition |
+----+-------------+-------+-------+---------------+------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)
- 唯一索引
# 索引生效
mysql> explain select * from order_info t where t.number is null;
+----+-------------+-------+------+---------------+-----------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+-----------+---------+-------+------+-----------------------+
| 1 | SIMPLE | t | ref | index_num | index_num | 5 | const | 1 | Using index condition |
+----+-------------+-------+------+---------------+-----------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
# 索引失效
mysql> explain select * from order_info t where t.number is not null;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t | ALL | index_num | NULL | NULL | NULL | 10 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
6.1.7 索引字段使用like不以通配符开头
索引字段使用like以通配符开头(’%字符串’)时,会导致索引失效而转向全表扫描
mysql> explain select * from order_info t where t.info like '1%';
+----+-------------+-------+-------+---------------+------------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------------+---------+------+------+-----------------------+
| 1 | SIMPLE | t | range | indec_info | indec_info | 99 | NULL | 1 | Using index condition |
+----+-------------+-------+-------+---------------+------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)
mysql> explain select * from order_info t where t.info like '%1%';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t | ALL | NULL | NULL | NULL | NULL | 10 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from order_info t where t.info like '%1';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t | ALL | NULL | NULL | NULL | NULL | 10 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
问题:解决like ‘%字符串%’时,索引失效问题的方法? 使用覆盖索引可以解决,一般开发过程中使用率很低。
6.1.8 in 与 or 在一些情况下会失效
- 查询字段太多会造成索引失效
6.1.9 总结
组合索引 KEY(a, b, c)
注意:仅供参考,实际情况以 执行计划为主。
where语句 | 索引是否被使用 |
---|---|
where a = 1 | 使用到a |
where a = 1 and b = 2 | 使用到a,b |
where a = 1 and b = 2 and c = 3 | 使用到a,b,c |
where b = 1 / where b = 1 and c =3 / where c = 3 | 没使用到索引 |
where a = 1 and c = 3 | 使用到a,中间索引中断了,没使用到c |
where a = 1 and b > 1 and c = 3 | 使用a和b, c 不能在范围之后,到b断了 |
where a = 1 and b like ’k%‘ and c = 3 | 使用到a,c,c |
where a = 1 and b like ’k%‘ and c = 3 | 只使用到a |
where a = 1 and b like ’%k%‘ and c = 3 | 只是用到a |
where a = 1 and b like ’%k‘ and c = 3 | 只用到a |
where a = 1 and b like k’%k%‘ and c = 3 | 使用到a,b,c |