mysql------SQL语句优化

[导读]SQL语句优化 3.1 优化SQL语句的一般步骤 3.1.1 通过show status命令了解各种SQL的执行频率。格式： mysql>show [session|global]sta

SQL语句优化 3.1 优化SQL语句的一般步骤 3.1.1 通过show status命令了解各种SQL的执行频率。

格式： mysql>show [session|global]status;
其中： session（默认）表示当前连接，global表示自数据库启动至今

mysql>show status;
mysql>show global  status;
mysql>show status like 'Com_%';
mysql>show global status like 'Com_%';

3.1.2 定位执行效率较低的SQL语句

explain select * from table where id=1000;
desc select * from table where id =1000;

3.1.3 通过EXPLAIN分析较低效SQL的执行计划

3.2 索引问题索引是数据库优化中最常见也是最重要的手段之一，通过索引通常可以帮助用户解决大多数的SQL性能问题。 3.2.1 索引的存储分类 MyISAM存储引擎的表的数据和索引是自动分开存储的，各自是独一的文件；InnoDB存储引擎的表的数据和索引是存储在同一个表空间里面，但可以有多个文件组成。 MySQL目前不支持函数索引，但是能对列的前面某一部分进行索引，例如name字段，可以只取name的前4个字符进行索引，这个特性可以大大缩小索引文件的大小，用户在设计表结构的时候也可以对文本列根据此特性进行灵活设计。

mysql>create index ind_company2_name on companny2(name(4));
    其中company表名ind_company2_name索引名

3.2.2MySQL如何使用索引索引用于快速找出在某个列中有一特定值的行。对相关列使用索引是提高select操作性能的最佳途径。（一）.使用索引

（1）对于创建的多列索引，只要查询的条件中用到最左边的列，索引一般就会被使用。如下创建一个复合索引。

mysql>create index ind_sales2_com_mon onsales2(company_id,moneys);
然后按company_id进行查询，发现使用到了复合索引
mysql>explain select * from sales2 where company_id=2006G
使用下面的查询就没有使用到复合索引。
mysql>explain select * from sales2 where moneys=1G

（2）使用like的查询，后面如果是常量并且只有%号不在第一个字符，索引才可能会被使用，如下：

（3）如果对大的文本进行搜索，使用全文索引而不使用like“%…%”. 如果列名是索引，使用column_name is null将使用索引。如下
（二）.存在索引但不使用索引

（1）如果MySQL估计使用索引比全表扫描更慢，则不使用索引。例如如果列key_part1均匀分布在1到100之间，查询时使用索引就不是很好

msyql>select * from table_name where key_part1>1 and key_part<90;

（2）如果使用MEMORY/HEAP表并且where条件中不使用“=”进行索引列，那么不会用到索引。Heap表只有在“=”的条件下会使用索引。
（3）用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

从上面可以发现只有year列上面有索引。来看如下的执行计划。

（4）如果不是索引列的第一部分，如下例子：可见虽然在money上面建有复合索引，但是由于money不是索引的第一列，那么在查询中这个索引也不会被MySQL采用。

（5）如果like是以%开始，可见虽然在name上面建有索引，但是由于条件中like的值的“%”在第一位了，那么MySQL也会采用这个索引。

（6）如果列类型是字符串，但在查询时把一个数值型常量赋值给了一个字符型的列名name，那么虽然在name列上有索引，但是也没有用到。

3.2.3查看索引使用情况如果索引正在工作，Handler_read_key的值将很高，这个值带了一个行被索引值读的次数。 Handler_read_rnd_next的值高则意味着查询运行低效，并且应该建立索引补救。
3.3 两个简单使用的优化方法对于大多数开发人员来说，可能只希望掌握一些简单实用的优化方法，对于更多更复杂的优化，更倾向于交给专业DBA来做。 3.3.1定期分析表和检查表

ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE  tal_name [,tbl_name] ...

本语句用于分析和存储表的关键字分析，分析的结果将可以使得系统得到准确的统计信息，使得SQL能够生成正确的执行计划。

分析表的语法如下：（检查一个或多个表是否有错误）

3.3.2定期优化表

优化表的语法格式：

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tb1_name [,tb1_name]

如果已经删除了表的一大部分，或者如果已经对含有可变长度行的表进行了很多改动，则需要做定期优化。这个命令可以将表中的空间碎片进行合并，但是此命令只对MyISAM,BDB和InnoDB表起作用。

3.4 常用SQL的优化 3.4.1 大批量插入数据

当用load命令导入数据的时候，适当设置可以提高导入的速度。

select name from tb1 infile into "/tmp/test.txt";
load data infile "/tmp/test.txt" into table tb1(name);

对于MyISAM存储引擎的表，可以通过以下方式快速的导入大量的数据。

ALTER TABLE tb1_name DISABLE KEYS
loading the data
ALTER TABLE tb1_name ENABLE KEYS

DISABLE KEYS 和 ENABLE KEYS 用来打开或关闭MyISAM表非唯一索引的更新，可以提高速度，
注意：对InnoDB表无效。
注意：对InnoDB表无效。

（2）关闭唯一性校验可以提高导入效率
在导入数据前先执行set unique_checks=0,关闭唯一性校验，在导入结束后执行set unique_checks=1,恢复唯一性校验，可以提高导入效率。

（1）针对InnoDB类型表数据导入的优化
因为InnoDB表的按照主键顺序保存的，所以将导入的数据主键的顺序排列，可以有效提高导入数据的效率。

（3）关闭自动提交可以提高导入效率
在导入数据前先执行set autocommit=0,关闭自动提交事务，在导入结束后执行set autocommit=1,恢复自动提交，可以提高导入效率。

3.4.2 优化insert语句尽量使用多个值表的insert语句，这样可以大大缩短客户与数据可的连接，关闭等损耗。可以使用insert delayed （马上执行）语句得到更高的效率。将索引文件和数据文件分别存放不同的磁盘上。可以增加bulk_insert_buffer_size变量值的方法来提高速度，但是只对MyISAM表使用。当从一个文件中装载一个表时，使用LOAD DATA INFILE。这个通常比使用很多insert语句快20倍。 3.4.3 优化group by语句如果查询包含group by但用户想要避免排序结果的损耗，则可以使用order by null 来禁止排序：
如下没有使用order by null来禁止排序

如下使用order by null的效果：
3.4.4 优化order by语句

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

3.4.5 优化嵌套查询

下面是采用嵌套查询的效果（可以使用更有效的链接查询JOIN替代）。