高性能MySQL-第1章 MySQL 架构与历史- 高飞网

第1章 MySQL 架构与历史

2016-01-22 20:58:45.0

1. MYSQL架构

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

1.1 mysql逻辑构

第一层:连接处理、授权认证、安全等

每个客户端连接服务器会使用服务器的一个线程,服务器会使用缓存线程或线程池。当连接以后会对客户端进行认证,认证通过用户名、密码和主机信息。认证以后会判断用户有没有相关的权限操作。

第二层:查询、分析、优化、缓存和所有内置函数,所有跨存储引擎的功能都在这一层实现

mysql会解析查询,并创建内部数据结构(查询树),然后对其进行各种优化,包括重写查询、决定表的读取顺序,以及选择合适的索引。用户可以使用特殊的关键字提示(hint)优化器,也可以请求优化器解释(explain)优化过程的各个因素。

第三层:存储引擎。负责mysql数据的存储和提取。

1.2 并发控制


任何时候,只要有多个查询同时修改数据,都会有并发问题。这里讨论的是mysql在两个层面的并发控制:服务器层和存储引擎层。
读写锁:
解决这类经典问题的方法就是并发控制。在处理并发读或写时,通过实现一个由两种类型的锁组成的锁系统来解决问题。分别为共享锁(shared lock)和排他锁(exclusive lock),或叫读锁(read lock)和写锁(write lock)。
锁粒度(表锁、行级锁):
一种提高共享资源并发性的方式就是让锁定对象更有选择性,更精准。尽量只锁定修改的那部分资源。锁定越少,则系统并发性越高。
而加锁也消耗资源,锁的获得、检查锁、释放锁等管理操作也有系统开销,如果这部分开销过大就会影响数据的存储和提取。
锁策略,就是在锁的开销和数据安全性之间寻求平衡。
1)表锁:表锁策略是mysql中开销最小的基本锁策略。
2)行级锁:行级锁可以最大程度地支持并发处理(同时也带来了大量的锁开销)。InnoDB和XtraDB实现了行级锁(只在存储引擎层而不是服务层)。

1.3 事务

    事务就是一个原子性的SQL查询,或者说一个独立的工作单元。一个事务中的SQL,要么全部成功,要么全部失败。事务的ACID概念如下。
原子性(atomicity)
一致性(consistency)
数据库总是从一个一致性状态转换到另一个一致性状态。
隔离性(isolation)
通常来说一个事务在提交以前对其他事务操作是不可见的。
持久性(durability)
一旦事务提交,其所做的操作就永久保存在数据库中。

1.3.1 隔离级别
READ UNCOMMITTED(未提交读)
在该级别,即便事务未提交,对其他事务也是可见的,即可能会出现脏读,一般不会使用。
READ COMMITTED(提交读)
多数数据库系统默认级别都是提交读(mysql不是)。该级别下只有提交了以后,才能被其他事务可见。也叫不可重复读,因为两次相同的查询可能出现不同的结果。
REPEATABLE READ(可重复读)
该级别解决了脏读的问题,保证了在同一个事务中多次读取同样记录结果是一致的。
SERIALIZBLE(可串行化)
最高级别。强制事务串行执行。避免了幻读问题(当某个事务在读取某个范围内的记录时,另一个事务在其中添加了一条记录,当之前的事务再次读取该范围的数据时会产生幻行)。
该级别在读取每一行时都加锁,只有在非常需要保证数据安全性而不需要并发时使用


隔离级别
脏读
不可重复读 幻读
加锁读
READ UNCOMMITTED
 √ 
 √ 
 √ 
 × 
READ COMMITTED
 × 
 √ 
 √ 
 × 
REPEATABLE READ
 × 
 × 
 √ 
 × 
SERIALIZBLE
 × 
 × 
 × 
 √ 
1.3.2 死锁
    死锁是指两个或多个事务在同一资源上相互占用,并请求锁定对方占用的资源,从而导致恶性循环现象。为了解决死锁,数据库提供了各种死锁检测和死锁超时机制。InnoDB处理死锁的方式是,将持有少量排它锁的事务进行回滚。死锁发生后,只有部分或完全回滚其中一个事务,才能打破死锁。

