MySQL索引优化

目录

独立的列

前缀索引和索引选择性

多列索引

选择合适的索引列顺序

聚簇索引

覆盖索引 

使用索引扫描来做排序

压缩（前缀压缩）索引

冗余和重复索引

索引和锁

---

Innodb特殊功能：自适应哈希索引。某些索引值被使用得非常频繁的时候，会在B+ Tree的基础上再创建一个哈希索引。

独立的列

独立的列指的是索引列不能是表达式的一部分，也不能是函数的参数

例如：select * from actors where actor_id+1=5;

前缀索引和索引选择性

索引开始的部分字符。可能降低选择性，节约索引空间，提高索引效率。

对于BLOB和TEXT或者较长的Varchar必须使用前缀索引。

前缀索引的选择：较高的选择性，同时也不能太长

左前缀使用left(索引列名，前缀长度）

select count(*) AS cnt,LEFT(city,3) as pref from city_demo GROUP BY pref ORDER BY cnt DESC LIMIT 10

创建前缀索引

ADD KEY(索引列名（前缀长度））

ALTER TABLE city_demo ADD KEY(city(7));

MYSQL无法使用前缀索引做GROUP BY 和ORDER BY，也无法做覆盖扫描

多列索引

优化器评估索引的查询效率，选择查询效率最高的进行使用。用or连接两个单列索引的时候，会对两个单列索引进行扫描然后合并，如果没有一个索引能够完全执行查询，and也会使用多个索引进行合并。explain中的type为index_merge说明对索引进行了合并，这样并不好

选择合适的索引列顺序

1. 经常用的列优先【最左匹配原则】
2. 选择性（离散度）高的列优先【离散度原则】
3. 宽度小的列优先【最小空间原则】

聚簇索引

使用聚簇索引时，表数据存储在索引的叶子页中。

MySQL的聚簇索引是主键，如果没有定义主键，Innodb会选择一个唯一的非空索引替代，如果还是没有会隐式的创建聚簇索引

• 优点

1. 可以把相关数据保存在一起
2. 数据访问更快
3. 使用覆盖索引扫描的查询可以直接使用页节点中的主键值

• 缺点

覆盖索引 

如果一个索引包含（或者说覆盖）所需要查询的字段的值，就称之为覆盖索引

覆盖索引的优点

使用索引扫描来做排序

MySQL有两种方式可以生成有序的结果：通过排序操作；或者按索引顺序扫描。

只有当索引列的子句和ORDER BY子句的顺序完全一致，并且所有列排序方向都一样时，才能使用扫描索引列来进行排序。关联时，第一张表满足上面的条件。必须满足最左前缀要求。前面的列为常量时也可从第二个开始匹配。

压缩（前缀压缩）索引

MyISAM使用前缀压缩来减少索引的大小。

冗余和重复索引

重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引

 PRIMARY UNIQUE INDEX都是使用索引来实现的，一个字段上创建了三个索引

冗余索引，如果创建了索引（A,B)，那么创建(A)就是冗余的。

索引和锁

Inoodb只有在访问行的时候才会进行加锁，而索引能够减少Inoodb访问的行数，从而减少锁的数量。

InnoDB的行锁是通过给索引上的索引项加锁来实现的。只有通过索引条件进行数据检索，InnoDB才使用行级锁，否则，InnoDB 将使用表锁（锁住索引的所有记录）