注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

唐老鸭的博客

太多的不如意让我们学会了抱怨,却忘记怎么去努力改变现状.

 
 
 

日志

 
 
 
 

【引用】MySql中管理百万级要注意些什么东西  

2011-01-06 21:35:35|  分类: 默认分类 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
和大家分享这篇日志,我的看法是:

 
原文地址:MySql中管理百万级要注意些什么东西    原文作者:sbj306

一、我们可以且应该优化什么?    
   
  硬件    
   
  操作系统/软件库    
   
  SQL服务器(设置和查询)    
   
  应用编程接口(API)    
   
  应用程序    
   
   
  --------------------------------------------------------------------------------    
   
  二、优化硬件    
   
  如果你需要庞大的数据库表(>2G),你应该考虑使用64位的硬件结构,像Alpha、Sparc或即将推出的IA64。因为MySQL内部使用大量64位的整数,64位的CPU将提供更好的性能。    
   
  对大数据库,优化的次序一般是RAM、快速硬盘、CPU能力。    
   
  更多的内存通过将最常用的键码页面存放在内存中可以加速键码的更新。    
   
  如果不使用事务安全(transaction-safe)的表或有大表并且想避免长文件检查,一台UPS就能够在电源故障时让系统安全关闭。    
   
  对于数据库存放在一个专用服务器的系统,应该考虑1G的以太网。延迟与吞吐量同样重要。    
   
   
  --------------------------------------------------------------------------------    
   
  三、优化磁盘    
   
  为系统、程序和临时文件配备一个专用磁盘,如果确是进行很多修改工作,将更新日志和事务日志放在专用磁盘上。    
  低寻道时间对数据库磁盘非常重要。对与大表,你可以估计你将需要log(行数)/log(索引块长度/3*2/(键码长度   +   数据指针长度))+1次寻到才能找到一行。对于有500000行的表,索引Mediun   int类型的列,需要log(500000)   /   log(1024/3*2/(3   +   2))+1=4次寻道。上述索引需要500000*7*3/2=5.2M的空间。实际上,大多数块将被缓存,所以大概只需要1-2次寻道。    
  然而对于写入(如上),你将需要4次寻道请求来找到在哪里存放新键码,而且一般要2次寻道来更新索引并写入一行。    
  对于非常大的数据库,你的应用将受到磁盘寻道速度的限制,随着数据量的增加呈N   log   N数据级递增。    
  将数据库和表分在不同的磁盘上。在MySQL中,你可以为此而使用符号链接。    
  条列磁盘(RAID   0)将提高读和写的吞吐量。    
  带镜像的条列(RAID   0+1)将更安全并提高读取的吞吐量。写入的吞吐量将有所降低。    
  不要对临时文件或可以很容易地重建的数据所在的磁盘使用镜像或RAID(除了RAID   0)。    
  在Linux上,在引导时对磁盘使用命令hdparm   -m16   -d1以启用同时读写多个扇区和DMA功能。这可以将响应时间提高5~50%。    
  在Linux上,用async   (默认)和noatime挂载磁盘(mount)。    
  对于某些特定应用,可以对某些特定表使用内存磁盘,但通常不需要。    
   
  --------------------------------------------------------------------------------    
   
  四、优化操作系统    
   
  不要交换区。如果内存不足,增加更多的内存或配置你的系统使用较少内存。    
  不要使用NFS磁盘(会有NFS锁定的问题)。    
  增加系统和MySQL服务器的打开文件数量。(在safe_mysqld脚本中加入ulimit   -n   #)。    
  增加系统的进程和线程数量。    
  如果你有相对较少的大表,告诉文件系统不要将文件打碎在不同的磁道上(Solaris)。    
  使用支持大文件的文件系统(Solaris)。    
  选择使用哪种文件系统。在Linux上的Reiserfs对于打开、读写都非常快。文件检查只需几秒种。    
   
  --------------------------------------------------------------------------------    
   
  五、选择应用编程接口    
   
  PERL    
  可在不同的操作系统和数据库之间移植。    
  适宜快速原型。    
  应该使用DBI/DBD接口。    
  PHP    
  比PERL易学。    
  使用比PERL少的资源。    
  通过升级到PHP4可以获得更快的速度。    
  C    
  MySQL的原生接口。    
  较快并赋予更多的控制。    
  低层,所以必须付出更多。    
  C++    
  较高层次,给你更多的时间来编写应用。    
  仍在开发中    
  ODBC    
  运行在Windows和Unix上。    
  几乎可在不同的SQL服务器间移植。    
  较慢。MyODBC只是简单的直通驱动程序,比用原生接口慢19%。    
  有很多方法做同样的事。很难像很多ODBC驱动程序那样运行,在不同的领域还有不同的错误。    
  问题成堆。Microsoft偶尔还会改变接口。    
  不明朗的未来。(Microsoft更推崇OLE而非ODBC)    
  ODBC    
  运行在Windows和Unix上。    
  几乎可在不同的SQL服务器间移植。    
  较慢。MyODBC只是简单的直通驱动程序,比用原生接口慢19%。    
  有很多方法做同样的事。很难像很多ODBC驱动程序那样运行,在不同的领域还有不同的错误。    
  问题成堆。Microsoft偶尔还会改变接口。    
  不明朗的未来。(Microsoft更推崇OLE而非ODBC)    
  JDBC    
  理论上可在不同的操作系统何时据库间移植。    
  可以运行在web客户端。    
  Python和其他    
  可能不错,可我们不用它们。    
   
  --------------------------------------------------------------------------------    
   
  六、优化应用    
   
  应该集中精力解决问题。    
  在编写应用时,应该决定什么是最重要的:    
  速度    
  操作系统间的可移植性    
  SQL服务器间的可移植性    
  使用持续的连接。.    
  缓存应用中的数据以减少SQL服务器的负载。    
  不要查询应用中不需要的列。    
  不要使用SELECT   *   FROM   table_name...    
  测试应用的所有部分,但将大部分精力放在在可能最坏的合理的负载下的测试整体应用。通过以一种模块化的方式进行,你应该能用一个快速“哑模块”替代找到的瓶颈,然后很容易地标出下一个瓶颈。    
  如果在一个批处理中进行大量修改,使用LOCK   TABLES。例如将多个UPDATES或DELETES集中在一起。    
   
  --------------------------------------------------------------------------------    
   
  七、应该使用可移植的应用    
   
  Perl   DBI/DBD    
  ODBC    
  JDBC    
  Python(或其他有普遍SQL接口的语言)    
  你应该只使用存在于所有目的SQL服务器中或可以很容易地用其他构造模拟的SQL构造。www.mysql.com上的Crash-me页可以帮助你。    
  为操作系统/SQL服务器编写包装程序来提供缺少的功能。    
   
  --------------------------------------------------------------------------------    
   
  八、如果你需要更快的速度,你应该:    
   
  找出瓶颈(CPU、磁盘、内存、SQL服务器、操作系统、API或应用)并集中全力解决。    
  使用给予你更快速度/灵活性的扩展。    
  逐渐了解SQL服务器以便能为你的问题使用可能最快的SQL构造并避免瓶颈。    
  优化表布局和查询。    
  使用复制以获得更快的选择(select)速度。    
  如果你有一个慢速的网络连接数据库,使用压缩客户/服务器协议。    
  不要害怕时应用的第一个版本不能完美地移植,在你解决问题时,你总是可以在以后优化它。    
   
   
  --------------------------------------------------------------------------------    
   
  九、优化MySQL    
   
  挑选编译器和编译选项。    
  位你的系统寻找最好的启动选项。    
  通读MySQL参考手册并阅读Paul   DuBios的《MySQL》一书。(已有中文版-译注)    
  多用EXPLAIN   SELECT、SHOW   VARIABLES、SHOW   STATUS和SHOW   PROCESSLIST。    
  了解查询优化器的工作原理。    
  优化表的格式。    
  维护你的表(myisamchk、CHECK   TABLE、   OPTIMIZE   TABLE)    
  使用MySQL的扩展功能以让一切快速完成。    
  如果你注意到了你将在很多场合需要某些函数,编写MySQL   UDF函数。    
  不要使用表级或列级的GRANT,除非你确实需要。    
  购买MySQL技术支持以帮助你解决问题    
   
  --------------------------------------------------------------------------------    
   
  十、编译和安装MySQL    
   
  通过位你的系统挑选可能最好的编译器,你通常可以获得10-30%的性能提高。    
  在Linux/Intel平台上,用pgcc(gcc的奔腾芯片优化版)编译MySQL。然而,二进制代码将只能运行在Intel奔腾CPU上。    
  对于一种特定的平台,使用MySQL参考手册上推荐的优化选项。    
  一般地,对特定CPU的原生编译器(如Sparc的Sun   Workshop)应该比gcc提供更好的性能,但不总是这样。    
  用你将使用的字符集编译MySQL。    
  静态编译生成mysqld的执行文件(用--with-mysqld-ldflags=all-static)并用strip   sql/mysqld整理最终的执行文件。    
  注意,既然MySQL不使用C++扩展,不带扩展支持编译MySQL将赢得巨大的性能提高。    
  如果操作系统支持原生线程,使用原生线程(而不用mit-pthreads)。    
  用MySQL基准测试来测试最终的二进制代码。     
    十一、维护    
   
  如果可能,偶尔运行一下OPTIMIZE   table,这对大量更新的变长行非常重要。    
  偶尔用myisamchk   -a更新一下表中的键码分布统计。记住在做之前关掉MySQL。    
  如果有碎片文件,可能值得将所有文件复制到另一个磁盘上,清除原来的磁盘并拷回文件。    
  如果遇到问题,用myisamchk或CHECK   table检查表。    
  用mysqladmin   -i10   precesslist   extended-status监控MySQL的状态。    
  用MySQL   GUI客户程序,你可以在不同的窗口内监控进程列表和状态。    
  使用mysqladmin   debug获得有关锁定和性能的信息。    
   
  --------------------------------------------------------------------------------    
   
  十二、优化SQL    
   
  扬SQL之长,其它事情交由应用去做。使用SQL服务器来做:    
   
  找出基于WHERE子句的行。    
  JOIN表    
  GROUP   BY    
  ORDER   BY    
  DISTINCT    
   
  不要使用SQL来做:    
   
  检验数据(如日期)    
  成为一只计算器    
   
  技巧:    
   
  明智地使用键码。    
  键码适合搜索,但不适合索引列的插入/更新。    
  保持数据为数据库第三范式,但不要担心冗余信息或这如果你需要更快的速度,创建总结表。    
  在大表上不做GROUP   BY,相反创建大表的总结表并查询它。    
  UPDATE   table   set   count=count+1   where   key_column=constant非常快。    
  对于大表,或许最好偶尔生成总结表而不是一直保持总结表。    
  充分利用INSERT的默认值。    
   
  --------------------------------------------------------------------------------    
   
  十三、不同SQL服务器的速度差别(以秒计)    
   
       
  +--------------------------+--------+---------+    
  |通过键码读取2000000行:   |   NT   |   Linux   |    
  +--------------------------+--------+---------+    
  |mysql   |   367   |   249   |    
  +--------------------------+--------+---------+    
  |mysql_odbc   |   464   |   |    
  +--------------------------+--------+---------+     
  |db2_odbc   |   1206   |   |    
  +--------------------------+--------+---------+     
  |informix_odbc   |   121126   |   |    
  +--------------------------+--------+---------+     
  |ms-sql_odbc       |   1634   |   |    
  +--------------------------+--------+---------+    
  |oracle_odbc   |   20800   |   |    
  +--------------------------+--------+---------+     
  |solid_odbc   |   877       |   |    
  +--------------------------+--------+---------+    
  |sybase_odbc   |   17614   |   |    
  +--------------------------+--------+---------+     
   
  +--------------------------+--------+---------+     
  |插入350768行:   |   NT   |   Linux   |    
  +--------------------------+--------+---------+    
  |mysql   |   381   |   206   |    
  +--------------------------+--------+---------+    
  |mysql_odbc   |   619       |   |    
  +--------------------------+--------+---------+    
  |db2_odbc   |   3460    |   |    
  +--------------------------+--------+---------+    
  |informix_odbc   |   2692    |   |    
  +--------------------------+--------+---------+    
  |ms-sql_odbc   |   4012    |   |    
  +--------------------------+--------+---------+    
  |oracle_odbc   |   11291   |   |    
  +--------------------------+--------+---------+     
  |solid_odbc   |   1801    |   |    
  +--------------------------+--------+---------+    
  |sybase_odbc   |   4802    |   |    
  +--------------------------+--------+---------+    
   
   
  在上述测试中,MySQL配置8M高速缓存运行,其他数据库以默认安装运行。    
   
   
  --------------------------------------------------------------------------------    
   
  十四、重要的MySQL启动选项    
   
  back_log   如果需要大量新连接,修改它。    
  thread_cache_size   如果需要大量新连接,修改它。    
  key_buffer_size   索引页池,可以设成很大。    
  bdb_cache_size   BDB表使用的记录和键吗高速缓存。    
  table_cache   如果有很多的表和并发连接,修改它。    
  delay_key_write   如果需要缓存所有键码写入,设置它。    
  log_slow_queries   找出需花大量时间的查询。    
  max_heap_table_size   用于GROUP   BY    
  sort_buffer   用于ORDER   BY和GROUP   BY    
  myisam_sort_buffer_size   用于REPAIR   TABLE    
  join_buffer_size   在进行无键吗的联结时使用。    
   
   
  --------------------------------------------------------------------------------    
   
  十五、优化表    
   
  MySQL拥有一套丰富的类型。你应该对每一列尝试使用最有效的类型。    
  ANALYSE过程可以帮助你找到表的最优类型:SELECT   *   FROM   table_name   PROCEDURE   ANALYSE()。    
  对于不保存NULL值的列使用NOT   NULL,这对你想索引的列尤其重要。    
  将ISAM类型的表改为MyISAM。    
  如果可能,用固定的表格式创建表。    
  不要索引你不想用的东西。    
  利用MySQL能按一个索引的前缀进行查询的事实。如果你有索引INDEX(a,b),你不需要在a上的索引。    
  不在长CHAR/VARCHAR列上创建索引,而只索引列的一个前缀以节省存储空间。CREATE   TABLE   table_name   (hostname   CHAR(255)   not   null,   index(hostname(10)))    
  对每个表使用最有效的表格式。    
  在不同表中保存相同信息的列应该有同样的定义并具有相同的列名。    
   
  --------------------------------------------------------------------------------    
   
  十六、MySQL如何次存储数据    
   
  数据库以目录存储。    
  表以文件存储。    
  列以变长或定长格式存储在文件中。对BDB表,数据以页面形式存储。    
  支持基于内存的表。    
  数据库和表可在不同的磁盘上用符号连接起来。    
  在Windows上,MySQL支持用.sym文件内部符号连接数据库。    
   
  --------------------------------------------------------------------------------    
   
  十七、MySQL表类型    
   
  HEAP表:固定行长的表,只存储在内存中并用HASH索引进行索引。    
  ISAM表:MySQL   3.22中的早期B-tree表格式。    
  MyIASM:IASM表的新版本,有如下扩展:    
  二进制层次的可移植性。    
  NULL列索引。    
  对变长行比ISAM表有更少的碎片。    
  支持大文件。    
  更好的索引压缩。    
  更好的键吗统计分布。    
  更好和更快的auto_increment处理。    
  来自Sleepcat的Berkeley   DB(BDB)表:事务安全(有BEGIN   WORK/COMMIT|ROLLBACK)。    
   
  --------------------------------------------------------------------------------    
   
  十八、MySQL行类型(专指IASM/MyIASM表)    
   
  如果所有列是定长格式(没有VARCHAR、BLOB或TEXT),MySQL将以定长表格式创建表,否则表以动态长度格式创建。    
  定长格式比动态长度格式快很多并更安全。    
  动态长度行格式一般占用较少的存储空间,但如果表频繁更新,会产生碎片。    
  在某些情况下,不值得将所有VARCHAR、BLOB和TEXT列转移到另一个表中,只是获得主表上的更快速度。    
  利用myiasmchk(对ISAM,pack_iasm),可以创建只读压缩表,这使磁盘使用率最小,但使用慢速磁盘时,这非常不错。压缩表充分地利用将不再更新的日志表    
   
  --------------------------------------------------------------------------------    
   
  十九、MySQL高速缓存(所有线程共享,一次性分配)    
   
  键码缓存:key_buffer_size,默认8M。    
  表缓存:table_cache,默认64。    
  线程缓存:thread_cache_size,默认0。    
  主机名缓存:可在编译时修改,默认128。    
  内存映射表:目前仅用于压缩表。    
  注意:MySQL没有运行高速缓存,而让操作系统处理。    
   
   
  --------------------------------------------------------------------------------    
   
  二十、MySQL缓存区变量(非共享,按需分配)    
   
  sort_buffer:ORDER   BY/GROUP   BY    
  record_buffer:扫描表。    
  join_buffer_size:无键联结    
  myisam_sort_buffer_size:REPAIR   TABLE    
  net_buffer_length:对于读SQL语句并缓存结果。    
  tmp_table_size:临时结果的HEAP表大小。    
       
   
  --------------------------------------------------------------------------------

二十一、MySQL表高速缓存工作原理    
   
  每个MyISAM表的打开实例(instance)使用一个索引文件和一个数据文件。如果表被两个线程使用或在同一条查询中使用两次,MyIASM将共享索引文件而是打开数据文件的另一个实例。    
  如果所有在高速缓存中的表都在使用,缓存将临时增加到比表缓存尺寸大些。如果是这样,下一个被释放的表将被关闭。    
  你可以通过检查mysqld的Opened_tables变量以检查表缓存是否太小。如果该值太高,你应该增大表高速缓存。    
       
   
  --------------------------------------------------------------------------------    
   
  二十二、MySQL扩展/优化-提供更快的速度    
   
  使用优化的表类型(HEAP、MyIASM或BDB表)。    
  对数据使用优化的列。    
  如果可能使用定长行。    
  使用不同的锁定类型(SELECT   HIGH_PRIORITY,INSERT   LOW_PRIORITY)    
  Auto_increment    
  REPLACE   (REPLACE   INTO   table_name   VALUES   (...))    
  INSERT   DELAYED    
  LOAD   DATA   INFILE   /   LOAD_FILE()    
  使用多行INSERT一次插入多行。    
  SELECT   INTO   OUTFILE    
  LEFT   JOIN,   STRAIGHT   JOIN    
  LEFT   JOIN   ,结合IS   NULL    
  ORDER   BY可在某些情况下使用键码。    
  如果只查询在一个索引中的列,将只使用索引树解决查询。    
  联结一般比子查询快(对大多数SQL服务器亦如此)。    
  LIMIT    
  SELECT   *   from   table1   WHERE   a   >   10   LIMIT   10,20    
  DELETE   *   from   table1   WHERE   a   >   10   LIMIT   10    
  foo   IN   (常数列表)   高度优化。    
  GET_LOCK()/RELEASE_LOCK()    
  LOCK   TABLES    
  INSERT和SELECT可同时运行。    
  UDF函数可装载进一个正在运行的服务器。    
  压缩只读表。    
  CREATE   TEMPORARY   TABLE    
  CREATE   TABLE   ..   SELECT    
  带RAID选项的MyIASM表将文件分割成很多文件以突破某些文件系统的2G限制。    
  Delay_keys    
  复制功能    
   
  --------------------------------------------------------------------------------    
   
  二十二、MySQL何时使用索引    
   
  对一个键码使用>,   >=,   =,   <,   <=,   IF   NULL和BETWEEN    
  SELECT   *   FROM   table_name   WHERE   key_part1=1   and   key_part2   >   5;    
  SELECT   *   FROM   table_name   WHERE   key_part1   IS   NULL;    
   
  当使用不以通配符开始的LIKE    
  SELECT   *   FROM   table_name   WHERE   key_part1   LIKE   'jani%'    
   
  在进行联结时从另一个表中提取行时    
  SELECT   *   from   t1,t2   where   t1.col=t2.key_part    
   
  找出指定索引的MAX()或MIN()值    
  SELECT   MIN(key_part2),MAX(key_part2)   FROM   table_name   where   key_part1=10    
   
  一个键码的前缀使用ORDER   BY或GROUP   BY    
  SELECT   *   FROM   foo   ORDER   BY   key_part1,key_part2,key_part3    
   
  在所有用在查询中的列是键码的一部分时间    
  SELECT   key_part3   FROM   table_name   WHERE   key_part1=1    
   
  --------------------------------------------------------------------------------    
   
  二十三、MySQL何时不使用索引    
   
  如果MySQL能估计出它将可能比扫描整张表还要快时,则不使用索引。例如如果key_part1均匀分布在1和100之间,下列查询中使用索引就不是很好:    
  SELECT   *   FROM   table_name   where   key_part1   >   1   and   key_part1   <   90    
   
  如果使用HEAP表且不用=搜索所有键码部分。    
   
  在HEAP表上使用ORDER   BY。    
   
  如果不是用键码第一部分    
  SELECT   *   FROM   table_name   WHERE   key_part2=1    
   
  如果使用以一个通配符开始的LIKE    
  SELECT   *   FROM   table_name   WHERE   key_part1   LIKE   '%jani%'    
   
  搜索一个索引而在另一个索引上做ORDER   BY    
  SELECT   *   from   table_name   WHERE   key_part1   =   #   ORDER   BY   key2    
   
  --------------------------------------------------------------------------------    
   
  二十四、学会使用EXPLAIN    
   
  对于每一条你认为太慢的查询使用EXPLAIN!    
   
  mysql>   explain   select   t3.DateOfAction,   t1.TransactionID    
  ->   from   t1   join   t2   join   t3    
  ->   where   t2.ID   =   t1.TransactionID   and   t3.ID   =   t2.GroupID    
  ->   order   by   t3.DateOfAction,   t1.TransactionID;    
  +-------+--------+---------------+---------+---------+------------------+------+---------------------------------+    
  |   table   |   type   |   possible_keys   |   key   |   key_len   |   ref   |   rows   |   Extra   |    
  +-------+--------+---------------+---------+---------+------------------+------+---------------------------------+    
  |   t1   |   ALL   |   NULL   |   NULL   |   NULL   |   NULL   |   11   |   Using   temporary;   Using   filesort   |    
  |   t2   |   ref   |   ID   |   ID   |   4   |   t1.TransactionID   |   13   |   |    
  |   t3   |   eq_ref   |   PRIMARY   |   PRIMARY   |   4   |   t2.GroupID   |   1   |   |    
  +-------+--------+---------------+---------+---------+------------------+------+---------------------------------+    
   
  ALL和范围类型提示一个潜在的问题。    
       
   
   
  --------------------------------------------------------------------------------    
   
  二十五、学会使用SHOW   PROCESSLIST    
   
  使用SHOW   processlist来发现正在做什么:    
  +----+-------+-----------+----+---------+------+--------------+-------------------------------------+    
  |   Id   |   User   |   Host   |   db   |   Command   |   Time   |   State   |   Info   |    
  +----+-------+-----------+----+---------+------+--------------+-------------------------------------+    
  |   6   |   monty   |   localhost   |   bp   |   Query   |   15   |   Sending   data   |   select   *   from   station,station   as   s1   |    
  |   8   |   monty   |   localhost   |   |   Query   |   0   |   |   show   processlist   |    
  +----+-------+-----------+----+---------+------+--------------+-------------------------------------+    
   
  在mysql或mysqladmin中用KILL来杀死溜掉的线程。    
  --------------------------------------------------------------------------------    
   
  二十六、如何知晓MySQL解决一条查询    
   
  运行项列命令并试图弄明白其输出:    
  SHOW   VARIABLES;    
  SHOW   COLUMNS   FROM   ...\G    
  EXPLAIN   SELECT   ...\G    
  FLUSH   STATUS;    
  SELECT   ...;    
  SHOW   STATUS;    
   
  --------------------------------------------------------------------------------    
   
  二十七、MySQL非常不错    
   
  日志    
  在进行很多连接时,连接非常快。    
  同时使用SELECT和INSERT的场合。    
  在不把更新与耗时太长的选择结合时。    
  在大多数选择/更新使用唯一键码时。    
  在使用没有长时间冲突锁定的多个表时。    
  在用大表时(MySQL使用一个非常紧凑的表格式)。    
   
  --------------------------------------------------------------------------------    
   
  二十八、MySQL应避免的事情    
   
  用删掉的行更新或插入表,结合要耗时长的SELECT。    
  在能放在WHERE子句中的列上用HAVING。    
  不使用键码或键码不够唯一而进行JOIN。    
  在不同列类型的列上JOIN。    
  在不使用=匹配整个键码时使用HEAP表。    
  在MySQL监控程序中忘记在UPDATE或DELETE中使用一条WHERE子句。如果想这样做,使用mysql客户程序的--i-am-a-dummy选项。    
   
  --------------------------------------------------------------------------------    
   
  二十九、MySQL各种锁定    
   
  内部表锁定    
  LOCK   TABLES(所有表类型适用)    
  GET   LOCK()/RELEASE   LOCK()    
  页面锁定(对BDB表)    
  ALTER   TABLE也在BDB表上进行表锁定    
  LOCK   TABLES允许一个表有多个读者和一个写者。    
  一般WHERE锁定具有比READ锁定高的优先级以避免让写入方干等。对于不重要的写入方,可以使用LOW_PRIORITY关键字让锁定处理器优选读取方。    
  UPDATE   LOW_PRIORITY   SET   value=10   WHERE   id=10;    
   
   
   
  --------------------------------------------------------------------------------    
   
  三十、给MySQL更多信息以更好地解决问题的技巧    
   
  注意你总能去掉(加注释)MySQL功能以使查询可移植:    
   
  SELECT   /*!   SQL_BUFFER_RESULTS   */   ...    
  SELECT   SQL_BUFFER_RESULTS   ...    
  将强制MySQL生成一个临时结果集。只要所有临时结果集生成后,所有表上的锁定均被释放。这能在遇到表锁定问题时或要花很长时间将结果传给客户端时有所帮助。    
  SELECT   SQL_SMALL_RESULT   ...   GROUP   BY   ...    
  告诉优化器结果集将只包含很少的行。    
  SELECT   SQL_BIG_RESULT   ...   GROUP   BY   ...    
  告诉优化器结果集将包含很多行。    
  SELECT   STRAIGHT_JOIN   ...    
  强制优化器以出现在FROM子句中的次序联结表。    
  SELECT   ...   FROM   table_name   [USE   INDEX   (index_list)   |   IGNORE   INDEX   (index_list)]   table_name2    
  强制MySQL使用/忽略列出的索引。    
   
  --------------------------------------------------------------------------------

二十一、MySQL表高速缓存工作原理    
   
  每个MyISAM表的打开实例(instance)使用一个索引文件和一个数据文件。如果表被两个线程使用或在同一条查询中使用两次,MyIASM将共享索引文件而是打开数据文件的另一个实例。    
  如果所有在高速缓存中的表都在使用,缓存将临时增加到比表缓存尺寸大些。如果是这样,下一个被释放的表将被关闭。    
  你可以通过检查mysqld的Opened_tables变量以检查表缓存是否太小。如果该值太高,你应该增大表高速缓存。    
       
   
  --------------------------------------------------------------------------------    
   
  二十二、MySQL扩展/优化-提供更快的速度    
   
  使用优化的表类型(HEAP、MyIASM或BDB表)。    
  对数据使用优化的列。    
  如果可能使用定长行。    
  使用不同的锁定类型(SELECT   HIGH_PRIORITY,INSERT   LOW_PRIORITY)    
  Auto_increment    
  REPLACE   (REPLACE   INTO   table_name   VALUES   (...))    
  INSERT   DELAYED    
  LOAD   DATA   INFILE   /   LOAD_FILE()    
  使用多行INSERT一次插入多行。    
  SELECT   INTO   OUTFILE    
  LEFT   JOIN,   STRAIGHT   JOIN    
  LEFT   JOIN   ,结合IS   NULL    
  ORDER   BY可在某些情况下使用键码。    
  如果只查询在一个索引中的列,将只使用索引树解决查询。    
  联结一般比子查询快(对大多数SQL服务器亦如此)。    
  LIMIT    
  SELECT   *   from   table1   WHERE   a   >   10   LIMIT   10,20    
  DELETE   *   from   table1   WHERE   a   >   10   LIMIT   10    
  foo   IN   (常数列表)   高度优化。    
  GET_LOCK()/RELEASE_LOCK()    
  LOCK   TABLES    
  INSERT和SELECT可同时运行。    
  UDF函数可装载进一个正在运行的服务器。    
  压缩只读表。    
  CREATE   TEMPORARY   TABLE    
  CREATE   TABLE   ..   SELECT    
  带RAID选项的MyIASM表将文件分割成很多文件以突破某些文件系统的2G限制。    
  Delay_keys    
  复制功能    
   
  --------------------------------------------------------------------------------    
   
  二十二、MySQL何时使用索引    
   
  对一个键码使用>,   >=,   =,   <,   <=,   IF   NULL和BETWEEN    
  SELECT   *   FROM   table_name   WHERE   key_part1=1   and   key_part2   >   5;    
  SELECT   *   FROM   table_name   WHERE   key_part1   IS   NULL;    
   
  当使用不以通配符开始的LIKE    
  SELECT   *   FROM   table_name   WHERE   key_part1   LIKE   'jani%'    
   
  在进行联结时从另一个表中提取行时    
  SELECT   *   from   t1,t2   where   t1.col=t2.key_part    
   
  找出指定索引的MAX()或MIN()值    
  SELECT   MIN(key_part2),MAX(key_part2)   FROM   table_name   where   key_part1=10    
   
  一个键码的前缀使用ORDER   BY或GROUP   BY    
  SELECT   *   FROM   foo   ORDER   BY   key_part1,key_part2,key_part3    
   
  在所有用在查询中的列是键码的一部分时间    
  SELECT   key_part3   FROM   table_name   WHERE   key_part1=1    
   
  --------------------------------------------------------------------------------    
   
  二十三、MySQL何时不使用索引    
   
  如果MySQL能估计出它将可能比扫描整张表还要快时,则不使用索引。例如如果key_part1均匀分布在1和100之间,下列查询中使用索引就不是很好:    
  SELECT   *   FROM   table_name   where   key_part1   >   1   and   key_part1   <   90    
   
  如果使用HEAP表且不用=搜索所有键码部分。    
   
  在HEAP表上使用ORDER   BY。    
   
  如果不是用键码第一部分    
  SELECT   *   FROM   table_name   WHERE   key_part2=1    
   
  如果使用以一个通配符开始的LIKE    
  SELECT   *   FROM   table_name   WHERE   key_part1   LIKE   '%jani%'    
   
  搜索一个索引而在另一个索引上做ORDER   BY    
  SELECT   *   from   table_name   WHERE   key_part1   =   #   ORDER   BY   key2    
   
  --------------------------------------------------------------------------------    
   
  二十四、学会使用EXPLAIN    
   
  对于每一条你认为太慢的查询使用EXPLAIN!    
   
  mysql>   explain   select   t3.DateOfAction,   t1.TransactionID    
  ->   from   t1   join   t2   join   t3    
  ->   where   t2.ID   =   t1.TransactionID   and   t3.ID   =   t2.GroupID    
  ->   order   by   t3.DateOfAction,   t1.TransactionID;    
  +-------+--------+---------------+---------+---------+------------------+------+---------------------------------+    
  |   table   |   type   |   possible_keys   |   key   |   key_len   |   ref   |   rows   |   Extra   |    
  +-------+--------+---------------+---------+---------+------------------+------+---------------------------------+    
  |   t1   |   ALL   |   NULL   |   NULL   |   NULL   |   NULL   |   11   |   Using   temporary;   Using   filesort   |    
  |   t2   |   ref   |   ID   |   ID   |   4   |   t1.TransactionID   |   13   |   |    
  |   t3   |   eq_ref   |   PRIMARY   |   PRIMARY   |   4   |   t2.GroupID   |   1   |   |    
  +-------+--------+---------------+---------+---------+------------------+------+---------------------------------+    
   
  ALL和范围类型提示一个潜在的问题。    
       
   
   
  --------------------------------------------------------------------------------    
   
  二十五、学会使用SHOW   PROCESSLIST    
   
  使用SHOW   processlist来发现正在做什么:    
  +----+-------+-----------+----+---------+------+--------------+-------------------------------------+    
  |   Id   |   User   |   Host   |   db   |   Command   |   Time   |   State   |   Info   |    
  +----+-------+-----------+----+---------+------+--------------+-------------------------------------+    
  |   6   |   monty   |   localhost   |   bp   |   Query   |   15   |   Sending   data   |   select   *   from   station,station   as   s1   |    
  |   8   |   monty   |   localhost   |   |   Query   |   0   |   |   show   processlist   |    
  +----+-------+-----------+----+---------+------+--------------+-------------------------------------+    
   
  在mysql或mysqladmin中用KILL来杀死溜掉的线程。    
  --------------------------------------------------------------------------------    
   
  二十六、如何知晓MySQL解决一条查询    
   
  运行项列命令并试图弄明白其输出:    
  SHOW   VARIABLES;    
  SHOW   COLUMNS   FROM   ...\G    
  EXPLAIN   SELECT   ...\G    
  FLUSH   STATUS;    
  SELECT   ...;    
  SHOW   STATUS;    
   
  --------------------------------------------------------------------------------    
   
  二十七、MySQL非常不错    
   
  日志    
  在进行很多连接时,连接非常快。    
  同时使用SELECT和INSERT的场合。    
  在不把更新与耗时太长的选择结合时。    
  在大多数选择/更新使用唯一键码时。    
  在使用没有长时间冲突锁定的多个表时。    
  在用大表时(MySQL使用一个非常紧凑的表格式)。    
   
  --------------------------------------------------------------------------------    
   
  二十八、MySQL应避免的事情    
   
  用删掉的行更新或插入表,结合要耗时长的SELECT。    
  在能放在WHERE子句中的列上用HAVING。    
  不使用键码或键码不够唯一而进行JOIN。    
  在不同列类型的列上JOIN。    
  在不使用=匹配整个键码时使用HEAP表。    
  在MySQL监控程序中忘记在UPDATE或DELETE中使用一条WHERE子句。如果想这样做,使用mysql客户程序的--i-am-a-dummy选项。    
   
  --------------------------------------------------------------------------------    
   
  二十九、MySQL各种锁定    
   
  内部表锁定    
  LOCK   TABLES(所有表类型适用)    
  GET   LOCK()/RELEASE   LOCK()    
  页面锁定(对BDB表)    
  ALTER   TABLE也在BDB表上进行表锁定    
  LOCK   TABLES允许一个表有多个读者和一个写者。    
  一般WHERE锁定具有比READ锁定高的优先级以避免让写入方干等。对于不重要的写入方,可以使用LOW_PRIORITY关键字让锁定处理器优选读取方。    
  UPDATE   LOW_PRIORITY   SET   value=10   WHERE   id=10;    
   
   
   
  --------------------------------------------------------------------------------    
   
  三十、给MySQL更多信息以更好地解决问题的技巧    
   
  注意你总能去掉(加注释)MySQL功能以使查询可移植:    
   
  SELECT   /*!   SQL_BUFFER_RESULTS   */   ...    
  SELECT   SQL_BUFFER_RESULTS   ...    
  将强制MySQL生成一个临时结果集。只要所有临时结果集生成后,所有表上的锁定均被释放。这能在遇到表锁定问题时或要花很长时间将结果传给客户端时有所帮助。    
  SELECT   SQL_SMALL_RESULT   ...   GROUP   BY   ...    
  告诉优化器结果集将只包含很少的行。    
  SELECT   SQL_BIG_RESULT   ...   GROUP   BY   ...    
  告诉优化器结果集将包含很多行。    
  SELECT   STRAIGHT_JOIN   ...    
  强制优化器以出现在FROM子句中的次序联结表。    
  SELECT   ...   FROM   table_name   [USE   INDEX   (index_list)   |   IGNORE   INDEX   (index_list)]   table_name2    
  强制MySQL使用/忽略列出的索引。     
   

  评论这张
 
阅读(85)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018