mysql知识摘要

MySQL即可以嵌入到应用程序中,也可以支持数据仓库、内容索引和部署软件、高可用的冗余系统、在线事物

处理系统(OLTP)等各种应用类型。
psearch

 

MySQL由以下几部分组成:
连接池组件,管理服务和工具组件,SQL接口组件,查询分析器组件,优化器组件,
缓冲组件,插件式存储引擎,物理文件。
MySQL会解析查询,并创建内部数据结构(解析树),然后会对其进行各种优化,包括重写查询、决定表的读
取顺序,以及选择合适的索引等。用户可以通过特殊的关键字提示(hint)优化器,影响它的决策过程。也可
以请求优化器解释(explain)优化过程的各个因素,使用户可以知道服务器是如何进行优化决策的,并提供
一个参考基准,便于用户重构查询和schema、修改相关配置,使应用尽可能高效运行。

 

MySQL存储引擎架构

MySQL最重要、最与众不同的特性是它的存储引擎架构,这种架构的设计将查询处理(Query Processing)
及其他系统任务(Server Task)和数据的存储/提取相分离。这种处理和存储分离的设计可以在使用时根据
性能、特性,以及其他需求选择数据存储的方式。

各引擎特征对比图:

pluggable-storage-choosing

 

在处理并发读或者写时,可以通过实现一个由两种类型的锁组成的锁系统来解决。这两种类型的锁通常被称为共享锁(shared lock)和排他锁(exclusive lock),也叫读锁(read lock)和写锁(write lock)。

  • 读锁是共享的,或者说是相互不阻塞的。多个客户在同一时刻可以同时读取同一个资源,而互不干扰。
  • 写锁是排他的,也就是说一个写锁会阻塞其他的的写锁和读锁。

这时出于安全策略的考虑,只有这样,才能确保在给定的时间里,只有一个用户能执行写入,并防止其他用户读取正在写入的统一资源。

锁粒度

一种提高共享资源并发性的方式就是让锁定对象更有选择性。尽量只锁定需要修改的部分数据,而不是所有的资源。更理想的方式是,只对会修改的数据片进行精确的锁定。

问题是加锁也需要消耗资源。锁的各种操作,包括获得锁、检查锁是否已经解除、释放锁等,都会增加系统的开销。所谓的锁策略,就是在锁的开销和数据的安全性之间寻求平衡。

每种MySQL存储引擎都可以实现自己的锁策略和锁粒度。

两种重要的锁策略:

  • 表锁(table lock)是MySQL中最基本的锁策略,并且是开销最小的策略。它会锁定整张表。
  • 行级锁(row lock)可以最大程度地支持并发处理(同时也带来了最大的锁开销)。

 

事务

事务就是一组原子性的SQL查询,或者说一个独立的工作单元。如果数据库引擎能够成功地对数据库应用
该组查询的全部语句,那么就执行该组查询。如果其中有任何一条语句因为崩溃或其他原因无法执行,那么
所有的语句都不会执行。
也就是说,事务内的语句,要么全部执行成功,要么全部执行失败。
银行转账时解释事务必要性的经典例子。

示例中从用户的checking账户转款到自己的savings账户。如果这几条语句不能保证事务操作,
则可能因系统奔溃或其他原因引发账务差错。

 

ACID

原子性(atomicity)、一致性(consistency)、隔离性(isolation)、持久性(durability)

就像锁粒度的升级会增加系统开销一样,这种事务处理过程中额外的安全性,也会需要数据库系统做更多的额外工作。一个实现了ACID的数据库,相比没有实现ACID的数据库,通常会需要更强的CPU处理能力、更大的内存和更多的磁盘空间。死锁 两个或者多个事务在统一资源上相互占用,并请求锁定对方占用的资源,从而导致恶性循环的现象。

 

多版本并发控制MVCC

MySQL的大多数存储引擎实现的都不是简单的行级锁。基于提示并发性能的考虑,它们一般都同时实现了多版本并发控制(MVCC)。

可以认为MVCC是行级锁的一个变种,但是它在很大情况下避免了加锁操作,因此开销更低。虽然实现机制有所不同,但大都实现了非阻塞的读操作,写操作也只锁定必要的行。

 

基准测试工具:

  • 集成式测试工具:ab(Apache bench)
  • 单组件式测试工具:mysqlslap; MySQL Benchmark Suite; Super Smack; sysbench…

 

选择优化的数据类型:

MySQL支持的数据类型非常多,选择正确的数据类型对于获得高性能至关重要。不管存储哪种类型的数据,下面几个简单的原则都有助于做出更好的选择:

  • 更小的通常更好
  • 简单就好
  • 尽量避免NULL

 

索引

索引时存储引擎用于快速找到记录的一种数据结构。

索引对于良好的性能非常关键。尤其是当表中的数据量越来越大时,索引对性能的影响愈发重要。在MySQL中,索引是在存储引擎层而不是服务器层实现的。所有,并没有统一的索引标准:不同存储引擎的索引工作方式并不一样,也不是所有的存储引擎都支持所有类型的索引。

 

B-Tree索引

当人们谈论索引的时候,如果没有特别指明类型,那多半说的是B-Tree索引,它使用B-Tree数据结构来存储数据。B-Tree对索引列是顺序组织存储的,所以很适合查找范围数据。

可以使用B-Tree索引的查询类型:

  • 全值匹配:和索引中的所有列进行匹配。比如查找姓名为Allen,,1990.01.01出生于华盛顿的人。
  • 匹配最左前缀:只使用索引的第一列。比如查找所有性为Allen的人
  • 匹配列前缀:只匹配某一列的值的开头部分。比如查找所有以J开头的姓的人。
  • 匹配范围值:比如查找姓在Allen和Billy之间的人。
  • 精确匹配某一列并范围匹配另外一列:
  • 只访问索引的查询:

 

哈希索引(hash index):

基于哈希表实现,只有精确匹配索引所有列的查询才有效。对于每一行数据,存储引擎都会对所有的索引列计算一个哈希码,哈希码是一个较小的值,并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中,同时在哈希表中保存指向每个数据行的指针。

在MySQL中,只有Memory引擎显示支持哈希索引。InnoDB引擎有一个特殊的功能叫做“自适应哈希索引(
adaptive hash index)”。当InnoDB注意到某些索引值被使用得非常频繁时,它会在内存中基于B-Tree
索引之上再创建一个哈希索引。

 

空间数据索引(R-Tree):

MyISAM表支持空间索引,可以用作地理数据存储

 

全文索引

全文索引时一种特殊类型的索引,它查找的事文本中的关键词,而不是直接比较索引中的值。全文搜索和其他
几类索引的匹配方式完全不一样。它有许多需要注意的细节,如停用词、词干和复数、布尔搜索等。全文索引
更类似于搜索引擎做的事情,而不是简单的WHERE条件匹配。

 

高性能的索引策略(todo)

  • 前缀索引
  • 多列索引
  • 聚簇索引
  • 覆盖索引
  • 压缩(前缀压缩)索引:
  • 冗余和重复索引

 

 

References:

  • 《高性能mysql》
  • 《mysql技术内幕InnoDB存储引擎》
  • wikipedia

暂无评论

发表评论

电子邮件地址不会被公开。 必填项已用*标注