|
本文将从各方面介绍优化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： 表示使用的索引
key_len： 索引长度
rows： 扫描范围
Extra：执行情况的说明和描述
例如上面的例子，因为是对b表的全表扫描导致效率下降，则对b表的 id 字段创建索引，查询需要扫描的行数将会减少。
返回结果
+--------+---------------+-------+-------+--------------+----------------+-----------+-------+--------+--------------+
| 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 | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+--------+---------------+-------+-------+--------------+----------------+-----------+-------+--------+--------------+
(2)sql语句优化方式
1)大批量插入数据 1)对于Myisam类型的表，可以通过以下步骤快速的导入大量的数据。
前后两个命令用来打开或者关闭Myisam表非唯一索引的更新。在导入大量的数据到一个非空的Myisam表时，通过设置这两个命令，可以提高导入的效率。
ALTER TABLE mytable DISABLE KEYS;
INSERT INTO mytable(id, username, city, age) VALUES(1, 'name1', 'city1', 10),(2, 'name2', 'city2', 20),(3, 'name3', 'city3', 30);
ALTER TABLE mytable ENABLE KEYS;
对于导入大量数据到一个空的Myisam表，默认就是先导入数据然后才创建索引的，所以不用进行设置。
2)对于Innodb类型的表，我们有以下几种方式可以提高导入的效率（对Innodb类型的表，上面的方式并不能提高导入数据的效率）
①因为Innodb类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率。
如果Innodb表没有主键，那么系统会默认创建一个内部列作为主键，所以如果可 |