索引

索引是对数据库表中一个或多个列的值进行排序的结构,建立索引有助于快速获取信息。

你也可以这样理解,数据库的索引类似于书籍的索引。 在书籍中,索引允许用户不必翻阅完整个书就能迅速地找到所需要的信息。 在数据库中,索引也允许数据库程序迅速地找到表中的数据,而不必扫描整个数据库。

存储类型

B-Tree 索引

InnoDB 使用的是 B+ Tree。

B+ Tree 每一个叶子节点都包含指向下一个叶子节点的指针,从而方便叶子节点的范围遍历。

B+ Tree 通常意味着所有的值都是按顺序存储的,并且每一个叶子页到根的距离相同,很适合查找范围数据。

B-Tree 可以对 < <= = > >= BETWEEN IN 以及不以通配符开始的 LIKE 使用索引。

Hash 索引

哈希索引基于哈希表实现,只有精确索引所有列的查询才有效。

对于每一行数据,存储引擎都会对所有的索引列计算一个哈希码。 哈希索引将所有的哈希码存储在索引中,同时在哈希表中保存指向每个数据的指针。

空间索引

MyISAM 支持空间索引,主要用于地理空间数据类型,例如 GEOMETRY。

全文索引

全文索引是 MyISAM 的一个特殊索引类型,它查找的是文本中的关键词,主要用于全文检索。

索引使用

建立索引类型

  • 单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。
  • 组合索引,即一个索包含多个列。

索引是在存储引擎中实现的,而不是在服务器层中实现的。 所以,每种存储引擎的索引都不一定完全相同,并不是所有的存储引擎都支持所有的索引类型。

普通索引

普通索引是最基本的索引,它没有任何限制。 普通索引(由关键字 KEY 或 INDEX 定义的索引)的唯一任务是加快对数据的访问速度。 因此,应该只为那些最经常出现在查询条件 (WHERE column = …) 或排序条件 (ORDER BY column) 中的数据列创建索引。

  • 创建:CREATE INDEX indexName ON mytable(username(length))

如果是 CHAR VARCHAR 类型,length 可以小于字段实际长度;如果是 BLOB 和 TEXT 类型,必须指定 length,下同。

  • 修改表结构:ALTER mytable ADD INDEX [indexName] ON (username(length))
  • 创建表的时候直接指定:CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) )
  • 删除:DROP INDEX [indexName] ON mytable
唯一索引

它与前面的普通索引类似,不同的是普通索引允许被索引的数据列包含重复的值,唯一索引列的值必须唯一,但允许有空值。 如果是组合索引,则列值的组合必须唯一。

  • 创建:CREATE UNIQUE INDEX indexName ON mytable(username(length))
  • 修改表结构:ALTER mytable ADD UNIQUE [indexName] ON (username(length))
  • 创建表的时候直接指定:CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)) );
主键索引

它是一种特殊的唯一索引,不允许有空值。一个表只能有一个主键。

一般是在建表的时候同时创建主键索引: CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) );

当然也可以用 ALTER 命令。

与之类似的,外键索引。 如果为某个外键字段定义了一个外键约束条件,MariaDB 就会定义一个内部索引来帮助自己以最有效率的方式去管理和使用外键约束条件。

组合索引

为了进一步提升效率,就要考虑建立组合索引。就是将多个字段建到一个索引里。

建立这样的组合索引,其实是相当于分别建立了下面三组组合索引:

MariaDB 的组合索引使用最左前缀。

建立索引的时机

一般来说,在 WHERE 和 JOIN 中出现的列需要建立索引,但也不完全如此。 因为 B-Tree 只对 < <= = > >= BETWEEN IN 以及不以通配符开始的 LIKE 使用索引。

正确使用索引

使用索引时,有以下一些技巧和注意事项:

  • 索引字段尽量使用数字型(简单的数据类型)

若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。 这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。

  • 尽量不要让字段的默认值为 NULL

含有空值的列很难进行查询优化,因为它们使得索引、索引的统计信息以及比较运算更加复杂。 索引不会包含有 NULL 值的列,复合索引中只要有一列含有 NULL 值,那么这一列对于此复合索引就是无效的。 在数据库设计时尽量不要让字段的默认值为 NULL,应该指定列为 NOT NULL,除非你想存储 NULL。 你应该用 0、特殊的值或者空串代替空值。

  • 前缀索引和索引选择性

