MySQL中的索引

索引的常见模型有哈希表、有序数组和搜索树。
哈希表：一种以 KV 存储数据的结构，只适合等值查询，不适合范围查询。
有序数组：只适用于静态存储引擎，涉及到插入的时候比较麻烦。可以参考 Java 中的 ArrayList。
搜索树：按照数据结构中的二叉树来存储数据，不过此时是 N 叉树（B+树）。广泛应用在存储引擎层中。

B+树比 B 树优势在于：

1. B+ 树非叶子节点存储的只是索引，可以存储的更多。B+树比 B 树更加矮胖，IO 次数更少。
2. B+ 树叶子节点前后管理，更加方便范围查询。同时结果都在叶子节点，查询效率稳定。
3. B+树中更有利于对数据扫描，可以避免 B 树的回溯扫描

索引的优点：

1. 唯一索引可以保证每一行数据的唯一性 ；
2. 提高查询速度 ；
3. 加速表与表的连接 ；
4. 显著的减少查询中分组和排序的时间；
5. 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

索引的缺点：

1. 创建跟维护都需要耗时 ；
2. 创建索引时，需要对表加锁，在锁表的同时，可能会影响到其他的数据操作 ；
3. 索引需要磁盘的空间进行存储，磁盘占用也很快；
4. 当对表中的数据进行 CRUD 的时，也会触发索引的维护，而维护索引需要时间，可能会降低数据操作性能。

索引的设计原则：

• 索引不是越多越好。索引太多，维护索引需要时间跟空间；
• 索引数据类型越小越好，越小的数据类型占用空间更少，计算和判断更快；
• 频繁更新的数据，不宜建索引；
• 数据量小的表没必要建立索引。
• 重复率小的列建议生成索引。因为重复数据少，索引树查询更有效率，等价基数越大越好；
• 数据具有唯一性，建议生成唯一性索引。在数据库的层面，保证数据正确性 ；
• 频繁 group by、order by 、DISTINCT 和 UNION 的列建议生成索引。可以大幅提高分组和排序效率 ；
• 经常用于查询条件的字段、级联查询下的on语句下使用的字段建议生成索引。通过索引查询，速度更快；
• 在多列索引中将选择性最高的行放在第一列，选择性高是指能够过滤掉更多不需要的记录；
• 索引不会包含有NULL值的列,只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的.所以我们在数据库设计时尽量不要让字段的默认值为NULL,应该指定列为NOT NULL,除非你想存储NULL.你应该用0、一个特殊的值或者一个空串代替空值.

索引失效的场景：

• 模糊搜索：左模糊或全模糊都会导致索引失效，比如'%a'和'%a%'。但是右模糊是可以利用索引的，比如'a%' 。
• 隐式类型转换：比如 select * from t where name = xxx , name 是字符串类型，但是没有加引号，所以是由 MySQL 隐式转换的，所以会让索引失效
• 当语句中带有 or的时候（or的条件必须都有单独的索引）：比如 select * from t where name=‘sw’ or age=14
• 不符合联合索引的最左前缀匹配：(A,B,C)的联合索引，你只 where 了 C 或 B 或只有 B,C
• 没有查询条件，或者查询条件没有建立索引;
• 在查询条件上没有使用复合索引的引导列，引导列基于最左原则，例如 创建了 org_no+user_number，仅当使用org_no或使用
  org_no and user_number 才会使用索引，单独使用user_number不会走索引
• 查询的数量是大表的大部分，应该是30％以上。
• 索引本身失效 （出现几率比较小，需要重建索引）
• 查询条件使用函数在索引列上（见12）
• 对小表查询 ，可能使用索引更慢
• 提示不使用索引
• CBO计算走索引花费过大的情况。其实也包含了上面的情况，这里指的是表占有的block要比索引小。
• 隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误. 由于表的字段tu_mdn定义为varchar2(20),
  但在查询时把该字段作为number类型以where条件传给mysql,这样会导致索引失效.
  错误的例子：select * from test where tu_mdn=13333333333;
  正确的例子：select * from test where tu_mdn='13333333333';
• 对索引列进行运算导致索引失效,我所指的对索引列进行运算包括(+，-，*，/，! 等)
  错误的例子：select * from test where id-1=9;
  正确的例子：select * from test where id=10;
