概念、要点

概念:

  • 索引(在MySQL中也叫键)是存储引擎用于快速找到记录的一种数据结构。

索引的类型

不同的存储引擎的索引的工作方式并不一样,也不是所有的存储引擎都支持所有类型的索引,即使类型一样,底层实现也可能不同。
  • B-Tree索引
  • 哈希索引
  • 空间数据索引(R-Tree)
  • 全文索引
  • 其他类型索引

要点

  • 表中数据量非常大时,索引对性能的影响非常重要。
  • 索引应该是对查询性能优化最有效的手段了。
  • 索引可以包含一个或多个列的值。如果索引包含多个列,那么列的顺序十分重要,因为MySQL只能高效地使用索引的最左前缀列

索引的原理

1. B-Tree索引

若没说索引类型,一般指的是B-Tree索引,使用B-Tree数据结构来存储数据,意味着所有的值都是按顺序存储的(很适合查找范围数据),并且每一个叶子页到根的距离相同。

1
2
3
4
5
6
7
8
# 若有如下数据表
CREATE TABLE People {
last_name varchar(50) not null,
first_name varchar(50) not null,
dob date not null,
gender enum('m', 'f') not null,
key(last_name, first_name, dob)
};
  • 索引对多个值进行排序的依据是create table语句中定义索引时列的顺序。
  • MySQL适用的查找类型为 全值匹配匹配最左前缀匹配列前缀匹配范围值精确匹配某一列并范围匹配另外一列只访问索引的查询

2. 哈希索引

哈希索引(hash index)基于哈希表实现,只有精确匹配索引所有列的查询才有效。哈希索引将所有的哈希码存储在索引中,同时在哈希表中保存指向每个数据行的指针。

  • 只有Memory引擎显式支持哈希索引。

3. 空间数据索引

空间索引会从所有维度来索引数据。MySQL的GIS支持并不完善,所以大部分人都不会使用这个特性。

4. 全文索引

索引的应用

1. B-Tree索引

  • 创建数据库并建立如下数据表
    image
  • 给表中插入数据
    image
使用索引的几种情况
1. 全值匹配
1
SELECT * FROM high_performance_mysql.People WHERE last_name = "Allen" AND first_name = "Cuba" AND dob = "1960-01-01";

image

2. 匹配最左前缀
1
EXPLAIN SELECT * FROM high_performance_mysql.People WHERE last_name = "Allen";

image

3. 匹配列前缀
1
EXPLAIN SELECT * FROM high_performance_mysql.People WHERE last_name LIKE 'J%';

有问题 TODO

4. 匹配范围值
5. 精确匹配某一列并范围匹配另外一列
1
EXPLAIN SELECT * FROM high_performance_mysql.People WHERE last_name = "Allen" AND first_name LIKE "K%";

image

B-Tree索引的限制
根据索引的底层原理使用,就很容易想通为什么有这些限制了。
  • 如果不是按照索引的最左前列开始查找,则无法使用索引。
  • 不能跳过索引中的列。如果跳过,则只是用索引的第一列。
  • 如果查询中有某个列的范围查询,则其右边所有列都无法使用索引优化查找。

问题记录

  1. like ‘A%’ 和 like ‘J%’
    EXPLAIN SELECT * FROM high_performance_mysql.People WHERE last_name LIKE ‘A%’ 当数据库中A%数据占数据表很大部分时,走索引和不走索引效率区别不大,索引没有走索引。
    一种情况使用到索引,一种情况没有使用到索引,猜测是否用索引可能和结果集大小也有关,mysql内部做优化。
    在表数据量足够大情况下,才走索引。
  2. 底层实现原理弄懂,才能真正明白一些应用和限制的原因。
  3. SQL 查询性能分析 https://blog.csdn.net/moqiang02/article/details/42433525