Mysql SQL性能优化

Mysql优化一方面是找出系统的瓶颈，提高mysql数据库的整体性能，另一方面需要合理的结构设计和参数的调整，以提高用户的操作响应速度，同时还要尽可能节省系统资源，以便系统可以提供更大负荷的服务。
原则是减少系统瓶颈，减少资源占用，增加系统反应速度。比如提高I/O的读写速度，通过优化操作系统调度策略，提高mysql在高负荷情况下的负载能力，优化表结构，索引，查询语句等使查询响应更快。
mysql有一些查询数据库性能的参数，show status
show connections //连接服务器的次数，
show update //mysql服务器的上线时间
show slow_queries  //慢查询的次数
show com_select //查询操作的次数
show com_insert //插入操作的次数
show com_update //更新搞作的次数
show com_delete //删除操作的次数
mysql中有一个专门负责优化select语句的优化器模块，该优化器模块就是Query Optimizer，
一般的检查mysql时候我习惯用explan和profiling
insert语句优化：
1.插入很多行数据到mysql，如果是一次多个值，可以使用下边格式，将大大缩短客户端与数据库服务器之间的连接和关闭。

insert into table values (id,name),(id,name2);

当从一个文件装载一个表时，使用load data infile加载数据往往比使用很多insert语句效率至少提高20倍
2.对于MyISAM类型的表，如果不同客户端插入很多行，可以通过使用insert delayed语句提升速度，该操作向服务器提交数据后服务器会返回OK给客户端。这并不是将数据立即插入到列表，而是存储在内存里等待排队，直到当mysql服务器有空闲时再插入，此时的数据没有真正写入磁盘，这样的好处是提高插入数据的速度，不好的地方在于如果系统奔溃你就没有办法找到丢失的数据了。
3.通常可以锁定表以加快插入数据，

lock tables test write
insert into test values(1,'22',2,'22' );
unlock tables;

如果不加锁定表，每一次执行insert语句完成后，索引缓存区都会被写到磁盘上，而加入锁定后索引缓存区仅被写到磁盘上一次。
4.影响插入速度的主要是索引，唯一性校验，一次插入记录条数等我们可以分别进行优化。
a.禁用索引
对非空表插入记录时，mysql会根据表的索引对插入记录建立索引，如果插入大量数据，建立索引会降低插入记录的速度，所以在插入前禁用索引，插入后开启索引

alter table test distable keys；
//插入数据
alter table test enable keys；

b.禁用唯一性检查
为一些校验会降低插入的速度，为了降低这种情况的查询速度，可在插入记录之前禁用唯一性检查等到记录插入后开启

set unique_checks=0;
set unique_checks=1;
load data infile '/home/mysql/data.txt' into table test;
set unique_checks=0;//不校验唯一性
load data infile '/home/mysql/data.txt' into table test;

c.使用批量插入
就如上边一个insert多个数据
d.使用load命令批量导入

alert table test disable keys；
load data infile ‘/home/mysql/data.txt’ into table test;
alert table test enable keys；

上边是使用MyISAM引擎的方法
对应Innodb使用

a,禁止唯一检查set unique_checks=0
b,禁止外键检查set foreign_key_checks=0
c,禁止自动提交set autocommit=0

优化ORDER BY语句
1，通常可以采用索引来对order by语句进行优化。order by+limit；where +order by+limit

select id，name from test order by name limit 0,10
select id,name from test where name='Janle' order by name limit 0,10;

通常不要对where和order by使用表达式或者函数，例如

select * from test order by year(birthday) limit 0,20;

以下几种情况是不应该使用索引：
1》order by的字段混合使用asc和desc,如

select * from test order by a asc，b desc ;

2》where子句使用的字段和order by不一致如：

select * from test where b=1 order by a;

3》对不同的关键字使用order by排序如：

select * from test order by a;

2.使用group by 语句时，mysql会对符合的结果自动排序，通过扫描临时表并创建一个临时表，表中每个组的所有行为连续的。然后使用临时表来找到组并应用累积行数。在某些情况下，mysql可以通过索引访问而不用临时表。通过指定group by null可以禁止排序，从而节省损耗。

select count(*) from test group by id order by null;

优化嵌套查询
1.子查询虽然方便查询很灵活，但是mysql需要为内层查询语句建立一个临时表，然后外层查询语句从临时表中查询记录。查询完毕后再撤销这些临时表。因此速度会有影响，如果查询的数据量比较大，这种影响会随之增大。可以使用join查询来代替子查询，连接查询不需要建立临时表，速度比子查询快，使用索引的话，性能会更好。从执行计划可看出采用join后连接查询扫描记录有了很大的改善，性能有一定的提高。
优化OR条件
对于使用OR条件语句子查询，如果要使用索引，则OR之间的每个条件列都必须使用到索引，如果没有所以更可以考虑添加索引。

explan select * from test where col=1 and col1=2；//没有使用union
create index index_col on(test.col);
create index index_col1 on(test.col1);
explan select * from test where col=1 and col1=2；//可以看见是做了union操作

添加索引后是对各个字段查询到结果之后再进行union操作