从零开始的Linux运维屌丝之路,资源免费分享平台   运维人员首选:简单、易用、高效、安全、稳定、社区活跃的开源软件
  • 首页
  • MYSQL
  • MYSLQ体系结构和引擎知识介绍,应付面试足以

MYSLQ体系结构和引擎知识介绍,应付面试足以

发布:蔺要红09-01分类: MYSQL


MYSQL体系结构

连接者:不同语言的代码程序和mysql的交互(SQL交互)

1、连接池 管理、缓冲用户的连接,线程处理等需要缓存的需求
2、
管理服务和工具组件 系统管理和控制工具,例如备份恢复、Mysql复制、集群等 
3、
SQL接口 接受用户的SQL命令,并且返回用户需要查询的结果
4、
查询解析器 SQL命令传递到解析器的时候会被解析器验证和解析(权限、语法结构) 
5、
查询优化器 SQL语句在查询之前会使用查询优化器对查询进行优化
    select id,name from user where age = 40; 
    a、这个select 查询先根据where 语句进行选取,而不是先将表全部查询出来以后再进行age过滤 
    b、这个select查询先根据id和name进行属性投影,而不是将属性全部取出以后再进行过滤 
    c、将这两个查询条件联接起来生成最终查询结果 
6、
缓存 如果查询缓存有命中的查询结果,查询语句就可以直接去查询缓存中取数据
7、
插入式存储引擎 存储引擎说白了就是如何管理操作数据(存储数据、如何更新、查询数据等)的一种方法。因为在关系数据库 
中数据的存储是以表的形式存储的,所以存储引擎也可以称为表类型(即存储和操作此表的类型)



MYSQL最常用的引擎为MyISAM和InnoDB,5.5版本已经都支持,不同的引擎存储,引擎功能占用的空间大小,读取性能等可能有区别
show engines\G  # 查看mysql支持的引擎
ALTER TABLE `hong`.`honghong` ENGINE = MyISAM;   #修改表的引擎为MYISAM
ALTER TABLE `hong`.`honghong` ENGINE = InnoDB;

MyISAM和Innodb引擎数据文件存放的区别
# MyISAM引擎 一个表有3个文件
[root@test01 /data/3306/data/hong]# ll         
total 24K
-rw-rw---- 1 mysql mysql   61 2019-08-28 17:22 db.opt
-rw-rw---- 1 mysql mysql 8.4K 2019-09-01 18:37 honghong.frm  # 表的定义
-rw-rw---- 1 mysql mysql 2.9K 2019-09-01 18:37 honghong.MYD  # 表的数据
-rw-rw---- 1 mysql mysql 4.0K 2019-09-01 18:37 honghong.MYI  # 表的索引

# InnoDB引擎 一个表只有一个文件(共享表空间)
[root@test01 /data/3306/data/hong]# ll    
total 16K
-rw-rw---- 1 mysql mysql   61 2019-08-28 17:22 db.opt
-rw-rw---- 1 mysql mysql 8.4K 2019-09-01 18:55 honghong.frm     

[root@test01 /data/3306/data]# ll   
total 141M
drwx------ 2 mysql mysql   40 2019-09-01 18:55 hong
-rw-rw---- 1 mysql mysql 128M 2019-09-01 18:55 ibdata1     # 这里存储Inoodb数据
-rw-rw---- 1 mysql mysql 4.0M 2019-09-01 18:55 ib_logfile0   
-rw-rw---- 1 mysql mysql 4.0M 2019-09-01 18:55 ib_logfile1
-rw-rw---- 1 mysql mysql 4.0M 2019-08-28 11:39 ib_logfile2

#----------------或者如果Innodb如果独立表空间
# 独立表空间 每个库的数据目录下,每个表会单独有一个 ibd 文件,就不会存到 ibdata1里了
[root@yao_hong aaaa]# ll                 
总用量 112K
-rw-rw---- 1 mysql mysql   67 9月   1 20:33 db.opt
-rw-rw---- 1 mysql mysql 8.4K 9月   1 20:33 test.frm
-rw-rw---- 1 mysql mysql  96K 9月   1 20:33 test.ibd


