| 導購 | 订阅 | 在线投稿
分享
 
 
當前位置: 王朝網路 >> mysql >> 個人經驗總結:MySQL數據庫優化技巧集錦
 

個人經驗總結:MySQL數據庫優化技巧集錦

2008-07-10 05:54:11  編輯來源:互聯網  简体版  手機版  評論  字體: ||
 
 
  一、我們可以且應該優化什麽?

  硬件

  操作系統/軟件庫

  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使用/忽略列出的索引。

  三十一、事務的例子

  MyIASM表如何進行事務處理:

  mysql> LOCK TABLES trans READ, customer WRITE;

  mysql> select sum(value) from trans where customer_id=some_id;

  mysql> update customer set total_value=sum_from_previous_statement

  where customer_id=some_id;

  mysql> UNLOCK TABLES;

  BDB表如何進行事務:

  mysql> BEGIN WORK;

  mysql> select sum(value) from trans where customer_id=some_id;

  mysql> update customer set total_value=sum_from_previous_statement

  where customer_id=some_id;

  mysql> COMMIT;

  注意你可以通過下列語句回避事務:

  UPDATE customer SET value=value+new_value WHERE customer_id=some_id;

  三十二、使用REPLACE的例子

  REPLACE的功能極像INSERT,除了如果一條老記錄在一個唯一索引上具有與新紀錄相同的值,那麽老記錄在新紀錄插入前則被刪除。不使用

  SELECT 1 FROM t1 WHERE key=#

  IF found-row

  LOCK TABLES t1

  DELETE FROM t1 WHERE key1=#

  INSERT INTO t1 VALUES (...)

  UNLOCK TABLES t1;

  ENDIF

  而用

  REPLACE INTO t1 VALUES (...)

  三十三、一般技巧

  使用短主鍵。聯結表時使用數字而非字符串。

  當使用多部分鍵碼時,第一部分應該時最常用的部分。

  有疑問時,首先使用更多重複的列以獲得更好地鍵碼壓縮。

  如果在同一台機器上運行MySQL客戶和服務器,那麽在連接MySQL時則使用套接字而不是TCP/IP(這可以提高性能7.5%)。可在連接MySQL服務器時不指定主機名或主機名爲localhost來做到。

  如果可能,使用--skip-locking(在某些OS上爲默認),這將關閉外部鎖定並將提高性能。

  使用應用層哈希值而非長鍵碼:

  SELECT * FROM table_name WHERE hash=MD5(concat(col1,col2)) AND

  col_1='constant' AND col_2='constant'

  在文件中保存需要以文件形式訪問的BLOB,在數據庫中只保存文件名。

  刪除所有行比刪除一大部分行要快。

  如果SQL不夠快,研究一下訪問數據的較底層接口。

  三十四、使用MySQL 3.23的好處

  MyISAM:可移植的大表格式

  HEAP:內存中的表

  Berkeley DB:支持事務的表。

  衆多提高的限制

  動態字符集

  更多的STATUS變量

  CHECK和REPAIR表

  更快的GROUP BY和DISTINCT

  LEFT JOIN ... IF NULL的優化

  CREATE TABLE ... SELECT

  CREATE TEMPORARY table_name (...)

  臨時HEAP表到MyISAM表的自動轉換

  複制

  mysqlhotcopy腳本

  三十五、正在積極開發的重要功能

  改進事務處理

  失敗安全的複制

  正文搜索

  多個表的刪除(之後完成多個表的更新)

  更好的鍵碼緩存

  原子RENAME (RENAME TABLE foo as foo_old, foo_new as foo)

  查詢高速緩存

  MERGE TABLES

  一個更好的GUI客戶程序
 
 
 
上一篇《lighttpd+PHP(FAST-CGI)+MySQL的學習筆記》
下一篇《教你在MySQL 5.0以上版本中配置主從庫》
 
 
 
 
 
 
日版寵物情人插曲《Winding Road》歌詞

日版寵物情人2017的插曲,很帶節奏感,日語的,女生唱的。 最後聽見是在第8集的時候女主手割傷了,然後男主用嘴幫她吸了一下,插曲就出來了。 歌手:Def...

兄弟共妻,我成了他們夜裏的美食

老鍾家的兩個兒子很特別,就是跟其他的人不太一樣,魔一般的執著。兄弟倆都到了要結婚的年齡了,不管自家老爹怎麽磨破嘴皮子,兄弟倆說不娶就不娶,老父母爲兄弟兩操碎了心...

如何磨出破洞牛仔褲?牛仔褲怎麽剪破洞?

