一个不留神,索引就创建重复了
相信没有人会故意创建重复的冗余的索引,很多重复和冗余的索引都是在不经意间创建的,今天松哥来和大家捋一捋这个问题。
因为我们日常在使用 MySQL 的过程中,基本上都是使用 InnoDB 引擎,所以接下来的讨论主要是基于 InnoDB 引擎的 B+Tree 索引来讨论,其他的哈希索引全文索引等不在讨论范围种。
1. 与联合索引重复
在前面的文章中,松哥通过好几篇文章和大家分享了联合索引,包括它涉及到的覆盖索引、前缀匹配等等,联合索引好用,但是对联合索引理解不到位的话,可能会创建出如下的重复索引:
CREATE TABLE `user2` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`address` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`password` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`email` varchar(16) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `user_index1` (`username`,`address`),
KEY `user_index2` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
可以看到,这里创建了两个索引:
- user_index1:这个索引包含两个字段,username 在前 address 在后。
- user_index2:这个索引包含一个字段 username。
(username,address) 索引既可以当成联合索引来用,也可以通过最左匹配原则当成单独的 (username) 索引来用。
所以,如果再为 username 字段单独创建一个索引就没有必要了,这反而会导致增删改的时候速度变慢。
不过怎么说呢,上面这个结论适用于 99% 的场景,可能会有一些特殊情况,例如想把 (username) 和某一个特别长的字段建立一个联合索引,此时如果单独使用 username 字段进行搜索的话,效率可能降低,此时视搜索的重要程度,看是否需要创建一个重复的索引。
2. 主键加入联合索引中
来看看下面这个索引:
CREATE TABLE `user2` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`address` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`password` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`email` varchar(16) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `user_index` (`username`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
一个名为 user_index 的索引中包含了两个字段 username 和 id,其中 id 是主键。
在什么是 MySQL 的“回表”?一文中,松哥和大家聊了,索引按照物理存储方式可以分为聚簇索引和非聚簇索引。
我们日常所说的主键索引,其实就是聚簇索引(Clustered Index);主键索引之外,其他的都称之为非主键索引,非主键索引也被称为二级索引(Secondary Index),或者叫作辅助索引。
对于主键索引和非主键索引,使用的数据结构都是 B+Tree,唯一的区别在于叶子结点中存储的内容不同:
- 主键索引的叶子结点存储的是一行完整的数据。
- 非主键索引的叶子结点存储的则是主键值以及索引列的值。
这是两者最大的区别。
既然主键已经存在于叶子结点中,那当然没有在联合索引中加入主键了。
好啦,几个小小的注意点,希望能给小伙伴们启发。
参考资料:
《高性能 MySQL》
相关文章
- 【WOT2018】黄东旭:如何运用HTAP数据库帮到你?来听听TiDB的故事
- 干货整理:处理不平衡数据的技巧总结
- 本周六京东、闪闪泛娱技术大咖进行区块链应用场景解读
- UMStor Hadapter:大数据与对象存储的柳暗花明
- Spark streaming中持久保存的RDD/有状态的内存
- Hadoop 3中的磁盘管理大招解密
- 大数据之谜Spark基础篇,核心RDD特征分析讲解
- 数据科学简介:通过对数据分类进行预测(二)
- 深入理解HBase的系统架构
- 数据分析技术给商业模式带来颠覆的五种方式
- OpenStack如何在大数据用例中扮演关键角色
- 大数据领域的12大工具,试试看哪个更好用
- 大数据与区块链:一对桴鼓相应的搭档
- 大型.NET ERP系统的20条数据库设计规范
- 如何避免HBase写入过快引起的各种问题
- 数据分析秘籍在这里:Kaggle六大比赛最全面解析(上)
- Tech Neo技术沙龙第19期专题回顾 ——容器技术实践(含视频、PPT)
- Spark及Spark Streaming核心原理及实践
- 精英数据科学家应该具备这些能力和特质
- 数据的本质和核心是什么?