MySQL索引分析以及相关面试题
1.什么是索引
-
一种能够帮助mysql提高查询效率的数据结构：索引数据结构
-
索引优点：大大提高数据查询的速度
-
索引缺点：
维护索引需要耗费数据库资源
索引要占用磁盘空间
当对表的数据进行增删改的时候，因为要维护索引，所以速度受到影响
-
结合索引的优缺点，得出结论：数据库并不是索引加的越多越好，而是仅为那些常用的搜索字段建立索引效果才是最佳的！
2.索引分类
-
主键索引：PRIMARY KEY
设定为主键后，数据库自动建立索引，INNODB为聚簇索引，主键索引列值不能有空（null）
-
单值索引：又叫单列索引、普通索引
即，一个索引只包含单个列，一个表可以有多个单列索引 单列索引可以为空
-
唯一索引
索引列的值必须唯一，但允许有空值（null），但只允许有一个空值（null）
-
复合索引
即一个索引可以包含多个列，多个列共同构成一个复合索引
-
全文索引
全文索引的类型为FULLTEXT，再定义索引的列上支持全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在Char、Varchar上创建。
3.索引的基本操作
3.1主键索引创建
-- 建表语句 建表的时候 设置主键 自动创建主键索引
create table t_user (
id varchar(20) primary key,
name varchar(20)
);
-- 查看索引
show index from t_user
3.2单列索引创建(普通索引、单值索引)
-- 建表的时候创建单列索引
-- 这种方式创建单列索引 其名称默认为字段名称：name
create table t_user(
id varchar(20) primary key,
name varchar(20),
key(name)
);
-- 注意 单例索引可以为空 而主键索引不可以为空
-- 建表后创建单列索引
-- 索引名称为：name_index 格式---> 字段名称_index
create index name_index on t_user(name)
-- 删除单列索引
drop index 索引名称 on 表名
3.3唯一索引创建
-- 建表的时候创建唯一索引
create table t_user(
id varchar(20) primary key,
name varchar(20),
unique(name)
);
-- 唯一索引允许为空 但是只能有一个为空
-- 建表后创建唯一索引
create unique index name_index on t_user(name)
3.4复合索引创建
-- 建表的时候创建复合索引
create table t_user(
id varchar(20) primary key,
name varchar(20),
age int,
key(name,age)
);
-- 建表后创建复合索引
create index name_age_index on t_user(name,age)
-- 复合索引查询的两个原则
-- 1.1索引最左前缀原则
-- eg:创建复合索引的时候 字段的顺序为 name,age,birthday
-- 在查询的时候能利用上索引的查询条件为:
select * from t_user where name=?
select * from t_user where name=? and age=?
select * from t_user where name=? and birthday=?
select * from t_user where name=? and age=? and birthday=?
-- 而其他顺序不满足最左前缀原则：
... where name=? and birthday=? and age=? -- 不满足最左前缀原则
... where name=? and birthday=? -- 不满足最左前缀原则
... where birthday=? and age=? and name=? -- 不满足最左前缀原则
... where age=? and birthday=? -- 不满足最左前缀原则
-- 2.mysql引擎在执行查询的时候 为了更好的利用索引，在查询过程中会动态调整查询字段的顺序
-- 这时候再来看上面不满足最左前缀原则的四种情况：
-- 不满足最左前缀原则，但是经过动态调整顺序以后，变为name age birthday，可以利用复合索引
... where name=? and birthday=? and age=?
-- 不满足最左前缀原则，也不能动态调整（因为缺少age字段），不可以利用复合索引
... where name=? and birthday=?
-- 不满足最左前缀原则，但是经过动态调整顺序以后，变为name age birthday，可以利用复合索引
... where age=? and birthday=? and name=?
-- 不满足最左前缀原则，也不能动态调整（因为缺少name字段），不可以利用复合索引
... where age=? and birthday=?
4.mysql索引的数据结构(B+树)
-- 建表
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,'3',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,'b',13);
-- 查询：自动排序，有序显示（因为主键是有索引的，因此会自动排序）
问题：为什么数据插入的时候，未按照主键顺序，而查询的时候却是有序的呢？
- 原因：mysql底层为主键自动创建索引，一旦创建了索引，就会进行排序。
- 实际上这些数据在mysql底层的真正存储结构变成了下面这种方式：
问题：为什么要排序呢？
- 因为排序之后查询效率就快了，比如查询id=3的数据，只需要按照顺序去查找即可，而如果不排序，就如同大海捞针，假如1000W条数据，可能有时候需要随机查询100W次才找到这个数据，也可能运气好上来第一次就查询到了该数据，不确定性太高。
4.1原理分析图
- 上图中这种分层树结构查询效率较高，因为如果我想要查询id=4地数据，只需要再页目录中匹配，大于3小于5，则去3对应地page=2中查找数据，这样就不需要从第1页开始检索数据了，大大提高了效率。
- 从上图可得出，在只有2层的结构下，1page 可以存储记录总数为 1365 * 455 ≈ 62万条，而如果再加1层结构，来存储page层分页目录数据的分页层PAGE的话，那么1PAGE可以存储总page数为：1365 * 1365 ≈ 186万条page，而1PAGE存储的总记录数为 1365 * 1365 * 455 ≈ 8.5 亿条。因此，我们平时使用的话，2层结构就已经足够了！实际上1个页存储的总数据树可能大于理论估计的，因为我们分配name字段的VARCHAR(20)占20个字节，而实际上可能存储的name数据并没有20个字节，可能更小！
4.2 B+树结构分析
上图4.1原理分析图中这种索引结构称之为B+树数据结构，那么什么是B+树呢？B+树和B树的区别是什么呢?
详情参考文章: https://www.cnblogs.com/lianzhilei/p/11250589.html
问题：为什么InnoDB底层使用B+树做索引而不用B树?
B树结构图:
- 从上面的B树结构图中分析得出，B树每个节点中不仅包含数据的key，还有data数据。而每个页的存储空间是有限的，如果data数据较大的时候，将会导致每个节点（即一个页16KB）能存储的key的数量较少的时候，当存储数据量很大的时候，会造成B树的深度较大，增大查询时的磁盘IO读取次数，进而影响查询效率。（树的深度影响IO读取次数）
- 在上一小节的B+树的结构分析图中，所有数据记录都是按照键值大小存放在同一层的叶子节点上，而非叶子节点上只能存储key信息，这样就可以大大增加每个节点（即一个页16KB）能存储的key的数量，进而可以降低树的高度，进而减少磁盘IO读取次数，提高查询效率。
- 所以B树和B+树的区别就在于：
- B+树只有叶子节点存储数据记录
- B+树非叶子节点只存储键值信息（B树的非叶子节点也存储数据记录）
- 所有节点直接都有一个链指针
- InnoDB引擎中，页的大小为16KB，一般表的主键类型为int（占用4个字节）或者bigint（占用8个字节），指针类型也一般占用4个或者8个字节，也就是说，一个页（B+树种的一个节点）中大概可以存储16KB/(8B+8B)=1000个键值（只是估计值，方便计算而已）。也就是说，一个深度为3的B+树索引可以维护10^3 * 10^3 * 10^3 = 10亿条记录。
- 实际情况中每个节点可能都不能填满，因此数据库中，B+树的高度一般为2-4层。MySQL的InnoDB存储引擎在设计的时候是将根节点常驻在内存中（不需要动磁盘IO）的，也就是说，查找某个键值的行记录最多只需要1-3次IO操作（每查询一次都需要动用一次磁盘IO操作）
mysql默认搜索引擎
mysql5.5以后默认使用InnoDB为搜索引擎
MyISAM是表锁，不支持事务和主外键
InnoDB默认可以创建16个索引
- InnoDB支持事务，MyIsam不支持事务，对于InnoDB每一条sql语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条sql语言放到begin和commit之间，组成一个事务。
- InnoDB支持外键，而MyIsam不支持，对一个包含外键的InnoDB表转成MyIsam表会失败、
- InnoDB是聚簇索引，数据文件和索引绑定在一起，必须要有主键，通过主键索引效率很高，但是辅助索引需要两次查询，先查询到主键，然后通过主键查询到数据。因此主键不应该过大。主键过大的时候，其他索引也会很大。而MyIsam是非聚簇索引，数据和文件是分离的，索引保存的是数据文件的指针，主键索引和辅助索引是独立的。
- InnoDB不支持全文检索，而MyIsam支持全文检索，查询效率上MyIsam要高
索引
MySQL官方对索引的定位为：索引是帮助MySQL高效获取数据的数据结构，可以得到索引的本质就是，索引是数据结构。
可以简单的理解为：排好序的快速查找B+树数据结构，B+树中的B代表平衡(balance)而不是二叉(binary)
检索原理
在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据上实现高级查找算法，这种数据结构，就是索引。下图就是一种可能的索引方式示例：
为了加快Col2的查找，可以维护一个左边所示的二叉树，每个节点分别包含索引键值和一个指向对应数据记录的物理地址的指针，这样就可以运用二叉树在一定的复杂度内获取相应数据，从而快速的检索出所有复合条件的记录。
聚簇索引和非聚簇索引
- 聚簇索引：将数据存储与索引放到一起，索引结构的叶子节点保存了行数据
- 非聚簇索引：将数据与索引分开，索引结构的叶子节点指向了数据对应的位置
在InnoDB搜索引擎中，在聚簇索引之上创建的索引被称为辅助索引，非聚簇索引都是辅助索引，像复合索引，前缀索引，唯一索引。辅助索引叶子节点存储的不再是行的物理位置，而是主键值，辅助索引访问数据总是需要二次查找，这个就被称为回表操作。
InnoDB使用的是聚簇索引，将主键组织到一颗B+树中，而行数据就存储在叶子节点上，若使用where id=4这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶子节点，之后获得行数据。
若对name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树种检索name,到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树中在执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。（重点在于通过其他键需要建立辅助索引）
聚簇索引具有唯一性，由于聚簇索引是将数据跟索引结构放在一起，因此一个表中仅仅有一个聚簇索引。
表中行的物理顺序和索引中行的物理顺序是相同的，在创建任何非聚簇索引之前创建聚簇索引，这时因为聚簇索引改变了表中行的物理顺序，数据行按照一定的顺序排列，并且自动维护这个顺序
聚簇索引默认是主键，如果表中没有定义主键，InnoDB会选择一个唯一且非空的索引来代替，如果没有这样的索引，InnoDB会隐式的定义一个主键（类似Oracle中的rowid）来作为聚簇索引。如果已经创建了主键为聚簇索引又希望再单独设置聚簇索引，必须先删除主键，然后添加我们想要的聚簇索引，最后恢复成主键即可。
MyIsam使用的是非聚簇索引，非聚簇索引的两颗B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助索引B+树的存储了辅助键。表数据存储再独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。
使用聚簇索引的优势
**每次使用辅助索引检索都要经过两次B+树查找，**看上去聚簇索引的效率明显要低于非聚簇索引，这不是多次一举吗？聚簇索引的优势在哪？
-
由于行数据和聚簇索引的叶子节点存储在一起，同一页中会有多条行数据，访问同一数据页不同行记录的时候，已经把页加载加载到了Buffer中（缓冲器），再次访问的时候，会在内存中完成访问，不必访问磁盘。这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键id来组织数据，获得数据更快。
-
辅助索引的叶子节点，存储主键值，而不是数据的存放地址。好处是当行数据发生变化，索引树的节点也需要发生分裂变化，或者是我们需要查找的数据，在上一次IO读写的缓存中没有，需要发生一次新的IO操作的时候，可以避免对辅助索引的维护工作，只需要维护聚簇索引树就可以了，另一个好处是，因为辅助索引存放的是主键值，减少辅助索引占用的存储空间大小。
注：我们直到一次IO读写，可以获取到16KB大小的资源，我们称之为读取到的数据区域为page。而我们的B树，B+树的索引结构，叶子节点上存放好多个关键字（索引值）和对应的数据，都会在一次IO操作中被读取到缓存中，所以在访问同一个页中的不同记录的时候，会在内存中操作，而不用再次进行IO操作了，除非发生了页的分裂，即要查询的行数据不再上次IO操作的缓存里，才会触发新的IO操作。
-
因为MyIsam的主索引并非聚簇索引，那么它的数据结构的物理地址必然是凌乱的，拿到这些物理地址，按照合适的算法进行IO读取，于是开始不停的寻道不停的旋转。聚簇索引则只需要一次IO。
-
不过如果涉及到大量的数据的排序、全表扫描、count之类的操作的话，还是MyIsam占优势一些，因为索引所占的空间小，这些操作是需要再内存中完成的。
聚簇索引需要注意的地方
当使用主键为聚簇索引的时候，主键最好不要使用uuid，因为uuid的值太过离散，不适合排序且可能出现新增加记录的uuid，会插入再索引树中间的位置，导致所引述调整复杂度变大，消耗更多的时间和资源。
建议使用int类型的自增，方便排序并且默认会再索引树的末尾增加主键值，对索引树的结构影响最小。而且，主键值占用的存储空间越大，辅助索引中保存的主键值也会跟着变大，占用存储空间，也会影响到IO操作读取到的数据量。
为什么主键通常建议使用自增id
聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻的存放在磁盘上的。如果主键不是自增id，那么可以想象，它会干些什么，不断地调整数据地物理地址、分页，当然也有一些措施来减少这些操作，但却无法彻底避免。但如果是自增地，那就简单了，它只需要一页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。
问题：为什么非聚簇索引（name字段的单列索引）构成的树，其叶子节点存储聚簇索引（主键id），而不直接存储行数据的物理第呢？换个方式说，非聚簇索引检索数据的时候，检索一次本树再去聚簇索引树种检索一次，这样二次检索树结构，那么为什么不直接在非聚簇索引树叶子节点种存放行数据物理地址，这样只需要检索一次树结构就拿到行数据呢？
这里画个图方便理解一些：
从上图得出，在做新增数据的时候，因为底层是需要基于主键索引进行排序的，那么就可能导致原来某些数据对应的物理地址发生了变化，而这时候由于我们的非聚簇索引树的叶子节点直接存储了数据的物理地址，所以为了保证能获取到数据，还需要同时对非聚簇索引树叶子节点的地址进行一遍更新修改。
同理，如果我们不做插入主键id为4这4行记录的操作，而是将其删除的话，这个流程可以自己思考一下。
也就是说，**之所以不在非聚簇索引树的叶子节点直接存放行数据的物理地址，是因为存储数据的物理地址会随着数据库的CRUD操作而不断变更，为了保证能获取到数据，这时必须要对非聚簇索引树相关叶子节点的地址进行一遍修改。**而存储的是主键的话，主键不会随着CRUD操作变化，宁愿多查一次树，也不要修改一次树的结构。
什么情况下无法利用索引呢?
-- 1.查询语句种使用like关键字：（这种情况主要是针对于单列索引）
-- 在使用like关键字查询的时候，如果匹配字符串的第一个字符为'%'，则索引不会被使用，而'%'
不在最左边，而是在右边，则索引会被使用到
-- eg:
select * from t_user where name like 'xx%' --可以利用上索引,这种情况下可以拿xx到索引树上去匹配
select * from t_user where name like '%xx' -- 不可以利用上索引
select * from t_user where name like '%xx' --不可以利用上索引
-- 2.查询语句种使用多列索引：（这种情况主要是针对于聚合索引）
-- 多列索引是在表的字段创建索引，只有查询条件中使用了这些字段中的第一个字段，索引才会被使用。即：最左前缀原则
-- 3.查询语句中使用or关键字
-- 查询条件中有or关键字的时候，如果or前后的两个条件列都具有索引，则查询中索引将被使用
-- 而如果or前后有一个或者两个列不具有索引，那么查询中索引将不被使用到
6.什么是约束以及分类？
约束的概念:
-
作用：是为了保证数据的完整性而实现的一套机制，即（约束是针对表中数据记录的）
-
MySQL中的约束：
- 非空约束：NOT NULL保证某列数据不能存储NULL值
- 唯一约束：**UNIQUE（字段名）**保证所约束的字段，数据必须是唯一的，允许数据是空值（NULL），但是只能有一个NULL
- 主键约束：**PRIMARY KEY（字段名）**主键约束=非空约束+唯一约束 保证某列数据不能为空且唯一
- 外键约束：**FOREIGN KEY（字段名）**保证一个表中某个字段的数据匹配另一个表中的某个字段，建立表与表直接的联系。
- 自增约束：AUTO_INCREMENT保证表中新插入数据的时候，某个字段数据可以依次递增
- 默认约束：DEFAULT保证表中新插入数据的时候，如果某个字段未被赋值，则会有默认初始值
- 检查性约束：CHECK保证列中的数据必须复合指定的条件
-
示例：
create table member( id int(10), phone int(15) unsigned zerofill, name varchar(30) not null, constraint uk_name unique(name), constraint pk_id primary key (id), constraint fk_dept_id foreign key (dept_id，字段2) references dept(主表1)(dept_id) );
7.MySQL索引与约束的区别
- 索引的作用：索引用于快速定位特定数据，提高查询效率的。
- 约束的作用：约束是为了保证数据的完整性，即约束是针对表中数据记录的。
- 总结：约束是为了保证表数据的完整性，索引是为了提高查询效率，两者作用不一样，种类也不太一样。
5万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
