1,MySQL的逻辑架构
MySQL可以分为Server层和存储引擎层两部分。
Server层包括连接器、查询缓存、分析器、优化器、执行器等,涵盖MySQL的大多数核心服务功能以及所有的内置函数(如日期、时间、数学和加密函数等),所有跨存储引擎的功能都在这一层实现,比如存储过程、触发器、视图等。
而存储引擎层负责数据的存储和提取。其架构模式是插件式的,支持InnoDB、MyISAM、Memory等多个存储引擎。现在最常用的存储引擎是InnoDB,它从MySQL 5.5.5版本开始成为了默认存储引擎。不同的存储引擎共用一个Server层,也就是从连接器到执行器的部分。
2,redo log(重做日志)和 binlog(归档日志)
这两种日志有以下三点不同。
1),redo log是InnoDB引擎特有的,负责存储相关的具体事宜,有了redo log,InnoDB就可以保证即使数据库发生异常重启,之前提交的记录都不会丢失,这个能力称为crash-safe。
binlog是MySQL的Server层实现的,所有引擎都可以使用,它主要做的是MySQL功能层面的事情。
2),redo log是物理日志,记录的是“在某个数据页上做了什么修改”;binlog是逻辑日志,记录的是这个语句的原始逻辑,比如“给ID=2这一行的c字段加1 ”。
3),redo log是循环写的,空间固定会用完;binlog是可以追加写入的。“追加写”是指binlog文件写到一定大小后会切换到下一个,并不会覆盖以前的日志。
3,事务与隔离
1),务的特性:ACID(Atomicity、Consistency、Isolation、Durability,即原子性、一致性、隔离性、持久性)
2),多事务同时执行的时候,可能会出现的问题:脏读、不可重复读、幻读
3),事务隔离级别:读未提交、读提交、可重复读、串行化
4),不同事务隔离级别的区别:
- 读未提交:一个事务还未提交,它所做的变更就可以被别的事务看到
- 读提交:一个事务提交之后,它所做的变更才可以被别的事务看到
- 可重复读:一个事务执行过程中看到的数据是一致的。未提交的更改对其他事务是不可见的
- 串行化:对应一个记录会加读写锁,出现冲突的时候,后访问的事务必须等前一个事务执行完成才能继续执行
5),配置方法:启动参数transaction-isolation
4,数据库的索引
-
索引的作用:其实就是为了提高数据查询的效率,就像书的目录一样。
索引的常见模型:
1,哈希表这种结构适用于只有等值查询的场景。
2,有序数组索引只适用于静态存储引擎。
3,二叉搜索树:为了维持O(log(N))的查询复杂度,你就需要保持这棵树是平衡二叉树。为了做这个保证,更新的时间复杂度也是O(log(N))。 -
InnoDB 的索引模型:主键索引的叶子节点存的是整行数据。在InnoDB里,主键索引也被称为聚簇索引(clustered index)。非主键索引的叶子节点内容是主键的值。在InnoDB里,非主键索引也被称为二级索引(secondary index)。也就是说,基于非主键索引的查询需要多扫描一棵索引树。因此,我们在应用中应该尽量使用主键查询,避免回表查询。
InnoDB采用的:B+树结构,B+树能够很好地配合磁盘的读写特性,减少单次查询的磁盘访问次数。 -
主键索引的选择:自增主键是指自增列上定义的主键,插入新记录的时候可以不指定ID的值,系统会获取当前ID最大值加1作为下一条记录的ID值。自增主键的插入数据模式,每次插入一条新记录,都是追加操作,都不涉及到挪动其他记录,也不会触发叶子节点的分裂。而有业务逻辑的字段做主键,则往往不容易保证有序插入,这样写数据成本相对较高。MySQL 5.6 引入的索引下推优化(index condition pushdown), 可以在索引遍历过程中,对索
引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。
5,全局锁和表锁
在MySQL 5.5版本中引入了MDL(metadata lock),当对一个表做增删改查操作的时候,加MDL读锁;当要对表做结构变更操作的时候,加MDL写锁。
读锁之间不互斥,因此你可以有多个线程同时对一张表增删改查。
读写锁之间、写锁之间是互斥的,用来保证变更表结构操作的安全性。因此,如果有两个线程要同时给一个表加字段,其中一个要等另一个执行完才能开始执行
6,索引的选择
创建唯一索引对应的字段也必须唯一。
- 对于查找操作,唯一索引只比普通多度一次,差别不大。
- 对于更新操作,当这个记录要更新的目标页在内存中时,唯一索引只多一次判断,只会耗费微小的CPU时间。当更新的目标页不在内存中时,对于唯一索引来说,需要将数据页读入内存,判断到没有冲突,插入这个值,语句执行结束;对于普通索引来说,则是将更新记录在change buffer,语句执行就结束了。将数据从磁盘读入内存涉及随机IO的访问,是数据库里面成本最高的操作之一。
change buffer因为减少了随机磁盘访问,所以对更新性能的提升是会很明显的。有一种特殊场景,假设一个业务的更新模式是写入之后马上会做查询,那么即使满足了条件,将更新先记录在change buffer,但之后由于马上要访问这个数据页,会立即触发merge过程。这样随机访问IO的次数不会减少,反而增加了change buffer的维护代价。所以,对于这种业务模式来说change buffer反而起到了副作用。
总结:对于普通索引和唯一索引应该怎么选择。其实,这两类索引在查询能力上是没差别的,主要考虑的是对更新性能的影响。所以,建议尽量选择普通索引。
补充:redo log 主要节省的是随机写磁盘的IO消耗(转成顺序写),而change buffer主要节省的则是随机读磁盘的IO消耗。
7,优化器选择索引
用explain +sql查询语句,可以通过rows字段显示估计要扫描的行数。
加了limit 1 能减少扫描多少行,其实优化器也不确定,【得执行才知道】,所以显示的时候还是按照“最多可能扫多少行”来显示。
对于由于索引统计信息不准确导致的问题,你可以用analyze table来解决。而对于其他优化器误判的情况,你可以在应用端用force index来强行指定索引,也可以通过修改语句来引导优化器,还可以通过增加或者删除索引来绕过这个问题。
8,收缩数据库的表空间
delete命令其实只是把记录的位置,或者数据页标记为了“可复用”,但磁盘文件的大小是不会变的。也就是说,通过delete命令是不能回收表空间的。这些可以复用,而没有被使用的空间,看起来就像是“空洞”。实际上,不止是删除数据会造成空洞,插入数据也会。更改其实是删除加插入,也会记录的复用,只限于符合范围条件的数据。而当整个页从B+树里面摘掉以后,可以复用到任何位置。如果相邻的两个数据页利用率都很小,系统就会把这两个页上的数据合到其中一个页上,另外一个数据页就被标记为可复用。而重建表,就可以达到这样的目的。
在MySQL 5.5版本之前,可以使用alter table A engine=InnoDB(alter table t
engine=innodb,ALGORITHM=inplace;跟inplace对应的就是拷贝表的方式了)命令来重建表,但是有新的数据要写入的话,就会造成数据丢失,这个DDL不是Online的。
而在MySQL 5.6版本开始引入的Online DDL(生成临时文件的过程中,将所有对A的操作记录在一个日志文件(row log)中)DDL过程如果是Online的,就一定是inplace的;反过来未必
从MySQL 5.6版本开始,alter table t engine = InnoDB(也就是recreate);analyze table t 其实不是重建表,只是对表的索引信息做重新统计,没有修改数据,这个过程中加了MDL读锁;optimize table t 等于recreate+analyze。
9,计算一个表的行数
- count()的实现方式
MyISAM引擎把一个表的总行数存在了磁盘上,因此执行count()的时候会直接返回这个数,效率很高,如果加了where 条件的话,MyISAM表也是不能返回得这么快的;而InnoDB引擎就麻烦了,它执行count()的时候,需要把数据一行一行地从引擎里面读出来,然后累积计数。
按照效率排序的话,count(字段)<count(主键id)<count(1)≈count(),所以我建议你,尽量使用count(*)。
10,orderby的工作顺序
- 场景:city字段建立了索引
select city,name,age from t where city=‘杭州’ order by name limit 1000 ;
通常情况下,这个语句执行流程如下所示 :
1,初始化sort_buffer,确定放入name、city、age这三个字段;
2,从索引city找到第一个满足city='杭州’条件的主键id,也就是图中的ID_X;
3,到主键id索引取出整行,取name、city、age三个字段的值,存入sort_buffer中;
4,从索引city取下一个记录的主键id;
5,重复步骤3、4直到city的值不满足查询条件为止,对应的主键id也就是图中的ID_Y;
6,对sort_buffer中的数据按照字段name做快速排序;
7,按照排序结果取前1000行返回给客户端。
在数据量很大的情况下,适当的使用LIMIT 1对查询操作的优化效果还是相当明显的。
注意:如果以上表字段中username被设置为了索引的话,这个时候使用LIMIT 1在查询速度上没有明显的效果。
11,索引会失效
-
第一种:对索引字段做函数操作,可能会破坏索引值的有序性,因此优化器就决定放弃走树搜索功能。
mysql> select count() from tradelog where month(t_modified)=7;
改写成:mysql> select count() from tradelog where
-> (t_modified >= ‘2016-7-1’ and t_modified<‘2016-8-1’) or
-> (t_modified >= ‘2017-7-1’ and t_modified<‘2017-8-1’) or
-> (t_modified >= ‘2018-7-1’ and t_modified<‘2018-8-1’);
select * from tradelog where id + 1 = 10000
应该改写成 where id = 10000 -1 -
第二种:隐式类型转换,mysql> select * from tradelog where tradeid=110717;tradeid的字段类型是varchar(32),而输入的参数却是整型,所以需要做类型转换。在MySQL中,字符串和数字做比较的话,是将字符串转换成数字。
12,查询慢可能的原因
1),有100万行数据,其中有10万行数据的b的值是’1234567890’,字段b定义的是varchar(10),mysql> select * from table_a where b=‘1234567890abcd’;在传给引擎执行的时候,做了字符截断。因为引擎里面这个行只定义了长度是10,所以只截了前10个字节,就是’1234567890’进去做匹配;这样满足条件的数据有10万行;因为是select *, 所以要做10万次回表;但是每次回表以后查出整行,到server层一判断,b的值都不是’1234567890abcd’;返回结果是空。
2),mysql> select * from t where c=50000 limit 1;由于字段c上没有索引,这个语句只能走id主键顺序扫描,因此需要扫描5万行。
3),用select * from t where id=1 lock in share mode,能避免在可重复读情况下,其他会话大量的更新语句
4),等flush。出现Waiting for table flush状态的可能情况是:有一个flush tables命令被别的语句堵住了,然后它又堵住了我们的select语句。
5),等DML锁
13,如果你的MySQL现在出现了性能瓶颈,而且瓶颈在IO上,可以通过哪些方法来提升性能呢?
1),设置 binlog_group_commit_sync_delay 和
binlog_group_commit_sync_no_delay_count参数,减少binlog的写盘次数。这个方法是基于“额外的故意等待”来实现的,因此可能会增加语句的响应时间,但没有丢失数据的风险。
2),将sync_binlog 设置为大于1的值(比较常见是100~1000)。这样做的风险是,主机掉电时会丢binlog日志。
3),将innodb_flush_log_at_trx_commit设置为2。这样做的风险是,主机掉电的时候会丢数据。我不建议你把innodb_flush_log_at_trx_commit 设置成0。因为把这个参数设置成0,表示redo log只保存在内存中,这样的话MySQL本身异常重启也会丢数据,风险太大。而redo log写到文件系统的page cache的速度也是很快的,所以将这个参数设置成2跟设置成0其实性能差不多,但这样做MySQL异常重启时就不会丢数据了,相比之下风险会更小。
14,加锁规则包含了两个“原则”、两个“优化”和一个“bug”
- 原则1:加锁的基本单位是next-key lock。希望你还记得,next-key lock是前开后闭区间。
- 原则2:查找过程中访问到的对象才会加锁。
- 优化1:索引上的等值查询,给唯一索引加锁的时候,next-key lock退化为行锁。
- 优化2:索引上的等值查询,向右遍历时且最后一个值不满足等值条件的时候,next-key lock退化为间隙锁。
- 一个bug:唯一索引上的范围查询会访问到不满足条件的第一个值为止。
15,怎么用join
1),select * from t1 straight_join t2 on (t1.a=t2.a);straight_join让MySQL使用固定的连接方式执行查询,在这个语句里,t1 是驱动表,t2是被驱动表。
2),应该让有索引的表作为被驱动表,这样可以走树搜索,而无索引走的是全盘扫描,因此整个执行过程,近似复杂度是 N + N2log2M。
3),应该让小表来做驱动表。
16,InnoDB和Memory引擎
InnoDB和Memory引擎的数据组织方式是不同的:
InnoDB引擎把数据放在主键索引上,其他索引上保存的是主键id。这种方式,我们称之为索引组织表(Index Organizied Table)。
而Memory引擎采用的是把数据单独存放,索引上保存数据位置的数据组织形式,我们称之为堆组织表(Heap Organizied Table)。
从中我们可以看出,这两个引擎的一些典型不同:
1),InnoDB表的数据总是有序存放的,而内存表的数据就是按照写入顺序存放的;
2),当数据文件有空洞的时候,InnoDB表在插入新数据的时候,为了保证数据有序性,只能在固定的位置写入新值,而内存表找到空位就可以插入新值;
3),数据位置发生变化的时候,InnoDB表只需要修改主键索引,而内存表需要修改所有索引;
4),InnoDB表用主键索引查询时需要走一次索引查找,用普通索引查询的时候,需要走两次索引查找。而内存表没有这个区别,所有索引的“地位”都是相同的。
5),InnoDB支持变长数据类型,不同记录的长度可能不同;内存表不支持Blob 和 Text字段,并且即使定义了varchar(N),实际也当作char(N),也就是固定长度字符串来存储,因此内存表的每行数据长度相同。
由于内存表的这些特性,每个数据行被删除以后,空出的这个位置都可以被接下来要插入的数据复用
17,自增主键不是连续的
在MyISAM引擎里面,自增值是被写在数据文件上的。而在InnoDB中,自增值是被记录在内存的。MySQL直到8.0版本,才给InnoDB表的自增值加上了持久化的能力,确保重启前后一个表的自增值不变。
MySQL 5.1.22版本开始引入的参数innodb_autoinc_lock_mode,控制了自增值申请时的锁范围。从并发性能的角度考虑,我建议你将其设置为2,同时将binlog_format设置为row
- 其他唯一键冲突是导致自增主键id不连续的第一种原因。
- 同样地,事务回滚也会产生类似的现象,这就是第二种原因。
此文章是学习网课后的笔记,写的比较散乱,供自己复习使用
原文链接:https://time.geekbang.org/column/intro/139?utm_term=zeusFW7QE&utm_source=wechat&utm_medium=geektime&utm_campaign=citiao&utm_content=0608