在面试过程中，经常会问到mysql的优化，现在就根据一些资料总结一下mysql优化的方法。mysql优化总共主要分为四大方面：

•       设计方面：存储引擎的选择，字段类型的选择，数据库范式；
•       功能方面：创建索引，开启缓存缓存，分区分表；
•       架构方面：主从复制，读写分离，负载均衡；
•       sql方面：合理sql语句，测试还有经验。
  

     下面我们就从存储引擎、字段类型选择、数据库范式、开启缓存，合理sql语句这些方面来谈谈mysql的优化：
一.存储引擎
要对mysql进行优化，就得对mysql的逻辑架构进行了解，下图是mysql各组件之间的工作架构图：

      MySQL逻辑架构整体分为三层，最上层为客户端层，并非MySQL所独有，诸如：连接处理、授权认证、安全等功能均在这一层处理。
      MySQL大多数核心服务均在中间这一层，包括查询解析、分析、优化、缓存、内置函数(比如：时间、数学、加密等函数)。所有的跨存储引擎的功能也在这一层实现：存储过程、触发器、视图等。
      最下层为存储引擎，其负责MySQL中的数据存储和提取。和Linux下的文件系统类似，每种存储引擎都有其优势和劣势。中间的服务层通过API与存储引擎通信，这些API接口屏蔽了不同存储引擎间的差异。   
      在mysql中存储引擎的主要有InnoDB，MyISAM，Archive，Memory等，目前在市面上主要运用的是前面两种，而存储引擎的选择也有利于提高mysql的性能。
1.InnoDB存储引擎
    MySQL版本5.5以后默认的都是此引擎，MySQL推荐使用的存储引擎。支持事务，行级锁定，外键约束。事务安全型存储引擎。更加注重数据的完整性和安全性。使用innodb引擎时，需要理解独立表空间、共享表空间。
2.  MyISAM存储引擎
    MySQL版本小于 5.5 默认的存储引擎。ISAM：Indexed Sequential Access Method(索引顺序存取方法)的缩写，是一种文件系统，擅长与处理，高速读与写。
3.两种引擎的对比

    那么，两种引擎如何选择呢？
    如果没有特别的需求，使用默认的Innodb即可。
    MyISAM：以读写插入为主的应用程序，比如博客系统、新闻门户网站。
    Innodb：更新（删除）操作频率也高，或者要保证数据的完整性；并发量高，支持事务和外键保证数据完整性。比如OA自动化办公系统。
二、字段类型的选择
        MySQL可以支持大量数据的存取，但是一般来说，数据库中的表越小，执行查询的时间会越少，速度也会更快。所以在对表中字段类型的选择我们应该遵循尽可能小(占用存储空间小)，尽可能定长（占用存储空间固定）和尽可能使用整数的这几个原则。
        举个例子，如果要一个字段记录年龄，那么我们会选择整形，可是MySQL支持五种整形，包括：TINYINT、SMALLINT、MEDIUMINT、INT和BIGINT五种。它们的取值范围是：

  通过分析知道，如果设置一个年龄的字段类型，我们只要选TINYINT就行，而上面各种整数类型可以根据场景来进行设置字段，比如SMALLINT，可以设置端口号，包含在0~65535之间等。
        当然还可以举个例子，就是在定义邮政编码这个字段时，如果将其设置为CHAR(255),显然给数据库增加了不必要的空间，甚至使用VARCHAR这种类型也是多余的，因为CHAR(6)就可以很好的完成任务了。同样的，如果可以的话，我们应该使用MEDIUMINT而不是BIGIN来定义整型字段。
        另外一个提高效率的方法是在可能的情况下，应该尽量把字段设置为NOT NULL，这样在将来执行查询的时候，数据库不用去比较NULL值。
        对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的性能。
三、范式
        数据库查询时最终操作的都是数据库中的表，而建立冗余较小，结构合理的数据库也有助于提高mysql的性能。而建立或者设计数据库时必须遵守的规则，我们称之为范式。建立数据库需要遵循下面三大范式：
       1.原子性
        所谓的原子性，就是数据库表中的所有字段值都是不可分解的原子值。第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式。
        2.消除部分依赖
        第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。
       3.消除传递依赖
        所谓传递函数依赖，指的是如果存在"A-》B-》C"的决定关系，则C传递函数依赖于A。比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。
