MySQL 面试系列：如何选择普通索引和唯一索引?

2022-01-06 本文已影响0人 you的日常

其它MySQL 面试系列：

MySQL 面试系列：MySQL查询如何进行优化?

MySQL 面试系列：一条select语句在MySQL是这样执行的?
MySQL 面试系列：MySQL 常见的开放性问题
 MySQL 面试系列：MySQL 性能优化 & 分布式

MySQL 面试系列：MySQL 命令和内置函数
 MySQL 面试系列：MySQL 中日志的面试题总结
 MySQL 面试系列：MySQL 中锁的面试题总结

MySQL 面试系列：MySQL 事务的面试题总结
 MySQL 面试系列：MySQL 索引的面试题总结
 MySQL 面试系列：MySQL 基础模块的面试题总结

看过相关文章的的小伙伴会发现并没有聊过关于索引和事务的知识点，这两个大点再之前的文章中已经写过了。
接下来打开普通索引和唯一索引的世界。

一、了解普通索引和唯一索引

普通索引

MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。

唯一索引

索引列中的值必须是唯一的，但是允许为空值。

主键索引是一种特殊的唯一索引，不允许有空值。

扩展一下其它两中索引，知识点放在一起记忆会更好

全文索引

只能在char，varchar，text类型字段上使用全文索引，介绍了要求，说说什么是全文索引，就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行，比如有“你是个靓仔，靓女。。。”通过靓仔，可能就可以找到该条记录。

空间索引

空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种，GEOMETRY、POINT、LINESTRING、POLYGON。在创建空间索引时，使用SPATIAL关键字。要求，引擎为Myisam，创建空间索引的列，必须将其声明为not null。

索引添加方式

1、主键索引：alter table table_name add primary key (column)

2、唯一索引：alter table table_name add unique (column)

3、普通索引：alter table table_name add index index_name (column)

4、全文索引：alter table table_name add fulltext (column)

5、多列索引：alter table table_name add index index_name (column1,column2,column3)

二、应用场景

现在你应该知道普通索引和唯一索引的区别，接下来看看在一些场景下如何选择两个索引。

丁老师文章中提到一个业务场景是市民系统，通过身份证号来查姓名。

这里咔咔也借用这个场景来给大家通过咔咔的思路描述一下这个流程。

执行语句为

这个场景第一反应肯定是给card创建一个索引，但创建什么索引呢？主键索引肯定不建议使用。

思考：为什么不能用身份证号来作为主键索引？

三、为什么不能用太大的值作为主键

Innodb 存储引擎的主键索引结构如下图

image

普通索引数据结构如下图

image

主键索引的叶子节点存储的是对应主键的整行数据。

普通索引的叶子节点存储的是对应的主键值。

如果说 B+Tree 读取数据的深度是三层，每个磁盘的大小为 16 kb。

那在 B+Tree中非叶子节点可以存储多少数据呢！一般来说我们每个表都会存在一个主键。

根据三层来计算，第一层跟第二层存储的是key值，也就是主键值。

都知道int类型所占的内存时4Byte（字节），指针的存储就给个 6 Byte，一共就是 10 Tybe，那么第一层节点就可以存储 16 * 1000 /10 = 1600。

同理第二层每个节点也是可以存储 1600 个 key。

第三层是叶子节点，每个磁盘存储大小同样安装 BTree 的计算一样，每条数据占 1 kb。

在 B+Tree 中三层可以存储的数据就是1600 1600 16 = 40960000

结论：若主键过大会直接影响索引存储的数据量，所以非常不建议使用过大的数据作为主键索引。

四、从查询的角度分析

假设现在要查 card = 5 这条记录，查询过程为，先通过B+树 从树根开始，按层搜索到叶子节点，然后通过二分法来定位 card = 5 的这条记录。