对串列进行索引,如果可能应该指定一个前缀长度。

对于 BLOB、TEXT 或者很长的 VARCHAR 类型的列,必须使用前缀索引,因为 MariaDB、MySQL 不允许索引这些列的完整长度。

前缀索引是一种能使索引更小、更快的有效办法,但另一方面也有其缺点。 MariaDB、MySQL 无法使用前缀索引做 order by 和 group by,也无法使用前缀索引做覆盖扫描。

一般情况下某个前缀的选择性也是足够高的,足以满足查询性能。

例如,如果有一个 CHAR(255) 的列,如果在前 10 个或 20 个字符内,多数值是惟一的,那么就不要对整个列进行索引。

短索引不仅可以提高查询速度而且可以节省磁盘空间和 I/O 操作。 绝大多数数据库中的字符串数据大都以各种各样的名字为主,把索引的长度设置为10~15个字符已经足以把搜索范围缩小到很少的几条数据记录了。

通常可以索引开始的部分字符,这样可以大大节约索引空间,从而提高索引效率。但这样也会降低索引的选择性。 索引的选择性是指,不重复的索引值(基数)和数据表中的记录总数的比值。 索引的选择性越高则查询效率越高,因为选择性高的索引可以让MYSQL在查找时过滤掉更多的行。 唯一索引的选择性是 1,这是最好的索引选择性,性能也是最好的。 决窍在于要选择足够长的前缀以保证较高的选择性,同时又不能太长,以便节约空间。 前缀应该足够长,以使得前缀索引的选择性接近于索引整个列。 换句话说,前缀的基数应该接近于完整列的基数。

  • 使用唯一索引

考虑某列中值的分布。索引的列的基数越大,索引的效果越好。 例如,存放出生日期的列具有不同值,很容易区分各行。 而用来记录性别的列,只含有 M 和 F,则对此列进行索引没有多大用处,因为不管搜索哪个值,都会得出大约一半的行。

  • 使用组合索引代替多个列索引

一个组合索引与多个列索引的解析执行是不一样的,如果在 explain 中看到有索引合并,应该好好检查一下查询的表和结构是不是已经最优。

  • 重复、冗余、不使用的索引

MariaDB、MySQL 允许在相同的列上创建多个索引,无论是有意还是无意的。大多数情况下不需要使用冗余索引。 对于重复、冗余、不使用的索引可以直接删除这些索引。因为这些索引需要占用物理空间,并且也会影响更新表的性能。

  • 如果对大的文本进行搜索,使用全文索引而不要用使用 like ‘%…%’

  • like 语句不要以通配符开头

对于 like,在以通配符 %_ 开头作查询时,MariaDB、MySQL 不会使用索引。 like 操作一般在全文索引中会用到,当然,InnoDB 不支持全文索引。 这句会使用索引:SELECT * FROM mytable WHERE username like'admin%' ,而这句就不会使用:SELECT * FROM mytable WHEREt Name like'%admin'

  • 不要在列上进行运算

索引列不能是表达式的一部分,也不是是函数的参数。例如以下两个查询无法使用索引:

  1. 表达式:select actor_id from sakila.actor where actor_id+1=5;
  2. 函数参数:select ... where TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col)<=10;

  3. 尽量不要使用 NOT IN、<>、!= 操作

应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。

对于 not in,可以用 not exists 或者外联结加判断为空来代替。 很多时候用 exists 代替 in 是一个好的选择:

select num from a where num in(select num from b)
select num from a where exists(select 1 from b where num=a.num)

对于 <>,用其它相同功能的操作运算代替,如 a<>0 改为 a>0 or a<0

  • or 条件

用 or 分割开的条件,如果 or 前的条件中的列有索引,而后面的列中没有索引,那么涉及到的索引都不会被用到。 应尽量避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如:

