史上最全的MySQL优化手册

MySQL数据库优化大全

（注：文档参考高性能MySQL，SQL手册，官方文档

[提前声明]
文章由作者:张耀峰 结合自己生产中的使用经验整理,最终形成简单易懂的文章
写作不易,转载请注明,谢谢!
大数据代码案例地址: https://github.com/Mydreamandreality/sparkResearch

---

大纲

1. MySQL中基础架构的优化
2. MySQL中的并发控制（锁）
3. MySQL中数据类型的优化
4. MySQL中索引的优化
5. MySQL中查询的优化
6. 可扩展的MySQL

优化指标

1. 如何确认服务器是否达到了性能最佳的状态
2. 为什么这个语句执行的不够快
3. 某些情况下某些语句会有卡死，停顿的情况

MySQL基本架构

连接器

我们要进行查询，首先肯定是要去连接数据库，这个时候我们操作的就是连接器
连接器负责跟客户端建立连接 获取权限 维持和管理连接
连接的时候会经过TCP握手 然后进行身份验证
连接成功之后 连接是处于空闲状态

查询缓存

MySQL缓存机制
MySQL查询缓存是MySQL中比较独特的一个缓存区域，用来缓存特定Query的整个结果集信息，且共享给所有客户端

为了提高完全相同的Query语句的响应速度，MySQL Server会对查询语句进行Hash计算后，把得到的hash值与Query查询的结果集对应存放在Query Cache中

当MySQL Server打开Query Cache之后，MySQL Server会对接收到的每一个SELECT 语句通过特定的Hash算法计算该Query的Hash值，然后通过该hashi值到Query Cache中去匹配

如果没有匹配，将这个hash值存放在一个hash链表中，并将Query的结果集存放到cache中，存放hash值链表的每个hash节点存放了相应Quey结果集在cache中的地址，以及该query所涉及到一些table相关信息；

如果通过hash值匹配到了一样的Query，则直接将cache中相应的Query结果集返回给客户端

• 缓存失效
  如果表更改了，那么使用这个表的所有缓存查询将不再有效，查询缓存中值相关条目被清空。这里的更改指的是表中任何数据或是结构发生改变，包括INSERT、UPDATE、 DELETE、TRUNCATE、ALTER TABLE、DROP TABLE或DROP DATABASE等

• 内存管理
  MySQL Query Cache 使用内存池技术，自己管理内存释放和分配，而不是通过操作系统。内存池使用的基本单位是变长的block, 用来存储类型、大小、数据等信息；一个result set的cache通过链表把这些block串起来。block最短长度为query_cache_min_res_unit

• 使用
  其实不建议在生产中使用MySQL Cache

查询语句的hash计算和hash查找带来的资源消耗。如果将query_cache_type设置为1（也就是ON），那么MySQL会对每条接收到的SELECT类型的查询进行hash计算，然后查找这个查询的缓存结果是否存在。虽然hash计算和查找的效率已经足够高了，一条查询语句所带来的开销可以忽略，但一旦涉及到高并发，有成千上万条查询语句时，hash计算和查找所带来的开销就必须重视了

Query Cache的失效问题。如果表的变更比较频繁，则会造成Query Cache的失效率非常高。表的变更不仅仅指表中的数据发生变化，还包括表结构或者索引的任何变化。查询语句不同，但查询结果相同的查询都会被缓存，这样便会造成内存资源的过度消耗。查询语句的字符大小写、空格或者注释的不同，Query Cache都会认为是不同的查询（因为他们的hash值会不同）

相关系统变量设置不合理会造成大量的内存碎片，这样便会导致Query Cache频繁清理内存

开发中不应该使用MySQL Cache 建议手动关闭
使用缓存中间件来代替MySQL Cache

分析器

其处理过程：词法分析，语法分析，语义分析

优化器

经过分析器后，MySQL知道SQL是做什么操作的，优化器的作用是，当一个表存在多个索引的时，决定使用哪个索引；或者一个语句存在多表关联（join）时，决定各个表的连接顺序

执行器

到了这一步，MySQL才会开始验证用户是否有查询权限，有才开始执行，没有则返回提示没有权限，理论上这一步很快，除非设置了过多的权限

MySQL中的并发控制

此处的并发控制指的是服务器与存储引擎两个层面的控制

场景：一张表，一条数据

