数据库索引类型及原理记要

索引的数据结构

1. B-Tree及其变种：值按顺序存储，并且每一个叶子节点到根的距离相同。优点：因为值按顺序存储，所以适合范围查找，适合全值匹配和最左前缀匹配，对于多列索引支持精确匹配某一列并范围匹配另外一列；缺点：对于多列索引，不从索引的最左列查询，不能使用索引，不能跳过索引中的列，对某个列使用范围查询，其后所有列无法使用索引优化
2. 哈希索引：基于哈希表实现，因为哈希，所以只能精确匹配。缺点：无法存储字段信息，不能用于排序，不支持部分索引匹配，只能用于等值的查询。哈希索引的数据库支持不好，可以自己自定义哈希索引，通过增加一列哈希字段，通过某一种哈希函数，得到键的哈希值存入其中，对这一列建立索引即可，需要注意的是，哈希函数的选择，不要使用SHA1()和MD5()，因为它们计算出来的哈希值很长，会浪费空间并且比较也更慢，此外，为了避免哈希冲突，查询时除了使用哈希列的值，也好带入原值，方便哈希冲突的时候排除
3. 其他还有空间数据索引（R-Tree），全文索引
   
   • 聚簇索引：不是一种索引类型，而是一种数据存储方式，不同数据库引擎实现可能不同，InnoDB使用B-Tree实现。聚簇的意思是，索引的叶子节点不是存放指向行记录的指针，而是存放真实的一行数据，因为一个数据不能放两个地方，所以聚簇索引一个表只能有一个。InnoDB选择主键作为表的聚簇索引，如果表没有主键，则选择无null的唯一索引索引，如果这个也没有，则隐式创建一个
   • 聚簇索引扩展：聚簇索引是稀疏索引（只对某些搜索码建立索引），数据页上一级的索引页存储的是页指针，而不是行指针。而对于非聚集索引，则是密集索引（对每一个索引项建立索引），在数据页的上一级索引页它为每一个数据行存储一条索引记录。
   • 聚簇索引优缺点：优点：数据访问更快，使用覆盖索引时可以直接使用主键值；缺点：插入速度严重依赖于插入顺序，更新聚簇索引列的代价很高，InnoDB会让每个被更新的行移动到新的位置，不是插入到表末尾的数据，可能需要移动行而“页分裂”
   • InnoDB使用聚簇索引，进而所有二级索引（非聚簇索引）的叶子节点都默认包含主键列，而MyISAM则不使用聚簇索引，它的主键，就是一个名为primary的唯一非空索引，因为InnoDB的二级索引使用主键再次查询真实数据，所以二级索引需要两次索引查询

索引的好处：

• 减少对数据的全表扫描
• 减少排序和临时表
• 将随机IO变为顺序IO

索引创建原则

• 查询数据时需要索引的列不应该是表达式的一部分，也不是函数的参数
• 对于字符串的索引，应当索引开始的部分字符或者使用先前提到的模拟哈希索引。这里的问题是选取多长的字符：
  
  1. 对于已有数据，可以通过实验，查询索引列的前N的字符的count(*)，让其接近于完整列的count，也可以计算count(DISTINCT LEFT(colunm,N))/count(*)的结果，看n到几的时候，数值变化趋近于无，这个n就是前缀长度
  2. 此外，还需要考虑最坏情况，在选择n之后，查询一下这个前缀长度下的count，看一看最多的是多杀，有没有区分度
• 前缀索引也有缺点，无法使用其做排序和覆盖扫描
• 正确使用多列索引：查询只能使用一个索引，所以对每个查询的列使用单独的索引性能并不会好很多（可以通过改写sql，变成联合查询的方式改善），这个时候可以使用多列索引。这里需要注意的是，创建正确顺序的索引，将选择性最高的列放到最前，同时查询的时候查询顺序和索引顺序一致，选择性可以通过创建字符串前缀索引的判断方式，查询每一列的count数得到，count越小选择性越好，但要注意，存在一类特殊情况，可能多列索引的n列选择性高的列筛选完之后，符合条件的数据也特别多，比如像以前A站的用户，所有用户都是A站某管理员的好友，因为好友功能需要增加这位A站管理员实现，这种情况下，即便是用索引，查询这个A站管理员账户的好友，最后依旧会选择出几乎所有的数据
• 尽量使用多列索引的顺序来排序
• 使用覆盖索引，注意覆盖索引中可以隐式使用主键，因为InnoDB内部使用聚簇索引，二级索引都引用主键
• MyISAM可以使用前缀压缩来减少索引的大小，前缀压缩类似于哈夫曼树，下一个索引值的信息依赖于上一个索引值，所以顺序读的话速度好，但是对前缀压缩的索引不好二分查找，倒序扫描。对于CPU密集型应用不适合前缀索引，IO密集型，可能会好
• 尽量避免重复索引和冗余索引，但是扩展索引可能存在性能问题的时候（比如原索引有覆盖索引的用途或者扩展的索引有字符串比较等），这个时候可以引入冗余索引。
• 对于多列索引，尽量把可能范围查询的列放到后面，以便于使用更多的列来索引
• 对于多列索引中并不需要索引的列，因为索引最左匹配的原因，可以使用in()将这一列所有可能出现的值都列出，这样虽然对查询结果没有影响，但是可以继续使用索引

数据库的几个优化技巧摘要

• 一个对于计数器的优化：可以设置多个同种计数器，每次更新，随机更新一个，查询的时候查询sum
• 可以使用 on duplicate key update 将’插入或更新’变为原子操作，不存在则插入，存在则更新，注意的一点是，这需要在列上有唯一索引或主键索引（eg insert into tabA (a,b,c) values(1,2,3) on duplicate update c=c+1 ）
• 可以使用 replace 替代insert将’插入新的，删除旧的’变为原子操作，不存在则插入，存在则覆盖，同样，这需要列上有唯一索引或者主键索引（eg replace into tabA (a,b,c) values (1,2,3) ）
• 最好避免使用随机的聚簇索引，因为聚簇索引和顺序严重相关，随机值影响性能
• 使用顺序主键，如果遇到高并发的时候，也会有问题，顺序的界限会成为竞争的热点，如果使用自增键，自增键的锁也会竞争
• 覆盖索引：当索引包含所有查询语句需要的列的时候，称为覆盖索引，由聚簇索引可知，每个二级索引都包含主键，所以覆盖索引中即便没有主键也可以覆盖主键。因为哈希索引，空间索引和全文索引都不储存列的值，所以MySql只能使用B-Tree索引来做覆盖索引。
• 覆盖索引的优缺点：优点：值按顺序存储，顺序IO性能好，某些存储引擎只缓存索引，所以使用覆盖索引更快，InnoDB的聚簇索引原因，使用覆盖索引可以减少一次索引查询；
• 使用延迟关联来解决“查询条件可以使用覆盖索引，但是查询的数据列很多，不能通过覆盖索引获得”的情况。延迟关联思路是，通过嵌套查询首先在子查询中通过覆盖索引找到具有较好选择性的列，然后根据查询出来的列再查询数据
• 关于偏移量的问题：思考偏移量很大真的有必要吗？可以限制查询的偏移量吗？还可以通过延迟关联的方式获得主键（这样偏移量很大也仅仅针对索引），然后通过主键筛选数据