MySQL性能优化

一、MySQL性能优化

1.1、优化介绍

在进行优化讲解之前，先请大家记住**不要听信你看到的关于优化的“绝对真理”，而应该是在实际的业务场景下通过测试来验证你关于执行计划以及响应时间的假设。**本课程只是给大家提供一些优化方面的方向和思路，而具体业务场景的不同，使用的MySQL服务版本不同，都会使得优化方案的制定也不同。

1.1.1、mysql优化的结果

好的结果

• sql语句运行变快，数据服务器变得速度快

不好的结果：

• 数据库服务器变得不稳定，容易宕机

1.1.2、mysql优化的风险

有可能造成数据丢失，有可能造成服务器不稳定，可能宕机，一定要提前备份表结构和数据，防止丢失。

1.1.3、数据库优化纬度

• 硬件：
  服务器性能，网卡、cpu、内存、硬盘都是性能的决定因素。
• 数据库结构：
  可以从业务角度出发，考虑对业务数据量大的数据库，进行分库，分表
• sql语句和索引：
  在mysql中有慢查询日志，这个日志中记录了所有运行中的执行慢的sql语句，默认执行时间 超过5秒钟的sql语句就认为是执行慢的sql，找到执行慢的sql语句再使用mysql的Explain执行计划进行分析，sql语句哪里慢，然后再根据慢的技术点，进行针对性优化。
  在编写sql语句的时候，可以参照sql优化文档，对执行比较慢的sql语句进行调整优化。
• 索引优化
  索引是为了加快查询速度，主要是提高查询的命中率，避免全表扫描

1.2、MySQL介绍

MySQL 是一款安全、跨平台、高效的，并与 PHP、Java 等主流编程语言紧密结合的数据库系统。该数据库系统是由瑞典的 MySQL AB 公司开发、发布并支持，由 MySQL 的初始开发人员 David Axmark 和 Michael Monty Widenius 于 1995 年建立的。MySQL 的象征符号是一只名为 Sakila 的海豚，代表着 MySQL 数据库的速度、能力、精确和优秀本质。

目前 MySQL 被广泛地应用在 Internet 上的中小型网站中。由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，使得很多公司都采用 MySQL 数据库以降低成本。

MySQL 数据库可以称得上是目前运行速度最快的 SQL 语言数据库之一。除了具有许多其他数据库所不具备的功能外，MySQL 数据库还是一种完全免费的产品，用户可以直接通过网络下载 MySQL 数据库，而不必支付任何费用。

我们将这里进行一个较为全面的分析，让大家了解到MySQL的性能到底与哪些地方有关，以便于让大家寻找出其性能问题的根本原因，而尽可能清楚的知道该如何去优化自己的数据库。

二、sql语句优化

2.1、 MySQL查询流程

如何进行sql优化呢， 首先需要知道，sql优化其实主要是解决查询的优化问题，所以先从数据库的查询开始入手，查询的执行路径：
1、客户端将查询发送到服务器；
2、服务器检查查询缓存，如果找到了，就从缓存中返回结果，否则进行下一步。
3、服务器解析，预处理。
4、查询优化器优化查询
5、生成执行计划，执行引擎调用存储引擎API执行查询
6、服务器将结果发送回客户端。

2.2、sql语句优化（重点）

特点
成本最低，效果最好

优化思路
1、开启mysql慢查询日志，慢查询日志默认认为查询超过5秒的sql语句为执行慢的sql，会记录到慢查询日志中，这个时间可以自定义。
2、使用myslq的执行计划explain，分析执行慢的sql语句，看看哪里慢。
3、根据sql语句优化规则文档，针对sql语句执行慢的部分，按照sql语句优化规则文档进行优化。

2.3、索引优化（重点）

特点
字段加上索引后，查询会变快，增删改会变慢。
查询多，增删改少的地方加索引

使用场景
数据量大的表，查询非常多，经常作为查询条件的字段上，加索引

2.3.1、索引的类型

1、 主键索引 PRIMARY KEY
它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引。

PRIMARY KEY (`id`)

2、唯一索引 UNIQUE
唯一索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。可以在创建表的时候指定，也可以修改表结构。

UNIQUE KEY `num` (`number`) USING BTREE

3、 普通索引 INDEX
这是最基本的索引，它没有任何限制。可以在创建表的时候指定，也可以修改表结构

KEY `num` (`number`) USING BTREE

4、组合索引 INDEX
索引分单列索引和组合索引(联合索引)。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。

KEY `num` (`number`,`name`) USING BTREE

注意，组合索引前面索引必须要先使用，后面的索引才能使用。
5、全文索引 FULLTEXT
全文索引（也称全文检索）是目前搜索引擎使用的一种关键技术。它能够利用分词技术等多种算法智能分析出文本文字中关键字词的频率及重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。

2.4、索引的存储结构

