mysql sql优化和索引摘录

1 . 使用连接（JOIN）来代替子查询(Sub-Queries)

 

SELECT*FROMcustomerinfo WHERE CustomerID NOT in(SELECT CustomerID FROM salesinfo)

 

SELECT*FROMcustomerinfo

LEFT JOINsalesinfo ON customerinfo.CustomerID = salesinfo.CustomerID

WHEREsalesinfo.CustomerID IS NULL

 

因为MySql不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作，但是对于大数据量的数据查询不建议使用这两者实现，因为Join虽然效率较子查询高，但他通过嵌套循环实现，效率不高，同时子查询在通过临时表实现的同时也是通过嵌套的方式实现数据匹配，所以，但数据量较大时，建议分开查询不同的表，同时在程序中实现对数据的组合和过滤

 

2. 索引建立原则

 

1） 选择唯一性索引

唯一性索引的值是唯一的，可以快速确定记录

 

2） 经常排序、分组、联合操作的字段建立索引

Orderby \ gourp by \distinct \ union 

 

3） 经常作为查询条件的字段

 

4） 限制索引数据

索引会占用磁盘空间，维护索引会消耗性能（增加修改表时会维护索引）

 

5） 字段长度小的索引快

对char(100) 的字段索引比char(10) 慢

 

6） 对大字段索引尽量使用前缀

Text \ blog 类型的字段，只是检索字段前面若干个字符

 

7） 删除不用活少用的索引

减少索引多更新操作的影响

 

8） 最左前缀原则

当建立了复合字段索引 a,b,c ，相当于分别建立了 字段a  ，字段a,b ，字段a,b,c的索引

例如：

select * table where a = ‘’ ;

select * table where a = ‘’ and b=’’;

select * table where a = ‘’ and b=’’ and c=’’ ;

都会用到索引，但是select * table where a = ‘’ and c=’’; 不会用到索引

 

索引字段必须是有序的才能快速的取出数据，建立a,b字段的索引时索引会对a进行排序，然后对b进行排序，类似order by a b

如果只查b字段会使用type:index的方式查询索引，从索引中的第一个数据一个个查询，速度不会很快

如果使用条件where a=’’ and b=’’ 的条件，会使用type:ref 的方式，因为索引中a的字段值确定后 b的值也是有序的，所以查询速度快

 

Mysql的查询优化器会处理实际执行时的参数顺序 使用最搞笑的顺序执行，例如where b=’’ and a=’’ ，优化后会使用a=’’ and b=’’的顺序查询

 

 

9） 选择区分度高的字段建立索引

区分度计算公式count(distinctcol) / count(*)  ，就是重复值越低区分度越高，具体场景要求不同，一般要求join的字段区分度在0.1以上，搜到1条需要扫描10次

 

10）     索引字段不能参与计算

例如 from_timestamp(col) = ‘2017-01-01 10:23:31’ 应该写成 col = unix_timestamp(‘2017-01-01 10:23:31’);

因为索引b+树存储的是字段值

 

11）     能扩展索引的话，不要新建

例如a字段有索引，要建立a,b的索引，只需要在原索引上修改

 

 

3. 索引失效

 

1） 使用 like ‘%xxx’

由于前面是模糊查询，所以不能利用索引的顺序，必须一个一个的查，会导致全索引扫描

 

2） 使用or时要为每个列建索引

 

3） 索引字段不能参与计算

    如果字段是字符串类型，参数要用引号

 

摘录资料：http://book.51cto.com/art/201012/240955.htm 、http://blog.csdn.net/qq_24690761/article/details/52787897 、http://blog.csdn.net/zhanglu0223/article/details/8713149