说明:本篇主要是概念性的东西,主要是课程内容。
一、数据库常见概念
1、锁
(1)表级锁:表锁是开销最小的锁策略,会锁定整张被访问到的表。写之前要获得写锁,会阻塞其它所有的读写操作;读锁属于共享锁,读互相之间不阻塞;写锁的优先级高于读锁,也就是说在排队序列中,写的操作会被插入到读之前。
(2)行级锁:行锁可以最大程度支持并发处理,但同时增大了锁开销。行级锁只在存储层实现。
例如:InnoDB支持行级锁,更安全,myisam只支持表级锁。
2、事务
事务是数据库中的原子操作,不管要操作的数据在一个或多个数据库中。事务中所有语句的结果要么全部提交(操作正常),要么全部回滚(操作失败,不改变原来状态)。
Atomicity:原子性。一个事务被视为一个不可分割的最小单元。
Consistency:一致性。数据库总是从一个已知悉状态转换到另一个一致性状态,例如执行过程崩溃,数据库的状态并不会发生变化。
Isolation:隔离性。一个事务所做的修改在最终提交前,对其它事务是不可见的。
Durability:持久性。一旦事务提交,则其所有的修改就会永久保存到数据库,此时即使系统崩溃,数据也不会丢失。
3、死锁
死锁是指的多个事务在同一资源上相互占用,并请求锁定对方占用的资源,例如以下两个事务:
如果正好第一行被执行完,并导致行被锁,那么彼此第二行都无法运行。InnoDB有很强的机制来检测,但我们必须意识到这样的问题是可能出现的。
4、AUTOCOMMIT
MySQL默认采用自动提交,也就是默认把每个没有显示声明为事务的查询,当成一个一个独立的事务执行提交。
show variables like ‘AUTOCOMMIT’
如果设置为AUTOCOMMIT = 0,那么所有查询都在同一个事务中,必须使用commit提交或者rollback回滚。
二、MySQL的数据存储结构
1、InnoDB存储框架
(1)一张表存储在一个或多个文件里
(2)表包含多个segment,索引、数据、回滚记录等都是独立的segment
(3)每个segment包含多个extent
(4)extent包含64个page
(5)每个page包含若干row
(6)每个row包含了数据域
2、InnoDB
(1)主键索引既存储索引值,又在叶子中存储行的数据
(2)如果没有主键,则会unique key 做主键
(3)如果没有unique,则系统生成一个内部的rowid做主键
(4)次索引需要同时存主键ID
(5)像InnoDB中,主键的索引结果中,既存储了主键值,又存储了行数据,这种结构称为“聚簇索引”
(6)对主键查询有极高的性能,但是二级索引必须包含主键列,因此如果主键列很大,其它的索引都会很大
(7)支持事务,行级锁,颗粒度小,并发好,但是加锁过程更复杂
(8)崩溃后可以安全恢复
3、myisam
(1)不支持事务,不能安全恢复
(2)表级锁,读的时候所有读到的表加共享锁,写的时候加排他锁
(3)加锁效率高,并发支持效率低
(4)支持前文索引(基于分词),可以支持复杂字段
(5)可以延迟更新索引键,如何设置了delay_key_write,每次修改完成时不会立即将修改的索引数据写入磁盘,而是会写入缓冲区,因此能提高性能,但是崩溃的时候索引会被损坏。
(6)设计简单,数据紧密格式存储,某些场景下性能很好
4、选择合适引擎
(1)默认应选择InnoDB
(2)一般不应使用混合引擎存储
(3)如果需要事务,InnoDB是最稳定的;如果主要是select或者insert,那么myisam也许更合适,例如日志类型的应用
(4)需要热备份的话,应该用InnoDB
(5)重要数据应该InnoDB存储(能恢复),myisam崩溃后损坏概率比InnoDB大得多
(6)大多数情况下,InnoDB远远比myisam要快
(7)只读的表,优先考虑myisam,会快很多
5、char / varchar
(1)char会分配固定长度的空间,使得结构更加固定,尤其是在update的时候,没有额外的开销,对于很长的字符串,用char会造成空间的浪费
(2)varchar可以分配变长的空间,因为空间更加紧凑,根据实际需要来,但是当一个页满的情况下,update更新会造成很大的负担,myisam会把行拆成不同的片段拆成,而InnoDB则会分页
(3)varchar(5)比varchar(200):如果都存储hello这个字符串,空间开销一样,但是更长的列会消耗更多的内存,尽量按照实际需求来设计
6、Schema设计要点
(1)避免太多的列:存储引擎API需要在服务器层与存储引擎层通过行缓冲格式拷贝数据,然后在服务器层讲缓冲内容解码成各个列。从行缓冲中奖编码过的列转换成行数据结构的代价是非常高的,它依赖列的数量
(2)太多的关联:MySQL限制了最多关联为61张表
(3)避免使用null:对于索引列使用null,会带来存储以及大量优化问题
(4)汇总表:把一些历史汇总数据,离线或定时汇总,最后总的结果是汇总结果加上当前的结果。例如个人的消费总额,可以把过去每个月的汇总,然后加上本月从1号到现在的sum,这样的计算量会极大的减小。代价是写的速度会变慢,每个人的数据都需要定期被更新,但是读的性能得到了极大的提升。在数据库设计的时候,类似的冗余数据、统计结果的缓存往往是必要的
7、B+ Tree
8、多列索引
多列索引可以看成是把多个列拼接在一起后,再排序的数组。
多列索引可以看成是把多个列拼接在一起后,再排序的数组,所以对于and过滤是有用的,而对于or过滤是无用的;
多列索引的顺序对最终的索引有影响,索引首先是按照最左列进行排序(上面例子从左到右是lastname、firstname)。
三、MySQL的查询过程
1、查询流程
》》》客户端发送一条查询给服务器
》》》服务器检查缓存(第一次查询后都会缓存,除非数据更新),如果命中则直接返回
》》》服务器进行SQL解析,预处理
》》》交给优化器生成执行计划
》》》根据优化器生成的执行计划,调用存储引擎API执行查询
》》》结果返回
2、通信协议
(1)半双工的方式通信,意味着任意时刻,要么服务端发送数据,要么服务端接收请求
(2)服务端必须接收完整请求,客户端也必须接收完整结构
(3)很多时候,客户端驱动会先读完数据,缓存到自己的缓存,然后应用层代码从本地驱动缓存读取,这样可能会导致性能降低,可以使用 unbuffered_read 来直接从服务端读取,这样能减少本地驱动层的内存缓存使用,针对特别大的数据结果集会有用
3、查询缓存
(1)缓存是通过一个大小敏感的hash查找实现的,必须完整匹配
(2)如果命中,仍然要检查权限,权限本身也是缓存的
(3)如果都通过,会直接返回结果
4、优化处理
(1)对关键字做SQL解析,生成一棵“解析树”
(2)优化器会尝试多种查询执行方式,来预测执行计划的成本,因此语句要尽量帮助正确预测,否则可能会使用最糟糕的查询方案来执行,有很多的因素,比如并发、统计信息错误、索引预判错误等,导致优化预判不准确
(3)优化器是非常复杂的组件,一般会对关联表的顺序、排序方法、min()、max()、count()等表达式做优化,比如myisam维护了一个变量来存放表的行数,因此count(*)就能直接返回,而不用一个一个去累加
5、错误的优化
当我们要查询多条数据时,会用到 in 操作,然而上面这句并不会先执行划线部分的子查询,而是先执行主查询,然后每次调用子查询。
6、执行及返回
(1)执行阶段只是简单根据执行计划给出的指令逐步执行
(2)如果查询结果可以被缓存,那么在执行完成后结果会放到查询缓存(更新后会消失,直到重新得到缓存)
(3)执行过程中,临时数据集会放到临时结果表,例如关联查询得到的结果,会缓存到一个文件里,然后等到其它关联结果出来后,开始进一步合并、过滤
(4)结果返回给客户端是一个增量的、逐步返回的过程,比如select被命中的结果,如果不需要排序,那么从命中的第一条结果就开始返回给客户端
四、深翻页及优化
1、深翻页查询过程
数据库查询,会优先通过where条件过滤,如果没有设置limit参数,会全部扫描全部数据;一般情况,如果不是GROUP by、sum、count这一类的聚合操作,当limit限制达到后就立即返回不再查询,如果没有limit限制,查询结果会被缓存下来,然后再写入一个缓存文件,然后会对这样的缓存文件再进行归并。
2、深翻页优化
(1)在查询的时候尽量避免这样的深翻页,一般人类的行为不会需要进行深翻页
(2)如果不得不做深翻页,比如skip前800个结果,尽量考虑增加冗余列,例如用冗余列来标记当前结果的ID好,然后通过where来帮助过滤,或者用时间来做偏移量的分割等,总之,使用参数来帮助过滤