SQL优化

来源：互联网发布：js设置placeholder颜色编辑：程序博客网时间：2024/06/16 22:00

优化SQL语句的一般命令

通过所show status命令了解SQL的执行频率

mySql客户端连接成功后，可以通过show[session|global]status提供服务器状态信息，也可以使用 mysqladmin extended-statu获取这些信息。参数session指定显示当前连接的统计结果，参数global显示自数据库上次启动至今的统计结果。如果不写，默认为session。Com_xxx表示每个xxx语句执行的次数，通常我们比较关心的是以下几个参数，

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

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

Com_update：执行UPDATE操作的次数

Com_delete：执行DELETE操作的次数

以下几个参数对于所有存储引擎的表操作都会进行累计，

Innodb_rows_read：select查询返回的行数

Innodb_rows_inserted：执行INSERT操作插入的行数

Innodb_rows_updated：执行UPDATE操作更新的行数

Innodb_rows_deleted：执行DELETE操作删除的行数

通过以上几个参数，可以了解当前数据的应用是以插入更新为主还是以查询操作为主，对于更新操作的计数，是对执行次数的计数，不论提交还是回滚都会进行累加。

对于事务型的应用，通过Com_commit 和 Com_rollback 可以了解事务提交和回滚的情况，对于回滚操作非常频繁的数据库，可能意味着应用编写存在问题。

以下几个参数便于用户了解数据库的基本情况，

Connections：视图连接mySql服务器的次数

Uptime：服务器工作时间

Slow_queries：慢查询的次数

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

通过慢查询日志定位执行效率较低的SQL语句，--log-slow-queries[=file_name]选项启动时，mysqld写一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件。如何开启慢查询日志

慢查询日期在查询结束以后才记录，在应用反映执行效率出现问题的时候，慢查询日志不能定位问题，可以使用show processlist命令查看当前mySql进行的线程，包括线程状态、锁表等。

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

通过以上步骤查询到的效率低的SQL语句后，可以通过EXPLAIN或者DESC命令获取mySql如何执行select语句的信息，包括在selsect语句执行过程中，表如何连接和连接的顺序，

    explain select sum(moneys) for sales a,company b where a .compay_id=b.id and a.year=2006\G;

简单解释下参数，

select_type：表示select的类型，常见的取值有SIMPLE(不使用表连接或者子查询)，PRIMARY(主查询，外层的查询)，UNION(UNION中的第二个或者后面的查询语句)，SUBQUERY(子查询中的第一个SLECTET)；

table：输出结果集的表。

type：表示连接类型，性能由好到差的连接类型为System，（表中仅有一行，即常量表）、const(单表中最多有一个匹配行，例如 primary key 或者 unique index) 、eq_ref(对于前面的每一行，在此表只查询一条记录，多表连接中使用 primary 或者unique index)，ref(与eq_ref类似，区别是使用的普通索引)，ref_or_null(与ref类似，区别是条件中包含对null的查询)，index_merge(索引合并优化)，unique_subquery(in的后面是一个查询主键字段的子查询)，index_subquery(与unique_subquery类似，区别在于in的后面是查询非唯一索引字段的子查询)，range(单表中的查询范围)，index(对于前面每一行，都通过查询索引来得到数据)，all(对于前面的每一行都通过全表扫描来得到数据)。

possible_keys：表示查询时，可能使用的索引

key：表示实际使用的索引

key_len：索引字段的长度

row：扫描行的数量

Extra：执行情况的说明和描述

确定问题并采取相应的优化措施

经过以上步骤，基本可以确认问题出现的原因，

上图是前面查询语句的示例结果，通过观察该结果可以确认对表a的全表扫描导致效率不理想，对表a的year字段创建索引，

    create index ind_sales2_year on sales2(year);

在此进行查询，

索引问题

索引的存储分类

MyISAM存储引擎的表的数据和索引时自动分开存储的，各自是独立的一个文件；InnoDB存储引擎的表的数据和索引是存储在同一个表空间里面，但可以由多个文件组成。

mySql中索引的存储类型目前只有两种(BTREE和HASH)，具体和表的存储引擎相关：MyISAM和InnoDB存储引擎都只支持BTREE索引；MEMORY/HEAP存储引擎可以支持HASH和BTREE索引。

mySql目前不支持函数索引，但能对列前面的某一部分进行索引，例如对name字段的前4个字符进行索引，这个特性大大缩小索引文件的大小，

    create index ind_company2_name on company2(name(4))

mySql如何使用索引

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