1. 同时写入

   1. 产生后果：数据损坏，两条数据交叉的附在表中
   2. 解决：操作数据时对数据上锁
   3. 影响：任意一个时刻，只有一个进程可以修改这条数据，不支持并发处理

2. 同时读：无任何影响，因为读不修改数据，但此时我在读取该数据，你在删除该数据

   1. 产生后果：不确定，读取可能会错误退出

解决以上问题的方法：并发控制：

1. 共享锁(读锁)
2. 排它锁(写锁)

锁的概念：

1. 读锁是共享的，互相不阻塞，多客户可以在同一时刻同时读取同一资源，互不干扰
2. 写锁是排它的，一个写锁会阻塞其他的写锁和读锁，这是出于安全策略的考虑，这样才能确保一定时间内，只有一个用户能执行写入，并防止其他用户读取正在写入的同一资源

注：在Mysql中，每时每刻都发生锁定，当某个用户在修改一部分数据时，MySQL会通过锁定防止其他用户读取同一数据，大多数时候，MySQL锁的内部管理都是透明的

锁粒度

提高共享资源并发性的方式就是让锁定对象更优选择性，尽量只锁定需要修改的数据，而不是所有资源，任何时候，给定的资源中，锁定的数据量越少，系统的并发程度越高

注：加锁也需要消耗资源，获得锁，检查锁状态，释放等，都会增加系统开销，如果系统花费大量的时间来管理锁，而不是存取数据，那么系统数据可能会受到影响

所谓的锁策略，就是在锁的开销和数据的安全性之间寻求平衡，这种平衡当然会影响到性能

MySQL提供多种选择，不同的存储引擎都可以实现自己的所策略和锁粒度

MySQL中两个重要的锁策略：

1. 表锁

   1. 表锁是开销最小的策略，它会锁定整张表，当用户操作时，需要先获得写锁，这回阻塞其他用户对表的所有读写操作
   2. 写锁的优先级比读锁更高

2. 行级锁

   1. 行级锁可以最大程度的支持并发处理，它只会锁定当前操作的这一条数据，同事也会带来最大的锁开销

注：锁只在存储引擎层实现，MySQL服务器层没有实现，并且完全不了解存储引擎中锁实现

事务

注：这些是数据库的通用基础

事务是一组原子性的SQL查询，独立的工作单元

如果能成功的执行工作单元内的全部语句，那么就执行，否则的话，如果有任何一条语句因为崩溃或者其它问题无法执行，那么工作单元内的所有语句都不会执行

简单来说，要么全部执行成功，要么全部执行失败

事务ACID
A：atomicity 原子性
C：consistency 一致性
I：isolation 隔离性
D：durability 持久性

1. 原子性：

   1. 事务被视为一个不可分割的最小工作单元，事务中的所有操作，要么全部成功执行，要么全部失败回滚

2. 一致性：

   1. 数据库从一个一致性的状态转换到另外一个一致性的状态

3. 隔离性：

   1. 通常来说，一个事务所做的修改在最终提交之前，其它事务是不可见的，（隔离级别这块要着重说一下）

4. 持久性：

   1. 事务提交后，修改会永久保存到数据库中

隔离级别（并发事务）

查看系统当前隔离级别：select @@global.tx_isolation
设置系统当前隔离级别：set global transaction isolation level repeatable read

什么是隔离级别：正常来讲，MySQL中的一次操作过程我们可以定义为一次事务，如果多个线程同时操作MySQL中的数据（同一条，或者同一个范围内的）就叫并发事务，对并发事务进行操作哦粒度的控制就是隔离级别

在SQL的标准中定义了四种隔离级别,每一种级别都规定了一个事务中所做的修改，哪些在事务内和事务间是可见的，哪些是不可见的，较低级别的隔离通常可以执行更高的并发，系统的开销也更低

注：每种存储引擎实现的隔离级别不一定相同

说白了隔离级别的核心就是不同的锁粒度，配合锁粒度来理解

1. READ UNCOMMITTED （未提交读）（读未锁 写排它锁）
   1. 在Read UnCommitTed级别， 事务中的修改，即使没有提交，对其他事务也都是可见的，其它事务可以读取未提交的数据，这就叫脏读，这个级别会导致很多的问题，而且性能也没有比其他的好太多，所以实际应用中一般很少用

