`
jimmee
  • 浏览: 528521 次
  • 性别: Icon_minigender_1
  • 来自: 杭州
社区版块
存档分类
最新评论

MySQL索引实验-主键索引一定比辅助索引快吗?【转载】

阅读更多

一、在一个表执行了分别执行了两条语句:
a.SELECT ID FROM MNG_ROLE ORDER BY ID; -- 耗时37秒
b.SELECT ID FROM MNG_ROLE ORDER BY ID, NAME; -- 耗时0.01秒
c.SELECT ID FROM MNG_ROLE; -- 耗时0.22秒
二、表结构如下,插入3万条数据,而且REMARK和RESERVER字段都是填满数据:
====================================================
CREATE TABLE `MNG_ROLE` (
`ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`NAME` varchar(50) NOT NULL,
`CREATE_DATE` char(8) NOT NULL,
`CREATE_TIME` char(6) DEFAULT NULL,
`UUID` char(32) NOT NULL,
`REMARK` varchar(3000) NOT NULL DEFAULT ‘‘,
`RESERVER` varchar(3000) NOT NULL DEFAULT ‘RESERVER‘,
PRIMARY KEY (`ID`),
UNIQUE KEY `UK_UUID` (`UUID`) USING BTREE,
KEY `INDEX_NAME_CREATE_DATE_TIME_REMARK` (`NAME`,`CREATE_DATE`,`CREATE_TIME`,`REMARK`(255)) USING BTREE,
KEY `ID_NAME` (`ID`,`NAME`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=300001 DEFAULT CHARSET=utf8
====================================================
这个表建立了一个主键索引(ID),唯一键约束(UUID),普通索引(INDEX_NAME_CREATE_DATE_TIME_REMARK、ID_NAME)而且需要注意的是,这里有两个字符数为3000的字段,而且在表中的数据都是填满的。
三、首先我们分析两个语句EXPLAIN的结果:
MariaDB [yjtmng]> EXPLAIN SELECT ID FROM MNG_ROLE ORDER BY ID;
+------+-------------+----------+-------+---------------+---------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+-------+---------------+---------+---------+------+--------+-------------+
| 1 | SIMPLE | MNG_ROLE | index | NULL | PRIMARY | 4 | NULL | 145134 | Using index |
+------+-------------+----------+-------+---------------+---------+---------+------+--------+-------------+
1 row in set (0.00 sec)
MariaDB [yjtmng]> EXPLAIN SELECT ID FROM MNG_ROLE ORDER BY ID, NAME;
+------+-------------+----------+-------+---------------+---------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+-------+---------------+---------+---------+------+--------+-------------+
| 1 | SIMPLE | MNG_ROLE | index | NULL | ID_NAME | 156 | NULL | 145134 | Using index |
+------+-------------+----------+-------+---------------+---------+---------+------+--------+-------------+
1 row in set (0.00 sec)
MariaDB [yjtmng]> explain SELECT ID FROM MNG_ROLE;
+------+-------------+----------+-------+---------------+---------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+-------+---------------+---------+---------+------+--------+-------------+
| 1 | SIMPLE | MNG_ROLE | index | NULL | UK_UUID | 96 | NULL | 145134 | Using index |
+------+-------------+----------+-------+---------------+---------+---------+------+--------+-------------+
1 row in set (0.00 sec)
上面两个查询,前者是用到了主键索引,后两者用到了辅助索引,但是为什么用到主键索引会更慢呢?上述三个查询的type=index,这个方式跟全表扫描一样,只是MySQL扫描表时按索引次序进行而不是行。它的主要优点是避免了排序;最大的缺点是要承担按索引次序读取整个表的开销。
四、分析:
1.由于表的存储引擎采用的InnoDB,InnoDB的索引属于聚集索引,就是说表数据文件和索引文件都是同一个,表数据的分布按照主键排序,以BTREE数据格式存储,而辅助索引的叶子节点指向的是对应的主键。而上述表的REMARK和RESERVER的数据很多,导致硬盘存储的数据块很多,而主键索引的查找就会因为数据块的增多,导致更多的IO操作,降低查询效率。
而MyISAM引擎的索引属于非聚集索引,索引文件跟数据文件是分开的。而索引文件的所指向的是对应数据的物理地址。
2.EXPLAIN 三个查询的Extra都是“Using index”,表示这三个查询的数据都是直接从索引获取的,没有通过主键,再获取对应的一行数据,这就是索引覆盖。
所以第1、3个查询都是直接从辅助索引中查询,并且获取索引值返回,大大加快了效率,但是第三个查询,MySQL会自动采用唯一键"UUID"作为索引,这是为什么呢。而第二个遍历的是主键索引,而且数据量大,IO操作频繁。

 

分享到:
评论

相关推荐

    LNH_MySQL 18-主键-唯一-普通索引创建及删除小结.mp4

    LNH_MySQL 18-主键-唯一-普通索引创建及删除小结.mp4

    mysql面试题-mysql经典面试题目-数据库的基本概念-SQL语法-事务处理-索引优化-性能调优-mysql-面试题目

    30道经典mysql面试题 当面试MySQL数据库开发职位时,以下是一些经典的MySQL面试题供参考: 什么是数据库?什么是关系数据库管理系统(RDBMS)? 什么是SQL?列举一些常见的SQL命令。 什么是索引?为什么使用...

    MySQL索引之主键索引

    在MySQL里,主键索引和辅助索引分别是什么意思,有什么区别? 上次的分享我们介绍了聚集索引和非聚集索引的区别,本次我们继续介绍主键索引和辅助索引的区别。 1、主键索引 主键索引,简称主键,原文是PRIMARY KEY,...

    MySQL 主键与索引的联系与区别分析

    主键一定是唯一性索引,唯一性索引并不一定就是主键。 所谓主键就是能够唯一标识表中某一行的属性或属性组,一个表只能有一个主键,但可以有多个候选索引。因为主键可以唯一标识某一行记录,所以可以确保执行数据...

    Mysql-索引原理分析

    1 .索引的存储结构是什么? 是B树、B+树还是二叉树 2.什么是聚集索引? 聚集索引的主键索引和次要索引区别是什么 3.什么是非聚集索引? 非聚集索引的主键索引和次要索引区别是什么

    MySQL的主键与唯一索引约束

    总结,对于主键与唯一索引约束: • 执行insert和update时,会触发约束检查 • InnoDB违反约束时,会回滚对应SQL • MyISAM违反约束时,会中断对应的SQL,可能造成不符合预期的结果集 • 可以使用 insert … on ...

    JAVA面试题MySQL索引原理及索引优化校招面试找工作笔试

    JAVA面试题MySQL索引原理及索引优化校招面试找工作笔试 目录: 基本概念 MySQL索引结构的分类 Hash索引、B+树索引、全文索引、RTree索引。 B+树索引 B+树介绍,为什么选择B+树,非聚集索引 聚集索引 第一点、第二点...

    MySQL面试题-2023

    MySQL如何执行数据的备份和恢复? MySQL中什么是主键?在MySQL中如何定义一个主键? 什么是SQL注入?如何防止? 什么是索引?MySQL中有哪些索引类型? MySQL的InnoDB和MyISAM两个存储引擎各有什么优缺点?

    MySql数据库索引详解,索引看这一篇就够了

    6 主键索引,辅助索引,聚集索引,非聚集索引 6.1 MyISAM存储引擎-主键索引 6.2 MyISAM存储引擎-辅助索引 6.3 InnoDB存储引擎-主键索引 6.4 InnoDB存储引擎-辅助索引 7 索引的相关问题 7.1 索引的设计原则 7.2 一次...

    mysql中创建各种索引的语句整理.pdf

    添加PRIMARY KEY(主键索引) 添加UNIQUE(唯一索引) 添加INDEX(普通索引) 添加FULLTEXT(全文索引) 添加多列索引 ) mysql>ALTER TABLE `table_name` ADD INDEX index_name (`column1`,...

    MySql索引详解,索引可以大大提高MySql的检索速度

    打个比方,如果合理的设计且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车拿汉语字典的目录页(索引)打比方,我们可以按拼音、笔画、偏旁部首等排序的目录(索引)快速查找到...

    MySQL数据库:创建索引.pptx

    CREATE INDEX 语句并不能创建主键索引。 创建索引 CREATE [UNIQUE | FULLTEXT] INDEX 索引名 ON 表名(列名[(长度)] [ASC | DESC],...) 说明: 索引名:索引的名称,索引名在一个表中名称必须是唯一的。 列名:表示...

    主键索引与唯一索引的区别

    2、 主键创建后一定包含一个唯一性索引,唯一性索引并不一定就是主键。 3、 唯一性索引列允许空值,而主键列不允许为空值。 4、 主键列在创建时,已经默认为空值 ++ 唯一索引了。 5、 一个表最多只能创建一个主键...

    MySQL自整理超全精华版面试八股文

    主键索引和辅助索引(二级索引) 聚簇索引和非聚簇索】 非聚簇索引一定会回表查询吗?(要盖索) 联合索引 最左前缀匹配原则 剑建索引的建议 索引失效的场景 日志 MySQL的三个日志 事务 什么是事务? 事务的四大特性...

    (mysql面试题)MySQL中的索引、主键和外键的概念及其作用及代码展示.txt

    创建索引会占用额外的存储空间,并且在插入、更新和删除数据时,需要维护索引的结构,这会带来一定的性能开销。 2. 主键(Primary Key): 主键是数据库表中用于唯一标识每一行数据的列或一组列。主键的值不能重复...

    Oracle与Mysql主键、索引及分页的区别小结

    oracle新建序列,SEQ_USER_Id.nextval 2、索引: mysql索引从0开始,Oracle从1开始。 3、分页, mysql: select * from user order by desc limit n ,m. 表示,从第n条数据开始查找,一共查找m条数据。 Oracle:...

    深入浅出Mysql优化性能提升.txt

    21-Exists一定比子查询慢吗.wmv 22-Max Min非常规优化技巧.wmv 23-Count优化小技巧.wmv 24-巧用变量减少查询.wmv 25-MySQL主从集群配置.wmv 26-Binlog-Format的区别.wmv 27-主主复制.wmv 28-主主复制时的主键冲突...

    MySQL索引不会被用到的情况汇总

    主键索引:一种特殊的唯一索引,不允许有空值 联合索引:索引列有多个字段,使用时需要满足最左前缀原则 普通索引 这是最基本的索引,它没有任何限制。它有以下几种创建方式: 1.创建索引 代码如下: CREATE ...

Global site tag (gtag.js) - Google Analytics