如何优化sql?怎么发现有问题的sql?

实践中如何优化MySQL

实践中,MySQL的优化主要涉及SQL语句及索引的优化、数据表结构的优化、系统配置的优化和硬件的优化四个方面,如下图所示:

Mysql性能优化-82.5kB

1)、SQL语句及索引的优化

(1). SQL语句的优化

SQL语句的优化主要包括三个问题,即如何发现有问题的SQL、如何分析SQL的执行计划以及如何优化SQL,下面将逐一解释。

a. 怎么发现有问题的SQL?(通过MySQL慢查询日志对有效率问题的SQL进行监控)

MySQL的慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阀值的语句,具体指运行时间超过long_query_time值的SQL,则会被记录到慢查询日志中。long_query_time的默认值为10,意思是运行10s以上的语句。慢查询日志的相关参数如下所示:

æ¢æŸ¥è¯¢æ—¥å¿—相å³å‚æ•°.png-20.4kB

通过MySQL的慢查询日志,我们可以查询出执行的次数多占用的时间长的SQL、可以通过pt_query_disgest(一种mysql慢日志分析工具)分析Rows examine(MySQL执行器需要检查的行数)项去找出IO大的SQL以及发现未命中索引的SQL,对于这些SQL,都是我们优化的对象。

b. 通过explain查询和分析SQL的执行计划

使用 EXPLAIN 关键字可以知道MySQL是如何处理你的SQL语句的,以便分析查询语句或是表结构的性能瓶颈。通过explain命令可以得到表的读取顺序、数据读取操作的操作类型、哪些索引可以使用、哪些索引被实际使用、表之间的引用以及每张表有多少行被优化器查询等问题。当扩展列extra出现Using filesort和Using temporay,则往往表示SQL需要优化了。

c. SQL语句的优化

优化insert语句:一次插入多值;

应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描;

应尽量避免在 where 子句中对字段进行null值判断,否则将导致引擎放弃使用索引而进行全表扫描;

优化嵌套查询:子查询可以被更有效率的连接(Join)替代;

很多时候用 exists 代替 in 是一个好的选择。

1)、索引优化

建议在经常作查询选择的字段、经常作表连接的字段以及经常出现在order by、group by、distinct 后面的字段中建立索引。但必须注意以下几种可能会引起索引失效的情形:

以“%(表示任意0个或多个字符)”开头的LIKE语句,模糊匹配;

OR语句前后没有同时使用索引;

数据类型出现隐式转化(如varchar不加单引号的话可能会自动转换为int型);

对于多列索引,必须满足最左匹配原则(eg,多列索引col1、col2和col3,则 索引生效的情形包括col1或col1,col2或col1,col2,col3)。

2). 数据库表结构的优化

数据库表结构的优化包括选择合适数据类型、表的范式的优化、表的垂直拆分和表的水平拆分等手段。

(1). 选择合适数据类型

使用较小的数据类型解决问题;

使用简单的数据类型(mysql处理int要比varchar容易);

尽可能的使用not null 定义字段;

尽量避免使用text类型,非用不可时最好考虑分表;

(2). 表的范式的优化

一般情况下,表的设计应该遵循三大范式。

(3). 表的垂直拆分

把含有多个列的表拆分成多个表,解决表宽度问题,具体包括以下几种拆分手段:

把不常用的字段单独放在同一个表中;

把大字段独立放入一个表中;

把经常使用的字段放在一起;
  
这样做的好处是非常明显的,具体包括:拆分后业务清晰,拆分规则明确、系统之间整合或扩展容易、数据维护简单。

(4). 表的水平拆分

表的水平拆分用于解决数据表中数据过大的问题,水平拆分每一个表的结构都是完全一致的。一般地,将数据平分到N张表中的常用方法包括以下两种:

对ID进行hash运算,如果要拆分成5个表,mod(id,5)取出0~4个值;
针对不同的hashID将数据存入不同的表中;
  表的水平拆分会带来一些问题和挑战,包括跨分区表的数据查询、统计及后台报表的操作等问题,但也带来了一些切实的好处:

表分割后可以降低在查询时需要读的数据和索引的页数,同时也降低了索引的层数,提高查询速度;

表中的数据本来就有独立性,例如表中分别记录各个地区的数据或不同时期的数据,特别是有些数据常用,而另外一些数据不常用。

需要把数据存放到多个数据库中,提高系统的总体可用性(分库,鸡蛋不能放在同一个篮子里)。

3). 系统配置的优化

操作系统配置的优化:增加TCP支持的队列数

mysql配置文件优化:Innodb缓存池设置(innodb_buffer_pool_size,推荐总内存的75%)和缓存池的个数(innodb_buffer_pool_instances)

4). 硬件的优化

CPU:核心数多并且主频高的
内存:增大内存
磁盘配置和选择:磁盘性能

加油!会技术的人很多,你不过是一只刚走路的菜鸟!

更多数据库问题相关参考博客:https://blog.csdn.net/justloveyou_/article/details/78308460

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值