可能产生的疑问：uncommitted不是有排它锁？并且是在事务完成后再去释放的，为什么其它线程可以读取到未提交的数据，这个不符合排它锁的特性

浅析：uncommitted的操作是对事务内的每一条更新语句对应的行记录加上写锁来操作，而不是把一个事务整体来加锁，所以它只能保证写的排它性

2. READ COMMITED（提交读/不可重复读）
   1. 一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的，但是会导致幻读，比如事务A删除数据1，但是此时没有提交，事务B查询数据1，然后要修改数据1，此时事务A删除数据，事务B修改数据发现报错了，造成幻读现象

可能产生的疑问：既然不是对事务整体加锁，如何保证在事务提交前其它事务不可见

浅析：使用MVCC技术（一种非阻塞的读操作）MVCC的实现，是通过保存数据在某个时间点的快照来实现的。即为：不管需要执行多长时间，每个事务看到的数据都是一致的

注：MVCC是引擎实现的 不同的引擎实现不同 MVCC只会在RC 和 RR两个隔离级别下工作

3. REPEATABLE READ（可重复读）（MySQL默认级别）

   1. 该级别保证了同一个事务中多次读取同样的记录的结果是一只的，但是理论上讲，可重复读隔离级别还是无法解决另一个幻读问题，当前事务在读取某个范围内的记录，另一个事务在该范围内插入了新的记录，之前的事务再次读取该范围的记录时，会产生幻读

4. SERIALIZABLE（可串行化）

   1. 最高的隔离级别，通过强制事务串行执行，会在读取的每一行数据上都加锁，可能导致大量的超时和锁争用问题，一般不会用这种级别，除非你一定要保证数据的一致性，并且可以接受没有并发的情况，才会考虑一下这个级别

死锁

死锁是指两个或者多个事务在同一资源上互相占用，请求锁定对方占用的资源，从而导致恶性循环的现象，当多个事务试图以不同的顺序锁定资源时，就可能会产生死锁

案例：

事务A：

update ... where id =1
update ... where id =2

事务B：

update ... where id =2
update ... where id =1

如果凑巧，两条事务都执行了第一条更新语句，同时锁定当前数据，接着两个事务都尝试去执行第二条语句，却发现被锁定，然后两个事务都等待对方释放锁，同时又持有对方需要的锁，陷入死循环，此时除非有外部因素介入才可能解除死锁

为了解决这种问题，数据库实现了各种死锁检测和死锁超时机制

解决方案：
当查询的时间达到锁等待超时的设定后放弃锁请求

InnDB处理死锁的方法是：将持有最少行级排它锁的事务进行回滚，就能避免死锁

注：死锁发生后，只有部分或者完全回滚其中一个事务，才能打破死锁，对于事务性的系统，这是无法避免的

事务日志

事务日志可以提交事务的效率，使用事务日志，在修改表的数据时，只需要修改内存拷贝，再把修改行为记录到持久在硬盘上的事务日志中(binlog)，而不用每次豆浆修改的数据本身持久到磁盘，事务日志采用的是追加的方式，因此写入日志的操作是磁盘上的一小块区域内的顺序IO，不是随机IP需要在磁盘的多个地方移动磁头，所以采用事务日志的方式相对来说要快得多，事务日志持久之后，内存中被修改的数据在后台可以慢慢的刷回到磁盘，（预写式日志），修改数据需要写两次磁盘

如果数据的修改意见记录到日志并持久化，但数据本身还没有写回磁盘，这个时候系统崩溃了，存储引擎在重启时，能够自动恢复这部分修改的数据，具体恢复方式视存储引擎而定

MySQL中的事务

innodb是MySQL中的事务性存储引擎

默认情况下MySQL是采用自动提交的模式，就是每一个查询不需要显示的开启事务，可以自行启动或者禁用自动提交

SET AUTOCOMMIT = 1

隐式和显式锁定
Inndb里采用的是两阶段锁定协议，在事务的执行过程中，随时都可以执行锁定，锁只有在commit或者roolback的时候才会被释放

显式锁定可以使用

SELECT * FROM xxx FOR UPDATE

优化点：显式的LOCK table和事务本身相互影响的话，会产生一些玄学的Bug，不仅没有必要(innerdb 本身就有行级锁)，而且某些情况下会非常影响性能，所以尽量避免使用显式锁定，如果有必要可以使用Redis或者zookeeper等实现锁机制

