1、索引可以包含一个或者多个列的值,如果索引包含多个列的值,列的顺序很重要,mysql只能高效地使用索引的最左列前缀列。
2、索引是在存储引擎层而非服务器层实现的。
3、B-tree索引的限制:
- 如果不是按照索引的最左列开始查找,则无法使用索引;
- 不能跳过索引中的列;
- 如果查询中有某个列的范围查询,则其右边所有列都无法使用索引优化查询。
4、哈希索引的限制:
- 哈希索引只包含哈希值和行指针,而不存储字段值,所以不能使用索引中的值来避免避免读取行;
- 哈希索引数据不是按索引值顺序储存的,所以也就无法用于排序;
- 哈希索引不支持部分索引列匹配查找,因为哈希索引始终是使用索引列的全部内容来计算哈希值的;
- 哈希索引只支持等值比较查询,包括=、IN()、<=>(注意<>和<=>是不同的操作),也不支持任何范围查询,如where price<50;
- 访问哈希索引的速度非常快,除非有很多哈希冲突(不同的索引列值 ,却有很多相同的哈希值);
- 如果哈希冲突很多,一些索引维护操作的代价会很高。
5、索引的优点:
- 大大减少服务器需要扫描的数据量;
- 帮助服务器避免排序和临时表;
- 将随机I/O变为顺序I/O。
6、评价索引的“三星系统”:
- 一星:索引将相关的记录放到一起;
- 二星:如果索引中的数据顺序和查找中的排列顺序一致;
- 三星:如果索引中的列包含了查询中需要的全部列。
7、快速查找除了索引技术,还有分区技术和块级别元数据技术。
8、独立的列
如果查询的列不是独立的,则mysql不会使用索引。“独立的列”指索引列不能是表达式的一部分,也不能是函数的参数。以下查询无法使用列的索引:
select actor_id from actor where actor_id+1=5
select ... where to_days(current_date)-to_days(date_col)<=10
8、前缀索引和索引选择性
索引的选择性指不重复的索引值与数据表的记录总数的比值。前缀索引是一种能使索引更小、更快的有效办法,缺点是:mysql无法使用前缀索引做order by和group by,也无法使用前缀索引做覆盖扫描。
9、多列索引
当出现服务器对多个索引做相交操作时(通常有多个and条件),通常意味着需要一个包含所有相关列的多列索引,而不是多个独立的单列索引。
10、选择合适的索引顺序列
经验法则:将选择性最高的列放到索引最前面。有时和值的分布有关。
11、 聚簇索引
聚簇索引不是一种单独的索引类型,而是一种数据存储方式。InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。术语“聚簇”表示数据行和相邻的键值紧凑的存储在一起。
聚簇的数据优点:
- 可以把相关数据保存在一起;
- 数据访问更快;
- 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。
聚簇索引缺点:
- 聚簇数据最大限度地提高了I/O密集型应用的性能,但如果数据全部存放在内存中,则访问顺序就没那么重要了,聚簇索引也就没什么优势了;
- 插入速度严重依赖于插入顺序;
- 更新聚簇索引列的代价很高,因为会强制InnoDB将每个被更新的行移动到新的位置;
- 基于聚簇索引的表在插入新行,或者主键被更新导致需要移动行的时候,可能面临“页分裂”的问题;
- 聚簇索引可能导致全表扫描变慢,尤其是行比较稀疏的时候,或者由于页分裂导致数据存储不连续的时候;
- 二级索引(非聚簇索引)可能比想象的要更大,因为在二级索引的叶子节点包含了引用行的主键列;
- 二级索引访问需要两次索引查找,而不是一次。
向UUID主键插入行占用更多时间和空间;对于高并发负载,在InnoDB中按主键顺序插入可能会造成明显的争用,主键的上界和AUTO_INCREAMENT锁机制会成为“热点”。
12、覆盖索引
如果一个索引包含所有需要查询的字段的值,我们称之为“覆盖索引”。覆盖索引必须要存储索引列的值,而哈希索引、空间索引和全文索引都不存储索引列的值,所以mysql只能使用B-Tree索引做覆盖索引。
当发起一个索引覆盖查询时,在EXPLAIN的Extra列可以看到“Using index”的信息。
13、使用索引扫描来做排序
- 如果EXPLAIN出来的type列的值为“index”,则说明mysql使用了索引扫描来做排序;
- 只有当索引的列排序顺序和order by子句的顺序完全一致,并且所有列的排序方向(倒序或正序)都一样时,mysql才能使用索引来对结果排序;
- 如果查询需要关联多张表,则只有当order by子句引用的字段全部为第一个表时,才能使用索引做排序;
- order by子句和查找型查询的限制一样:需要满足索引的最左前缀的要求。
14、压缩(前缀压缩)索引
myisam使用前缀压缩来减少索引的大小,从而可以让更多的索引放入内存,在某些情况下极大地提高性能。默认只压缩字符串,但通过参数设置也可以对整数压缩。
15、重复索引和冗余索引
重复索引:在相同的列上按照相同的顺序创建的相同类型的索引,应当避免和移除,如:
create table test{
id int not null privary key,
a int not null,
unique(id),
index(id),
}ENGINE=InnoDB;
冗余索引和重复索引有一些不同。如果创建了索引(a,b),再创建索引(a)就是冗余索引。
16、未使用的索引
有些索引可能服务器永远也不会用到,建议删除。
17、索引和锁
InnoDB只有在访问行的时候才会对其加锁,而索引能减少InnoDB访问的行数,从而减少锁的数量。
18、选择和编写利用索引的查询时,三个原则:
- 单行访问很慢;
- 按顺序访问范围数据很快;
- 索引覆盖查询很快。