MySql 进阶教程
http://www.imooc.com/wiki/mysqladvanced/indexbasic.html
一、MySQL 索引
1.索引的概念
存储引擎用于快速查找记录的一种数据结构(排序好的快速查找)
索引就是为了提高数据查询的效率。
常见的索引类型主要有 B-Tree 索引、哈希索引、空间数据索引(R-Tree)、全文索引
- InnoDB 和 MyISAM 存储引擎可以创建 B-Tree 索引,单列或多列都可以创建索引;
- Memory 存储引擎可以创建哈希索引,同时也支持 B-Tree 索引;
- 从 MySQL5.7 开始,InnoDB 和 MyISAM 存储引擎都可以支持空间类型索引;
- InnoDB 和 MyISAM 存储可以支持全文索引(FULLTEXT),该索引可以用于全文搜索,仅限于CHAR、VARCHAR、TEXT 列。
2.索引优点
索引最大的作用是快速查找数据,除此之外,索引还有其他的附加作用。
B-Tree 是最常见的索引,按照顺序存储数据,它可以用来做 order by
和 group by
操作。因为 B-Tree 是有序的,将相关的值都存储在一起。因为索引存储了实际的列值,某些查询仅通过索引就可以完成查询,如覆盖查询。
总的来说,索引三个优点如下:
- 索引可以大大减少 MySQL 需要扫描的数据量;
- 索引可以帮助 MySQL 避免排序和临时表;
- 索引可以将随机 IO 变为顺序 IO。
但是,索引是最好的解决方案吗?任何事物都是有两面性的,索引同样如此。索引并不总是最好的优化工具
- 对于非常小的表,大多数情况,全表扫描会更高效;
- 对于中大型表,索引就非常有效;
- 对于特大型表,建索引和用索引的代价是日益增长,这时候可能需要和其他技术结合起来,如分区表。
总的来说,只有当使用索引利大于弊时,索引才是最好的优化工具。
二、B-Tree 索引类型详解
索引有很多种类型,可以为不同的应用场景提供更好的性能。在 MySQL 中,索引是在存储引擎层实现的。接下来重点介绍四种常见的索引类型:B-Tree 索引、哈希索引、空间数据索引(R-Tree)、全文索引。这部分内容分为上下两个小节,本小节重点介绍 B-Tree 索引。
1.1存储结构
B-Tree 对索引列的值是按顺序存储的,并且每一个叶子页到根的距离相同。B-Tree 索引可以加快数据查找的速度,因为存储引擎不需要全表扫描来获取数据,只要从索引的根节点开始搜索即可。
以表 customer 为例,我们来看看索引是如何组织数据的存储的。
mysql> create table customer(
id int,
last_name varchar(30),
first_name varchar(30),
birth_date date,
gender char(1),
key idx1_customer(last_name,first_name,birth_date)
);
如图,对于表中的每行数据,索引包含了 last_name、first_name 和 birth_date 的值。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wkWPCYze-1645176725285)(http://img.mukewang.com/wiki/5ec1612809734c8f06700376.jpg)]
1.2 适合 B-Tree 索引的查询类型
1.全值匹配
和索引中的所有列进行匹配,如查找姓名为 George Bush、1960-08-08 出生的客户。
mysql> explain select * from customer where first_name='George' and last_name='Bush' and birth_date='1960-08-08'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: customer
partitions: NULL
type: ref
possible_keys: idx1_customer
key: idx1_customer
key_len: 190
ref: const,const,const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
2.匹配最左前缀
只使用索引的第一列,如查找所有姓氏为 Bush 的客户:
mysql> explain select * from customer where last_name='Bush'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: customer
partitions: NULL
type: ref
possible_keys: idx1_customer
key: idx1_customer
key_len: 93
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
3.匹配列前缀
只匹配某一列的值的开头部分,如查找所有以 B 开头的姓氏的客户,这里使用了索引的第一列:
mysql> explain select * from customer where last_name like 'B%'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: customer
partitions: NULL
type: range
possible_keys: idx1_customer
key: idx1_customer
key_len: 93
ref: NULL
rows: 1
filtered: 100.00
Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
4.匹配范围值
查找所有姓氏在 Allen 和 Bush 之间的客户,这里使用了索引的第一列:
mysql> explain select * from customer where last_name between 'Allen' and 'Bush'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: customer
partitions: NULL
type: range
possible_keys: idx1_customer
key: idx1_customer
key_len: 93
ref: NULL
rows: 1
filtered: 100.00
Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
5.精确匹配某一列,并范围匹配另一列
第一列全匹配,第二列范围匹配,如查找姓氏为 Bush,名字以 G 开头的客户:
mysql> explain select * from customer where last_name='Bush' and first_name like 'G'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: customer
partitions: NULL
type: range
possible_keys: idx1_customer
key: idx1_customer
key_len: 186
ref: NULL
rows: 1
filtered: 100.00
Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
6.只访问索引的查询
只需要访问索引即可获取数据,不需要回表访问数据行,这种查询也叫覆盖索引:
mysql> explain select last_name from customer where last_name='Bush'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: customer
partitions: NULL
type: ref
possible_keys: idx1_customer
key: idx1_customer
key_len: 93
ref: const
rows: 1
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)
除了上述这些查询类型外,索引还可以用于 order by 排序操作,因为索引中的节点是有序的。如果 B-Tree 可以按照某种方式查找到数据,那么也可以按照这种方式进行排序。
1.3 B-Tree 索引的限制
如果不是按照索引的最左列开始查找数据,则无法使用索引。如查找名字为 George 的客户:
mysql> explain select * from customer where first_name='George'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: customer
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
不能跳过索引的列。如查找姓氏为 Bush,生日为 1960-08-08 的客户,这种查询只能使用索引的第一列:
mysql> explain select * from customer where last_name='Bush' and birth_date='1960-08-08'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: customer
partitions: NULL
type: ref
possible_keys: idx1_customer
key: idx1_customer
key_len: 93
ref: const
rows: 1
filtered: 100.00
Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
如果查询中有某个列的范围查询,在其右边的列都无法使用索引进行查找数据。如查找姓氏为以 B 开头,名字为 George 的客户。这个查询只能使用第一列,因为 like 是一个范围查询:
mysql> explain select * from customer where last_name like 'B%' and first_name='George'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: customer
partitions: NULL
type: range
possible_keys: idx1_customer
key: idx1_customer
key_len: 186
ref: NULL
rows: 1
filtered: 100.00
Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
三、如何高效高性能的选择使用MySQL 索引?
想要实现高性能的查询,正确的使用索引是基础。
1.独立的列
**概念:**独立的列,是指索引列不能是表达式的一部分,也不能是函数的参数。如果SQL 查询中的列不是独立的,MySQL 不能使用该索引。
案例:
下面两个查询,MySQL 无法使用id 列和 birth_date 列的索引。始终要将索引列单独放在比较符号的左侧。
mysql> select * from customer where id + 1 = 2;
mysql> select * from customer where to_days(birth_date) - to_days('2020-06-07') <= 10;
2.前缀索引
有时候需要对很长的字符列创建索引,这会使得索引变得很占空间,效率也很低下。碰到这种情况,一般可以索引开始的部分字符,这样可以节省索引产生的空间,但同时也会降低索引的选择性。
那我们就要选择足够长的前缀来保证较高的选择性,但是为了节省空间,前缀又不能太长,只要前缀的基数,接近于完整列的基数即可。
Tips:索引的选择性指,不重复的索引值(也叫基数,cardinality)和数据表的记录总数的比值,索引的选择性越高表示查询效率越高。
完整列的选择性:
mysql> select count(distinct last_name)/count(*) from customer;
+------------------------------------+
| count(distinct last_name)/count(*) |
+------------------------------------+
| 0.053 |
+------------------------------------+
不同前缀长度的选择性:
mysql> select count(distinct left(last_name,3))/count(*) left_3, count(distinct left(last_name,4))/count(*) left_4, count(distinct left(last_name,5))/count(*) left_5, count(distinct left(last_name,6))/count(*) left_6 from customer;
+--------+--------+--------+--------+
| left_3 | left_4 | left_5 | left_6 |
+--------+--------+--------+--------+
| 0.043| 0.046| 0.050| 0.051|
+--------+--------+--------+--------+
从上面的查询可以看出,当前缀长度为 6 时,前缀的选择性接近于完整列的选择性 0.053,再增加前缀长度,能够提升选择性的幅度也很小了。
创建前缀长度为6的索引:
mysql> alter table customer add index idx_last_name(last_name(6));
前缀索引可以使索引更小更快,但同时也有缺点:无法使用前缀索引做 order by 和 group by,也无法使用前缀索引做覆盖扫描。
3.合适的索引列顺序
在一个多列 B-Tree 索引中,索引列的顺序表示索引首先要按照最左列进行排序,然后是第二列、第三列等。索引可以按照升序或降序进行扫描,以满足精确符合列顺序的 order by、group by 和 distinct 等的查询需求。
索引的列顺序非常重要,在不考虑排序和分组的情况下,通常我们会将选择性最高的列放到索引最前面。
以下查询,是应该创建一个 (last_name,first_name)
的索引,还是应该创建一个(first_name,last_name)
的索引?
mysql> select * from customer where last_name = 'Allen' and first_name = 'Cuba'
我们首先来计算下这两个列的选择性,看哪个列更高。
mysql> select count(distinct last_name)/count(*) last_name_selectivity, count(distinct first_name)/count(*) first_name_selectivity from customer;
+-----------------------+------------------------+
| last_name_selectivity | first_name_selectivity |
+-----------------------+------------------------+
| 0.053 | 0.372 |
+-----------------------+------------------------+
很明显,列 first_name 的选择性更高,所以选择 first_name 作为索引列的第一列:
mysql> alter table customer add index idx1_customer(first_name,last_name);
4. 覆盖索引
如果一个索引包含所有需要查询的字段,称之为覆盖索引。由于覆盖索引无须回表,通过扫描索引即可拿到所有的值,它能极大地提高查询效率:索引条目一般比数据行小的多,只通过扫描索引即可满足查询需求,MySQL 可以极大地减少数据的访问量。
表 customer 有一个多列索引 (first_name,last_name)
,以下查询只需要访问 first_name
和last_name
,这时就可以通过这个索引来实现覆盖索引。
mysql> explain select last_name, first_name from customer\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: customer
partitions: NULL
type: index
possible_keys: NULL
key: idx1_customer
key_len: 186
ref: NULL
rows: 1
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)
当查询为覆盖索引查询时,在 explain 的 extra 列可以看到 Using index。
5.使用索引实现排序
MySQL 可以通过排序操作,或者按照索引顺序扫描来生成有序的结果。如果 explain 的 type 列的值为index,说明该查询使用了索引扫描来做排序。
order by 和查询的限制是一样的,需要满足索引的最左前缀要求,否则无法使用索引进行排序。只有当索引的列顺序和 order by 子句的顺序完全一致,并且所有列的排序方向(正序或倒序)都一致,MySQL才能使用索引来做排序。如果查询是多表关联,只有当 order by 子句引用的字段全部为第一个表时,才能使用索引来做排序。
5.1 可以通过索引进行排序的查询
- 索引的列顺序和 order by 子句的顺序完全一致:
mysql> explain select last_name,first_name from customer order by last_name, first_name, birth_date\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: customer
partitions: NULL
type: index
possible_keys: NULL
key: idx_customer
key_len: 190
ref: NULL
rows: 1
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)
- 索引的第一列指定为常量:
从 explain 可以看到没有出现排序操作(filesort):
mysql> explain select last_name,first_name from customer order by last_name, first_name, birth_date\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: customer
partitions: NULL
type: index
possible_keys: NULL
key: idx_customer
key_len: 190
ref: NULL
rows: 1
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)
- 索引的第一列指定为常量:
从 explain 可以看到没有出现排序操作(filesort):
mysql> explain select * from customer where last_name = 'Allen' order by first_name, birth_date\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: customer
partitions: NULL
type: ref
possible_keys: idx_customer
key: idx_customer
key_len: 93
ref: const
rows: 1
filtered: 100.00
Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
- 索引的第一列指定为常量,使用第二列排序:
mysql> explain select * from customer where last_name = 'Allen' order by first_name desc\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: customer
partitions: NULL
type: ref
possible_keys: idx_customer
key: idx_customer
key_len: 93
ref: const
rows: 1
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
- 索引的第一列为范围查询,order by 使用的两列为索引的最左前缀:
mysql> explain select * from customer where last_name between 'Allen' and 'Bush' order by last_name,first_name\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: customer
partitions: NULL
type: range
possible_keys: idx_customer
key: idx_customer
key_len: 93
ref: NULL
rows: 1
filtered: 100.00
Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
5.2 不能通过索引进行排序的查询
-
使用两种不同的排序方向:
mysql> explain select * from customer where last_name = 'Allen' order by first_name desc, birth_date asc
-
order by 字句引用了一个不在索引的列:
mysql> explain select * from customer where last_name = 'Allen' order by first_name, gender
-
where 条件和 order by 的列无法组成索引的最左前缀:
mysql> explain select * from customer where last_name = 'Allen' order by birth_date
-
第一列是范围查询,where 条件和 order by 的列无法组成索引的最左前缀:
mysql> explain select * from customer where last_name between 'Allen' and 'Bush' order by first_name
-
第一列是常量,第二列是范围查询(多个等于也是范围查询):
mysql> explain select * from customer where last_name = 'Allen' and first_name in ('Cuba','Kim') order by birth_date