MySQL 常用SQL优化

MySQL 常用SQL优化:

一.大批量插入数据:

1.对于load MyISAM存储引擎的表,可以通过关闭打开MyISAM表非唯一索引的更新来提升导入速度:

  例:mysql > alter table tbl_name disable keys;

     mysql > load data infile ‘/home/mysql/tbl_name.txt’ into table tbl_name;

     mysql > alter table tbl_name enable keys;

2.对于InnoDB类型的表有以下方式提高导入数据库的效率:

  (1)在导入钱执行set unique_checks=0关闭唯一性校验,在导入结束后执行set set unique_checks=1 恢复唯一性校验,可以提高
导入速度

   例:mysql > set set unique_checks=0;

      mysql > load data infile ‘/home/mysql/tbl_name.txt’ into table tbl_name;

      mysql > set set unique_checks=1;

  (2)如果使用自动提交方式,建议在导入前执行set autocommit=0关闭自动提交,导入后再执行set autocommit=1打开自动提交

   例:mysql > set autocommit=0;

      mysql > load data infile ‘/home/mysql/tbl_name.txt’ into table tbl_name;

      mysql > set autocommit=1;
二.优化Insert语句:

1.如果同时从同一客户插入很多行,尽量使用多个值表示的insert语句,这种方式将大大缩减客户端与数据库之间的连接,关闭等消耗
,是的效率比分开执行的单个insert语句快(在一些情况中几倍).

  例:insert into tset values (1,2),(1,3),(1,4)……

2.如果从不同客户插入很多行,能通过使用insert delayed语句得到更高的速度.DELAYED的含义是让insert语句马上执行,其实数据
都被放在内存队列中,并没有真正写入磁盘,这比每条语句分别插入要快得多;LOW_PRIORITY刚好相反,在所有其他用户对表的读写完
后才能插入.

3.将索引文件和数据库文件分在不同的磁盘上存放(利用建表中的选项);

4.如果进行批量插入,可以增加bukl_insert_buffer_size 变量值的方法来提高速度,但是,这只能对MyISAM表使用;

5.当从一个文本文件装载一个表时,使用LOAD DATA INFILE .这通常比使用很多INSERT语句快20倍.
三.优化GROUP BY语句:

1.如果查询包括GROUP BY但用户想要避免排序结果的消耗,则可以指定ORDER BY NULL禁止排序:

  例:mysql > explain select id ,sum(moneys) from sales2 group id 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 temporary ; Using Filesort

    1 row in set (0.00 sec)

    mysql > explain select id ,sum(moneys) from sales2 group id order by null 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 temporary

    1 row in set (0.00 sec)
从上面的例子可以看出第一个SQL语句需要进行”filesort”,而第二个SQL由于ORDER BY NULL 不需要进行”filesort”,而filesort
往往非常耗费时间.
四.优化ORDER BU语句:

   在某些情况中,MySQL可以使用一个索引来满足ORDER BY子句,而不需要额外的排序.WHERE条件和ORDER BY使用相同的索引,并且
ORDER BY的顺序和索引的顺序相同,并且ORDER BY的字段都是升序或者都是降序.

例如,下列SQL可以使用索引:

  mysql > select * from t1 order by key_part1 , key_part2,… ;

  mysql > select * from t1 where key_part1=1 order by key_part1 DESC , key_part2 DESC;

  mysql > select * from t1 ORDER BY key_part1 DESC , key_part2 DESC;

  但是在以下几种情况下则不使用索引:

  mysql > select * from t1 order by key_part1 DESC , key_part2 ASC ;

  –order by 的字段混合ASC和DESC

  mysql > select * from t1 where key2=constant order by key1;

  –用于查询行的关键字与ORDER BY中所使用的不相同

  mysql > select * from t1 order by key1 ,key2;

  –对不同的关键字使用ORDER BY
五.优化嵌套查询:

1.使用子查询可以一次性地完成很多逻辑上需要多个步骤才能完成的SQL操作,同时也可以避免事务或者表锁死,并且写起来也很容易
.但是有些情况下,子查询可以被更有效率的连接(JOIN)替代.
六.优化OR条件:

1.对于含有OR的查询子句,如果要利用索引,则OR之间的每个条件列都必须用到索引;如果没有索引,则应该考虑增加索引.
七.使用SQL提示:

1.USE INDEX

  在查询语句中表名的后面,添加USE INDEX来提供希望MySQL去参考的索引列表,就可以让MySQL不在考虑其他可用的索引

  例: mysql > select * from sales2 use index (ind_sales2_id) where id = 3 ;

      –查询sales2表时采用ind_sales2_id索引

2.IGNORE INDEX

  如果用户只是单纯地向让MySQL忽略一个或者多个索引,则可以使用IGNORE INDEX作为HINT.

  例: mysql > select * from sales2 ignore index (ind_sales2_id) where id = 3 ;

      –查询sales2表时忽略ind_sales2_id索引

3.FORCE INDEX

  为强制MySQL使用一个特定的索引,可在查询中使用FORCE INDEX 作为HINT.

  例: mysql > select * from sales2 where id > 0 ;

      –当使用FORCE INDEX进行提示时,即便使用索引的效率不是最高,MySQL还是选择使用索引,这是MySQL留给用户的一个自行选择执行计划的权利