MySQL中的物理设计

MySQL中的三大范式和反范式

第一范式：
确保数据表中每列（字段）的原子性，不可再拆分
第二范式：
在第一范式的基础上更进一步，目标是确保表中的每列都和主键相关
第三范式：
在第二范式的基础上更进一步，目标是确保表中的列都和主键直接相关，而不是间接相关

反范式：
通过增加冗余或重复的数据来提高数据库的读性能，比如中间表中增加一些需要join才能取到的数据，减少join次数，提高读能力

在范式的数据库中，每个数据出现并且只出现一次，相反反范式的数据库中，数据出现并且会出现多次，会有冗余的情况

范式

1. 范式的更新操作比反范式的要快，当数据库按照范式来设计，就只有很少或者没有重复的数据，所以修改的数据会少
2. 通常都需要关联来获取数据，稍微麻烦的语句都至少需要一次或者以上的关联，这也不算缺点，只是范式设计的特性

反范式

1. 大部分数据都在一张表，减少关联
2. 单表可以更好的设置索引策略
3. 增加维护成本

注：良好的设计是高性能的基石，需要考虑的因素有很多，需要综合考虑，不过一般我们的设计都是混着用

数据类型的选择

在MySQL中支持的数据类型太多了，如何选择正确的数据类型对我们应用程序的性能提升是比较重要的，不管什么数据类型，都可以参考几个原则来帮助我们设计

1. 更小的
   一般情况下，应该尽量使用可以存储数据的最小数据类型，因为更小的类型一般都会更快一些，它占用的磁盘，CPU，内存更少，处理时需要的CPU周期也更少
2. 简单的
   一般情况下，尽量使用int来作为字段类型，因为在所有的数据类型中int不管是存储空间还是执行速度方面都是最好的
   例如：如果业务中存储的都是数据的话而且长度不是特别长的话，就可以考虑使用int来进行存储，或者权重之类的，也可以使用int来进行存储
   注：但是不要因为int高效，而有意识的把所有字段都设计成int来处理，最终还是要根据业务的具体需求来设计相应的字段

挑几个常用的具体的讲：

整数类型：

int(5)和int(15)有什么区别

注：没有区别，它只是限制了显示的长度，存储的范围限制还有int的最大限制

有符号和无符号的类型都使用了相同的存储空间，性能也是一样的

浮点数类型：

在涉及到金额的时候如果对精度要求不高的情况下可以优先使用float，其次是使用double来进存储

float占4个字节，double占8个字节，

如果对精度要求比较高的情况下最好使用decimal来存储，但是相应的它的效率没有float和double那么高效，因为额外占用了空间和计算开销

字符串类型：

1. varchar（空间效率）

varchar(100)和varchar(255)有什么区别

注：理论上没区别（只有对字符的长度限制）varchar比定长类型更节省空间，因为它只使用了必要的空间，它虽然设置了长度值，但由于是可变的，所以当存储的长度小于x的时候，它的实际存储不是x，而是字符长度+长度标识（这个一般只占1-2个字节）

正是因为varchar节省了存储空间，但是由于是可变长度，当我们更新的时候，现有的长度可能会变化，所以引擎会有一些额外的工作，比如我们更新的长度变长了，引擎需要分裂页再合并（这个过程是有排他锁的机制存在）

注：虽然varchar(100)和(19999)存储Hello world时空间开销是一样的，但是varchar(19999)会消耗更多的内存，MySQL的内存分配机制，一般会分配固定大小的内存块来保存内部值，所以最好的策略是只分配需要的空间

2. char（时间效率）

char(10)的长度是固定的 如果存储的是HELLO char在存储是会在后面追加空格，并且在检索的时候消除空格

正是由于长度是固定的，所以处理速度是比varchar要快的，但是浪费了存储空间，如果存储的数据不大，速度也有要求的话，可以考虑char类型，否则尽量别用

大字符类型：

1. blob和Text

blob和text都是用来存储大字符的类型，blob是采用二进制存储，text是字符存储

b和t引擎会独立处理这两个类型，当它们的值太大时，引擎会使用独立的存储区域进行存储，值有1-4个字节的指针指向存储区域

