[版权声明] 本站所有资料由用户提供并上传,若内容存在侵权,请联系邮箱。资料中的图片、字体、音乐等需版权方额外授权,请谨慎使用。网站中党政主题相关内容(国旗、国徽、党徽)仅限个人学习分享使用,禁止广告使用和商用。
更多详情,请转向我的Github仓库:
欢迎来做客,欢迎star~
在本文将讨论数据库原理和MySQL核心知识,MySQL性能优化等,包含 “MySQL基础” 和 “高性能MySQL实践” 两部分。
这里先有个整体的MySQL Server的整体概念,详情转向:
(3)在事务1中,Mary 再次读取自己的工资时,工资变为了2000
在一个事务中前后两次读取的结果并不致,导致了不可重复读。
如果只有在修改事务完全提交之后才可以读取数据,则可以避免该问题。把数据库的事务隔离级别调整到REPEATABLE_READ
T2 读取一个数据,T1 对该数据做了修改。如果 T2 再次读取这个数据,此时读取的结果和第一次读取的结果不同。
事务1再次读取所有工资为 1000的 员工(共读取到了 11 条记录,这就像产生了幻读)
如果在操作事务完成数据处理之前,任何其他事务都不可以添加新数据,则可避免该问题。把数据库的事务隔离级别调整到 SERIALIZABLE_READ
T1 读取某个范围的数据,T2 在这个范围内插入新的数据,T1 再次读取这个范围的数据,此时读取的结果和和第一次读取的结果不同。
所有事务一个接着一个的执行,这样可以避免幻读 (phantom read),对于基于锁来实现并发控制的数据库来说,串行化要求在执行范围查询的时候,需要获取范围锁,如果不是基于锁实现并发控制的数据库,则检查到有违反串行操作的事务时,需回滚该事务。
所有被 Select 获取的数据都不能被修改,这样就可以避免一个事务前后读取不一致的情况。但是没有办法控制幻读,因为这个时候其他事务不能更改所选的数据,但是可以增加数据,因为强恶意事务没有范围锁。
被读取的数据可以被其他事务修改,这样可能导致不可重复读。也就是说,事务读取的时候获取读锁,但是在读完之后立即释放(不需要等事务结束),而写锁则是事务提交之后才释放,释放读锁之后,就可能被其他事务修改数据。该等级也是 SQL Server 默认的隔离等级。
最低的隔离等级,允许其他事务看到没有提交的数据,会导致脏读。
隔离级别脏读不可重复读幻影读未提交读√√√提交读×√√可重复读××√可串行化×××
对于初学者来说我们通常不关注存储引擎,但是 MySQL 提供了多个存储引擎,包括处理事务安全表的引擎和处理非事务安全表的引擎。在 MySQL 中,不需要在整个服务器中使用同一种存储引擎,针对具体的要求,可以对每一个表使用不同的存储引擎。
MySQL 中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术,你能够获得额外的速度或者功能,从而改善你的应用的整体功能。存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。
例如,如果你在研究大量的临时数据,你也许需要使用内存存储引擎。内存存储引擎能够在内存中存储所有的表格数据。又或者,你也许需要一个支持事务处理的数据库(以确保事务处理不成功时数据的回退能力)。
在MySQL中有很多存储引擎,每种存储引擎大相径庭,那么又改如何选择呢?
不同存储引起都有各自的特点,为适应不同的需求,需要选择不同的存储引擎,所以首先考虑这些存储引擎各自的功能和兼容。
MySQL 5.5版本之前的默认存储引擎,在 5.0
以前最大表存储空间最大 4G
,5.0
以后最大 256TB
。
Myisam 存储引擎由 .myd
(数据)和 .myi
(索引文件)组成,.frm
文件存储表结构(所以存储引擎都有)
MySQL 5.5 及之后版本的默认存储引擎
.csv
文件存储表内容
.csm
文件存储表的元数据,如表状态和数据量
a.arz
,a.frm
)
注意: Memory 数据易丢失,所以要求数据可再生
默认是禁止的。如果需要启用,需要在启动时增加Federated参数
总结 强烈建议:对Innodb引擎使用独立表空间(mysql5.6版本以后默认是独立表空间)
系统表转移为独立表的步骤(非常繁琐)
重要一点: 不要混合使用存储引擎 强烈推荐: Innodb
仅作拓展延伸,详情请转向:
INT(11) 中的数字只是规定了交互工具显示字符的个数,对于存储和计算来说是没有意义的。
FLOAT 和 DOUBLE 为浮点类型,DECIMAL 为高精度小数类型。CPU 原生支持浮点运算,但是不支持 DECIMAl 类型的计算,因此 DECIMAL 的计算比浮点类型需要更高的代价。
主要有 CHAR 和 VARCHAR 两种类型,一种是定长的,一种是变长的。
VARCHAR 这种变长类型能够节省空间,因为只需要存储必要的内容。但是在执行 UPDATE 时可能会使行变得比原来长,当超出一个页所能容纳的大小时,就要执行额外的操作。MyISAM 会将行拆成不同的片段存储,而 InnoDB 则需要分裂页来使行放进页内。
VARCHAR 会保留字符串末尾的空格,而 CHAR 会删除。
能够保存从 1001 年到 9999 年的日期和时间,精度为秒,使用 8 字节的存储空间。
默认情况下,MySQL 以一种可排序的、无歧义的格式显示 DATATIME 值,例如“ 22:37:08”,这是 ANSI 标准定义的日期和时间表示方法。
和 UNIX 时间戳相同,保存从 1970 年 1 月 1 日午夜(格林威治时间)以来的秒数,使用 4 个字节,只能表示从 1970 年 到 2038 年。
它和时区有关,也就是说一个时间戳在不同的时区所代表的具体时间是不同的。
默认情况下,如果插入时没有指定 TIMESTAMP 列的值,会将这个值设置为当前时间。
索引能够轻易将查询性能提升几个数量级。
索引是在存储引擎层实现的,而不是在服务器层实现的,所以不同存储引擎具有不同的索引类型和实现。
定义一条数据记录为一个二元组 [key, data],B-Tree 是满足下列条件的数据结构:
查找算法:首先在根节点进行二分查找,如果找到则返回对应节点的 data,否则在相应区间的指针指向的节点递归进行查找。
由于插入删除新的数据记录会破坏 B-Tree 的性质,因此在插入删除时,需要对树进行一个分裂、合并、旋转等操作以保持 B-Tree 性质。
一般在数据库系统或文件系统中使用的 B+Tree 结构都在经典 B+Tree 基础上进行了优化,在叶子节点增加了顺序访问指针,做这个优化的目的是为了提高区间访问的性能。
红黑树等平衡树也可以用来实现索引,但是文件系统及数据库系统普遍采用 B Tree 作为索引结构,主要有以下两个原因:
平衡树检索数据的时间复杂度等于树高 h,而树高大致为 O(h)=O(logdN),其中 d 为每个节点的出度。
红黑树的出度为 2,而 B Tree 的出度一般都非常大。红黑树的树高 h 很明显比 B Tree 大非常多,因此检索的次数也就更多。
B+Tree 相比于 B-Tree 更适合外存索引,因为 B+Tree 内节点去掉了 data 域,因此可以拥有更大的出度,检索效率会更高。
(二)利用计算机预读特性
为了减少磁盘 I/O,磁盘往往不是严格按需读取,而是每次都会预读。这样做的理论依据是计算机科学中著名的局部性原理:当一个数据被用到时,其附近的数据也通常会马上被使用。预读过程中,磁盘进行顺序读取,顺序读取不需要进行磁盘寻道,并且只需要很短的旋转时间,因此速度会非常快。
操作系统一般将内存和磁盘分割成固态大小的块,每一块称为一页,内存与磁盘以页为单位交换数据。数据库系统将索引的一个节点的大小设置为页的大小,使得一次 I/O 就能完全载入一个节点,并且可以利用预读特性,相邻的节点也能够被预先载入。
B+Tree 索引是大多数 MySQL 存储引擎的默认索引类型。
因为不再需要进行全表扫描,只需要对树进行搜索即可,因此查找速度快很多。除了用于查找,还可以用于排序和分组。
可以指定多个列作为索引列,多个索引列共同组成键。
B+Tree 索引适用于全键值、键值范围和键前缀查找,其中键前缀查找只适用于最左前缀查找。
如果不是按照索引列的顺序进行查找,则无法使用索引。
主索引的叶子节点 data 域记录着完整的数据记录,这种索引方式被称为聚簇索引。因为无法把数据行存放在两个不同的地方,所以一个表只能有一个聚簇索引。
辅助索引的叶子节点的 data 域记录着主键的值,因此在使用辅助索引进行查找时,需要先查找到主键值,然后再到主索引中进行查找。
InnoDB 引擎有一个特殊的功能叫 “自适应哈希索引”,当某个索引值被使用的非常频繁时,会在 B+Tree 索引之上再创建一个哈希索引,这样就让 B+Tree 索引具有哈希索引的一些优点,比如快速的哈希查找。
哈希索引能以 O(1) 时间进行查找,但是失去了有序性,它具有以下限制:
MyISAM 存储引擎支持全文索引,用于查找文本中的关键词,而不是直接比较是否相等。查找条件使用 MATCH AGAINST,而不是普通的 WHERE。
全文索引一般使用倒排索引实现,它记录着关键词到其所在文档的映射。
MyISAM 存储引擎支持空间数据索引,可以用于地理数据存储。空间数据索引会从所有维度来索引数据,可以有效地使用任意维度来进行组合查询。
必须使用 GIS 相关的函数来维护数据。
两个或更多个列上的索引被称作联合索引,联合索引又叫复合索引。对于复合索引:Mysql 从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分。
例如索引是key index (a,b,c),可以支持[a]、[a,b]、[a,b,c] 3种组合进行查找,但不支 [b,c] 进行查找。当最左侧字段是常量引用时,索引就十分有效。
其中 table_name 是要增加索引的表名,column_list 指出对哪些列进行索引,多列时各列之间用逗号分隔。索引名 index_name 可选,缺省时,MySQL将根据第一个索引列赋一个名称。另外,ALTER TABLE 允许在单个语句中更改多个表,因此可以在同时创建多个索引。
在创建索引时,可以规定索引能否包含重复值。如果不包含,则索引应该创建为 PRIMARY KEY 或 UNIQUE 索引。对于单列惟一性索引,这保证单列不包含重复的值。对于多列惟一性索引,保证多个值的组合不重复。 PRIMARY KEY 索引和 UNIQUE 索引非常类似。
第3条语句只在删除 PRIMARY KEY 索引时使用,因为一个表只可能有一个 PRIMARY KEY 索引,因此不需要指定索引名。如果没有创建 PRIMARY KEY 索引,但表具有一个或多个 UNIQUE 索引,则 MySQL 将删除第一个 UNIQUE 索引。
如果从表中删除了某列,则索引会受到影响。对于多列组合的索引,如果删除其中的某列,则该列也会从索引中删除。如果删除组成索引的所有列,则整个索引将被删除。
注:更多 Redis 相关内容将在
简单来说,数据的切分就是通过某种特定的条件,将我们存放在同一个数据库中的数据分散存放到多个数据库(主机)中,以达到分散单台设备负载的效果,即分库分表。
数据的切分根据其切分规则的类型,可以分为如下两种切分模式。
垂直切分是将一张表按列切分成多个表,通常是按照列的关系密集程度进行切分,也可以利用垂直切分将经常被使用的列和不经常被使用的列切分到不同的表中。
在数据库的层面使用垂直切分将按数据库中表的密集程度部署到不同的库中,例如将原来的电商数据库垂直切分成商品数据库 payDB、用户数据库 userBD 等。
水平切分又称为 Sharding,它是将同一个表中的记录拆分到多个结构相同的表中。
当一个表的数据不断增多时,Sharding 是必然的选择,它可以将数据分布到集群的不同节点上,从而缓存单个数据库的压力。
使用分布式事务来解决,比如 XA 接口。
可以将原来的 JOIN 查询分解成多个单表查询,然后在用户程序中进行 JOIN。
主要涉及三个线程:binlog 线程、I/O 线程和 SQL 线程。
主服务器用来处理写操作以及实时性要求比较高的读操作,而从服务器用来处理读操作。
读写分离常用代理方式来实现,代理服务器接收应用层传来的读写请求,然后决定转发到哪个服务器。
MySQL 读写分离能提高性能的原因在于:
Explain 用来分析 SELECT 查询语句,开发人员可以通过分析 Explain 结果来优化查询语句。
使用 WHERE 语句进行查询过滤,有时候也需要使用 LIMIT 语句来限制返回的数据。
(三)缓存重复查询的数据
使用缓存可以避免在数据库中进行查询,特别要查询的数据经常被重复查询,缓存可以带来的查询性能提升将会是非常明显的。
最有效的方式是使用索引来覆盖查询。
一个大查询如果一次性执行的话,可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。
将一个大连接查询(JOIN)分解成对每一个表进行一次单表查询,然后将结果在应用程序中进行关联,这样做的好处有:
抢订单环节一般会带来2个问题:
比较火热的秒杀在线人数都是10w起的,如此之高的在线人数对于网站架构从前到后都是一种考验。
任何商品都会有数量上限,如何避免成功下订单买到商品的人数不超过商品数量的上限,这是每个抢购活动都要面临的难题。
将存库MySQL前移到Redis中,所有的写操作放到内存中,由于Redis中不存在锁故不会出现互相等待,并且由于Redis的写性能和读性能都远高于MySQL,这就解决了高并发下的性能问题。然后通过队列等异步手段,将变化的数据异步写入到DB中。
优点:解决性能问题
缺点:没有解决超卖问题,同时由于异步写入DB,存在某一时刻DB和Redis中数据不一致的风险。
引入队列,然后将所有写DB操作在单队列中排队,完全串行处理。当达到库存阀值的时候就不在消费队列,并关闭购买功能。这就解决了超卖问题。
优点:解决超卖问题,略微提升性能。
缺点:性能受限于队列处理机处理性能和DB的写入性能中最短的那个,另外多商品同时抢购的时候需要准备多条队列。
**将提交操作变成两段式,先申请后确认。然后利用Redis的原子自增操作(相比较MySQL的自增来说没有空洞),同时利用Redis的事务特性来发号,保证拿到小于等于库存阀值的号的人都可以成功提交订单。**然后数据异步更新到DB中。
优点:解决超卖问题,库存读写都在内存中,故同时解决性能问题。
缺点:由于异步写入DB,可能存在数据不一致。另可能存在少买,也就是如果拿到号的人不真正下订单,可能库存减为0,但是订单数并没有达到库存阀值。
数据库主库和从库不一致,常见有这么几种优化方案:
(1)业务可以接受,系统不优化
(2)强制读主,高可用主库,用缓存提高读性能
(3)在cache里记录哪些记录发生过写请求,来路由读主还是读从
版权声明:文章内容来源于网络,版权归原作者所有,如有侵权请点击这里与我们联系,我们将及时删除。