java架构之路-(mysql底层原理)Mysql之让我们再深撸一次mysql

  让我再深撸一次mysql吧,这次主要以应对面试来说说mysql,大概几个方向,索引结构,查询引擎,索引优化,explain的详解和trace工具的使用。

索引:

我们先来看一下mysql的B+tree,本文几乎都在围绕这个图来说的。

mysql的底层是使用B+tree来存储数据的,和B+tree有一点点不同的是叶子节点是双向链表的结构,并不是图内的单向指针的。且null值放置在叶子节点的最前面。这个是主键索引。

下面我来看一下联合索引,比如我们现在有Student表,将name,age,address三个字段设置成联合索引,这时存储的节点变为先按照name排序,name一致按照age排序的B+tree,携带数据为主键ID,并不携带整体数据的。

查询引擎:

  我们常见的查询引擎主要是InnoDB还有MyISAM,区别主要是,MyISAM存储B+tree的索引携带数据都是内存地址,我们在查询的时候需要拿到hash计算后的内存地址,然后回行得到数据,而InnoDB直接携带数据,不需要回行,MyISAM不支持事物,不支持外键,也不支持行级锁,对于数据的非范围查询效率可能要高于InnoDB,且在底层有维护count总条数的内存,但是MyISAM的范围查询是不能用到索引的。我们大部分使用的都是InnoDB查询引擎,顺便提一下,MyISAM在磁盘上的文件为三个,一个是表的结构,一个是索引文件,一个是真正的数据文件,InnoDB在磁盘上存的是两个文件,一个是表结构文件,两一个是索引和数据文件。

explain的详解:

我们执行

explain select * from student;

这时会有十列数据,分别的是id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra。我们来逐个说一下他们都是干啥的。深入理解explain和B+tree的使用,mysql面试也就有救了。

id:就是一个编号,同时也代表了select的执行顺序,一般来说,我们有几个select就有几行数据,他们可能拥有相同或者不同的ID,执行顺序为ID大的优先执行,id相同,从上到下执行。id为null的最后执行。

select_type:代表我们的执行是一个什么样子的SQL,是简单查询啊,还是连表查询,大致可以分为

simple:简单查询,比如explain select * from table;

primary:复杂查询的最外层查询,(嵌套查询的最外层)比如explain select 1 from (select * from table) t;

subquery:子查询的select,但不表示在from后面的查询,比如explain select (select 1 from table) from table;或者explain select * from table where id = (select 1 from table where id = 2)

derived:和上面的subquery是相对的,表示在外层from后面的子查询。比如explain select * from (select * from table) t;

union:联合查询,explain select * from table union select * from table;

union result:表示联合查询后的组合,并不代表实际的select。

table:代表你查询的是哪一张表,如果表你给予了别名,这里会显示别名,会显示<derivedn>,标红色的n为执行计划里的id列。还有某些时候会显示<union1,2>,也就是说,我们合并id为1和2的结果虚拟表。有时候还会显示null,例如EXPLAIN SELECT 1 

type(超级重要)判断你SQL的运行效率的参数,依次从最优到最差分别为:system > const > eq_ref > ref > range > index > ALL

system一般是表为空,或者表里只有一行数据。 性能也是最好的,用左脚脚指头想想,数据都为空,或者只要一行,查询一定不会慢到哪里去。

const:用到了主键索引的查询,效率依然给力。主键索引叶子节点直接带着数据呢,不需要再去扫描第二颗树,效果一定给力了。

eq_ref:eq比较啊。所以简单的sql不会出现这个玩意。例如explain select * from table t innter join table2 t2 on t.pid = t2.id;也就是说该select下关联的一定是主键id,效率也是很OK的,后面会说一下innter join的查询机制。在trace的使用会说的,别慌,干货面试还没到来。

ref:相比eq_ref来说比较好记忆的,还是比较,也就是非主键索引的比较。例如explain select * from table t innter join table2 t2 on t.pid = t2.name;还是走索引的性能还是可以的。说明一下,这个type为ref,简单查询也可以出现,不一定是两张表关联才会出现的。

range:范围查询,也可以理解为索引范围查询。

index:扫描全表索引,比All强一点,说完All会举例。

All:垃圾了,全表扫描。

例如:explain SELECT classNum from  student; 就是一个index查询,因为classNum是一个非主键索引,所以在我们的节点上存储的,不需要携带id再次去第二个上扫描。

   explain SELECT classNum,create_time from  student;就是一个all查询,因为classNum虽然是一个非主键索引,可以拿到classNum的数据,但是我们却得不到create_time的数据,其实也可以通过classNum的索引树得到id,然后再拿着id去主键索引树上找create_time,需要查找两颗树,代价太大了,mysql底层帮我们优化了,在这里说一下啊。sql具体走不走索引和表内数据量一点关系都没有,不是说表里的数据大,就一定走索引或者不走索引,后面我们在trace会具体分析。

possible_keys:可能用到的索引。比如主键索引,非主键联合索引。

