【数据库基础知识】常见的数据库基础知识点梳理

1. 什么是索引

索引其实就是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址，在数据十分庞大的时候,索引可以大大的加快查询的速度，这是因为使用索引之后不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据的屋里地址然后访问相应的数据。

索引的缺点：在创建索引和维护索引会耗费时间，随着数据的增加而增加.索引也会增加，所以如果是经常增删改的列创建索引会大大的增加项目的维护速度。

索引的分类：

• 普通索引
• 主键索引
• 唯一索引
• 多列索引
• 全文索引

创建索引和删除索引：

-- 普通索引
create index index_name on tb_name
alter table tb_name add index index_name

drop index index_name on tb_name  -- 删除索引

-- 主键索引
alter table tb_name add primary key

-- 删除主键索引：
alter table tb_name drop primary key  -- 普通非自增主键

alter table tb_name id modify id int;  -- 自增主键，取消自增列
alter table tb_name drop primary key  -- 删除主键索引

-- 唯一索引，与普通索引类似，可以有null值
create unique index index_name on tb_name
alter table tb_name add unique index index_name

drop index index_name on tb_name  -- 删除索引

-- 多列索引
ALTER TABLE people ADD INDEX lname_fname_age (lame, fname,age);

2. 索引的数据结构

索引的数据结构和具体存储引擎的实现有关，在MySql中常用的有Hash索引和B+树索引，而常用的InnoDB存储引擎的默认索引实现为：B+树索引。

• Hash索引

Hash索引底层就是hash表，进行查找时，调用一次hash函数就可以获取到相应的键值，之后进行回表查询获取实际数据。

• B+树索引

B+树索引底层实现是多路平衡查找树，所有节点遵循左节点小于父节点，右节点大于父节点；对于每一次查询都是从根节点出发，查找到叶子节点方可以获取所查询的键值，然后根据查询判断是否需要返回表查询数据。

Hash索引与B+树所以的差异：

• hash索引进行等值查询比较快，但是无法进行范围查询，而B+支持范围查询；
• hash索引不支持使用索引排序；
• hash索引不支持模糊查询以及多列索引的最左前缀匹配；
• hash索引避免不了回表查询数据，而B+树在（聚簇索引、覆盖索引）的时候可以只通过索引完成查询；
• hash索引不稳定，性能不可预测，当某个键值存在大量重复的时候，发生hash碰撞，此时效率极差，而B+树查询效率比较稳定，所有查询都是从根节点到叶子节点，且树的高度较低。

3. 聚簇索引与非聚簇索引

在B+树的索引中，叶子节点可能存储了当前的key值，也可能存储了当前的key值以及整行的数据，这就是聚簇索引和非聚簇索引。以InnoDB作为存储引擎的数据表中，只有主键索引才是聚簇索引，如果没有主键，则挑选一个唯一键建立聚簇索引；如果没有唯一键，则隐式的生成一个键来建立聚簇索引。

当查询使用聚簇索引时，在对应的叶子节点，可以获取到整行数据，因此不用在进行回表查询。

非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键，想要查找数据还需要根据主键再去聚集索引中进行查找，也就是回表查询。非聚簇索引也不一定会回表查询，因为这涉及到查询语句所要求的字段是否全部命中了索引，如果是，那么就不用再进行回表查询了。

4. 什么是索引覆盖

只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。

可以利用索引覆盖来优化SQL的场景：

• 全表count查询优化
• 列查询回表优化
• 分页查询

5. 什么是回表查询

先定位主键值，再定位行记录，它的性能比扫一遍索引树更低。

6. 建立索引需要考虑的因素

• 考虑字段的使用频率，经常作为条件进行查询的字段；
• 经常作为表连接的字段考虑建索引，可以加快连接的速度；
• 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度；
• 经常在order by、group by之后的字段考虑建索引；
• 考虑联合索引中的顺序，否则无法命中索引
• 对非空字段(NOT NULL)创建索引，Mysql很难对控制做查询优化
• 索引适合区分度高、离散程度大的字段，有大量重复值的字段不适合建索引；
• 索引的长度不能太长，耗时；
• 对于那些定义为text, image和bit数据类型的列不应该增加索引；

7. 联合索引

MySQL可以使用多个字段同时建立一个索引，叫做联合索引。在联合索引中，如果想要命中索引，需要按照建立索引时的字段顺序挨个使用，否则无法命中索引。

因此在建立联合索引的时候应该注意索引列的顺序，一般情况下，将查询需求频繁或者字段选择性高的列放在前面。此外可以根据特例的查询或者表结构进行单独的调整。

8. 查看索引是否被使用到

MySQL提供了explain命令来查看语句的执行计划，MySQL在执行某个语句之前，会将该语句过一遍查询优化器，之后会拿到对语句的分析，也就是执行计划，其中包含了许多信息。可以通过其中和索引有关的信息来分析是否命中了索引，如possilbe_key, key,key_len等字段，分别说明了此语句可能会使用的索引、实际使用的索引以及使用的索引长度。

Mysql索引未被使用到的情况：

• 列参与了数学运算或者函数；
• 在字符串like时，左边是通配符，如’%aaa’;
• 当Mysql分析全表扫描比使用索引快的时候不用索引；
• 当使用联合索引，前面一个条件为范围查询，后面的即使符合最左前缀原则，也无法使用索引；

9. 为什么要尽量设定一个主键

主键是数据库确保数据行在整张表唯一性的保障，即使业务上本张表没有主键，也建议添加一个自增长的ID列作为主键。设定了主键之后，在后续的删改查的时候可能更加快速以及确保操作数据范围安全。

10. 主键使用自增ID还是UUID

推荐使用自增ID，不要使用UUID（通用唯一识别码，Universally Unique Identifier）。因为在InnoDB存储引擎中，主键索引是作为聚簇索引存在的，也就是说主键索引的B+树叶子节点上存储了主键索引以及全部的数据，如果主键索引是自增ID,那么只需要不断向后排序即可；如果用UUID，由于到来的ID与原来的大小不确定，会造成非常多的数据插入、数据移动，然后导致产生很多的内存碎片，进而造成插入性能的下降。

11. 字段为什么要求定义为NOT NULL

-- MySQL官网这样介绍：
NULL columns require additional space in the rowto record whether their values are NULL. For MyISAM tables, each NULL columntakes one bit extra, rounded up to the nearest byte.