深入浅出Mysql（四）

大批量插入数据优化
1、对应Myisam类型的表，可以通过以下方式快速的导入大量数据

alter table tablname DISABLE KEYS;
loading the data 
alter table tablname enable keys;

这两个命令用来打开或者关闭Myisam表非唯一索引的更新。
2、对于InnoDB类型的表，这种方式并不能提高导入的效率，我们可以采取下面几种策略：
（1）、因为InnoDB类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率。如果InnoDB表没有主键，那么系统会默认创建一个内部列作为主键，所以，如果可以给表创建一个主键，就可以利用这个优势提高导入数据的效率。
（2）、在导入数据前执行set unique_checkes = 0，关闭唯一性校验，在导入结束后执行set unique_checks = 1，恢复唯一性校验，可以提高导入的效率
（3）、如果应用使用自动提交的方式，建议在导入前执行set autocommit = 0，导入结束收再执行set autocommit = 1，也可以提高导入效率
优化group by语句
默认情况下，Mysql排序所有group by col1,col2,…的字段。查询的方法如同在查询中指定order by col1,col2,….。如果显示的包括一个包含相同的列order by 子句，Mysql可以毫不减速的对他进行优化，尽管仍然进行排序。
如果查询包括group by但你还想要避免排序结果的消耗，你可以指定order by null禁止排序，例如：

select a,count(*) form bar group by a order by null;

优化order by语句
在某些情况下，Mysql可以使用一个索引来满足order by子句，而不需要额外的排序，where条件和order by使用相同的索引，并且order by的顺序和索引顺序相同，并且order by的字段都是升序或者都是降序。
例如：下列sql可以使用索引

select * from t1 order by key_part1,key_part2,....;
select * from t1 where key_part1 = 1 order by key_part1 desc,key_part2 desc;
select * from t1 order by key_part1 desc,key_part2 desc;

以下情况不适用索引

select * from t1 order by key_part1 desc,key_part2 asc; -- 排序混合
select * from t1 where key2 = constant order by key1; -- 用于查询行的关键字和order by中所使用的不同
select * from t1 order by key1,key2;-- 一对不同的关键字使用order by

优化join语句
假设我们要将所有没有订单记录的用户取出来，可以用下面这个查询完成：
select * from customerinfo where customer_id not in (select customer_id from salesinfo);
如果使用join来完成，速度将会快很多。尤其是salesinfo表中对customer_id建有索引的话，性能将会更好，查询如下：

select * from customerinfo left join salesinfo on customerinfo.customer_id = salesinfo.customer_id 
where salesinfo.customer_id is null;

join的效率更高是因为Mysql不需要再内存中创建临时表来完成这个逻辑上需要两步骤的查询工作。
优化or条件
对于or子句，如果要利用索引，则or之间的每一个条件都必须用到索引
查询优先还是更新（insert,update,delete）优先
我们首先应该确定应用的类型，判断应用是以查询为主，还是以更新为主，下面我们提到的改变调度策略的方法主要指针对Myisam存储引擎，对于InnoDB存储引擎，语句的执行时由获得行锁的顺序决定的。
Mysql的默认调度策略总结如下：
1、写入操作优先于读取操作
2、对某张表的写入操作某一时刻只能发生一次，写入请求按照他们到来的次序来处理
3、对某张表的多个读取操作可以同时进行，Mysql提供了几个语句调节符，允许你修改他的调度策略
（1）、low_priority关键字应用于delete、insert、load data、replace和update
（2）、high_priority关键字应用于select和insert语句
（3）、delayed关键字应用于insert和replace语句
优化表的数据类型
我们可以使用procedure analyse()对当前已有用用的表类型的判断，该函数可以对数据表中的列的数据类型提出优化建议，可以根据应用的实际情况酌情考虑是否实施优化。
语法：

select * from tbl_name procedure analyse();
select * from tbl_name procedure analyse(16,256);

输出的每一列信息都会对数据表中的列的数据类型提出优化建议。第二个例子告诉procedure analyse()不要为那些包含的值多于16个或者256字节的enum类型提出建议，如果没有这个限制，输出信息可能很长，enum定义通常很难阅读。
通过拆分，提高表的访问效率
这里所说的拆分主要针对Myisam类型的表，拆分的方法分为两种情况：
1、纵向拆分
按照应用访问的频度，将表中经常访问的字段和不经常访问的字段拆分成两个表，经常访问的字段尽量是定长的，这样可以有效的提高表的查询和更新效率
2、横向拆分
按照应用的情况，有目的的将数据横向拆分成几个表或者通过分区分到多个分区中，这样可以有效的避免Myisam表的读取和更新导致锁的问题。
使用冗余统计表
使用create temporary table语句，他是基于session的表，表的数据保存在内存里面，当session断掉后，表自然消除
对于大表的统计分析，如果统计的表数据列不大，利用insert…select 将数据迁移到临时表比直接在大表上统计效率更高