1.3.3 事务日志
    事务日志可以帮助提高事务的效率。使用事务日志,存储引擎在修改表的数据时,只需要修改其内存拷贝,再把该修改记录行为记录到持久磁盘上的事务日志中,而不用每次都将修改的数据本身都持久化到磁盘。
    事务日志采用的是追加的方式,因此写日志的操作是磁盘上一小块区域的顺序IO,而不像随机IO需要在磁盘的多个地方移动磁头,所以性能更好。
事务日志持久以后,内存中被修改的数据可以慢慢刷回磁盘,称为预写式日志,修改数据需要写再次磁盘。
事务与事务日志的具体工作流程是怎样的?

1.3.4 MYSQL中的事务
    MySQL提供了两种事务型的存储引擎:InnoDB和NDB Cluster。另外还有第三方存储引擎:XtraDB和PBXT。
1)自动提交

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
自动提交设置默认是开启的。该模式下,每个查询都是一个事务,每次查询都会commit。
可以通过设置开启事务:

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.00 sec)
注意:
     对非事务型存储引擎设置是无效的,比如说MyISAM。
     有一些操作,在执行之前会强制执行COMMIT提交当前活动的事务,比如修改表的alter table操作。可以查询官方文档找到具体的操作。

隔离级别,可以通过数据库设置全局事务隔离级别,也可以更改会话事务隔离级别:
查询与设置当前会话事务隔离级别:

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
mysql能识别4个ANSI隔离级别,InnoDB引擎也支持所有隔离级别。  


在事务中混合使用存储引擎
注意:MYSQL服务层不管理事务,事务是由下层的存储引擎实现的,所以在一个事务中,使用多种存储引擎是不可靠的。比如在需要回滚时,非事务性的引擎是无法回滚数据的,会造成数据的不一致性。

隐式和显示锁定
InnoDB采用的是两阶段锁定协议。在事务执行过程中随时都可以锁定,锁定只有在执行commit或rollback时才会释放,且都在同一时刻释放。上面说的是隐式锁定。mysql还可以显示锁定,如:

select ... lock in share mode
select ... for update
lock tables和事务之间可能会相互影响。结果无法预料。除了在autocommit禁用时使用lock tables外其他情况下不要使用。  

1.4 多版本并发控制  

非事务存储引擎下的事务代码实现?版本号方法。     
MYSQL的大多数事务型存储引擎实现都不简单的行级锁。基于提高性能的考虑,一般同时实现了多版本并发控制(MVCC)。包括Oracle、postgreSQL也是这样。
可以认为MVCC是行级锁的一个变种,它在很多情况下避免了加锁操作,因此开销更低。都实现了非阻塞读,写操作只锁定必要的行。
MVCC是通过保存某个时间点的快照来实现的。也即不管需要执行多久,每个事务看到的数据都是一致的。根据事务开始时间的不同,每个事务对同一张表,同一时刻看到的数据可能是不一样的。比如说事务A在时间点13:01开始,它会读取数据库中某条id的记录,该记录的快照时间是13:01或之前的,然后在整个业务过程中都是这一条数据,即使执行到13:05分时,查看数据仍然是这一条;而事务B开始时间是13:03分,它读取的数据是13:03分或之前的快照,版本和事务A已经不同了,而到13:05分时,则两个事务对同一表的同一条记录,查询的数据就可能不同了。
不同的存储引擎对MVCC的实现一不样。典型的有乐观并发控制和悲观并发控制。
InnoDB的MVCC是通过在每行记录后面添加两列隐藏的列来实现的。一列保存了行的创建时间,另一列保存了行的失效时间(失效时间)。这里的时间不是真正的时间,而是系统版本号。每开始一个新事务,系统版本号都会递增。事务开始时的版本号作为系统版本号,用来在查询到的每行记录做比较。下面以REPEATBLE READ级别说明MVCC.
SELECT
     a. InnoDB只查找版本早于当前事务版本的数据行(也即行的系统版本号小于等于事务的系统版本号)。这样可以确保事务读取的行,要么是在事务开始之前已经存在的,要么是事务自身插入或修改过的。
     b. 行的删除版本要么未定义,要么大于当前事务版本号。这可以确保事务读取到的行,在事务开始之前未被删除。
