目录
1. 什么是索引
- MySQL官方是这样说的：一种帮助MySQL提高查询效率的数据结构。
- 索引的优点：
- 大大提高查询效率
- 索引的缺点：
- 维护索引需要耗费数据库资源
- 会占用磁盘空间
- 在进行增、删、改时因为要维护索引所以效率较慢
- 结论：索引并不是越多越好，我们只需为常用的字段建立索引就可以了。
2. 索引的分类
- 主键索引 （PRIMARY KEY）
- 在创建主键时会自动创建主键索引，innodb引擎内部就是主键索引，主键索引所在列字段要求非空且唯一
- 单列索引（也叫单值索引、普通索引KEY）
- 以一个字段建立索引就是单列索引，这里的一个字段就是一列（即一个属性），一个表中可以由多个单列索引
- 唯一索引（唯一索引字段的值必须唯一，允许为空，只允许有一个空值，UNIQUE KEY）
- 多列索引（复合索引）（以表中的多个列建立的索引为多列索引）
- 全文索引（MySQL5.7之前，只有在MYISAM引擎上才支持全文索引）
- 全文索引类型为FULLTEXT，在建立索引的列上进行值的全文查找，允许这些列值为空或重复，全文索引可以在char，varchar，TEXT类型上创建
3. 索引的基本操作
3.1 主键索引
在创建表时创建主键后主键索引会默认创建
CREATE TABLE student(
id INT PRIMARY KEY,
NAME VARCHAR(30)
);
-- 查看索引
SHOW INDEX FROM student;
从图中我们可以看到主键索引字段的值不允许为空
3.2 单列索引
在创建表时创建单列索引
CREATE TABLE student2(
id INT PRIMARY KEY,
NAME VARCHAR(30),
KEY(name)
);
-- 查看索引
SHOW INDEX FROM student2;
-- 在创建表后创建索引
CREATE INDEX `name` ON student2(name);
/*
注意：
在创建表时创建索引无法改索引名字，名字默认为列名
而在创建表后创建索引可以自己为索引起名
*/
下面是创建的单列索引
从图中我们可以看到单列索引字段的值允许为空
3.3 唯一索引
-- 在创建表时创建唯一索引
CREATE TABLE student3(
id INT PRIMARY KEY,
NAME VARCHAR(30), UNIQUE KEY(name)
);
-- 查看索引
SHOW INDEX FROM student3;
-- 在创建表后创建索引
CREATE UNIQUE INDEX NAME ON student3(NAME)
从图中我们可以看到唯一索引允许为空
3.4 多列索引
-- 在创建索引时创建多列索引
CREATE TABLE student4(
id INT PRIMARY KEY,
age INT,
NAME VARCHAR(30),
KEY(age, NAME)
);
SHOW INDEX FROM student4;
DROP TABLE student4;
-- 在创建表后创建多列索引
CREATE UNIQUE INDEX NAME ON student4(NAME)
-- 最后我们说一下多列索引的最左前缀匹配
/*
假如我们在一个表中使用name、age、bir作为复合索引。
最左前缀匹配：只有（name），（name，age），（name，age，bir）里面的顺序可以打乱因为在查询时会动态调整，但必须包含name
name bir age 能否利用索引 可以
name age bir 能否利用索引 可以
age bir 能否利用索引 不可以
bir age name 能否利用索引 可以 动态调整
age bir 能否利用索引 不可以
*/
4. MySQL底层数据结构（B+ Tree树）
CREATE TABLE t_emp(
id INT PRIMARY KEY,
NAME VARCHAR(20),
age INT
);
INSERT INTO t_emp VALUES(5,'d',22);
INSERT INTO t_emp VALUES(6,'d',22);
INSERT INTO t_emp VALUES(7,'e',21);
INSERT INTO t_emp VALUES(1,'a',23);
INSERT INTO t_emp VALUES(2,'b',26);
INSERT INTO t_emp VALUES(3,'c',27);
INSERT INTO t_emp VALUES(4,'a',32);
INSERT INTO t_emp VALUES(8,'f',53);
INSERT INTO t_emp VALUES(9,'v',13);
SELECT * FROM t_emp;
/*
首先，我们先来引出问题，当我们向t_emp表中插入下面的数据之后，进行查询时，
我们会发现如下结果
*/
-- 问题：为什么我们在插入数据时明明是按照id无序的，为什么在查询时结果却是有序的呢？
-- 答：因为id是主键，innodb会默认为主键创建主键索引，一旦创建索引，会按照索引字段进行排序，其实MySQL内部就是这样存储的
-- 问题：为什么要排序呢？
-- 答：比如说我们要查询id为3的行记录，如果我们不排序的话就一个一个找，大海捞针（看运气），太慢了，但是如果我们排序之后只需找三次
底层的存储是这样的
有键值信息、数据信息、指针，但是这种查询也很慢，如果我们要查询的记录在最后，那么我们需要的时间就很多，效率很慢，为了提高效率，我们在链表的基础上进行了优化
在优化之后其实是基于页的管理，每一页的大小为16KB，
每次查询时，先去比较页，在页的目录上找到之后再去数据目录上寻找
上面这种索引结构称之为B+Tree数据结构
参考资料：
https://www.cnblogs.com/lianzhilei/p/11250589.html
我们来看一下B+Tree的图
B+Tree
从图中我们可以看出B+Tree上的叶子结点存储的是键值信息、数据信息、指针，而非叶子结点存储的只有键值信息和指针
B Tree
介绍了B+Tree当然就避免不了介绍B Tree
那么什么是B Tree树呢？
B Tree树与B+Tree树很相似，B Tree中的非叶子结点存储的是键值信息、数据信息、指针，这是B Tree与B+Tree最大的不同
innodb采用的是B+Tree，为什么不采用B Tree呢？
我们知道，优化之后是基于页的管理的，而每一页的大小是有限的，如果我们的非叶子结点也存储数据，那么势必就会造成每一页存储的记录量的减少，树的深度会增加，那么在查询时磁盘I/O次数会加大，会影响查询效率。而在B+Tree中，叶子结点存储数据按照键值大小进行排序，非叶子结点不存储数据，这样每一页存储的记录更多，树的高度会降低，磁盘I/O次数会降低，查询效率会提升。
-
B+Tree与B Tree的区别
- B+树只有叶子结点存储数据信息
- 叶子结点使用链指针连接
- B+Tree非叶子结点只存储键值信息
-
InnoDB引擎中，页大小为16KB，一般表的类型为INT（4个字节）或BIGINT（占8个字节），指针类型一般占4个或8个字节，也就是说，一个页中可以存储16*1024/（8+8） = 1024，为了方便计算我们约等于1000，也就是说一个深度为3层的B+Tree可以维护10^3 * 10^ 3 * 10 ^3 = 10 ^9 条数据，也就是10亿条数据。
-
实际情况中每个节点（页）可能并不能存储满，因此在数据库中，B+Tree树的高度一般是24层，MySQL在设计时将根节点常驻在内存中（不需要移动磁盘I/O），也就是说，MySQL在查询某个键值的行记录时，最多需要13次磁盘I/O操作。
5. 聚簇索引和非聚簇索引
-- 聚簇索引：数据和索引存储放到了一起，索引结构的叶子结点保存了行数据
-- 非聚簇索引：将数据和索引分开存储，索引结构的叶子结点指向了数据对应的位置
注意：
在InnoDB中，在聚簇索引上创建的称为辅助索引，非聚簇索引都是辅助索引，像单列索引、多列索引、唯一索引，辅助索引叶子结点不存储行记录，而是存储主键值，辅助索引访问数据总是需要二次查找。
- InnoDB中
- InnoDB中使用聚簇索引，将主键组织存储到一棵B+Tree树上，而行数据存储在叶子节点上，如果使用"where id = 14"就采用B+Tree的检索方式找到对应的叶结点，之后获得行记录。
- 若对Name列进行搜索，则需要两个步骤，第一步首先在辅助B+Tree树上检索name，到达其叶子结点获得主键，第二步在主索引B+Tree树上用主键进行检索，找到叶子节点，获取对应的数据。（重点在于通过其他键需要建立辅助索引）
- 聚簇索引默认是主键，如果表中没有定义主键，会使用一个非空且唯一的索引代替，如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。如果已经设置了主键为聚簇索引，而又希望设置新的聚簇索引，则需要删除主键，之后添加我们想要的聚簇索引，之后恢复主键即可。
- MYISAM
- MYISAM使用的是非聚簇索引，两棵B+树看起来没有什么不同，节点的结构完全一致只是存储的内容不一致，主键索引B+Tree节点存储主键，辅助键索引B+Tree节点存储辅助键。表数据单独存储在其他地方，这两棵B+Tree的叶子节点都使用一个地址指向表真正存储的地址，对于表数据而言，这两个树没有任何区别。由于两棵树是独立的，通过辅助键索引无需访问主索引树。
6. 约束以及分类
-
是为了保证数据完整性而实现的一套机制，即（约束是针对表中数据记录的）
-
MySQL中的约束
-
非空约束：NOT NULL保证某列数据不能存储NULL值
CREATE TABLE stu( id INT, NAME VARCHAR(20) NOT NULL -- name为非空 ); -
唯一约束：**UNIQUE（字段名）**保证所约束的字段，数据必须是唯一的，允许数据是空值（NULL），但只允许有一个NULL值
CREATE TABLE stu( id INT, phone_number VARCHAR(20) UNIQUE -- 添加了唯一约束 ); -
主键约束：**PRIMARY KEY（字段名）**主键约束 = 唯一约束 + 非空约束，保证某列数据不能为空且唯一
create table stu( id int primary key,-- 给id添加主键约束 name varchar(20) ); -
外键约束：**FOREIGN KEY（字段名）**保证一个表中某个字段的数据匹配另一个表中的某个字段，可以建立表与表直接的联系。
create table 表名( .... 外键列 constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称) ); -
自增约束：AUTO_INCREMENT保证表中新插入数据时，某个字段数据可以依次递增。
create table stu( id int primary key auto_increment,-- 给id添加主键约束 name varchar(20) ); -
默认约束：DEFALUT保证表中新插入数据时，如果某个字段没有被赋值，则会有默认值
CREATE TABLE student12( id INT DEFAULT 1, `name` VARCHAR(10) ) -
检查性约束：CHECK保证列中的数据必须符合指定的条件
-
7. MySQL约束和索引的区别
- 索引：索引是一种为了提高查询效率的数据结构
- 约束：约束是用来保证数据完整性的，约束是针对表中记录的
- 索引是为了提高查询效率的，约束是为了保证数据完整性的，两者定义不同，作用也不同。
8. 面试题小结
- 1. mysql索引分类并对比区别
- 2. 复合索引查询时，查询字段顺序与定义的复合索引字段顺序不一致是否可以查询
- 3. MySQL索引的数据结构是什么？
B+Tree，聚簇索引
- 4. MySq中索引和约束的区别和各自的分类
- 5. 为什么InnoDB使用B+Tree而不是用BTree呢
- 6. 什么是聚簇索引和非聚簇索引
- 7. 为什么非聚簇索引构成的树存储主键，而不存储数据的物理地址呢
- 8. 使用聚簇索引需要注意什么
- 9. 为什么主键通常建议使用自增id
- 10. 什么情况下无法利用索引
- 11. 聚簇索引相对于非聚簇索引的优势是什么呢
# 使用聚簇索引的优势
- 问题：对于聚簇索引莱说，每次使用辅助索引都需要经过两次B+Tree树查找，看上去聚簇索引的效率明显要低于非聚簇索引，请问聚簇索引的优势体现在哪里
- 1. 由于行数据和聚簇索引的叶子节点存储到了一起，同一页中会有多条行数据，访问同一数据页不同行记录时，已经把页加载到了缓存器中，再次访问时，会在内存中完成访问，不必访问磁盘。这样主键和行数据是一起被载入内存的，找到叶子结点就立刻可以将数据返回了，如果按照主键id来组织数据，获得数据更快。
- 2. 辅助索引的叶子结点存储主键值，而不是数据的存放地址，好处是当行数据发生变化时，索引树的节点也需要分裂变化或者是我们需要查找的数据，在上一次I/O读写的缓存中没有，需要发生新的一次I/O操作时，可以避免对辅助索引进行维护，只需要维护主索引树就可以了。另一个好处是，因为辅助索引存放的是主键值，减少了辅助索引占用的空间。
# 聚簇索引需要注意什么
- 当使用主键为聚簇索引时，主键最好不要使用uuid，因为uuid的值太过离散，不适合排序，也可能新增加的记录，插入在树的中间位置，导致索引树调整复杂度变大，消耗更多的资源
- 建议使用INT类型的自增，方便排序且默认会在索引树的末尾增加主键值，对索引树的结构影响最小。而且，主键值占用的空间越大，辅助索引中保存的主键值也会跟着变大，占用存储空间，也会影响到I/O操作读取到的数据量。
# 为什么主键通常建议使用自增id
- 聚簇索引的数据的物理存放顺序与索引的顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻的存放在磁盘上的。如果主键不是自增id，它会不断的调整数据的物理地址、分页，当然也有一些其他措施来减少这些操作，但却无法根除。但，如果是自增的，它只需要一页一页的写，索引结构相对紧凑，磁盘碎片少，效率也高。
# 什么情况下无法利用索引呢
- 1. 查询语句中使用LIKE关键字
在查询语句中使用LIKE关键字进行查询时，如果匹配字符串的第一个字符串是“%”，索引不会被使用。如果“%”不是在第一个位置，索引就会被使用
- 2. 在查询语句中使用多列索引
多列索引是在表的多个字段创建一个索引，只有在查询字段中使用了这些字段中的第一个字段，索引才会被使用。
- 3. 查询语句中使用OR关键字
查询语句只有OR关键字时，如果OR前后的两个条件的列都是索引，那么查询中将会使用索引。如果OR前后的有一个条件的列不是索引，那么查询中将不使用索引
298
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
