Mysql -- 索引

索引

索引介绍

• 索引是什么
  • 索引（Index）是帮助MySQL高效获取数据的数据结构。索引的实质即是，排序加查找的数据结构。
  • 数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法。
  • 索引的实现通常通过B+树来进行实现（聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引），另外也有哈希索引。
• 为什么索引的实现是通过B+树，而非B树
  • MySQL的默认存储引擎是InnoDB，最小存储单位是页，每页大小为16K，在B+树中非叶子节点的页，存储键值加指针，叶子节点可以存储具体真实的数据，MySQL可以通过在B+树上二分查找，来确定数据在那一页上，进而找到数据。由于B+树只在叶子节点存储数据，而使得非叶子节点能存储更多的键值（非叶子节点存储的键值和指针要比叶子节点数据占的内存小的多），进而使得能有更多的叶子来存储数据。这样一般我们的树的深度为3，就可以存储全部数据了。深度越小，io操作也进而变少，性能提高（树的第一级一般就在内存中，不用进行io操作；另外辅助索引也会建立B+树，会导致io操作的增加）。
  • 而B树的非叶子节点也会存储数据，导致在相同深度情况下，整颗树相对与B+树能存储的数据减少，这使得不得不增加深度，增加io的操作，性能降低。
• 索引优劣势（空间换时间）
  • 优势
  • 提高数据检索的效率，降低数据库的io成本。
  • 建立索引的同时，也会对数据进行排序，从而降低了CPU的消耗。
  • 劣势
  • 索引的建立也会占用一部分空间
  • 对数据库进行写操作的时候，不仅要更新数据，也要更新因数据变化而带来的索引信息的变化。（所以频繁更新的表，不适合建立索引）
• 索引分类

• 单值索引：即一个索引只包含单个列，一个表可以有多个单列索引。
• 唯一索引：索引列的值必须唯一，但允许有空值。
• 复合索引：即一个索引包含多个列。

合理使用索引

• 适合建立索引
  • 主键自动建立唯一索引
  • 频繁作为查询条件的字段应该创建索引
  • 查询中与其它表关联的字段，外键关系建立索引
  • 频繁更新的字段不适合创建索引，因为每次更新不单单是更新了记录还会更新索引
  • Where条件里用不到的字段不创建索引
  • 单键/组合索引的选择问题，who?(在高并发下倾向创建组合索引)
  • 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
  • 查询中统计或者分组字段
• 不适合建立索引
  • 表记录太少
  • 经常增删改的表
  • 数据重复且分布平均的表字段
• 索引失效的情况
  • 最佳左前缀法则 - 如果索引了多列，要遵守最左前缀法则。（这和B+树这种数据结构有关，复合索引在B+树中，总是先排第一个列，再依次排剩下的列，即所有列的排序都基于前面列的排序，跳过前面，即左边的列，会致使排序失效，进而索引失效）
  • 不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描。
  • 存储引擎不能使用索引中范围条件右边的列（和最佳左前缀法则原理相同）。
  • 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *。（不用回表查询）
  • Mysql在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描。
    is null, is not null 也无法使用索引。
  • like以通配符开头（’%abc…’），mysql索引失效会变成全表扫描的操作。
  • 字符串不加单引号索引失效。（会使得MySQL进行转换，和在索引上进行操作，原理相同）
  • 少用or，用它来连接时会索引失效。（也和最佳左前缀法则原理相同）
  • 小表驱动大表。（一般小表建立索引用处不大，进行全扫描，而大表使用索引，减少io操作，可以大大增加效率，一般常见，多表连接查询，左连接，则右表建立索引，相反同理）。