mysql索引总结(3)-MySQL聚簇索引和非聚簇索引
非聚簇索引
索引节点的叶子页面就好比一片叶子。叶子头便是索引键值。
先创建一张表:
CREATE TABLE `user` ( `id` INT NOT NULL , `name` VARCHAR NOT NULL , `class` VARCHAR NOT NULL);
对于MYISAM引擎,如果创建 id 和 name 为索引。对于下面查询:
select * from user where id = 1
会利用索引,先在索引树中快速检索到 id,但是要想取到id对应行数据,必须找到改行数据在硬盘中的存储位置,因此MYISAM引擎的索引 叶子页面上不仅存储了主键id 还存储着 数据存储的地址信息。如图:
像这样的索引就称为非聚簇索引。
非聚簇索引的二级索引与主键索引类似。假设我们对name添加索引,那么name的索引树叶子将是如下结构:
聚簇索引
对于 非聚簇索引 来说,每次通过索引检索到所需行号后,还需要通过叶子上的磁盘地址去磁盘内取数据(回行)消耗时间。为了优化这部分回行取数据时间,InnoDB 引擎采用了聚簇索引。
聚簇索引,即将数据存入索引叶子页面上。对于 InnoDB 引擎来说,叶子页面不再存该行对应的地址,而是直接存储数据:
这样便避免了回行操作所带来的时间消耗。 使得 InnoDB 在某些查询上比 MyISAM 还要快!
ps. 关于查询时间,一般认为 MyISAM 牺牲了功能换取了性能,查询更快。但事实并不一定如此。多数情况下,MyISAM 确实比 InnoDB 查的快 。但是查询时间受多方面因素影响。InnoDB 查询变慢得原因是因为支持事务、回滚等等,使得 InnoDB的叶子页面实际上还包含有事务id(换句话说就是版本号) 以及回滚指针。
在二级索引方面, InnoDB 与 MyISAM 有很大区别。
InnoDB默认对主键建立聚簇索引。如果你不指定主键,InnoDB会用一个具有唯一且非空值的索引来代替。如果不存在这样的索引,InnoDB会定义一个隐藏的主键,然后对其建立聚簇索引。一般来说,InnoDB 会以聚簇索引的形式来存储实际的数据,它是其它二级索引的基础。
假设对 InnoDB 引擎上表name字段加索引,那么name索引叶子页面则只会存储主键id:
检索时,先通过name索引树找到主索引id,再通过id在主索引树的聚簇索引叶子页面取出数据。
相关文章
- Mysql:is not allowed to connect to this MySQL server
- 【MySQL】解决mysql的 1594 错误
- 【问题解决方案】MySQL安装后无法启动-net start mysql服务名无效
- Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result
- 连接Mysql提示Can’t connect to local MySQL server through socket的解决方法
- 一台MySQL数据库启动多个实例
- Mysql蜜罐读取电脑配置文件
- mysql启动时报错:Starting MySQL... ERROR! The server quit without updating PID file (/opt/mysql/data/mysql.pid) 的解决方法
- MYSQL避免全表扫描__如何查看sql查询是否用到索引(mysql)
- MySQL优化:如何避免回表查询?_什么是索引覆盖?
- Mysql索引数据结构有多个选择,为什么一定要是B+树呢?_面试 (MySQL 索引为啥要选择 B+ 树)
- 【面试】MySQL的事务和索引
- MySQL索引相关的数据结构和算法
- MySQL 避免行锁升级为表锁——使用高效的索引
- 常用MySQL命令整理
- mysql报错 1142 - SELECT command denied to user ‘dev‘@‘localhost‘ for table ‘user‘ (已解决)
- MySQL数据库唯一索引
- mysql - 账号授权
- Python:mysql-connector-python模块对MySQL数据库进行增删改查
- Mysql 计算当前日期是本月第几周:一个自定义算法
- 如何实现MySQL表数据随机读取?从mysql表中读取随机数据
- Mysql 用户及权限
- mysql对GIS空间数据的支持,包括创建空间索引
- MySQL单列索引和组合索引的创建及区别介绍
- Mysql之修改mysql的视图定义者
- MySQL_02_InnoDB存储引擎默认使用B+树索引
- 两个MySQL数据库之间同步表结构及索引模式