《MySQL实战45讲》个人总结

一、知识点 未完成 二、参数配置 推荐配置为READ-COMMITTED。 0:当事务提交后,Mysql仅仅是…

一、知识点

未完成

二、参数配置

推荐配置为READ-COMMITTED。

0:当事务提交后,Mysql仅仅是将binlog_cache中的数据写入binlog文件,但不执行fsync之类的磁盘 同步指令通知文件系统将缓存刷新到磁盘,而让Filesystem自行决定什么时候来做同步,这个是性能最好的。
n:在进行n次事务提交以后,Mysql将执行一次fsync之类的磁盘同步指令,同志文件系统将Binlog文件缓存刷新到磁盘。

0:log buffer将每秒一次地写入log file中,并且log file的flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候,不会主动触发写入磁盘的操作。
1:每次事务提交时MySQL都会把log buffer的数据写入log file,并且flush(刷到磁盘)中去,该模式为系统默认。
2:每次事务提交时MySQL都会把log buffer的数据写入log file,但是flush(刷到磁盘)操作并不会同时进行。该模式下,MySQL会每秒执行一次 flush(刷到磁盘)操作。

死锁超时时间,默认值50s。缺点:如果设置时间太短但容易把长时间锁等待释放掉。

发现死锁后,主动回滚死锁链条中的某一个事务,让其他事务得以继续执行。缺点:假设1000个线程更新同一行,则死锁检测要执行100万次。

OFF 存在共享表空间里,也就是跟数据字典放在一起;
ON 单独的文件,每个innodb表数据存储在以.ibd为后缀的文件中。

内存临时表的大小,默认是 16M。如果内存不够则使用磁盘临时表。

三、开发时需要注意的

做数据校对,例如判断上个月的余额和当前余额的差额,是否与本月的账单明细一致。希望在校对过程中,即使有用户发生了一笔新的交易,也不影响校对结果。

查看information_schema 库的 innodb_trx 表中的当前事务,等待事务结束或者 kill 该事务。(另外MariaDB支持DDL NOWAIT/WAIT n 语法避免长时间等待导致业务不可用)

自增主键的插入数据模式,都是追加操作,都不涉及到挪动其他记录,也不会触发叶子节点的分裂。并且自增主键在非主键索引占用的空间最小。

假设a、b两个字段都需要索引,a字段存储空间比b字段大,则建议建(a,b)和 b 两个索引。
假设有PRIMARY KEY(a,b)和KEY c,则不需要建(c,a)索引,可以建(c,b)索引。

在一个连接中循环执行 20 次 delete from T limit 500,避免长事务(delete from T limit 10000),避免多线程(20 个连接中同时执行 delete from T limit 500)

尽量选择普通索引,因为当更新记录的目标页不在内存中时,唯一索引需要将数据页读入内存,判断到没有冲突,插入这个值,语句执行结束;而普通索引来说,则是将更新记录在change buffer,语句执行就结束了。但是如果业务不能保证重复,就需要唯一索引保证。

平常不断地删除历史数据和新增数据的场景,mysql有可能会选错索引。sql太慢就用explain看看,有可能就是索引选错了。

直接使用字符串建索引有时候可能效率较低,存储空间较大

偶尔慢一下的那个瞬间,可能在刷脏页(flush)。innodb的redo log写满、buffer pool内存不足等情况。
合理地设置 innodb_io_capacity 的值,平时要多关注脏页比例,不要让它经常接近 75%。

删除某行,innodb只会标记删除。如果之后在该行范围内插入新数据就会复用。假如表本身没有多少空洞,重建索引可能会使表文件变大。
重建主键索引

不推荐drop,再add。并且不论是删除主键还是创建主键,都会将整个表重建。

Innodb需要一行一行读出来累积计数,MyISAM 引擎保存总行数,所以count很快。

不同的 count 用法效率
count(字段)<count(主键 id)<count(1)≈count(*),所以建议尽量使用 count(*)

Extra中”Using filesort”表示排序,mysql会给每个线程分配一个块内存(sort_buffer)用来排序。
假设从某个索引上取出来的行天然按照递增排序,就不需要再进行排序了。但维护索引是有代价的,所以需要权衡。

对索引字段做函数操作,可能会破坏索引值的有序性,因此优化器就决定放弃走树搜索功能。

limit删除数据时,只会扫描limit行数,不会继续扫描,所以加锁粒度更小。

statement 格式:由于没有记录行信息,删除时如果主备走的索引不一致会删除不同的行
row 格式:记录的行信息
mixed 格式:由于可能以statement格式记录,所以也会主备不一致

循环复制:规定两个库的 server id 必须不同,每个库收到主库发来的日志,判断server id是否和自己相同,相同直接丢弃日志。

在备库执行show slave status 命令,seconds_behind_master显示了当前备库延迟,精度秒。

上述过程会有一段时间不可用,假设4、5步互换会导致不一致。
binlog_format=mixed时,可能有两行不一致,binlog_format=row时会有一行不一致。
可靠性异常切换
假设主库掉电,必须等到备库B seconds_behind_master=0 之后,才能切换。

让小表做驱动表、被驱动表有索引。
如果被驱动表没有索引会走BNL算法,将驱动表加载到 join_buffer 中,将被驱动表中的数据一行行读出来与内存中的驱动表数据对比。
如果被驱动表是个大表,会把冷数据的page加入到buffer pool(join_buffer 用了其中的内存),并且BNL要扫描多次,两次扫描的时间可能会超过1秒,使上节提到的分代LRU优化失效,把热点数据从buffer pool中淘汰掉,影响正常业务的查询效率。

Multi-Range Read 优化
Batched Key Access:缓存读取多行传给被驱动表

除了给被驱动表加索引之外,还可以使用临时表,创建临时表然后加索引

分库分表系统都有一个中间层 proxy,如果 sql 能够直接确定某个分表,这种情况是最理想的。
但如果涉及到跨库,一般有两种方式:

执行相同的 insert 语句,发现了唯一键冲突,加上读锁(Next-key lock)。session A 回滚,session B 和 session C 都试图继续执行插入操作,都要加上插入意向锁(LOCK_INSERT_INTENTION)。
https://blog.csdn.net/varyall/article/details/80219459

假设我们现在的目标是在 db1 库下,复制一个跟表 t 相同的表 r:

innodb 只会锁一个分区,而 MyISAM 会锁所有的。

分区表的一个显而易见的优势是对业务透明,相对于用户分表来说,使用分区表的业务代码更简洁。还有,分区表可以很方便的清理历史数据。
按照时间分区的分区表,就可以直接通过 alter tablet drop partition …这个语法删掉分区,从而删掉过期的历史数据。

SQL 性能优化的目标:至少要达到 range 级别,要求是 ref 级别,如果可以是 consts最好。

本文来自网络,不代表软粉网立场,转载请注明出处:https://www.rfff.net/p/7590.html

作者: HUI

发表评论

您的电子邮箱地址不会被公开。

返回顶部