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

最新推荐文章于 2024-07-25 15:45:16 发布

一个人世界

最新推荐文章于 2024-07-25 15:45:16 发布

阅读量1k

点赞数

本文链接：https://blog.csdn.net/yangwenfu123/article/details/78793578

版权

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=0	
	
3.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。	
	
4.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：	
select id from t where num=10 or num=20	
可以这样查询：	
select id from t where num=10	
union all	
select id from t where num=20	
	
5.in 和 not in 也要慎用，否则会导致全表扫描，如：	
select id from t where num in(1,2,3)	
对于连续的数值，能用 between 就不要用 in 了：	
select id from t where num between 1 and 3	
	
6.下面的查询也将导致全表扫描：	
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*2	
	
8.应尽量避免在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 的执行计划如下:

    
          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)对于创建的多列索引,只要查询的条件中用到了最左边的列,索引一般就会被使用

    
          #首先按 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 的查询,后面如果是常量并且只有%号不在第一个字符,索引才可能会被使用：

    
          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 将使用索引。

    
          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 之间,下列查询中使用索引就不是很好:

    
          SELECT 
            
          *  
          FROM 
            
          table_name  
          where 
            
          key_part1 > 1  
          and 
            
          key_part1 < 90;

(2)如果使用 MEMORY/HEAP 表并且 where 条件中不使用“=”进行索引列,那么不会用到索引。heap 表只有在“=”的条件下才会使用索引。

(3)用 or 分割开的条件,如果 or 前的条件中的列有索引,而后面的列中没有索引,那么涉及到的索引都不会被用到,例如:

    
          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 列上面有索引,来看如下的执行计划:

    
          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)如果不是索引列的第一部分,如下例子:

    
          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 是以%开始,例如:

    
          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 也不能正确地用上索引,而是继续进行全表扫描。

    
          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)