MYSQL设计优化

本文将从各方面介绍优化mysql设计的一些方式。

1、优化sql语句

(1)定位需要优化的sql语句

1)show status统计SQL语句频率

对Myisam和Innodb存储引擎都计数的参数：

SHOW STATUS可以根据需要显示session级别的统计结果和global级别的统计结果。

1.Com_select  执行select操作的次数，一次查询只累加1；
2.Com_insert 执行insert操作的次数，对于批量插入的insert操作，只累加一次；
3.Com_update 执行update操作的次数；
4.Com_delete　执行delete操作的次数；

执行如：SHOW STATUS WHERE Variable_name = 'Com_select';

对Innodb存储引擎计数的参数（计算的方式不一样）：
1.Innodb_rows_read select查询返回的行数；
2.Innodb_rows_inserted 执行Insert操作插入的行数；
3.Innodb_rows_updated 执行update操作更新的行数；
4.Innodb_rows_deleted 执行delete操作删除的行数；
通过以上几个参数，可以很容易的了解当前数据库的应用是以插入更新为主还是以查询操作为主，以及各种类型的SQL大致的执行比例是多少。
对于更新操作的计数，是对执行次数的计数，不论提交还是回滚都会累加。

对于事务型的参数
1.Com_commit 事务提交次数
2.Com_rollback 事务回滚次数
对于回滚操作非常频繁的数据库，可能意味着应用编写存在问题。

数据库的基本情况的参数：
1.Connections 试图连接Mysql服务器的次数
2.Uptime 服务器工作时间

3.Slow_queries 慢查询的次数

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

两种方式定位执行效率较低的SQL语句：
(1)通过 慢查询日志定位那些执行效率较低的sql语句（需要查询结束后），用--log-slow-queries[=file_name]选项启动时，mysqld写一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件.
(2)使用 show processlist命令查看当前MySQL在进行的线程，包括线程的状态，是否锁表等等，可以实时的查看SQL执行情况，同时对一些锁表操作进行优化。

3)EXPLAIN命令分析SQL语句

通过explain或者desc 获取MySQL如何执行SELECT语句的信息
EXPLAIN SELECT * FROM message a LEFT JOIN mytable b ON a.id = b.id WHERE a.id=1;
返回结果
+--------+---------------+-------+-------+--------------+----------------+-----------+-------+--------+--------------+
|   id   | select_type   | table | type  | possible_keys| key            | key_len   | ref   |  rows  |  Extra       |
+--------+---------------+-------+-------+--------------+----------------+-----------+-------+--------+--------------+
|   1    | SIMPLE        | a     | const | PRIMARY      | PRIMARY        | 4         | const | 1      |              |
|   1    | SIMPLE        | b     | ALL    | NULL             | NULL           | NULL      |       | 9999   |              |
+--------+---------------+-------+-------+--------------+----------------+-----------+-------+--------+--------------+
select_type：select 类型
table：      输出结果集的表
type：       表示表的连接类型
①当表中仅有一行是最佳的连接类型；
②当select操作中使用索引进行表连接时type的值为ref；
③当select的表连接没有使用索引时，经常会看到type的值为ALL，表示对该表进行了全表扫描，这时需要考虑通过创建索引来提高表连接的效率。

possible_keys：表示查询时,可以使用的索引列.
key：