MySQL-逻辑架构和存储引擎

前言

本文纪录了MySQL的逻辑架构和MySQL执行流程，开启show_profile查看执行计划，MyISAM和InnoDB对比，以及group by使用原则。

逻辑架构

MySQL系统架构图

MySQL逻辑架构图

show_profile执行周期

• 修改配置

  # 修改配置文件/etc/my.cf，新增一行,开启缓存，0表示关闭，1表示打开
  query_cache_type=1
  
  # 重启mysql
  systemctl restart mysqld
  
  # 查看mysql启动状态
  systemctl status mysqld

• 开启profiling

  # 先查看profiling状态
  mysql> show variables like '%profiling%';
  
  # 开启profiling
  mysql> set profiling =1;

• 查看执行周期

  # 执行一条sql，然后可以查看执行计划。
  mysql> select * from mytbl2 where id =2;
  mysql> show profiles ;
  mysql> show profile cpu ,block io for query Query_ID;

• sql执行顺序

  select distinct 
          <select_list>
  from 
          <left_table> <join_type>
  join <right_table> on <join_condition>
  where 
          <where_condition>
  group by 
          <group_by_list>
  having 
          <having_condition>
  order by 
          <order_by_condition>
  limit <limit_number>

• 总结
  多次执行相同sql时，查询缓存中的数据。只能是相同sql，因为类似redis存储的是键值对。

存储引擎

• 查看数据库引擎

  mysql> show engines;

• MyISAM和InnoDB对比

  对比项	MyISAM	InnoDB
  外键	不支持	支持
  事务	不支持	支持
  行表锁	表锁，即使操作一条纪录也会锁住整个表，不适合高并发操作	行锁，操作时只锁定某一行，不对其他行有影响
  缓存	只缓存索引，不缓存真是数据	不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定行的影响
  关注点	节省资源、消耗少、简单业务	并发写、事务、更大资源
  默认安装	Y	Y
  默认使用	Y	Y
  自带系统表使用	Y	N

其他配置

• 建表

  create table mytbl2 (id int,name varchar(200), age int ,dept int);
  insert into mytbl2 values (1,'zhang3',33,101);
  insert into mytbl2 values (2,'li4',34,101);
  insert into mytbl2 values (3,'wang5',34,102);
  insert into mytbl2 values (4,'zhao6',34,102);
  insert into mytbl2 values (5,'tian7',36,102);
  
  # 错误查询
  SELECT name, dept, max(age) from mytbl2 group by dept;
  # 正确查询
  select * from mytbl2 m inner join (
  select dept, max(age) maxage from mytbl2 group by dept
  ) ab on ab.dept = m.dept and m.age=ab.maxage;

  group by使用原则：select后面只能放函数和group by后相关的字段

• 查看sql_mode

  show variables like 'sql_mode';