高性能数据库索引策略-优快云博客

本文链接：https://blog.youkuaiyun.com/RamboKitty/article/details/85251231

索引随笔

文章目录

索引随笔

索引分析

EXPLAIN命令可以查看sql语句的执行策略
例如：

EXPLAIN select fil_id,actor_id from film_actor
      where actor_id =1 or file_id =1
***********************************************
select_type:simple
table:film_actor
type: index_merge  #索引合并
possible_key : PRIMARY,idx_fk_film_id
key: PRIMARY,idx_fk_film_id
key_len :2,2
ref: null
rows: 29
Extra: Using union(PRIMARY,idx_fk_film_id);Using where

索引优点

索引大大减少了服务器需要扫描的数据量
索引可以帮助服务器避免排序和临时表
索引可以将随机I/O变为顺序I/O

索引顺序

如下建表语句:

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)
)

索引中包含了last_name,first_name和dob列值，其对多个值进行排序的依据是create table语句中定义索引时列的顺序，第一个列值一样则根据第二个列值进行排序，第二个也一样则根据第三个列值进行排序

索引限制

如果不是按照索引的最左列开始查找，则无法使用索引，例如上面的表结构，无法通过索引查找first_name=bill的人，也无法查找某个特定生日的人，因为这两列都不是最左索引数据列，类似的也无法查找last_name以某个字母结尾的人。
不能跳过索引中的列，也就是无法用于查找last_name='smith’并且在某个特定日期出生的人，如果不指定first_name 则Mysql 只能使用索引的第一列
如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找，例如

where last_name='smith' and first_name list 'J%' and dob='1975-02-06'

这个查询只能使用索引的前两列，无法使用使用dob索引

高性能索引策略

1. 独立的列

是指索引列不能是表达式的一部分，也不能是函数的参数，不然索引会被忽略

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

索引列不能是很长的字符列，这会让索引变得大且慢，过于长的列可以采用索引字符前缀或者后缀。
索引选择性是指，不重复的索引值（也称为基数）和数据表的记录总数（T）的比值,范围从1/T 到1之间，比值越高则查询效率越高，因为选择性高的索引可以让mysql在查找时过滤掉更多的行，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

3. 多列索引

多列索引并不是为每个列创建独立的索引，或按照错误的顺序创建多列索引。
一个错误的示例：

Create table t(
   c1  INT,
   c2  INT,
   C3  INT,
   KEY(c1),
   KEY(c2),
   KEY(c3)

);

索引合并：

大多数情况下多个列上建立独立的单列索引并不能提高mysql的查询性能。多列分别建立索引会导致索引数据后进行合并操作。
索引合并策略有时候是一种优化的结果，但实际上更多的时候说明了表上的索引建的很糟糕。
索引合并会消耗大量的CPU和内存资源。
例如：

select * from t where c1=1 and c2=2

c1索引后的数据会与c2索引后的数据进行交集操作

多列索引

Create table t(
   c1  INT,
   c2  INT,
   c3  INT,
   KEY(c1,c2,c3)
);

全覆盖索引

索引列覆盖需要的所有列，查询只需要扫描索引而无需回表

explain extra字段 = using index 表示使用覆盖索引

4. 选择合适的索引列顺序

通常情况下将选择性最高的列放到索引最前列，但是不如避免随机IO和排序那么重要，当不需要考虑排序和分组时是很好的。

5. 聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储的方式，InnoDB的聚簇索引实际上是同一个结构中保存了B-Tree索引和数据行，当表有聚簇索引时，它的数据行实际上存放在索引的叶子页，聚簇表示数据行和相邻的键值紧凑的存储在一起，因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

优点

可以把相关数据保存在一起，例如实现电子邮箱时，可以根据用户ID来聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件
数据访问更快，聚簇索引将索引和数据保存在同一个B-tree中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找块
使用覆盖索引扫描的查询可以直接使用页节点中的主键值

缺点

聚簇数据最大限度的提高了I/O密集型应用的性能，但如果数据全部都放在内存中。则访问的顺序也就没那么重要了，聚簇索引也就没什么优势
插入速度严重依赖与插入顺序
更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置
基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临"页分裂"的问题。
聚簇索引可能导致全表扫描变慢
二级索引可能比想象的要更大因为在二级索引的叶子节点包含了引用行的主键列
二级索引访问需要两次索引查找，而不是一次

第一次二级索引到主键ID,第二次聚簇索引根据ID拿到行数据

InnoDB的主键就是聚簇索引，其他索引为二级索引，二级索引存储的是主键值，并以此作为指向行的指针，通过非聚簇索引查询需要进行两次索引查找，第一次二级索引查询到主键id，第二次聚簇索引查询到行数据。使用主键值作为指针会让二级索引占用更多的空间，换来的好处是Innodb在移动的时候无需更新二级索引中的这个指针。

对于InnoDb，自适应哈希索引能够减少这样的重复工作

create table layout_test
(
     col1 int not null,
     col2 int not null,
     primary key(col1),//聚簇索引
     key(col2)//第二索引
)

优化

没有什么数据需要聚集，那么可以定义一个代理键作为主键，最简单的方式是使用AUTO_INCREMENT自增列，这样可以保证数据行是按顺序写入，对于根据主键做关联操作的性能也会更好
最好避免随机的聚簇索引，特别是对于IO密集型的应用，例如从性能的角度考虑，使用UUID来作为聚簇索引会很糟糕，它使得聚簇索引的插入变得完全随机

6. 使用索引扫描来做排序

只有当索引的列顺序和order by子句的顺序完全一致，并且所有列的排序反向都一样时，mysql才能够使用索引来对结果做排序。如果查询需要关联多张表，则只有当order by子句引用的字段全部为第一个表时，才能使用索引做排序。

有一种情况order by 子句可以不满足索引的最左前缀的要求，就是前导列为常量的时候。

例如 index_test('name','age','brithday')

select * from table where name='test' order by age, brithday

其前导列 name为常量 order by 子句就可以忽略最左前缀的要求 进行索引排序

索引列都是正序排序的

7.避免冗余、重复、未使用的索引

8.索引和锁

索引能够减少InnoDB访问的行数，从而减少锁的数量，但这只有当InnoDB在存储引擎层能够过滤掉所有不需要的行时才有效。