MySql 架构与历史

Mysql 逻辑架构

MySql逻辑架构图：整体分为三层：

第一层(客户端)：连接处理、授权认证、安全等 

第二层(服务层)：大多数核心服务，查询解析、分析、优化、缓存、内置函数，所有跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等

第三层(存储引擎)：负责MYSQL中的数据储存和提取，服务器通过API与存储引擎通信。

并发控制

读写锁

共享锁（读锁）：读锁是共享的，多个客户（连接）可以在同一时刻同时读取同一资源，而互不干扰

排它锁（写锁）：写锁是排它的，一个写锁会阻碍其他的读锁和写锁

锁粒度

一种题号共享资源的并发性的方式就是让锁更有针对性，即锁粒度影响并发程度。

锁粒度越小哦（行锁小于表锁）并发程度越高，但开销也越大（获取锁、检查锁、释放锁）、冲突的概率也越高

事务

事务是一组原子性的sql，或者说一个独立的工作单元，改组sql语句要不全部执行，要不全不执行。

单初的事务概念并不是全部，试想一下，一组sql在运行过程中服务器突然崩溃了，上述的事务概念如何保证？\
所以一个良好的事务系统必须满足ACID

ACID

A(atomicity)原子性：事务内的一组sql要不全部执行要不全不执行

C(consistency)一致性：时终保持一个正确的状态到另一个正确的状态，由AID保证了一致性，事务执行前后我们的约定没有被打破（例如约束字段A为int且>=0，则任何情况下不可能出现A=非int或者小于0的情况）\
知乎的优秀回答：
https://www.zhihu.com/question/31346392
摘自数据密集型应用系统设计中:"ensuring the consistency is the responsibility of user, not DBMS.", "DBMS assumes that consistency holds for each transaction".(确保一致性是用户的责任，而不是DBMS的责任，DBMS假定每个事务都保持一致性)

I(isolation)隔离性：一个事务内所做的修改未提交前对其他事务不可见

D(durability)持久性：一旦事务提交，则所做的修改就会永久保存到数据库中（但任何策略都很难保证百分百的持久化）

隔离级别

READ UNCOMMITTED(未提交读)：事务可以读取到其他事务未提交的修改，又称为脏读（如事务A读到事务B未提交的修改，并基于此做了处理后，A回滚了）

READ COMMITED(提交读)：事务只能读取到其他事务提交后的修改，避免了脏读，但无法避免不可重复读（同一个事务中获取两次同一条数据出现了不同的结果，即：事务A开始获取了id=1的数据，此时事务B修改了id=1的数据并提交事务，然后事务A又再次获取id=1的数据，两次结果不一样）

REPEATABLE READ(可重复读)：保证了事务中多次读取同样记录的结果是一致的，即解决了不可重复读的问题但无法解决幻读（区别于不可重复读，幻读强调范围查询，比如事务A查询创建时间在近一个月范围内的数据量c1，事务B插入了一条该范围内的数据，事务A再次查询得到的c2!=c1） InnoDB存储引擎通过多版本并发控制（MVCC Muitiversion Concurrency Controller）来实现该隔离级别

SERIALIZABLE(串行读)：强制所有的事务串行执行，在读取的每一行数据上加锁

MVCC

行级锁的变种，很多情况下避免了加锁操作。从而开销更低。
通过保存某个时间节点的快照来实现，在每行记录后面保存两个隐藏列来实现（创建时间、过期时间并不是实际时间而是版本号，版本号递增，且基于表的并不是基于每行记录递增）
下面为在REPEATABLE READ 隔离级别下，MVCC的具体操作:
SELECT： InnoDB会根据以下两个条件检查每行记录，a:查找早于或者等于当前事务版本号的数据行（意味着数据是之前已经存在或者是在当前事务中创建的） b:查找过期时间不存在晚于当前事务的记录（意味着数据未被删除或者在事务开始后才被删除的）
INSERT：为新插入的每一行添加当前版本号
DELETE: 为删除的记录添加过期版本号==当前版本号 UPDATE：插入一条新记录，版本号==当前版本号，标记之前记录的过期版本号为当前版本号

MVCC只在REPEATABLE READ 和 READ COMMITED 两个隔离级别下工作。其他两个隔离级别和MVCC不兼容，因为READ UNCOMMITED总是读取最新的行，而SERIALIZABLE则会对所有的行都加索。

PS：MVCC使用行锁+间隙锁的方式防止幻读的发生（NEXT-KEY LOCK）
间隙锁（GAP LOCK）：在涉及范围查询时，对范围的开始和结束上锁，其他事务此时无法获取到该范围内数据的锁。

死锁

指两个或多个事务在同一资源上相互占用，并请求锁定对方已占用的资源，从而恶性循环的现象。当事务以不同顺序获取锁定资源时，就可能产生死锁。

例如：

恰巧两个事务同时都执行了第一条语句，即两个事务分别锁定了一条记录，而都尝试去获取另一条记录的锁，则会陷入死循环成为死锁。

解决方式: 1、设置超时时间 2、发起超时检测，回滚其中较小的事务

事务日志

使用事务日志，存储引擎在修改数据时先修改内存拷贝，然后将修改行为顺序追加写入事务日志，再从事务日志持久化到真正的数据文件。

优点：顺序写入事务日志可以保证最快时间将数据持久化，在意外崩溃可以把损失降到最低（在内存中尚未写入事务日志的数据会丢失，写入事务日志未写入数据文件的可以从事务日志恢复）

MySQL中的事务

自动提交

