织梦欧美网站模板,广西智能网站建设报价,云南网站建设哪家公司好,seo外链资源MySQL 索引#xff08;B树#xff09;详解 MySQL逻辑架构对比InnoDB与MyISAM存储结构存储空间可移植性、备份及恢复事务支持AUTO_INCREMENT表锁差异全文索引表主键表的具体行数CRUD操作外键 sql优化简介什么情况下进行sql优化sql语句执行过程sql优化就是优化索引 索引索引的优… MySQL 索引B树详解 MySQL逻辑架构对比InnoDB与MyISAM存储结构存储空间可移植性、备份及恢复事务支持AUTO_INCREMENT表锁差异全文索引表主键表的具体行数CRUD操作外键 sql优化简介什么情况下进行sql优化sql语句执行过程sql优化就是优化索引 索引索引的优势索引的弊端索引的分类创建索引MySQL索引原理 - B树BTree相对于B-Tree有几点不同 聚簇索引与非聚簇索引聚簇索引非聚簇索引 如何触发联合索引对user表建立联合索引username、password触发联合索引 分析sql的执行计划---explainexplan使用简介用户表部门表未触发索引触发索引结果分析 explain查询结果简介 MySQL逻辑架构 MySQL的存储引擎架构将查询处理与数据的存储/提取相分离和其他数据库相比MySQL有点与众不同主要体现在存储引擎的架构上这种架构可以根据业务的需求和实际需求选择合适的存储引擎。 下面是MySQL的逻辑架构分层
连接层最上层是一些客户端和连接服务包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。服务层MySQL的核心服务功能层该层是MySQL的核心包括查询缓存解析器解析树预处理器查询优化器。主要进行查询解析、分析、查询缓存、内置函数、存储过程、触发器、视图等select操作会先检查是否命中查询缓存命中则直接返回缓存数据否则解析查询并创建对应的解析树。引擎层存储引擎层存储引擎真正的负责了MySQL中数据的存储和提取服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同这样我们可以根据自己的实际需要进行选取。存储层数据存储层主要是将数据存储在运行于裸设备的文件系统之上并完成与存储引擎的交互。
对比InnoDB与MyISAM
存储结构
MyISAM每个MyISAM在磁盘上存储成三个文件。分别为表定义文件、数据文件、索引文件。第一个文件的名字以表的名字开始扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。
InnoDB所有的表都保存在同一个数据文件中也可能是多个文件或者是独立的表空间文件InnoDB表的大小只受限于操作系统文件的大小一般为2GB。
存储空间
MyISAM MyISAM支持支持三种不同的存储格式静态表(默认但是注意数据末尾不能有空格会被去掉)、动态表、压缩表。当表在创建之后并导入数据之后不会再进行修改操作可以使用压缩表极大的减少磁盘的空间占用。
InnoDB 需要更多的内存和存储它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。
可移植性、备份及恢复
MyISAM数据是以文件的形式存储所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。
InnoDB免费的方案可以是拷贝数据文件、备份 binlog或者用 mysqldump在数据量达到几十G的时候就相对痛苦了。
事务支持
MyISAM强调的是性能每次查询具有原子性,其执行数度比InnoDB类型更快但是不提供事务支持。
InnoDB提供事务支持事务外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
AUTO_INCREMENT
MyISAM可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引如果是组合索引自动增长可以不是第一列他可以根据前面几列进行排序后递增。
InnoDBInnoDB中必须包含只有该字段的索引。引擎的自动增长列必须是索引如果是组合索引也必须是组合索引的第一列。
表锁差异
MyISAM 只支持表级锁用户在操作myisam表时selectupdatedeleteinsert语句都会给表自动加锁如果加锁以后的表满足insert并发的情况下可以在表的尾部插入新的数据。
InnoDB 支持事务和行级锁是innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁只是在WHERE的主键是有效的非主键的WHERE都会锁全表的。
全文索引
MyISAM支持 FULLTEXT类型的全文索引
InnoDB不支持FULLTEXT类型的全文索引但是innodb可以使用sphinx插件支持全文索引并且效果更好。
表主键
MyISAM允许没有任何索引和主键的表存在索引都是保存行的地址。
InnoDB如果没有设定主键或者非空唯一索引就会自动生成一个6字节的主键(用户不可见)数据是主索引的一部分附加索引保存的是主索引的值。
表的具体行数
MyISAM 保存有表的总行数如果select count() from table;会直接取出出该值。
InnoDB 没有保存表的总行数如果使用select count(*) from table就会遍历整个表消耗相当大但是在加了wehre条件后myisam和innodb处理的方式都一样。
CRUD操作
MyISAM如果执行大量的SELECTMyISAM是更好的选择。
InnoDB如果你的数据执行大量的INSERT或UPDATE出于性能方面的考虑应该使用InnoDB表。
外键
MyISAM不支持
InnoDB支持
sql优化简介
什么情况下进行sql优化
性能低、执行时间太长、等待时间太长、连接查询、索引失效。
sql语句执行过程
编写过程
select distinct ... from ... join ... on ... where ... group by ... having ... order by ... limit ...解析过程
from ... on ... join ... where ... group by ... having ... select distinct ... order by ... limit ...sql优化就是优化索引
索引相当于书的目录。索引的数据结构是B树。
索引
索引的优势
提高查询效率降低IO使用率降低CPU使用率
比如查询order by age desc因为B索引树本身就是排好序的所以再查询如果触发索引就不用再重新查询了。
索引的弊端
索引本身很大可以存放在内存或硬盘上通常存储在硬盘上。索引不是所有情况都使用比如①少量数据②频繁变化的字段③很少使用的字段索引会降低增删改的效率
索引的分类
单值索引唯一索引联合索引主键索引
备注唯一索引和主键索引唯一的区别主键索引不能为null
创建索引
alter table user add INDEX user_index_username_password (username,password)MySQL索引原理 - B树
MySQL索引的底层数据结构是 B树
BTree是在B-Tree基础上的一种优化使其更适合实现外存储索引结构InnoDB存储引擎就是用BTree实现其索引结构。
B-Tree结构图中每个节点中不仅包含数据的key值还有data值。而每一个页的存储空间是有限的如果data数据较大时将会导致每个节点即一个页能存储的key的数量很小当存储的数据量很大时同样会导致B-Tree的深度较大增大查询时的磁盘I/O次数进而影响查询效率。在BTree中所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上而非叶子节点上只存储key值信息这样可以大大加大每个节点存储的key值数量降低BTree的高度。
BTree相对于B-Tree有几点不同
非叶子节点只存储键值信息。所有叶子节点之间都有一个链指针。数据记录都存放在叶子节点中。
由于BTree的非叶子节点只存储键值信息假设每个磁盘块能存储4个键值及指针信息则变成BTree后其结构如下图所示 通常在BTree上有两个头指针一个指向根节点另一个指向关键字最小的叶子节点而且所有叶子节点即数据节点之间是一种链式环结构。因此可以对BTree进行两种查找运算一种是对于主键的范围查找和分页查找另一种是从根节点开始进行随机查找。
可能上面例子中只有22条数据记录看不出BTree的优点下面做一个推算
InnoDB存储引擎中页的大小为16KB一般表的主键类型为INT占用4个字节或BIGINT占用8个字节指针类型也一般为4或8个字节也就是说一个页BTree中的一个节点中大概存储16KB/(8B8B)1K个键值。也就是说一个深度为3的BTree索引可以维护103 * 10^3 * 10^3 10亿 条记录。
实际情况中每个节点可能不能填充满因此在数据库中BTree的高度一般都在24层。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的也就是说查找某一键值的行记录时最多只需要13次磁盘I/O操作。
数据库中的BTree索引可以分为聚集索引clustered index和辅助索引secondary index。上面的BTree示例图在数据库中的实现即为聚集索引聚集索引的BTree中的叶子节点存放的是整张表的行记录数据。辅助索引与聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据而是存储相应行数据的聚集索引键即主键。当通过辅助索引来查询数据时InnoDB存储引擎会遍历辅助索引找到主键然后再通过主键在聚集索引中找到完整的行记录数据。
聚簇索引与非聚簇索引
mysql中普遍使用BTree做索引但在实现上又根据聚簇索引和非聚簇索引而不同。
聚簇索引
所谓聚簇索引就是指主索引文件和数据文件为同一份文件聚簇索引主要用在Innodb存储引擎中。在该索引实现方式中BTree的叶子节点上的data就是数据本身key为主键如果是一般索引的话data便会指向对应的主索引如下图所示 在BTree的每个叶子节点增加一个指向相邻叶子节点的指针就形成了带有顺序访问指针的BTree。做这个优化的目的是为了提高区间访问的性能例如上图中如果要查询key为从18到49的所有数据记录当找到18后只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点极大提到了区间查询效率。
非聚簇索引
非聚簇索引就是指BTree的叶子节点上的data并不是数据本身而是数据存放的地址。主索引和辅助索引没啥区别只是主索引中的key一定得是唯一的。主要用在MyISAM存储引擎中如下图 非聚簇索引比聚簇索引多了一次读取数据的IO操作所以查找性能上会差。
如何触发联合索引
对user表建立联合索引username、password 触发联合索引
使用联合索引的全部索引键可触发联合索引 使用联合索引的全部索引键但是用or连接的不可触发联合索引 单独使用联合索引的左边第一个字段时可触发联合索引 单独使用联合索引的其它字段时不可触发联合索引
分析sql的执行计划—explain
explain可以模拟sql优化执行sql语句。
explan使用简介
用户表 部门表 未触发索引 触发索引 结果分析
explain中第一行出现的表是驱动表。
指定了联接条件时满足查询条件的记录行数少的表为[驱动表]未指定联接条件时行数少的表为[驱动表]对驱动表直接进行排序就会触发索引对非驱动表进行排序不会触发索引。
explain查询结果简介 idSELECT识别符。这是SELECT的查询序列号。 select_typeSELECT类型 SIMPLE 简单SELECT(不使用UNION或子查询)PRIMARY 最外面的SELECTUNIONUNION中的第二个或后面的SELECT语句DEPENDENT UNIONUNION中的第二个或后面的SELECT语句取决于外面的查询UNION RESULTUNION的结果SUBQUERY子查询中的第一个SELECTDEPENDENT SUBQUERY子查询中的第一个SELECT取决于外面的查询DERIVED导出表的SELECT(FROM子句的子查询) table表名 type联接类型 system表仅有一行(系统表)。这是const联接类型的一个特例。const表最多有一个匹配行它将在查询开始时被读取。因为仅有一行在这行的列值可被优化器剩余部分认为是常数。const用于用常数值比较PRIMARY KEY或UNIQUE索引的所有部分时。eq_ref对于每个来自于前面的表的行组合从该表中读取一行。这可能是最好的联接类型除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY。eq_ref可以用于使用 操作符比较的带索引的列。比较值可以为常量或一个使用在该表前面所读取的表的列的表达式。ref对于每个来自于前面的表的行组合所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀或如果键不是UNIQUE或PRIMARY KEY(换句话说如果联接不能基于关键字选择单个行的话)则使用ref。如果使用的键仅仅匹配少量行该联接类型是不错的。ref可以用于使用或操作符的带索引的列。ref_or_null该联接类型如同ref但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。index_merge该联接类型表示使用了索引合并优化方法。在这种情况下key列包含了使用的索引的清单key_len包含了使用的索引的最长的关键元素。unique_subquery该类型替换了下面形式的IN子查询的refvalue IN (SELECT primary_key FROMsingle_table WHERE some_expr);unique_subquery是一个索引查找函数可以完全替换子查询效率更高。index_subquery该联接类型类似于unique_subquery。可以替换IN子查询但只适合下列形式的子查询中的非唯一索引value IN (SELECT key_column FROM single_table WHERE some_expr)range只检索给定范围的行使用一个索引来选择行。key列显示使用了哪个索引。key_len包含所使用索引的最长关键元素。在该类型中ref列为NULL。当使用、、、、、、IS NULL、、BETWEEN或者IN操作符用常量比较关键字列时可以使用range index该联接类型与ALL相同除了只有索引树被扫描。这通常比ALL快因为索引文件通常比数据文件小。all对于每个来自于先前的表的行组合进行完整的表扫描。如果表是第一个没标记const的表这通常不好并且通常在它情况下很差。通常可以增加更多的索引而不要使用ALL使得行能基于前面的表中的常数值或列值被检索出。 possible_keyspossible_keys列指出MySQL能使用哪个索引在该表中找到行。注意该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。 keykey列显示MySQL实际决定使用的键(索引)。如果没有选择索引键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。 key_lenkey_len列显示MySQL决定使用的键长度。如果键是NULL则长度为NULL。注意通过key_len值我们可以确定MySQL将实际使用一个多部关键字的几个部分。 refref列显示使用哪个列或常数与key一起从表中选择行。 rowsrows列显示MySQL认为它执行查询时必须检查的行数。 Extra该列包含MySQL解决查询的详细信息。 DistinctMySQL发现第1个匹配行后停止为当前的行组合搜索更多的行。Not existsMySQL能够对查询进行LEFT JOIN优化发现1个匹配LEFT JOIN标准的行后不再为前面的的行组合在该表内检查更多的行。range checked for each record (index map: #)MySQL没有发现好的可以使用的索引但发现如果来自前面的表的列值已知可能部分索引可以使用。对前面的表的每个行组合MySQL检查是否可以使用range或index_merge访问方法来索取行。Using filesortMySQL需要额外的一次传递以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序并按排序顺序检索行。Using index从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。当查询只使用作为单一索引一部分的列时可以使用该策略。Using temporary为了解决查询MySQL需要创建一个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时。Using whereWHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行如果Extra值不为Using where并且表联接类型为ALL或index查询可能会有一些错误。Using sort_union(…), Using union(…), Using intersect(…)这些函数说明如何为index_merge联接类型合并索引扫描。Using index for group-by类似于访问表的Using index方式Using index for group-by表示MySQL发现了一个索引可以用来查询GROUP BY或DISTINCT查询的所有列而不要额外搜索硬盘访问实际的表。并且按最有效的方式使用索引以便对于每个组只读取少量索引条目。
通过相乘EXPLAIN输出的rows列的所有值你能得到一个关于一个联接如何的提示。这应该粗略地告诉你MySQL必须检查多少行以执行查询。当你使用max_join_size变量限制查询时也用这个乘积来确定执行哪个多表SELECT语句。
