mysql笔记01 MySQL架构与历史、Schema与数据类型优化

时间:2022-01-21 05:23:25

MySQL架构与历史

1. MySQL架构推荐参考:http://www.cnblogs.com/baochuan/archive/2012/03/15/2397536.html

2. MySQL会解析查询,并创建内部数据结构(解析树),然后对其进行各种优化,包括重写查询、决定表的读取顺序,以及选择合适的索引等。用户可以通过特殊的关键字提示(hint)优化器,影响它的决策过程。

也可以请求优化器解释(explain)优化过程的各个因素,使用户可以知道服务器如何进行优化决策的,并提供一个参考基准,便于用户重构查询和schema、修改相关配置,使应用尽可能高效运行。

3. 对于SELECT语句,在解析查询之前,服务器先检查查询缓存(Query Cache),如果能够在其中找到对应的查询,服务器就不会再执行查询解析、优化和执行的整个过程,而是直接返回查询缓存中的结果集。

4. 读锁(共享锁)是共享的,或者说是互相不阻塞的。多个客户在同一时刻可以同时读取同一个资源,而互不干扰。

写锁(排他锁)则是排他的,也就是说一个写锁会阻塞其他的写锁和读锁,这是出于安全策略的考虑,只有这样才能确保在给定的时间里,只有一个用户能执行写入,并防止其他用户读取正在写入的同一资源。

5. 锁粒度:锁的各种操作,包括获得锁、检查锁是否已经解除、释放锁等,都会增加系统的开销。如果系统花费大量的时间来管理锁,而不是存储数据,那么系统性能可能会受到影响。

锁策略:就是在锁的开销和数据安全性之间寻求平衡。

表锁(table lock):表锁是MySQL中最基本的锁策略,并且是开销最小的锁策略。写锁比读锁有更高的优先级,因此一个写锁请求可能会被插入到读锁队列的前面(写锁可以插入到锁队列中读锁的前面,反之

读锁则不能插入到写锁的前面)。

行级锁(row lock):行级锁可以最大程度地支持并发处理(同时也会带来最大化的锁开销)。

6. 事务:事务就是一组原子性的SQL查询,或者说一个独立的工作单元。事务内的语句,要么全部执行成功,要么全部执行失败。

1). ACID:

原子性(atomicity):一个事务必须视为一个不可分割的最小工作单元,整个事务中所有操作要么全部提交成功,要么全部失败回滚,对于一个事务来说,不可能只执行其中的一部分操作,这就是事务的原子性。

一致性(consistency): 数据库总是从一个一致性状态转换到另一个一致性状态。

隔离性(isolation):通常来说,一个事务所做的修改在最终提交以前,对其他事务总是不可见的。

持久性(durability):一旦事务提交,则其所做的修改就会永久保存到数据库中。此时,即使系统崩溃,修改的数据也不会丢失。

2). 事务处理过程中额外的安全性,也会需要数据库系统做更多的额外工作,影响运行效率。

3). 隔离级别:

READ UNCOMMITTED(未提交读):事务可以读取未提交的数据,这也被称为脏读(Dirty Read)

READ COMMITTED(提交读):大多数数据库的默认隔离级别,但MySQL不是。这个级别有时候也叫不可重复读,因为两次执行同样的查询,可能会得到不一样的结果。

是指在一个事务内,多次读同一数据。在这个事务还没有结束时,另外一个事务也访问该同一数据。那么,在第一个事务中的两次读数据之间,由于第二个事务的修改,

那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的,因此称为是不可重复读。

REPEATABLE READ(可重复读):解决了脏读问题。该级别保证了同一个事务中多次读取同样记录的结果是一致的。MySQL的默认隔离级别。

SERIALIZABLE(可串行化):最高隔离级别,通过强制事务串行执行,避免了前面说的幻读问题。只有在数据非常中的时候才使用。

4). 死锁:指两个或多个事务在同一资源上相互占用,并请求锁定对方占用的资源,从而导致恶性循环的现象。当多个事务试图以不同的顺序锁定资源时,就可能会产生死锁。

InnoDB目前处理死锁的方法是,将持有最少行级排他锁的事务进行回滚。

