MySQL优化

什么是优化

合理安排资源、调整系统参数使MySQL运行更快、更节省资源。
优化是多方面的，包括查询、表设计、服务器等。
原则：减少系统瓶颈，减少资源占用，增加系统的反应速度。

MySQL架构

• 1、MySql流程
• 2、查询优化
• 3、索引的使用
• 4、存储优化
• 5、数据库结构优化
• 6、硬件优化
• 7、MySql缓存
• 8、服务器参数
• 

查询执行流程

1. 连接
   1.1客户端发起一条Query请求，监听客户端的‘连接管理模块’接收请求
   1.2将请求转发到‘连接进/线程模块’
   1.3调用‘用户模块’来进行授权检查
   1.4通过检查后，‘连接进/线程模块’从‘线程连接池’中取出空闲的被缓存的连接线程和客户端请求对接，如果失败则创建一个新的连接请求

2. 处理
   2.1先查询缓存，检查Query语句是否完全匹配，
   2.2查询缓存失败则转交给‘命令解析器’
   2.3再转交给对应的模块处理
   2.4如果是SELECT查询还会经由‘查询优化器’做大量的优化，生成执行计划
   2.5模块收到请求后，通过‘访问控制模块’检查所连接的用户是否有访问目标表和目标字段的权限
   2.6有则调用‘表管理模块’，先是查看table cache中是否存在，有则直接对应的表和获取锁，否则重新打开表文件
   2.8根据表的meta数据，获取表的存储引擎类型等信息，通过接口调用对应的存储引擎处理
   2.9上述过程中产生数据变化的时候，若打开日志功能，则会记录到相应二进制日志文件中

3. 结果
   3.1Query请求完成后，将结果集返回给‘连接进/线程模块’
   3.2返回的也可以是相应的状态标识，如成功或失败等
   3.3‘连接进/线程模块’进行后续的清理工作，并继续等待请求或断开与客户端的连接

4.查询优化

在优化MySQL时，通常需要对数据库进行分析。常见的分析手段有慢查询日志，EXPLAIN 分析查询，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。

5.1.EXPLAIN

在MySQL中可以使用EXPLAIN查看SQL执行计划，用法：EXPLAIN SELECT * FROM products
5.1.1.id
SELECT识别符。这是SELECT查询序列号。这个不重要。
5.1.2.select_type
表示SELECT语句的类型。
例如：
1、SIMPLE
表示简单查询，其中不包含连接查询和子查询。
2、PRIMARY
表示主查询，或者是最外面的查询语句。
UNION
表示连接查询的第2个或后面的查询语句。
5.1.3.table
表示查询的表。
5.1.4.type
表示表的连接类型。
以下的连接类型的顺序是从最佳类型到最差类型：
1、system
表仅有一行，这是const类型的特列，平时不会出现，这个也可以忽略不计。
2、const
数据表最多只有一个匹配行，因为只匹配一行数据，所以很快，常用于
3、eq_ref
mysql手册是这样说的:“对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY”。eq_ref可以用于使用=比较带索引的列。
4、ref
查询条件索引既不是UNIQUE也不是PRIMARY KEY的情况。ref可用于=或<或>操作符的带索引的列。
5、ref_or_null
该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。
6、index_merge
该联接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素。
7、unique_subquery
该类型替换了下面形式的IN子查询的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr)
unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。
8、index_subquery
该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)
9、range
只检索给定范围的行,使用一个索引来选择行。
10、index
该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。
11、ALL
对于每个来自于先前的表的行组合,进行完整的表扫描。（性能最差）5.1.5.possible_keys
指出MySQL能使用哪个索引在该表中找到行。
如果该列为NULL，说明没有使用索引，可以对该列创建索引来提高性能。
5.1.6.possible_keys
指出MySQL能使用哪个索引在该表中找到行。
如果该列为NULL，说明没有使用索引，可以对该列创建索引来提高性能。
5.1.7.key_len
显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL
注意：key_len是确定了MySQL将实际使用的索引长度。
5.1.8.ref
显示使用哪个列或常数与key一起从表中选择行。
5.1.9.rows
显示MySQL认为它执行查询时必须检查的行数。

6.索引使用

