简谈SQL优化

前言：在入门mysql之后，对mysql的基本内容有所了解时，随着越来越多的使用mysql进行数据操作，很多同学会发现单纯地得到正确的所需的数据集已经不能满足实际项目开发，尤其是在所操作的数据集较大，优化成了开发人员的关注点。（一个复杂的sql查询语句花费1小时与优化后只花费10分钟，给人的体验感不要太强烈！）

一、学会分析sql的执行计划

通过explain分析低效的sql执行计划

格式：explain sql语句;

此处借鉴，大家通过这个http://blog.csdn.net/ustczyy/article/details/20547355 ，可以对explain结果有个简单认识。

mysql> explain select sum(moneys) from sales a,company b where a.company_id = b.id and a.year
= 2006\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: b
type: ref
possible_keys: ind_company_id
key: ind_company_id
key_len: 5
ref: sakila.a.company_id
rows: 1
Extra: Using where; Using index
2 rows in set (0.00 sec)

下面针对type进行详细的讲解，常见类型如下，从左到右性能越来越好

ALL--->index--->range--->ref--->eq_ref--->const/system--->NULL

(1) type = ALL,属于全表扫描

例如：mysql> explain select * from tableA where age>12;(age为未加索引的普通列)

(2) type = index,属于索引全扫描

例如：mysql> explain select merchantId from tableA;(merchantId为加索引的列)

(3) type = range,属于索引范围扫描，常用于索引列的<,<=,>,>=,between等操作

例如：mysql> explain select * from tableA where merchantId>200;(merchantId为加索引的列)

(4) type = ref,多用于非唯一索引的等值查询或join操作

例如：mysql>explain select * from tableA where merchantId=200;(merchantId为加非唯一索引的列)

或者 mysql>explain select a.*, b.* from tableA a,tableB b where a.merchantId=b.merchantId;(merchantId为加非唯一索引的列)

(5) type = eq_ref,类似ref,区别在于使用的是唯一索引（即单值匹配）

(6) type = const/system,单表中最多有一条匹配行

例如：mysql>explain select * (select * from tableA where id = 3);

(7) type = NULL,表示mysql不用访问表或者索引就能得到结果

例如：mysql>explain select 1 from tableA where 1;

 

二、索引的使用

1.索引的分类

索引是在mysql的存储引擎层实现的，常用的存储引擎：MyISAM、InnoDB、Memory。mysql目前支持以下4种索引：

B-Tree索引：大部分存储引擎都支持

Hash索引：只有Memory支持，适用于键值对的查询，且效率比B-Tree索引的查询效率更快；不适用于<、>、<=、>=等范围操作，且只有在“=”条件下才会使用索引

R-Tree索引：空间索引是MyISAM的一个特殊索引类型，用于地理空间数据类型（不常用）

Full-Text索引:全文索引，也是MyISAM的一个特殊索引类型

2.索引的适用情况

索引的使用会提升数据查询性能，但是高性能的代价就是需要物理空间以及维护。因此，如何选择合适的索引很重要。下面所述的为B-Tree索引：

(1) 对常用于搜索显示的列设置索引，会加快查询速度

(2) 对使用where的列使用索引，加快判断速度

(3) 对使用排序、分组的列使用索引，加快速度（由于索引已经排好序）

(4) 表中的外键设置索引，在进行表连接时会加快速度

3.加速器避开索引的情况

(1) 以%开头的like模糊查询，这种情况是不能使用B-Tree索引的；但第一个字符不为通配符事会使用索引

(2) 数据类型进行隐式转化时不会使用索引，特别当列类型为字符串型，在where中将字符常量用""括起来

(3) 使用or的语句，只有当前后两个条件都用到索引，设置的索引才起作用

(4) 复合索引不满足最左原则时，不会使用该复合索引。复合索引最多包含16个列，对于最左原则，举例如下：一个复合索引(A,B,C),只有A,AB,ABC时会使用复合索引，对于B,C,BC均不会使用复合索引

(5) where条件中使用!=比较符或者函数不会使用索引

 

注：这些是在开发中遇到的，并且看了一些书籍总结的，可能不够详细，有兴趣的同学可以重点学习。另外，对于有误的地方，希望大家不吝赐教~