mysql索引学习1

1. 索引概述

1. 什么是索引

   索引是帮助数据库快速检索数据的一种数据结构。mysql中，存储引擎首先在索引中找到对应值，然后根据匹配的索引记录找到对应的数据行。索引可以包含一个或多个列，如果索引包含多个列(联合索引)，那么列的顺序十分重要。

---

2. 索引类型

1. 索引有多种类型，mysql中索引是在存储引擎层实现而不是服务器器层，所以不同的存储引擎的索引的工作方式并不一样，不是所有的存储引擎都支持所有类型的索引，即使多个存储引擎支持同一种索引，其底层实现也可能不同。
2. B-tree索引
   1. 不同的存储引擎有着不同的存储结构，如NDB集群存储引擎实际使用T-tree结构存储索引，而innodb则使用b+ tree。
   2. 适用查询类型
      1. 全值匹配
         • 匹配所有的索引列，如 select xx from yy where a = A and b = B and c = C，匹配联合索引(a,b,c)
      2. 匹配最左前缀
         • 联合索引的最左前缀匹配，如 select xx from yy where a = A ，匹配联合索引(a,b,c)
      3. 匹配列前缀
         • 字符串类型的列的前缀匹配(匹配字符串的前缀)
      4. 匹配范围值
         • 范围查询，如 select xx from yy where a > A1 and a < A2
      5. 精确匹配某一列并范围匹配另一列
         • 联合索引的最左前缀匹配，如 select xx from yy where a = A and b > B and c = C，匹配联合索引(a,b,c)，只会用到(a,b)索引
      6. 只访问索引的查询(索引覆盖)
         • 查询访问的是索引列，直接进行索引覆盖，避免回表的操作
      7. 缺点
         1. 若不是按照索引列的顺序(最左前缀匹配)，则无法使用索引
         2. 不能跳过索引中的列
         3. 如果查询中有某个列的范围查询，则该列之后的索引列无法使用
         4. insert和delete操作会引起B+ tree结果的分裂和合并操作，导致操作性能下降
   3. hash索引
      1. 适应场景：适合等值查询(hash表数据结构)
      2. 缺点：
         1. 无法满足联合索引的最左前缀匹配
         2. 无法进行索引覆盖
         3. 不适用于范围查询
         4. 不适用于数据的排序(hash的特点为让数据尽可能的分散)
         5. 当hash碰撞频繁时，数据的检索效率会下降

3. 索引的优点

1. 索引一般能大大减少mysql服务器扫描的数据量
2. 索引可以帮助服务器减少排序和临时表
3. 索引可以将随机IO转化为顺序IO
4. 索引是最好的性能解决方案吗？
   • 索引不总是最好的工具，只有当索引可以帮助存储引擎减少搜寻的数据量时带来的收益大于其带来的额外工作，索引才是有效的。

4. 创建高性能的索引

1. 独立的列

   如果查询的列不是独立的，则mysql不会使用索引，独立的列指：不能是表达式的一部分或者函数的参数。

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

   当索引列为很长的字符串时，索引会变大且慢，此时可以使用hash索引，但有时并不能很好的解决问题，我们可以索引列的前一部分字符(即字符串前缀)，这样减少了索引的占用空间，但是索引的选择性降低了，索引选择性为不重复的索引值和数据表的记录总数之比，选择性越高的索引，效率越高，因为可以过滤更多的数据行，如唯一索引(unique index)的选择性为1，性能最好。所以选择性最高索引应置为最左的列。