数据库优化:MySQL优化及索引解析
当我们搜索13这条数据时,在根节点和子节点 都能定位,但是一直会找到叶子结点。
二叉树平衡二叉树,B树对比:
如图显示如果是自增主键情况下:
二叉树显然不适合做关系型数据库索引(和全表扫描没什么区别)。
平衡二叉树呢,虽然解决了这种情况,但是同样会导致这棵树,又瘦又高,这同样会造成上文所提到查询IO次数过多以及IO利用率不高。
B树呢,显然已经解决了这两个问题,所以下文来解释,为什么在这种情况下MySQL还用了B+树,又做了那些增强。
B树和B+树比较:
B+树在B树上面的优化:
IO效率更高(B树每个节点都会保留数据区,而B+树则不会,假设我们查询一条数据要遍历三层,那么显然B+树查询中IO消耗更小)
范围查找效率更高(如图,B+树已经形成了一个天然链表形式,只需要根据最结尾的链式结构查找)
基于索引的数据扫描效率更高。
索引类型的分类索引类型可分为两类:
辅佐索引(二级索引) 唯一性索引主键索引相对来说性能是最好的,但是对于SQL优化,其实大多时候我们都在辅佐索引上面做一些改进和补充。
B+树在储存引擎层面落地 我们创建两个表分别为test_innodb(采用InnoDB作为储存引擎)test_myisam(采用MyISAM作为储存引擎)下图是两张表磁盘落地的相关文件,这两个储存引擎在B+树磁盘落地式截然不同的。B+树在MyISAM落地:
*.frm文件是表格骨架文件比如这个表中的id字段name字段是什么类型的存储在这里 *.MYD(D=data)则储存数据 *.MYI (I=index)则储存索引B+树在InnoDB落地:
我们现在执行如下SQL语句,他会先去找辅助索引,然后找到辅助索引下101的主键,再去回表(二次扫描)根据主键索引查询103这条数据将其返回。
SELECT id,name from test_myisam where name = zhangsan这里就有一个问题了,为什么不像MyISAM在辅助索引下直接记录磁盘地址,而是要多此一举再去回表扫描主键索引,这个问题在下面相关面试题中回答,记一下这个问题是这里来的。
这个可以总结一下,MyISAM落地数据储存会有三个类型文件 ,.frm文件是表骨架文件,.MYD(D=data)则储存数据 ,.MYI (I=index)则储存索引,MyISAM引擎中主键索引和二级索引平级关系,在MyISAM引擎中,有可能使用多个索引,InnoDB则相反,主键索引和二级索有严格的主次之分在InnoDB一条语句只能用一个索引要么不用。
set global optimizer_trace= enabled=on 打开执行计划开关他将会把每一条查询sql执行计划记录在information_schema 库中OPTIMIZER_TRACE表中
和上一个问题原因一样,当一个索引经常发生变化,那么就意味这,这个缩印树也要经常发生变化。4
这个原因是因为离散性,比如说,一张一百万数据的表,其中一个字段代表性别,0代表男1代表女,把这字段加了索引,那么在索引树上,将会有大量的重复数据。而我们常见的索引建立一般都是驱动型的。其目的是,尽可能的删减数据的查询范围,这个显然是不匹配的。
联合索引是一个包含了多个功效的索引,他只是一个索引而不是多个,
其次,单列索引是一种特殊的联合索引
联合索引的创立要遵循最左前置原则(最常用列 离散度 占用空间小)
通过索引项信息可直接返回所需要查询的索引列,该索引被称之为覆盖索引,说白了就是不需要做回表操作,可以从二级索引中直接取到所需数据。
索引下推,简单点来说就是,在sql执行过程中,面对where多条件过滤时,通过一个索引,完成数据搜索和过滤条件其,特点能减少io操作。
就是在你手动显式指定这一个字段为主键时候,会以这一个字段为聚集索引。 在没有显式指定主键时候有两种情况: 他会寻找第一个UK(unique key)作为主键索引组织索引编排。 如果既没有指定主键也没有UK的情况下,此时会以rowId(在InnoDB表中每一个记录都会有一个隐藏(6byte)的rowId)为聚集索引。
在InnoDB 中基于辅助索引查询的内容,从辅助索引中无法直接获取,需要基于主键索引的二次扫描的操作叫做回表操作。
为什么在InnoDB 中辅助索引叶子结点数据区记录的是主键索引的值而不是像MyISAM中去记录磁盘地址。
这个原因其实很简单,因为主键索引的数据结构是会经常发生变化的,如果在辅助索引数据区记录磁盘地址,那么假设我们有10个辅助索引,当我们主键索引结构发生变化后,还要一个个去通知辅助索引,且主键索引结构是经常发生变化的,增删都有可能影响他的
数据结构。
我想要获取技术服务或软件
服务范围:MySQL、ORACLE、SQLSERVER、MongoDB、PostgreSQL 、程序问题
服务方式:远程服务、电话支持、现场服务,沟通指定方式服务
技术标签:数据恢复、安装配置、数据迁移、集群容灾、异常处理、其它问题
本站部分文章参考或来源于网络,如有侵权请联系站长。
数据库远程运维 数据库优化:MySQL优化及索引解析
相关文章
- mysql 插入多行数据、插入检索数据详解数据库
- MySQL双主主从复制:实现高可用(mysql双主主从)
- 获取MySQL数据库中的数据(查询mysql数据库)
- 使用MySQL增加主键约束(mysql加主键)
- MySQL数据库表的创建与管理(mysql数据库表的创建)
- MySQL设计模式:评论和回复表(mysql评论回复表设计)
- MySQL日志管理:深入解析(mysql日志管理)
- MySQL查看数据库结构:深入结构掌握(mysql查看数据库结构)
- 理解MySQL数据库中的外键约束(mysql数据库外键)
- Mysql软解析:极致性能优化体验(mysql软解析)
- 远程访问MySQL数据库:实现简单而快速(访问远程mysql数据库)
- MySQL如何为列添加索引(mysql列加索引)
- 轻松清空MySQL注册表,让数据库运行更稳定(mysql注册表清空)
- Mac 环境下如何安装 MySQL 数据库?(mysql安装mac)
- 如何打开MySQL中的数据库(mysql打开某个数据库)
- 探秘MySQL光标:优化数据库操作的高效利器(mysql光标)
- MySQL实现数据分区,提升查询效率(mysql数据分区)
- Exploring the System Databases of MySQL: A Comprehensive Overview(mysql的系统数据库)
- MySQL如何使用制表符对数据进行整理和排列(mysql制表符)
- MySQL安全性:如何保障低权限用户的数据安全?(mysql低权限)
- 实现大数据处理的关键之一——MySQL数据库管理系统(mysql大数据)
- MySQL中文官网:解析数据库神器的中文版指南(mysql 中文官网)
- MySQL 数据库的倒入指南(mysql倒入)
- 命令行登录MySQL数据库:简易操作指南(命令登录mysql)
- MySQL中PK的含义及作用(mysql中pk的意思)
- 深入浅出MySQL中ER图的作用及实现方法(mysql中er图的作用)
- 如何通过CMD进入MySQL数据库(cmd进mysql数据库)
- ASP连接MySQL失败排查与解决方案(asp连接mysql失败)
- AMH简易教程快速导入MySQL数据库(amh mysql 导入)
- 2008年MySQL版本发布抢占市场提升数据库性能(2008 mysql版本)
- 1290 MySQL让企业数据库存储变得更安全(1290 mysql)
- MySQL中创建主外键,规范化数据库设计(mysql中创建主外键)
- 了解MySQL索引为什么不仅仅使用唯一索引(mysql 不唯一索引吗)
- MySQL 数据库中不为空的数据如何处理(mysql 不是空的)
- 如何让MySQL支持中文字符集(mysql不支持中文吗)