select id from t where num=10 or num=20 
select id from t where num=10 union all select id from t where num=20
  • 组合索引的使用要遵守最左前缀原则
    CREATE TABLE People (
      last_name varchar(50) not null,
      first_name varchar(50) not null,
      birthday date not null,
      gender enum(‘m', 'f') not null,
      key(1ast_name, first_name, birthday)
    );
    

当不需要考虑排序和分组时,将选择性最高的列放在前面通常是最好的。

  1. 查询必须从索引的最左边的列开始,否则无法使用索引。例如,你不能直接利用索引查找在某一天出生的人。

  2. 不能跳过某一索引列。例如,你不能利用索引查找 last name 为 Smith 且出生于某一天的人。

  3. 存储引擎不能使用索引中范围条件右边的列。例如,如果你的查询语句为 WHERE last_name="Smith" AND first_name LIKE 'J%' AND dob='1976-12-23' ,则该查询只会使用索引中的前两列,因为 LIKE 是范围查询。

  • 使用索引排序时,ORDER BY 也要遵守最左前缀原则

  • 当索引的顺序与 ORDER BY 中的列顺序相同,且所有的列是同一方向(全部升序或者全部降序)时,可以使用索引来排序。

  • ORDER BY 子句和查询型子句的限制是一样的,需要满足索引的最左前缀的要求。 有一种情况下 ORDER BY 子句可以不满足索引的最左前缀要求,那就是前导列为常量时,WHERE 或者 JOIN 子句中对前导列指定了常量。

  • 如果查询是连接多个表,仅当 ORDER BY 中的所有列都是第一个表的列时才会使用索引。其它情况都会使用 filesort 文件排序。

  • 如果列类型是字符串,那么一定记得在 where 条件中把字符常量值用引号引起来,否则的话即便有索引也不会用到的。 因为 MariaDB、MySQL 默认把输入的常量值进行转换以后才进行检索。

  • 任何地方都不要使用 select * from t ,用具体的字段列表代替 *,不要返回用不到的任何字段。

  • 如果 MariaDB、MySQL 估计使用索引比全表扫描更慢,则不使用索引。 当索引列有大量数据重复时,查询可能不会去利用索引,如表中有字段性别,男女几乎各一半,即使在建了索引也对查询效率起不了作用。

性能测试与优化

只有当数据库里已经有了足够多的测试数据时,它的性能测试结果才有实际参考价值。 如果在测试数据库里只有几百条数据记录,它们往往在执行完第一条查询命令之后就被全部加载到内存了。 这将使后续的查询命令都执行得非常快,不管有没有使用索引。 只有当数据库里的记录超过了 1000 条、数据总量也超过了服务器上的内存总量时,数据库的性能测试结果才有意义。

在不确定应该在哪些数据列上创建索引的时候,从 EXPLAIN SELECT 命令那里往往可以获得一些帮助。 这其实只是简单地给一条普通的 SELECT 命令加一个 EXPLAIN 关键字作为前缀而已。 有了这个关键字,数据库将不是去执行那条 SELECT 命令,而是去对它进行分析, 以表格的形式把查询的执行过程和用到的索引等信息列出来,如果有的话。

查看索引使用情况:show status like 'Handler_read%'

如果索引正在工作,Handler_read_key 的值将很高。 这个值代表了一个行被索引值读的次数,很低的值表明增加索引得到的性能改善不高,因为索引并不经常使用。

Handler_read_rnd_next 的值高则意味着查询运行低效,并且应该建立索引补救。 这个值的含义是在数据文件中读下一行的请求数。 如果正进行大量的表扫描, Handler_read_rnd_next 的值较高,则通常说明表索引不正确或写入的查询没有利用索引。

优缺点

优点

  • 索引减小了服务器需要扫描的数据量
  • 索引可以帮助服务器避免排序和临时表
  • 索引可以将随机IO变成顺序IO
  • 索引对于 InnoDB 非常重要,因为它可以让查询锁更少的元组
    • InnoDB 在二级索引上使用共享锁,但访问主键索引需要排他锁

缺点

  • 虽然索引提高了查询速度,同时却会降低更新速度,因为不仅要保存数据,还要保存索引文件
  • 建立索引会占用磁盘空间的索引文件。如果在一个大表上创建了多种组合索引,索引文件的会膨胀很快
  • 如果某个数据列包含许多重复的内容,为它建立索引就没有太大的实际效果
  • 对于非常小的表,大部分情况下简单的全表扫描更高效

索引只是提高效率的一个因素,如果有大数据量的表,就需要花时间研究建立最优秀的索引或优化查询语句。 因此应该只为最经常查询和最经常排序的数据列建立索引。

powered by Gitbook该文件修订时间: 2020-04-10 10:05:54

results matching ""

    No results matching ""

    results matching ""

      No results matching ""