黑马程序员技术交流社区

标题: 【广州校区】+【原创】+MySQL优化浅谈 [打印本页]

作者: wujianhui 时间: 2019-7-18 11:17
标题: 【广州校区】+【原创】+MySQL优化浅谈
本帖最后由 wujianhui 于 2019-7-18 11:20 编辑

在面试过程中，经常会问到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无法使用索引。如果非要转型，应该在传入参数上进行转换。

欢迎光临黑马程序员技术交流社区 (http://bbs.itheima.com/)

黑马程序员IT技术论坛 X3.2