vlambda博客
学习文章列表

索引为什么快

• +bonus: 加快检索速度，加快多表连接
• -price: 额外空间开销，维护索引的额外时间开销

数据结构比较

• 有序数组：等值查询和范围查询场景中的性能就都非常优秀。 特定值查询 用二分法就可以快速得到，这个时间复杂度是 O(log(N))。类似between[x, y]的 范围查询 也比较快，先用值查询二分法找到x, 然后向后遍历，知道找到y。但是他最大的问题是插入或者删除一个新数据，这个新数据后面的整个数组都需要挪动，复杂度是O(N)。
• HashMap：虽然可以快速定位，值查询的时间复杂度是O(1), 但是Hashmap没有顺序，进行范围查询的话复杂度高是O(N)。
• 二叉树查找树BST：二叉树的高度不均匀，不能自平衡，查找效率跟数据量有关（树的高度），在极端情况下（插入数据本身就是有序的）这棵树就退化成链表了，查询实际复杂度是O(N)
• 红黑树：是平衡的BST，性能稳定在O(logN), 但因为是二叉树，树的高度随着数据量增加而增加，并且需要再平衡。适合数据都在内存的情况，比如Java里的HashMap。但是在硬盘寻址的场景下IO成本会比较高。
• B-Tree：相比二叉树来说是一种多路平衡查询树，但是B树不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少（有些资料也称为扇出），指针少的情况下要保存大量数据，只能增加树的高度，导致IO操作变多，查询性能变低；
• B+Tree: 从物理存储结构上说是N叉树，B-Tree和B+Tree都以页(4K)来划分节点的大小，但是由于B+Tree的中间节点（非叶子节点）不存储数据，存的是索引信息，索引包含Key和Point指针。因此B+Tree能够在同样大小的节点中，存储更多的key，提高查找效率。

每一个索引在 InnoDB 里面对应一棵 B+ 树。以 InnoDB 的一个整数字段索引为例，这个 N 差不多是 1200。这棵树高是 4 的时候，就可以存 1200 ^(4-1) 个值，这已经 17 亿了。考虑到树根的数据块总是在内存中的，一个 10 亿行的表上一个整数字段的索引，查找一个值最多只需要访问 3 次磁盘。

聚簇索引/非聚簇索引

覆盖索引

  
    
    
  

   
     
     
   -- 索引覆盖了
select id from T where key = 'test';

-- 索引没覆盖，需要回表
select * from T where key = 'test';
  
    
    
  

唯一索引/普通索引

• 唯一索引找到满足的第一条记录会立马返回，通知检索（因为唯一性的保证）。但是这个区别并没有很大的性能区别，因为Innodb是按照页（默认16KB）读写的，读数据的时候是从B+树的根节点开始搜索，搜索的时候将整个页从硬盘加载到内存。
• 唯一索引在插入的时候会多做些判断，想要做这个判断就必须先把数据页读入内存。但是普通索引不需要做这个判断，就可以把需要更新的数据做判断：如果数据在内存则直接更新；如果不在也不加载内存，而是先写入change buffer，等下次查询的时候再执行change buffer。这样普通索引会相对性能好一些。但是注意：如果业务场景是写入后立马有查询，其实还是会立马需要把数据页加载到内存，这样的情况下其实并不能带来优化IO的操作。

Full-index全文索引

MySQL支持三种模式的全文检索模式：自然语言模式（IN NATURAL LANGUAGE MODE），即通过MATCH AGAINST 传递某个特定的字符串来进行检索。布尔模式（IN BOOLEAN MODE），可以为检索的字符串增加操作符，例如“+”表示必须包含，“-”表示不包含，“*”表示通配符（这种情况， 即使传递的字符串较小或出现在停词中，也不会被过滤掉），其他还有很多特殊的布尔操作符，可以通过如下参数控制：查询扩展模式（WITH QUERY EXPANSION）, 这种模式是自然语言模式下的一个变种，会执行两次检索，第一次使用给定的短语进行检索，第二次是结合第一次相关性比较高的行进行检索。

单列索引/联合索引

• 如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用
• 按照字段在查询条件中出现的频度建立索引

1. 索引的匹配规则是左匹配的
2. 只有复合索引的第一个字段出现在查询条件中，该索引才可能被使用
3. 有了(A,B,C)，就等于同时拥有了(A)，（A,B）和 (A,B,C) 三个索引
4. 只要索引内，开始用范围查询，后面的索引就失效了。**这里注意：**IN 在 where 中，也属于准确查询，不会使后面索引失效。

什么是下推索引？

  
    
    
  

   
     
     
   mysql> select * from tuser where name like '张 %' and age=10 and ismale=1;
  
    
    
  

哪些字段适合建索引?

1. 出现在 SELECT、UPDATE、DELETE 语句的 WHERE 从句中的列
2. 包含在 ORDER BY、GROUP BY、DISTINCT 中的字段
3. 并不要将符合 1 和 2 中的字段的列都建立一个索引， 通常将 1、2 中的字段建立联合索引效果更好
4. 多表 join 的关联列

为什么有些SQL不走索引？

1. 使用了通配符开头，NOT IN 语句或者
2. 联合索引的第一个字段查询条件中
3. 数据引擎的优化器选错了索引（可以适当使用 force index 语句来优化）

为什么一般主键索引最好是自增的, 尽量短的数值类型？

• 自增

• 短数

索引的最佳实践？

要建索引

1. 定义主键的数据列一定要建立索引。
2. 定义有外键的数据列一定要建立索引。
3. 对于经常查询的数据列最好建立索引。
4. 对于需要在指定范围内的快速或频繁查询的数据列;
5. 经常用在WHERE子句中的数据列。
6. 经常出现在关键字order by、group by、distinct后面的字段，建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致，否则索引不会被使用。

不要建索引

7. 对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。
8. 对于定义为text、image和bit的数据类型的列不要建立索引。
9. 对于经常存取的列避免建立索引

索引的坑

10. 限制表上的索引数目。对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个。索引虽说提高了访问速度，但太多索引会影响数据的更新操作。
11. 对复合索引，按照字段在查询条件中出现的频度建立索引。在复合索引中，记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录，系统再按照第二个字段的取值排序，以此类推。因此只有复合索引的第一个字段出现在查询条件中，该索引才可能被使用,因此将应用频度高的字段，放置在复合索引的前面，会使系统最大可能地使用此索引，发挥索引的作用。

索引不会包含有NULL值的列

12. 只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL

使用短索引（列内容越短越好）

12. 对列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

索引列排序

13. MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

like语句操作

14. 一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引，而like “aaa%”可以使用索引。即：左匹配规则。可以使用reverse函数来支持逆序匹配，从而增强like走索引的可能。

  
    
    
  

   
     
     
   ALTER TABLE `T` ADD `reverse_identifier` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci;

select * from T where reverse_identifier like reverse('%SDTE');
  
    
    
  

不要在列上进行运算

15. select * from users where YEAR(adddate)<2007; 将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成 select * from users where adddate<‘2007-01-01’;

不使用NOT IN和<>操作

16. 因为MySQL只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE才会使用索引。因为在以通配符 % 和 _ 开头作查询时，MySQL不会使用索引。