MySQL学习笔记
本文主要介绍MySQL的一些理论知识,尽量不介绍SQL指令,MySQL操作指令等,主要包括索引,优化,主从,日志,事务,存储等
索引
索引分类可以有很多角度,我们主要从数据结构来分类,MySQL支持B+树索引,Hash索引,Full-texts索引
MySQL存储引擎主要有InnoDB,MyISAM,Memory,下面是三种存储引擎对索引的支持情况
InnoDB | MyISAM | Memory | |
---|---|---|---|
B+树索引 | yes | yes | yes |
Hash索引 | no | no | yes |
Full-texts索引 | yes | yes | no |
可以看到B+树索引是最常用的索引类型
扩展平衡二叉树,B+tree,B-tree,hash表
平衡二叉树
平衡二叉树也叫AVL树,它或者是一颗空树,或者具有以下性质的二叉排序树:它的左子树和左子树的高度之差(平衡因子)的绝对值不超过1,且它的左子树和右子树都是一颗平衡二叉树。
AVL树有个问题是当数据量很大的时候,平衡二叉树的高度会很大,磁盘IO访问次数过多,导致性能很低,所以降低搜索的树的高度是关键。还有一点平衡二叉树逻辑很近的节点可能实际存储位置非常远,无法很好的利用磁盘预读(空间局部性原理)。
空间局部性原理:如果一个存储器的某个位置被访问,那么将它附近的位置也会被访问。
B-tree
B是balance平衡的意思,B树是一种多路自平衡查找树,类似于普通的平衡二叉树,不同的在于B树允许每个节点有更多的子节点。
特点:
- 键值和值都一起存在节点里
- 允许每个节点有更多的子节点(多叉树)
- 任何关键字只会出现在一个节点中
- 搜索有可能在非叶子节点结束
- 在关键字全集内做一次查找,性能逼近二分查找
相较于AVLtree性能更好的原因
-
更少的磁盘I/O操作:由于B树的高度较低,对于存储在磁盘上的数据结构来说,B树可以减少访问磁盘的次数。每次访问磁盘都是昂贵的操作,因此减少I/O操作可以显著提高性能。
-
更好的空间局部性:B树的设计允许它在内存中更好地利用缓存,因为B树的节点较大,可以一次性加载更多的数据到缓存中。这可以提高数据访问的速度,因为缓存的命中率更高。
B+树
与B树的区别在于
- B+树的所有键值对都存在叶子节点中,非叶子节点只存key,而不存储value
- B树的叶子节点没有特定的顺序或者链接,B+树的所有叶子节点通过指针链接在一起,形成一个有序链表,使得遍历数据更加高效
优势:
-
范围查询效率:B+树的叶子节点形成了一个有序链表,这使得对数据进行范围查询(如查找某个范围内的所有记录)更加高效。在B树中进行范围查询可能需要多次树的遍历,而在B+树中,只需沿着链表顺序访问即可。
-
磁盘I/O优化:由于B+树的非叶子节点只存储键,不存储完整的数据记录,这减少了非叶子节点的大小,使得每个节点可以包含更多的键,从而减少了树的高度。这进一步减少了磁盘I/O操作的次数。
-
空间利用:B+树的所有数据记录都存储在叶子节点中,这使得数据在磁盘上的存储更加紧凑,提高了空间利用率。
-
插入和删除操作:在B+树中,插入和删除操作通常只影响叶子节点,而非叶子节点的调整较少。这简化了维护操作,并可能减少因调整导致的磁盘I/O。
-
缓存效率:由于B+树的叶子节点形成了一个有序链表,缓存可以更有效地存储和访问这些节点。当访问一个节点时,相邻的节点也很可能被访问,因此缓存可以预先加载这些节点,提高缓存命中率。
Hash表
hash表相较于树不适合做范围查询,而范围查询在MySQL中是很常用的场景,hash表更适合等值查询
InnoDB的索引
InnoDB表的索引按照叶子节点存储的是否为完整表数据分为聚簇索引和二级索引。
以InnoDB的默认索引,B+tree索引举例,聚簇索引的每个叶子节点存储了一行完整的表数据,叶子节点间采用单向链表按id列递增连接,可以方便的进行顺序检索。
InnoDB表要求必须有聚簇索引,默认在主键字段上建立聚簇索引,在没有主键字段的情况下,表的第一个NOT NULL 的唯一索引将被建立为聚簇索引,在前两者都没有的情况下,InnoDB将自动生成一个隐式自增id列并在此列上创建聚簇索引。
而我们创建的二级索引的叶子节点并不存储一行完整的表数据,而是存储了聚簇索引所在列的值
例如我们建了一个InnoDB表,包括id,name,age三列,id为主键,那么默认id列上建立了聚簇索引,然后我们在name列上建立了二级索引,该二级索引的叶子节点存储的key是name,value是id列数据
正是由于二级索引不存储完整的一行记录数据,所以得根据二级索引查询到聚簇索引的列值后,还需要回到局促索引也就是表数据本身进一步获取数据。
但是如果查询语句只希望查询这里的name和id,那就不需要进行回表操作。这种情况二级索引称为覆盖索引。
回表需要额外的B+tree搜索过程,必然增大查询耗时。
MyISAM的索引
以MyISAM存储引擎存储的表不存在聚簇索引。
MyISAM表中的主键索引和非主键索引的结构是一样的。他们的叶子节点是不存储表数据的,节点中存放的是表数据的地址,所以MyISAM表可以没有主键。
性能优化
MySQl的性能优化手段可以从硬件,系统配置,表结构优化,SQL语句优化,索引优化几个方面考虑。
成本:硬件优化 > 系统配置优化 > 表结构优化 > SQL语句优化 > 索引优化。
效果:索引优化 > SQL语句优化 > 表结构优化 > 系统配置优化 > 硬件优化。
硬件优化
硬件优化无非就是对MySQL所在的服务器CPU,内存,磁盘进行优化。
系统配置优化
基本参数配置
Linux系统中MySQl配置文件一般位于/etc/my.cnf
- innodb_buffer_pool_size:缓冲池是数据和索引缓存的地方。默认大小为128M。这个值越大越好决于CPU的架构,这能保证你在大多数的读取操作时使用的是内存而不是硬盘。典型的值是5-6GB(8GB内存),20-25GB(32GB内存),100-120GB(128GB内存)。
- innodb_log_file_size:这是redo日志的大小。redo日志被用于确保写操作快速而可靠并且在崩溃时恢复。一方面你想让它更大来提高性能,另一方面你想让它更小来使得崩溃后更快恢复。到MySQL 5.5,redo日志的总尺寸被限定在4GB(默认可以有2个log文件)。这在MySQL 5.6里被提高。一开始就把innodb_log_file_size设置成512M(这样有1GB的redo日志)会使你有充裕的写操作空间。如果你知道你的应用程序需要频繁的写入数据并且你使用的时MySQL 5.6,你可以一开始就把它设置成4G。
- max_connections:最大的数据库连接数。max_connection值被设高了(例如1000或更高)之后一个主要缺陷是当服务器运行1000个或更高的活动事务时会变的没有响应。在应用程序里使用连接池或者在MySQL里使用进程池有助于解决这一问题。
InnoDB参数配置
- innodb_file_per_table:将所有表的数据和索引存放在共享表空间里(
innodb_file_per_table = OFF
) 或者为每张表的数据单独放在一个.ibd文件(innodb_file_per_table = ON
)。ON每张表一个文件允许你在drop、truncate或者rebuild表时回收磁盘空间。这对于一些高级特性也是有必要的,比如数据压缩。但是它不会带来任何性能收益。你不想让每张表一个文件的主要场景是:有非常多的表(比如10k+)。 - innodb_flush_log_at_trx_commit:默认值为1,表示InnoDB完全支持ACID特性。当你的主要关注点是数据安全的时候这个值是最合适的,比如在一个主节点上。但是对于磁盘(读写)速度较慢的系统,它会带来很巨大的开销,因为每次将改变flush到redo日志都需要额外的fsyncs。将它的值设置为2会导致不太可靠(reliable)因为提交的事务仅仅每秒才flush一次到redo日志,但对于一些场景是可以接受的,比如对于主节点的备份节点这个值是可以接受的。如果值为0速度就更快了,但在系统崩溃时可能丢失一些数据:只适用于备份节点。类似于redis持久化的RDB和AOF,经典的CAP定理
- innodb_flush_method:决定了数据和日志写入硬盘的方式
- innodb_log_buffer_size:决定了为尚未执行的事务分配的缓存。其默认值(1MB)一般来说已经够用了,但是如果你的事务中包含有二进制大对象或者大文本字段的话,这点缓存很快就会被填满并触发额外的I/O操作。
其他设置
- query_cache_size:query cache(查询缓存)是一个众所周知的瓶颈,甚至在并发并不多的时候也是如此。最佳选项是将其从一开始就停用,设置
query_cache_size = 0
(现在MySQL 5.6的默认值)并利用其他方法加速查询:优化索引、增加拷贝分散负载或者启用额外的缓存(比如memcache或redis)。 - log_bin:设置
log_bin=ON
可以启用二进制日志功能。二进制日志记录了数据库中所有修改数据的语句,如INSERT、UPDATE和DELETE操作,但不记录SELECT和SHOW这类的语句。它是实现数据复制和数据恢复的关键组件。 - skip_name_resolve:当客户端连接数据库服务器时,服务器会进行主机名解析,并且当DNS很慢时,建立连接也会很慢。因此建议在启动服务器时关闭skip_name_resolve选项而不进行DNS查找。唯一的局限是之后GRANT语句中只能使用IP地址了,因此在添加这项设置到一个已有系统中必须格外小心。
表结构优化
数据类型的选择
关于字段类型的优化建议主要适用于记录条数较多,数据量较大的场景,因为精细化的数据类型设置可能带来维护成本的提高,过度优化也可能会带来其他的问题。
-
数字类型:尽量不使用double,不仅仅只是存储长度的问题,同时还会存在精确性的问题。
同样,固定精度的小数,也不建议使用decimal,建议乘以固定倍数转换成整数存储,可以大大节省存储空间,且不会带来任何附加维护成本。
对于整数的存储,在数据量较大的情况下,建议区分开 tinyint / int / bigint 的选择,因为三者所占用的存储空间也有很大的差别,能确定不会使用负数的字段,建议添加unsigned定义。
-
字符类型:非万不得已不要使用 text 数据类型,其处理方式决定了它的性能要低于char或者是varchar类型的处理。
对于长度固定的字段,建议使用 char 类型,不定长度字段尽量使用 varchar,且仅仅设定适当的最大长度,而不是非常随意的给一个很大的最大长度限定,因为不同的长度范围,MySQL也会有不一样的存储处理。
(注意:char(n) 不管该字段是否存储数据,都占n个字符的存储空间;varchar 不存的时候不占空间,存多长数据就占多少空间,可以节省存储空间。)
-
时间类型:尽量使用timestamp类型,因为其存储空间只需要 datetime类型的一半。但是timestamp存储的数据所以被限制在了1970~2038年之内。
对于只需要精确到某一天的数据类型,建议使用date类型,因为他的存储空间只需要3个字节,比timestamp还少。
-
enum和set:对于状态字段,可以尝试使用enum来存放,因为可以极大的降低存储空间,而且即使需要增加新的类型,只要增加于末尾,修改结构也不需要重建表数据。
如果是存放可预先定义的属性数据呢?可以尝试使用set类型,即使存在多种属性,同样可以游刃有余,同时还可以节省不小的存储空间。
字符编码的选择
同样的内容使用不同字符集表示所占用的空间大小会有较大的差异。
1.纯拉丁字符能表示的内容,没必要选择latin1之外的其他字符编码,因为这会节省大量的存储空间。
2.如果我们可以确定不需要存放多种语言,就没必要非得使用utf8或者其他unicode字符编码,这会造成大量的存储空间浪费。
数据库表拆分
当我们的表中存在类似于text或者是很大的varchar类型的大字段的时候,如果我们大部分访问这张表的时候都用不到这个字段,就该将其拆分到另外的独立表中,以减少常用数据所占用的存储空间。这样好处就是每个数据块中可以存储的数据条数大大增加,既减少物理 IO 次数,也能大大提高内存中的缓存命中率。
数据库表适度冗余
如果某个字段被频繁的通过join两张表或者多张表来获取,join得到的结果又很多,这种情况可以考虑牺牲空间换时间。
default值尽量保证not null
null类型对于MySQL来说是难优化的,影响索引效率,占用额外空间,增大IO量
设置ID字段
我们应该为数据库里的每张表都设置一个ID做为其主键,而且最好的是一个INT型的(推荐使用UNSIGNED),并设置上自动增加的AUTO_INCREMENT(自增)标志。
尽量避免类似varchar这种类型作为主键,会使性能下降
SQL语句优化
1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。
2.应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。
3.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描。
4.应尽量避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描。
5.应尽量避免在 where 子句中使用like,否则将导致引擎放弃使用索引而进行全表扫描。
6.in 和 not in 也要慎用,否则会导致全表扫描。
7.如果在 where 子句中使用参数,也会导致全表扫描。
8.应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。
9.应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。
10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。
11.在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用,并且应尽可能的让字段顺序与索引顺序相一致。
12.不要写一些没有意义的查询。
13.很多时候用 exists 代替 in 是一个好的选择。
14.并不是所有索引对查询都有效,SQL是根据表中数据来进行查询优化的,当索引列有大量数据重复时,SQL查询可能不会去利用索引
15.索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率。一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有必要。
16.尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。
17.尽可能的使用 varchar/nvarchar
代替 char/nchar
18.任何地方都不要使用select * from t
,用具体的字段列表代替*
,不要返回用不到的任何字段。
MySQL日志
MySQL 中有七种日志文件,分别是:
- 二进制日志(bin log)
- 重做日志(redo log)
- 回滚日志(undo log)
- 错误日志(error log)
- 慢查询日志(slow query log)
- 一般查询日志(general log)
- 中继日志(relay log)
二进制日志(bin log)
记录了数据库所有执行的DDL和DML等数据库更新事件的语句,但是不包含没有修改任何数据的语句(如数据查询语句select、show等)。
bin log主要作用有两个:
- 数据复制:master把它的二进制日志传递给slaves,slaves 回放来达到 master-slave 数据一致的目的
- 数据恢复:如果MySQL数据库意外停止,可以通过二进制日志文件来查看用户执行了哪些操作,对数据库服务器文件做了哪些修改,然后根据二进制日志文件中的记录来恢复数据库服务器。
重做日志(redo log)
只记录事务对数据页做了哪些修改。redo log 包括两部分:一个是内存中的日志缓冲( redo log buffer ),另一个是磁盘上的日志文件( redo logfile)。mysql 每执行一条 DML 语句,先将记录写入 redo log buffer,后续某个时间点再一次性将多个操作记录写到 redo log file。这种 先写日志,再写磁盘 的技术就是 MySQL里经常说到的 WAL(Write-Ahead Logging) 技术。
在计算机操作系统中,用户空间( user space )下的缓冲区数据一般情况下是无法直接写入磁盘的,中间必须经过操作系统内核空间( kernel space )缓冲区( OS Buffer )。因此, redo log buffer 写入 redo logfile 实际上是先写入 OS Buffer ,然后再通过系统调用 fsync() 将其刷到 redo log file中。
中继日志(relay log)
中继日志用于主从复制架构中的从服务器上,从服务器的 slave 进程从主服务器处获取二进制日志的内容并写入中继日志,然后由 IO 进程读取并执行中继日志中的语句。
回滚日志(undo log)
undo log是一种用于撤销回退的日志,在事务没提交之前,MySQL会先记录更新前的数据到 undo log日志文件里面,当事务回滚时或者数据库崩溃时,可以利用 undo log来进行回退。
undo log有两个作用:提供回滚和多个行版本控制(MVCC)。
undo log和redo log记录物理日志不一样,它是逻辑日志。可以认为当delete一条记录时,undo log中会记录一条对应的insert记录,反之亦然,当update一条记录时,它记录一条对应相反的update记录。
错误日志(error log)
用来记录 MySQL 服务器运行过程中的错误信息,默认开启无法关闭。
一般查询日志(general log)
一般查询日志里面记录了数据库执行的所有命令,不管语句是否正确,都会被记录,默认是关闭的(建议关闭)。
慢查询日志(slow query log)
用来记录在MySQL中响应时间超过阀值的SQL语句,具体是指运行时间超过 long_query_time
值的SQL,这样的SQL则会被记录到慢查询日志中。long_query_time
的默认值为10,意思是运行10S以上的SQL语句。
事务
ACID
理论是指在数据库管理系统(DBMS)
中事务所具有的四个特性:
- 原子性(
Atomicity
):多个操作放到一个事务中,保证这些操作要么都成功,要么都不成功;MySQL的事务支持操作的回滚,所以支持原子性 - 一致性(
Consistency
):数据库中的增删改操作,使数据库不断从一个一致性的状态转移到另一个一致性的状态
。拿银行转账来说,A转给B 1000元,那么一致性意思就是A账户必须-1000,B账户必须+1000,不应该存在A账户-1000,B账户不变这种情况。 - 隔离性(
Isolation
,又称独立性):多个事务之间互相不干扰,即使是并发事务的情况下,他们只是两个并发执行没有交集,互不影响 - 持久性(
Durability
):持久性就是当某个操作操作完毕了,那么结果就是这样了,并且这个操作会持久化到日志记录中。
MySQL事务的隔离级别
事务操作可能会出现的数据问题
- 脏读
事务A修改了数据,但未提交,而事务B查询了事务A修改过却没有提交的数据,这就是脏读,因为事务A可能会回滚 - 不可重复读
事务A 先查询了金额,是200块钱,未提交 。事务B在事务A查询完之后,修改了金额,变成了300, 在事务A前提交了;如果此时事务A再查询一次数据,就会发现钱跟上一次查询不一致,是300,而不是200。这就是不可重复读。强调事务A对要操作的数据被别人修改了,但在不知请的情况下拿去做之前的用途 - 幻读
事务A先修改了某个表的所有纪录的状态字段为已处理,未提交;事务B也在此时新增了一条未处理的记录,并提交了;事务A随后查询记录,却发现有一条记录是未处理的,很是诧异,刚刚不是全部修改为已处理嘛,以为出现了幻觉,这就是幻读
tip:不可重复读和幻读两者有些相似。但不可重复读重点在于update和delete,而幻读的重点在于insert。
事务的隔离级别的标准
- Read Uncommitted 读未提交
限制最弱的事务级别,忽略其他事务放置的锁,该级别下的事务可以在读取其他事务修改后(插/删/更
)但未提交的的数据;说白了这个级别的事务就是个弱鸡,只能保证多个操作的原子性,完整不能解决并发问题;无法解决脏读,不可重复读,幻读
- Read Committed 读已提交
SQL Server默认级别,指定事务执行期间(未提交)不能读取其他事务还未提交的数据,禁止脏读;但可以读取到其他事务 (插/删/更)
操作后并提交了的 数据;从而造成多次查询的数据不一致,即不可重复读,同时也不法避免幻读,因为当前事务执行期间,其他事务可以插入新记录
- Repeatable Read 可重复读
MySQL的默认级别,事务执行期间(未提交)不能读取其他事务还未提交的数据,也不能其他事务读取更新
操作后并提交的数据,所以可以避免脏读和不可重复读;但是可以读取到其他事务插/删
操作并提交的数据,因此因为可以读取到其他事务插
入并提交的新数据,所以可能会造成幻读
- Serializable 事务同步,串行
最严格的事务管理,等于事务同步执行,不允许并发,所以不存在并发问题,所有问题都解决了,但是存在性能问题,执行数据慢
级别隔离强大:读未提交
<读已提交
<可重复读
<串行
锁机制
MySQL支持三种层级的锁定,分别为:
- 表级锁定
表级锁是MySQL中锁定粒度最大的一种锁,表示对当前操作的整张表加锁,它实现简单,资源消耗较少,被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定
- 页级锁定
页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快,但冲突多,行级冲突少,但速度慢。所以取了折衷的页级,一次锁定相邻的一组记录。BDB支持页级锁
- 行级锁定
行级锁是Mysql中锁定粒度最细的一种锁,表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小,但加锁的开销也最大
从上到下,锁的粒度逐渐细粒化, 但实现开销逐渐增大。
MyISAM引擎所支持的锁
MyISAM只支持表锁,不支持行锁和页面锁。
- 表共享读锁(表级共享锁)
获得表共享读锁的事务可以读取该表的任意数据。MyISAM引擎在执行select
语句前,会自动给涉及的表加表共享读锁
- 表独占写锁(表级排他锁)
获得表独占写锁的事务可以更新或删除该表中任意数据。在执行update,delete,insert
等语句前,会自动给涉及的表加表独占写锁
InnoDB引擎所支持的锁
InnoDB存储引擎,不仅仅支持表级锁定,还支持行级锁定。
-
共享锁和排他锁 (Shared and Exclusive Locks)
-
意向锁(Intention Locks)
-
记录锁(Record Locks)
-
间隙锁(Gap Locks)
-
临键锁 (Next-Key Locks)
-
插入意向锁(Insert Intention Locks)
-
主键自增锁 (AUTO-INC Locks)
-
空间索引断言锁(Predicate Locks for Spatial Indexes)
-
表级共享锁
表级共享锁,又称为表共享读锁
,既在表的层级上对数据加以共享锁,实现读读共享
- 表级排他锁
表级排他锁,又称为表独占写锁
,既在表的层级上对数据加以排他锁,实现读写互斥,写写互斥
- 行级共享锁
行级共享锁既在行的层级上,对数据加以共享锁,实现对该行数据的读读共享
- 行级排他锁
行级排他锁既在行的层级上,对数据加以排他锁,实现对该行数据的读写互斥,写写互斥
意向锁
通常情况下,表锁和行锁是相互冲突的,既获得了表锁,就无法再获得该表具体行的行锁,反之亦然。但是有的时候表锁和行锁实现部分的共存有利于更细粒度的对锁进行控制,以便得到更加的并发性能。所以InnoDB存储引擎支持多粒度(granular)锁定, 这种锁定允许一个事务中同时存在行锁和表锁。
所以InnoDB为了实现行锁和表锁共存的多粒度锁机制特性,InnoDB存储引擎也就还支持一种额外的锁方式以对多粒度锁机制进行支持,称之为意向锁(Intention Lock)。
- 意向锁(Intention Lock)就是一种不与行级锁冲突的
表级锁
- 意向锁的主要目的展示出某事务已对表中某行加锁,或即将对表中某行加锁
- 意向锁就是指,未来的某个时刻,事务可能要对某行加共享锁或排它锁了,先提前声明一个意向
并且意向锁具体实现又可以分为以下两种:
- 意向共享锁(
intention shared lock
,IS
)
事务打算给表中的某些行加行级共享锁,事务在给某些行加S锁
前必须先取得该表的IS锁
。
- 意向排他锁(
intention exclusive lock
,IX
)
事务打算给表中的某些加行级排他锁,事务在给某些行加X锁
前必须先取得该表的IX锁
。
怎么使用意向锁?
- 意向锁的维护是由存储引擎隐式帮我们做了,不需要程序员操心!
- 既意向锁是由存储引擎自己维护的,用户无法手动操作意向锁,在为数据行加共享 / 排他锁之前,InooDB 会先获取该数据行所在的表的对应意向锁