把牛仔褲磨出有線的破洞 1、具體工具就是磨腳石,下面墊一個硬物,然後用磨腳石一直磨一直磨,到把那塊磨薄了,用手撕開就好了。出來的洞啊很自然的。需要貓須的話調幾...

我就是掃描下圖得到了敬業福和愛國福

先來看下敬業福和愛國福 今年春節,支付寶再次推出了“五福紅包”活動,表示要“把欠大家的敬業福都還給大家”。 今天該活動正式啓動,和去年一樣,需要收集“五福”...

冰箱異味産生的原因和臭味去除的方法

有時候我們打開冰箱就會聞到一股異味,冰箱裏的這種異味是因爲一些物質發出的氣味的混合體,聞起來讓人惡心。 産生這些異味的主要原因有以下幾點。 1、很多人有這種習...

《極品家丁》1-31集大結局分集劇情介紹

簡介 《極品家丁》講述了現代白領林晚榮無意回到古代金陵,並追隨蕭二小姐化名“林三”進入蕭府,不料卻陰差陽錯上演了一出低級家丁拼搏上位的“林三升職記”。...

李溪芮《極品家丁》片尾曲《你就是我最愛的寶寶》歌詞

你就是我最愛的寶寶 - 李溪芮 (電視劇《極品家丁》片尾曲) 作詞:常馨內 作曲:常馨內 你的眉 又鬼馬的挑 你的嘴 又壞壞的笑 上一秒吵鬧 下...

烏梅的功效與作用以及烏梅的食用禁忌有哪些?

烏梅,又稱春梅,中醫認爲,烏梅味酸,性溫,無毒,具有安心、除熱、下氣、祛痰、止渴調中、殺蟲的功效,治肢體痛、肺痨病。烏梅泡水喝能治傷寒煩熱、止吐瀉,與幹姜一起制...

什麽是脂肪粒?如何消除臉部脂肪粒?

什麽是脂肪粒 在我們的臉上總會長一個個像脂肪的小顆粒,弄也弄不掉,而且顔色還是白白的。它既不是粉刺也不是其他的任何痘痘,它就是脂肪粒。 脂肪粒雖然也是由油脂...

網絡安全治理:國家安全保障的主要方向是打擊犯罪,而不是處置和懲罰受害者

來源:中國青年報 新的攻擊方法不斷湧現,黑客幾乎永遠占據網絡攻擊的上風,我們不可能通過技術手段杜絕網絡攻擊。國家安全保障的主要方向是打擊犯罪,而不是處置和懲罰...

河南夫妻在溫嶺網絡直播“造人”內容涉黃被刑事拘留

夫妻網絡直播“造人”爆紅   1月9日,溫嶺城北派出所接到南京警方的協查通告,他們近期打掉了一個涉黃直播APP平台。而根據掌握的線索,其中有一對涉案的夫妻主播...

如何防止牆紙老化?牆紙變舊變黃怎麽辦?

如何防止牆紙老化? (1)選擇透氣性好的牆紙 市場上牆紙的材質分無紡布的、木纖維的、PVC的、玻璃纖維基材的、布面的等,相對而言,PVC材質的牆紙最不透氣...

鮮肌之謎非日本生産VS鮮肌之謎假日貨是謠言

觀點一:破日本銷售量的“鮮肌之謎” 非日本生産 近一段時間,淘寶上架了一款名爲“鮮肌之謎的” 鲑魚卵巢美容液,號稱是最近日本的一款推出的全新護膚品,産品本身所...

中國最美古詩詞精選摘抄

系腰裙(北宋詞人 張先) 惜霜蟾照夜雲天,朦胧影、畫勾闌。人情縱似長情月,算一年年。又能得、幾番圓。 欲寄西江題葉字,流不到、五亭前。東池始有荷新綠,尚小如...

關于女人的經典語句

關于女人的經典語句1、【做一個獨立的女人】 思想獨立:有主見、有自己的人生觀、價值觀。有上進心,永遠不放棄自己的理想,做一份自己喜愛的事業,擁有快樂和成就...

未來我們可以和性愛機器人結婚嗎?

你想體驗機器人性愛嗎?你想和性愛機器人結婚嗎?如果你想,機器人有拒絕你的權利嗎? 近日,第二屆“國際人類-機器人性愛研討會”大會在倫敦金史密斯大學落下帷幕。而...

全球最變態的十個地方

10.土耳其地下洞穴城市 變態指數:★★☆☆☆ 這是土耳其卡帕多西亞的一個著名景點,傳說是當年基督教徒們爲了躲避戰爭而在此修建。裏面曾住著20000人,...

