A股上市公司传智教育(股票代码 003032)旗下技术交流社区北京昌平校区

 找回密码
 加入黑马

QQ登录

只需一步,快速开始

本帖最后由 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无法使用索引。如果非要转型,应该在传入参数上进行转换。

0 个回复

您需要登录后才可以回帖 登录 | 加入黑马