INSERT
     InnoDB为新插入的每一行保存当前系统版本号作为行版本号
DELETE
     InnoDB为删除的每一行都保存当前系统版本号作为删除标识
UPDATE
     InnoDB为插入一行新记录,保存当前系统版本号作为行版本号,同时保存当前系统版本号到原来的行作为删除标识。

保存这两个额外的版本号,使大多数读操作可以不用加锁。这样设计使读数据操作很简单,性能很好,并且也能保证只会读取到符合标准的行。
不足之处是每行记录都需要额外的存储空间,需要做更多的检查工作,以及一些额外的维护工作。
MVCC只在REPEATABLE READ和READ COMMITTED两个隔离级别下工作。其他两个隔离级别都和MVCC不兼容。因为READ UNCOMMITTEDa总是读取最新的数据行,而不是符合当前事务版本的数据行。而SERIALIZABLE则会对所有读取行都加锁。

1.5  MySql的存储引擎 
在文件系统中,MySQL将每个数据库(也称为schema)保存为数据目录下的一个子目录。建表时,MySQL会在数据库子目录下创建一个和表同名的.frm文件保存表的定义。因为MySQL使用文件系统的目录和文件夹来保存数据库和表定义,因此大小写敏感性和具体平台密切相关。 


可以使用SHOW TABLE STATUS命令(在MySQL5.0以后可以在INFORMATION_SCHEMA中对应的表)显示表的相关信息。如:  


mysql> show table status like 'user'\G;
*************************** 1. row ***************************
           Name: user
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 4
Avg_row_length: 107
    Data_length: 540
Max_data_length: 281474976710655
   Index_length: 2048
      Data_free: 112
Auto_increment: NULL
    Create_time: 2015-04-03 22:59:57
    Update_time: 2015-04-03 23:38:32
     Check_time: NULL
      Collation: utf8_bin
       Checksum: NULL
Create_options:
        Comment: Users and global privileges

mysql> select * from tables where table_schema='mysql' and table_name='user'\G;
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: mysql
     TABLE_NAME: user
     TABLE_TYPE: BASE TABLE
         ENGINE: MyISAM
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 4
AVG_ROW_LENGTH: 107
    DATA_LENGTH: 540
MAX_DATA_LENGTH: 281474976710655
   INDEX_LENGTH: 2048
      DATA_FREE: 112
AUTO_INCREMENT: NULL
    CREATE_TIME: 2015-04-03 22:59:57
    UPDATE_TIME: 2015-04-03 23:38:32
     CHECK_TIME: NULL
TABLE_COLLATION: utf8_bin
       CHECKSUM: NULL
CREATE_OPTIONS:
  TABLE_COMMENT: Users and global privileges
各列的含义如下:  

Name: user          表名
Engine: MyISAM 表的存储引擎,旧版本中叫type
Version: 10
系统版本号
Row_format: Dynamic
行的格式。Dynamic长度可变的,如varchar。对于MyISAM可选的有Dynamic、Fixed(固定长)、Compressed(压缩格式)
Rows: 4
表的行数。对于MyISAM和其他引擎是精确的,对InnoDB是估计值
Avg_row_length: 107
平均每行包含的字节数
Data_length: 540
数据表的大小(字节)
Max_data_length: 281474976710655
数据表的最大容量,与存储引擎有关
Index_length: 2048
索引大小(字节)
Data_free: 112
对于MyISAM,是表以分配还没有使用的空间,包括删除的行空间
Auto_increment: NULL
下一个AUTO_INCREMENT的值
Create_time: 2015-04-03 22:59:57
创建时间
Update_time: 2015-04-03 23:38:32    更新时间
Check_time: NULL 使用CHECK TABLE命令或myisamcheck工具最的一次检查表的时间
Collation: utf8_bin 定序
Checksum: NULL
如果启用,保存整个表的实时校验和。
Create_options: 
创建表时指定的其他选项
Comment: Users and global privileges 其他额外信息