2.4.1、BTree索引

在前面的例子中我们看见有USING BTREE，这个是什么呢？这个就是MySQL所使用的索引方案，MySQL中普遍使用B+Tree做索引，也就是BTREE。

BTree占用存储空间比较大，查询速度比较快，标准的用空间换速度

使用的场景：

• 全值匹配的查询，例如根据订单号查询 order_sn=‘98764322119900’
• 联合索引时会遵循最左前缀匹配的原则,即最左优先
• 匹配列前缀查询，例如：order_sno like ‘1234%’
• 匹配范围值的查找，例如：order_sno > ‘5678’
• 只访问索引的查询

2.4.2、哈希索引

Hash索引在MySQL中使用的并不是很多，目前主要是Memory存储引擎使用，在Memory存储引擎中将Hash索引作为默认的索引类型。所谓Hash索引，实际上就是通过一定的Hash算法，将需要索引的键值进行Hash运算，然后将得到的Hash值存入一个Hash表中。然后每次需要检索的时候，都会将检索条件进行相同算法的Hash运算，然后再和Hash表中的Hash值进行比较并得出相应的信息。

占用存储空间小，查询速度快，但是不是所有的地方都能用，支持的功能也比较少，也就是只有在精确查询，或者大于等于，小于等于这样的查询中可以使用，其它地方不可以使用。

特点：

• Hash索引仅仅只能满足“=”,“IN”和“<=>”查询，不能使用范围查询；
• Hash索引无法被利用来避免数据的排序操作；
• Hash索引不能利用部分索引键查询；
• Hash索引在任何时候都不能避免表扫描；
• Hash索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高；

2.4.3、Full-text全文索引（鸡肋）

Full-text索引也就是我们常说的全文索引，MySQL中仅有MyISAM和InnoDB存储引擎支持。在mysql早版本不支持，现在新版本支持。

相当于全文检索的原理，但是mysql不适合存储大文本数据，所以一般这个索引类型不用，
如果有大文本数据，可以放入ES中，mongodb中等，不直接存储在mysql文本中，msyql存储大文本数据，查询和传输效率会受很大影响。

对于文本的大对象，或者较大的CHAR类型的数据，如果使用普通索引，那么匹配文本前几个字符还是可行的，但是想要匹配文本中间的几个单词，那么就要使用LIKE %word%来匹配，这样需要很长的时间来处理，响应时间会大大增加，这种情况，就可使用时FULLTEXT索引了，在生成Full-text索引时，会为文本生成一份单词的清单，在索引时根据这个单词的清单来索引。

注意：

• 对于较大的数据集，把数据添加到一个没有Full-text索引的表，然后添加Full-text索引的速度比把数据添加到一个已经有Full-text索引的表快。
• 针对较大的数据，生成全文索引非常的消耗时间和空间。
• 5.6版本前的MySQL自带的全文索引只能用于MyISAM存储引擎，如果是其它数据引擎，那么全文索引不会生效。5.6版本和之后InnoDB存储引擎开始支持全文索引。
• 在MySQL中，全文索引支队英文有用，目前对中文还不支持。5.7版本之后通过使用ngram插件开始支持中文。
• 在MySQL中，如果检索的字符串太短则无法检索得到预期的结果，检索的字符串长度至少为4字节。

三、 存储优化

3.1、InnoDB存储引擎

特点：
redolog binlog

• InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。相比较MyISAM存储引擎，InnoDB写的处理效率差一点并且会占用更多的磁盘空间保留数据和索引。
• 提供了对数据库事务ACID（原子性Atomicity、一致性Consistency、隔离性Isolation、持久性Durability）的支持，实现了SQL标准的四种隔离级别。
• 设计目标就是处理大容量的数据库系统，MySQL运行时InnoDB会在内存中建立缓冲池，用于缓冲数据和索引。
• 执行“select count(*) from table”语句时需要扫描全表，因为使用innodb引擎的表不会保存表的具体行数，所以需要扫描整个表才能计算多少行。
• InnoDB引擎是行锁，粒度更小，所以写操作不会锁定全表，在并发较高时，使用InnoDB会提升效率。即存在大量UPDATE/INSERT操作时，效率较高。
• InnoDB清空数据量大的表时，是非常缓慢，这是因为InnoDB必须处理表中的每一行，根据InnoDB的事务设计原则，首先需要把“删除动作”写入“事务日志”，然后写入实际的表。所以，清空大表的时候，最好直接drop table然后重建。即InnoDB一行一行删除，不会重建表

使用场景：

• 经常UPDETE/INSERT的表，使用处理多并发的写请求
• 支持事务，必选InnoDB。
• 可以从灾难中恢复（日志+事务回滚）
• 外键约束、列属性AUTO_INCREMENT支持

3.2、MyISAM存储引擎

特点：