5). 使用事务日志,存储引擎在修改表的数据时,只需要修改其内存拷贝,再把修改行为记录到持久在硬盘的事务日志中,而不用每次都将修改的数据本身持久到硬盘。事务采用的是追加的方式。事务日志持久化

         以后,内存中被修改的数据在后台可以慢慢地刷回到磁盘。如果数据的日志已经记录到事务日志并持久化,但数据本身还没有回写磁盘,此时系统崩溃,存储引擎在重启时能够自动恢复这部分修改的数据。

 6). MySQL中的事务:

a. 自动提交(AUTOMCOMMIT):MySQL默认采用自动提交的模式。也就是说如果不是显示地开始一个事务,则每个查询都会被当做一个事务执行提交操作。可以通过设置AUTOCOMMIT变量来启用或禁用

自动提交模式。另外,有一些命令执行之前会强制执行COMMIT提交当前的活动事务。

b. 隐式和显示锁定:事务执行过程中,随时都可以执行锁定,所只有在执行COMMIT或者ROLLBACK的时候才会释放,并且所有的锁是在同一时刻被释放。这些都是隐式锁。

7. MySQL的大多数事务型存储引擎实现的都不是简单的行级锁。基于提升并发性能的考虑,它们一般都同时实现了多个版本并发控制(MVCC)。可以认为MVCC是行级锁的一个变种,但是它在很多情况下避免了

    加锁操作,因此开销更低。MVCC的实现,通过保存数据在某个时间点的快照来实现的。

8. 在文件系统中,MySQL将每个数据库(也可以称为schema)保存为数据目录下的一个子目录。创建表时,MySQL会在数据库子目录下创建一个和表同名的.frm文件保存表的定义。例如:MyTable表,对应

MyTable.frm文件。

9. MySQL在5.5以后才彻底使用InnoDB plugin替代旧版本的InnoDB。作为事务型存储引擎,InnoDB通过一些机制和工具支持真正的热备份,Oracle提供的MySQL Enterpries Backup 、Percona提供开源的

XtraBackup都可以做到这一点。MySQL的其他存储引擎不支持热备份,要获取一致性视图需要停止对所有表的写入,而在读写混合场景中,停止写入可能也意味着停止读取。

Schema与数据类型优化

1. 选择优化的数据类型

1). 更小的通常更好:更小的数据类型通常更快,因为他们占用更少的磁盘、内存和CPU缓存,并且处理需要的CPU周期也更少。

2). 简单就好:简单的数据类型的操作通常需要更少的CPU周期。例如:整型比字符串操作的代价更低,因为字符集和校对规则(排序规则)是字符串比较比整型比较更复杂。这里有两个例子:

一个是应该使用MySQL内建的类型而不是字符串来存储日期和时间,另外一个是应该使用整型存储IP地址。

3). 避免使用null:通常情况下最好指定列为not null,除非真的需要存储null。因为null列使得索引、索引统计和值比较都更复杂。可为null的列会使用更多的存储空间,在MySQL中也需要特殊处理。

2. MySQL兼容性支持很多别名,例如INTEGER、BOOL等。他们都是别名,这些别名可能令人不解,但不会影响性能。如果建表时采用数据烈性的别名,然后用show create table检查,会发现MySQL

    报告的是基本类型,而不是别名。

3. 整数类型:TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT,分别使用8,16,24,32,64位存储空间。它们的存储值的范围从-2的n-1次幂,到2的n-1次幂减一。

1). 整数类型有可选的UNSIGNED属性,表示不允许负值,这大致可以使正数的上限提高一倍。

2). 有符号和无符号类型使用相同的存储空间,并且有相同的性能,因此可以根据实际情况选择合适的类型。

3). MySQL可以为正数类型指定宽度,例如INT(11),但大多数应用这是没有意义的。对于存储和计算来说,INT(1)和INT(20)是相同的。

4. 实数类型:实数是带有小数部分的数字。然而,它们不只是为了存储小数部分;也可以使用DECIMAL存储比BIGINT还大的整数。

1). FLOAT和DOUBLE类型支持使用标准的浮点运算进行近视计算DECIMAL类型用于存储精确的小数。

2). 浮点类型在存储同样类型的范围的值时,通常比DECIMAL使用更少的空间。FLOAT使用4个字节存储.DOUBLE占用8个字节,相比FLOAT有更高的精度和更大的范围。

