写出高效率的sql和sql的优化

1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num is null可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num=03.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。4.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num=10 or num=20可以这样查询：select id from t where num=10union allselect id from t where num=205.in 和 not in 也要慎用，否则会导致全表扫描，如：select id from t where num in(1,2,3)对于连续的数值，能用 between 就不要用 in 了：select id from t where num between 1 and 36.下面的查询也将导致全表扫描：select id from t where name like '%abc%'7.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：select id from t where num/2=100应改为:select id from t where num=100*28.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：select id from t where substring(name,1,3)='abc'--name以abc开头的id应改为:select id from t where name like 'abc%'9.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。10.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。11.不要写一些没有意义的查询，如需要生成一个空表结构：select col1,col2 into #t from t where 1=0这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：create table #t(...)12.很多时候用 exists 代替 in 是一个好的选择：select num from a where num in(select num from b)用下面的语句替换：select num from a where exists(select 1 from b where num=a.num)13.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。14.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。15.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。16.尽可能的使用 varchar 代替 char ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。17.任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。18.避免频繁创建和删除临时表，以减少系统表资源的消耗。

通过 EXPLAIN 分析低效 SQL 的执行计划
　　通过以上步骤查询到效率低的 SQL 语句后,可以通过 EXPLAIN 或者 DESC 命令获取 MySQL如何执行 SELECT 语句的信息,包括在 SELECT 语句执行过程中表如何连接和连接的顺序,比如想计算 2006 年所有公司的销售额,需要关联 sales 表和 company 表,并且对 moneys 字段做求和(sum)操作,相应 SQL 的执行计划如下:
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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)
每个列的简单解释如下:
 select_type:表示 SELECT 的类型,常见的取值有 SIMPLE(简单表,即不使用表连接或者子查询)、PRIMARY(主查询,即外层的查询)、UNION(UNION 中的第二个或者后面的查询语句)、SUBQUERY(子查询中的第一个 SELECT)等。
 table:输出结果集的表。
 type:表示表的连接类型,性能由好到差的连接类型为
system(表中仅有一行,即常量表)
const(单表中最多有一个匹配行,例如 primary key 或者 unique index)
eq_ref(对于前面的每一行,在此表中只查询一条记录,简单来说,就是多表连接中使用 primary key 或者 unique index)
ref(与 eq_ref 类似,区别在于不是使用 primary key 或者 unique index,而是使用普通的索引)
ref_or_null(与 ref 类似,区别在于条件中包含对 NULL 的查询)
index_merge(索引合并优化)
unique_subquery(in的后面是一个查询主键字段的子查询)
index_subquery (与 unique_subquery 类似,区别在于 in 的后面是查询非唯一索引字段的子查询)
range (单表中的范围查询)
index (对于前面的每一行,都通过查询索引来得到数据)
all (对于前面的每一行,都通过全表扫描来得到数据)
possible_keys:表示查询时,可能使用的索引。
key:表示实际使用的索引。
key_len:索引字段的长度。
rows:扫描行的数量。
Extra:执行情况的说明和描述。
2 MySQL 如何使用索引
　　索引用于快速找出在某个列中有一特定值的行。对相关列使用索引是提高 SELECT 操作性能的最佳途径。
　　查询要使用索引最主要的条件是查询条件中需要使用索引关键字,如果是多列索引,那么只有查询条件使用了多列关键字最左边的前缀时,才可以使用索引,否则将不能使用索引。
 
1.使用索引
在 MySQL 中,下列几种情况下有可能使用到索引：
(1)对于创建的多列索引,只要查询的条件中用到了最左边的列,索引一般就会被使用
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
#首先按 company_id,moneys 的顺序创建一个复合索引,具体如下:
mysql> create index ind_sales2_companyid_moneys on sales2(company_id,moneys);
Query OK, 1000 rows affected (0.03 sec)
Records: 1000 Duplicates: 0 Warnings: 0
 
#然后按 company_id 进行表查询,具体如下:
mysql> explain select * from sales2 where company_id = 2006\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sales2
type: ref
possible_keys: ind_sales2_companyid_moneys
208key: ind_sales2_companyid_moneys
key_len: 5
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)
 
#可以发现即便 where 条件中不是用的 company_id 与 moneys 的组合条件,索引仍然能用到,这就是索引的前缀特性。#但是如果只按 moneys 条件查询表,那么索引就不会被用到,具体如下:
mysql> explain select * from sales2 where moneys = 1\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sales2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using where
1 row in set (0.00 sec)
 