MYSQL默认采用自动提交（AUTOCOMMIT）的模式，如果不是显示的开始一个事务，则每个查询都会被当作一个事务执行提交操作。
有一些命令，在执行之前会强制提交事务，典型的时DDL中，会导致大量数据改变的操作，例如ALTER TABLE、LOCK TABLES

在事务混合使用存储引擎

混合使用时，在事务回滚后会导致数据不一致，不支持事务的存储引擎无法被回滚

显示和隐式锁定

显示：例如 SELECT ... LOCK IN SHARE MODE; SELECT ... FOR UPDATE (不符合规范)

阴式：在事务执行过程中，随时都可以执行锁定，在提交事务或者回滚事务解除事务中的全部锁定。

MySQL的存储引擎

在文件系统中，MYSQL将每个数据库（schema）保存为数据目录下的一个子目录。创建表时，MySQL会在数据库子目录下创建一个和表同名的.frm文件保存表的定义。大小写敏感性与具体的平台密切相关，windows不区分大小写而Unix则区分。
不同存储引擎保存数据和索引的方式不同，但表的定义是在Mysql的服务层统一处理的。可以使用show table status like 'table_name'查看表的相关信息

InnoDB

MySQL的默认事务性引擎。

采用MVCC支持高并发，默认隔离级别未可重复读（REPEATABLE READ）,并且通过间隙锁策略防止了幻读的出现。间隙锁不仅仅锁定涉及的行，还会对索引的间隙进行锁定。

基于聚簇索引（主键索引，数据与主键一起存储，叶子节点为数据行），聚簇索引对主键查询有很高的性能，二级索引（非聚簇索引）必须包含主键列（叶子节点为主键列），所以若主键列很大会导致所有的索引都会很大，通过二级索引定位到主键列，再通过主键列到主键索引中定位数据行（回表）

内部做了很多优化，包括从磁盘读取数据时采用的可预测性预定，能够自动在内存中创建hash索引以加索读操作的自适应哈希索引，能够加速插入操作的插入缓存区。

通过一些机制和工具支持真正的热备份，其他存储引擎不支持

MyISAM 存储引擎

5.1及之前的版本为默认的存储引擎，提供了大量的特性，全文索引、压缩、空间函数（GIS）等，但不支持行级锁和事务，且崩溃后无法安全恢复

MyISAM 对整张表加索，读取时会对需要的所有表加共享锁，写入时则会对表加排他锁。但是在表有读取查询的同时，也可以在表中插入新的记录（并发插入）

对应MyISAM即使时BLOB和TEXT等长字段，也可以基于前500个字符创建索引，也支持全文索引。

支持压缩表，对于不会再进行修改的表进行压缩操作，压缩后不能再被修改，但可以极大程度减少空间占用，减少磁盘IO从而提交查询性能，同时压缩表也支持索引（同样，索引也是只读的）

选择合适的索引

大多数情况下，Innodb都是最好的选择，除非用到一些InndoDB不具备的特性，且没有办法可以代替，可以考虑结合其他存储引擎。

若其他引擎的特性能更好的满足需求，也可以考虑其他的引擎，举个例子,如果不在乎扩展能力和并发能力，也不在乎崩溃后的数据丢失问题，却对InnoDB空间占用多敏感，可以选择MyISAM

应用需要不同的存储引擎，请先考虑以下几个因素。

事务：需要事务支持，InnoDB或者XtraDB是目前最好的选择，如果不需要事务，并且主要是SELECT和INSERT 操作，那么MyISAM是不错的选择。（一般日志型的应用符合这一特性）

备份：如果需要在线热备份，InnoDB就是比较好的选择，若可以定时关闭服务器来执行备份，那么备份的因素可以忽略

崩溃恢复：数据量比较大的时候，系统崩溃后如何快速恢复是一个需要考虑的问题。相对而言，MyISAM崩溃发生损坏的概率比InnoDB高很多，并且恢复速度也要慢。

MySQL基准测试

目标：掌握系统的行为、重现某个系统状态、新硬件的可靠性测试

sysbench:是一款非常优秀的MYSQL基准测试工具

为什么需要基准测试

基准测试是唯一方便有效的得到系统在给定的工作负载下会发生什么的方法。

基准测试可以完成以下工作：

验证基于系统的一些假设，确认这些假设是否符合实际假设
重现系统中的某些异常，以解决这些异常
测试当前系统的运行清空，如果不清楚系统当前的性能，就无法确认某些优化的效果如何。也可以利用历史的基准测试结果来分析诊断一些无法预测的问题
模拟比当前系统更高的负载，以找出系统随着压力增加而可能遇到的扩展性瓶颈
规划未来的业务增长。基准测试可以评估在项目未来的负载下，需要什么样的硬件，需要多大容量的网络，以及相关的资源。这有助于降低系统升级喝重大变更的风险。

基准测试的策略

两种主要的策略

1、集成式full-stack：针对整个系统的整体测试 
2、单组件式single-component：单独测试MySQL

测试何种指标

吞吐量：单位时间内的事务处理数，每秒事务数TPS，每分钟事务数TPM
响应时间或者延迟：任务测试所需的整体时间，根据不同时间单位计算出平均响应时间、最小响应时间、最大响应时间和所占百分比
并发性：同时工作中的线程或连接数，通过判断并发性提高时对吞吐量和响应时间的影响来判断并发性的瓶颈
可扩展性：指的是理想的情况下，给系统增加一倍的资源，是否可以达到双倍的效果，比如cpu增加一倍吞吐量是否可以加倍

基准测试的工具

集成式测试工具

ab是一个Apache HTTP服务端基准测试工具。可以测试HTTP服务器每秒最多可以处理多少请求，只能针对单个url进行压力测试