InnoDB支持AICD事务    Atomicity(原子性)、Consistency(稳定性)、Isolation(隔离性)、Durability(可靠性)
重要参数
InnoDB 配置文件参数
innodb_data_home_dir = /www/server/data          # 数据存放目录
innodb_data_file_path = ibdata1:12M:autoextend   # 数据存放大小 ibdata1 控制此文件大小
innodb_file_per_table = 0     # 关闭独立的表空间,默认关闭 .    1为开启
innodb_buffer_pool_size = 2048M  # 内存的40%-80%,多实例 /2 (具体根据业务和内存大小来决定) 

InnoDB引擎特点(MySQL默认使用的引擎)
    1.支持事务:支持4个事务隔离级别,支持多版本读
    2.行级锁定(※更新时一般是锁定当前行):通过索引实现,全表扫描仍然会是表锁,注意间隙锁的影响。
    3.读写阻塞与事务隔离级别相关
    4.具有非常高效的缓存特性:能缓存索引,也能缓存数据。
    5.支持分区,表空间,类似 oracle数据库。
    6.支持外键约束,5.5以前不支持全文索引,以后支持了。
    7.和MyISAM引擎比,InnoDB对硬件资源要求比较高。
    
Innodb引擎调优。
    1.主键尽可能小,避免给 Secondary index带来过大的空间负担
    2.建立有效索引避免全表扫描,因为会使用表锁
    3.尽可能缓存所有的索引和数据,提高响应速度,减少磁盘IO消耗
    4.在大批量小插入的时候,尽量自己控制事务而不要使用autocommit自动提交,有开关可以控制提交方式;
    5、合理设置 innodb_flush_log_at_trx_commit参数值,不要过度追求安全性
    6、避免主键更新,因为这会带来大量的数据移动

MyISAM引擎 
    1.不支持事务
    2.表级锁定,数据更新时,锁定整个表,锁定机制是表级锁定,锁的实现比较简单,但同时大大降低了并发性能
    3.读写互相阻塞,不仅仅在写入的时候阻塞读取,MyISAM还会再读取的时候阻塞写入,但读本身不会阻塞另外的读
    4.只会缓存索引,可以通过key_buffer_size缓存索引,以大大提高访问性能减少磁盘IO,但是这个缓存只会缓存索引,不会缓存数据
    5.读取速度较快,占用资源相对较少
    6.不支持外键约束,但支持全文索引
    7.MySQL 5.5 前默认的存储引擎
    
MylSAM引擎适用的生产业务场景
    1.不需要事务支持的业务(例如转账就不行)。
    2.一般为读数据比较多的应用,读写都频繁场景不适合,读多或者写多的都适合。
    3.读写并发访问相对较低的业务(纯读纯写高并发也可以)(锁定机制问题)。
    4.数据修改相对较少的业务(阻塞问题)。
    5.以读为主的业务,例如:数据库系统表、ww,blog,图片信息数据库,用户数据库,商品库等业务
    6.对数据一致性要求不是非常高的业务(不支持事务)。
    7.硬件资源比较差的机器可以用 MyISAM(占用资源少)
    小结:单一对数据库的操作都可以使用 MyISAM,所谓单一就是尽量纯读,或纯写(insert,update,delete)等


面试题
1.MySQL引擎的种类
2.工作中如何选择MySQL引起
3.不同的引擎备份如何备份,混合引擎如何备份
# myisam或者混合
mysqldump -uroot -p111111 -A -x -B -F -R --master-data=2 gzip >/data/baks( date +%F).sql 
# innodb
mysqldump -uroot -p111111 -A -B -F -R --master-data=2 --single-transaction gzip >/data/baks( date +%F).sql 
 
附图:官方给出的各个引擎之前的区别
温馨提示如有转载或引用以上内容之必要,敬请将本文链接作为出处标注,如有侵权我会在24小时之内删除!

欢迎使用手机扫描访问本站