总结：先满足第一范式，再满足第二范式，才能满足第三范式。
四、开启缓存
        我们总是希望MySQL能够获得更高的查询性能，最好的办法是弄清楚MySQL是如何优化和执行查询的，那么当我们向MySQL发送一个请求的时候，MySQL做了一些什么，通过下面一幅图我们来了解一下：

从中我们可以看出，在解析一个查询语句前，如果查询缓存是打开的，那么MySQL会检查这个查询语句是否命中查询缓存中的数据。如果当前查询恰好命中查询缓存，在检查一次用户权限后直接返回缓存中的结果。这种情况下，查询不会被解析，也不会生成执行计划，更不会执行。
        接下来我们看下开启缓存和不开启缓存之间，使用同一条sql的耗时，先查看一下数据库是否开启缓存：

[SQL] 纯文本查看 复制代码

SHOW VARIABLES LIKE 'query_cache%';

现在没有开启缓存，使用下面语句进行查询

[SQL] 纯文本查看 复制代码

SELECT * FROM tab_route_img WHERE rid=248;

然后开启缓存

[SQL] 纯文本查看 复制代码

SET GLOBAL QUERY_CACHE_TYPE =ON;

如果出现下面的错误，

[HTML] 纯文本查看 复制代码

错误代码： 1651
Query cache is disabled; restart the server with query_cache_type=1 to enable it

则需要修改配置文件,windows下是my.ini，linux下是my.cnf

[XML] 纯文本查看 复制代码

query_cache_type = ON

修改成功之后重启mysql服务，之后再使用同一个语句进行查询，第一次时间是相同的，可是第二次查询的时候，发现时间直接变成零，所以开启缓存对查询的性能是有很大的提高的。
五、合理sql语句
1.尽量避免Select * 命令
        从表中读取越多的数据，查询会变得更慢。它会增加磁盘的操作时间，还是在数据库服务器与web服务器是独立分开的情况下，你将会经历非常漫长的网络延迟。仅仅是因为数据不必要的在服务器之间传输。
2.利用limit 1取得唯一行
        有时要查询一张表时，你要知道需要看一行，你可能去查询一条独特的记录。你可以使用limit 1.来终止数据库引擎继续扫描整个表或者索引,如：

[SQL] 纯文本查看 复制代码

Select * from A where name like ‘%xxx’ limit 1;

这样只要查询符合like ‘%xxx’的记录，那么引擎就不会继续扫描表或者索引了。
3.尽量少排
        排序操作会消耗较多的CPU资源，所以减少排序可以在缓存命中率高等。
4.尽量少OR
        当where子句中存在多个条件以“或”并存的时候，Mysql的优化器并没有很好的解决其执行计划优化 问题，再加上mysql特有的sql与Storage分层架构方式，造成了其性能比较地下，很多时候使用union all或者union(必要的时候)的方式代替“or”会得到更好的效果。
5.尽量用union all 代替union
         union和union all的差异主要是前者需要将两个（或者多个）结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的cpu运算，加大资源消耗及延迟。所以当 我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候，尽量使用union all而不是union.
6.不要在列上进行运算
        如下面:

[SQL] 纯文本查看 复制代码

select * fromusers where YEAR(adddate)<2007;

     将在每个行进行运算，这些导致索引失效进行全表扫描，因此我们可以改成：

[SQL] 纯文本查看 复制代码

Select * from users where adddate<’2007-01-01’;

7.对多表关联的查询，建立视图
        对多表的关联可能会有性能上的问题，我们可以对多表建立视图，这样操作简单话，增加数据安全性，通过视图，用户只能查询和修改指定的数据。且提高表的逻辑独立性，视图可以屏蔽原有表结构变化带来的影响。
8.避免类型转换
        这里所说的“类型转换”是指where子句中出现column字段的类型和传入的参数类型不一致的时候发生的类型转换。人为的上通过转换函数进行转换，直接导致mysql无法使用索引。如果非要转型，应该在传入参数上进行转换。

     在面试过程中，经常会问到mysql的优化，现在就根据一些资料总结一下mysql优化的方法。mysql优化总共主要分为四大方面：

