MySQL快速入门(三)

1. 性能优化

 

          1.1  MySQL索引

                                     什么是索引?

                                              索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含

                                       着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，

                                        能加快数据库的查询速度。

                                        索引分类:

                                                  ①单列索引：一个索引只包含单个列，一个表可以有多个单列索引

                                                  ②组合索引：一个索引包含多个列

                                        索引的使用

                                                  创建索引

                                                         create index indecName on tableName(userName(length))’

                                                   修改索引

                                                          alter table tableName add index indexName(columnName)

                                                    删除索引

                                                          drop index indexName on tableName

                1.2   explain命令

                                   explain命令介绍

                                         explain命令显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句,使用方法在select语句前加上explain就可以了.

                                     示例:

                                                  

 

                                          Mysql执行计划的图解:

 

                   1.3   慢查询

                                     1. 什么是慢查询?

                                                        MySQL的慢查询，全名是慢查询日志，是MySQL提供的一种日志记录，用来记录在MySQL中响应时间超过阀值的语句。具体环境中，运行时间超过long_query_time值的SQL语句，则会被记录到慢查询日志                                                    中。long_query_time的默认值为10，意思是记录运行10秒以上的语句。默认情况下，MySQL数据库并不启动慢查询日志，需要手动来设置这个参数。当然，如果不是调优需要的话，一般不建议启动该参数，因为                                                    开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件和数据库表。

                                                     参数说明:

                                                              slow_query_log       慢查询开启状态(ON 为开启，OFF 为关闭)
                                                              slow_query_log_file   慢查询日志存放的位置

                                                              long_query_time      查询超过多少秒才记录(记录日志，单位为秒)

 

                                           2. 慢查询设置步骤

                                                           1.查看是否开启慢查询功能

                                                                      

                                                                      2.设置方法

                                                                           方法一:全局变量设置

                                                                                     1. 把slow_query_log 全局变量设置为“ON”状态

                                                                                         mysql> set global slow_query_log='ON';

                                                                                     2.设置慢查询日志存放的位置

                                                                                          mysql> set global slow_query_log_file='/usr/local/mysql/data/slow.log';

                                                                                     3.查询超过1秒就记录

                                                                                              mysql> set global long_query_time=1;

                                                                          方法二:配置文件设置

                                                                                      修改配置文件my.cnf，在[mysqld]下的下方加入

                                                                                    [mysqld]

                                                                                    slow_query_log = ON

                                                                                    slow_query_log_file = /usr/local/mysql/data/slow.log

                                                                                    long_query_time = 1

                                                                 3.重启MySQL服务

                                                                                    service mysqld restart

                                                                 4.查看设置后的参数

设置完后我们来测试一下是否开启慢查询

1.执行一条慢查询SQL语句

mysql> select sleep(2);

2.查看是否生成慢查询日志

ls /usr/local/mysql/data/slow.log

如果日志存在，MySQL开启慢查询设置成功！

3. 对慢查询的分析

使用mysqldumpslow工具

   在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活。

MySQL提供了日志分析工具mysqldumpslow

查看mysqldumpslow的帮助信息：

三 .MySQL性能分析语句show profile

什么是show profile?

          show profiles 这个命令非常强大，能清晰的展示每条SQL的持续时间。通常结合show profile 命令可以更加详细的展示其耗时信息。这样就能很容易的分析出，到底慢在哪个环节了。比较遗憾的是，在MySQL中，该命令默认是关闭状态的。

Show profile的使用

1..开启命令：

set profiling = ON; 或等于1

2.查看是否生效：

 Value的取值范围有两个：其中 ON 为开启状态，OFF为关闭状态。

值得注意的是：通过上述命令开启后仅在当前会话有效。

作用:

      show profiles 其作用为显示当前会话服务器最新收到的15条SQL的性能信息。

其中包括：持续时间，以及Query_ID。我们可以通过Query_ID分析其性能

1.Query_ID 表示执行SQL的唯一标识。

2.Duration 表示持续时间，默认单位为秒。

3.Query 就是我们所执行的SQL语

注意:

 show profiles 语句 默认显示的是服务端接收到的最新的15条语句。

我们可以通过以下语句进行修改默认值：

set profiling_history_size =20;

profiling_history_size最大取值取值范围为[0,100]。

当超过100时，则会设置自动设置为最大值100。

当小于0时，则会自动设置最小值为0。

当其等于0时，其效果等同于 set profiling=0，关闭性能分析模式

show profile命令的使用

  示例:用Query_ID查

当结果显示的比较多时，可以通过 limit 选项，来显示指定的行数.

   通过上述结果，我们可以非常清楚的查看每一步的耗时，其中(Druation的单位为秒)。这样，当我们遇到一条慢SQL时，就能很清楚的知道，为什么慢，慢在哪一步了。就可以进行针对性的优化。我们对优化后的SQL语句也能查看其持续时间，是否符合我们的指标.