在排序的处理上也与其他类型不同，只针对最前面的max_sort_length自己进行排序，如果只需要对前面的更少的字节进行排序，那么可以通过设置max_sort_length参数或者substring(value,length)来截取部分字符串

在实际使用中应该慎用这两个类型，尤其是会创建临时表的情况下，因为如果临时表大小超过max_heap_table_size或者tmp_table_size，就会将临时表存储在磁盘上，进而导致整体速度下降

时间类型：

1. timestamp和datetime

MySQL中时间类型可选择性有很多，year，date…等，但它们都有自己的场景，t和d是两种很相似的日期类型，一般情况下，它们都可以实现同样的功能，但是一些场景下，timestamp做的更好

timestamp和datetime都可以以ANSI标准日期显示

datetime能保存1001-9999年 精度为秒的数据到整数中YYYYMMDDHHMMSS，时间与时区无关，使用8个字节的存储空间

timestamp保存了1970/1/1以来的秒数，它只使用4个字节的存储空间，所以它的时间范围比datetime小，只表示1970-2038年，时间是依赖于时区的

注：一般情况下，可以尽量使用timestamp，因为它的空间效率更高

合理的利用索引

索引优化是对查询性能优化最有效的有段之一，索引能够轻易的将查询性能提升几个量级，一般最优索引的设计都经常意味着这个SQL也要跟着改写

在innodb引擎中，总共有四种索引类型，两种索引方法

索引类型：

• 1、Normal 普通索引
• 2、Unique 唯一索引
• 3、Full Text 全文索引
• 4、SPATIAL 空间索引

索引方法：

• 1、btree索引方法
• 2、hash索引方法（就是K，V的数据结构）

索引类型详解：

• 1、首先说Normal这个索引类型，是最常用的一种索引类型了，也是最基本的一种索引类型，它本身没有任何的限制，你可以针对某个字段设置Normal，也可以做联合索引，在做单条件查询、多条件查询啊，或者其它需要left join操作的时候，MySQL优化器就会自动使用该索引类型，来优化查询速度，那如果我们要知道这个索引到底生效没有，可以使用explain命令来查看SQL执行计划
• 2、Unique这个顾名思义就是唯一索引，唯一索引就是在普通索引的基础上增加了唯一约束，我在百万级数据下测试，和Normal对比没有太大的性能差异，这种索引的应用场景也挺多的，比如你数据有个字段需要索引，而且这个字段的值必须是唯一的，那用Unique索引是比较好的选择
• 3、Full Text全文索引，讲道理，一般需要全文索引的都上Elasticsearch了，MySQL这个里面的全文索引用的确实不多，不过全文索引的基本单位肯定是词，MySQL这里面的分词器不知道有没有es那么强大，总之这种索引的应用场景就是某个字段存的大文本，你就可以使用Full Text索引
• 4、SPATIAL空间索引，这个没用过，因为这个索引在innodb引擎中没法用的，需要在MYISAM引擎中空间数据类型字段上才能使用，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON，MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引，创建空间索引的列，必须将其声明为NOT NULL

索引的优点

索引可以让服务器快速地定位到表的指定位置。但是这并不是索引的唯一作用，根据创建索引的数据结构不同，索引也有一些其他的附加作用

1. 索引大大减少了服务器需要扫描的数据量（避免全表扫描）
2. 索引可以帮助服务器避免排序和临时表（b树按序存储）
3. 索引可以将随机I/O变为顺序I/O

注：索引某些情况下不是最好的解决方案，首先数据量小的情况下有索引和没索引没有区别，如果数据量在TB级别，索引的作用也有可能体现不出来，HBASE的分区策略和元数据meta表可以了解一下

高性能索引策略

不同的场景下索引的策略也不同，下面列几个常用的场景来设计索引策略

1. 合适的索引合并

全时bugfeel平台，扫描任务模块，数据量大的情况下每次加载都需要10s+的时间，检查数据库后发现每个字段都设置了独立的索引，一般在网上查找索引如何设置时，总有把WHERE条件里面的列都建上索引，这样模糊的建议，实际上这个建议是非常错误的，在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的査询性能，MySQL在5.x版本引入了索引合并（多列索引）的策略

注：当查询出现对多个索引相交操作时（AND），那就意味着需要一个所有相关列的多列索引，而不是多个独立的索引（bugfeel扫描任务模块，通过设置正确的索引，查询时间控制在了200ms内）