6.1.1.MySQL索引
6.1.1.1.B-Tree索引
一般来说，MySQL中的B-Tree索引的物理文件大多都是以二叉树的结构来存储的，也就是所有实际需要的数据都存放于树的叶子节点，而且到任何一个叶子节点的最短路径的长度都是完全相同的。

6.1.1.2.Hash索引
Hash索引在MySQL中使用的并不是很多，目前主要是Memory存储引擎使用，而且在Memory存储引擎中将Hash索引作为默认的索引类型。所谓Hash索引，实际上就是通过一定的Hash算法，将需要索引的键值进行Hash运算，然后将得到的Hash值存入一个Hash表中。然后每次需要检索的时候，都会将检索条件进行相同算法的Hash运算，然后再和Hash表中的Hash值进行比较并得出相应的信息。
Hash索引仅仅只能满足“=”,“IN”和“<=>”查询，不能使用范围查询；
Hash索引无法被利用来避免数据的排序操作；
Hash索引不能利用部分索引键查询；
Hash索引在任何时候都不能避免表扫面；
Hash索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高；
6.1.1.3.Full-text索引
Full-text索引也就是我们常说的全文索引，目前在MySQL中仅有MyISAM存储引擎支持，而且也并不是所有的数据类型都支持全文索引。目前来说，仅有CHAR，VARCHAR和TEXT这三种数据类型的列可以建Full-text索引。

6.1.2.创建索引
是否需要创建索引，几点原则：
较频繁的作为查询条件的字段应该创建索引；
唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件；
更新非常频繁的字段不适合创建索引；
不会出现在WHERE子句中的字段不该创建索引；

索引能够极大的提高数据检索效率，也能够改善排序分组操作的性能，但是我们不能忽略的一个问题就是索引是完全独立于基础数据之外的一部分数据，更新数据会带来的IO量和调整索引所致的计算量的资源消耗。
6.1.3.使用索引
6.1.3.1.使用联合索引的查询
MySQL可以为多个字段创建索引，一个索引可以包括16个字段。对于联合索引，只有查询条件中使用了这些字段中第一个字段时，索引才会生效。

6.1.3.2.使用OR关键字的查询
查询语句的查询条件中只有OR关键字，且OR前后的两个条件中的列都是索引时，索引才会生效，否则，索引不生效。

7.存储优化

存储数据时，影响存储速度的主要是索引、唯一性校验、一次存储的数据条数等。

存储数据的优化，不同的存储引擎优化手段不一样，在MySQL中常用的存储引擎有，MyISAM和InnoDB，两者的区别：

7.1.1.MyISAM存储引擎
MyISAM存储引擎是一种非事务性的引擎，提供高速存储和检索，以及全文搜索能力，适合数据仓库等查询频繁的应用。

每一个表都被存放为三个以表名命名的物理文件。有存放表结构定义信息的.frm文件，还有存放了表的数据.MYD文件和存放索引数据的.MYI文件。

7.1.2.Innodb 存储引擎
Innodb 存储引擎是事务安全的, 因此如果需要一个事务安全的存储引擎,建议使用它。如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑应该使用InnoDB表。
InnoDB 给 MySQL 提供了具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。InnoDB 提供了行锁(locking on row level)，提供与 Oracle 类型一致的不加锁读取(non-locking read in SELECTs)。这些特性均提高了多用户并发操作的性能表现。

在InnoDB表中不需要扩大锁定(lock escalation)，因为 InnoDB 的列锁定(row level locks)适宜非常小的空间。InnoDB 是 MySQL 上提供外键约束(FOREIGN KEY constraints)的表引擎。

InnoDB 的设计目标是处理大容量数据库系统，它的 CPU 利用率是其它基于磁盘的关系数据库引擎所不能比的。在技术上，InnoDB 是一套放在 MySQL 后台的完整数据库系统，InnoDB 在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。

InnoDB 把数据和索引存放在表空间里，可能包含多个文件，这与MyISAM不一样。InnoDB 表的大小只受限于操作系统的文件大小，一般为 2 GB。InnoDB所有的表都保存在同一个数据文件 ibdata1 中(也可能是多个文件，或者是独立的表空间文件),相对来说比较不好备份。备份的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump。

7.2.MyISAM和Innodb的区别

