存储引擎
MyISAM
不支持事务,表级锁
InnoDB
支持事务,支持行级锁,事务ACID
Innodb 是一种事务性存储引擎,完全支持事务的ACID特性。
支持事务所需要的两个特殊日志类型:Redo Log 和Undo Log
Undo日志记录某数据被修改前的值,可以用来在事务失败时进行rollback;
Redo日志记录某数据块被修改后的值,可以用来恢复未写入data file的已成功事务更新的数据。
行级锁是由存储引擎层实现的
共享锁(读)和独占锁(写)
MySQL 使用 InnoDB 存储表时,会将表的定义和数据索引等信息分开存储,其中前者存储在 .frm 文件中,后者存储在 .ibd 文件中
InnoDB 存储引擎在绝大多数情况下使用 B+ 树建立索引,这是关系型数据库中查找最为常用和有效的索引
表空间
InnoDB使用表空间进行数据存储。
系统表空间无法简单的收缩文件大小,造成空间浪费,并会产生大量的磁盘碎片
独立表空间可以通过optimeze table 收缩系统文件,强烈建立对Innodb 使用独立表空间
体系结构
客户端 - 服务层 - 存储引擎
MySQL是插件式的存储引擎。
所有跨存储引擎的功能都是在服务层实现的。
MySQL的存储引擎是针对表的,不是针对库的。
MySQL数据库实例:
MySQL是单进程多线程(而oracle是多进程)
MySQL实例是线程和内存组成,实例才是真正用于操作数据库文件的;
一般情况下一个实例操作一个或多个数据库;集群情况下多个实例操作一个或多个数据库。
性能优化
优化原则
减少系统瓶颈,减少资源占用,增加系统的反应速度。
数据库结构设计和SQL语句
数据库存储引擎和参数配置
系统选择及优化
硬件升级
配置较大的内存
配置高速磁盘,比如SSD
合理分配磁盘IO
配置多核处理器
EXPLAIN 查看SQL执行计划
在使用LIKE关键字进行查询的查询语句中,如果匹配字符串的第一个字符为"%",索引不起作用。只有"%"不在第一个位置,索引才会生效
MySQL可以为多个字段创建索引,一个索引可以包括16个字段。对于联合索引,只有查询条件中使用了这些字段中第一个字段时,索引才会生效。
查询语句的查询条件中只有OR关键字,且OR前后的两个条件中的列都是索引时,索引才会生效,否则,索引不生效
子查询虽然很灵活,但是执行效率并不高。可以使用连接查询(JOIN)代替子查询,连接查询时不需要建立临时表,其速度比子查询快。
将字段很多的表分解成多个表
增加中间表 通过建立中间表,将需要通过联合查询的数据插入到中间表中,然后将原来的联合查询改为对中间表的查询
索引 NULL 列需要额外的空间来保存,所以要占用更多的空间。
进行比较和计算时要对 NULL 值做特别的处理。
事务
A 原子性
实现原理:undo log 逻辑日志
记录的是sql执行相关的信息。当发生回滚时,InnoDB会根据undo log的内容做与之前相反的工作
C 一致性
一致性是事务追求的最终目标:前面提到的原子性、持久性和隔离性,都是为了保证数据库状态的一致性
D 持久性
实现原理:redo log
数据是存放在磁盘中的,InnoDB提供了缓存(Buffer Pool)
redo log采用的是WAL(Write-ahead logging,预写式日志),所有修改先写入日志,再更新到Buffer Pool,保证了数据不会因MySQL宕机而丢失,从而满足了持久性要求。
刷脏是随机IO,因为每次修改的数据位置随机,但写redo log是追加操作,属于顺序IO。
刷脏是以数据页(Page)为单位的,MySQL默认页大小是16KB,一个Page上一个小修改都要整页写入;而redo log中只包含真正需要写入的部分,无效IO大大减少。
I 隔离性
脏读 :事务a读取到事务b未提交的内容
不可重复读:事务a先后两次读取同一数据,两次结果不同·读取到事务b提交的内容 update
幻读:事务a按照相同查询条件,读取导的数据的条数不同 insert
四种隔离级别 Read Uncommitted,Read Committed, Repeatable Read, Serializale
Mysql默认是RR,解决不了幻读问题。
解决幻读问题使用的MVCC
在同一时刻,不同的事务读取到的数据可能是不同的(即多版本)
binlog redo log
作用不同:binlog用于主从复制。
层次不同:redo log是InnoDB存储引擎实现的,而binlog是MySQL的服务器层实现的
内容不同:redo log是物理日志,内容基于磁盘的Page;binlog是逻辑日志,内容是一条条sql。
写入时机不同:binlog在事务提交时写入;redo log的写入时机相对多元
分区
一个表最多只能有1024个分
RANGE分区
LIST分区
HASH分区
KEY分区
注意事项
表事项
表字段避免null值出现,null值很难查询优化且占用额外的索引空间。
尽量使用INT而非BIGINT,如果非负则加上UNSIGNED(这样数值容量会扩大一倍),当然能使用TINYINT、SMALLINT、MEDIUM_INT更好。
使用枚举或整数代替字符串类型
尽量使用TIMESTAMP而非DATETIME
单表不要有太多字段,建议在20以内
用整型来存IP
索引事项
应尽量避免在WHERE子句中对字段进行NULL值判断,否则将导致引擎放弃使用索引而进行全表扫描
值分布很稀少的字段不适合建索引,例如"性别"这种只有两三个值的字段
不用外键,由程序保证约束
问题
为什么用B+树做索引的存储解构
哈希表的特点就是可以快速的精确查询,但是不支持范围查询。
完全平衡二叉树,因为是有序的,查询可能要多次
B树
B+树可以提高查询索引时的磁盘IO效率,并且可以提高范围查询的效率,并且B+树里的元素也是有序的
互联网项目请用:读已提交(Read Commited)这个隔离级别!
在RR隔离级别下,存在间隙锁,导致出现死锁的几率比RC大的多
在RR隔离级别下,条件列未命中索引会锁表!而在RC隔离级别下,只锁行
在RC隔离级别下,半一致性读(semi-consistent)特性增加了update操作的并发性!