高性能mysql[day 1]

 

大多数MySQL的核心功能都在第二层 包括查询解析，分析，优化，缓存以及所有的内置函数（例如日期，时间，数学和加密函数），所有跨存储引擎的功能都在这一层实现：存储过程，触发器，视图。

第三层包含了存储引擎，存储引擎负责MySQL中的数据存储和提取。

 1.1.1连接管理与安全性

每个客户端连接都会在服务器进程中拥有一个线程，这个连接的查询只会在这个单独的线程中执行，MySQL5.5或者更新的版本提供了一个API,支持线程池插件，可以使用少量的线程来服务大量的连接。

认证：用户名，原始主机和密码，如果使用SSL的方式连接，还可以使用X.509证书认证，一旦认证成功，服务器会继续验证该客户端是否具有执行某个特定查询的权限

1.1.2 优化与执行

MySQL会解析查询，并创建内部数据结构（解析树），包括重写查询，决定表的读取书讯，以及选取合适的索引等。

1.2 并发控制

在处理并发读写时，通常有两种锁来解决，共享锁（shared lock）和排他锁(exclusive lock) 也叫读锁和写锁。读锁是共享的，或者说是相互不阻塞的。写锁是排他的，也就是说一个写锁会阻塞其他的写锁和读锁
1.2.2 锁粒度
表锁（table lock）
表锁是MySQL中最基本的锁策略，并且是开销最小的策略。
写锁通常比读锁有更高的优先级
MySQL自身会使用ALTER TABLE 之类的语句使用表锁，而忽略存储引擎的锁机制
行锁 row lock
行锁可以最大程度的支持并发处理，同时也带来最大的锁开销，InnoDB和XtraDB 以及一些其他的存储引擎实现来行锁
1.3 事务
事务就是一组原子性的SQL查询，或者说一个独立的工作单元，也就是说在事务中的语句，要么全部成功，要么全部失败
原子性 atomicity
一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性
一致性 consistency
数据库总是从一个一致性的状态转换到另外一个一致性的状态。

 

隔离性 isolation
通常来说，一个事务所做的修改在最终提交之前，对其他事务是不可见的。
持久性 durability
一旦事务提交，则其所做的修改就会永久保存到数据库中，此时即使系统崩溃，修改的数据也不会丢失
1.3.1 隔离级别
READ UNCOMMITED 未提交读
在READ UNCOMMITED 级别，事务中的修改，即使没有提交，对其他事务也都是可见的，事务可以读取未提交的数据，这也被称为脏读 dirty read。
READ COMMITED 提交读
大多数数据库系统默认的隔离级别是 READ COMMITED，但MySQL不是。一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的，这个级别有时候也叫不可重复读，因为两次执行同样的查询，可能会得到不一样的结果
REPEATABLE READ 可重复读
MYSQL模式事务的的隔离级别
REPEATABLE READ 解决了脏读的问题 ，该级别保证了同一个事务中多次读取同样记录的结果是一致的。但是可能导致幻读 phantom read 的问题。所谓幻读，指的是当某个事务再次读取某个范围内的记录时，会产生幻行 phantom row . InnoDB和XtraDB存储引擎通过多版本并发控制 MVCC multiversion concurrency control 解决了幻读的问题。

SERIALIZABLE 可串行化
serializable 是最高的隔离级别。它通过强制事务串行执行，避免了前面说的幻读的问题，简单来说serializable会在读取的每一行数据上都加锁
隔离级别 脏读 不可重复读 幻读 加读锁
read uncommited yes yes yes no
read commited no yes yes no
repeatable commited no no yes no
serializable no no no yes
1.3.2 死锁
死锁是指两个或者多个事务在同一资源上互相占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。
为了解决这种问题，数据库实现了各种死锁检测和死锁超时机制。
1.3.3 事务日志
事务日志可以帮助提高事务的效率，write-ahead-logging 预写式日志，修改数据需要写两次磁盘
1.3.4 MySQL中的事务
MySQL提供了两种事务型的存储引擎 InnoDB和NDB Cluster，另外还有一些迪桑存储引擎也支持事务 如XtraDB和PBXT
自动提交 autocommit
MySQL默认采用自动提交autocommit 模式， 也就是说如果不是显式地开始一个事务，则每个查询都被当作一个事务执行提交操作。
查看命令 show variables like 'autocommit'