3). 因为需要额外的空间和计算开销,所以应该尽量只在对小数进行精确计算时才使用DECIMAL。在数据量比较大的时候,可以考虑使用BIGINT代替DECIMAL,将对应的值扩大N倍。

5. 字符串类型:

1). VARCHAR:它比定长类型更节省空间,因为它仅使用必要的空间。VARCHAR节省了空间,所以对性能也有帮助。但是由于行是变长的,在UPDATE时可能使行变得比原来更长,这就导致需要做额外的工作。

 下面的情况使用VARCHAR是合适的:字符串最大长度比平均长度大很多;列的更新少,所以碎片不是问题;使用了像UTF-8这样复杂的字符集,每个字符使用不同的字节数。

在5.0或更高的版本中,MySQL在存储和检索时会保留末尾空格。InnoDB则更灵活,它可以把长的VARCHAR存储为BLOB。

2). CHAR: 定长,当存储CHAR值时,MySQL会删除所有的末尾空格。定长的CHAR类型不容易产生碎片,对于非常短的列,CHAR比VARCHAR在存储空间上也更有效率,VACHAR还有一个记录长度的额外字节。

3). 记住字符串的长度定义不是字节数,是字符数。多字节字符集会需要更多的空间存储单个字符。

4). 与CHAR和VARCHAR类似的类型还有BINARY和VARBINARY,它们存储的是二进制字符串。二进制字符串中存储的是字节码而不是字符。

二进制比较的优势并不仅仅体现在大小写敏感上。MySQL比较BINARY字符串是,每次按一个字节,并且根据该字节的数值进行比较。因此,二进制比字符比较简单的多,所以也就更快。

5). BLOB和TEXT类型:BLOB和TEXT都是为了存储很大的数据而设计的字符串数据类型,分别采用二进制和字符方式存储。当BLOB和TEXT值太大时,InnoDB会使用专门的"外部"存储区域来进行存储。原表字段

          存储指针指向外部存储区域。

6. 日期和时间类型:MySQL能存储的最小时间粒度为秒。

1). DATETIME : 这个类型能保存大范围的值,从1001年到9999年,精度为秒。它把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中。默认显示格式为"2008-02-16 22:37:08"

2). TIMESTAMP:保存从1970年1月1日午夜依赖的秒数,它和UNIX时间戳相同。只能表示从1970年到2038年。TIMESTAMP因为空间占用小,所以效率更高。

3). 可以使用BIGINT类型存储微秒级别的时间戳。

7. 位运算:BIT , SET

8. 选择标识符(identifier,主键) 

1). 当选择标识列的类型时,不仅仅需要考虑存储类型,还需要考虑MySQL这种类型怎么执行计算和比较

2). 一旦选择了一种类型,要确保在所有关联表中都使用同样的类型。会用不同数据类型可能导致性能问题,即使没有性能影响,在比较操作时隐式类型转换也可能导致很难发现错误。

3). 在可以满足值的范围要求,并且预留未来增长空间的前提下,应该选择最小的数据类型。

4). 整数类型通常是标识列最好的选择,因为它们很快并且可以使用AUTO_INCREMENT

5). 如果可能,应该避免使用字符串类型作为标识列,因为它们很消耗空间,并且通常比数字类型慢。

6). 对于完全"随机"的字符串也需要多加注意。例如:MD5(),SHAI()或者UUID()产生的字符串。这些函数生成的新值也任意分布在很大空间内,这会导致INSERT和一些SELECT语句很缓慢

7).如果存储UUID值,则应该移除"-"符号,或者更好的做法是,用UNHEX()函数转换UUID值为16字节的数字,并存储在一个BINARY(16)的列中。检索时再转换回来。

9. 当心利用框架自动生成的schema(表)

10. 人们通常使用VARCHAR(15)来存储IP地址。然而,它们实际是32位无符号整数,不是字符串。用小数点将字段分割成四段是为了阅读方便。所以应该用无符号整数存储IP地址。MySQL提供INET_ATON()

和INET_NTOA()函数在这两种表示方法之间转换。

11. MySQL schema 设计中的陷阱:

1). 太多的列

2). 太多的关联