对于创建的多列索引，只要查询的条件中用到了最左边的列，索引一帮就会被使用，按company_id和moneys的顺序创建一个复合索引，

    create index ind_sales2_companyid_moneys on sales2(company_id,moneys);

按company_id进行表查询，

    explain select * from sales2 where company+_id = 2006\G;

where条件中不是用的company_id与moneys的组合条件，索引仍然能用到，这就是索引的前缀特性，如果只按moneys条件查询表，索引不会被用到。
对于使用like的查询，后面如果是常量并且只有%号不在第一个字符时，索引才可能会被使用，

    explain select * from company2 where name like '%3'\G;

    explain select * from company2 where name like '3%'\G;

如果对大的文本进行搜索，使用全文索引，而不使用 like'%...%'，如果列名是索引，使用column_name is null将使用索引，

    explain select * from company2 where name is null\G;

存在索引而不使用索引

在下列情况下，虽然存在索引，但mySql不会使用相应的索引，

如果mySql估计在使用索引比全表扫描慢，则不使用索引，如果列key_part1均匀分布在1和100之间，下列查询使用索引效果不好，

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

如果使用memory/heap表并且where条件中不使用 "=" 进行索引列，那么不会用到索引。heap表只有在 "=" 的条件下才会使用索引。

用 or 分割的条件，如果or前的条件中的列有索引，而后面的列中没有索引，涉及到的索引都不会被用到，例如year字段有索引，country字段没有索引，

    explain select * from sales where year = 2001 or country='China'\G;

虽然在year字段中存在索引，但是SQL语句并没有使用这个索引，原因是or中有一个条件中的列没有索引。

如前面提到的，是复合索引但不是索引列的第一部分，mySql也不采用。

如果列类型是字符串，那么一定记得在where条件中把字符串常量用引号引起来，否则的话这个列上有索引，mySql也不会用到，因为mySql默认把输入的常量值进行转换时才进行检索。如，一个字段名为name，类型是字符，但是有一行的name值为879，在以该name字段的值为条件进行查询时，不能正确的使用索引，而进行全表搜索。

查看索引使用情况

如果索引在工作，Hander_read_key的值将很高，这个值表示了一行被索引值读的次数，很低的值表示增加索引得到的性能改善不高，因为索引不经常使用。

Handler_read_rnd_next的值高意味着查询运行低效，应该建立索引补救，这个值的含义是在数据文件中读取下一行的请求数。

简单实用的优化方法

定义分析表和检查表

    analyze [local|no_write_to_binlog] table tb1_name [,tab1_name] ...

本句用于分析和存储表的关键字分布，分析的结果可以使得系统得到准确的统计信息，使得SQl能够正确执行计划。如果用户感觉实际执行计划并不是预期的执行计划，执行一次分析表可能会解决问题。在分析期间，使用一个读取锁定对表进行锁定。这对于MyISAM，BDB和InnoDB表有作用。对于MyISAM表，本语句与使用myisamchk -a相当。

    CHECK TABLE tb1_name [. tb1_name] ... [option] ... option = {QUICK|FAST|MEDIUM|EXTENDED|CHANGED}

检查表的作用是检查一个或多个表是否有错误。CHECK TABLE 对MyISAM和InnoDB表有作用。CHECK TABLE也可以检查视图是否有错误，比如在视图定义中被应用的表已不存在，首先创建一个视图，

    create view sales_view3 as select * from sales3;

然后check以下这个视图，发现没有问题。然后删除掉视图依赖的表，在CHECK刚才的视图，发现报错了。

定期优化表

    optimize [local|no_write_to_binlog] table tb1_name [, tb1_name] ...

如果已经删除了表的一大部分，或者如果已经对含有可变长度行的表(varchar blob 或 text 列的表)进行了很多更改，则应使用 optimize table 命令进行表优化，该命令可以将空间碎片进行合并，可以消除由于删除或者更新造成的控件浪费，但 optimize table 命令只对MyISAM、BDB 和 InnoDB表起作用。

    optimize table sales;

常用的SQL优化

大批量插入数据，当用load命令导入数据的时候，适当的设置可以提高导入的速度，对于MyISAM存储引擎的表，可以通过以下方式快速的导入大量的数据

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

