数据库的优化第一步——建索引

携手创作，共同成长！这是我参与「掘金日新计划 · 8 月更文挑战」的第6天，点击查看活动详情 >>

索引的定义：索引是对数据库表中一列或多列的值进行排序的一种结构。使用索引可快速访问数据，提高数据查询的效率。

索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。

1.索引类型和数据结构

• 索引类型：
• • 应用层面、物理顺序和键值得逻辑顺序关系
• 1. 唯一索引：索引列的值必须唯一，但允许有空值
  2. 非唯一索引：允许两行具有相同索引值的索引。
  3. 主键索引：为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。
  4. 聚集索引（也叫聚簇索引）：一种数据存储方式。在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。与非聚集索引相比，聚集索引提供更快的数据访问速度。InnoDB的：在同一结构中保存了数据行和B+Tree索引，文件xx.ibd；

• 存储结构

1. 普通二叉树不平衡，极端情况需要遍历查找，性能差；
2. Hash索引：基于哈希表实现，只有精确匹配索引查询才有效；
3. 平衡二叉树：树的深度也会上升，造成IO次数频繁；
4. B-Tree索引：数据分布在在各个节点之中，同样的高度，比平衡二叉树存储的数据多，减少了io次数，同时每次io获取的数据也更多，提升了IO效率；
5. B+Tree索引：将所有的节点都下沉到叶子节点上数据只存在叶子节点上，并建立一个单项的链表相连接，范围查找更加的遍历；查询能力稳定，每次都要查找到最后一层。
6. LSM Tree（日志结构合并树）:2个或者多个树或类似树的结构的集合;LSM树将原本的随机写操作转化成了顺序写操作，写性能大幅提升。

2.B+树和B树的区别是什么

• 每个节点的指针上限位2d而不是2d+1

• 内节点不存储data，只存储key；叶子节点不存储指针

• 一般来说，B+Tree比B树更适合实现外存储索引结构

3.mysql聚簇索引和非聚簇索引的区别

• 非聚簇索引，数据和索引分别存储。
• 聚簇索引，数据和索引保存在一起。

4.使用mysql索引都有什么原则

1、 对于查询频率高的字段创建索引；

2、 对排序、分组、联合查询频率高的字段创建索引；

3、 索引的数目不宜太多

4-7、尽量使用数据量少的索引,使用前缀来索引，选择唯一性索引，可以采用多列索引，删除不用的索引

8、最左前缀匹配原则（非常重要的原则）

5.不同的存储引擎是如何进行实际存储的

• 在MyISAM引擎中的实现:

  • 非聚簇索引，数据和索引分别存储。
  • 索引文件xx.MYI 数据文件xx.MYD 叶子节点保存的是引用地址而非数据
  • hash存储

• 在InnoDB中的实现：

  • 聚簇索引，数据和索引保存在一起
  • 文件xx.ibd 在叶子节点保存对应的所有数据，每个节点大小16KB 以主键索引来组织数据，没有主键的话，会帮我们隐式创建主键索引 辅助索引不存地址，存主键，这样便于维护
  • B++Tree

6.B+树和LSM树

B+树

优点：

1. 高度低，随机寻道次数少；密度大，查询稳定，遍历方便；范围查询->顺序读

缺点：

1. 写的数据比较离散，寻找写入位置时，子节点不在内存中，产生随机写
2. 如果运行时间较长，对应的块不会顺序存储，变分散，变随机读

总结：B+树在多读少写的情境下比较有优势，即使可以用SSD来获得成倍提升的读写效率，但是成本昂贵。

LSM Tree（日志结构合并树）

LSM树将原本的随机写操作转化成了顺序写操作，写性能大幅提升。不过，它的tradeoff就是牺牲了一部分读性能，因为读取时需要将内存中的数据和磁盘中的数据合并.这样的意义：

1. 1. 可以读取内存中C0树的缓存数据，内存的效率高，最近写入的数据的命中率高
   2. 写入数据未刷到磁盘时不会占用磁盘的I/O，不会与读取竞争

实际应用中，为了防止内存因为断电等原因丢失数据，会预写日志（WAL），比如LevelDB，HBase

7.索引的缺点和优化：

缺点：

索引占用磁盘空间，并且降低添加、删除、更新行的速度。大多数情况下，索引的速度优势大大超过它的不足之处。但是如果程序非常频繁的更新数据或磁盘空间有限，则需要限制索引的数量。

优化：

尽量自增，避免太多的页分裂

8.主键、外键和唯一索引的区别

	定义	作用	个数
主键：	唯一标识一条记录，不能有重复的，不允许为空	用来保证数据完整性	主键只能有一个
外键：	表的外键是另一表的主键, 外键可以有重复的, 可以是空值	用来和其他表建立联系用的	一个表可以有多个外键
索引：	该字段没有重复值，但可以有空值	是提高查询排序的速度	一个表可以有多个唯一索引