1.5.1 InnoDB存储引擎
InnoDB是MySQL的默认事务引擎,也是最重要使用最广泛的存储引擎,被设计用以处理大量短期事务,有自动崩溃恢复特性。除非有非常特别的原因要使用其他存储引擎,否则应优先考虑InnoDB引擎。
InnoDB的数据存储在表空间中,表空间是InnoDB管理的黑盒子,由一系列数据库文件组成。MySQL1.4以上将表数据和索引存储在单独的文件中
InnoDB采用MVCC支持高并发,并实现了四个标准隔离级别。其默认的隔离级别是可重复读(REPEATABLE READ),并通过间隙锁策略防止幻读出现。间隙锁使InnoDB不仅锁住查询涉及的行,也会对索引中的间隙锁进行锁定,以防止幻读出现。
InnoDB表是基于聚簇索引建立的。该索引对主键查询性能非常高,不过它的二级索引必须包含主键列,所以如果主键很大的话,二级索引也会很大,因此表上索引比较多时,主键要尽可能的小。InnoDB的存储格式是平台独立的,也就是说要以将数据和索引文件从Inter平台复制到PowerPC或Sun SRARC平台。
1.5.2 MyISAM存储引擎
在MySQL5.1之前,MyISAM是MySQL的默认存储引擎。其特性包括:全文索引、压缩、空间函数(GIS),但MyISAM不支持事务和行级锁,而且有一个很大的缺陷即崩溃后无法恢复。
MyISAM会将表存储在两个文件中。数据文件和索引文件,分别以.MYD和.MYI为扩展名
MyISAM的表可以包含动态行或静态行。MySQL会根据表的定义来决定使用何种行格式。MyISAM存储的行记录数,一般受限于可用的磁盘空间,或者单个文件的最大尺寸。
在MySQL5.0中,MyISAM表如果是变长行,则默认配置只能处理256T的数据,因为指向数据记录的指针长度为6个字节,2^48=256T。而更早期的版本中默认是4个字节,即只能保存4G数据。而所有版本的MySQL都支持8个字节的指针。要改变MyISAM表的指针长度,可以通过修改MAX_ROWS和AVG_ROW_LENGTH选项的值来实现,乘积即指表达到最大大小。
加锁与并发:MyISAM对整张表加锁,而不是行。读取时会对需要读到的所有表加共享锁(读锁,S锁),写入时对表加排他锁(写锁,X锁)。但是在表有读取查询时,也可以往表中插入新的记录(并发插入)。
修复:对于MyISAM表,MySQL可以手工或者自动执行和修改操作。但这里说的修改和事务修复以及崩溃恢复是不同的概念。执行表的修复可能导致一些数据丢失,而且修改是非常慢的。可通过CHECK TABLE mytable检查表的错误,如果有错误可通过执行REPAIR TABLE mytable进行修复,如果服务关闭,可通过myisamchk命令修复。
索引特性:即使是BLOB和TEXT等长字段也可以基于前500个字符创建索引。MyISAM也支持全文索引。
延迟更新索引键(delayed key write):创建MyISAM表时,如果指定了DELAY_KEY_WRITE选项,在每次修改执行完成时,不会立即将索引数据写入磁盘,而会写到内存的键缓冲区,只有清理键缓冲区或关闭表的时候才会将对应的索引块写入到磁盘。这种方式可以大大提高写性能。但在数据库或主机崩溃时会造成索引损坏。可全局设置也可表设置。
MyISAM压缩表
对于导入以后不再修改的数据表,可以采用MyISAM压缩表。压缩表不能进行修改,支持索引。压缩表可以极大的压缩磁盘空间,因此可以减少磁盘IO,从而提升性能。以现在的硬件能力,读取压缩表数据时解压带来的开销影响并不大,而减少磁盘IO带来的好处要大的多。
MySIAM性能
MyISAM引擎设置简单,数据以紧密格式存储,所以在某些场景下性能很好。但其最典型的问题是表锁问题
其他存储引擎:
Archive引擎(只支持INSERT和SELECT操作)。
Memory引擎(内存表),不持久化。
如果需要快速访问数据,并且这些数据不会被修改,重启丢失也没关系,则可以使用内存表。性能比MyISAM快一个数量级。
如果MySQL在执行查询的过程中需要使用临时表来保存中间结果,内部使用的临时表就是Memory表。如果中间表太大超出了Memory表的限制,或者含有BLOB或TEXT字段,则临时表会转为MyISAM表。
人们经常混淆Memory表和临时表。临时表是指使用CREATE TEMPORARY TABLE语句创建的表,它可以使用任何存储引擎,因此和Memory表不是一回事。临时表只在单个链接中可见,当连接断开时,临时表也不复存在。

