哈希索引

  1. 在mysql中，只有memory的存储引擎显式支持哈希索引
  2. 基于哈希表的实现，只有精确匹配索引所有列的查询才有效
  3. 哈希索引自身只需存储对应的hash值，所以索引的结构十分紧凑，这让哈希索引查找的速度非常快

哈希索引的限制

1、哈希索引只包含哈希值和行指针，而不存储字段值，索引不能使用索引中的值来避免读取行
2、哈希索引数据并不是按照索引值顺序存储的，所以无法进行排序
3、哈希索引不支持部分列匹配查找，哈希索引是使用索引列的全部内容来计算哈希值
4、哈希索引支持等值比较查询，也不支持任何范围查询
5、访问哈希索引的数据非常快，除非有很多哈希冲突，当出现哈希冲突的时候，存储引擎必须遍历链表中的所有行指针，逐行进行比较，直到找到所有符合条件的行
6、哈希冲突比较多的话，维护的代价也会很高

  1. 1. 当需要存储大量的URL，并且根据URL进行搜索查找，如果使用B+树，存储的内容就会很大
  2. select id from url where url=""
  4. 2. 也可以利用将url使用CRC32做哈希，可以使用以下查询方式：
  5. select id fom url where url="" and url_crc=CRC32("")
  6. 此查询性能较高原因是使用体积很小的索引来完成查找

组合索引

当包含多个列作为索引，需要注意的是正确的顺序依赖于该索引的查询，同时需要考虑如何更好的满足排序和分组的需要—->最左匹配原则
跟where 中条件顺序无关，如：where name=”aa” and age=14 同 where age=14 and name=”aa” 一样，但是如果第一条件为范围查询的话，后面的索引就不会生效了。

聚簇索引与非聚簇索引

聚簇索引

不是单独的索引类型，而是一种数据存储方式，指的是数据行跟相邻的键值紧凑的存储在一起（索引文件和数据文件为同一个文件）

优点：

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

缺点：

1、聚簇数据最大限度地提高了IO密集型应用的性能，如果数据全部在内存，那么聚簇索引就没有什么优势
2、插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式
3、更新聚簇索引列的代价很高，因为会强制将每个被更新的行移动到新的位置
4、基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂的问题
5、聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候

非聚簇索引

数据文件跟索引文件分开存放（索引文件中记录了数据存放的地址）

覆盖索引

基本介绍

1、如果一个索引包含所有需要查询的字段的值，我们称之为覆盖索引
2、不是所有类型的索引都可以称为覆盖索引，覆盖索引必须要存储索引列的值
3、不同的存储实现覆盖索引的方式不同，不是所有的引擎都支持覆盖索引，memory不支持覆盖索引

查看方式：

1、当发起一个被索引覆盖的查询时，在explain的extra列可以看到using index的信息，此时就使用了覆盖索引
2、在大多数存储引擎中，覆盖索引只能覆盖那些只访问索引中部分列的查询。不过，可以进一步的进行优化，可以使用innodb的二级索引来覆盖查询。

优势

1、索引条目通常远小于数据行大小，如果只需要读取索引，那么mysql就会极大的较少数据访问量
2、因为索引是按照列值顺序存储的，所以对于IO密集型的范围查询会比随机从磁盘读取每一行数据的IO要少的多
3、一些存储引擎如MYISAM在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用，这可能会导致严重的性能问题
4、由于INNODB的聚簇索引，覆盖索引对INNODB表特别有用

优化小细节

  • 当使用索引列进行查询的时候尽量不要使用表达式，把计算放到业务层而不是数据库层

select actor_id from actor where actor_id=4;（推荐） select actor_id from actor where actor_id+1=5;

  • 尽量使用主键查询，而不是其他索引，因此主键查询不会触发回表查询（跟覆盖索引不冲突—只查询索引列才会有覆盖索引的概念，也不触发回表）
  • 使用前缀索引——>索引列是很长的字符串的时候，可以采用字符串的前几个字符来创建索引（尽量减少一个索引对应的列数，选择合适长度来创建前缀索引）
  • 使用索引扫描来排序
  • union all,in,or都能够使用索引，但是推荐使用in
  • 范围列可以用到索引

范围条件是：<、<=、>、>=、between
范围列可以用到索引，但是范围列后面的列无法用到索引，索引最多用于一个范围列

  • 强制类型转换会全表扫描

    1. phone字段为varchar类型
    2. explain select * from user where phone=13800001234;（不使用索引）
    3. explain select * from user where phone='13800001234';（使用索引）

  • 更新十分频繁，数据区分度不高的字段上不宜建立索引

    1. 更新会变更B+树，更新频繁的字段建议索引会大大降低数据库性能
    2. 类似于性别这类区分不大的属性，建立索引是没有意义的，不能有效的过滤数据，
    3. 一般区分度在80%以上的时候就可以建立索引，区分度可以使用 count(distinct(列名))/count(*) 来计算
  • 创建索引的列，不允许为null，可能会得到不符合预期的结果
  • 当需要进行表连接的时候，最好不要超过三张表，因为需要join的字段，数据类型必须一致
  • 能使用limit的时候尽量使用limit
  • 单表索引建议控制在5个以内

    创建索引的时候应该避免以下错误概念

  1. 索引越多越好
  2. 过早优化，在不了解系统的情况下进行优化

    索引监控

show status like ‘Handler_read%’;

参数解释

Handler_read_first：读取索引第一个条目的次数
Handler_read_key：通过index获取数据的次数
Handler_read_last：读取索引最后一个条目的次数
Handler_read_next：通过索引读取下一条数据的次数
Handler_read_prev：通过索引读取上一条数据的次数
Handler_read_rnd：从固定位置读取数据的次数
Handler_read_rnd_next：从数据节点读取下一条数据的次数