数据库索引问题

MySQL数据库索引

什么是索引？ 帮助MySQL高效获取数据的数据结构

存储引擎：不同的数据文件在物理磁盘上的不同组织形式

索引存储在文件系统中

索引的文件存储形式与存储引擎有关。

主键的选择应该尽可能小，而且需要与业务无关，主键要自增，防止重复

 

为什么MySQL索引能够提高查询效率？

   （1）数据存储是有序的

   （2）在有序情况下，查询无需遍历整个表

   （3）在极端情况下，数据索引的查询效率为二分查找，复杂度为 log2(N)
 

缺点：（1）由于需要对索引进行存储，所以使用索引时会占用物理内存 

（2）对数据的修改和插入数据都会维护对应的索引，会消耗一定的性能.

    Question1： 为什么数据库索引不采用哈希索引
        答：哈希的等值查询很快（时间复杂度O（1）），但是使用哈希索引不能进行范围查找。如果使用排序操作，不能使用哈希索引的哈希值进行排序    （无序）

              需要将所有数据文件添加到内存，耗费内存空间
    

    Question2：为什么不采用平衡二叉树索引？
        答：平衡二叉树的平衡体现在左子树和右子树的高度绝对值相差不会超过1，

但随着树的高度增加，查找速度越来越慢。查找时需要回溯，影响数据读取效率
    

    Question3：为什么不采用B树索引？
        答：树的高度相对于平衡二叉树边小。

  特点：

（1）所有键值分布在整棵树中（每个节点存储key和value）

（2）搜索可能在非叶子节点结束，性能逼近二分查找 【相对于B+树的优势】

（3）每个节点最多拥有m颗子树，根节点最少拥有2个子树

（4）分支节点最多拥有m/2颗子树

虽然性能比平衡二叉树好，但是还是需要回溯查找
    

什么是B+树？

    B+树是平衡多叉树

  Question4：为什么采用B+树索引？
        答：将叶子节点采用链表（指针）的方式进行从小到大排序。一个节点存储多个节点，树的高度很低，同时链表解决了回溯查找的问题，非叶子节点存储key，叶子节点存储key和value。

1.B+树的层级更少：相较于B树，B+树每个非叶子节点存储的关键字数更多（不存具体数值，只是存指针），树的层级更少，所以查询数据更快
2.B+树的查询速度更稳定：B+树种所有关键字数据地址都存在叶子节点上，所以每次查找的次数都相同，所以查询速度要比B树更稳定
3.B+树具备排序功能：B+树所有的叶子节点数据构成了一个有序链表，在查询大小区间的数据更加方便，数据紧密性很高，缓存的命中率也会比B树高
4.B+树全节点遍历更快：B+树遍历整棵树只需要遍历所有叶子节点即可，而不需要像B树一样对每一层遍历，这样有利于数据库做全表扫描

 Question5:既然索引能够加快数据查询，为什么不给每一列创建索引？
   答： 因为索引数据也是要存到磁盘的，从磁盘读取数据到内存的过程，如果索引过多，会造成IO性能下降
    每次进行插入或删除操作时，非叶和合并操作会过多的操作磁盘

 Question6：索引分类 ：
    1.主键索引：主键是一种唯一性索引，但它必须指定为primary key，每个表只能有一个主键
    2.唯一索引：索引列的值都只能出现一次，即必须唯一，可以为空
    3.普通索引：基本的索引类型，值可以为空，没有唯一性的限制
    4.全文索引：全文索引的索引类型为FULLTEXT。全文索引可以在varchar,char,text类型的列上创建
    5.组合索引：多列值组成一个索引，用来搜索

 

Question7：SQL语句如何优化

  1. 强制类型转换会触发全表扫描（整形和字符串类型不要混用）
  2.创建索引的，不允许为null，可能会得到预期之外的值
  3.尽量不要超过三张表的join，能使用limit尽量使用limit
  4.单表索引控制在5个以内
  5.组合索引不要超过5个

 

存储引擎InnoDB和MyISAM引擎区别

1. InnoDB 支持事务，MyISAM 不支持事务。

2. InnoDB 支持外键，而 MyISAM 不支持。

3. InnoDB 是聚集索引，MyISAM 是非聚集索引。通过主键索引效率很高。而 MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

4. InnoDB 不保存表的具体行数，执行 select count(*) from table 时需要全表扫描。而MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；

5. InnoDB 最小的锁粒度是行锁，MyISAM 最小的锁粒度是表锁。一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。