• 使用mysql内部函数导致索引失效.对于这样情况应当创建基于函数的索引. 使用子查询并使用in操作会不走索引
  错误的例子：select * from test where round(id)=10;
  说明，此时id的索引已经不起作用了 正确的例子：首先建立函数索引，
  create index test_id_fbi_idx on test(round(id));
  然后 select * from test where round(id)=10; 这时函数索引起作用了

索引的基本概念
主键索引：主键索引的叶子节点存的是整行数据信息。在 InnoDB 里，默认使用聚簇索引（clustered index）作为主键索引。主键自增是无法保证完全自增的哦，遇到唯一键冲突、事务回滚等都可能导致不连续。

• 聚簇索引维护很昂贵，特别是插入新行或者主键被更新导至要分页(page split)的时候。建议在大量插入和删除行后，选在负载较低的时间段，进行表碎片优化。

• 如果使用UUId（随机ID）作为主键，使数据存储稀疏，这就会出现聚簇索引有可能有比全表扫面更慢

• 如果使用字符串当做主键会是索引数据变大，占用更多内存和存储，检索变慢

• 聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id，那么可以想象，它会干些什么，不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免。但，如果是自增的，那就简单了，它只需要一页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。

• 聚簇索引是将数据和索引放在一起，按照索引的顺序存储数据，因此一个表只能有一个聚簇索引。聚簇索引的优点是查询速度快，因为找到索引就找到了数据；缺点是插入和更新操作比较慢，因为可能需要移动数据或分裂页来维护索引的顺序。

• InnoDB可以使用非聚簇索引作为主键索引，但是不建议这样做，因为这样会降低查询性能和空间利用率。

  如果没有指定主键，InnoDB会选择一个唯一的非空索引作为聚簇索引，如果没有这样的索引，InnoDB会隐式定义一个主键作为聚簇索引。因此，最好是显式地指定一个合适的主键作为聚簇索引 。

• 一般索引：由单列创建的索引称为一般索引

create index indexname on tablename(columnname);

复合索引：由多列创建的索引称为符合索引，至少使用复合索引的第一个索引并且用and连接查询条件，可触发索引的使用

• 索引使用基于最左原则，没有使用第一个复合索引，不触发索引的使用
• 使用大于/小于不影响索引的选择
• 使用or不会命中索引

create index indexname on tablename(columnname1，columnname2);

前缀索引：当索引的字符串列很大时，创建的索引也就变得很大，为了减小索引体积，提高索引的扫描速度，就用索引的前部分字串索引，这样索引占用的空间就会大大减少，并且索引的选择性也不会降低很多。而且是对BLOB和TEXT列进行索引，或者非常长的VARCHAR列，就必须使用前缀索引，因为MySQL不允许索引它们的全部长度

create index indexname on tablename(columnname(10)); //单列的前10个字符创建前缀索引alter table tablename add index

唯一索引：以唯一列生成的索引，该列不允许有重复值，但允许有空值(NULL)

create unique index indexname on tablename(columnname);

主键索引与唯一索引的差异:

• 主键索引是一种特殊的唯一索引，不允许有空值，而唯一索引可以有空值。
• 主键索引是表的标识，一个表只能有一个主键索引，而唯一索引可以有多个。
• 主键索引可以作为外键的参照，而唯一索引不能。
• 主键索引和唯一索引都可以提高查询的速度，但主键索引在RBO模式下的优先级要高于唯一索引。
• 主键索引和唯一索引都可以有多列，但主键索引更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。

普通索引跟唯一索引查询性能：InnoDB 的数据是按数据页为单位来读写的，默认每页 16KB，因此这两种索引查询数据性能差别微乎其微。
change buffer：普通索引用在更新过程的加速，更新的字段如果在缓存中，如果是普通索引则直接更新即可。如果是唯一索引需要将所有数据读入内存来确保不违背唯一性，所以尽量用普通索引。

change buffer技术是一种用于提高MySQL更新性能的技术。它的基本原理是，当对一个二级索引（非主键索引）的数据页进行修改操作时，如果该数据页不在缓冲池中，MySQL不会立即从磁盘读取该数据页，而是将修改操作记录在change buffer中，这样就减少了磁盘的随机I/O。当该数据页被读取到缓冲池时，MySQL会将change buffer中的修改操作合并到数据页上，这样就保证了数据的一致性。

