浙江龙网

  1. 首页 > 生活百科 > >

一文读懂 MySQL 索引( 二 )

生活百科


  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;这条语句是符合一星的，因为索引是按照 age 从小到大排序的，所以 age = 20 的数据肯定是在一起的
二星
select * from user where age = 20 order by name;这条语句符合一星，但不符合二星 ， 因为数据列的顺序是按照 age 排序的，如果现在改成 name 排序，可能导致索引顺序与 order by 排序结果不同，结果如下：
一文读懂 MySQL 索引

文章插图

一文读懂 MySQL 索引

文章插图
select * from user where age = 20 order by age这条查询语句则符合一星和二星
三星
select * from user where age = 20这条语句不符合三星，因为索引列中只有 id 和 age，没有 name
select age from user where age = 20这条语句则符合三星，因为只查询了 age，age 在索引中存在，不需要回表
4.2 回表上面三星索引提到了一个次回表 ， 那么回表是什么？
简单点说，就是查询语句中需要的列，在索引中不包含，需要根据主键 id 再查询一次才能获取到 。回表相当于多查询一次，再查询时我们要尽量避免回表查询 。
因为普通索引中只包含了对应列和主键的值，比如 age 索引，那么 age 索引中包含的数据有 age，id 。此时如果需要 name 的话，需要先通过 age 索引找到对应的 id，然后再去主键索引上找到 name ， 主键索引包含了一行所有记录的值 。这里回答了上面的问题，为什么 MySQL 一定要有主键索引 ， 因为主键索引子节点中包含了全部数据
一文读懂 MySQL 索引

文章插图
4.3 索引覆盖CREATE TABLE `user` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(10) NOT NULL,`age` int(1) DEFAULT NULL,`sex` varchar(2) DEFAULT NULL,PRIMARY KEY (`id`),KEY `idx_name_age` (`name`,`age`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;select name,age from user where name = "张三"-- 这条语句就使用了索引覆盖，因为 name 和 age 再 idx_name_age 索引中都有，不需要回表查询select name,age,sex from user where name = "张三"-- 如果加上了 sex ， 那么就需要回表查询了，因为索引中不存在 sex 字段

推荐阅读


上一篇：springboot H2 linux下搭建使用

下一篇：Docker | Compose创建mysql容器