DISABLE KEYS 和 ENABLE KEYS 用来打开或者关闭MyISAM表非唯一索引的更新。在导入大量数据到一个非空的MyISAM表时，通过设置这两个命令，可以提高导入的效率。对于导入大量数据到一个空的MyISAM表，默认就是先导入数据然后创建索引，所以不需进行设置。而对于InnoDB类型的表，这种方式不能提高导入数据的速率
因为InnoDB类型表时按照主键顺序保存的，将导入的数据按照主键的顺序排列，可以有效地提高导入数据的效率。当被导入的文件按表主键顺序存储的时候比不按主键顺序存储的时候快1.12倍。

在导入数据前执行 SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行 SET UNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入效率。

如果应用使用自动提交的方式，建议在导入前执行 SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行 SET AUTOCOMMIT=1 ，也可以提高导入效率。

优化INSERT语句

如果同时从同一客户插入很多行，尽量使用多个值表INSERT语句，这种方式大大缩减客户端与数据库之间的连接、关闭等消耗，使得效率比分开执行的单个INSERT语句快。

    insert into test values(1,2), (1,3), (1,4) ...

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

将索引文件和数据文件分在不同的磁盘存放（利用建表中的选项）

如果进行批量插入，可以增加 bulk_insert_buffer_size 变量值的方法提高速度，只对MyISAM表使用。

当从一个文本文件装载一个表时，使用 LOAD DATA INFILE。比使用INSERT语句快20倍。

优化GROUP BY语句

如果查询包括 GROUP BY但用户想要避免排序结果的消耗，则可以指定 ORDER BY NULL禁止排序，普通的GROUP BY 需要进行"filesort"，而OERDER BY NULL 不需要进行"filesort"排序。

优化ORDER BY语句

在某些情况下，mySql可以使用一个索引满足 ORDER BY 子句，不需要额外的排序。WHERE 条件和 ORDER BY使用相同的索引，并且 ORDER BY 的顺序和索引顺序相同，并且 ORDER BY 字段都是升序或都是降序。下列SQL可以使用索引，

    select * from t1 order by key_part1, key_part2, ...    select * from where key_part1=1 order by key_part_1 DESC, key_part_2 DESC;    select * from t1 ORDER BY key_part1 DESC, key_part2 DESC;

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

    select * from t1 order by key_part1 DESC, keypart2 ASC; # order by 的字段混合ASC 和 DESC    select * from t1 where key2=constant order by key1; # 查询行的关键字与order by中使用的不相同    select * from t1 order by key1, key2; # 对不同的关键字使用 order by

优化嵌套查询

mySql4.1开始支持SQL的子查询，有些情况下子查询可以被更有效率的连接（JOIN）替代，

下面要从sales2表中找到那些在company2表中不存在的所有公司的信息，

    explain select * from sales2 where company_id not in (select id from company2)\G;

如果使用连接（JOIN）来完成这个查询工作，速度将会快很多，尤其当company2表对id建有索引的话，性能将会更好

    explain select * from sales2 left join company2 on sales2.company_id=company2.id where sales2.company_id is null;

查询扫描的记录范围和使用索引的情况都有了很大改善。连接（JOIN）之所以更有效率一些，是因为mySql不需要在内存中创建临时表完成这个逻辑上需要两个步骤的查询工作。

mySql如何优化 OR 条件

对于含有OR的查询子句，如果要利用索引，OR之间的每个条件列都必须用到索引，如果没有索引，应该考虑增加索引。在符合索引的列作OR操作，不能用到索引。

使用SQL提示

SQL提示（SQL HINT）是优化数据库的一个重要手段，简单来说SQL语句中加入一些人为的提示达到优化操作的目的。

    SELECT SQL_BUFFER_RESULTS * FROM...

这个语句强制mySql生成一个临时结果集，只要临时结果集生成后，所有表上的锁定均被释放。

USE INDEX

在查询语句表名后面，添加USE INDEX提供希望mySql区参考的索引列表，可以使mySql不再考虑其他可用索引，

    explain select * from sales2 use index (ind_sales2_id) where id=3;

IGNORE INDEX

如果用户单纯想让mySql忽略一个或者多个索引，可以使用 IGNORE INDEX作为HINT，

    explain select * from sales2 ignore index (ind_sales2_id) where id=3;

FORCE INDEX

强制mySql使用一个特定的索引，可在查询中使用 FROCE INDEX 作为HINT，例如当不强制使用缩印的时候，因为id的值都是大于0，mySql会默认进行全表扫描，而不使用索引，

    explain select * from sales2 where id>0;

当使用FROCE IDNEX进行提示时，即便是用索引的效率不是最高，mySql还是选择使用了索引。

阅读全文

0 0

js设置placeholder颜色

原创粉丝点击