(2)对于使用 like 的查询,后面如果是常量并且只有%号不在第一个字符,索引才可能会被使用：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
mysql> explain select * from company2 where name like '%3'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: company2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using where
1 row in set (0.00 sec)
 
 
mysql> explain select * from company2 where name like '3%'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: company2
type: range
209possible_keys: ind_company2_name
key: ind_company2_name
key_len: 11
ref: NULL
rows: 103
Extra: Using where
1 row in set (0.00 sec)
#可以发现第一个例子没有使用索引,而第二例子就能够使用索引,
#区别就在于“%”的位置不同,前者把“%”放到第一位就不能用到索引,而后者没有放到第一位就使用了索引。
#另外,如果如果 like 后面跟的是一个列的名字,那么索引也不会被使用。
 
(3)如果对大的文本进行搜索,使用全文索引而不用使用 like ‘%...%’。
 
(4)如果列名是索引,使用 column_name is null 将使用索引。
1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> explain select * from company2 where name is null\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: company2
type: ref
possible_keys: ind_company2_name
key: ind_company2_name
key_len: 11
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)
 
2.存在索引但不使用索引
在下列情况下,虽然存在索引,但是 MySQL 并不会使用相应的索引。
(1)如果 MySQL 估计使用索引比全表扫描更慢,则不使用索引。例如，如果列key_part1 均匀分布在 1 和 100 之间,下列查询中使用索引就不是很好:
1
SELECT * FROM table_name where key_part1 > 1 and key_part1 < 90;
 
(2)如果使用 MEMORY/HEAP 表并且 where 条件中不使用“=”进行索引列,那么不会用到索引。heap 表只有在“=”的条件下才会使用索引。

(3)用 or 分割开的条件,如果 or 前的条件中的列有索引,而后面的列中没有索引,那么涉及到的索引都不会被用到,例如:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> show index from sales\G;
*************************** 1. row ***************************
Table: sales
Non_unique: 1
Key_name: ind_sales_year
Seq_in_index: 1
Column_name: year
210Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
1 row in set (0.00 sec)
 
从上面可以发现只有 year 列上面有索引,来看如下的执行计划:
1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> explain select * from sales where year = 2001 or country = 'China'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sales
type: ALL
possible_keys: ind_sales_year
key: NULL
key_len: NULL
ref: NULL
rows: 12
Extra: Using where
1 row in set (0.00 sec)
 可见虽然在 year 这个列上存在索引 ind_sales_year,但是这个 SQL 语句并没有用到这个索引,原因就是 or 中有一个条件中的列没有索引。
 
(4)如果不是索引列的第一部分,如下例子:
1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> explain select * from sales2 where moneys = 1\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sales2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using where
1 row in set (0.00 sec)
 可见虽然在 money 上面建有复合索引,但是由于 money 不是索引的第一列,那么在查询中这个索引也不会被 MySQL 采用。
 
(5)如果 like 是以%开始,例如:
1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> explain select * from company2 where name like '%3'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: company2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using where
1 row in set (0.00 sec)
可见虽然在 name 上建有索引,但是由于 where 条件中 like 的值的“%”在第一位了,那么MySQL 也不会采用这个索引。
 
(6) 如果列类型是字符串,那么一定记得在 where 条件中把字符常量值用引号引起来,否则的话即便这个列上有索引,MySQL 也不会用到的,因为,MySQL 默认把输入的常量值进行转换以后才进行检索。如下面的例子中 company2 表中的 name 字段是字符型的,但是 SQL 语句中的条件值是一个数值型值,因此即便在 name 上有索引, MySQL 也不能正确地用上索引,而是继续进行全表扫描。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
mysql> explain select * from company2 where name = 294\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: company2
type: ALL
possible_keys: ind_company2_name
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using where
1 row in set (0.00 sec)
 
 
mysql> explain select * from company2 where name = '294'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: company2
type: ref
possible_keys: ind_company2_name
key: ind_company2_name
key_len: 23
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)
 从上面的例子中可以看到,第一个 SQL 语句中把一个数值型常量赋值给了一个字符型的列name,那么虽然在 name 列上有索引,但是也没有用到;而第二个 SQL 语句就可以正确使用索引。