•       设计方面：存储引擎的选择，字段类型的选择，数据库范式；
•       功能方面：创建索引，开启缓存缓存，分区分表；
•       架构方面：主从复制，读写分离，负载均衡；
•       sql方面：合理sql语句，测试还有经验。
  

     下面我们就从存储引擎、字段类型选择、数据库范式、开启缓存，合理sql语句这些方面来谈谈mysql的优化：
一.存储引擎
要对mysql进行优化，就得对mysql的逻辑架构进行了解，下图是mysql各组件之间的工作架构图：

      MySQL逻辑架构整体分为三层，最上层为客户端层，并非MySQL所独有，诸如：连接处理、授权认证、安全等功能均在这一层处理。
      MySQL大多数核心服务均在中间这一层，包括查询解析、分析、优化、缓存、内置函数(比如：时间、数学、加密等函数)。所有的跨存储引擎的功能也在这一层实现：存储过程、触发器、视图等。
      最下层为存储引擎，其负责MySQL中的数据存储和提取。和Linux下的文件系统类似，每种存储引擎都有其优势和劣势。中间的服务层通过API与存储引擎通信，这些API接口屏蔽了不同存储引擎间的差异。   
      在mysql中存储引擎的主要有InnoDB，MyISAM，Archive，Memory等，目前在市面上主要运用的是前面两种，而存储引擎的选择也有利于提高mysql的性能。
1.InnoDB存储引擎
    MySQL版本5.5以后默认的都是此引擎，MySQL推荐使用的存储引擎。支持事务，行级锁定，外键约束。事务安全型存储引擎。更加注重数据的完整性和安全性。使用innodb引擎时，需要理解独立表空间、共享表空间。
2.  MyISAM存储引擎
    MySQL版本小于 5.5 默认的存储引擎。ISAM：Indexed Sequential Access Method(索引顺序存取方法)的缩写，是一种文件系统，擅长与处理，高速读与写。
3.两种引擎的对比

    那么，两种引擎如何选择呢？
    如果没有特别的需求，使用默认的Innodb即可。
    MyISAM：以读写插入为主的应用程序，比如博客系统、新闻门户网站。
    Innodb：更新（删除）操作频率也高，或者要保证数据的完整性；并发量高，支持事务和外键保证数据完整性。比如OA自动化办公系统。
二、字段类型的选择
        MySQL可以支持大量数据的存取，但是一般来说，数据库中的表越小，执行查询的时间会越少，速度也会更快。所以在对表中字段类型的选择我们应该遵循尽可能小(占用存储空间小)，尽可能定长（占用存储空间固定）和尽可能使用整数的这几个原则。
        举个例子，如果要一个字段记录年龄，那么我们会选择整形，可是MySQL支持五种整形，包括：TINYINT、SMALLINT、MEDIUMINT、INT和BIGINT五种。它们的取值范围是：

  通过分析知道，如果设置一个年龄的字段类型，我们只要选TINYINT就行，而上面各种整数类型可以根据场景来进行设置字段，比如SMALLINT，可以设置端口号，包含在0~65535之间等。
        当然还可以举个例子，就是在定义邮政编码这个字段时，如果将其设置为CHAR(255),显然给数据库增加了不必要的空间，甚至使用VARCHAR这种类型也是多余的，因为CHAR(6)就可以很好的完成任务了。同样的，如果可以的话，我们应该使用MEDIUMINT而不是BIGIN来定义整型字段。
        另外一个提高效率的方法是在可能的情况下，应该尽量把字段设置为NOT NULL，这样在将来执行查询的时候，数据库不用去比较NULL值。
        对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的性能。
三、范式
        数据库查询时最终操作的都是数据库中的表，而建立冗余较小，结构合理的数据库也有助于提高mysql的性能。而建立或者设计数据库时必须遵守的规则，我们称之为范式。建立数据库需要遵循下面三大范式：
       1.原子性
        所谓的原子性，就是数据库表中的所有字段值都是不可分解的原子值。第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式。
        2.消除部分依赖
        第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。
       3.消除传递依赖
        所谓传递函数依赖，指的是如果存在"A-》B-》C"的决定关系，则C传递函数依赖于A。比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。
