MySQL数据库优化（1）

前言

不管对于哪种服务，对于其优化，无非是从两个方面着手，第一个是对于硬件方面的优化，第二个是对系统以及服务本身的优化。

栖霞网站建设公司成都创新互联公司,栖霞网站设计制作，有大型网站制作公司丰富经验。已为栖霞上千余家提供企业网站建设服务。企业网站搭建\外贸营销网站建设要多少钱，请找那个售后服务好的栖霞做网站的公司定做！

1、查询连接MySQL服务器的次数

mysql> show status like 'connections';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 3     |
+---------------+-------+
1 row in set (0.01 sec)

2、查询MySQL服务器的运行时间

mysql> show status like 'uptime';      //单位为“秒”
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime        | 127   |
+---------------+-------+
1 row in set (0.00 sec)

3、查询操作的次数

mysql> show status like 'com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 12    |
+---------------+-------+
1 row in set (0.00 sec)

4、插入操作的次数

mysql> show status like 'com_insert';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_insert    | 1     |
+---------------+-------+
1 row in set (0.00 sec)

5、更新操作的次数

mysql> show status like 'com_update';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_update    | 1     |
+---------------+-------+
1 row in set (0.00 sec)

6、删除操作的次数

mysql> show status like 'com_delete';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_delete    | 0     |
+---------------+-------+
1 row in set (0.00 sec)

7、查询MySQL服务器的慢查询次数

mysql> show status like 'slow_queries';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 21    |
+---------------+-------+
1 row in set (0.00 sec)

二、对SQL语句进行分析

1、使用explain关键字进行分析

mysql> explain select * from stu_info\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE 
        table: stu_info         #表名
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL     #使用哪个列或常数与索引一起使用来查询记录
         rows: 3
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

上面的select_type解释如下：
Select_type:表示select语句的类型其中simple 是简单查询(不包括连接查询和子查询) Primary 主查询 Union 连接查询；

2、利用索引来提高查询效率

mysql> explain select * from stu_info where s_id=3\G     #没有索引时的查询结果分析如下
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: stu_info
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3        #需要查询三行才能查到（这个表数据总共也就三行）
     filtered: 33.33
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

mysql> create index index_01 on stu_info(s_id);     #创建索引
mysql> explain select * from stu_info where s_id=3\G   #再次进行查询
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: stu_info
   partitions: NULL
         type: ref
possible_keys: index_01   #使用的是哪个索引名称
          key: index_01
      key_len: 5
          ref: const
         rows: 1     #创建索引后，查询1行就查到可。
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

使用索引注意事项如下：
做索引了之后，用 like ‘xx%’ %不在第一位查询效率最高；
若使用多字段索引，除了第一字段查询最快，其余不会按索引来，索引不生效；
若创建索引所设置的字段，查询索引组合 or 左右边的值都是属于索引设置字段下的值。

关于使用索引的其他注意事项，可以参考博文：MySQL索引类型详解；

三、profiling分析查询

通过慢日志查询可以知道哪些SQL语句执行效率低下，通过explain我们可以得知SQL语句的具体执行情况，索引使用等，还可以结合show命令查看执行状态。如果觉得explain的信息不够详细，可以同通过profiling命令得到更准确的SQL执行消耗系统资源的信息。 profiling默认是关闭的。可以通过以下语句查看：

1、查看profiling是否开启

mysql> show variables like '%profiling%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| have_profiling         | YES   |
| profiling              | OFF   |            #OFF表示未开启
| profiling_history_size | 15    |
+------------------------+-------+
3 rows in set (0.00 sec)

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |                   # 0表示未开启
+-------------+
1 row in set, 1 warning (0.00 sec)

2、开启profiling

mysql> set profiling=1;          #开启
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select @@profiling;    #qu
+-------------+
| @@profiling |
+-------------+
|           1 |
+-------------+
1 row in set, 1 warning (0.00 sec)

3、执行要测试的SQL语句

mysql> select * from bank;
+-------+-------+
| name  | money |
+-------+-------+
| lu    |  1000 |
| qi    |  1000 |
| zhang |  2000 |
+-------+-------+
3 rows in set (0.00 sec)

4、查看SQL语句对应的ID，对其进行分析

mysql> show profiles;
+----------+------------+--------------------+
| Query_ID | Duration   | Query              |
+----------+------------+--------------------+
|        1 | 0.00012925 | select @@profiling |
|        2 | 0.00401325 | SELECT DATABASE()  |
|        3 | 0.01405400 | show databases     |
|        4 | 0.00034675 | show tables        |
|        5 | 0.00011475 | show tabels        |
|        6 | 0.00029225 | show tables        |
|        7 | 0.00041200 | select * from bank |
|        8 | 0.00020225 | select * from bank |
+----------+------------+--------------------+
8 rows in set, 1 warning (0.00 sec)
mysql> show profile for query 7;     #查询sql语句的详细分析
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000161 |
| checking permissions | 0.000010 |
| Opening tables       | 0.000016 |
| init                 | 0.000047 |
| System lock          | 0.000013 |
| optimizing           | 0.000004 |
| statistics           | 0.000013 |
| preparing            | 0.000009 |
| executing            | 0.000004 |
| Sending data         | 0.000050 |
| end                  | 0.000004 |
| query end            | 0.000008 |
| closing tables       | 0.000007 |
| freeing items        | 0.000012 |
| logging slow query   | 0.000041 |
| cleaning up          | 0.000013 |
+----------------------+----------+
16 rows in set, 1 warning (0.00 sec)