http_load

跟ab类似，对Web服务器进行测试，但比ab更加灵活。
可以通过一个输入文件提供多个URL，http_load在这些URL中随机选择进行测试；
也可以定制http_load,使其按照事件比率进行测试，而不仅仅是测试最大请求处理能力。

JMeter

JMeter是一个java应用程序，可以加载其他应用并测试其性能。
它虽然是设计用来测试Web应用的，但也可以用于测试其他诸如FTP服务器，或者通过JDBC进行数据库查询测试。

单组件式测试工具

mysqlslap

可以模拟服务器的负载，并输出计时信息。它包含在MYSQL 5.1的发行包中，应该在MySQL4.1或者更新的版本中都可以使用。
测试时可以执行并发连接数，并指定SQL语句

sql-bench

Mysql发行包中提供的基准测试套件，用于在不同数据库服务器上进行比较测试。单线程，主要用于测试服务器执行查询的速度。

Super Smack

提供压力测试和负载生成，是一个复杂而强大的工具，可以模拟多用户访问，可以加载测试数据到数据库，并支持随机数填充测试表。

Database Test Suit

一款类似某些工业标准测试的测试工具集

Percona's TPCC-MySQL Tool

本书作者开发，可用于评估大压力下Mysql的一些行为。

sysbench

一款多线程系统压测工具。可以根据影响数据库服务器性能的各种因素来评估系统的性能。
例如，测试文件IO、操作系统调度器、内存分配和传输速度、POSIX线程、以及数据库服务器。
是一种全能测试工具，支持MySQL、操作系统和硬件的硬件测试。

MySQL BENCHMARK() 内置函数

可以测试某些特定操作的执行速度。测试可以是需要执行的次数和表达式，表达式可以是任何的标量表达式，例如

执行后的返回值永远是0，但可以通过客户端返回的时间来判断执行的时间。在上边例子上可以看到MD5()执行比SHA1()执行要快。
这个函数只是简单的返回服务器执行表达式的时间，而不会设计分析和优化的开销。
而且表达式必须像这个例子一样包含用户自定义的变量，否则多次执行同样的表达式会因为命中缓存而影响结果

服务器性能剖析

如何确认服务器是否到了性能最佳状态、找出某条语句为什么执行不够快、以及诊断被用户描述成"停顿"、"堆积" 或者卡死的某些间歇性疑难故障。本章主要针对这三个问题做出解答，我们将提供一些工具和技巧来优化整机的性能、优化单条语句的执行速度，以及诊断那些很难察觉的疑难问题。

性能即响应时间，数据库服务器的性能用查询的响应时间来度量，单位是每个查询花费的时间。

sql语句（任务）执行花费的时间分为两部分:等待时间和执行时间。

  如果要优化任务的执行时间，最好的办法是通过测量定位不同的子任务花费的时间，然后优化去掉一些子任务、降低子任务的执行频率或者提升子任务的效率。
  而优化任务的等待时间则相对复杂一些，因为等待有可能是由其他系统间接导致的，任务之间也可能由于争用磁盘或者CPU资源而相互影响。根据时间是花在执行还是等待上的不同，诊断也需要不同的工具和技术。
  基于执行时间的分析是研究什么任务执行时间最长，而基于等待的分析则是判断任务在什么地方被阻碍。

**值的优化的查询 **

第一：只占总响应时间比重很小的查询是不记得优化的
第二：优化的成本大于收益，就应当停止优化
第三：执行次数少，但每次都很慢，影响到用户体验的

应用程序性能剖析工具：New Relic

剖析MYSQL查询

剖析服务器负载

MySql5.1及更新的版本中，慢查询日志的功能被加强，可以通过设置long_query_time为0来捕获所有的查询，且查询的响应时间可以做到微秒级。

当前版本中，慢查询日志是开销最低、精度最高的测量查询时间的工具，且不需要担心慢查询日志带来的额外的I/O开销，可能更需要担心的是日志可能消耗的大量磁盘空间。

慢查询日志生成剖析报告需要一款好工具：pt-query-digest,一般情况下，只需要将慢查询日志文件作为参数传递给pt-query-digtest。就可以正常的工作了。以上为剖析报告以上为’最差‘查询的详细报告

剖析单条查询

SHOW PROFILE

默认是禁用的，通过服务器变量再会话（连接）级别动态的修改。 mysql> SET profiling=1

开启后，执行查询语句，执行完成后使用show profile查看剖析结果，给出了每个步骤的花费时间;

SHOW STATUS

返回了一些计数器，既有服务器级别的也有某个连接的会话级别的计数器。

show global status：查看服务器级别的从服务器启动时开始计算的查询次数统计。（全局的计数器也会出现在SHOW STATUS的结果中，在测量时要注意区分）

show status大部分结果都只是一个计数器，可以显示某些活动如读索引的频繁程度，但无法给出消耗了多少时间。show status的结果中只有一条是指的操作时间（Innodb_row_lock_time），而且是全局级的，而且还是无法测量会话级别的工作。

虽然无法提供基于时间的统计，但对于在执行查询后观察某些计数器的值还是有帮助的，有时候可以猜测哪些操作代价高或者消耗的时间多，最有用的包括句柄计数器（handler counter）、临时文件和表计数器等。

下边例子演示如何将会话级别的计数器重置为0，然后进行查询，再检查计数器的结果：

诊断间歇性问题

间歇性问题比如系统偶尔停顿或者慢查询，很难诊断。

判断是单条查询问题还是服务器的问题：

如果全部查询都突然变慢，那么可能大概率是服务器的问题，反过来，如果整体没问题，只是某条查询突然边慢，就需要将注意力放到这条特定的查询上来。