在数据库定义语言DDL中，ALTER TABLE 或者LOCK TABLE 等语句都会强制commit提交当前活动的事务
mysql 可以通过 SET TRANSACTION ISOLATION 命令来设置隔离级别
在事务中混合使用存储引擎
如果在事务中混合使用来事务型和菲事务型的表，在正常提交的情况下不会有什么问题，但是如果该事务需要回滚，非事务型的表上的变更就无法撤销
隐式和显式锁定
innodb采用的是两阶段锁定协议 two-phase locking protocol。在事务执行过程中，随时可以执行锁定，锁只有在执行commit或者rollback的时候才会释放。
另外innodb也可支持通过特定的语句显式锁定
select ... lock in share mode
select .. for update
mysql的服务器层也支持lock tables unlock tables 进行锁定
1.4 多版本并发控制
可以认为MVCC是行级锁的一种变种，但是它在很多情况下避免来加锁操作，因此开销更低，虽然实现机制有所不同，但大都实现了非阻塞的读操作，写操作也只锁定必要的行
mvcc的实现，是通过保存数据某个时间点的快照来实现的，也就是说不管需要执行多长时间，每个事务看到的数据都是一致的。根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。
不同存储引擎mvcc实现是不同的，典型有乐观并发控制和悲观并发控制。

innodb的mvcc，是通过在每行记录后保存两个隐藏的列来实现的，这两个列，一个保存了行的创建时间，一个保存了行的过期时间，当然存储的并不是实际的时间值，而是系统版本号。每开始一个新的事务，系统版本号都会自动递增。
mvcc只在repeatable read 和 read commited 两个隔离级别下工作。其他两个隔离级别都都mvcc不兼容， 因为read uncommited总是读取最新的数据行，而不是符合当前事务版本的数据行，而serializable则对所有读取的行都加锁

1.5 MySQL的存储引擎
SHOW TABLE STATUS LIKE 'TABLE_NAME' 查看表的情况
InnoDB存储引擎
性能和自动崩溃恢复的特性
InnoDB的数据存储在表空间tablespace中，InnoDB采用mvcc来支持高并发，并且实现来四个标准的隔离级别，其默认级别是REPEATABLE TABLE 可重复读，并且通过间隙锁 next key locking 策略防止幻读的出现，间隙锁使得InnoDB不仅锁定查询涉及的行，还会对索引中的间隙进行锁定，以防止幻影行的插入。
InnoDB的表是基于聚簇索引建立的，它的二级索引 secondary index 非主键索引中必须包含主键列，所以说如果主键列很大的化，其他索引都会很大，因此 若表上的索引比较多的化，主键应当尽可能的小。

InnoDB内部做了很多优化，包括从磁盘读取数据时采用的可预测性预读，能够自动在内存中创建hash索引以加速读操作的自适应哈希索引 adaptive hash index 以及能够加速插入操作的插入缓冲区insert buffer等
作为事务型存储引擎，InnoDB通过一些机制和工具支持真正的热备份，如MySQL Enterprise ，XtraBackup
MyISAM存储引擎
特性：全文索引，压缩，空间函数GIS等，但Myisam不支持事务和行锁，崩溃后无法安全恢复。但是对于只读的数据，或者表比较小，可以忍受修复repair操作，则可以继续使用myisam
存储：myisam会将表存在两个文件中，数据文件和索引文件，分别以.myd和.myi为扩展名
Myisam对整张表加锁，而不是针对行，读取时会对需要读到的所有表加入共享锁，写入时则对表加入排他锁。但是当表有读取查询的同时，也可以往表中插入新的记录（并发插入,concurrent insert)
修复：CHECK TABLE mytable 检查表的数据，如果有错误可以通过执行 REPAIR TABLE mytabl 进行修复，另外如果mysql服务器已经关闭，可以通过 myisamchk进行检查和修复操作
索引特性：myisam支持全文索引，这是一种基于粉刺创建的索引，可以支持复杂的查询
延迟更新索引键 delayed key write
创建myisam表的时候，如果指定了 DELAY_KEY_WRITE选项，在每次执行修改完成时，不会立刻将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区in-memory key buffer,只有在清理缓冲区或者关闭表的时候才会将对应的索引块写入到磁盘，极大提升写入性能，但是唉数据库或者主机崩溃时会造成索引损坏，需要执行修复操作

 

myisam压缩表
可以使用myisampack对myisam表进行压缩也叫打包，压缩表是不能进行修改的，压缩表可以极大地减少磁盘空间占用，因此也可以减少I/O，从而提升查询吸能
Archive 引擎
Archive存储引擎只支持INSERT 和SELECT操作，会缓存所有的写并利用zlib对插入的行进行压缩，减少I/O操作，适用于日志和数据采集类应用；支持行级锁和专用的缓冲区，可以实现高并发的插入。
Blackhole引擎
Blackhole没有实现任何的存储机制，但服务器会记录其表的日志，所以可用于复制数据到数据库，或者简单的记录到日志
CSV引擎
可以将普通的CSV文件作为MySQL的表来处理
Federated引擎
访问其他MySQL服务器的一个代理
Memory引擎
支持hash索引，因此查找速度非常块，表级锁并发写入低

 

选取合适的存储引擎需要考虑的因素
事务，备份，崩溃恢复。独有的特性。
1.5.6转换表的引擎
1，ALTER TABLE mytable ENGINE = InnoDB;
如果转换表的存储引擎，将会失去和原引擎相关的所有特性。
2，导入和导出 mysqldump
3, insert ... select
pt-online-schema-change工具