上篇文章说了;mysql5.6.6版本之前数据默认在系统表空间;之后默认在独立表空间;innodb因为索引和数据在一个b;树;所以两个文件;一个文件结构;一个存数据;myISAM则是三个文件。一个聚簇索引有两个段;叶子段和非叶子段;一个段有他专属的区;数据刚开始存在碎片区;不属于任何段;直属表空间。
系统表空间-mysql详解;四;https://blog.csdn.net/ke1ying/article/details/126861965
Mysql访问方法
Access method的概念主要通过两种方法获取数据;第一种是全表扫描获取;第二种是通过索引获取;至于采用什么方式访问代价更低;这是mysql优化器该做的事。
Const;当我们采用主键或者唯一键的时候;速度则是最高效的;但这是建立在查询条件没查询null的情况下;如果是查询null;因为就不是唯一;会查询到多条数据;这时候效率则是ref_null。
Ref;当我们用普通索引等值查询时候;这时候效率则是ref;他的经过是先查询二级索引b;树;再回表查询主表;这就意味着回表的数据越多;效率则越低;所以取决于二次查询的数据量;太多不会采用ref;当然;查询的前提也是没有null的情况。
Ref_or_null;当我们用索引列查询null的时候;效率就没有上面的高;于是就是当前等级。
Range;前面介绍都是等值查询;当前这个是范围查询;
Index;当我们查询的条件没有符合最左索引;但是查询的列都包含复合索引;结果查询条件是从第二列开始;这时候他不会走复合索引;但是他会遍历复合索引。
All;这就是直接全表扫描。
当有两个二级索引出现的时候;一般只会用一个二级索引来查询;比如a、b两个二级索引;mysql优化器;会看哪个索引查询的数据更少;再用更少的数据来回表查询聚簇索引真实数据;查到真实数据后在过滤其他条件。
联合索引;前面刚说了当两个二级索引出现的时候;只会有一个二级索引查询;这也不是百分百的;1、如果两个二级索引都是精确查找;没有区间查找;这时候也是会触发两个二级索引查询。2、当主键id区间查找;也会触发两个索引查询。
因为单独索引mysql优化器内部会有union或和intersection且的消耗;当时使用两个索引的时候;于是联合索引肯定是更优 的选择;在时间和空间都得到提升。
连接查询
当我们连接查询的时候;就有了驱动表的概念;mysql优化器会选择代价更小的作为驱动表;也就是第一个需要查询的表;而另一个表就是被驱动表;被驱动表查询的次数取决于驱动表查询数据的多少;驱动表查询一次;查询出来的数据;再多次查询被驱动表;多少条数据则查询多少次。
连接查询分为内连接和外连接;当查询的需求是驱动表有数据;而被驱动表没有数据;这时候则需要考虑用外连接;外连接则由on关键字需要使用;表示需要查询的结果即使没有查询到;也需要放入到结果集;而where查询的过滤条件不会放入结果集。Left join则是以左边表为驱动表;right join则是右边表为驱动表。
内连接和外连接最大的区别就是on后面的过滤条件;会不会在结果集返回;内连接的on可以直接理解为where。
Nested_loop join;前面说了;驱动表查询的时候;只会访问一次;然后通过结果集查询被驱动表;这时候被驱动表会查询多次;那如果三个表四个表进行查询呢;就需要再次重复第二步操作;用结果集再次查询第三张表;依次嵌套查询下去;这个过程就像是嵌套循环;所以连接查询超过三次则会非常影响效率。
虽然被驱动表是需要查询多次的;但是也是可以走索引查询;因为mysql优化器会把sql优化成单表查询;然后走索引;当表1的a字段和表2的b字段相等的情况下;直接把驱动表表1确定常数;然后把值带入b字段单表查询表2;从而可以走索引。
Join buffer;扫描表的过程是先把磁盘上的数据刷新到内存上;然后在处理join;但实际场景中查询数据太大;这时候后面的数据磁盘io的情况下;前面的数据就需要释放。于是joinbuffer就出来了;这个参数可以用join buffer size来设置大小;他会把驱动表的结果集放入这个内存中;当然更好的效果还是在被驱动表的查询条件上加上索引;这样查询效率更高。