如何判断：

一、使用SHOW GLOBAL STATUS 
以较高频率执行SHOW GLOBAL STATUS命令捕获数据，问题出现时，则可以通过某些计数器的尖刺或者凹陷来发现，
这个方法比较简单，对服务器的影响也很小，下面为示例：

以上命令每秒捕获一次SHOW GLOBAL STATUS的数据，
输出给awk计算并输出每秒的查询数、Thread_connected和Threads_runnings（表示正在执行查询的线程数）。
这三个数据对服务器级别偶尔停顿的敏感性很高。
一般发生此类问题时，根据原因的不同和应用连接数据库方式的不同，每秒的查询一般会下跌，而其他两个则至少会出现尖刺。
在这个例子中，应用使用了连接池，所以Thread_connected没有变化，但正在执行查询的线程数明显增加，
同时每秒的查询数相比正常数据严重下跌。

如何解释呢？ 有两个原因可能性比较大。其中之一时服务器内部出现了某种瓶颈，导致新查询需要获取老查询正在等待的锁而产生堆积，	 
另一个常见的原因时服务器突然收到了大量请求的冲击。

二、使用SHOW PROCESSLIST 
不停的捕获SHOW PROCESSLIST的输出，来观察是否有大量线程处于不正常的状态或者不正常的特征。‘
例如查询很少会长时间处于“statistics”,这个状态一般是指服务器在查询优化阶段如何确认表关联的顺序（通常都是非常快的）
另外，也很少会见到大量线程报告当前连接用户是"未经验证的用户(Unauthenticated user)"。
这只是在连接握手的中间过程中的状态，当客户端等待输入用于登陆的用户信息的时候才会出现。

在使用SHOW PROCESSLIST命令时，在尾部加上\G可以垂直的方式输出结果，这样会将每一行的每一列都单独输出为一行，这样可以方便
使用sort|uniq|sort一类的命令来激素那某个列值出现的次数。

要查看不同的列，只需要修改grep的模式即可，在大多数的案例中，State列都非常有用。以上例子中，很多线程处于查询执行结束部分的
状态，包括“freeing items”、“end”、“cleaning up”和“logging slow query”。大量线程处于“freeing items”状态时出现了大量
有问题查询的很明显的特征和指示。

三、使用查询日志
开启慢查询日志并在全局级别设置long_query_time=0,并且要确认所有连接都采用了新的设置，这可能需要重置所有连接以使
性的全局设置生效；或者使用Percona Server的一个特性，可以在不断开现有连接的情况下动态的使设置强制生效。

Schema与数据类型优化

选择优化的数据类型

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

更小的通常更好：
	尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快，因为占用更小的磁盘、内存和CPU缓存，处理需要的CPU周期也更小。
简答就好:
	简单的数据类型的操作通常需要更少的CPU周期，例如：整型比字符操作代价更低，因为字符集和校对规则（排序规则）
    使字符比较比整型比较更复杂。这里有两个例子：一个是应该使用MYSQL内建的类型再存储日期和时间
    另一个是应该使用整型来存储IP地址。

尽量避免NULL：
	若查询中包含为NULL的列，对MYSQL来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都跟复杂。
    可为NULL的列会使用更多的存储空间，在MYSQL里也需要特殊处理。当可为NULL的列被索引时，
    每个索引记录需要一个额外的字节。
    通常把可以NULL的列改为NOT NULL带来的提升比较小，所以调优时没必要首先改造该情况，除非确认这会导致问题，
    但是计划在列上建索引，应该尽量避免设计为NULL的列。
    当前也有例外，InnoDB使用单独的位（bit）存储NULL值，所以对于稀疏数据有很好的空间效率。但这一点不适用于MYISAM。

整数类型

TINYINT, SMALLINT,MEDIUMINT,INT,BIGINT：分别使用8，16，24，32，64位存储空间，可以存储值得范围从 -2^(N-1)到2^(N-1)
整数类型有可选得INSIGNED属性，表示不允许负值，大致可以使整数的上限提高一倍，例如TINYINT UNSIGNED（0~255）而TINYINT的
范围是-128~127。
Mysql可以为整数类型指定宽度，例如INT(11)，并不会限制值得合法范围，而是规定了MYSQL得一些交互工具（如MYSQL命令行客户端）
用来显示字符的个数。对于存储和计算来说，INT(1)和INT(20)是相同的。

实数类型

实数是带有小数的数字，但是并不只是为了存储小数部分，也可以使用DECIMAL存储比BIGINT还大的整数，Mysql即支持精确类型，也支持不精确类型。
FLOAT和DOUBLE支持使用标准的浮点运算进行近似运算。
DECIMAL用于存储精确的小数。
因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用DECIMAL，例如财务数据，但在数据量较大的时候，
可以考虑使用BIGINT代替DECIMAL，将需要存储的货币单位根据位数乘以相应的倍数即可。

VARCHAR和CHAR类型

VARCHAR:
VARCHAR类型用于存储可变长字符串，比定长更节省空间，因为仅使用必要空间。
VARCAHR需要使用1或2个额外字节记录字符串的长度（列的最大长度小于或者等于255字节，使用1字节，否则使用2个字节）
（另外mysql5.0以上，varchar(n),n指的时字符，无论是数字还是字母还是汉字都可以存放n个，每个汉字使用三个字节，
所以也通常说mysql的utf8不是真正的utf8,正常utf8存储汉字需要4个字节，可以使用后续版本的utf8mb4来代替utf8）
VARCHAR节省了存储空间，所以对性能也有帮助，但是由于是变长的，在UPDATE时可能要使行变成更长，那么则需要做额外的
工作。
这种情况下，不同的存储引擎处理方式不同，例如;MyISAM会将行拆成不同的片段存储，InnoDB则需要分裂页来使行可以放进页内。
下面这些情况适合使用VARCHAR:
	字符串的最大长度比平均长度大的多；
    列的更新很少，所以碎片不是问题；
    使用了像UTF-8这样复杂的字符集，每个字符使用不同的字节进行存储。
    