1.5.5 选择合适的引擎
除非需要用到某些InnoDB不具备的特性,并且没有其他办法可以替代,否则都应优先使用InnoDB引擎。
除非万不得已,否则建议不要混合使用存储引擎(如不同引擎对事务支持不同,混合使用将使事务不能正常工作)。
事务:
     如果应用需要事务支持,则选InnoDB。如果不需要事务,且主要是SELECT和INSERT(如日志型需求),可选MyISAM.
备份:
     如果需要热备份,使用InnoDB
崩溃恢复:
     MyISAM易崩溃,不易恢复,建议使用InnoDB


1.5.6 转换表的存储引擎
ALTER TABLE
     alter table mytable engin=innodb.

create table mytable (id int(11) ,primary key (id));

show create table mytable;
+---------+--------------------------------------------------------------+
| Table   | Create Table                                                 |
+---------+--------------------------------------------------------------+
| mytable | CREATE TABLE `mytable` (
  `id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------+--------------------------------------------------------------+
alter table mytable engine='MyISAM'mysql> show create table mytable;
+---------+--------------------------------------------------------------+
| Table   | Create Table                                                 |
+---------+--------------------------------------------------------------+
| mytable | CREATE TABLE `mytable` (
  `id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+---------+--------------------------------------------------------------+

上述方法的问题是:需要执行时间很长。MySQL会按行将数据从原表复制到一个新表中,在复制期间会占用所有的IO,原表上会加锁。比较好的方法是手工进行表的复制。
如果转换表的存储引擎,如InnoDB --> MyISAM --> InnoDB,则原表的外键将消失。
导出与导入
为更好地控制转换过程,可使用mysqldump工具将数据导出到文件,然后修改文件中的create table语句的存储引擎选项,同时修改表名(因为同一个数据库中不能存在相同的表名,即使它们使用的是不同的存储引擎)。同时注意mysqldump默认会自动在CREATE TABLE前加DROP TABLE语句,不注意这一点可能会导致数据丢失。
如果dump时出现如下错误:

mysqldump: Couldn't execute 'SET OPTION SQL_QUOTE_SHOW_CREATE=1': You have an error in your
SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use
near 'OPTION SQL_QUOTE_SHOW_CREATE=1' at line 1 (1064)
解决文案是:
The reason for this is that MySQL 5.6 has removed support for “SET OPTION” and your mysql client
tools are probably on older version. Most likely 5.5 or 5.1. There is more info about this issue on
MySQL bugs website.
因此只要升级一下客户端就可以了。
mysqldump -uroot -p test mytable
-- MySQL dump 10.13  Distrib 5.6.19, for Linux (x86_64)
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version     5.6.19

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `mytable`
--

DROP TABLE IF EXISTS `mytable`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `mytable` (
  `id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `mytable`
--

LOCK TABLES `mytable` WRITE;
/*!40000 ALTER TABLE `mytable` DISABLE KEYS */;
/*!40000 ALTER TABLE `mytable` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2015-06-07 20:22:10
如果想去掉DROP TABLE,可以查看dump帮助文档
--add-drop-table    Add a DROP TABLE before each create.(Defaults to on; use --skip-add-drop-table to disable.)
mysqldump -uroot -p --skip-add-drop-table test mytable

创建与查询(CREATE 和 SELECT)
第三种转换技术综合第一种方法的高效和第二种方法的安全。不需要导出整个表的数据,而是选创建一个新的存储引擎的表,然后利用INSERT ... SELECT 语法来导数据。
INSERT INTO innodb_table SELECT * FROM myisam_table;

myisam时表的存储是这样的:
mytable.frm  mytable.MYD  mytable.MYI
innodb时表的存储是这样的:
mytable.frm  mytable.ibd
mysql默认的数据文件存储在:
/var/lib/mysql