SQL优化

Mysql索引类型

1.B-Tree索引：最常见的

2.HASH索引:只有Memory引擎支持

3.R-Tree索引：空间索引是MyISAM的特殊索引，主要用于地理空间数据

4.Full-text（全文索引）：是MyISAM的特殊索引，主要用于全文索引

比较hash和b-tree

1. hash索引查找数据基本上能一次定位数据，当然有大量碰撞的话性能也会下降。而btree索引就得在节点上挨着查找了，很明显在数据精确查找方面hash索引的效率是要高于btree的；

2.那么不精确查找呢，也很明显，因为hash算法是基于等值计算的，所以对于“like”等范围查找hash索引无效，不支持；

3.对于btree支持的联合索引的最优前缀，hash也是无法支持的，联合索引中的字段要么全用要么全不用。提起最优前缀居然都泛起迷糊了，看来有时候放空得太厉害；

4. hash不支持索引排序，索引值和计算出来的hash值大小并不一定一致

SQL优化

要判断sql是否需要优化时，可以通过慢查询将查询缓慢的sql语句记录在日志文件中。但是慢查询会严重影响数据库的性能，当不分析时，需要将慢查询关掉。

慢查询： SHOW VARIABLES LIKE '%quer%'

到当前log_slow_queries状态为OFF,说明当前并没有开启慢查询.

开启慢查询,操作如下:

Linux下找到mysql的配置文件my.ini,在mysqld下方加入慢查询的配置语句(注意:一定要在[mysqld]下的下方加入)

EXPLAIN分析SQL执行

当一个sql查询语句被写出来之后，其实你的工作只完成了一小半，接下来更重要的工作是评估你自己写的sql的质量与效率。mysql为我们提供了很有用的辅助武器explain，它向我们展示了mysql接收到一条sql语句的执行计划。根据explain返回的结果我们便可以知道我们的sql写的怎么样，是否会造成查询瓶颈，同时根据结果不断的修改调整查询语句，从而完成sql优化的过程。

虽然 explain返回的结果项很多，这里我们只关注三种，分别是type，key，rows。其中key表明的是这次查找中所用到的索引，rows是指这次查找数据所扫描的行数（这里可以先这样理解，但实际上是内循环的次数）。

它们分别是all,index,range,ref,eq_ref，const/system。从左到右，它们的效率依次是增强的

1. all  全表扫描

 EXPLAIN SELECT *  FROM account WHERE

2. index  索引全扫描

EXPLAIN  SELECT  title  FROM  account

3. range 索引范围扫描 常见<，>,between

EXPLAIN  SELECT  *  FROM  account where accountId>10

4. ref 使用非唯一扫描或唯一索引的前缀扫描

EXPLAIN  SELECT  *  FROM  account  where  partyId=3

(partyId不唯一)常见的leftjoin

5. eq_ref 类似于ref,唯一索引,对于索引值表中只有唯一的一条记录（多表中使用外键或者唯一键进行条件关联）

EXPLAIN  SELECT  *  FROM  account  a, account_record  b  where  a.ccountId=b.accountId

6. const/system 单表中的主键或者唯一键查询

EXPLAIN  SELECT  title  FROM  account where accountId=10

7. null 不用访问数据库既能得到结果

sql优化建立索引的几个准则

    1、合理的建立索引能够加速数据读取效率，不合理的建立索引反而会拖慢数据库的响应速度。

    2、索引越多，更新数据的速度越慢。

    3、尽量在采用MyIsam作为引擎的时候使用索引（因为MySQL以BTree存储索引），而不是InnoDB。但MyISAM不支持事物。默认存储引擎为Inndb.Mylsam适合大量的查询。

    4、当你的程序和数据库结构/SQL语句已经优化到无法优化的程度，而程序瓶颈并不能顺利解决，那就是应该考虑使用诸如memcached这样的分布式缓存系统的时候了。

    5、习惯和强迫自己用EXPLAIN来分析你SQL语句的性能。

sql的方法：

1.count优化

MylSAM的count较快的条件是：没有where条件。

a.EXPLAIN SELECT count(*) FROM wechat_user WHERE wechat_user_id>12;

b.EXPLAIN SELECT (SELECT COUNT(*) FROM wechat_user)- COUNT(*) FROM wechat_user WHERE wechat_user_id<12;

a语句当行数超过11行的时候需要扫描的行数比b语句要多， b语句扫描了6行，此种情况下，b语句比a语句更有效率。当没有where语句的时候直接select count(*) from wechat_user这样会更快，因为mysql总是知道表的行数

2.避免使用不兼容的数据类型，尽量使用数字型字段。

一部分开发人员和数据库管理人员喜欢把包含数值信息的字段，设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接回逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

3.索引字段上避免使用函数。

 在查询时候使用函数会使索引失效，例如

SELECT * FROM T1 WHERE NAME LIKE ‘%L%’

SELECT * FROM T1 WHERE SUBSTING(NAME,2,1)=’L’

如果要使用like查询，也要尽量使用左like,这样还可以使用索引。例如

SELECT * FROM T1 WHERE NAME LIKE ‘L%’

4.避免使用！=或者<>,is null或者is not null,in,not in 等操作符。

5.合理使用exists,not exists字句。

1.SELECT SUM(T1.C1) FROM T1 WHERE (SELECT COUNT(*)FROM T2 WHERE T2.C2=T1.C2>0)

2.SELECT SUM(T1.C1) FROM T1WHERE EXISTS(SELECT * FROM T2 WHERE T2.C2=T1.C2)

两者产生相同的结果，但是后者的效率显然要高于前者。

6.能使用between就不要使用in。

因为in会进行全表扫描。

7.查询结果少用通配符*

8.尽量不要使用select into语句。导致表锁定，其他用户无法访问该表。

9.使用update的建议

a) 尽量不要修改主键字段。

b) 当修改VARCHAR型字段时，尽量使用相同长度内容的值代替。

c) 尽量最小化对于含有UPDATE触发器的表的UPDATE操作。

d) 避免UPDATE将要复制到其他数据库的列。

e) 避免UPDATE建有很多索引的列。

f) 避免UPDATE在WHERE子句条件中的列。

10.能使用union all就不要使用union

union会对返回的数据进行去重和重新排序，因此比较耗费性能。

11.程序中一次性插入多条语句，最好写出一条

例如：insert into person(name,age) values(‘xboy’, 14), (‘xgirl’, 15),(‘nia’, 19)

12.在选择栏位上放置索引没有意义，应该在选择条件上放置索引，比如where,order by

13.尽量不要使用数据库游标

14.关于大数据分页优化

http://ariyue.iteye.com/blog/553541