MySQL索引(二):建索引的原则

在了解了索引的基础知识及B+树索引的原理后(如需复习请点这里),这一节我们了解一下有哪些建索引的原则,来指导我们去建索引。

建索引的原则

1. 联合索引

我们可能听一些数据库方面的专业人士说过:“把 Where 条件里面的列都建上索引”,从而给每个列给每个列建独立的索引,这个理解是非常错误的。

如果 Where 条件里有多种组合的查询条件,可以尝试建联合索引来减少索引数量,同时提升查询性能。

2. 覆盖索引

普通索引查到主键后,回到主键索引搜索的过程,称为回表。

当使用普通索引查询时,普通索引有我们所需结果的所有信息(字段),就可以直接提供查询结果,而不需要回表。也就是说,在这个查询里,索引已经被“覆盖了”查询需求,称为覆盖索引。

mysql> create table T (
ID int primary key,
k int NOT NULL DEFAULT 0, 
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB;

select ID from T where k between 3 and 5

上面的例子中,k索引树上已经包括了ID的值,就不需要回表了。

由于覆盖索引可以减少树的搜索次数,显著提升查询性能,所以使用覆盖索引是一个常用的性能优化手段。

在使用覆盖索引时,要注意如果需要返回的字段较多,就要权衡空间和时间。因为覆盖索引要覆盖较多字段就需要更多的空间。

3. 最左前缀原则

假设我们有2个字段,例如身份证号和地址,现在有三种查询需求:按身份证号查询、按地址查询,以及按身份证号和地址查询。

如果每种需要都要创建一个索引,就显得有利浪费。

从上一篇文章里,我们了解到B+树的特点是有序的,因此我们可以只创建两个索引即满足上面的三种需求,分别是(身份证号,地址)和(地址)。这就是B+树索引的“最左前缀”原则。

在建立联合索引的时候,如何安排索引内的字段顺序:

  1. 第一原则是,如果通过调整顺序,可以少维护一个索引,那么这个顺序往往就是需要优先考虑采用的。
  2. 第二原则是,空间。当两个字段存在一个明显比另一个大时,例如name和age,明显name要比age要大,此时应该建一个(name,age)的联合索引和一个(age)的单字段索引。

4. 前缀索引和索引选择性

有时候需要索引很长的字符列,这会让索引变得大且慢。一种策略是模拟哈希索引。

通常可以索引开始的部分字符,这样可以大大节约索引空间,从而提高索引效率。但这样也会降低索引的选择性。

索引的选择性指,不重复的索引值(也称为基数,cardinality)和数据表的记录总数(T)的比值,范围从1/T到1之间。

如何确定前缀索引的长度及创建前缀索引

假设有个订单表,其中有包括城市名称的字段(city,且城市名称为英文),下面来介绍一下如何确定前缀索引的长度。

mysql> create table orders (
ID int primary key,
city varchar(16) NOT NULL
)engine=InnoDB;

一种方法是计算完整列的选择性,并使前缀的选择性接近于完整列的选择性。

计算完整列的选择性:

mysql> SELECT COUNT(DISTINCT city)/COUNT(*) FROM orders;

在同一个查询中计算不同前缀长度的选择性:

mysql> SELECT COUNT(DISTINCT LEFT(city, 3))/COUNT(*) AS sel3,
        COUNT(DISTINCT LEFT(city, 4))/COUNT(*) AS sel4,
        COUNT(DISTINCT LEFT(city, 5))/COUNT(*) AS sel5,
        COUNT(DISTINCT LEFT(city, 6))/COUNT(*) AS sel6,
        COUNT(DISTINCT LEFT(city, 7))/COUNT(*) AS sel7
    FROM orders;

创建前缀索引的方法:

mysql> ALTER TABLE orders ADD KEY (city(7));

5. 选择合适的索引顺序

在联合索引中,索引列的顺序是按照从左到右逐列进行排序的。因此索引可以按照升序或降序进行扫描,以满足精确符合列顺序的 ORDERY BY、GROUP BY 和 DISTINCT 等子句的查询需求。

因此联合索引的列顺序很重要。

如何选择索引的列顺序有一个经验法则:将选择性最高的列放到索引最前列。

这个经验法则在某些场景下有用,但可能在另外场景下,可能就没效了,这要根据具体情况进行分析。

参考资料

posted @ 2020-12-07 10:08  大杂草  阅读(686)  评论(0编辑  收藏  举报