CHAR:
CHAR类型是定长的：MYSQL总是根据定义的字符串长度分配足够的空间。
储存char值时，会删除所有的末尾空格。
适合存储很短的的字符串，或者所有值都接近一个同一个长度。
例如：比较适合存储密码的MD5值，因为这是一个定长的值。
对于经常变更的数据，CHAR也比VARCHAR更好，因为定长的CAHR类型不容易产生碎片。
对于非常短的列，CHAR比VARCHAR在空间存储上也更有效率。

与CHAR和VARCHAR类似的类型还有BINARY和VARBINARY,他们存储的是二进制字符串。
二进制字符串更常规字符串非常相似，但是二进制字符串存储的是字节码而不是字符，
当希望存储二进制数据，并且希望MYSQL使用字节码而不是字符比较，可以选择这些类型。
二进制比较不仅仅表现在大小写敏感上，MYSQL比较BINARY字符串时，每次按一个字节，
并且根据该字节的数值进行比较，因此，二进制比较比字符比较简单的多，也更快。

使用VARCHAR(5)和VARCHAR(200)存储’hello‘的空间开销是一样的，那么使用更短的列有什么优势么？

更长的列会消耗更多的内存，因为MYSQL通常会分配固定大小的内存块来保存内部值，尤其是使用内存临时表进行排序或者操作时会特别糟糕。

BLOB和TEXT类型

BLOB和TEXT都是为了存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。
与其他类型不同，MYSQL会把每个BLOB和TEXT的值当作一个独立的对象处理。存储引擎在存储时会做特殊处理。

MYSQL无法为整个BLOB和TEXT列进行索引

使用枚举ENUM代替字符串类型

Mysql在存储枚举时非常紧凑，会根据列表值得数量压缩到一到两个字节中，且Mysql会把每个枚举值保存为整数。

日期和时间类型

Mysql可以使用许多类型来保存日期和时间值，例如YEAR和DATE。

DATETIME

能保存大范围得值，从1001年到9999年，精度为秒。把日期和时间封装到格式为YYYYMMDDHHMMSS得整数中，与时区无关。
使用8个字节得存储空间。

TIMESTAMP

保存了从1970年1月1日依赖得秒数（和UNIX时间戳相同），使用4个字节得存储空间。只能表示从1970到2038年。
TIMESTAMP显示值依赖于时区（mysql服务器、操作系统、客户端连接都有时区设置）
TIMESTAMP列默认为NOT NULL，若插入或者更新时没有显示指定值，TIMESTAMP字段得值自动更新为当前时间。
timestamp的特性随MYSQL版本有很大的区别（所以上述也不是绝对的，很多特性在5.6后被取消 了），
另外mysql5.6下不允许一个表出现两个timestamp列

位数据类型

MYsql有少数几种存储类型使用紧凑的位存储数据。所有这些位数据，不管底层存储格式和处理方式如何，从技术上来说都是字符串类型。

BIT、SET (慎用)

选择标识符

为标识列选择数据类型非常重要:标识列通常会出现在关联操作中（在其他表中作为外键出现），标识别经常作为查询条件出现。
尽量保证标识列出现的所有地方类型包括长度完全一致。
在满足值的范围需求，并且预留未来增长空间的前提下，应该选择最小的数据类型。

整数类型：整数通常是标识列最好的选择，快而且可以使用AUTO_INCREMENT
字符串类型：尽量避免，消耗空间且比数字类型慢。
谨慎使用完全随机的字符串（MD5(),SHA1(),UUID()），这些值会任意分布在很大空间内，导致INSERT以及SELECT变得很慢。
（但对一些很多写得特别大得表，随机字符串可以帮助消除热点）
插入值会随机写到索引得不同位置，所以INSERT语句会很忙，会导致页分裂、磁盘随机访问以及对于聚簇存储引擎产生得索引碎片。
SELECT语句很慢是因为逻辑上相邻的行会分布在磁盘和内存的不同位置。
随机值导致缓存对所有类型的查询语句效果都很差，因为使得缓存赖以工作得访问局部性原理失效。

特殊类型数据

使用无符号整数存储IPV4地址，MYSQL提供INET_ATON()和INET_NTOA()函数来转换IP地址（字符串<->整数）

MYSQL shema设计中得陷阱

太长的列

MYSQL的存储引擎API工作时需要在服务层和存储引擎之间通过缓冲格式拷贝数据，然后在服务层将缓冲内容解码成各个列。
从行缓冲中将编码过的列转成行数据结构的操作代价是非常高的。
MyISAM的定长结构实际上与服务器层的行结构匹配，所以不需要转换，而MyISAM的变长结构和InnoDB的行结构则总是需要转换。
转换的代价依赖于列的数量。

太多的关联

MYSQL限制每个关联操作最多只能有61张表，但单个查询最好在12表内做关联。

错误|过度使用枚举

这种模式设计非常混乱，而且新增枚举值需要ALTER TABLE

范式和反范式

范式：数据库中，每个事实数据会出现并且只会出现一次

反范式：数据库中，信息是冗余的，同一个事实数据会储存在多个地方。

反范式示例：

范式化后示例：（第二范式）

范式的优缺点

