数据库索引

帮助MySql高效获取数据的数据结构

B- B+ tree

mysql 反向 mysql反向键索引_唯一索引能否插入空

order 3, max. 3 children, 2 keys

  1. 根节点至少有2个叶子节点
  2. 每个节点最多有m-1个key，升序排列，value也按照键值排列，左边的小于key1，中间的在key1与key2之间，右边的大于key2
  3. 除了跟节点外，其他节点至少有m/2取上整再减1个节点

insert, delete, search O(logN)

self-balancing: insert/delete sometimes split and promote

完美的平衡，即所有叶子节点都在同一层

为什么要用B+ B-树来实现索引

举个例子，当你要查找一个节点的时候，其key是8，那么这个key对应的value只存了一个值假设值为字符串"hello"，它被存放在内存中的页号为100的页中，因此内存就会调入页号为100的页。这时，你又查找了key为9的节点，其对应的value是字符串"world"，由于红黑树节点的地址是不连续的，所以它有可能被存在离key为8的节点很远的地址，比如它被存放在页号为200的页中，因此内存又要调入页号为200的页。这就会引起大量的IO操作

而B树通过将多个节点的key和value存放在一个节点中，在这一个节点中存放的value的地址是连续的，从而减少IO次数

B+ 树

  1. 非叶子节点的子树指针个数与节点中的元素个数相同
  2. 非叶子节点的子树指针，指向key属于[K[i], K[i + 1]]的子树
  3. 所有叶子节点有一个链指针
  4. 所有关键字都在叶子节点出现
  5. 只有叶子节点有data域，其他节点仅仅只用来索引到叶子节点

B-树与B+树

  1. B+树除了叶子节点，其他节点都只有索引，同样的磁盘页B+树可以容纳更多的节点
  2. B+树的范围查询更加方便，可以先找到范围下限，然后通过叶子节点的链表顺序遍历，直至找到上限即可。而B-树只能先找到下限，通过中序遍历查找上限

B+树索引与hash索引的区别

哈希索引适合等值查询，但是无法进行范围查询，没办法利用索引进行排序

哈希索引不支持联合索引的最左匹配原则

如果有大量重复键值的情况，哈希索引的效率会很低，因为存在哈希碰撞

MyISAM & InnoDB

MyISAM：非聚集，叶子节点存放数据记录的地址

InnoDB：聚集，叶子节点存放了完整的数据记录

InnoDB数据文件本身要按照主键聚集，所以InnoDB要求表必须有主键，MyISAM可以没有。如果我们定义了主键，那么InnoDB会选择其作为聚集索引；如果没有显示定义主键，InnoDB会选择第一个不包含NULL值的唯一索引作为主键索引

聚集索引 & 非聚集索引

根本区别：表记录的排列顺序和索引的排列顺序是否一致

聚集索引：表记录的排列顺序和索引的排列顺序一致。就像书的目录，整本书的内容都会根据目录排序，且只可能有一种顺序

优点是查询速度快，因为一旦具有第一个索引值的记录被找到，具有连续索引值的记录一定紧随其后

缺点是对表进行修改速度较慢，这是为了保持表中的记录的物理顺序与索引的顺序一致，而把记录插入到数据页的相应位置，必须在数据页中进行数据的重排列

场合：

a. 此列包含有限数目的不同值

b. 查询的结果返回一个区间

c. 查询的结果返回与某值相同的大量结果集

非聚集索引：表记录的排列顺序和索引的排列顺序不一致。叶子结点不与实际的数据页相重叠，而采用一个指向表中的记录在数据页中的位置的指针。就像书后的索引目录，根据某个特定的属性，来定位书的某一页。

但非聚集索引并不是越多越好，因为需要额外空间来储存索引

场合：

a. 此列包含了大量数目不同的值

b. 查询的结束返回的是少量的结果集

c. order by子句中使用了该列

InnoDB为什么要用自增主键