在上面命令的返回结果中，status是profile里的状态，duration是status状态下的耗时，因此我们关注的就是哪个状态最耗时，这些状态中哪些可以优化，当然也可以查看更多的信息，比如：CPU等。语法如下：

mysql> show profile block io for query 7\G
mysql> show profile all for query 7\G

除了上面的block io和all以外，还可以换成cpu（显示用户cpu时间、系统cpu时间）、ipc（显示发送和接收相关开销信息）、page faults（显示页面错误相关开销信息）、swaps（显示交换次数相关开销的信息）。

注意：测试完成之后，记得要关闭调试功能，以免影响数据库的正常使用。

四、对数据库表结构进行优化

对数据库表结构的优化大概可以从以下几个方面着手：
将字段很多的表分解成多个表，尽量避免表字段过多；
增加中间表，合理增加冗余字段；
优化插入记录的速度；
在插入数据之前禁用索引，会让创建索引不生效，命令： ALTER TABLE table_name DISABLE KEYS;
根据实际情况来定，在插入记录之前禁用唯一性检查，命令：set unique_checks=0;
多条插入数据的命令最好整合为一条；
使用load data infle批量插入数据。
对于innodb引擎的表来说，以下几点可以进行优化：
禁用唯一性检查：set unique_checks=0;
禁用外键检查：set foreign_key_checks=0;
禁用自动提交：set autocommit=0;

分析表，检查表和优化表

所谓分析表，就是分析关键字的分布，检查表就是检查是否存在错误，优化表就是删除或更新造成的空间浪费。

1、分析表

分析表可以一次分析一个或多个表，在分析期间只能读，不能进行插入和更新操作。分析表的语法如下：

mysql> analyze table bank;
+-------------+---------+----------+----------+
| Table       | Op      | Msg_type | Msg_text |
+-------------+---------+----------+----------+
| test01.bank | analyze | status   | OK       |
+-------------+---------+----------+----------+
1 row in set (0.00 sec)

对于上述返回的结果解释：Table是表名，op执行的操作是什么， msg_type 信息级别（status是正常状态，info是信息，note注意，warning警告，error错误）， msg_text 是显示信息。

2、检查表

检查是否存在错误,关键字统计，检查视图是否有错误 Check table 表名 option ={quick |fast | medium|extended |changed} Quick 不扫描行，不检查错误连接 Fast 只检查没有被正确关闭的表 Medium 扫描行验证被删除的连接是有效的，也可以计算各行的关键字校验和。 Extended 对每行所有关键字进行全面的关键字查找，Changed 只检查上次检查后被更改的表和没有被正确关闭的表，Option只对myisam 有效对innodb表无效，在执行时会给表加上只读锁。

mysql> check table bank;
+-------------+-------+----------+----------+
| Table       | Op    | Msg_type | Msg_text |
+-------------+-------+----------+----------+
| test01.bank | check | status   | OK       |
+-------------+-------+----------+----------+
1 row in set (0.00 sec)

3、优化表

消除删除或更新造成的空间浪费，命令语法格式为：Optimize [local |no_write_to_binlog] table tb1_name ….，优化myisam的表和innodb的表都有效，但是只能优化表中的varchar\text\blob数字类型，执行过程中上只读锁。

mysql> optimize table bank\G
*************************** 1. row ***************************
   Table: test01.bank
      Op: optimize
Msg_type: note
Msg_text: Table does not support optimize, doing recreate + analyze instead
*************************** 2. row ***************************
   Table: test01.bank
      Op: optimize
Msg_type: status
Msg_text: OK
2 rows in set (0.04 sec)

———————— 本文至此结束，感谢阅读 ————————

分享题目：MySQL数据库优化（1）
网页地址：http://bzwzjz.com/article/gpohdo.html

用户体验为先导为品牌带来生命力

MySQL数据库优化（1）

前言

1、查询连接MySQL服务器的次数

2、查询MySQL服务器的运行时间

3、查询操作的次数

4、插入操作的次数

5、更新操作的次数

6、删除操作的次数

7、查询MySQL服务器的慢查询次数

二、对SQL语句进行分析

1、使用explain关键字进行分析

2、利用索引来提高查询效率

三、profiling分析查询

1、查看profiling是否开启

2、开启profiling

3、执行要测试的SQL语句

4、查看SQL语句对应的ID，对其进行分析

四、对数据库表结构进行优化

分析表，检查表和优化表

1、分析表

2、检查表

3、优化表

其他资讯

用户体验为先导 为品牌带来生命力

MySQL数据库优化（1）

前言

1、查询连接MySQL服务器的次数

2、查询MySQL服务器的运行时间

3、查询操作的次数

4、插入操作的次数

5、更新操作的次数

6、删除操作的次数

7、查询MySQL服务器的慢查询次数

二、对SQL语句进行分析

1、使用explain关键字进行分析

2、利用索引来提高查询效率

三、profiling分析查询

1、查看profiling是否开启

2、开启profiling

3、执行要测试的SQL语句

4、查看SQL语句对应的ID，对其进行分析

四、对数据库表结构进行优化

分析表，检查表和优化表

1、分析表

2、检查表

3、优化表

其他资讯

用户体验为先导为品牌带来生命力