导航
MySQL
基本优化
存储引擎的选择
就目前实际需求来说，InnoDB是主流的存储引擎，主要的优化也在InnoDB方面。
MyISAM
MyISAM是传统古老的存储引擎之一，对于以读请求为主的非事务系统来讲，MyISAM确实有着优异的性能，但表级锁、整体读写互斥的架构，无法满足目前的基本并发需求，整体特性归纳如下：
- 不支持事务
- 表级锁定，数据更新时锁定整个表：其锁定机制是表级锁定，这虽然可以让锁定的实现成本很小但是也同时大大降低了其并发性能。
- 读写互相阻塞：不仅会在写入的时候阻塞读取，MyISAM还会在读取的时候阻塞写入，但读本身并不会阻塞另外的读。
- 只会缓存索引：MyISAM可以通过key_buffer_size缓存索引，以大大提高访问性能，减少产品IO，但是这个缓存区只会缓存索引，而不会缓存数据。
- 读取速度较快，占用资源相对少。
- 不支持外键约束，但支持全文索引。
InnoDB
InnoDB与MyISAM存储引擎最大的区别在于4点：缓存机制、事务支持、锁、存储方式，优化也是这从几个方面进行。从MySQL 5.5开始，InnoDB便作为MySQL的默认存储引擎，主要特性如下：
- 支持事务;
- 行级锁定(更新数据时一般指锁定当前行)：通过索引实现、全表扫描忍让时表锁、注意间隙锁的影响;
- 读写阻塞与事务的隔离级别相关；
- 具有非常高的缓存特性(既能缓存索引、也能缓存数据)；
- 这个表和主键以组(Cluster)的方式存储、组成一颗平衡树；
- 所有的辅助索引(secondary indexes)都会保存主键信息；
- 支持分区、表空间类似于oracle 数据库；
- 支持外键约束、不支持全文检索(5.5.5之前的MyISAM支持全文检索、5.5.5之后就不在支持);
- 相对MyISAM而言、对硬件的要求比较高
就目前实际情况来说，如果仅仅是读的需求，MongoDB、Redis、ES等辅助性数据库前赴后继的演进，MyISAM也不是一个好的可选方案之一，以下对MyISAM不再赘述，以InnoDB为存储引擎进行优化说明。
缓存
InnoDB不仅仅缓存索引，同时还缓存实际数据，物理磁盘的访问速度跟内存的访问速度永远不是一个量级，比较内存的价格和磁盘的价格，缓存机制是降本增效的典型。
参数 innodb_buffer_pool_size
该参数是设置用户表和索引的主要缓存空间，对整体性能影响最大，官方和各类分享的优化建议都是设置物理内存的50%~80%，但这只是一个默认理论建议，假设较为平衡的情况(最大500个连接)，其实际内存场景如下：
系统使用预留，1G
线程独享，500个连接 * ( 1MB + 1MB + 1MB + 512KB + 512KB)，2G
sort_buffer_size 1MB
join_buffer_size 1MB
read_buffer_size 1MB
read_rnd_buffer_size 512K
thread_stack 512K
innodb_buffer_pool_size 实际可用量 = 8G - 1G - 2G = 5G
事务
事务日志
数据库通常为了效率原因，当端提交一个事务，数据库响应事务成功时，数据库并没有真正将数据写到磁盘，通常只在内存中完成了修改。
InnoDB事务提交需要写入undo log( 回滚 )、redo log( 前滚 )和真正的数据页，采取日志先行的策略，也就是数据在内存中完成变更后，将事务记录成redo log，顺序IO(LSM算法，与HBase Kafka类似)到数据库落地后响应事务完成，而此时数据仅在内存中完成修改，并没有写入到磁盘。InnoDB事务崩溃或内存数据丢失，也是可以通过redo log恢复。
undo log是实时 顺序写入库(磁盘)，对事务提交前的每一行数据进行备份，如果有事务没有最终commit，而同时有连接读取数据，undo log则进行回滚，保证查询数据一致性。即redo log保证事务的持久性，undo log保证事务的原子性从而实现一致性。
所以控制redo log日志写的策略，也是优化的一个重点。
参数 innodb_flush_log_at_trx_commit
该参数是设置事务提交，redo log从内存写到磁盘的策略
innodb_flush_log_at_trx_commit=1（默认），始终将每次事务的redo log刷fflush到内核的缓存中，并立即调用fsync操作写入磁盘。
牺牲一定的一致性保证并发，可以设为0或2
innodb_flush_log_at_trx_commit=0，每隔一秒再把事务的redo log刷fflush到内核缓存中并fsync写入磁盘。存在丢失1秒数据的可能性。
innodb_flush_log_at_trx_commit=2，始终将每次事务的redo log刷fflush到内核缓存中，但每隔1秒再fsync写入磁盘。
在实践中，如果一致性的要求不是极致，可以将该参数设为2，至多丢失1秒的数据来提高并发性，相比1和0是一种折中选择。同时还可以调整以下参数，根据实际情况优化：
参数innodb_flush_log_at_timeout，控制redo log写入磁盘间隔时间，默认1S。
参数innodb_log_buffer_size，redo log缓存的大小，默认1M，一般不是重度大事务的话，8~16MB足够，数值越大，降低数据库内存缓存写磁盘的次数。
参数innodb_log_file_size，redo log的大小，默认8M，事务频繁的话适当调整到64~128MB，减少数据库checkpoint操作。
查询隔离级别
查询的问题：
- 脏读：一个事务中修改的数据并未提交，另外一个事务查询读到已修改的数据，称之为脏读。
- 不可重复读：事务A中多次查询数据，由于其它事务对查询数据作出修改并完成事务，在事务A中多次查询的数据不一致，称之为不可重复读。
- 幻读：事务A中多次查询指定条件的10行数据，由于其它事务新增了同样条件的一行数据，导致事务A中多次查询结果不一致。
不可重复读与幻读的区别，前者侧重修改/删除，后者侧重插入
InnoDB查询事务隔离级别
读取未提交(Read Uncommitted)，允许脏读，可能读取未提交事务修改的数据，也就是一个未提交的事务，允许它修改的数据被其它事务看到。
读取已提交(Read Committed)，只能读取已提交事务的数据，必须是完成后的事务，才允许其它其它事务看到。
可重复读(Repeated Read)，InnoDB默认级别。在同一个事务内的所有查询的数据，都是与事务开始时保持一致。通过MVCC和next-key lock解决幻读。
串行读(Serializable)，完全串行化的读，每次读都要锁行，且读、写互斥。
MVCC，数据快照技术，使得一个事务中多次查询看到一致的数据。
next-key lock，阻塞特定条件的新数据行插入
在InnoDB中，实际的查询隔离级别和应对问题矩阵如下：
| 脏读 | 不可重复读 | 幻读 | |
|---|---|---|---|
| 读取未提交(Read Uncommitted) | 是 | 是 | 是 |
| 读取已提交(Read Committed) | 否 | 是 | 是 |
| 可重复读(Repeated Read) | 否 | 否 | 是 |
| 串行读(Serializable) | 否 | 否 | 否 |
在InnoDB中，默认是 可重复读(Repeated Read) 隔离级别，如果业务系统中各个模块有一定程度或部分模块能够接受不可重复读或者幻读，优化方面有两种方式：
一，MySQL InnoDB默认级别改为 读取已提交(Read Committed)，应用模块中根据实际情况升级设为可重复读(Repeated Read) 。
二，MySQL InnoDB保持默认 可重复读(Repeated Read) ，但在应用模块中根据实际情况把事务降级设为读取已提交(Read Committed)。推荐
存储
如上图所示，InnoDB的存储结构为：
- page，最小物理单元，不论是数据还是索引，或是存储引擎自身内部的结构，都是以page作为最小物理单元存储，每个Page默认大小16KB，至少2条数据记录。
- extent，由多个连续page组成的存储单元，每个extent有64个page。
- segment，由1~N个extent组成，每个segment只存放同一种数据(即同一张表)。
- tablespace，由1~多个segment组成，是InnoDB种的最大存储单元，独立表空间时，一个tablespace下只有一个segment，共享表空间时，一个tablespace下有多个segment。
参数 innodb_file_per_table= 0表示共享表空间，1表示独立表空间。
独立表空间，在数据存放对应路径下根据数据库名分类，每个分类下都是.frm(表结构)、 .ibd(数据和索引)、 .opt(字符集)，优点是每个表都有自己的独立空间，DropTable自动回收表空间，大量删除后可使用（alter table TableName engine=innodb）回收不用的空间，并且InnoDB允许这些文件使用软链的方式重新定义这些文件的实际位置，在必要的情况下，可以通过软链分别存放到不同的磁盘。
共享表空间，在数据存放路径下会初始ibdata1文件作为共享表空间，当ibdata1满了后自动扩展ibdata2，最后一个文件总是自动扩展的，但是每次扩展，都会使SQL处于等待状态，调整扩展表空间的大小，可以有效减少自动扩展次数。通过参数innodb_autoextend_increment（默认8M）可控制扩展表空间的大小。
由于InnoDB存在log记录相应的事务日志，整体的IO量，相对于只将数据写入磁盘的数据库是倍增的，虽然log是采取顺序写入(LSM算法)，总归还是有IO消耗的，可以将log和data隔开，分别存放于不同的地方降低磁盘的相互争用，若已经做了Raid的磁盘也无需做此优化。
参数innodb_log_group_home_dir 指定log的存放位置。
参数innodb_data_home_dir 指定数据文件的存放位置。
索引
在InnoDB中，采取B+树的结构， 主键索引和数据记录是存储在一起的，主键索引也就是聚簇索引，通过主键索引，可以直接查询到数据记录，也正是这种聚簇索引的机制，InnoDB基于主键查询的效率非常高。聚簇索引查询如下图所示：
采取B+树而不用B树，是因为B树不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少（有些资料也称为扇出），指针少的情况下要保存大量数据，只能增加树的高度，导致IO 操作变多，查询性能变低。
而其它索引（非聚簇索引/二级索引），其数据值是 聚簇索引 ，通过其它非主键索引查询，需要先查到主键索引(聚簇索引)，再根据主键索引去查数据记录。非聚簇索引查询如s上图图所示。
在实践中只用主键作为条件，通常不能满足Query需求，需要建立二级索引（非聚簇索引）提高查询效率，带来的问题便是二级索引的控制，索引越多，占用空间越大，反而性能变慢，写入时也需耗费更多时间建立索引。
通过这些原理得知建立索引是为了提高查找效率，其基本原则：
- 主键（聚簇索引）尽可能的小，比如使用无符号int自增（若0-4294967295可满足需求）。
- Query一定必须用到的字段，一定要建立索引（二级索引/非聚簇索引）。
- 整体索引数量一定要尽可能的少，非大规模、高频率查询用到的字段，尽可能的不要建立索引。
- 建立索引的字段尽可能的是Int/BigInt/DateTime类型，尽可能的避免varchar字段做索引。
- 主键（聚簇索引）尽可能的不要变化。
- 索引建立的顺序，尽可能的匹配Query 条件语句、排序语句的顺序。
- 若必须对char、varchar类型做索引，务必截取到能保证唯一值的长度做索引。
- 若有全文索引需求，使用ES类的非结构化数据库替代。
其它常见
InnoDB内部线程数
参数innodb_thread_concurrency
这是一个比较有争议的参数，官方建议核心数+磁盘数，5.0.8之前默认值为8，后来更改默认值为20，在5.0.19和5.0.20又改为默认值0，5.0.21又改为默认值8，最终在5.7.2中移除，如果是使用较为早期的版本，建议设为0，完全让InnoDB自己决定实际需要创建线程数。
慢查询
根据日志文件检查慢查询的sql，默认文件名hostname-slow.log
使用explain方式
explain select … from … where … order by …
返回结果如下：
| 列 | 说明 |
|---|---|
| id | select sql标识符，如果是子查询或联表，会递增 |
| select_type | 查询的类型，每个select的类型 SIMPLE代表简单，PRIMARY包含子查询，UNION代表UNION查询， DEPENDENT UNION代表第二个语句依赖外部第一个语句， SUBQUERY 子查询中不依赖外部查询 DEPENDENT SUBQUERY 子查询依赖外部查询 DERIVED 基于派生表的查询 UNCACHEABLE SUBQUERY 子查询的结果不能被缓存，必须重新评估外链接的第一行 |
| table | 表名，也可以是自定义的名称，如select id from t1 t，则显示t |
| type | 对表访问类型，依次性能越来越好： ALL，必须遍历全表 index 只遍历Index range 只检索给定范围的行，使用一个索引来选择 ref 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值 eq_ref 类似ref，区别就在使用的索引是唯一索引 const/system 自动转化为常量的优化，如where 后面有主键，MySQL就将该查询转化为一个常量。 NULL 不需要访问表或者索引 |
| possible_keys | 可以使用的索引，如果没有则null |
| key | MySQL决定使用的索引，必然在possible_keys中 |
| key_len | 索引的字节数 |
| ref | 能够与索引匹配的，在条件中的列或常量 |
| rows | 估算结果行数 |
| extra | MySQL执行的详细信息，常见如 Using index代表查询的列本身都是索引，性能好 Using index condition代表能够用索引列查找到，性能较好 Using filesort 代表需要对文件排序，通常没有order by 索引 Using temporary代表建立临时表，如group by 和 order by同在 Using join buffer 需要嵌套循环计算，通常是join的时候没有使用索引作为条件 Using where 代表where过滤了条件，但仍有优化空间，比如尽量使用索引字段过滤 |
数据切分
基本概念
通过特定的策略或者条件，将数据库中的数据分散存放到多个数据库中，以达到分散的负载效果。数据切分根据规则分为两类，一种是将不同业务的表，切分到不同的库，或是将大宽表拆分不同的表，再放到不同的库，属于垂直（纵向）切分；另外一种是根据表行数据的逻辑，将同一个表中的数据按照特定的条件拆分到多台数据库中，属于水平（横向）切分。在实践中，两种切分通常是同时使用。
垂直（纵向）切分
这种切分最大的特点是规则简单，实施也更为方便，尤其是在微服务体系下，服务节点之间本身低耦合，相互影响小，有着清晰的边界。在这种系统中，很容易实现把不同业务模块的表拆分到不同的数据库，如支付系统中，订单表、交易表（渠道）、金融流水（或账户账套流水），分别可以放到订单系统、交易系统、账户系统不同的数据库中。
一般来说，在架构设计上各个模块低耦合，垂直切分更易实施，但是存在强join的业务点，也确实难以切分，如支付系统中商户信息，一般设计上不做大宽表也至少有进件信息、审核信息、开户后的主体信息、结算信息等多个关联表，建议还是通过分布式的微服务架构来避免强关联，不一定非要一次性查询出所有关联信息，如交易流程中用到时再查结算信息等业务流程调整优化的方式，来避免强关联。在实践中满打满算的情况也实属少见，垂直切分是实施最为简单的方式，但如何切分是对架构设计上的一种考验。
水平（横向）切分
水平切分相对于垂直切分来说更为复杂一些，因为要将同一个表中的数据拆分到不同的数据库中，不同的数据库存在着相同的表结构存放一部分数据，即按数据行拆分，拆分规则本身就较为复杂，后期运维工作也会更为复杂。例如，支付系统中订单流水是最为大量的，它包括支付的和未支付或支付超时的，如果从商户的角度讲，就按商户号为规则进行拆分，但如果要统计某天所有微信支付或其它指定的支付方式的订单，显然按支付方式作为切分规则更为合适，在冲突的情况下，如何定性分片规则需要综合考量。
目前主要有以下几种典型的分片规则：
-
按照ID取模，将数据分散到不同的数据库，如按用户ID取模，那么一个用户的数据一定在一个库中。
-
按照日期，如订单的下单月份，将不同月份的订单分散到不同的库中。
-
按照某个特定字段（如区域），hash后取模，分散到不同的数据库中。
实现方式
目前主要有两种方式，一种是基于应用中的多数据源整合，另一种是通过应用与数据库中间代理。
第一种方式，如ShardingSphere-JDBC（sharding-jdbc），需要在每个应用服务模块中配置管理多个数据源，直接访问不同的库中的表，在模块内完成整合。短期内可以迅速满足数据切分的需求，但随着系统不断演变的庞大复杂的时候，这种耦合度较高的方式会降低单个服务模块的适应性，难以调整。
第二种方式，如Mycat或ShardingSphere-Proxy，以中间件代理形式统一管理数据源，虽然短期内的技术成本相对更大一些，但是对于整个系统的演进发展，通过中间代理统一管理数据源实现降低耦合，保证了每个具体服务模块的适应性，便于调整，从而最终实现整个系统易于扩展的目的。
Mycat
经验教训
无论按何种规则切分，尽量根据历史增长或同行类比来预估未来1-2年的增长量，尽可能适当增加分片节点减轻风险，给扩容工作留出余地，因为增加节点必然导致分片命中数据库节点的改变，即使是一致性hash也只是在理想情况能够保证50~75%的命中点不变，迁移数据是无法避免的成本。
并且数据切分后，join的难度不可避免的提高，注意以下几点：
- 能不切分的，就不切分。
- 切分规则，一定要综合考量。
- 尽量通过冗余字段达到join的目的。
- 尽可能避免join操作，拆分业务流程。
