精华内容
参与话题
问答
  • mySQL查询优化

    2012-04-21 22:40:34
     如同其它学科,优化查询性能很大程度上决定于开发者的直觉。幸运的是,像MySQL这样的数据库自带有一些协助工具。本文简要讨论诸多工具之三种:使用索引,使用EXPLAIN分析查询以及调整MySQL的内部配置。  一、...

    糟糕的SQL查询语句可对整个应用程序的运行产生严重的影响,其不仅消耗掉更多的数据库时间,且它将对其他应用组件产生影响。

      如同其它学科,优化查询性能很大程度上决定于开发者的直觉。幸运的是,像MySQL这样的数据库自带有一些协助工具。本文简要讨论诸多工具之三种:使用索引,使用EXPLAIN分析查询以及调整MySQL的内部配置。

      一、使用索引
     
      MySQL允许对数据库表进行索引,以此能迅速查找记录,而无需一开始就扫描整个表,由此显著地加快查询速度。每个表最多可以做到16个索引,此外MySQL还支持多列索引及全文检索。

      给表添加一个索引非常简单,只需调用一个CREATE INDEX命令并为索引指定它的域即可。列表A给出了一个例子:


    mysql> CREATE INDEX idx_username ON users(username);
    Query OK, 1 row affected (0.15 sec)
    Records: 1  Duplicates: 0  Warnings: 0

      列表 A

      这里,对users表的username域做索引,以确保在WHERE或者HAVING子句中引用这一域的SELECT查询语句运行速度比没有添加索引时要快。通过SHOW INDEX命令可以查看索引已被创建(列表B)。

      
     
      列表 B

      值得注意的是:索引就像一把双刃剑。对表的每一域做索引通常没有必要,且很可能导致运行速度减慢,因为向表中插入或修改数据时,MySQL不得不每次都为这些额外的工作重新建立索引。另一方面,避免对表的每一域做索引同样不是一个非常好的主意,因为在提高插入记录的速度时,导致查询操作的速度减慢。这就需要找到一个平衡点,比如在设计索引系统时,考虑表的主要功能(数据修复及编辑)不失为一种明智的选择。

      二、优化查询性能

      在分析查询性能时,考虑EXPLAIN关键字同样很管用。EXPLAIN关键字一般放在SELECT查询语句的前面,用于描述MySQL如何执行查询操作、以及MySQL成功返回结果集需要执行的行数。下面的一个简单例子可以说明(列表C)这一过程:

      
     
      列表 C

      这里查询是基于两个表连接。EXPLAIN关键字描述了MySQL是如何处理连接这两个表。必须清楚的是,当前设计要求MySQL处理的是 country表中的一条记录以及city表中的整个4019条记录。这就意味着,还可使用其他的优化技巧改进其查询方法。例如,给city表添加如下索引(列表D):


    mysql> CREATE INDEX idx_ccode ON city(countrycode);
    Query OK, 4079 rows affected (0.15 sec)
    Records: 4079  Duplicates: 0  Warnings: 0

      列表 D

      现在,当我们重新使用EXPLAIN关键字进行查询时,我们可以看到一个显著的改进(列表E):

      

      列表 E

      在这个例子中,MySQL现在只需要扫描city表中的333条记录就可产生一个结果集,其扫描记录数几乎减少了90%!自然,数据库资源的查询速度更快,效率更高。

    三、调整内部变量

      MySQL是如此的开放,所以可轻松地进一步调整其缺省设置以获得更优的性能及稳定性。需要优化的一些关键变量如下:

      改变索引缓冲区长度(key_buffer)

      一般,该变量控制缓冲区的长度在处理索引表(读/写操作)时使用。MySQL使用手册指出该变量可以不断增加以确保索引表的最佳性能,并推荐使用与系统内存25%的大小作为该变量的值。这是MySQL十分重要的配置变量之一,如果你对优化和提高系统性能有兴趣,可以从改变 key_buffer_size变量的值开始。

      改变表长(read_buffer_size)

      当一个查询不断地扫描某一个表,MySQL会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。如果你认为连续扫描进行得太慢,可以通过增加该变量值以及内存缓冲区大小提高其性能。

      设定打开表的数目的最大值(table_cache)

      该变量控制MySQL在任何时候打开表的最大数目,由此能控制服务器响应输入请求的能力。它跟max_connections变量密切相关,增加 table_cache值可使MySQL打开更多的表,就如增加max_connections值可增加连接数一样。当收到大量不同数据库及表的请求时,可以考虑改变这一值的大小。

      对缓长查询设定一个时间限制(long_query_time)

      MySQL带有“慢查询日志”,它会自动地记录所有的在一个特定的时间范围内尚未结束的查询。这个日志对于跟踪那些低效率或者行为不端的查询以及寻找优化对象都非常有用。long_query_time变量控制这一最大时间限定,以秒为单位。

      以上讨论并给出用于分析和优化SQL查询的三种工具的使用方法,以此提高你的应用程序性能。使用它们快乐地优化吧!

    使用EXPLAIN语句检查SQL语句

      当你在一条SELECT语句前放上关键词EXPLAIN,MySQL解释它将如何处理SELECT,提供有关表如何联结和以什么次序联结的信息。

      借助于EXPLAIN,你可以知道你什么时候必须为表加入索引以得到一个使用索引找到记录的更快的SELECT。

    EXPLAIN tbl_name

    or  EXPLAIN SELECT select_options
    EXPLAIN tbl_name是DESCRIBE tbl_name或SHOW COLUMNS FROM tbl_name的一个同义词。

      从EXPLAIN的输出包括下面列:

      ·table
      输出的行所引用的表。

      · type
      联结类型。各种类型的信息在下面给出。
      不同的联结类型列在下面,以最好到最差类型的次序:
    system const eq_ref ref range index ALL possible_keys

      · key
      key列显示MySQL实际决定使用的键。如果没有索引被选择,键是NULL。

      · key_len
      key_len列显示MySQL决定使用的键长度。如果键是NULL,长度是NULL。注意这告诉我们MySQL将实际使用一个多部键值的几个部分。

      · ref
      ref列显示哪个列或常数与key一起用于从表中选择行。

      · rows
      rows列显示MySQL相信它必须检验以执行查询的行数。

      ·Extra
      如果Extra列包括文字Only index,这意味着信息只用索引树中的信息检索出的。通常,这比扫描整个表要快。如果Extra列包括文字where used,它意味着一个WHERE子句将被用来限制哪些行与下一个表匹配或发向客户。
     
      通过相乘EXPLAIN输出的rows行的所有值,你能得到一个关于一个联结要多好的提示。这应该粗略地告诉你MySQL必须检验多少行以执行查询。

      例如,下面一个全连接:


    mysql> EXPLAIN SELECT student.name From student,pet 
    -> WHERE student.name=pet.owner;

      其结论为:

    +---------+------+---------------+------+---------+------+------+------------+
    | table   | type | possible_keys | key  | key_len | ref  | rows | Extra      |
    +---------+------+---------------+------+---------+------+------+------------+
    | student | ALL  | NULL          | NULL |    NULL | NULL |   13 |            |
    | pet     | ALL  | NULL          | NULL |    NULL | NULL |    9 | where used |
    +---------+------+---------------+------+---------+------+------+------------+

      SELECT 查询的速度

      总的来说,当你想要使一个较慢的SELECT ... WHERE更快,检查的第一件事情是你是否能增加一个索引。在不同表之间的所有引用通常应该用索引完成。你可以使用EXPLAIN来确定哪个索引用于一条SELECT语句。

      一些一般的建议:

      ·为了帮助MySQL更好地优化查询,在它已经装载了相关数据后,在一个表上运行myisamchk --analyze。这为每一个更新一个值,指出有相同值地平均行数(当然,对唯一索引,这总是1。)

      ·为了根据一个索引排序一个索引和数据,使用myisamchk --sort-index --sort-records=1(如果你想要在索引1上排序)。如果你有一个唯一索引,你想要根据该索引地次序读取所有的记录,这是使它更快的一个好方法。然而注意,这个排序没有被最佳地编写,并且对一个大表将花很长时间!

      MySQL怎样优化WHERE子句


      where优化被放在SELECT中,因为他们最主要在那里使用里,但是同样的优化被用于DELETE和UPDATE语句。

      也要注意,本节是不完全的。MySQL确实作了许多优化而我们没有时间全部记录他们。

      由MySQL实施的一些优化列在下面:

      1、删除不必要的括号:
    ((a AND b) AND c OR (((a AND b) AND (c AND d))))
    -> (a AND b AND c) OR (a AND b AND c AND d)

      2、常数调入:
    (a-> b>5 AND b=c AND a=5

      3、删除常数条件(因常数调入所需):
    (B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)
    -> B=5 OR B=6

      4、索引使用的常数表达式仅计算一次。

      5、在一个单个表上的没有一个WHERE的COUNT(*)直接从表中检索信息。当仅使用一个表时,对任何NOT NULL表达式也这样做。

      6、无效常数表达式的早期检测。MySQL快速检测某些SELECT语句是不可能的并且不返回行。

      7、如果你不使用GROUP BY或分组函数(COUNT()、MIN()……),HAVING与WHERE合并。

      8、为每个子联结(sub join),构造一个更简单的WHERE以得到一个更快的WHERE计算并且也尽快跳过记录。

      9、所有常数的表在查询中的任何其他表前被首先读出。一个常数的表是:

      ·一个空表或一个有1行的表。

      ·与在一个UNIQUE索引、或一个PRIMARY KEY的WHERE子句一起使用的表,这里所有的索引部分使用一个常数表达式并且索引部分被定义为NOT NULL。

      所有下列的表用作常数表

     mysql> SELECT * FROM t WHERE primary_key=1;
    mysql> SELECT * FROM t1,t2
    WHERE t1.primary_key=1 AND t2.primary_key=t1.id;

      10、对联结表的最好联结组合是通过尝试所有可能性来找到:(。如果所有在ORDER BY和GROUP BY的列来自同一个表,那么当廉洁时,该表首先被选中。

      11、如果有一个ORDER BY子句和一个不同的GROUP BY子句,或如果ORDER BY或GROUP BY包含不是来自联结队列中的第一个表的其他表的列,创建一个临时表。

      12、如果你使用SQL_SMALL_RESULT,MySQL将使用一个在内存中的表。

      13、因为DISTINCT被变换到在所有的列上的一个GROUP BY,DISTINCT与ORDER BY结合也将在许多情况下需要一张临时表。

      14、每个表的索引被查询并且使用跨越少于30% 的行的索引。如果这样的索引没能找到,使用一个快速的表扫描。

      15、在一些情况下,MySQL能从索引中读出行,甚至不咨询数据文件。如果索引使用的所有列是数字的,那么只有索引树被用来解答查询。

      16、在每个记录被输出前,那些不匹配HAVING子句的行被跳过。

      下面是一些很快的查询例子


     mysql> SELECT COUNT(*) FROM tbl_name;
    mysql> SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name;
    mysql> SELECT MAX(key_part2) FROM tbl_name
               WHERE key_part_1=constant;
    mysql> SELECT ... FROM tbl_name
               ORDER BY key_part1,key_part2,... LIMIT 10;
    mysql> SELECT ... FROM tbl_name
               ORDER BY key_part1 DESC,key_part2 DESC,... LIMIT 10;

      下列查询仅使用索引树就可解决(假设索引列是数字的):


     mysql> SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val;
    mysql> SELECT COUNT(*) FROM tbl_name
               WHERE key_part1=val1 AND key_part2=val2;
    mysql> SELECT key_part2 FROM tbl_name GROUP BY key_part1;

      下列查询使用索引以排序顺序检索,不用一次另外的排序:

     mysql> SELECT ... FROM tbl_name ORDER BY key_part1,key_part2,...
    mysql> SELECT ... FROM tbl_name ORDER BY key_part1 DESC,key_part2 DESC,...

    MySQL怎样优化LEFT JOIN

    在MySQL中,A LEFT JOIN B实现如下:

    1、表B被设置为依赖于表A。

    2、表A被设置为依赖于所有用在LEFT JOIN条件的表(除B外)。

    3、所有LEFT JOIN条件被移到WHERE子句中。

    4、进行所有标准的联结优化,除了一个表总是在所有它依赖的表之后被读取。如果有一个循环依赖,MySQL将发出一个错误。

    5、进行所有标准的WHERE优化。

    6、如果在A中有一行匹配WHERE子句,但是在B中没有任何行匹配LEFT JOIN条件,那么在B中生成所有列设置为NULL的一行。

    7、如果你使用LEFT JOIN来找出在某些表中不存在的行并且在WHERE部分你有下列测试:column_name IS NULL,这里column_name 被声明为NOT NULL的列,那么MySQL在它已经找到了匹配LEFT JOIN条件的一行后,将停止在更多的行后寻找(对一特定的键组合)。

    MySQL怎样优化LIMIT

    在一些情况中,当你使用LIMIT #而不使用HAVING时,MySQL将以不同方式处理查询。

    1、如果你用LIMIT只选择一些行,当MySQL一般比较喜欢做完整的表扫描时,它将在一些情况下使用索引。

    2、如果你使用LIMIT #与ORDER BY,MySQL一旦找到了第一个 # 行,将结束排序而不是排序整个表。

    3、当结合LIMIT #和DISTINCT时,MySQL一旦找到#个唯一的行,它将停止。

    4、在一些情况下,一个GROUP BY能通过顺序读取键(或在键上做排序)来解决,并然后计算摘要直到键值改变。在这种情况下,LIMIT #将不计算任何不必要的GROUP。

    5、只要MySQL已经发送了第一个#行到客户,它将放弃查询。

    6、LIMIT 0将总是快速返回一个空集合。这对检查查询并且得到结果列的列类型是有用的。

    7、临时表的大小使用LIMIT #计算需要多少空间来解决查询。

    记录转载和修改的速度

    很多时候关心的是优化 SELECT 查询,因为它们是最常用的查询,而且确定怎样优化它们并不总是直截了当。相对来说,将数据装入数据库是直截了当的。然而,也存在可用来改善数据装载操作效率的策略,其基本原理如下:

    ·成批装载较单行装载更快,因为在装载每个记录后,不需要刷新索引高速缓存;可在成批记录装入后才刷新。

    ·在表无索引时装载比索引后装载更快。如果有索引,不仅必须增加记录到数据文件,而且还要修改每个索引以反映增加了的新记录。

    ·较短的 SQL 语句比较长的 SQL 语句要快,因为它们涉及服务器方的分析较少,而且还因为将它们通过网络从客户机发送到服务器更快。

    这些因素中有一些似乎微不足道(特别是最后一个因素),但如果要装载大量的数据,即使是很小的因素也会产生很大的不同结果。

    INSERT查询的速度

    插入一个记录的时间由下列组成:

    ·连接:(3)

    ·发送查询给服务器:(2)

    ·分析查询:(2)

    ·插入记录:(1 x 记录大小)

    ·插入索引:(1 x 索引)

    ·关闭:(1)

    这里的数字有点与总体时间成正比。这不考虑打开表的初始开销(它为每个并发运行的查询做一次)。

    表的大小以N log N (B 树)的速度减慢索引的插入。

    加快插入的一些方法:

    ·如果你同时从同一客户插入很多行,使用多个值表的INSERT语句。这比使用分开INSERT语句快(在一些情况中几倍)。

    ·如果你从不同客户插入很多行,你能通过使用INSERT DELAYED语句得到更高的速度。

    ·注意,用MyISAM,如果在表中没有删除的行,能在SELECT:s正在运行的同时插入行。

    ·当从一个文本文件装载一个表时,使用LOAD DATA INFILE。这通常比使用很多INSERT语句快20倍。

    ·当表有很多索引时,有可能多做些工作使得LOAD DATA INFILE更快些。使用下列过程:

    1、有选择地用CREATE TABLE创建表。例如使用mysql或Perl-DBI。

    2、执行FLUSH TABLES,或外壳命令mysqladmin flush-tables。

    3、使用myisamchk --keys-used=0 -rq /path/to/db/tbl_name。这将从表中删除所有索引的使用。

    4、用LOAD DATA INFILE把数据插入到表中,这将不更新任何索引,因此很快。

    5、如果你有myisampack并且想要压缩表,在它上面运行myisampack。

    6、用myisamchk -r -q /path/to/db/tbl_name再创建索引。这将在将它写入磁盘前在内存中创建索引树,并且它更快,因为避免大量磁盘寻道。结果索引树也被完美地平衡。

    7、执行FLUSH TABLES,或外壳命令mysqladmin flush-tables。

    这个过程将被构造进在MySQL的某个未来版本的LOAD DATA INFILE。

    ·你可以锁定你的表以加速插入


    mysql> LOCK TABLES a WRITE;
    mysql> INSERT INTO a VALUES (1,23),(2,34),(4,33);
    mysql> INSERT INTO a VALUES (8,26),(6,29);
    mysql> UNLOCK TABLES;

     


      主要的速度差别是索引缓冲区仅被清洗到磁盘上一次,在所有INSERT语句完成后。一般有与有不同的INSERT语句那样夺的索引缓冲区清洗。如果你能用一个单个语句插入所有的行,锁定就不需要。锁定也将降低多连接测试的整体时间,但是对某些线程最大等待时间将上升(因为他们等待锁)。例如:

    thread 1 does 1000 inserts
    thread 2, 3, and 4 does 1 insert
    thread 5 does 1000 inserts
     


    如果你不使用锁定,2、3和4将在1和5前完成。如果你使用锁定,2、3和4将可能不在1或5前完成,但是整体时间应该快大约40%。因为INSERT, UPDATE和DELETE操作在MySQL中是很快的,通过为多于大约5次连续不断地插入或更新一行的东西加锁,你将获得更好的整体性能。如果你做很多一行的插入,你可以做一个LOCK TABLES,偶尔随后做一个UNLOCK TABLES(大约每1000行)以允许另外的线程存取表。这仍然将导致获得好的性能。当然,LOAD DATA INFILE对装载数据仍然是更快的。

    为了对LOAD DATA INFILE和INSERT得到一些更快的速度,扩大关键字缓冲区。

    UPDATE查询的速度

    更改查询被优化为有一个写开销的一个SELECT查询。写速度依赖于被更新数据大小和被更新索引的数量。

    使更改更快的另一个方法是推迟更改并且然后一行一行地做很多更改。如果你锁定表,做一行一行地很多更改比一次做一个快。

    注意,动态记录格式的更改一个较长总长的记录,可能切开记录。因此如果你经常这样做,时不时地OPTIMIZE TABLE是非常重要的。

    DELETE查询的速度

    删除一个记录的时间精确地与索引数量成正比。为了更快速地删除记录,你可以增加索引缓存的大小。

    从一个表删除所有行比删除行的一大部分也要得多。

    索引对有效装载数据的影响

    如果表是索引的,则可利用批量插入(LOAD DATA 或多行的 INSERT 语句)来减少索引的开销。这样会最小化索引更新的影响,因为索引只需要在所有行处理过时才进行刷新,而不是在每行处理后就刷新。

    ·如果需要将大量数据装入一个新表,应该创建该表且在未索引时装载,装载数据后才创建索引,这样做较快。一次创建索引(而不是每行修改一次索引)较快。

    ·如果在装载之前删除或禁用索引,装入数据后再重新创建或启用索引可能使装载更快。
    ·如果想对数据装载使用删除或禁用策略,一定要做一些实验,看这样做是否值得(如果将少量数据装入一个大表中,重建和索引所花费的时间可能比装载数据的时间还要长)。

    可用DROP INDEX和CREATE INDEX 来删除和重建索引。

    另一种可供选择的方法是利用 myisamchk 或 isamchk 禁用和启用索引。这需要在 MySQL 服务器主机上有一个帐户,并对表文件有写入权。为了禁用表索引,可进入相应的数据库目录,执行下列命令之一:


    shell>myisamchk --keys-used=0 tbl_name
    shell>isamchk --keys-used=0 tbl_name 


      对具有 .MYI 扩展名的索引文件的 MyISAM 表使用 myisamchk,对具有 .ISM 扩展名的索引文件的 ISAM 表使用 isamchk。在向表中装入数据后,按如下激活索引:


    shell>myisamchk --recover --quick --keys-used=0 tbl_name
    shell>isamchk --recover --quick --keys-used=0 tbl_name


      n 为表具有的索引数目。可用 --description 选项调用相应的实用程序得出这个值:


    shell>myisamchk --discription tbl_name
    $isamchk --discription tbl_name

     
      如果决定使用索引禁用和激活,应该使用第13章中介绍的表修复锁定协议以阻止服务器同时更改锁(虽然此时不对表进行修复,但要对它像表修复过程一样进行修改,因此需要使用相同的锁定协议)。

    上述数据装载原理也适用于与需要执行不同操作的客户机有关的固定查询。例如,一般希望避免在频繁更新的表上长时间运行 SELECT 查询。长时间运行 SELECT 查询会产生大量争用,并降低写入程序的性能。一种可能的解决方法为,如果执行写入的主要是 INSERT 操作,那么先将记录存入一个临时表,然后定期地将这些记录加入主表中。如果需要立即访问新记录,这不是一个可行的方法。但只要能在一个较短的时间内不访问它们,就可以使用这个方法。使用临时表有两个方面的好处。首先,它减少了与主表上 SELECT 查询语句的争用,因此,执行更快。其次,从临时表将记录装入主表的总时间较分别装载记录的总时间少;相应的索引高速缓存只需在每个批量装载结束时进行刷新,而不是在每行装载后刷新。

    这个策略的一个应用是进入 Web 服务器的Web 页访问 MySQL 数据库。在此情形下,可能没有保证记录立即进入主表的较高权限。

    如果数据并不完全是那种在系统非正常关闭事件中插入的单个记录,那么减少索引刷新的另一策略是使用 MyISAM 表的 DELAYED_KEY_WRITE 表创建选项(如果将 MySQL 用于某些数据录入工作时可能会出现这种情况)。此选项使索引高速缓存只偶尔刷新,而不是在每次插入后都要刷新。

    如果希望在服务器范围内利用延迟索引刷新,只要利用 --delayed-key-write 选项启动 mysqld 即可。在此情形下,索引块写操作延迟到必须刷新块以便为其他索引值腾出空间为止,或延迟到执行了一个 flush-tables 命令后,或延迟到该索引表关闭。

    展开全文
  • Mysql查询优化

    千次阅读 2011-11-17 21:54:41
    Mysql优化 ##1:索引可以大幅度提高查询性能 1.1 缺省情况下建立的索引是非群集索引,但有时它并不是最佳的。在非群集索引下,数据在物理上随机存放在数据页上。合理的索引设计要建立在对各种查询的分析和预测...

                                            Mysql优化

    ##1:索引可以大幅度提高查询性能

    1.1  缺省情况下建立的索引是非群集索引,但有时它并不是最佳的。在非群集索引下,数据在物理上随机存放在数据页上。合理的索引设计要建立在对各种查询的分析和预测上。一般来说:

    a.有大量重复值、且经常有范围查询( > ,< ,> =,< =)和order by、group by发生的列,可考虑建立群集索引;

    b.经常同时存取多列,且每列都含有重复值可考虑建立组合索引;

    c.组合索引要尽量使关键查询形成索引覆盖,其前导列一定是使用最频繁的列。索引虽有助于提高性能但不是索引越多越好,恰好相反过多的索引会导致系统低

    效。用户在表中每加进一个索引,维护索引集合就要做相应的更新工作。

            1.2 用explain语句查询索引使用情况 


    如何使用请查看文章:http://blog.csdn.net/yueguanghaidao/article/details/6933387  

    索引注意事项:

    a. 使用FULLTEXT参数可以设置索引为全文索引,全文索引只能创建在CHAR ,VARCHAR ,TEXT类型字段上。->>但只有MyISAM存储引擎支持全文索引。


    b: 多列索引:在表的多列字段上建立一个索引,但只有在查询这些字段的第一个字段时,索引才会被使用。


    c. 查询语句使用like关键字进行查询,如果匹配的第一个字符为”%“时,索引不会被使用

       

    d. 查询语句中使用or关键字时,只有or前后两个条件的列都是索引时,查询时才使用索引


    e.最好在相同类型的字段间进行比较,如不能将建有索引的int字段与bigint字段进行比较


    如在一个DATE类型的字段上使用YEAE()函数时,将会使索引不能发挥应有的作用。所以,下面的两个查询虽然返回的结果一样,但后者要比前者快得多。

    SELECT * FROM order WHERE YEAR(OrderDate)<2001;

    SELECT * FROM order WHEREOrderDate<"2001-01-01";


    f. 任何对列的操作都将导致表扫描,它包括数据库函数、计算表达式等等,查询时要尽可能将操作移至等号右边


            因为在WHERE子句中对字段进行函数或表达式操作,这将导致引擎放弃使用索引而进行全表扫描


    如:SELECT * FROM inventory WHERE Amount/7<24;

    SELECT * FROM inventory WHERE Amount<24*7;

    上面的两个查询也是返回相同的结果,但后面的查询将比前面的一个快很多


    SELECT * FROM RECORD WHERESUBSTRING(CARD_NO,1,4)=’5378’

    应改为: SELECT *FROM RECORD WHERE CARD_NO LIKE ‘5378%’


    g. 搜索字符型字段时,我们有时会使用LIKE 关键字和通配符,这种做法虽然简单,但却也是以牺牲系统性能为代价的

    例如下面的查询将会比较表中的每一条记录。

    SELECT * FROM books WHERE name like "MySQL%"

    但是如果换用下面的查询,返回的结果一样,但速度就要快上很多:

    SELECT * FROM books WHERE name>="MySQL"andname<"MySQM";


    h. 避免使用!=或<>、IS NULL或IS NOT NULL、IN ,NOT IN等这样的操作符,因为这会使系统无法使用索引,而只能直接搜索表中的数据。


    例如:  SELECT id FROM employee WHERE id !="B%" 优化器将无法通过索引来确定将要命中的行数,因此需要搜索该表的所有行。



    i.  能够用BETWEEN的就不要用IN,因为IN会使系统无法使用索引,而只能直接搜索表中的数据


     如:SELECT * FROM T1 WHERE ID IN(10,11,12,13,14)改成:SELECT *FROM T1 WHERE ID BETWEEN 10 AND 14



    ##2:在可能的情况下尽量限制尽量结果集行数


    2.1:使用top

    如:SELECT TOP 300COL1,COL2,COL3 FROM T


    2.2:增加 limit 1 会让查询更加有效

          这样数据库引擎发现只有1后停止扫描,而不会去扫描整个表或索引


    2.3:尽量避免slecect  * 命令,而是需要什么字段,查询什么字段


    ##3: 合理使用EXISTS,NOT EXISTS子句


    如果想校验表里是否存在某条纪录,不要用count(*)那样效率很低,而且浪费服务器资源。可以用EXISTS代替。如:

    IF (SELECT COUNT(*) FROM table_name WHEREcolumn_name = 'xxx')

    可以写成:IF EXISTS (SELECT * FROM table_name WHERE column_name = 'xxx')



    ##4:数据类型


    4.1: 只要能满足需求,应尽可能使用小的数据类型,

               比如能用tinyint 就不用int


    4.2: varchar比char节省空间,但效率比char低,想要获得效率就得牺牲一定空间。

    如果一个varchar的列经常被修改,而且修改的数据长度不同,会引起‘行迁移’问题,造成多余I/O花费,这时最好用char代替varchar

    如果是像身份证定长的字段,一定要用char ,查询时是全字段匹配,能获取更高效率。

    这里考虑一个问题??  -》》使用varchar(5)和varchar(200)保存‘hello’占用的空间都是一样的,但是使用较短的列有巨大优势,因为较大的列会占用更多的
    内存。


    4.3:如果字段类型只有少量的几个,最好使用enum类型,因为enum类型被当作数值型数据来处理,而数值型数据被处理起来的速度要比文本类型快得多.

    例如省份,性别等字段。


    4.4:尽量使用数字型字段,一部分开发人员和数据库管理人员喜欢把包含数值信息的字段设计为字符型,这会降低查询和连接的性能,并会增加存储开销。

    这是因为引擎在处理查询和连接回逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了


    4.5:一般情况下日期和时间类型最好选择timestamp,因为datetime占用8字节存储空间,而timestamp占用4字节存储空间,明显更节约空间。


    ##5.在可能的情况下,应该尽量把字段设置为NOT NULL,这样在将来执行查询的时候,数据库不用去比较NULL值。


    ##6.使用连接查询(join)代替子查询


    因为子查询时,mysql需要为内层查询结果建立一个临时表,然后外层查询在临时表中查找,查询完后需要撤销临时表。

    而连接查询不需要建立临时表,所以比子查询快。


     ##7. 使用联合(union)来代替手动创建的临时表

    MySQL  4.0 的版本开始支持UNION 查询,它可以把需要使用临时表的两条或更多的 SELECT 查询合并的一个查询中。在客户端的查询会话结束的时候,临时表会被自

    动删除,从而保证数据库整齐、高效。使用 UNION 来创建查询的时候,我们只需要用 UNION作为关键字把多个 SELECT 语句连接起来就可以了,要注意的是所有

    SELECT语句中的字段数目要想同。下面的例子就演示了一个使用 UNION的查询。

    select  name,phone from clinet  union  select name,bitthdate from author union slect name,supplier from product


    ##8. 充分利用连接条件


          在某种情况下,两个表之间不止一个连接条件,这时可在where子句中将连接条件完整写上,可大大提高查询速度。如:

    SELECT SUM(A.AMOUNT) FROM ACCOUNT A,CARD BWHERE A.CARD_NO = B.CARD_NO 

    SELECT SUM(A.AMOUNT) FROM ACCOUNT A,CARD BWHERE A.CARD_NO = B.CARD_NO AND A.ACCOUNT_NO=B.ACCOUNT_NO

    第二句将比第一句执行快得多。


    ##9. 能用DISTINCT的就不用GROUP BY


      SELECT OrderID FROM Details WHERE UnitPrice > 10 GROUP BY OrderID 

       可改为:SELECT DISTINCT OrderID FROMDetails WHERE UnitPrice > 10

    ##10.尽量不要用SELECT INTO语句。 SELECTINTO 语句会导致表锁定,阻止其他用户访问该表


    ##11.UPDATE语句建议:

    a. 尽量不要修改主键字段。

    b. 当修改VARCHAR型字段时,尽量使用相同长度内容的值代替。

    c. 尽量最小化对于含有UPDATE触发器的表的UPDATE操作。

    d. 避免UPDATE将要复制到其他数据库的列。

    e. 避免UPDATE建有很多索引的列。

    f. 避免UPDATE在WHERE子句条件中的列。



     





    展开全文
  • MySQL查询优化

    2019-02-20 16:27:17
    MySQL体系结构 Client Connectors指不同语言与MySQL之间的交互 **Services&amp;utilities:**负责备份恢复、安全性控制、数据库复制、分区等。 **SQL Interface:**接收...**Optimizer:**查询优化器,SQL语句的...

    MySQL体系结构

    MySQL体系结构
    Client Connectors指不同语言与MySQL之间的交互
    **Services&utilities:**负责备份恢复、安全性控制、数据库复制、分区等。
    **SQL Interface:**接收用户请求,生成结果。
    **Parser:**分析SQL语句是否符合规则;将结果集解析成可被应用程序处理的数据结构。
    **Optimizer:**查询优化器,SQL语句的最优执行顺序。

    MySQL语句执行过程

    SQL语句执行过程
    客户端、服务器的通讯——>查询缓存——> 查询优化处理——>调用执行引擎——>返回客户端

    MySQL客户端、服务器之间的通讯

    首先,了解几个概念:
    1. 什么是MySQL的客户端和服务器?
    我们在Windows平台上进行MySQL安装的时候,会同时安装MySQL的客户端和服务器,在Linux系统上,两者是分开的。在Windows系统上,如果是使用命令行使用数据库,使用时需要首先使用net start 命令启动MySQL服务,这里可以视作开启MySQL的服务器端,然后需要进入安装目录下的bin文件夹下,运行mysql.exe程序,此处可以视作MySQL的客户端。如果是使用如workbench等程序,则其可以也视作MySQL的客户端。
    既然MySQL分为客户端和服务器两个部分,那么就存在客户端和服务器之间通讯的问题。我们平时练习使用的MySQL,客户端和服务器都在同一台机器上,如何访问其他机器上的服务器呢?首先,服务器端首先需要建立允许非本地登录的账号,然后在客户端登录时,指定要连接的服务器;例如在命令行模式下,使用mysql -h 主机地址 -u用户名 -p 密码。详细请参考其他博文。
    以上是个人理解,如果有错希望大佬指正。
    2.数据传输方式有哪些?
    半双工:半双工数据传输允许数据在两个方向上传输,但是,在某一时刻,只允许数据在一个方向上传输。它实际上是一种切换方向的单工通信。例如:对讲机。
    全双工:全双工数据通信允许数据同时在两个方向上传输;例如:电话通信
    单工:单工数据传输只支持数据在一个方向上传输;在同一时间只有一方能够接受或发送信息,不能实现双向通信;例如:电视、广播。

    MySQL客户端与服务端的通信方式是“半双工”。客户端一旦开始发送消息另一端要接收完整个消息才能响应;客户端一旦开始接收数据没法停下来发送指令。

    对于一个mysql连接,或者说一个线程,时刻都有一个状态来标识这个连接正在做什么。
    查看线程状态命令:
    show full processlist(普通用户)
    show processlist(root账户,普通用户使用只能查看本用户开启的线程)

    id列:一个标识,你要kill 一个语句的时候很有用。
    user列: 显示当前用户,如果不是root,这个命令就只显示你权限范围内的sql语句。
    host列:显示这个语句是从哪个ip 的哪个端口上发出的。可用来追踪出问题语句的用户。
    db列:显示这个进程目前连接的是哪个数据库。
    command列:显示当前连接的执行的命令,一般就是休眠(sleep),查询(query),连接(connect)。
    time列:此这个状态持续的时间,单位是秒。
    state列:显示使用当前连接的sql语句的状态,很重要的列,后续会有所有的状态的描述,请注意,state只是语句执行中的某一个状态,一个sql语句,已查询为例,可能需要经过copying to tmp table,Sorting result,Sending data等状态才可以完成。
    Info列:当前使用的SQL语句;
    

    对于出现问题的连接可通过 kill{id} 的方式关闭连接。

    查询缓存

    工作原理:
    缓存SELECT操作的结果集和SQL语句
    新的SELECT语句,先去查询缓存,判断是否存在可用的记录集。

    缓存命中标准:
    当前查询语句与缓存的SQL语句是否完全一样。
    (简单认为存储了一个key-value结构,key为sql语句,value为sql查询结果集)。

    查询缓存的坑
    1.SQL语句前后空格不影响,中间空格数不同都会视为不同的SQL语句
    2.当查询量超出缓存最大存储(可配置)时,缓存无法使用
    3.使用系统函数时,不会进入到缓存之中
    4.操作视图时,不会进入到缓存之中
    5. 当表发生改变时,所有与此表有关的缓存都会失效。

    查看缓存是否开启: Show variavles like ‘query_cache_type’;0 不开启 1 开启 2 按需开启

    开启缓存:Windows下修改my.ini配置文件;Linux 修改/etc/my.cnf文件。
    [mysqld]中添加:
    query_cache_size = 20M
    query_cache_type = ON

    按需开启时:使用select sql_cache * from 表名;语句可添加查询缓存
    查看缓存状态:show status like ‘Qcache%’;

    查询优化处理

    查询优化分为三个阶段:

    解析sql:通过lex词法分析,yacc语法分析将sql语句解析成解析树;
    预处理阶段:根据mysql的语法规则仅一步检查解析树的合法性。如:检查数据的表和列是否存在,解析名字和别名的设置,进行权限的验证。
    查询优化器:优化器的主要作用就是找到最优的执行计划。
    

    查看执行计划:

    查询优化器如何找到最优执行计划?

    使用等价变化规则
    覆盖索引扫描
    子查询优化
    提前终止查询
    IN的优化
    ...
    

    MySQL的查询优化器是基于成本计算的原则,他会尝试各种执行计划。如果数据抽样进行实验(随机的读取一个4K的数据块进行分析)。

    MySQL执行计划参数:
    ID:select查询的序列号,标识执行的顺序

    1.	ID相同,执行顺序由上至下
    2.	Id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行。
    3.	Id相同又不同的两种情况同时存在,id如果相同,可以认为是一组,从上往下顺序执行;在所有组中,id值越大,优先级越高,越先执行;
    

    select_type:查询的类型,主要是用于区分普通查询、联合查询、子查询等。

    SIMPLE:简单的select查询,查询中不包含子查询或者union
    PRIMARY:查询中包含子部分,最外层查询则被标记为primary
    SUBQUERY:表示在select或where列表中包含了子查询
    MATERIALIZED:表示where后面in条件的子查询
    UNION:若第二个select出现在union之后,则被标记为union
    UNION RESULT:从union表获取结果的select
    

    table:查询涉及到的表,有三种情况

    直接显示表名或者表的别名
    <unionM,N>由ID为M,N查询union产生的结果
    <subqueryN>由ID为N查询产生的结果
    

    type:访问类型,SQL查询优化汇总一个很重要的指标,结果值从好到坏依次是:
    system>const>eq_ref>range>index>ALL

    system:表只有一行记录(等于系统表),const类型的特例,基本不会出现。
    const:表示通过索引一次就找到了,const代表primary key或者unique索引
    eq_ref:唯一索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描
    ref:非唯一性索引扫描,返回匹配某个单独值的所有行,本质也是一种索引访问
    range:只检索给定范围的行,使用一个索引来选择行
    index:Full index Scan:索引全表扫描,把索引从头到尾扫描一遍
    ALL:Full Table Scan,遍历全表找到匹配的行。
    

    Possible_keys:查询过程有可能用到的索引
    Key:实际使用的索引,如果为NULL,则没有使用索引
    Rows:根据表统计信息或者索引选用情况,大致估算出找到所需的记录所需要读取的行数
    Filtered:指返回结果的行占需要督导的行(rows列的值)的百分比,越大越好。
    Extra:十分重要的额外信息。包括:

    Using filesort:mysql对数据使用一个外部的文件内容进行排序,而不是按照表内的索引进行排序读取
    Using temporary:使用临时表保存中间结果;常见于order by或group by
    Using index:相应的select 操作中使用了覆盖索引(Covering Index),避免了访问表的数据行,效率高。
    Using where:使用了where过滤条件
    Select tables optimized away:基于索引优化MIN/MAX操作或者MyISAM存储引擎优化COUNT(*)操作,不必等到执行阶段再进行计算,查询执行计划生成的阶段即可完成优化。
    

    注意:文件、临时表效率比较低。。。

    4.插拔式的存储引擎,可按需选择
    5.返回客户端

    有需要做缓存的执行缓存操作
    增量的返回结果(开始生成第一条结果是,MySQL就开始往请求方逐步返回数据  优点:MySQL服务器无需保存过多的数据,节省内存,用户体验好。)
    

    .

    展开全文
  • mysql查询优化

    2017-08-11 19:14:26
    之前在面试的时候,被问过MySQL查询相关的问题,做PHP也有两年多了,发现对于查询优化这方面,还不是特别清楚,所以就在网上查询相关的文档,在一篇博客中深受启发,要想了解MySQL的查询优化,就需要了解MySQL的查询...

    之前在面试的时候,被问过MySQL查询相关的问题,做PHP也有两年多了,发现对于查询优化这方面,还不是特别清楚,所以就在网上查询相关的文档,在一篇博客中深受启发,要想了解MySQL的查询优化,就需要了解MySQL的查询机制,也就是他的查询都经过了什么哪些步骤。在网上盗了一张图:


    1.客户端发送一条查询给服务器

    2.服务器先检查查询缓存,如果命中了缓存,则立刻返回存储在缓存中的结果,否则进入下一阶段。

    3.服务器进行SQL解析,预处理,再由优化器生成对应的执行计划,

    4.mysql根据优化器生成的执行计划,调用存储引擎的API来执行查询。

    5.将结果返回给客户端。


    不知道大家在平常的数据库查询中有没有发现一个问题,就是用navicat(我用的是这个数据库管理软件)执行sql的时候,执行同样的语句时,第2次的时间时间远比第一次的时间短(可能在一个小的数据库中不是很明显),那是因为,第二次的时候数据库只执行到上面的第二部就已经将结果返回给客户端了,所以时间会很短,这只是一个很简单,很常见的现象,但是也是从上面的图中可以反应出的一个结果。


    还有就是当查询一个量级较小的数据库是,一个复杂的查询语句会比多个简单的查询语句效率高,但当如果是一个量级比较大的数据库的时候,多个简单的查询语句会比一个复杂的语句查询效果高(这里先不考虑存储过程),如果数据量大的话,把查询结果放到内存的时候会耗费很长时间,等待表锁的时间也会很长,数据量小的话,就存在类似的问题了,反而多次查询造成的连续开关数据库会耗费很长的时间,总之是两者之害取其轻。但是如果使用存储过程的话,就会优化很多。其实我自己感觉这和分流类似。当然这些都是在内存和服务器大小等外部条件固定的情况下来讲的。

    一些简单的重构查询的方式:

      1.一个复杂查询 or 多个简单查询

        设计查询的时候一个需要考虑的重要问题是,是否需要将一个复杂的查询分成多个简单的查询。

      2.切分查询

        有时候对于一个大查询我们需要“分而治之”,将大查询切分为小查询,每个查询功能完全一样,只完成一小部分,每次

        只返回一小部分查询结果。

      3.分解关联查询

        select * from tag 

           join tag_post on tag_post.tag_id = tag.id

           join post on tag_post.post_id = post.id

        where tag.tag = 'mysql'

        可以分解成下面这些查询来代替:

        > select * from tag where tag = 'mysql'

        > select * from tag_post where tag_id = 1234

        > select * from post where post_id in (123, 456, 567, 9098, 8904)    

      优势:

          让缓存的效率更高。

          将查询分解后,执行单个查询可以减少锁的竞争。

          在应用层做关联,可以更容易对数据库进行拆分,更容易做到高性能和可扩展。

          查询本身效率也可能会有所提升。

          可以减少冗余记录的查询,

          更进一步,这样做相当于在应用中实现了哈希关联,而不是使用mysql的嵌套循环关联。

    总之,了解了MySQL的查询机制,对于以后的深度查询优化很有帮助,其实这篇博客也是我参照另外一篇博客并加了一部分自己的感受,毕竟对于MySQL深度的理解我也不是很到位,大家共同学习嘛。最后推荐一位博主,他的一一些关于MySQL查询性能优化方面的文章写的挺好的,大家可以去看看。(http://www.cnblogs.com/w2154/p/4691015.html),关于查询性能优化方面的有6篇,也有很多关于MySQL方面的知识,在此分享给大家。

    展开全文
  • 此文章主要向大家描述的是MySQL查询优化系列之MySQL查询优化器,当你在对一查询进行提交的时候,MySQL数据库会对它进行分析,主要是看其是否可以用来做一些优化使处理该查询的速度更快。 这一部分将介绍查询...
  • MySQL查询优化之查询优化器

    千次阅读 2009-08-20 16:45:00
    MySQL查询优化之查询优化器phpma.com  当你提交一个查询的时候,MySQL会分析它,看是否可以做一些优化使处理该查询的速度更快。这一部分将介绍查询优化器是如何工作的。如果你想知道MySQL采用的优化手段,可以查看...
  • MySQL查询优化之一-WHERE语句优化

    千次阅读 2018-01-18 13:51:34
    MySQL查询优化之一-WHERE语句优化 如需转载请标明出处:http://blog.csdn.net/itas109 QQ技术交流群:12951803 环境: MySQL版本:5.5.15 操作系统:windows 本文讨论WHERE语句的优化。 这些示例使用SELECT...
  • 最近做项目遇到一些mysql数据库查询优化的问题,单表查询一百万左右的数据,select count(*) 查询比较慢,如果再有分页查询,按条件查询又比较慢,各位有没有好的优化建议?
  • 一、mysql查询类型(默认查询出所有数据列) 1、内连接   默认多表关联查询方式,查询出两个表中所有字段;可省略inner join 关键字 2、外连接 查询出某一张表中的所有数据 (1)左连接  查询出第一张表的...
  • mysql查询优化

    千次阅读 2019-03-15 00:55:44
    优化器分类 传统关系型数据库里面的优化器分为CBO和RBO两种。 RBO— Rule_Based Potimizer 基于规则的优化器: RBO :RBO所用的判断规则是一组内置的规则,这些规则是硬编码在数据库的编码中的,RBO会根据这些规则...
  • 一对多的两张表,一般是一张表的外键关联到另一个表的主键。但也有不一般的情况,也就是两个表并非通过其中一个表的主键关联。 例如: create table t_team ( tid int primary key, tname varchar(100) ...
  • 从事前端开发的都知道,页面显示的数据一定要及时的呈现,否则会影响用户体现.那么导致页面加载数据慢或者显示滞后的原因又是什么呢?...主要原因1: 后台数据库中的数据过多,没做数据优化导致后台查询
  • php mysql查询优化问题

    2018-10-17 03:39:26
    1.user表存储用户名和电话,其他表关联用户user表的主键id,展示用户的数据时(如订单),需要根据用户id获得用户名和电话,现在用的是left join,每次查询都会执行一次left ...这种耗时久,结果频繁变动的查询要怎么处理好呢?
  • 一,最常见MYSQL最基本的分页方式limit: select * from `table` order by id desc limit 0, 20 在中小数据量的情况下,这样的SQL足够用了,唯一需要注意的问题就是确保使用了索引。随着数据量的增加,页数会...
  • MySQL查询优化方法

    千次阅读 2017-11-28 15:05:58
    MySQL查询优化方法
  • 通过对源代码跟踪和调试,对MySQL查询优化器进行分析并编写文档,为开发人员和数据库管理人员提供查询SQL语句的建议。   基础 MySQL的设计架构在官方文档中给出,如下图所示。该图的具体描述和讲解,请参考...
  • php mysql查询优化

    2018-12-28 16:19:14
    2、对查询进行优化,首先应考虑在where及orderby涉及的列上建立索引,避免全表扫描。 3、应尽量避免在where子句中对字段进行null值判断,否则将导致引擎放弃使用索引,而进行全表扫描,如: select id from t ...
  • 原贴:http://networld.tianyablog.com/blogger/post_show.asp?BlogID=40003&PostID=5154512&idWriter=0&Key=0 下一篇>> MySQL查询优化系列讲座之查询优化器作者:弥勒菩萨
  • Mysql查询优化

    千次阅读 2016-08-14 15:31:18
    Mysql查询优化器 本文的目的主要是通过告诉大家,查询优化器为我们做了那些工作,我们怎么做,才能使查询优化器对我们的sql进行优化,以及启示我们sql语句怎么写,才能更有效率。那么到底mysql到底能进行哪些优化...
  • mysql查询优化技巧

    千次阅读 2016-04-28 16:33:32
    MYSQL查询语句优化索引优化,查询优化,查询缓存,服务器设置优化,操作系统和硬件优化,应用层面优化(web服务器,缓存)等等。这里的记录的优化技巧更适用于开发人员,都是从网络上收集和自己整理的,主要是查询...
  • mysql 查询优化分析

    2018-06-12 17:40:51
    一、开启慢查询日志 要进行SQL优化,首先要知道要优化目标--哪些SQL需要优化,哪些SQL查询比较慢??MySQL提供了慢查询日志--slow_query_log,用来记录查询比较慢的SQL语句。 MySQL默认是没有开启慢查询日志的,...
  • MySQL查询优化

    2013-05-02 17:37:34
    当你提交一个查询的时候,MySQL会...当然,MySQL查询优化器也利用了索引,但是它也使用了其它一些信息。例如,如果你提交如下所示的查询,那么无论数据表有多大,MySQL执行它的速度都会非常快: SELECT * FROM
  • MySQL 查询优化

    千次阅读 2012-03-19 17:40:05
    这篇描述MySQL查询优化器的工作原理。MySQL查询优化器主要为执行的查询决断最有效的路线(routine,走向)。   一。源代码和概念   这部分讨论优化器关键概念,术语,及在MySQL源代码怎么对应的。  ...
  • mysql查询优化方法

    2008-07-08 15:56:00
    则无法使用索引5 尽量不用having子句,不利优化6 Order by 不要使用查询条件表达式7 对重复值索引没用8 单表索引总数不要超过16个9 不要用空行10 WHERE子句中的所有未使用的索引部分

空空如也

1 2 3 4 5 ... 20
收藏数 26,194
精华内容 10,477
关键字:

mysql查询优化

mysql 订阅