MySQL|MySQL 中这么多索引该怎么选择
目录
- 前言
- MySQL 单字段索引问题
- 组合索引
- 唯一索引和普通索引
- 总结
前言 索引的本质是存储引擎用于快速查询记录的一种数据结构。特别是数据表中数据特别多的时候,索引对于数据库的性能就愈发重要。
在数据量比较大的时候,不恰当的索引对于数据库的性能的影响是非常大的。在实际的应用中常常会遇见使用错误的索引而导致一系列问题,所以,选择正确的索引对于 MySQL 数据库来说相当重要。
下面我们就来一起聊聊在 MySQL 数据库中该怎么选择正确的索引。
在了解怎么选择索引之前,我先给你举一个例子。如果我们在字典中用拼音查询某一个字,首先我们得根据拼音字母进而找到对应的页码。索引也是这个原理。
当我们查询一条数据的时候,我们首先在索引中查询到对应的值,然后根据匹配到的索引去找到对应数据。
例如:
mysql> select name from city where fid = 1; +--------------+| name|+--------------+| 浦东新区|+--------------+1 row in set (0.00 sec)
如果我们在
fid
字段上建立索引,那么 MySQL 数据库就会使用索引找到fid = 1
的行,然后返回包含fid = 1
的行中的所有数据。对于 MySQL 数据库来说,索引是由存储引擎实现的,所以不同的存储引擎提供的索引也不一样。下面我们就来了解一下 MySQL 数据库中各种索引的优缺点。
MySQL 单字段索引问题 在 MySQL 数据库中,索引不能够使用表达式,具体如下:
mysql> explain select * from city where fid + 1 = 2; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key| key_len | ref| rows | filtered | Extra|+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+|1 | SIMPLE| city| NULL| ALL| NULL| NULL | NULL| NULL |5 |100.00 | Using where |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.01 sec)mysql> explain select * from city where fid = 1; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key| key_len | ref| rows | filtered | Extra|+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+|1 | SIMPLE| city| NULL| ALL| index_1| NULL | NULL| NULL |5 |100.00 | Using where |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)
从结果上讲,
select * from city where fid + 1 = 2;
和 select * from city where fid = 1;
是完全一致的。但是,在
explain
表达式中可以看出select * from city where fid + 1 = 2;
是无法命中索引的。这是因为 MySQL 数据库无法解析fid + 1 = 2
这个表达式,所以我们在使用索引时,索引的列不能够是一个表达式。总之,通常情况下,对于单个字段的索引来说,必须直接使用,不能够使用一个表达式。
组合索引 我们经常会遇见这样一个场景,假设要求查询
fid=1
或者name='青浦区'
,这个时候我们查询的SQL
语句如下:select * from city where fid = 1 or name = '青浦区';
这个时候,我们如果要想提高查询速度,一般就会选择在
fid
字段和name
字段上分别加上一个索引,但实际上这种做法是不恰当的。【MySQL|MySQL 中这么多索引该怎么选择】具体如下:
mysql> explain select * from city where name = '青浦区' or fid = 1; +----+-------------+-------+------------+------+-----------------+------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys| key| key_len | ref| rows | filtered | Extra|+----+-------------+-------+------------+------+-----------------+------+---------+------+------+----------+-------------+|1 | SIMPLE| city| NULL| ALL| index_1,index_2 | NULL | NULL| NULL |5 |100.00 | Using where |+----+-------------+-------+------------+------+-----------------+------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)
我们可以看出,本次查询并没有使用到任何索引。
具体步骤如下:
- 首先,根据
name
字段全表扫描查询出name = '青浦区'
包含的所有结果; - 其次,再根据
fid
字段全表扫描查询出fid = 1
包含的所有结果; - 最后,通过
UNION ALL
将所有的结果组合到一起并返回。
MySQL
的查询性能。为了解决多个字段同时需要索引的这一问题,
MySQL 5.0
之后的版本中提供了一个组合索引
。它主要是将所有的字段组合建立一个索引,这样就可以直接利用索引匹配,而不需要全表扫描了。具体如下:
mysql> explain select * from city where name = '青浦区' or fid = 1; +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+| id | select_type | table | partitions | type| possible_keys | key| key_len | ref| rows | filtered | Extra|+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+|1 | SIMPLE| city| NULL| index | index_3| index_3 | 772| NULL |5 |36.00 | Using where; Using index |+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+1 row in set, 1 warning (0.00 sec)
我们可以看出,利用了组合索引之后的查询是使用到了索引,具体如下:
- 首先,根据索引匹配出
name = '青浦区'
的所有的内容; - 第二次查询仍然是根据
fid
字段全表扫描查询出fid = 1
包含的所有结果; - 最后,通过
UNION ALL
将所有的结果组合到一起并返回。
MySQL
的查询性能。讲完单字段索引和组合索引之后,下面我们可以聊一下唯一索引和普通索引的区别以及使用场景。
唯一索引和普通索引 说起唯一索引和普通索引,有朋友可能就非常熟悉。普通索引的主要特征就是提高了查询的速度,唯一索引的主要特征除了提高查询的速度外就是所有字段的值唯一。
那么,我现在提一个问题,唯一索引和普通索引都应该在什么场景下使用呢?一定是需要唯一值的场景下才使用唯一索引吗?下面我们就来对比着聊一下普通索引和唯一索引。
为了加强了解,我们从
读写性能
方面来聊一下普通索引和唯一索引。假设现在我们有一个订单系统,订单号唯一,那么我们看一下订单号在使用唯一索引和普通索引的情况下读的性能。
具体如下:
mysql> select * from sp_order where order_id = 52355096; +----+----------+---------+--------------+-------------+-----------+------------+-------------+-------------+| id | order_id | user_id | order_number | order_price | order_pay | pay_status | create_time | update_time |+----+----------+---------+--------------+-------------+-----------+------------+-------------+-------------+|1 | 52355096 |410 | DD52355096|332.44 | 2| 1|1509051984 |1507411372 |+----+----------+---------+--------------+-------------+-----------+------------+-------------+-------------+1 row in set (0.00 sec)
在
order_id
字段上设置唯一索引时,具体步骤如下:MySQL
首先会在B-Tree
的子树上查询order_id = 52355096
;- 再根据查询到的索引值,通过主键索引查询出对应的记录;
- 组装结果并返回。
order_id
字段上设置普通索引时,具体步骤如下:MySQL
首先会在B-Tree
的子树上查询order_id = 52355096
;- 继续向下匹配,直至匹配到
order_id 不等于 52355096
时; - 再根据查询到的索引值,通过主键索引查询出对应的记录;
- 组装结果并返回。
那么,在这个过程当中,普通索引与唯一索引之间的性能差多少呢?其实是微乎其微的。这是因为
B-Tree
算法将相邻或相近的数据都放在相邻的子树之中,索引查询性能相差无几。聊完普通索引与唯一索引读的性能之后,我们再来聊一下写的性能。
具体如下:
mysql> update sp_order set order_price = '888' where order_id = 52355096;
对于
MySQL
来说,写的过程如下。- 首先判断需要修改的数据是否在
Buffer Pool
之中。
- 如果该数据在
Buffer Pool
之中,则直接修改逻辑记录到Buffer Pool
中的数据。 - 如果该数据不在
Buffer Pool
之中,MySQL 会将这一修改的过程记录在Change Buffer
之中。之后如果该条数据被查询到,则会将该修改过程merge
到Buffer Pool
之中,确保数据一致性。
- 如果该数据在
- 之后,再统一写入磁盘。
Change Buffer
了,因为在修改之前,唯一索引会将所有的数据全部读取到Buffer Pool
之中,直接在内存修改即可。但是不可避免的是,唯一索引会将所有的数据全部独到内存之中,无异于一次全表扫描。于是,我们可以得出:唯一索引和普通索引都适用于读的场景,而唯一索引不适用于写的场景。
总结 本次我从根本上给你介绍了各种索引的情况。
- 对于单个字段的索引来说,要直接使用,而不能写成一个表达式,写成表达式将会无法命中索引。
- 对于多个字段需要索引来说,一般需要创建组合索引,这样有利于命中索引,但是一定要注意组合索引的前缀性。
- 对于索引的类型,我还给你介绍了唯一索引和普通索引,在读的场景比较多的情况下普通索引和唯一索引都能胜任,不过在写场景比较多的情况下,普通索引的性能要优于唯一索引。
到此这篇关于MySQL 中这么多索引该怎么选择的文章就介绍到这了,更多相关MySQL 索引选择内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家!
推荐阅读
- 如何在ElementUI的上传组件el-upload中设置header
- 一篇文章带你了解MySQL索引下推
- 投稿|“中国最贵酒”跌去 300 亿:水井坊市值为何缩水
- 综艺|中国喜剧综艺简史
- 交换机|中科大发4万封钓鱼邮件给师生“免费送月饼”,网友(学校太会“整活”了......)
- 网络|中科大给师生们发了一封钓鱼邮件 结果3000多人上当了
- 投稿|争上市、拼产能、抢客户,中创新航“刚”上宁德时代
- Vue中的nextTick作用和几个简单的使用场景
- 中信证券(盈利驱动转向资金驱动|中信证券:盈利驱动转向资金驱动 配置紧扣两条主线)
- 消费|新中式烘焙,正面临“后浪”围剿