mysql 语句的索引和优化
一、基本语句优化
1.尽量避免在列上进行运算，这样会导致索引失败。例如：
select * from table where DATE_FORMAT(`customer_regtime`,'%Y')>='2010'
优化为
select * from table where customer_regtime>='2010-01-01'
2.在使用join时，应该根据功能的需要尽量使用小结果集驱动大结果集。同时把复杂的join查询拆分成多个query。因为join多表时，可能导致更多的锁定和堵塞。
3.仅列出需要的字段，这对查询速度没有影响，但是对内存可以节省很多。例如：
select * from customer;
优化为
select customer_id,customer_name from customer;
4.使用批量插入时节省交互例如:
insert into a1(name) values('name1');
insert into a1(name) values('name2');
insert into a1(name) values('name3');
insert into a1(name) values('name4');
优化为：
insert into a1(name) values('name1'),('name2'),('name3'),('name4');
5.limit 的基数比较大时，可以使用where between 或其他方式代替。
6.不要使用rand函数获取多条随机数据例如：
select * from a2 order by rand() limit 20;
可以使用php参数随机数使用 mysql in 查询 。
7.避免使用null
8.不要使用count(id),因该使用count(*)
9.不要做无谓的排序操作，尽可能的在索引中完成。
二、索引与性能分析
查看sql执行的效率可以通过开始 profiling 来查看
set profiling =1;
开启后执行sql语句mysql就会分析执行该sql 的详细报告
例如 多执行几遍
select * from a2;
然后 查看
show profiles;
第一次用时是最长的比后面的时间变多了几乎一倍，这是因为mysql缓存了查询。
如果需要查看某一个语句的细节可以使用
show profile cpu,block io for query 4;
结果为：
查看select 语句在执行过程中是否用到索引，如果是联合查询时联合的顺序类型等信息可以使用explain
explain select * from a2;
结果为：
个属性含有如下
id：查询序列号
select_type:查询的类型，主要包括普通查询，联合查询、子查询。
table:查询的表明。
type:联合查询使用的类型。
possible_keys：表示mysql能使用哪个索引在该表中找到该行。如果这个值是空就表示没有用到索引，可以通过检查where子句，看看是否引用了某些字段。
key：显示mysql实际决定使用的键。如果没有索引被应用则为空。
key_len:显示mysql决定使用的键长度。如果键是null这个也是null。这个值反应出一个多重主键里实际使用了哪部分。
ref：显示哪个字段或常数与key一起被使用。
rows：这个值表示mysql要便利多少数据才能找到需要的结果集，在innodb上不准确。
extra：如果是 only index，意味着信息只能用索引树中的信息检索，这比扫描整个表要快，如果是where used，则表示使用了where 限制，但是用索引还不够，如果是impossi-ble where，则表示通过收集到的统计信息判断出不可能存在的结果。除此之外，extra还有下面一些可能值：using filesort：表示包含orderby 且无法使用索引进行排序操作时，不得不使用相应的排序算法实现。using temporary:使用临时表，常见于orderby和group by。select tables optimized way：使用聚合函数，并且mysql进行了快速定位。通常是max，min，count（*） 等函数。
type特别说明：type显示的访问类型是较重要的指标：结果重好到坏一次是：system(系统表)，const(读常量)，eq_tef(最大一条比配结果，通常是通过主键访问)，ref(被驱动表索引引用)，fulltext(全文索引检索)，ref_of_null(带空值的索引查询)，index_merge(合并索引结果集),unique_subquery(子查询中返回的字段是唯一组合或索引)，index_subquery(子查询返回的是索引，但非主键)，range(索引范围扫描)，index(全索引烧苗)，all(全表扫描)。
一般来说，保证查询至少range级，最好能达到ref级。all为全表扫描，是最坏的情况，表示没有用到索引。
索引的建立和使用原则：
合理设计和使用索引。
在关键字段的索引上，建与不建索引，查询数度相差近100倍。
差的索引和没有索引想过一样。
索引并非越多越好，因为维护需要成本。
每个表的索引在5个一下，应合理利用部分索引和联合索引。
不在结果集中的结果单一的列上建立索引。比如性别字段只有0和1两种结果，在这个字段上建立索引不会有太大的帮助。
建索引的字段结果集最好分布均匀，或者符合正态分布。