优点：
范式化的更新操作通常比反范式快，因为往往只需更新一条记录或者更新更少的记录。
范式化的表通常会更小，可以更好的放在内存里，所以执行操作会更快。
很少有多余的数据意味着检索数据项时更少使用distinct或者group by 语句。

缺点：
通常需要关联。
原本在一张表中同列的索引，拆分到多个表后意味着需要创建更多的索引。

反范式得优点和缺点

优点：
反范式化得schema所有数据在一张表中，避免了很多关联查询。
不需要关联表，在没有使用索引的情况下，全表扫面也是顺序IO(更存储引擎有关：大部分引擎在全表扫面都是顺序IO)，而
关联查询就会很可能出现随机IO（跟数据大小有关？数据小会放入内存？）。
可以使用更有效的索引策略。

缺点：
维护成本高，同样的数据可能要在多个表中去维护或者同一个数据出现在多行数据中

混用范式与反范式

实际中，通常要混合两种范式来得到更好的效果。

缓存表和汇总表

术语"缓存表"、"汇总表"并没有标准的含义
缓存表存储那些可以从shcema获取但获取速度比较慢的数据
汇总表通常保存其他表的group by聚合数据
以此来提高查询效率，但往往会牺牲一些实时性

实例：计时器表:(记录一个网站的点击次数)
可以设计一个表，只有一行数据，cnt,每次点击，cnt++
但对于该行数据，任何想要更新这一行数据的事务都需要获取锁，
也就是只能串行执行，并发性能差
优化为，初始化一百条数据，任何可以任意更新这一百条的cnt值
最后我们得到的点击量为这一百个cnt的和，大大提高了并发性能

加快 ALTER TABLE操作的速度

MYSQL的ALETR TABLE操作的性能对大表来说是个大问题。
Mysql执行大部分修改表结构的操作都是用新的结构创建一个空表，
然后从旧表中查出所有数据插入到新表中，最后删除旧表。

并不是所有操作都会引起表重建。
例如：两种方法可以改变或者删除一个列的默认值，例如修改电影的默认租赁期限，从三天到五天
第一种：（慢）
ALTER TABLE FILM MODIFY COLUMN RENTAL_DURATION NOT NULL DEDAULT 5
SHOW STATUS显示这个语句做了1000次读和1000次插入操作，换句话说，它拷贝了整张表到一个新表。
列的默认值实际存储在表的.frm文件中，所以可以直接修改这个文件，然而Mysql还没有采用这种优化方法，
所有的MODIFY COLUMN 都会导致表重建。
第二种：（快）
ALTER TABLE film alter column rental_duration set default  5
这个语句会直接修改.frm文件而不涉及表数据，这个操作是非常快的

创建高性能的索引

索引（在MYSQL中也叫做键key）是存储引擎用于快速找到记录的一种存储结构。

索引基础

索引类型

Mysql中索引是在存储引擎层实现的而不是服务层，所以没有统一的索引标准。

B-Tree 索引

没有指明类型，多半说的是B-TREE索引，使用B-Tree数据结构来存储数据。
所有的值都是按顺序存储的，每一个叶子节点到根的距离相同。
下图大概反映了InnoDB索引是如何工作的。

假如有如下表：
Create table people(
	last_name varchar(50),
    first_name varchar(50),
    dob date date,
    gender enum('m', 'f')
    key(last_name, first_name, dob)
)
下图显示了该索引如何组织数据存储：

索引对多个值进行排序时依据CREATE TABLE语句中定义索引时列的顺序。
该索引对如下类型的查询有效：

全值匹配：和索引的所有列进行匹配，例如查找姓名为Cuba Allen、出生于1960-01-01的人

匹配最左前缀：只匹配索引的第一列，即last_name， 例如查找last_name=Allen的人

匹配列前缀：按last_name模糊匹配，例如查找last_name以A开头的人

匹配范围值：用于查找last_name在Allen和Barrmore之间的人

精确匹配某一列并范围匹配另一列：查找last_name为Allen并且first_name以K开头的人

只访问索引的查询：即覆盖索引，要查询的数据包含在了索引数据中（但要符合左匹配原则，
select last_name, fisrt_name from people where last_name='x' and fisrt_name='x' 符合
select first_name, dob from people where fisrt_name='' and dob='' 不符合
select last_name, fisrt_name from people where last_name like '%x' and fisrt_name='x'
不符合）
左匹配泛指：
1、where条件中索引列必须包含建表时定义的第一个索引即最左边索引
2、where条件中使用模糊匹配like时，必须左边精确右边模糊，如A%符合但%A不符合

总而言之：要想使得查询使用索引，查询条件必须符合索引树的组织排序规则，
复合索引中，索引树的组织排序规则定义自第一个索引列，对应左匹配的第一个项
字符串模糊匹配like，字符串排序是自左到右依次比较字符串各个字符来判断字符串之间的大小关系的，
若不知道字符串的左侧字符是无法定位数据在索引树的位置的，对应左匹配的第二项
复合索引中，若跳过中间的索引字段使用第一个索引列和第三个索引列不使用第二个索引列，
会导致第三个索引列无法生效，因为索引查找是基于索引字段数据的有序性的，而在复合索引中，
除第一个索引是全局有序，其他索引是基于上一个索引有序的（？？？待确认）

因为索引树种的节点是有序的，所以除了按值查找外，索引还可以用于查询中的order by操作

另外注意：尽量将范围查询的列放在索引的最后列，如该索引：（name, country, age） 
age经常在查询条件中作为范围查询出现，因为查询中只能使用索引的最左前缀，直到遇到第一个范围查询
当然也可以将范围查询以in代替。