联合索引的优势：

• 减少开销：建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销
• 覆盖索引：通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作，减少io操作，特别的随机io其实是dba主要的优化策略，实际应用中，覆盖索引是主要的提升性能的优化手段之一
• 效率高：索引列越多，通过索引筛选出的数据越少

2. 合适的索引列顺序（最左前缀匹配的原则）

在mysql建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配

KEY in_col1,col2,col3 on key_test(col1,col2,col3)

SELECT * FROM tablename where col1=x and col2=y and col4=z

以上就是遵循了最左前缀匹配的原则

注：如何选择索引的列顺序有一个基本准则，将选择性最高的列放到索引的最前列，这个在某些场景下可能有用，在不考虑排序和分组时，索引只是优化where条件的查找，这种情况将选择性最高的列放在前面一般来说是很好的，能最快的过滤出需要的行

只要在联合索引中包含的字段，字段顺序是任意的，优化器会纠正

where col1 and col2
where col2 and col1

索引的性能并不只是依赖于所有索引列的选择性，也和查询条件的具体值有关（结果分布）

select * from tablename where col1 = x and col2 = y

正常来讲，我们的索引设计都是 ( col1,col2 )
但是结果的分布是
col1命中30条
col2命中10000条
这个时候按照之前的方式来优化，可能对其他一些条件值的查询不公平，服务器的整体性能可能会降低

总：所以在索引优化时，要考虑到where的排序，分组和范围条件等等情况，某一个特殊的情况就可能会对性能造成比较大的影响

3. 索引和锁

索引另一个能提高性能的点在于可以让查询锁定更少的行，如果我们的查询从不访问哪些不需要的行，那么引擎就会锁定更少的行，这两个方面一结合，性能就会更好

查询语句的优化

1. 请求多余的数据

MySQL客户端和服务器之间的通信协议是半双工协议，所以在任何一个时刻，要么是由服务器向客户端发送数据，要么是由客户端向服务器发送数据，这两个动作不能同时发生

客户端用一个单独的数据包将查询传给服务器，参数max_allowed_packet就是用来配置服务端接收的包大小，只要客户端发送了请求，能做的事情就只是等待结果了

服务端是相反的，一般服务器响应给用户的数据通常很多，由多个数据包组成，当服务器响应客户端请求时，客户端必须完整地接收整个返回结果，而不能简单地只取前面几条结果，然后让服务器停止发送数据

查询不需要的记录会给MySQL服务器带来额外的负担，并增加网络开销，消耗应用服务器的CPU和内存资源

注：典型的案例，判断数据是否存在，不需要使用count(*)来校验，直接limit 1 来校验即可

2. 多表关联返回全部列

如果不是真的需要返回全部的列。SELECT * 尽量避免，使用*，会让优化器无法完成索引覆盖扫描这类优化，还会带来额外的IO，内存和cpu消耗，在高并发的情况下，这个性能的差异才会有体现

3. 重复的数据

不断的重置执行相同的查询，然后每次都返回相同的数据

注：这个不算查询语句层的优化）比较好的方案就是缓存，将不会经常变动的数据存入到缓存中，减少数据库的压力，并且为了避免缓存击穿和热点缓存的问题，可以提前指定相应策略

4. 切分处理

分而治之，将大查询切为小查询，每个查询的功能完全一样，只完成一小部分，每次只返回一小部分查询结果，把原本一次性的压力分散到一个很长的
时间段中，减少服务器的压力

注：

rows_count = xxx
do {
    sql.execute("delete from xxx where xxx limit 1000")
} while rows_count > 0

5. count函数优化

count()可以统计某个列值的数量，也可以统计行数，在使用count(*)时，通配符并不会像很多地方说的扩展成所有的列，官方的描述中，*会直接忽略所有的列，去直接统计所有的行数

如果我们要统计集的行数，用count(*)定义清晰，性能也会好

6. 优化子查询和关联查询

尽可能的使用关联查询代替（因为一些特定的情况下，子查询的优化器会抽风，导致全表扫描的操作），最好在sql执行前做一波explain分析

关联查询要确保on或者using子句中的列上有索引，最好能确保order by 或者group by 中的表达式只涉及到一个表中的列，MySQL才有可能使用索引来优化这个过程

可扩展的MySQL

注：水平扩展的一种方案