key:实际用到的索引列。
key_len:实际使用的索引长度,在联合索引用处还是比较大的,根据长度可以判断出来到底走了联合索引里面的几个字段。计算公式如下,
char(n):3n字节长度,
varchar(n):2字节存储字符串长度,如果是utf-8,则长度 3n +2,
tinyint:1字节
smallint:2字节
int:4字节
date:3字节
timestamp:4字节
datetime:8字节
Mysql版本不同计算会有所不同,但是都差不多的。
ref:这一列显示了在key列记录的索引中,表查找值所用到的列或常量,常见的有:const(常量),或者字段名。
rows:mysql预估的检测行数,不是最终查询到的行数,也不是表里一共有多少数据。只是一个预估值。
Extra(超级重要):这个字段可能性太多太多了,大家可以去阅读官方文档,在这里我简单说几个最常见的。
Using index:使用覆盖索引,比如:explain SELECT id  from  student where id  = 2
Using where:使用 where 语句来处理结果,查询的列未被索引覆盖,比如:explain SELECT *  from  student where name  = '2'
Using index condition:查询的列不完全被索引覆盖,where条件中是一个前导列的范围;比如:explain SELECT *  from  student where name  = '2' and stuNum=2
Using temporary:mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行 优化的,首先是想到用索引来优化。例如:explain SELECT DISTINCT create_time  from student t;
Using filesort:将用外部排序而不是索引排序,数据较小时从内存排序,否则需要在磁盘 完成排序。这种情况下一般也是要考虑使用索引来优化的。例如:explain SELECT  create_time  from student t order by create_time ;
Using filesort这个详细记录一下,后面通过题目来说明具体细节实现的。
  explain的工具大概就这么多东西了,通过执行计划我们可以得到一大部分sql的执行过程,我们还可以使用trace来具体看一下是否需要走索引,扫描一个树,和扫描两个树对查询的影响。
trace
首先我们先打开trace。
set session optimizer_trace="enabled=on",end_markers_in_json=on

直接在mysql控制台运行就OK的,平时没事别开这个玩意,会对性能有影响的。

然后我们运行sql;在后面加上SELECT * FROM information_schema.OPTIMIZER_TRACE;例如:

select * from student WHERE name > '张三';
SELECT * FROM information_schema.OPTIMIZER_TRACE;

这时我们看结果2中会有有这样一个数据

我们来主要看第二列,TRACE,复制出来弄到json解析器内。

然后我们查找一下cost这个参数,cost就是我们使用各个索引的一个指标,越大表示越差,只在一个sql内比较,不要在两个不同的sql比较啊。我们来看一下我的cost

这个是全表扫描大概是4.1。然后我继续向下看。

这有还有一个使用name_num_address这个联合索引的cost为3.41要比前面的4.1好,那么mysql选择走name_num_address联合索引。我这还有一个事例。EXPLAIN select * from student WHERE name > 'a';

正常来说,name是一个联合索引,我们拿按照name去范围查找,type列应该为range,其实不然,mysql并没有选择走任何索引。可以自己尝试用trace去看看执行过程。

由于我爱动mysql的默认配置,这里简单说一下using filesort排序,底层分为两中排序方式,一种是单路排序,也可以理解为一次排序或者叫非回溯排序,就是你的查询结果足够小(小于1024字节),mysql有一个

max_length_for_sort_data 的参数默认为1024字节,我们就将我们要排序的结果集拿到sort buffer中进行排序,如果大于1024字节,放不下啦,也就是双路排序,也可以叫回溯排序,就是我们只拿着需要排序的字段和唯一标识的id到sort buffer中进行排序,排序以后再回去找他们对应的数据,

这个就是双路排序,使用trace工具可以看到不同的using filesort,可以自己尝试。输入set max_length_for_sort_data = 字节数,可以自己更改这个参数,最好没事别动这个玩意。让DBA调,我们只是知道这么回事,太底层的还没深入研究。

  貌似说了这么多可以出几个面试题来聊聊了。

1、我们InnoDB的主键用数字自增好,还是UUID好?

答:当然是数字的好,还是回到我们的B+tree,这颗树是按照由小到大,由左向右来排列的。我们的数字便于我们去比较,UUID比较起来是很耗力耗时的。而且UUID比较占地方,mysql的B+tree的每个节点16KB大小,我们用数字类型,可在有限的空间内,有限的层级,存储更多的数据(其实没啥用了,三层的B+tree就可以存2000万的数据了)

而且最好是自动增长的,因为中间有间隙时,当我们插入的数据正好需要排列在间隙位置,可能会造成树的重新排列,影响效率。

2、为什么要设置is not null字段。

答:mysql对于null是不友好的,官方文档也是这样来说的,不建议使用null,null都放在B+tree最左侧,对于比较大小是很不利的。在sql语句中where ** is null 会直接不使用索引,与其null还不如给予其一个默认值。

3、什么是最左前缀原则。

答:我们在使用复合索引时必须要按照其顺序充分的使用,比如我们的联合索引为ABC三列,那我们想用C就一定先使用B,想使用B一定要使用A。范围查询以后的索引不再继续使用,并且不要做任何函数计算处理,也会不再走索引查询了。

再就是比如有一个字段varchar类型,我们在比较数字的时候一定要加“”,不然mysql底层会执行一个强转函数,从而造成不在走索引。

4、说说对于mysql的优化措施。个人总结。

答: 使用int类型作为主键,且自动增长。(一定要设置一个主键),设置索引字段is not null,索引字段要选择区分度高使用频率高的字段。

货币字段使用DECIMAL来存储。 很多事还要对应实际的业务需要来确定的。

  mysql的索引个人觉得差不多就这么多吧。关于索引的使用优化并没有说太多,这个还是需要靠个人经验的,心中有索引的存储模型,熟练使用explain我相信优化sql不成问题的。

下一期我们再来说说mysql的锁,事务,分布式还有日志。 还有MVCC

有一个点忘记说了,select count(name) from table 非主键索引是最快的。别不信,自己琢磨琢磨。自己去试(InnoDB)。MyISAM引擎有维持count的内存。

最进弄了一个公众号,小菜技术,欢迎大家的加入

posted @ 2019-08-27 14:14  小菜技术  阅读(593)  评论(0编辑  收藏