哈希索引

基于哈希表实现，只有精确匹配所有列的查询才有效，Mysql中只有Menory引擎显示支持哈希索引。

有时候在需要给字符串长度较长的字段添加索引时，可以采用hash索引的思路（伪hash）例如url
即添加一列为数据列的hash值（整型）url_hash,然后在该列创建索引，然后载查询的时候
select * from t where url_hash = hash(url_val) and url = user_val
hash()代表计算hash的方法可以自己实现，注意一定要在条件中加上原值的比较，即url=url_val,
防止在出现hash冲突（不同的url值计算出了相同的hash值）时得到错误的值

空间索引

MYISAM支持的索引类型，用作地理数据存储。
MYSQL的支持并不完善，开源关系数据库系统中对GIS的接解决方案做的比较好是PostgreSQL的PostGIS

全文索引

是一种特殊类型的索引，查找的是文本中的关键词，而不是直接比较索引的值。

索引的优点

1、大大减少了服务器需要扫描的数据量
2、帮助避免排序和临时表
3、可以将随机I/O变为顺序I/O

高性能索引的策略

独立的列

独立的列指的是索引列不能是表达式的一部分，也不能是函数的参数
错误示范：
select id from t where f1+1=5
select id from t where to_days(f1)-to(f2)>1

前缀索引和索引选择性

在对长度较长的列创建索引的时候，一种方式可以用前边提过的计算hash值，利用hash值创建索引，
另一种方式就是根据字段的前n个字符去创建索引。
通过 select count(distinct left(col_name, n))/count(*) from t 计算一个合适的n值（越接近1说明越接近原值的区分度，但要评分长度，长度不能过大）
前缀索引可以使得索引更小、更快，但也有缺点：
因为前缀索引中存储的不是字段的真实值而是字段的一部分
所以无法基于该索引做ORDER BY 和GROUP BY,也无法做覆盖扫描

多列索引

索引合并：
在Mysql5.0以及更新的版本中，mysql采用了索引合并的优化策略，类似于同时使用两个单独的索引然后将结果合并
例如：select * from t where f1=1 or f2=2 （t表在f1、f2两个字段上都建立了索引）
查询时mysql会使用这两个单列的索引进行扫描，并对结果进行合并。
类似于select * from t where f1=1 union select * from t where f2=2 

索引合并策略有时候是一种优化的结果，但实际上更多时候说明了表上的索引建的糟糕：
1）当出现服务器对多个索引做相交操作的时（通常有多个AND条件），通常意味着需要一个包含所有相关列的多列索引而不是多个单列索引
2）当服务器需要对多个索引做联合操作时（通常由多个OR条件），通常需要消耗大量CPU和内存资源在算法的缓存、排序和合并操作上
   特别当其中有些索引选择性不高，需要合并扫描返回大量的数据的时候
3）更重要的是，优化器不会把这些计算到查询成本中，优化器只关心随机页面的读取。这会使查询的成本被低估，导致该查询计划还不如走全表扫描
   这样做不但会消耗更多的CPU和内存资源，还可能影响查询的并发性。
   通常来说，将查询改成UNION的方式往往更好。

选择合适的索引顺序：
在创建的多列的联合索引的时，通常需要考虑索引的列的顺序，这对最后的执行效率有着非常大的影响，
在多列的B-Tree索引中，索引列的顺序意味着索引首先按最左边的进行排序，其次是第二列，等等。
所以，索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的ORDER BY、GROUP BY 和 DISTINCT等字句的查询要求。
那么如何顺序呢，通常需要考虑两点：
1）选择性高的通常放在前边（where条件中出现频率高的）
2）列值的分布（也就是区分度，区分度越高越适合，像性别这种字段即使使用频率再高，区别度太低，不适合）

聚簇索引

聚簇索引是一种数据存储方式，而不是一种单独的索引类型。
以InnoDB的聚簇索引来说明：以主键列建立索引，并在叶子节点上储存了全部的数据行。

优点：
1）可以把相关数据保存在一起
2) 数据访问更快，聚簇索引将索引和数据保存在同一个B-Tree中，因此从聚簇索引中获取数据通常比非聚簇索引中查找要快
3）使用覆盖索引扫描的查询可以直接使用叶节点的主键值

缺点：
1）聚簇索引最大限度的提高了I/O密集型应用的性能，但如果数据全部放在内存中，
   则访问的顺序就没那么重要了，聚簇索引也就没什么优势了。
2）插入速度严重依赖插入顺序，按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式。
   但如果不是按照主键顺序加载顺序，那么加载完后最好使用OPTIMIZE TABLE命令重新组织一下表
3）更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置。
4）基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂。
   当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，
   这就是一次页分裂操作，页分裂会导致表占用更多的磁盘。
5）聚簇索引可能导致全表扫描变慢，尤其时行比较稀疏，或者由于页分裂导致数据存储不连续的时候。
6）二级索引（非聚簇索引）可能比想象中更大，因为在二级索引的叶子节点包含了引用行的主键列。
7）二级索引需要两次索引查找，通过二级索引找到主键列然后根据聚簇索引

覆盖索引

包含所有要查询字段的值的索引称为覆盖索引。

好处：
1)索引条目通常远小于数据行的大小，所以如果只需要读取索引，那么mysql就会极大的减少数据访问量
2)索引按列指存储（至少单个页内如此），所以对于I/O密集型的范围查询比随机从磁盘读取每一行数据的I/O少得多
3)对于InnoDB的聚簇索引，索引的叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，
  则可以避免对主键索引的二次查询。
  
