SQL优化大全

1. 优化SQL步骤

1. 通过 show status和应用特点了解各种 SQL的执行频率

       通过 SHOW STATUS 可以提供服务器状态信息，也可以使用 mysqladmin extende d-status 命令获得。 SHOW STATUS 可以根据需要显示 session 级别的统计结果和 global级别的统计结果。

       如显示当前session： SHOW STATUS like "Com_%"; 全局级别：show global status;        以下几个参数对 Myisam 和 Innodb 存储引擎都计数：

1. Com_select 执行 select 操作的次数，一次查询只累加 1 ；

2. Com_insert 执行 insert 操作的次数，对于批量插入的 insert 操作，只累加一次 ；

3. Com_update 执行 update 操作的次数；

4. Com_delete 执行 delete 操作的次数；

        以下几个参数是针对 Innodb 存储引擎计数的，累加的算法也略有不同：

1. Innodb_rows_read select 查询返回的行数；

2. Innodb_rows_inserted 执行 Insert 操作插入的行数；

3. Innodb_rows_updated 执行 update 操作更新的行数；

4. Innodb_rows_deleted 执行 delete 操作删除的行数；

       通过以上几个参数，可以很容易的了解当前数据库的应用是以插入更新为主还 是以查询操作为主，以及各种类型的 SQL大致的执行比例是多少。对于更新操作的计 数，是对执行次数的计数，不论提交还是回滚都会累加。       对于事务型的应用，通过 Com_commit 和 Com_rollback 可以了解事务提交和回 滚的情况，对于回滚操作非常频繁的数据库，可能意味着应用编写存在问题。此外，以下几个参数便于我们了解数据库的基本情况：

1. Connections 试图连接 MySQL 服务器的次数 2. Uptime 服务器工作时间 3. Slow_queries 慢查询的次数

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

     可以通过以下两种方式定位执行效率较低的 SQL 语句：      1. 可以通过慢查询日志定位那些执行效率较低的 sql 语句，用 --log-slow-queries[=file_name] 选项启动时， mysqld 写一个包含所有执行时间超过long_query_time 秒的 SQL 语句的日志文件。可以链接到管理维护中的相关章节。      2. 使用 show processlist查看当前MYSQL的线程， 命令慢查询日志在查询结束以后才纪录，所以在应用反映执行效率出现问题的时候查 询慢查询日志并不能定位问题，可以使用 show processlist 命令查看当前 MySQL 在进行的线程，包括线程的状态，是否锁表等等，可以实时的查看 SQL 执行情况， 同时对一些锁表操作进行优化。

3. 通过EXPLAIN 分析低效 SQL的执行计划：

       通过以上步骤查询到效率低的 SQL 后，我们可以通过 explain 或者 desc 获取MySQL 如何执行 SELECT 语句的信息，包括 select 语句执行过程表如何连接和连接 的次序。

2. MySQL索引

1. mysql如何使用索引    

       索引用于快速找出在某个列中有一特定值的行。对相关列使用索引是提高SELECT 操作性能的最佳途径。        查询要使用索引最主要的条件是查询条件中需要使用索引关键字，如果是多列 索引，那么只有查询条件使用了多列关键字最左边的前缀时（前缀索引），才可以使用索引，否则 将不能使用索引。

       下列情况下， Mysql 不会使用已有的索引：       1、如果 mysql 估计使用索引比全表扫描更慢，则不使用索引。例如：如果 key_part 1均匀分布在 1 和 100 之间，下列查询中使用索引就不是很好：         SELECT * FROM table_name where key_part1 > 1 and key_part1 < 90       2、如果使用 heap 表并且 where 条件中不用＝索引列，其他 > 、 < 、 >= 、 <= 均不使 用索引（MyISAM和innodb表使用索引）；

      3、使用or分割的条件，如果or前的条件中的列有索引，后面的列中没有索引，那么涉及到的索引都不会使用。       4、如果创建复合索引，如果条件中使用的列不是索引列的第一部分；（不是前缀索引）       4、如果 like 是以％开始；       5、对 where 后边条件为字符串的一定要加引号，字符串如果为数字 mysql 会自动转 为字符串，但是不使用索引。

2. 查看索引使用情况

        如果索引正在工作， Handler_read_key 的值将很高，这个值代表了一个行被索引值读的次数，很低的值表明增加索引得到的性能改善不高，因为索引并不经常使 用。 Handler_read_rnd_next 的值高则意味着查询运行低效，并且应该建立索引补救。这个值的含义是在数据文件中读下一行的请求数。如果你正进行大量的表扫描， 该值较高。通常说明表索引不正确或写入的查询没有利用索引。        语法：          mysql> show status like 'Handler_read%';

3. 具体优化查询语句

1. 查询进行优化，应尽量避免全表扫描

    对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引

       .    尝试下面的技巧以避免优化器错选了表扫描：

       ·   使用ANALYZE TABLEtbl_name为扫描的表更新关键字分布。

       ·   对扫描的表使用FORCEINDEX告知MySQL，相对于使用给定的索引表扫描将非常耗时。

            SELECT * FROM t1, t2 FORCE INDEX (index_for_column)   WHERE t1.col_name=t2.col_name；

       ·   用--max-seeks-for-key=1000选项启动mysqld或使用SET max_seeks_for_key=1000告知优化器假设关键字扫描不会超过1,000次关键字搜索。

 1). 应尽量避免在 where 子句中对字段进行 null 值判断

        否则将导致引擎放弃使用索引而进行全表扫描，如：

        select id from t where num is null

        NULL对于大多数数据库都需要特殊处理，MySQL也不例外，它需要更多的代码，更多的检查和特殊的索引逻辑，有些开发人员完全没有意识到，创建表时NULL是默认值，但大多数时候应该使用NOT NULL，或者使用一个特殊的值，如0，-1作为默  认值。

        不能用null作索引，任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下，只要这些列中有一列含有null，该列    就会从索引中排除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。 任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。

        此例可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

         select id    from t where num=0

 2). 应尽量避免在 where 子句中使用!=或<>操作符

         否则将引擎放弃使用索引而进行全表扫描。          MySQL只有对以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE。 

         可以在LIKE操作中使用索引的情形是指另一个操作数不是以通配符（%或者_）开头的情形。例如:          SELECT id FROM  t WHERE col LIKE 'Mich%'; #  这个查询将使用索引，          SELECT id FROM  t WHERE col  LIKE '%ike';   #这个查询不会使用索引。

 3). 应尽量避免在 where 子句中使用 or 来连接条件

        否则将导致引擎放弃使用索引而进行全表扫描，如：

        select id from t where num=10 or num=20

        可以 使用UNION合并查询： select id from t where num=10 union all select id from t where num=20

          

       在某些情况下，or条件可以避免全表扫描的。

        1 .where 语句里面如果带有or条件, myisam表能用到索引， innodb不行。

           2 .必须所有的or条件都必须是独立索引

       mysql or条件可以使用索引而避免全表

 4) .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

  5).下面的查询也将导致全表扫描：

        select id from t where name like '