温馨提示×

温馨提示×

您好,登录后才能下订单哦!

密码登录×
登录注册×
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》

MySQL数据库优化(1)

发布时间:2020-08-02 15:22:13 来源:网络 阅读:226 作者:warrent 栏目:MySQL数据库

前言

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

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)

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

向AI问一下细节

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

AI