索引基础
主键
在
如果在创建表时没有显式地定义主键(Primary Key
- 首先表中是否有非空的唯一索引(Unique NOT NULL
) ,如果有,则该列即为主键。 - 不符合上述条件,
InnoDB 存储引擎自动创建一个6 个字节大小的指针,用户不能查看或访问。
主键的选择
在《

自增
- 唯一性:自增
ID 很容易会被暴力破解,数据迁移的时候,特别是发生表格合并这种操作的时候,会不可避免地存在冲突。UUID 则能够保证唯一性,彻底避免冲突。 - 键长度:自增字段的长度较
UUID 小很多,这会对检索的性能有较大影响。Innodb 引擎进行数据检索时,也是先根据索引找到主键,然后根据主键找到记录;这样在主键长度短的情况下,会有较好的读性能。 - 并发性:自增
ID 并且高并发的情况下,竞争自增锁会降低数据库的吞吐能力。UUID 则能够在应用层生成UUID ,提高数据库的吞吐能力。 - 数据库索引:
InnoDB 中表数据是按照主键顺序存放的,在写入数据时候如果发生了随机IO ,那么就会频繁地移动磁盘块。当数据量大的时候,写的短板将非常明显。自增ID 中新增的数据可以默认按序排列,对于性能有很大的提升;UUID 则主键之间没有顺序规律。
主键与唯一索引
主键就是唯一索引,但是唯一索引不一定是主键,唯一索引可以为空,但是空值只能有一个,主键不能为空。对于单列索引,要求该列所有数据都不相同,但允许有
对于字符串类型,可以指定索引前缀长度
前缀索引
联合索引前缀与最左匹配(Leftmost Prefix)
联合索引前缀指的是在建立多列索引的时候,必须按照从左到右的顺序使用全部或部分的索引列,才能充分的使用联合索引,比如:(col1, col2, col3)
使用 (col1)、(col1, col2)、(col1, col2, col3)
有效。在查询语句中会一直向右匹配直到遇到范围查询 (>,<,BETWEEN,LIKE)
就停止匹配,其后的索引列将不会使用索引来优化查找了。
以 (name, city, interest)
三个字段联合的索引为例,如果查询条件为 where name='Bush';
那么就只需要根据where name='Bush' and city='Chicago';
的查询,
由此我们可以得出联合索引前缀的注意点:
- 无法跨越字段使用联合索引,如
where name='Bush' and interest='baseball';
,对于该查询,name 字段是可以使用联合索引的第一个字段过滤大部分数据的,但是对于interest 字段,其无法通过B+ 树的特性直接定位第三个字段的索引片数据,比如这里的baseball 可能分散在了第二条和第七条数据之中。最终,interest 字段其实进行的是覆盖索引扫描。 - 对于非等值条件,如
> 、<、!= 等,联合索引前缀对于索引片的过滤只能到第一个使用非等值条件的字段为止,后续字段虽然在联合索引上也无法参与索引片的过滤。这里比如where name='Bush' and city>'Chicago' and interest='baseball';
,对于该查询条件,首先可以根据name 字段过滤索引片中第一个字段的非Bush 的数据,然后根据联合索引的第二个字段定位到索引片的Chicago 位置,由于其是非等值条件,这里MySQL 就会从定位的Chicago 往下顺序扫描,由于interest 字段是可能分散在索引第三个字段的任何位置的,因而第三个字段无法参与索引片的过滤。
因此Index(A,B,C)
# 使用索引
A>5 AND A<10 - 最左前缀匹配
A=5 AND B>6 - 最左前缀匹配
A=5 AND B=6 AND C=7 - 全列匹配
A=5 AND B IN (2,3) AND C>5 - 最左前缀匹配,填坑
# 不能使用索引
B>5 - 没有包含最左前缀
B=6 AND C=7 - 没有包含最左前缀
# 使用部分索引
A>5 AND B=2 - 使用索引 A 列
A=5 AND B>6 AND C=2 - 使用索引的 A 和 B 列
使用索引对结果进行排序,需要索引的顺序和
# 使用索引排序
ORDER BY A - 最左前缀匹配
WHERE A=5 ORDER BY B,C - 最左前缀匹配
WHERE A=5 ORDER BY B DESC - 最左前缀匹配
WHERE A>5 ORDER BY A,B - 最左前缀匹配
# 不能使用索引排序
WHERE A=5 ORDER BY B DESC,C ASC - 升降序不一致
WHERE A=5 ORDER BY B,D - D 不在索引中
WHERE A=5 ORDER BY C - 没有包含最左前缀
WHERE A>5 ORDER BY B,C - 第一列是范围条件,无法使用 BC 排序
WHERE A=5 AND B IN(1, 2) ORDER BY C - B 也是范围条件,无法用 C 排序
like 前缀
对于first_name like 'rMq%';
那么其是可以用到first_name like '%Chu%';
,其就无法使用first_name like 'rMq%';
,

字符串前缀
字符串前缀索引指的是只取字符串前几个字符建立的索引。在进行查询时,如果一个字段值较长,那么为其建立索引的成本将非常高,并且查询效率也比较低,字符串前缀索引就是为了解决这一问题而存在的。字符串前缀索引主要应用在两个方面:
- 字段前缀部分的选择性比较高;
- 字段整体的选择性不太大(如果字段整体选择性比较大则可以使用哈希索引
) 。
譬如为where first_name='qWhNIZqxcbD';
,那么
字符串前缀索引最需要注意的一个问题是如何选择前缀的长度,长度选择合适时,前缀索引的过滤性将和对整个字段建立索引的选择性几乎相等。这里我们就需要用到前面讲解的关于字段选择性的概念,即字段选择性为对该字段分组之后,数据量最大的组的数据量占总数据量的比例。这里选择前缀长度时,可以理解为,前缀的选择性为按照前缀分组之后,数据量最大的组占总数据量的比例。如下表所示为计算前缀长度的
select count(*) as cnt, first_name as perf from actor group by perf ORDER BY cnt desc limit 10; -- 0
select count(*) as cnt, left(first_name, 2) as perf from actor group by perf ORDER BY cnt desc limit 10; -- 2
select count(*) as cnt, left(first_name, 3) as perf from actor group by perf ORDER BY cnt desc limit 10; -- 3
select count(*) as cnt, left(first_name, 4) as perf from actor group by perf ORDER BY cnt desc limit 10; -- 4
联合索引
单列索引指的是在表上为某一个字段建立的索引,一般索引的创建选择整型或者较小的定长字符串将更有利于效率的提升。联合索引指的是多个字段按照一定顺序组织的索引。以索引 (name, city, gender)
为例,其首先是按照
常见的条件联合包括了