MySQL SQL优化笔记的实际操作流程-Mysql Tutorial-php.cn

MySQL SQL优化笔记的实际操作流程

WBOY

Release： 2016-06-07 16:09:33

Original

1023 people have browsed it

以下的文章主要讲述的是MySQL SQL优化的实际应用笔记的实际操作流程，以及关于Mysql查询使用 index 的问题解决，如果你对其相关的实际操作有兴趣的话，你就可以对以下的文章点击观看了。 Mysql的强制索引（Force Index）都为我们做了哪些优化？ mysql5下大

以下的文章主要讲述的是 MySQL SQL优化的实际应用笔记的实际操作流程，以及关于Mysql查询使用 index 的问题解决，如果你对其相关的实际操作有兴趣的话，你就可以对以下的文章点击观看了。

Mysql的强制索引（Force Index）都为我们做了哪些优化？

mysql5下大数据量查询优化的问题

推荐圈子: Database圈子

更多相关推荐一般使用SQL的时候你是不会去想到优化。但是面对一个有MySQL SQL性能问题的数据库时，我们应该如何入手进行系统的分析，使得能够尽快定位问题SQL，并且尽快解决问题。

1.使用show status 命令了解各种MySQL SQL的执行频率

引用

例如在Mysql的Cline上输入

show status like 'Com_%';

显示的是一些:Com_xxx.

Com_xxx 表示每个xx语句执行的次数。通常情况下我们比较关注如下一些操作：

引用

Com_select:执行select操作的次数

Com_insert:执行Insert操作的次数，对于批量插入的INSERT操作，只累加一次

Com_update:执行update操作的次数

Com_delete:执行Delete操作的次数

上面这些参数对于所有存储引擎的表操作都会进行累加。下面有些参数只针对InnoDB存储引擎的，累加的算法也有点不一样。

引用

Innodb_rows_read:select查询返回的行数

Innodb_rows_inserted:执行INSERT操作插入的行数

Innodb_rows_updated:执行Update操作更新的行数

Innodb_rows_deleted:执行Delete操作删除的行数

通过上面的一些参数，我们可以了解当前数据库的应用是以插入为主还是以查询为主。以及各种类型的MySQL SQL大致的执行比例是多少。对于更新操作的计数，是对执行次数的计数，不管提交还是回滚都会进行累加。

对于事务型的应用，通过Com_commit和Com_rollback进行分析。如果回滚操作非常频繁那么要思考下是不是编写存在问题。

下面有几个参数用于了解数据库的基本情况

引用

Connections:试图连接Mysql服务器的次数（执行的命令是：show status like 'Con_%';）

Uptime: 服务器工作时间（执行的命令是：show status like 'Up_%';）

Slow_queries:慢查询的次数（执行的命令是：show status like 'Slow_%';）

2. 定位执行效率较低的MySQL SQL语句

要想定义效率较低的SQL可以按照下面两种方式试试。

引用

1. 通过慢查询日志定位那些执行效率较低的SQL语句，用 --log-slow-queries[=file_name]选项启动时，mysqld写一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件。

2. 慢查询日志在查询结束以后才记录，所以在应用反映执行效率出现问题的时候进行查询慢查询日志并不能定位问题，可以使show processlist 命令查看当前MySQL在进行的线程，包括线程的状态，是否锁表等，可以实时地查看SQL的执行情况，同时对一些锁表操作进优化。

3. 使用EXPLAIN分析低效SQL的执行计划。

在查询到效率低的MySQLSQL语句后，那我们可以使用explain或者DESC命令获取Myswl如何执行SELECT语句的信息，包括在Select语句执行过程中表如何连接和连接的顺序。

例如你想计数xxxx年公司的销售额，那么需要操作sales和comapny table，并对money字段进行sum操作。看看怎么使用explain：

引用

explain select sum(moneys) from sales a company b where a.company_id = b.id and a.year=XXXX \G;(注意加上\G是为了更好的看)

显示如下：

<ol class="dp-xml">
<li class="alt"><span><span>*********************** 1. row***************************   </span></span></li>
<li><span>id: 1   </span></li>
<li class="alt"><span>select_type: SIMPLE   </span></li>
<li><span>table: a   </span></li>
<li class="alt"><span>type: ALL   </span></li>
<li><span>possible_keys: NULL   </span></li>
<li class="alt"><span>key:NULL   </span></li>
<li><span>key_len: NULL   </span></li>
<li class="alt"><span>ref: NULL   </span></li>
<li><span>rows:1000   </span></li>
<li class="alt"><span>Extra: Using where   </span></li>
<li><span>*********************** 2. row***************************   </span></li>
<li class="alt"><span>id: 2   </span></li>
<li><span>select_type: SIMPLE   </span></li>
<li class="alt"><span>table: b   </span></li>
<li><span>type: ref   </span></li>
<li class="alt"><span>possible_keys: ind_company_id   </span></li>
<li><span>key:ind_comapany_id   </span></li>
<li class="alt"><span>key_len: 5   </span></li>
<li><span>ref: sakila.a.company_id   </span></li>
<li class="alt"><span>rows:1   </span></li>
<li><span>Extra: Using where;Using index  </span></li>
</ol>

Copy after login

下面解释下每个列的含义：

引用

select_type: 表示SELECT的类型，常见的取值为SIMPLE(简单表，不使用表连接或者子查询)、PRIMARY(主查询，即外层的查询)、UNION、SUBQUERY

table: 输出结果集的表

type: 表示表的连接类型，性能由好到差的类型类型为

(System(表中仅有一行，即常量表)，

const(单表中最多有一个匹配行)，

eq_ref(对于前面的每一行,在此表中只查询一条记录)，

ref(使用普通的索引)，

ref_or_null(和ref类似，但是条件中包含对于NULL查询),

index_merge(索引合并优化),