科學家稱,人類死亡後意識將在另外一個宇宙中繼續存活

據英國《每日快報》報道,一位科學家兼理論家Robert Lanza博士宣稱,世界上並不存在人類死亡,死亡的只是身體。他認爲我們的意識借助我們體內的能量生存,而且...

《屏裏狐》片頭曲《我愛狐狸精》歌詞是什麽?

《我愛狐狸精》 - 劉馨棋   (電視劇《屏裏狐》主題曲)   作詞:金十三&李旦   作曲:劉嘉   狐狸精 狐狸仙   千年修...

 
 
 
一、我們可以且應該優化什麽? 硬件 操作系統/軟件庫 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構造。[url]www.mysql.com上的Crash-me頁可以幫助你。[/url] 爲操作系統/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使用/忽略列出的索引。 三十一、事務的例子 MyIASM表如何進行事務處理: mysql> LOCK TABLES trans READ, customer WRITE; mysql> select sum(value) from trans where customer_id=some_id; mysql> update customer set total_value=sum_from_previous_statement where customer_id=some_id; mysql> UNLOCK TABLES; BDB表如何進行事務: mysql> BEGIN WORK; mysql> select sum(value) from trans where customer_id=some_id; mysql> update customer set total_value=sum_from_previous_statement where customer_id=some_id; mysql> COMMIT; 注意你可以通過下列語句回避事務: UPDATE customer SET value=value+new_value WHERE customer_id=some_id; 三十二、使用REPLACE的例子 REPLACE的功能極像INSERT,除了如果一條老記錄在一個唯一索引上具有與新紀錄相同的值,那麽老記錄在新紀錄插入前則被刪除。不使用 SELECT 1 FROM t1 WHERE key=# IF found-row LOCK TABLES t1 DELETE FROM t1 WHERE key1=# INSERT INTO t1 VALUES (...) UNLOCK TABLES t1; ENDIF 而用 REPLACE INTO t1 VALUES (...) 三十三、一般技巧 使用短主鍵。聯結表時使用數字而非字符串。 當使用多部分鍵碼時,第一部分應該時最常用的部分。 有疑問時,首先使用更多重複的列以獲得更好地鍵碼壓縮。 如果在同一台機器上運行MySQL客戶和服務器,那麽在連接MySQL時則使用套接字而不是TCP/IP(這可以提高性能7.5%)。可在連接MySQL服務器時不指定主機名或主機名爲localhost來做到。 如果可能,使用--skip-locking(在某些OS上爲默認),這將關閉外部鎖定並將提高性能。 使用應用層哈希值而非長鍵碼: SELECT * FROM table_name WHERE hash=MD5(concat(col1,col2)) AND col_1='constant' AND col_2='constant' 在文件中保存需要以文件形式訪問的BLOB,在數據庫中只保存文件名。 刪除所有行比刪除一大部分行要快。 如果SQL不夠快,研究一下訪問數據的較底層接口。 三十四、使用MySQL 3.23的好處 MyISAM:可移植的大表格式 HEAP:內存中的表 Berkeley DB:支持事務的表。 衆多提高的限制 動態字符集 更多的STATUS變量 CHECK和REPAIR表 更快的GROUP BY和DISTINCT LEFT JOIN ... IF NULL的優化 CREATE TABLE ... SELECT CREATE TEMPORARY table_name (...) 臨時HEAP表到MyISAM表的自動轉換 複制 mysqlhotcopy腳本 三十五、正在積極開發的重要功能 改進事務處理 失敗安全的複制 正文搜索 多個表的刪除(之後完成多個表的更新) 更好的鍵碼緩存 原子RENAME (RENAME TABLE foo as foo_old, foo_new as foo) 查詢高速緩存 MERGE TABLES 一個更好的GUI客戶程序
󰈣󰈤
 
 
 
  免責聲明:本文僅代表作者個人觀點,與王朝網路無關。王朝網路登載此文出於傳遞更多信息之目的,並不意味著贊同其觀點或證實其描述,其原創性以及文中陳述文字和內容未經本站證實,對本文以及其中全部或者部分內容、文字的真實性、完整性、及時性本站不作任何保證或承諾,請讀者僅作參考,並請自行核實相關內容。
 
 
小龍女彤彤之情溢皇都
龔潔
智能手機形象美女
崔潔彤
回家的路上----
中國一站(哈爾濱)
清明植物園的花。
桃花堤印象之豎版
 
>>返回首頁<<
 
 熱帖排行
 
 
 
 
© 2005- 王朝網路 版權所有