mysql checking permissions 优化_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 | 158 |

+---------------+-------+

1 row in set (0.00 sec)

3、查询操作的次数

mysql> show status like 'com_select';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| Com_select | 1 |

+---------------+-------+

1 row in set (0.00 sec)

4、插入操作的次数

mysql> show status like 'com_insert';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| Com_insert | 2 |

+---------------+-------+

1 row in set (0.00 sec)

5、更新操作的次数

mysql> show status like 'com_update';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| Com_update | 3 |

+---------------+-------+

1 row in set (0.00 sec)

6、删除操作的次数

mysql> show status like 'com_delete';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| Com_delete | 1 |

+---------------+-------+

1 row in set (0.00 sec)

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

mysql> show status like 'slow_queries';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| Slow_queries | 15 |

+---------------+-------+

1 row in set (0.00 sec)

二、对SQL语句进行分析 1、使用explain关键字进行分析

mysql> explain select * from user\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: user #表名

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_1 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_1 #使用的是哪个索引名称

key: index_1

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; # 查看是否开启

+-------------+

| @@profiling |

+-------------+

| 1 |

+-------------+

1 row in set, 1 warning (0.00 sec)

3、执行要测试的SQL语句

mysql> select * from stu_info;

+------+--------+--------+

| s_id | s_name | s_into |

+------+--------+--------+

| 1 | 2 | 3 |

| 3 | 4 | 5 |

| 2 | 4 | 5 |

+------+--------+--------+

3 rows in set (0.00 sec)

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

mysql> show profiles;

+----------+------------+------------------------+

| Query_ID | Duration | Query |

+----------+------------+------------------------+

| 1 | 0.00016900 | select @@profiling |

| 2 | 0.00018425 | select * from bank |

| 3 | 0.00018475 | select * from stu_info |

+----------+------------+------------------------+

3 rows in set, 1 warning (0.00 sec)

mysql> show profile for query 3; #查询sql语句的详细分析

+----------------------+----------+

| Status | Duration |

+----------------------+----------+

| starting | 0.000058 |

| checking permissions | 0.000005 |

| Opening tables | 0.000022 |

| init | 0.000015 |

| System lock | 0.000006 |

| optimizing | 0.000002 |

| statistics | 0.000008 |

| preparing | 0.000007 |

| executing | 0.000001 |

| Sending data | 0.000036 |

| end | 0.000002 |

| query end | 0.000004 |

| closing tables | 0.000004 |

| freeing items | 0.000008 |

| cleaning up | 0.000008 |

+----------------------+----------+

15 rows in set, 1 warning (0.00 sec)

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

mysql> show profile block io for query 3\G

mysql> show profile all for query 3\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 stu_info;

+----------------+---------+----------+----------+

| Table | Op | Msg_type | Msg_text |

+----------------+---------+----------+----------+

| mysql.stu_info | analyze | status | OK |

+----------------+---------+----------+----------+

1 row in set (0.01 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表无效，在执行时会给表加上只读锁。http://www.sina.com.cn/mid/search.shtml?q=%E7%99%BE%E5%BA%A6%E5%85%B3%E9%94%AE%E8%AF%8D%E6%8E%92%E5%90%8D%E6%89%A3120280279

mysql> check table stu_info;

+----------------+-------+----------+----------+

| Table | Op | Msg_type | Msg_text |

+----------------+-------+----------+----------+

| mysql.stu_info | 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 stu_info\G

*************************** 1. row ***************************

Table: mysql.stu_info

Op: optimize

Msg_type: note

Msg_text: Table does not support optimize, doing recreate + analyze instead

*************************** 2. row ***************************

Table: mysql.stu_info

Op: optimize

Msg_type: status

Msg_text: OK

2 rows in set (0.04 sec)