如果表使用非自增主键，由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页的中间某个位置。频繁的移动，分页操作会造成大量的碎片，和不够紧凑的索引结构，后续不得不用optimize table来重建表优化填充页面。

如果表使用自增主键，每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟新的一页

MySql索引使用注意事项

MySql索引通常是被用于提高WHERE条件的数据行匹配时的搜索速度

  1. 不要在列上使用函数，这将导致索引失效而进行全表扫描
  2. 尽量避免使用!=, not in, <>等否定操作符
  3. 尽量避免使用or来连接条件
  4. 多个单列索引并不是最佳选择

MySql只能使用一个索引，会从多个索引中选择一个限制最为严格的索引，因此为多列创建单列索引，并不能提高MySql查询性能。可以使用复合索引

      5. 复合索引的最左前缀原则

SELECT * FROM news WHERE news_month = 1news_year_month_idx(news_year, news_month)在查询条件中没有使用复合索引的第一个字段，索引不会被使用

      6. 范围查询对多列查询的影响

select * from news where publish_time >= '2017-01-02' and publish_time <= '2017-01-08' and enable = 1

因为范围查询对多列查询的影响，将导致 news_publish_idx(publish_time, enable) 索引中 publish_time 右边所有列都无法使用索引优化查找。换句话说，news_publish_idx(publish_time, enable) 索引等价于 news_publish_idx(publish_time) 

  1. 使范围查询变成普通查询
  2. 用Redis的SortedSet有序集合来保存时间

      7. 索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的

      8. 隐式转换的影响

当查询条件左右两侧类型不匹配的时候会发生隐式转换

      9. LIKE语句的索引失效问题

like的方式进行查询，在like "value%"可以使用索引，但是对于"%value%"这样的方式，执行全表查询

创建索引的时候要注意什么

唯一，不为空，经常被查询的字段

非空字段：应该指定为NOT NULL。或者用0，一个特殊的值或者一个空串来代替空值

取值离散大的字段：变量各个取值之间差异程度大的放到联合索引的前面。可以通过count() 函数来查看字段的差异值

索引字段越小越好

InnoDB与MyISAM的区别

  1.  MyISAM支持事务，InnoDB不支持事务
  2. MyISAM是表级锁，InnoDB是行级锁

MyISAM：表级锁有两种模式：表共享读锁，表独占写锁。MyISAM进行读操作时，它不会阻塞其他用户对同一表的读请求，但阻塞写请求。写操作则阻塞读和写请求

InnoDB：行锁是通过给索引加锁来实现的，即只有通过索引条件检索数据，InnoDB才使用行级锁，否则将使用表锁。在InnoDB两个事务发生死锁的时候，会计算出每个事务影响的行数，然后回滚行数少的那个事务。当锁定的场景中不涉及InnoDb的时候，InnoDB是检测不到的。只能依靠锁定超时来解决

     3. MyISAM存储表的总行数，InnoDB不存储总行数

     4. MyISAM非聚集索引，InnoDB聚集索引

索引的分类

唯一索引：索引列所有值都只能出现一次，再为这个数据列创建索引的时候，就应该用关键字UNIQUE把它定义为一个唯一索引

主键：特殊的唯一索引，在一张表中只能定义一个主键索引

InnoDB数据文件本身要按照主键聚集，所以InnoDB要求表必须有主键，MyISAM可以没有。如果我们定义了主键，那么InnoDB会选择其作为聚集索引；如果没有显示定义主键，InnoDB会选择第一个不包含NULL值的唯一索引作为主键索引

组合索引：索引可以覆盖多个数据列

全文索引：类型为FULLTEXT，可以在VARCHAR或者TEXT类型的列上创建

查看是否使用到索引

可以用Explain

table: 显示这一行数据是关于哪张表的

type：显示连接使用了何种类型。一般保证查询至少达到range，最好能达到ref

possible_keys：可能应用的索引

key：实际的索引

key_len: 索引的长度

ref：索引的哪一列被使用了