当MySQL的规模越来越大的时候，单台服务器实例迟早会有性能瓶颈，所以才需要水平扩展（集群）

集群的优势：

• 高可用性：故障检测及迁移，多节点备份
• 可伸缩性：新增数据库节点便利，方便扩容
• 负载均衡：切换某服务访问某节点，分摊单个节点的数据库压力

MySQL主从复制

MySQL主从复制是官方提供的一种解决方案

核心：
主从复制是通过重放binlog实现主库数据的异步复制。即当主库执行了一条sql命令，那么在从库同样的执行一遍，从而达到主从复制的效果。在这个过程中，master对数据的写操作记入二进制日志文件中(binlog)，生成一个 log dump线程，用来给从库的 i/o线程传binlog。而从库的i/o线程去请求主库的binlog，并将得到的binlog日志写到中继日志（relaylog）中，从库的sql线程，会读取relaylog文件中的日志，并解析成具体操作，通过主从的操作一致，而达到最终数据一致

特性：
MySQL主从的结构，可以实现数据的多点备份，但是没有故障自动转移和负载均衡，对比单台服务器实例，主从的优势

• 后端服务读连接从库，写操作主库，实现读写分离的作用，从库我们可以配置多个，做到负载均衡的效果

优势：

• 主从复制是MySQL官方的，不需要中间件的支撑
• 数据丢失可以从binlog恢复
• 配置简单

劣势：

• 从库和主库的数据有一定的延迟，对主从之间的网络延迟要求高，最好在用一个内网
• 单master节点，只要挂了就不能提供写服务，不能做到HA

MySQL Fabirc

在MySQL主从的基础上增加了故障监测与转移，自动数据分片功能，不过还是一主多从的结构，当主节点挂了，会在从节点中选举一个master节点

注：这个性能太差，几乎没有人用

MySQL cluster

mysql集群（MySQL Cluster）也是mysql官方提供的

MySQL Cluster是多主多从结构

优势：

• MySQL官方的，不需要其它的插件
• 高可用性，自动切分数据，跨节点冗余数据
• 可伸缩性优秀
• 高性能的负载均衡
• 多主节点，不存在单点故障

注：由于架构模式和原理太复杂，维护成本太高，且节点与节点之间通信太多，对带宽要求太高，所以也几乎没有什么人用

第三方集群方案

MMM

MMM（Master Replication Manager for MySQL）
是一套支持双主故障切换和双主日常管理的脚本程序，双主多从结构，这是Google的开源项目，使用Perl语言来对MySQL Replication做扩展，提供一套支持双主故障切换和双主日常管理的脚本程序，主要用来监控mysql主主复制并做失败转移，高可用性，扩展性好

注：MMM的双主实际上只有一台主在工作，并且实时同步到另一台主几点，当master1挂掉后自动切换到master2节点

MHA

MHA(Master High Availability Manager and Toolsfor MySQL)目前在Mysql高可用方面是一个相对成熟的解决方案

它是日本的一位MySQL专家采用Perl语言编写的一个脚本管理工具，该工具仅适用于MySQLReplication 环境，目的在于维持Master主库的高可用性。

MHA是基于标准的MySQL复制(异步/半同步)。MHA是由管理节点(MHA Manager)和数据节点(MHA Node)两部分组成。MHA Manager可以单独部署在一台独立机器,也可以部署在一台slave上

读写分离解决方案

读写分离可以从两个方面解决，一个是客户端另一个是中间件

客户端解决方案（应用层）：

TDDL、 Sharding-Jdbc (常用shardding-jdbc)

通过这种方式来实现读写分离优势不多，并且缺点很致命

首先说优点，由我们自己来控制程序逻辑，不需要额外的中间件来维护，并且理论上支持任意的数据库，数据源也方便管理，但额外也引入了高昂的开发成本，代码入侵性，主要是做不到动态的数据源，并且这个过程运维是没有办法介入的，所以在大型项目开发中尽量少用

中间件解决方案（代理层）：

mysql proxy、mycat、altas (常用mycat)

通过中间件的方式避免了代码的入侵性，并且可以实现动态的数据源，但是也额外引入了维护中间件的工作，由于中间件就是一层代理，所以性能方面也会有一定的损耗，但如果是多从方案，其实影响不大，性能能得到保证，但是中间件proxy也有可能单点故障，所以需要做成HA的架构