经验总结

  1. 首页 > 生活 > >

一文读懂 MySQL 索引( 二 )

生活百科


一文读懂 MySQL 索引

文章插图
2.5 B* 树B树是B+树一种变形，它是在B+树的基础上，将索引层以指针连接起来（B+ 树只是将数据层用指针连接起来），使搜索取值更加快捷
一文读懂 MySQL 索引

文章插图
总结
分析了以上几种数据结构，MySQL 采用的是 B+ 树来存储索引，综合层面来说，这样查询效率最好 。oracle 采用的是 B* 树
3 索引分类MySQL 索引主要有以下几种
  • 主键索引
  • 唯一索引
  • 普通索引
  • 组合索引
  • 全文索引
3.1 主键索引主键索引是比较特殊的索引，一般在建表时会给表设置一个主键，MySQL 会默认给这个主键加上索引 。主键索引叶子节点存储的是数据表的某一行数据 。当表没有创建主键索引是，InnDB 会自动创建一个 ROWID 字段用于构建聚簇索引 。规则如下：
  1. 在表上定义主键 PRIMARY KEY，InnoDB 将主键索引用作聚簇索引 。
  2. 如果表没有定义主键，InnoDB 会选择第一个不为 NULL 的唯一索引列用作聚簇索引 。
  3. 如果以上两个都没有，InnoDB 会使用一个 6 字节长整型的隐式字段 ROWID 字段构建聚簇索引 。该 ROWID 字段会在插入新行时自动递增 。
创建方式：
CREATE TABLE `user` (`id` int(11) NOT NULL AUTO_INCREMENT ,`name` varchar(255) NOT NULL ,PRIMARY KEY (`id`));为什么建表时没有指定主键，MySQL 会默认使用一个隐式字段 ROWID 字段构建聚簇索引？这个在后面我们会提到
3.2 唯一索引与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值 。如果是组合索引，则列值的组合必须唯一 。
创建方式
CREATE UNIQUE INDEX indexName ON user(column)或者ALTER TABLE table_name ADD UNIQUE indexName ON (column)3.3 普通索引MySQL 基本的索引，没有什么限制
创建方式：
CREATE INDEX index_name ON user(column)或者ALTER TABLE user ADD INDEX index_name ON (column)3.4 组合索引组合索引，顾名思义，给 MySQL 多个字段同时加上索引，在使用时要遵循最左匹配原则
创建方式：
CREATE INDEX index_name ON user(column1,column2) -- 给 column1 和 column2 加上索引3.5 全文索引全文索引，主要用来查找文本中的关键字，不是直接与索引值相比较 。与我们常见的搜索引擎（如elasticsearch、solr 等）功能相似 。MySQL 全文索引性能一般，所以一般不用，作为了解即可
创建方式：
CREATE FULLTEXT INDEX index_column ON user(column)或者ALTER TABLE user ADD FULLTEXT index_column(column)4 索引设计4.1 三星索引三星索引是我们设计 MySQL 索引时的一个规范，符合三星索引的索引设计通常是比较好的设计
一星：索引中查询相关的索引行是相邻的，或者至少相距足够靠近
二星：索引中数据列的顺序和查找中排序顺序相同
三星：索引中的列包含了查询中需要的全部列 。索引包含查询所需要的数据列，不再进行全表查表，回表操作
下面举一个例子为大家介绍一下三星索引是什么样子的
现在有一张表，表结构如下
CREATE TABLE `user` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(10) NOT NULL,`age` int(10) DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;一星
我们现在给 age 加上索引
create index idx_age on user (age);查询
select * from user where age in (10,20,35,43)这条语句不一定符合一星，因为 age 是一个范围，数据可能比较分散
select * from user where age = 20;

经验总结扩展阅读


上一篇：2023年10月开鱼池黄道吉日 2023年10月哪天适合开鱼池

下一篇：万宝路的含义