总结：先满足第一范式，再满足第二范式，才能满足第三范式。
四、开启缓存
        我们总是希望MySQL能够获得更高的查询性能，最好的办法是弄清楚MySQL是如何优化和执行查询的，那么当我们向MySQL发送一个请求的时候，MySQL做了一些什么，通过下面一幅图我们来了解一下：

从中我们可以看出，在解析一个查询语句前，如果查询缓存是打开的，那么MySQL会检查这个查询语句是否命中查询缓存中的数据。如果当前查询恰好命中查询缓存，在检查一次用户权限后直接返回缓存中的结果。这种情况下，查询不会被解析，也不会生成执行计划，更不会执行。
        接下来我们看下开启缓存和不开启缓存之间，使用同一条sql的耗时，先查看一下数据库是否开启缓存：

[SQL] 纯文本查看 复制代码

SHOW VARIABLES LIKE 'query_cache%';

现在没有开启缓存，使用下面语句进行查询

[SQL] 纯文本查看 复制代码

SELECT * FROM tab_route_img WHERE rid=248;

然后开启缓存

[SQL] 纯文本查看 复制代码

SET GLOBAL QUERY_CACHE_TYPE =ON;

如果出现下面的错误，

[HTML] 纯文本查看 复制代码

错误代码： 1651
Query cache is disabled; restart the server with query_cache_type=1 to enable it

则需要修改配置文件,windows下是my.ini，linux下是my.cnf

[XML] 纯文本查看 复制代码

query_cache_type = ON

修改成功之后重启mysql服务，之后再使用同一个语句进行查询，第一次时间是相同的，可是第二次查询的时候，发现时间直接变成零，所以开启缓存对查询的性能是有很大的提高的。
五、合理sql语句
1.尽量避免Select * 命令
        从表中读取越多的数据，查询会变得更慢。它会增加磁盘的操作时间，还是在数据库服务器与web服务器是独立分开的情况下，你将会经历非常漫长的网络延迟。仅仅是因为数据不必要的在服务器之间传输。
2.利用limit 1取得唯一行
        有时要查询一张表时，你要知道需要看一行，你可能去查询一条独特的记录。你可以使用limit 1.来终止数据库引擎继续扫描整个表或者索引,如：

[SQL] 纯文本查看 复制代码

Select * from A where name like ‘%xxx’ limit 1;

这样只要查询符合like ‘%xxx’的记录，那么引擎就不会继续扫描表或者索引了。
3.尽量少排
        排序操作会消耗较多的CPU资源，所以减少排序可以在缓存命中率高等。
4.尽量少OR
        当where子句中存在多个条件以“或”并存的时候，Mysql的优化器并没有很好的解决其执行计划优化 问题，再加上mysql特有的sql与Storage分层架构方式，造成了其性能比较地下，很多时候使用union all或者union(必要的时候)的方式代替“or”会得到更好的效果。
5.尽量用union all 代替union
         union和union all的差异主要是前者需要将两个（或者多个）结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的cpu运算，加大资源消耗及延迟。所以当 我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候，尽量使用union all而不是union.
6.不要在列上进行运算
        如下面:

[SQL] 纯文本查看 复制代码

select * fromusers where YEAR(adddate)<2007;

     将在每个行进行运算，这些导致索引失效进行全表扫描，因此我们可以改成：

[SQL] 纯文本查看 复制代码

Select * from users where adddate<’2007-01-01’;

7.对多表关联的查询，建立视图
        对多表的关联可能会有性能上的问题，我们可以对多表建立视图，这样操作简单话，增加数据安全性，通过视图，用户只能查询和修改指定的数据。且提高表的逻辑独立性，视图可以屏蔽原有表结构变化带来的影响。
8.避免类型转换
        这里所说的“类型转换”是指where子句中出现column字段的类型和传入的参数类型不一致的时候发生的类型转换。人为的上通过转换函数进行转换，直接导致mysql无法使用索引。如果非要转型，应该在传入参数上进行转换。