一 SQL语句优化
1.1 使用 show status 命令了解sql执行频率
mysql> show session status 查询当前连接统计结果
mysql> show global status 查询自数据库上次启动至今统计结果
或者在操作系统的终端执行
shell> mysqladmin extended-status
可以通过like语句来查询一些特定的内容
mysql> show global status like "Com_%";
上次启动以来的每个Com_xxx语句执行的次数
Com_select: 执行select查询的次数
Com_insert: 执行insert的次数
Com_update: 执行update的次数
Com_delete: 执行delete的次数
以上针对所有存储引擎表操作记录，专门针对某一些存储引擎的如下：
Innodb_rows_read
Innodb_rows_insert
Innodb_rows_update
Innobd_rows_delete
通过上面这些数据的比较可以判断数据库是写为主，还是查询为主
事务信息 可通过
Com_commit
Com_rollback
来了解提交和回滚情况。如回滚操作很频繁，说明应用编写存在问题。
其他重要参数，可展示数据库基本情况句子：
Connections： 试图连接Mysql数据库的次数
Uptime： 服务器工作时间
Slow_queries： 慢查询的次数
1.2 定位执行效率比较低的sql语句
两种方法：
1）慢查询日志定位
用--log-slow-queries[=filename]选项启动时，mysqld写一个包含所有执行时间超过long_query_time秒得sql语句的日志文件。
2）show processlist
查看MySQL在进行的线程，包括线程状态，是否锁表等，可实时查看sql执行情况，并对一些锁表操作优化。
1.3 通过Explain分析低效SQL执行计划
通过上面步骤，查到低效sql语句后，可通过 explain和desc命令获取mysql如何执行select语句信息，如表连接。
explain
select sum(money)
from sales a, company b
where a.company_id = b.id and a.year = 2006
*************************** 1. row *****************
id: 1
select_type: SIMPLE
table: user
type: system
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 0
Extra: const row not found
1 row in set (0.00 sec)
ERROR:
No query specified
每一列的含义说明
select_type:
表示select的类型
（SIMPLE=>简单表，不使用表连接或者子查询,PRIMARY=>主查询,
外层的查询，UNION=>UNION中的第二个或者后面的查询语句，
SUBQUERY=>子查询中的第一个select）
table: 输出结果集的表
possible_keys: 表示查询时，可能使用的索引
key: 表示实际使用的索引
key_len: 索引字段的长度
rows; 扫描的行的数量
Extra: 执行情况的说明和描述
type:
表示表的连接类型，性能有好到差的链接类型为：
system=>只有一行，也就是常量表;
const=>单表中最多有一个匹配行，例如primary key或者unique index；
eq_ref=>对于前面的每一行，在此表中只查询一条记录，简单来说，就是多表连接中使用primary key或者unique index；
ref=>与eq_ref类似，区别在于不是使用primay key或者unique index而是使用普通的索引；
ref_or_null=>与ref类似，区别在于条件中包含对null的查询；
index_merge=>索引合并优化；
unique_subquery=>in的后面是一个查询主键字段的子查询；
index_subquery=>与unique_subquery类似，区别在于in的后面是查询非唯一索引字段的子查询；
range=>单表中的查询范围；
index=>对于前面的每一行，都通过查询索引来得到数据；
all=>对于前面的每一行，都通过全表扫描来得到数据
二 索引问题
2.1 索引存储分类
MyISAM存储索引 表数据 和 索引 自动分开存储，各自独立文件
InnoDB存储引擎 表数据 和 索引 存储在同一表空间，但可有多个文件组成
Mysql存储类型只有两种，BTREE和HASH，具体情况和表的存储引擎有关
MyISAM和InnoDb存储引擎 都支持 BTREE索引
MEMORY/HEAP存储引擎 支持HASH，BTREE索引
mysql不支持函数索引，但是能对队列的前面的某一部分进行索引，例如name字段，可以只取name的前四个字符来进行索引。
2.2 使用索引
对相关列使用索引是提高select性能的最佳途径。
使用索引的条件：
a、查询条件中有索引关键字，
b、多列索引只有查询条件使用了多列关键字最左边的前缀时，才可以使用索引，否则将不能使用索引。
2.2.1 使用索引
一下情况中会使用到索引：
（1） 多列索引，只要查询的条件中用到了最左边的列，索引一般就会被使用
（2） 对于使用like查询，后面如果是常量，只有%号不在第一个字符时，
索引才可能被用到 比如 like "%3" 不会用索引， like "3%"就会走索引
（3） 对大文本进行搜索的时候，使用全文索引，而不是使用 like '%...%'
（4） 如果列名是索引，使用column_name is null将使用索引，
例如： select * from aaa where name is null（name是索引列）
2.2.2 存在索引但不使用
在下列情况下，虽然mysql存在索引，但是并不会使用到索引
（1）如果Mysql估计使用索引比全表扫描更慢，则不使用索引。
例如如果列key_1 均匀分布在1和100之间，那么查询 select * from table where key_1 > 1 and key_1 < 90;
（2）如果使用MEMPRY/HEAP表并且，where条件中不使用“=”进行索引列，
那么不会用到索引。heap表只有在使用“=”的时候，才使用索引
（3）用or隔开的条件，如果or前面的列中有索引，而后面的列中没有索引，
那么涉及的索引都不会被用到（or中有一个条件中的列没有索引就用不到索引）
（4）如果不是索引列的第一部分（复合索引的第一部分）
（5）如果like是%开始的
（6）如果列类型是字符串，那么一定记得在where条件中把字符常量值用引号引起来，
否则即便是这个列上有索引，也不用用到（比如name字段是字符串的，却写了name=123。要改成“123”）
2.3 查看索引使用情况
Handler_read_key
代表一个行被索引值读的次数。
如果索引正在工作，值将很高，很低表明增加索引性能改善不高。
Handler_read_rnd_next
代表数据文件中读下一行的请求数。
查看方法：
show status like 'Handler_read%
值高则查询效率低，应建立索引补救。
如果正在进行大量的表扫描，Handle_read_rnd_next的值较高，则通常说明表索引不正确或者写入的查询没有利用索引。
3 两个简单优化方法
3.1 定期分析表和检查表
1）分析表语法
analyze [local | no_write_to_binlog] table tab1_name [, ta1_name] ...
分析和存储表的关键字分布，得到更准确地统计信息，使sql正确执行。
如果用户感觉实际执行计划并不是与预期的执行计划，执行一次分析表可能会解决问题。
在分析期间，使用一个读取锁对表进行锁定，这对于MyISAM，BDB和InnoDb表有作用。
对于MyISAM表，与使用myisamchk -a 相当。
mysql> analyze table user;
+------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------+---------+----------+----------+
| mysql.user | analyze | status | OK |
+------------+---------+----------+----------+
1 row in set (0.05 sec)
2）检查表语法
check table tab1_name [,tab1_name] ... [option] ..
option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
作用：
检查一或多表错误。对MyISAM和InnoDB表有用，对MyISAM表，关键字统计数据被更新。
例如：
mysql> check table user;
+------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------+-------+----------+----------+
| mysql.user | check | status | OK |
+------------+-------+----------+----------+
1 row in set (0.00 sec)
check table也可检查视图错误，如：视图定义被引用表不存在。
3.2 定期优化表
语法：
optimize [local | no_write_to_binlog] table tab1_name [, tab1_name] ...
适用范围：
a、删除了表的一部分
b、对含有可变长度行表（varchar，blob，text列的表）进行了很多更改。
作用：
将表空间碎片合并，消除删除或者更新造成的空间浪费。只适用MyISAM，BDB和InnoDb表。
mysql> optimize table user;
+------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------+----------+----------+----------+
| mysql.user | optimize | status | OK |
+------------+----------+----------+----------+
1 row in set (0.01 sec)
4 常用SQL优化
4.1 大批量插入数据优化
当用load命令导入数据的时候，适当的设置可以提高导入的速度
4.1.1 MyISAM引擎表插入大数据
alter table tab_name disable keys;
load the data
alter table tab_name enable keys;
以上是打开或者关闭MyISAM表非唯一索引的更新。
注意：
导入非空数据表，上面方法很有效，但导入空表，索引是数据导入完毕之后才去创建的，所以没有影响。
4.1.2 对InnoDB表数据导入大数据
（1）InnoDB类型表按主键顺序存储，故导入数据按主键顺序排列，可有效提高导入效率；
（2）导入数据前，关闭唯一性校验set unique_checks=0， 导入结束后设为 1 开启，可提高效率；
（3）如应用使用自动提交，导入数据的时候执行 set autocomment=0关闭自动提交，导入后打开；
4.2 优化insert
（1）如从同一客户插入很多行，使用多值表insert语句，将缩短客户端与数据库间链接、关闭等资源消耗，使效率快
如：
insert to test values(1,2),(1,3),(1,4)....
（2）如从不同客户插入多行，使用insert delayed语句得更高速度。
delayed延迟insert语句执行，数据放在内存队列，并没有真正写入磁盘，这比每一条数据分别插入快得多。
low_priority 相反，所有其他用户对表读写后才插入。
3）将索引文件和数据文件 分不同磁盘存放；
4）批量插入增加 bulk_insert_buffer_size 变量值提高速度，但只对MyIsAM表使用；
5）文本文件装载表，使用load file insert比通常sql语句快20倍。
4.3 优化group by
查询含group by但要避免排序结果的消耗，可指定order by null禁止排序
如：
select id, sum(money) from sale2
group by id
order by null
4.4 优化order by
可使用索引满足一个order by语句。
条件：
where条件和order by使用相同索引；
order by顺序和索引顺序相同；
order by字段都升或降序
如：
order by key1,key2
where key1=123 order by key1 desc, key2 desc
order by key1 desc, key2 desc
以下情况不行
order by key1 asc, key2 desc 混合使用ASC和DESC
where key2 = 1 ， order by key1 查询关键字和排序的不一样
order by key1，key2 对不同关键字使用排序
4.5 优化嵌套查询
有时子查询可被更有效的join代替
比如
select * from a where b_id not in(select id from b)
换成
select * from a left join b on a.b_id = b.id
where a.b_id is not null
4.6 优化OR
or每一个条件都使用索引
但若or几个条件是复合索引元素，则无优化效果
4.7 使用SQL提示
在sql中加入一些人为提示进行优化。
例如：
select sql_buffer_results * from
指示MySql生成临时结果集，所有表锁被释放。
这能解决表锁问题，或要花长时间将结果传给客户端，因为资源被快速释放。
1）use index
查询语句表名后加use index指定mysql参考的索引列表，不考虑其他索引。
select * from a use index(ind_a_id) where id = 1
2）ignore index
若打算忽略一多个索引，可用ignore index为hint。
select * from a ignore index(ind_a_id) where id = 1
3）force index
强制MySQL使用特定索引，可在查询中使用。