InnoDB和MyISAM是许多人在使用MySQL时最常用的两个表类型，这两个表类型各有优劣，视具体应用而定。基本的差别为：MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持。MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快，但是不提供事务支持，而InnoDB提供事务支持已经外部键等高级数据库功能。

7.2.1.具体实现的差别：
MyISAM是非事务安全型的，而InnoDB是事务安全型的。
MyISAM锁的粒度是表级，而InnoDB支持行级锁定。
MyISAM支持全文类型索引，而InnoDB不支持全文索引。
MyISAM相对简单，所以在效率上要优于InnoDB，小型应用可以考虑使用MyISAM。
MyISAM表是保存成文件的形式，在跨平台的数据转移中使用MyISAM存储会省去不少的麻烦。
InnoDB表比MyISAM表更安全，可以在保证数据不会丢失的情况下，切换非事务表到事务表（alter table tablename type=innodb）。

7.2.2.应用场景
MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择。
InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。

8.数据库结构优化

8.1.优化表结构
尽量将表字段定义为NOT NULL约束，这时由于在MySQL中含有空值的列很难进行查询优化，NULL值会使索引以及索引的统计信息变得很复杂。
对于只包含特定类型的字段，可以使用enum、set 等符合数据类型。
数值型字段的比较比字符串的比较效率高得多，字段类型尽量使用最小、最简单的数据类型。例如P地址可以使用int类型。
尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT，如果非负则加上UNSIGNED
VARCHAR的长度只分配真正需要的空间
尽量使用TIMESTAMP而非DATETIME，
单表不要有太多字段，建议在20以内
合理的加入冗余字段可以提高查询速度。

8.2.表拆分
8.2.1.垂直拆分
垂直拆分按照字段进行拆分，其实就是把组成一行的多个列分开放到不同的表中，这些表具有不同的结构，拆分后的表具有更少的列。例如用户表中的一些字段可能经常访问，可以把这些字段放进一张表里。另外一些不经常使用的信息就可以放进另外一张表里。

插入的时候使用事务，也可以保证两表的数据一致。缺点也很明显，由于拆分出来的两张表存在一对一的关系，需要使用冗余字段，而且需要join操作，我们在使用的时候可以分别取两次，这样的来说既可以避免join操作，又可以提高效率。

8.2.2.水平拆分
水平拆分按照行进行拆分，常见的就是分库分表。以用户表为例，可以取用户ID，然后对ID取10的余数，将用户均匀的分配进这 0-9这10个表中。查找的时候也按照这种规则，又快又方便。

有些表业务关联比较强，那么可以使用按时间划分的。例如每天的数据量很大，需要每天新建一张表。这种业务类型就是需要高速插入，但是对于查询的效率不太关心。表越大，插入数据所需要索引维护的时间也就越长。

8.3.分区
使用分区是大数据处理后的产物。比如系统用户的注册推广等等，会产生海量的日志，当然也可以按照时间水平拆分，建立多张表。但在实际操作中，容易发生忘记切换表导致数据错误。
分区适用于例如日志记录，查询少。一般用于后台的数据报表分析。对于这些数据汇总需求，需要很多日志表去做数据聚合，我们能够容忍1s到2s的延迟，只要数据准确能够满足需求就可以。

MySQL主要支持4种模式的分区：range分区、list预定义列表分区，hash 分区，key键值分区。

8.4.读写分离
大型网站会有大量的并发访问，如果还是传统的数据结构，或者只是单单靠一台服务器扛，如此多的数据库连接操作，数据库必然会崩溃，数据丢失的话，后果更是不堪设想。这时候，我们需要考虑如何减少数据库的联接。

我们发现一般情况对数据库而言都是“读多写少”，也就说对数据库读取数据的压力比较大，这样分析可以采用数据库集群的方案。其中一个是主库，负责写入数据，我们称为写库；其它都是从库，负责读取数据，我们称为读库。这样可以缓解一台服务器的访问压力

8.5.数据库集群
如果访问量非常大，虽然使用读写分离能够缓解压力，但是一旦写操作一台服务器都不能承受了，这个时候我们就需要考虑使用多台服务器实现写操作。

例如可以使用MyCat搭建MySql集群，对ID求3的余数，这样可以把数据分别存放到3台不同的服务器上，由MyCat负责维护集群节点的使用。