第五章创建高性能的索引

最新推荐文章于 2024-10-02 06:14:43 发布

sigma65535

最新推荐文章于 2024-10-02 06:14:43 发布

阅读量260

点赞数

分类专栏：《高性能MYSQL》文章标签： mysql

本文链接：https://blog.csdn.net/u010301542/article/details/78054553

版权

《高性能MYSQL》专栏收录该内容

2 篇文章 0 订阅

订阅专栏

索引是存储引擎用于快速找到记录的一种数据结构。对于良好的性能非常关键。
是对查询性能优化最有效的手段了。

5.1 索引基础

MYSQL现在索引中找到对应的值，然后根据匹配的索引记录找到对应的数据行。

5.1.1 索引的类型

索引是在存储引擎层实现的。
B-Tree索引
值按顺序存储，每个叶子页到根的距离相同。适合超找范围数据。
不需要全表扫描，而从索引的根节点开始进行搜索。
查询类型：全键值，键值范围或前缀查找。

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) #索引
);

索引的限制：
1.必须从最左列开始查找，否则无法使用索引。people表必须从last_name开始查找
2.不能跳过索引中的列。不能跳过first_name列，否则从最左列开始索引(last_name)
哈希索引
只有精确地匹配索引中的每一列，查询才有效。

5.2 索引的优点

1.减少服务器扫描的数据量
2.避免排序和临时表
3.将随机IO编程顺序IO
索引并不是最好的解决方案：
1.非常小的表，全表扫描更高效。
2.非常大的表，例如TB级别，使用分表更好。

5.3 高性能索引策略

5.3.1 独立的列
查询中必须是独立的列才能使用索引。
独立的列：列不能是表达式的一部分，也不能是函数的参数：
Ex:

mysql> SELECT actor_id from sakila.actor WHERE actor_id + 1 = 5;

不能使用actor_id索引，
应改为actor_id = 4

5.3.2 前缀索引和索引选择性

BLOB,TEXT，或者很长的VARCHAR类型的列，必须使用前缀索引。

5.3.3 多列索引

在多个列上建立独立的单独索引大部分情况下并不能提高MYSQL的查询性能。

5.3.4 索引顺序选取原则：经验法则，将选择性最高的列放在最前面

5.3.5 聚簇索引

聚簇索引是一种数据存储方式。InnoDB的聚簇索引实际是在同一个结构中保存了B-Tree索引和数据行。
表有聚簇索引时，数据行实际存放在索引的叶子页中。
一个表只能有一个聚簇索引。

5.3.6覆盖索引

定义：包含所有需要查询字段的值得索引。
查询只需要扫描索引，不需要返回。
好处：
• 索引条目常远小于数据行大小，极大地减少数据访问量。对缓存的负载非常重要。
• 索引按照列值顺序存储，可以减少IO开销
• 覆盖索引对InnoDB表特别有效
不是所有类型的索引都可以成为覆盖索引。覆盖索引必须存储索引列的值。
MYSQL只能使用B-Tree索引做覆盖索引。
MYSQL不能在索引中执行LIKE操作。

5.3.7使用索引扫描来排序

两种生成有序结果的方式：
1 通过排序操作。
2 按索引顺序扫描【explain出来的type列的值为”index”】
由于索引不能覆盖查询所有列，故比顺序全表扫描慢。
索引排序的必要条件：
索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序的方向[倒序或正序]都一致的条件下
大多数情况下都不需要冗余索引和重复索引，表中的索引越多插入速度会越慢。