3). 全能的枚举

4). 变相的枚举

5). 非此发明的NULL:不要因为不适用NULL值,而走极端,根据实际情况也可以使用NULL

12. 范式的优点和缺点

      优点:

      1). 范式化的更新操作通常比反范式化要快

      2). 修改更少的数据

      3). 范式化的表通常表更小,可以更好地放在内存中,所以执行操作会更快

      缺点:通常需要关联查询,不仅代价昂贵,也可能使一些索引策略无效

13. 反范式的优点和缺点:

      优点:很好的避免关联,更有效的使用索引策略

      缺点:范式的优点,就是反范式的缺点

14. 缓存表和汇总表:

     1). 缓存表:存储那些可以比较容易的从schema其他表获取(但每次获取速度缓慢)数据的表

     2). 汇总表:保存的是使用GROUP BY语句聚合数据的表。实时计算统计值是很昂贵的操作。

     4). 在使用缓存表和汇总表时,必须决定是实时维护数据还是定期重建。哪个更好依赖于应用程序,但是定期重建并不只是节省资源,可以保持表不会有很多碎片,以及完全顺序组织的索引。

15. 影子表:指的是在一张真实表"背后" 创建的表。当完成了建表操作后,可以通过一个原子的重命名操作切换影子表和原表。原表尽量保留备份,防止新表出问题。

16. 物化视图:物化视图实际上是预先计算并且存储在磁盘上的表,可以通过各种各样的策略刷新和更新。MySQL并不原声支持物化视图,可以使用开源工具Flexviews。

对比传统的维护汇总表和缓存表的方法,Flexviews通过提起对源表的更改,可以增量地重新计算物化视图的内容。

 17. 计数器表:创建一张独立的表存储计数器通常是一个好主意。使用计数器表的一些技巧

       1). 为了防止互斥锁影响效率,可以添加多条记录,随机更新一条记录。统计总数时,将所有数据相加。

       2). 对于需要根据时间更新计数器,也可以用上述方法,不过多加一步操作。每天定时将前一天的总数合计起来,插入到计数器表,删除那些零散的统计记录。

18. 加快ALTER TABLE操作的速度:MySQL的ALTER TABLE操作的性能对大表来说是一个问题。MySQL执行大部分修改表结构的方法是用新的结构创建一个空表,从旧表中查出所有数据插入新表,然后删除旧表。

      可以通过ALTER COLUMN操作来改变列的默认值。这个语句会直接修改.frm文件而不涉及表数据。所以,这个操作非常快。

1. 更小的通常更好:更小的数据类型通常更快,因为他们占用更少的磁盘、内存和CPU缓存,并且处理需要的CPU周期也更少。

2. 简单就好:简单的数据类型的操作通常需要更少的CPU周期。例如:整型比字符串操作的代价更低,因为字符集和校对规则(排序规则)是字符串比较比整型比较更复杂。这里有两个例子:

一个是应该使用MySQL内建的类型而不是字符串来存储日期和时间,另外一个是应该使用整型存储IP地址。

3. 避免使用null:通常情况下最好指定列为not null,除非真的需要存储null。因为null列使得索引、索引统计和值比较都更复杂。可为null的列会使用更多的存储空间,在MySQL中也需要特殊处理。

4. 因为需要额外的空间和计算开销,所以应该尽量只在对小数进行精确计算时才使用DECIMAL。在数据量比较大的时候,可以考虑使用BIGINT代替DECIMAL,将对应的值扩大N倍。

5. 二进制比较的优势并不仅仅体现在大小写敏感上。MySQL比较BINARY字符串是,每次按一个字节,并且根据该字节的数值进行比较。因此,二进制比字符比较简单的多,所以也就更快。

6. 可以使用BIGINT类型存储微秒级别的时间戳。

7. 一旦选择了一种类型,要确保在所有关联表中都使用同样的类型。会用不同数据类型可能导致性能问题,即使没有性能影响,在比较操作时隐式类型转换也可能导致很难发现错误。

8. 如果可能,应该避免使用字符串类型作为标识列,因为它们很消耗空间,并且通常比数字类型慢。

9. 范式(修改多,关联查询少)和反范式(查询多,修改少)的选择

10. 缓存表和汇总表