change buffer技术的优点是可以降低更新操作的磁盘I/O开销，提高内存利用率和性能。change buffer技术的缺点是需要占用一部分缓冲池空间，以及增加了数据页和change buffer的合并操作。change buffer技术只适用于非唯一的二级索引，因为唯一的二级索引需要进行唯一性检查，必须读取数据页到缓冲池。

change buffer技术可以通过以下参数进行配置和管理：

• innodb_change_buffer_max_size：设置change buffer的大小，占整个缓冲池的比例，默认值是25%，最大值是50%。
• innodb_change_buffering：设置哪些写操作启用change buffer技术，可以设置成all/none/inserts/deletes等，默认值是all。

非主键索引：非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）
回表：先通过数据库索引扫描出数据所在的行，再通过行主键id取出索引中未提供的数据，即基于非主键索引的查询需要多扫描一棵索引树。
覆盖索引：如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为覆盖索引。
联合索引：相对单列索引，组合索引是用多个列组合构建的索引，一次性最多联合 16 个。
最左前缀原则：对多个字段同时建立的组合索引(有顺序，ABC，ACB 是完全不同的两种联合索引) 以联合索引(a,b,c)为例，建立这样的索引相当于建立了索引a、ab、abc 三个索引。另外组合索引实际还是一个索引，并非真的创建了多个索引，只是产生的效果等价于产生多个索引。
索引下推：MySQL 5.6 引入了索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数。
索引维护：B+树为了维护索引有序性涉及到页分裂跟页合并。增删数据时需考虑页空间利用率。
自增主键：一般会建立与业务无关的自增主键，不会触发叶子节点分裂。
延迟关联：通过使用覆盖索引查询返回需要的主键，再根据主键关联原表获得需要的数据。
InnoDB 存储： .frm 文件是一份定义文件，也就是定义数据库表是一张怎么样的表。.ibd 文件则是该表的索引，数据存储文件，既该表的所有索引树，所有行记录数据都存储在该文件中。
MyISAM 存储： .frm 文件是一份定义文件，也就是定义数据库表是一张怎么样的表。 .MYD 文件是 MyISAM 存储引擎表的所有行数据的文件。* .MYI 文件存放的是 MyISAM 存储引擎表的索引相关数据的文件。MyISAM 引擎下，表数据和表索引数据是分开存储的。
MyISAM 查询：在 MyISAM 下，主键索引和辅助键索引都属于非聚簇索引。查询不管是走主键索引，还是非主键索引，在叶子结点得到的都是目的数据的地址，还需要通过该地址，才能在数据文件中找到目的数据。
PS：InnoDB 支持聚簇索引，MyISAM 不支持聚簇索引。

RBO&CBO:

RBO模式是指基于规则的优化（Rule-Based Optimization），是一种SQL优化器的类型。RBO模式是根据一系列预定义的规则来决定SQL的执行计划，不需要考虑数据的分布和统计信息。RBO模式的优点是简单和稳定，缺点是不够灵活和智能，可能无法找到最优的执行计划。

RBO模式与CBO模式（基于代价的优化）相对应，CBO模式是根据数据的分布和统计信息来估算SQL的执行代价，并选择代价最低的执行计划。CBO模式的优点是能够适应不同的数据和环境，缺点是需要维护和更新数据的统计信息，可能会受到数据倾斜和参数绑定等因素的影响。

SQL优化器类型	优化方式	优点	缺点
RBO	是根据一系列预定义的规则来决定SQL的执行计划，不需要考虑数据的分布和统计信息	简单和稳定	不够灵活和智能，可能无法找到最优的执行计划
CBO	是根据数据的分布和统计信息来估算SQL的执行代价，并选择代价最低的执行计划	能够适应不同的数据和环境	需要维护和更新数据的统计信息，可能会受到数据倾斜和参数绑定等因素的影响。

不同的数据库系统可能采用不同的SQL优化器类型，或者提供选项让用户选择。例如，Oracle数据库在10g之前默认使用RBO模式，在10g之后默认使用CBO模式。

删除无用索引

• 表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。应该定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。当项目上线一段时间后可根据以下语句查看未被使用过的索引，确认无用后可删除。
• 数据库索引创建不合理会出现冗余索引，使用以下语句可以查看当前存在的冗余索引，可以直接删除掉冗余的索引。

select * from sys.schema_unused_indexes;
select * from sys.schema_redundant_indexes;