注意：并不是所有索引都可以成为覆盖索引，覆盖索引必须要存储索引列的值，而哈希索引、空间索引、和全文索引
     都不存储索引列的值。

若使用了覆盖索引，那么在explain的执行结果中，Extra会输出：Using index

延迟关联：在涉及多个表join查询时，通过先子查询后关联的方式，延迟对列的访问，
         使子查询使用覆盖索引等比较高效的检索方式而提升整体的查询效率。

使用索引扫描来做排序

MYSQL有两种方式可以生成有序的结果：通过排序操作或者按索引顺序扫描，
如果EXPLAIN出来的type列的值为index,说明MYSQL使用了索引扫描来做排序

扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接着的下一条历史。
但如果索引不能覆盖所有列，则不得部扫描一条记录就要回表查询一次对应行，基本上都是随机IO，
因此按索引读取的速度比顺序扫描全表速度慢。

使用索引扫描排序的条件：
索引列的顺序和order by字句顺序完全一致，并且所有列的排序方向（倒叙和正序）一致

索引和锁

INNODB只有在访问行时才会对其加锁，而索引能减少InnoDB访问的行数，从而减少锁的数量。
前提是：只有当InniDB在存储层能够过滤调所有的不需要的行时才有效。
5.1后，Innodb可以在服务器端过滤掉行时就释放锁，早期版本中，只有在提交事务后释放锁。   

Innodb在二级索引上使用共享锁，在主键索引使用排他锁。（？）

索引使用的其他技巧（索引案例学习）

支持多种过滤条件

在为查询场景很多的表设计索引时，尽量可以做到复用索引而不是建立太多的索引。
有时候也可以将一些区别度不高的列放于索引列的左侧，然后在查询中即使用不到该列也可以用In全部枚举出来
例如：索引（sex, ...，name）
sex在很多查询中都需要用到，即使区别度不高我们也可以将其作为索引列，同时在查询中用不到时通过
sex in ('','') 来绕过，使我们的查询可以依旧满足最左匹配原则。
但也要注意不能滥用，假如查询中过多的使用in同样会影响性能，mysql优化器会将in查询转变为各种组合进行查询，
若组合太多可能也会导致优化器放弃使用索引.
例如 where a in (1, 2 ,3) and b in (1, 2, 3) and c in (1, 2, 3) 则会出现3*3*3=27种组合

将区分度不高的字段作为索引列通常有两种情景：
1、在大量的查询中出现
2、为了使用覆盖索引

避免多个范围条件

在一个查询种若同时出现多个范围查询必然会导致索引无法充分利用，这个时候可以考虑是否可以将其中的一部分范围查询
改为多个等值查询，也就是in

优化排序

表 user_score(id, sex, name, score) 索引test_index(sex, score)
在对一个匹配结果存在上百万行的查询进行排序时，若不能充分利用索引将会是非常慢的。
select id, name, sex, score from user_score where sex = 'M' order by score limit 500000,10
以上sql在表数据量为80万左右时执行用时为7.383秒
查看执行计划：

根据key:test_idx,extra:Using index condition 我们可以得知，查询利用索引test_key检索出了sex=M的

使用延迟关联的方式利用覆盖索引将上述查询优化为：
select s.id, name, sex, score from user_score s 
inner join (select id from user_score where sex='M' order by score limit 500000,10)t 
on s.id=t.id
优化后执行用时1.030秒
查看执行计划

优化后的sql语句究竟为什么会变快呢，单纯看执行计划可能并不能直观的得到结论。

主要是Sending data花费的时间

疑问

疑问1：mysql能否读取到尚未刷盘的数据,若不能一个事务读取到另一个事务中提交的数据延迟多久（MySQL 写入&查询完整流程）

疑问2：行锁锁在索引上面？未使用索引的情况？间隙锁？（待确认）

疑问3：MVCC怎样做到高并发，MVCC下什么情况下才会上锁？

疑问4：mysql热备份？

疑问5：什么场景下适合用MyISAM引擎

疑问6：列可为NULL对索引有什么影响？

疑问7: 索引相关

索引什么情况下会增加深度索引查询逻辑复合索引的查询逻辑

查询的各部分操作由哪一部分来执行，例如：select查询，哪些是由存储引擎处理的，哪些由服务器层处理

特别的：innodb行锁是由哪一部分来做的实现 innodb的上锁时机与解锁时机

猜测：在涉及需要对行上锁的情况下，innodb在服务层中对存储引擎返回的全部数据上锁。

5.1后，Innodb可以在服务器端过滤掉行时就释放锁，早期版本中，只有在提交事务后释放锁。

Innodb在二级索引上使用共享锁，在主键索引使用排他锁。（？）

mysql执行顺序与过程： www.cnblogs.com/wyq178/p/11…

索引下推ICP

存储层使用索引进行数据过滤的一种优化方式。索引中包含被检索字段，存储引擎会对此进行过滤，然后将过滤后的记录再回表查询数据，最后再传递到服务层前提是查询能利用上索引

例如表t(a, b, c, d) index(a, b, c) select * from t where a='x' and b like '%x%' and c like '%x%'

若没有ICP，该查询只能利用索引的最左匹配字段a,回表获取全部字段后到服务层进行过滤。
利用ICP后,根据a='x'定位到记录行后，再根据索引列b,c过滤，过滤后得到的记录回表查询得到a,b,c,d全部字段，再返回给服务层

减少了服务层与存储引擎的交互次数，较少IO次数，提高查询语句性能。

优化点

为字段定义合适的长度，特别为定长类型（如 char），影响每页能储存的key的数量，进而影响索引的高度，影响检索效率

高性能Mysql-阅读笔记