• MyISAM不支持事务，不支持外键，SELECT/INSERT为主的应用可以使用该引擎。
• 每个MyISAM在存储成3个文件，扩展名分别是：
  　　1) frm：存储表定义（表结构等信息）
  　　2) MYD(MYData)，存储数据
  　　3) MYI(MYIndex)，存储索引
• 不同MyISAM表的索引文件和数据文件可以放置到不同的路径下。
• MyISAM类型的表提供修复的工具，可以用CHECK TABLE语句来检查MyISAM表健康，并用REPAIR TABLE语句修复一个损坏的MyISAM表。
• 在MySQL5.6以前，只有MyISAM支持Full-text全文索引

使用场景：

• 经常SELECT/INSERT的表，插入不频繁，查询非常频繁
• 不支持事务
• 做很多count 的计算。

四、数据库结构优化（重点）

4.1、表结构优化

4.1.1、垂直切分

使用场景
字段较多的情况下使用
解决问题
如果单表字段较多，比如几十个字段，那么一次查询返回的数据将会很多，而几十个字段内容不可能在同一个页面展示出来，一般都会分几个tab页面展示，这样一次性查询过多数据，查询速度慢，而查询出来的结果又不加以使用，比较浪费，所以可以按照页面徐亚哟展示的内容，将一张表切分成多个表
多个表关系是1：1：1，每张表里面的字段不同，里面的数据不同。

4.1.2、水平切分

使用场景
mysql单表数据量大于500万条，那么查询速度将会成指数级降低，所以这个时候就需要进行水平分表，提高查询命中率

解决问题
如果mysql单表数据量大于500万条，数据量越多，查询速度越慢，原因是在大量数据查询的时候，命中率太低造成的，所以需要水平分表解决
将一张表水平的切割成多张表，每张表他们的字段个数一样，字段个数一样，字段类型一样，但是里面的数据不一样
水平分表可以使用按照规则切分，比如订单数据，一般都是查询近一周，一个月，三个月的的订单，对于几年前的数据，大多数人不关心，所以可以按照订单的创建时间进行水平分表。
水平分表还可以按照hash算法进行分表，使用一致性hash算法，优点是表中数据分布均匀。

4.2、库结构优化

如果mysql服务器硬盘吞吐率，也可以说是速度不够用，那么可以按照业务，将业务上有关联的几个表，分成一个库，这样分出来的多个库，可以分别部署到不同的数据库服务器上，这样就相当于根据业务使用不同的数据库服务器，不同的服务器使用的硬盘不一样，也就相当于提升了硬盘的吞吐率。

4.3、集群优化

读写分离方案
使用场景
读远远大于写, 高并发读取

集群做法
一台主机master, 多台备机slave,主机只允许写入, 不允许人为读取, 备机只允许读取, 不允许认为写入操作，我们可以向主机中写入数据, 主机会将数据自动同步到多台备机中，如果我们需要查询数据, 可以从多台备机中查询结果, 由于查询的服务器是多台, 所以提升了读取可以抗住的并发量。

缺点:
数据主和备之间存在一定时间的数据不一致情况.

五、总结-面试回答

如果面试官问会不会数据库优化怎么回答
回答: 会

5.1、优化思路

数据库优化有很多, 我是按照, sql语句优化, 索引优化, 数据库表结构优化等思路完成

5.2、sql语句优化

首先要通过慢查询日志判断出来查询慢的sql是哪个, 然后根据执行计划分析哪里慢, 最后根据优化规则文档进行优化sql语句

5.3、索引优化

是看哪个表, 哪个字段, 查询多, 数据量大, 可以考虑加索引. 加索引的类型大概率加BTree结构索引. 因为BTree索引索引占用存储空间大, 但是功能使用比较广泛. 目前硬盘存储空间不太值钱, 所以可以首先考虑BTree. 索引类型主键索引不需要考虑, 因为主键索引是创建表的时候, 数据库默认给主键添加的, 至于唯一索引, 普通索引, 组合索引会根据业务进行添加.

5.4、表结构索引优化

再就是看单表数据量, 表中的字段个数, 考虑分库分表, 字段多垂直分表,数据量大于单表500万条, 考虑水平分表, 水平分表有特殊业务可以按照业务分表, 比如时间规则, 如果没有业务规则按照hash分表. 水平分表可以解决数据量多, 查询命中率的问题, 如果是硬盘磁盘读取效率不够, 可以考虑按照业务规则分库.分库后就是将不同业务的库放到不同的服务器上, 可以提高硬盘的吞吐率.

• 如果读取和写入的吞吐率还达不到要求, 可以做数据库集群.
• 如果是读多写少, 考虑读写分离, 如果都是高并发写入, 考虑对等集群.
• 数据库集群可以使用mycat, corbar等后端集群中间件
• 也可以使用shardingJDBC来当传统的JDBC使用, 作为客户端集群工具.