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
附图:官方给出的各个引擎之前的区别