mysql內存數據庫性能（mysql 內存數據庫）

本文目錄一覽：

• 1、面試中常問：mysql數據庫做哪些優化也提高mysql性能
• 2、mysql數據庫可靠性分析
• 3、mysql數據庫最大能支持多少並發量
• 4、哪些因素會對mysql數據庫服務器性能造成影響
• 5、如何評估和測試Mysql及oracle數據庫性能
• 6、mysql數據庫性能測試

面試中常問：mysql數據庫做哪些優化也提高mysql性能

在開始演示之前，我們先介紹下兩個概念。

概念一，數據的可選擇性基數，也就是常說的cardinality值。

查詢優化器在生成各種執行計劃之前，得先從統計信息中取得相關數據，這樣才能估算每步操作所涉及到的記錄數，而這個相關數據就是cardinality。簡單來說，就是每個值在每個字段中的唯一值分布狀態。

比如表t1有100行記錄，其中一列為f1。f1中唯一值的個數可以是100個，也可以是1個，當然也可以是1到100之間的任何一個數字。這裡唯一值越的多少，就是這個列的可選擇基數。

那看到這裡我們就明白了，為什麼要在基數高的字段上建立索引，而基數低的的字段建立索引反而沒有全表掃描來的快。當然這個只是一方面，至於更深入的探討就不在我這篇探討的範圍了。

概念二，關於HINT的使用。

這裡我來說下HINT是什麼，在什麼時候用。

HINT簡單來說就是在某些特定的場景下人工協助MySQL優化器的工作，使她生成最優的執行計劃。一般來說，優化器的執行計劃都是最優化的，不過在某些特定場景下，執行計劃可能不是最優化。

比如：表t1經過大量的頻繁更新操作，（UPDATE,DELETE,INSERT），cardinality已經很不準確了，這時候剛好執行了一條SQL，那麼有可能這條SQL的執行計劃就不是最優的。為什麼說有可能呢？

來看下具體演示

譬如，以下兩條SQL，

A：

select * from t1 where f1 = 20;

B：

select * from t1 where f1 = 30;

如果f1的值剛好頻繁更新的值為30，並且沒有達到MySQL自動更新cardinality值的臨界值或者說用戶設置了手動更新又或者用戶減少了sample page等等，那麼對這兩條語句來說，可能不準確的就是B了。

這裡順帶說下，MySQL提供了自動更新和手動更新表cardinality值的方法，因篇幅有限，需要的可以查閱手冊。

那回到正題上，MySQL 8.0 帶來了幾個HINT，我今天就舉個index_merge的例子。

示例表結構：

mysql desc t1;+————+————–+——+—–+———+—————-+| Field      | Type         | Null | Key | Default | Extra          |+————+————–+——+—–+———+—————-+| id         | int(11)      | NO   | PRI | NULL    | auto_increment || rank1      | int(11)      | YES  | MUL | NULL    |                || rank2      | int(11)      | YES  | MUL | NULL    |                || log_time   | datetime     | YES  | MUL | NULL    |                || prefix_uid | varchar(100) | YES  |     | NULL    |                || desc1      | text         | YES  |     | NULL    |                || rank3      | int(11)      | YES  | MUL | NULL    |                |+————+————–+——+—–+———+—————-+7 rows in set (0.00 sec)

表記錄數：

mysql select count(*) from t1;+———-+| count(*) |+———-+|    32768 |+———-+1 row in set (0.01 sec)

這裡我們兩條經典的SQL：

SQL C：

select * from t1 where rank1 = 1 or rank2 = 2 or rank3 = 2;

SQL D：

select * from t1 where rank1 =100  and rank2 =100  and rank3 =100;

表t1實際上在rank1,rank2,rank3三列上分別有一個二級索引。

那我們來看SQL C的查詢計劃。

顯然，沒有用到任何索引，掃描的行數為32034，cost為3243.65。

mysql explain  format=json select * from t1  where rank1 =1 or rank2 = 2 or rank3 = 2\G*************************** 1. row ***************************EXPLAIN: {  “query_block”: {    “select_id”: 1,    “cost_info”: {      “query_cost”: “3243.65”    },    “table”: {      “table_name”: “t1”,      “access_type”: “ALL”,      “possible_keys”: [        “idx_rank1”,        “idx_rank2”,        “idx_rank3”      ],      “rows_examined_per_scan”: 32034,      “rows_produced_per_join”: 115,      “filtered”: “0.36”,      “cost_info”: {        “read_cost”: “3232.07”,        “eval_cost”: “11.58”,        “prefix_cost”: “3243.65”,        “data_read_per_join”: “49K”      },      “used_columns”: [        “id”,        “rank1”,        “rank2”,        “log_time”,        “prefix_uid”,        “desc1”,        “rank3”      ],      “attached_condition”: “((`ytt`.`t1`.`rank1` = 1) or (`ytt`.`t1`.`rank2` = 2) or (`ytt`.`t1`.`rank3` = 2))”    }  }}1 row in set, 1 warning (0.00 sec)

我們加上hint給相同的查詢，再次看看查詢計劃。

這個時候用到了index_merge,union了三個列。掃描的行數為1103，cost為441.09，明顯比之前的快了好幾倍。

mysql explain  format=json select /*+ index_merge(t1) */ * from t1  where rank1 =1 or rank2 = 2 or rank3 = 2\G*************************** 1. row ***************************EXPLAIN: {  “query_block”: {    “select_id”: 1,    “cost_info”: {      “query_cost”: “441.09”    },    “table”: {      “table_name”: “t1”,      “access_type”: “index_merge”,      “possible_keys”: [        “idx_rank1”,        “idx_rank2”,        “idx_rank3”      ],      “key”: “union(idx_rank1,idx_rank2,idx_rank3)”,      “key_length”: “5,5,5”,      “rows_examined_per_scan”: 1103,      “rows_produced_per_join”: 1103,      “filtered”: “100.00”,      “cost_info”: {        “read_cost”: “330.79”,        “eval_cost”: “110.30”,        “prefix_cost”: “441.09”,        “data_read_per_join”: “473K”      },      “used_columns”: [        “id”,        “rank1”,        “rank2”,        “log_time”,        “prefix_uid”,        “desc1”,        “rank3”      ],      “attached_condition”: “((`ytt`.`t1`.`rank1` = 1) or (`ytt`.`t1`.`rank2` = 2) or (`ytt`.`t1`.`rank3` = 2))”    }  }}1 row in set, 1 warning (0.00 sec)

我們再看下SQL D的計劃：

不加HINT，

mysql explain format=json select * from t1 where rank1 =100 and rank2 =100 and rank3 =100\G*************************** 1. row ***************************EXPLAIN: {  “query_block”: {    “select_id”: 1,    “cost_info”: {      “query_cost”: “534.34”    },    “table”: {      “table_name”: “t1”,      “access_type”: “ref”,      “possible_keys”: [        “idx_rank1”,        “idx_rank2”,        “idx_rank3”      ],      “key”: “idx_rank1”,      “used_key_parts”: [        “rank1”      ],      “key_length”: “5”,      “ref”: [        “const”      ],      “rows_examined_per_scan”: 555,      “rows_produced_per_join”: 0,      “filtered”: “0.07”,      “cost_info”: {        “read_cost”: “478.84”,        “eval_cost”: “0.04”,        “prefix_cost”: “534.34”,        “data_read_per_join”: “176”      },      “used_columns”: [        “id”,        “rank1”,        “rank2”,        “log_time”,        “prefix_uid”,        “desc1”,        “rank3”      ],      “attached_condition”: “((`ytt`.`t1`.`rank3` = 100) and (`ytt`.`t1`.`rank2` = 100))”    }  }}1 row in set, 1 warning (0.00 sec)

加了HINT，

mysql explain format=json select /*+ index_merge(t1)*/ * from t1 where rank1 =100 and rank2 =100 and rank3 =100\G*************************** 1. row ***************************EXPLAIN: {  “query_block”: {    “select_id”: 1,    “cost_info”: {      “query_cost”: “5.23”    },    “table”: {      “table_name”: “t1”,      “access_type”: “index_merge”,      “possible_keys”: [        “idx_rank1”,        “idx_rank2”,        “idx_rank3”      ],      “key”: “intersect(idx_rank1,idx_rank2,idx_rank3)”,      “key_length”: “5,5,5”,      “rows_examined_per_scan”: 1,      “rows_produced_per_join”: 1,      “filtered”: “100.00”,      “cost_info”: {        “read_cost”: “5.13”,        “eval_cost”: “0.10”,        “prefix_cost”: “5.23”,        “data_read_per_join”: “440”      },      “used_columns”: [        “id”,        “rank1”,        “rank2”,        “log_time”,        “prefix_uid”,        “desc1”,        “rank3”      ],      “attached_condition”: “((`ytt`.`t1`.`rank3` = 100) and (`ytt`.`t1`.`rank2` = 100) and (`ytt`.`t1`.`rank1` = 100))”    }  }}1 row in set, 1 warning (0.00 sec)

對比下以上兩個，加了HINT的比不加HINT的cost小了100倍。

總結下，就是說表的cardinality值影響這張的查詢計劃，如果這個值沒有正常更新的話，就需要手工加HINT了。相信MySQL未來的版本會帶來更多的HINT。

mysql數據庫可靠性分析

mysql數據庫有undo空間

5種mysql做可靠性分析的方案：

1．MySQL Clustering(ndb-cluster stogare)

簡介：

MySQL公司以存儲引擎方式提供的高可靠性方案，是事務安全的，實時複製數據，可用於需要高可靠性及負載均衡的場合。該方案至少需要三個節點服務器才能達到較好的效果。

成本：

節點服務器對RAM的需求很大，與數據庫大小呈線性比例；

最好使用千兆以太網絡；

還需要使用Dolphin公司提供的昂貴的SCI卡。

優點：

可用於負載均衡場合；

可用於高可靠性場合；

高伸縮性；

真正的數據庫冗餘；

容易維護。

缺點：

隨着數據庫的變大，對RAM的需求變得更大，因此成本很高；

速度：

幾乎 比典型的單獨服務器（無千兆以太網，無SCI卡，存儲引擎相關的限制少）慢10倍。

應用場合：

冗餘，高可靠性，負載均衡

2. MySQL / GFS-GNBD/ HA (Active/Passive)

簡介：

如果多個MySQL服務器使用共享硬盤作為數據存儲，此方案如何？

GFS/GNBD可以提供所需的共享硬盤。

GFS是事務安全的文件系統。同一時刻你可以讓一個MySQL使用共享數據。

成本：

最多n台高性能服務器的成本，其中一個激活的，其他作為備份服務器。

優點：

高可靠性

某種程度的冗餘

按照高可靠性進行伸縮

缺點：

沒有負載均衡

沒有保證的冗餘

無法對寫操作進行伸縮

速度：

單獨服務器的2倍。對讀操作支持得較好。

應用場合：

需要高可靠性的、讀操作密集型的應用

3. MySQL / DRBD / HA (Active/Passive)

簡介：

如果多個MySQL服務器使用共享硬盤作為數據存儲，此方案如何？

DRBD可以提供這樣的共享硬盤。DRBD可以被設置成事務安全的。

同一時刻你可以讓一個MySQL使用共享數據。

成本：

最多n台高性能服務器的成本，其中一個激活的，而其他則作為備份服務器。

優點：

高可靠性；

一定程度的冗餘；

以高可靠性名義來看是可伸縮的。

缺點：

沒有負載均衡

沒有保證的冗餘

在寫負載方面沒有伸縮性

速度：

在讀寫方面相當於單獨服務器

應用場合

需要高可靠性、讀操作密集型的應用

4. MySQL Write Master / Multiple MySQL Read Slaves (Active/Active)

簡介：

考慮不同的讀、寫DB數據庫連接的情況。可以使用一台主服務器用於寫操作，而採用n台從服務器用於讀操作。

成本：

最多1台高性能寫服務器，n台讀服務器的成本

優點:

讀操作的高可靠性；

讀操作的負載均衡；

在讀操作負載均衡方面是可伸縮的。

缺點：

無寫操作的高可靠性；

無寫操作的負載均衡；

在寫操作方面無伸縮性；

速度：

同單獨服務器；在讀操作方面支持得較好

應用場合

讀操作密集型的、需要高可靠性和負載均衡的應用。

5. Standalone MySQL Servers(Functionally separated) (Active)

多台功能分離的單獨服務器，沒有高可靠性、負載均衡能力，明顯缺點太多，不予考慮。

mysql數據庫最大能支持多少並發量

MySQL服務器的最大並發連接數是16384。

受服務器配置，及網絡環境等制約，實際服務器支持的並發連接數會小一些。主要決定因素有：

1、服務器CPU及內存的配置。

2、網絡的帶寬。互聯網連接中上行帶寬的影響尤為明顯。

擴展資料：

優化數據庫結構：

組織數據庫的schema、表和字段以降低I/O的開銷，將相關項保存在一起，並提前規劃，以便隨着數據量的增長，性能可以保持較高的水平。

設計數據表應盡量使其佔用的空間最小化，表的主鍵應儘可能短。·對於InnoDB表，主鍵所在的列在每個輔助索引條目中都是可複製的，因此如果有很多輔助索引，那麼一個短的主鍵可以節省大量空間。

僅創建需要改進查詢性能的索引。索引有助於檢索，但是會增加插入和更新操作的執行時間。

InnoDB的ChangeBuffering特性：

InnoDB提供了changebuffering的配置，可減少維護輔助索引所需的磁盤I/O。大規模的數據庫可能會遇到大量的表操作和大量的I/O，以保證輔助索引保持最新。當相關頁面不在緩衝池裡面時，InnoDB的changebuffer將會更改緩存到輔助索引條目。

從而避免因不能立即從磁盤讀取頁面而導致耗時的I/O操作。當頁面被加載到緩衝池時，緩衝的更改將被合併，更新的頁面之後會刷新到磁盤。這樣做可提高性能，適用於MySQL5.5及更高版本。

參考資料來源：百度百科-MySQL數據庫

哪些因素會對mysql數據庫服務器性能造成影響

網絡寬帶也會有所影響。

網絡是數據庫基礎架構的主要部分。但是，通常性能基準測試是在本地計算機上完成的，客戶端和服務器並置在一起。這樣做是為了簡化結構並排除一個以上的變量（網絡部分），但是我們也忽略了網絡對性能的影響。對於像 MySQL Group Replication 這樣的產品集群來說，網絡更為重要。在這篇文章中，我將介紹網絡設置。這些都是簡單而微不足道的，但卻是讓我們更了解複雜網絡設置效果的基石。

安裝我將使用兩台裸機服務器，通過專用的 10Gb 網絡連接。我將通過使用 ethtool-s eth1 speed1000duplex full autoneg off 命令更改網絡接口速度來模擬 1Gb 網絡。

我將運行一個簡單的基準：sysbench oltp_read_only –mysql-ssl=on –mysql-host=172.16.0.1 –tables=20 –table-size=10000000 –mysql-user=sbtest –mysql-password=sbtest –threads=$i –time=300 –report-interval=1 –rand-type=pareto

運行時線程數從 1 到 2048 不等。所有數據都適合內存 -innodb_buffer_pool_size 足夠大。因此工作負載在內存中佔用大量 CPU：沒有 IO 開銷。操作系統：Ubuntu 16.04

N1 基準-網絡帶寬在第一個實驗中，我將比較 1Gb 網絡和 10Gb 網絡。顯然，1Gb 網絡性能是這裡的瓶頸，如果我們遷移到 10Gb 網絡，我們可以顯着改善我們的結果。要查看 1Gb 網絡是瓶頸，我們可以檢查 PMM（percona 的數據庫監控管理開源工具） 中的網絡流量圖表：

我們可以看到我們的吞吐量達到了 116 MiB/s（或 928 Mb/s)，這非常接近網絡帶寬。但是，如果我們的網絡基礎設施僅限於 1Gb，我們可以做些什麼？

N2 基準-協議壓縮MySQL 協議中有一個功能，您可以看到客戶端和服務器之間的網絡交換壓縮：–mysql-compression=on。讓我們看看它將如何影響我們的結果。

這是一個有趣的結果。當我們使用所有可用的網絡帶寬時，協議壓縮實際上有助於改善結果。

但是 10Gb 網絡不是這種情況。壓縮/解壓縮所需的 CPU 資源是一個限制因素，通過壓縮，吞吐量實際上只達到我們沒有壓縮的一半。現在讓我們談談協議加密，以及如何使用 SSL 影響我們的結果。

N3基準-網絡加密

對於 1Gb 網絡，SSL 加密顯示了一些損失 – 單線程約為 10％ – 但是否則我們再次達到帶寬限制。我們還看到了大量線程的可擴展性，這在 10Gb 網絡案例中更為明顯。使用 10Gb 時，SSL 協議在 32 個線程後不會擴展。實際上，它似乎是 MySQL 目前使用的 OpenSSL 1.0 中的可伸縮性問題。在我們的實驗中，我們看到 OpenSSL 1.1.1 提供了更好的可伸縮性，但是您需要從鏈接到OpenSSL 1.1.1 的源代碼中獲得特殊的 MySQL 構建才能實現這一點。我沒有在這裡展示它們，因為我們沒有生產二進制文件。

結論

1. 網絡性能和利用率將影響一般應用程序吞吐量。

2. 檢查您是否達到了網絡帶寬限制。

3. 如果受到網絡帶寬的限制，協議壓縮可以改善結果，但如果不是，則可能會使事情變得更糟。

4. SSL 加密在線程數量較少的情況下會有一些損失（約10％），但對於高並發工作負載，它不會擴展。

如何評估和測試Mysql及oracle數據庫性能

1：服務器環境

操作系統：Red Hat Enterprise Linux Server release 5.5 (Tikanga)

CPU：Intel(R) Xeon(R) CPU           E5607  @ 2.27GHz   8核

內存：16G

Mysql：Ver 14.14 Distrib 5.5.21, for Linux (x86_64)

Oracle：Oracle Database 11g Enterprise Edition Release

詳細數據測試（操作通過存儲過程完成）

數據插入

50並發Mysql插入性能圖示（橫坐標：當前數據總量，縱坐標：每秒執行次數）{平均值：4841.98}

50並發Oracle插入性能圖示（橫坐標：執行時間（秒），縱坐標：每秒執行次數）{平均值：1459.408}

mysql數據庫性能測試

我理解的是你希望了解mysql性能測試的方法：

其實常用的一般：

選取最適用的字段屬性

MySQL可以很好的支持大數據量的存取，但是一般說來，數據庫中的表越小，在它上面執行的查詢也就會越快。因此，在創建表的時候，為了獲得更好的性能，我們可以將表中字段的寬度設得儘可能小。例如，在定義郵政編碼這個字段時，如果將其設置為CHAR(255),顯然給數據庫增加了不必要的空間，甚至使用VARCHAR這種類型也是多餘的，因為CHAR(6)就可以很好的完成任務了。同樣的，如果可以的話，我們應該使用MEDIUMINT而不是BIGIN來定義整型字段。

另外一個提高效率的方法是在可能的情況下，應該盡量把字段設置為NOT NULL，這樣在將來執行查詢的時候，數據庫不用去比較NULL值。

對於某些文本字段，例如“省份”或者“性別”，我們可以將它們定義為ENUM類型。因為在MySQL中，ENUM類型被當作數值型數據來處理，而數值型數據被處理起來的速度要比文本類型快得多。這樣，我們又可以提高數據庫的性能。

2、使用連接（JOIN）來代替子查詢(Sub-Queries)

MySQL從4.1開始支持SQL的子查詢。這個技術可以使用SELECT語句來創建一個單列的查詢結果，然後把這個結果作為過濾條件用在另一個查詢中。例如，我們要將客戶基本信息表中沒有任何訂單的客戶刪除掉，就可以利用子查詢先從銷售信息表中將所有發出訂單的客戶ID取出來，然後將結果傳遞給主查詢，如下所示：

DELETE FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )

使用子查詢可以一次性的完成很多邏輯上需要多個步驟才能完成的SQL操作，同時也可以避免事務或者表鎖死，並且寫起來也很容易。但是，有些情況下，子查詢可以被更有效率的連接（JOIN）.. 替代。例如，假設我們要將所有沒有訂單記錄的用戶取出來，可以用下面這個查詢完成：

SELECT * FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )

如果使用連接（JOIN）.. 來完成這個查詢工作，速度將會快很多。尤其是當salesinfo表中對CustomerID建有索引的話，性能將會更好，查詢如下：

SELECT * FROM customerinfo LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo. CustomerID WHERE salesinfo.CustomerID IS NULL

連接（JOIN）.. 之所以更有效率一些，是因為 MySQL不需要在內存中創建臨時表來完成這個邏輯上的需要兩個步驟的查詢工作。

3、使用聯合(UNION)來代替手動創建的臨時表

MySQL 從 4.0 的版本開始支持 UNION 查詢，它可以把需要使用臨時表的兩條或更多的 SELECT 查詢合併的一個查詢中。在客戶端的查詢會話結束的時候，臨時表會被自動刪除，從而保證數據庫整齊、高效。使用 UNION 來創建查詢的時候，我們只需要用 UNION作為關鍵字把多個 SELECT 語句連接起來就可以了，要注意的是所有 SELECT 語句中的字段數目要想同。下面的例子就演示了一個使用 UNION的查詢。

SELECT Name, Phone FROM client UNION SELECT Name, BirthDate FROM author

UNION

SELECT Name, Supplier FROM product

4、事務

儘管我們可以使用子查詢（Sub-Queries）、連接（JOIN）和聯合（UNION）來創建各種各樣的查詢，但不是所有的數據庫操作都可以只用一條或少數幾條SQL語句就可以完成的。更多的時候是需要用到一系列的語句來完成某種工作。但是在這種情況下，當這個語句塊中的某一條語句運行出錯的時候，整個語句塊的操作就會變得不確定起來。設想一下，要把某個數據同時插入兩個相關聯的表中，可能會出現這樣的情況：第一個表中成功更新後，數據庫突然出現意外狀況，造成第二個表中的操作沒有完成，這樣，就會造成數據的不完整，甚至會破壞數據庫中的數據。要避免這種情況，就應該使用事務，它的作用是：要麼語句塊中每條語句都操作成功，要麼都失敗。換句話說，就是可以保持數據庫中數據的一致性和完整性。事物以BEGIN 關鍵字開始，COMMIT關鍵字結束。在這之間的一條SQL操作失敗，那麼，ROLLBACK命令就可以把數據庫恢復到BEGIN開始之前的狀態。

BEGIN;

INSERT INTO salesinfo SET CustomerID=14;

UPDATE inventory SET Quantity=11

WHERE item=’book’;

COMMIT;

事務的另一個重要作用是當多個用戶同時使用相同的數據源時，它可以利用鎖定數據庫的方法來為用戶提供一種安全的訪問方式，這樣可以保證用戶的操作不被其它的用戶所干擾。

5、鎖定表

儘管事務是維護數據庫完整性的一個非常好的方法，但卻因為它的獨佔性，有時會影響數據庫的性能，尤其是在很大的應用系統中。由於在事務執行的過程中，數據庫將會被鎖定，因此其它的用戶請求只能暫時等待直到該事務結束。如果一個數據庫系統只有少數幾個用戶

來使用，事務造成的影響不會成為一個太大的問題；但假設有成千上萬的用戶同時訪問一個數據庫系統，例如訪問一個電子商務網站，就會產生比較嚴重的響應延遲。

其實，有些情況下我們可以通過鎖定表的方法來獲得更好的性能。下面的例子就用鎖定表的方法來完成前面一個例子中事務的功能。

LOCK TABLE inventory WRITE

SELECT Quantity FROM inventory

WHEREItem=’book’;

…

UPDATE inventory SET Quantity=11

WHEREItem=’book’;

UNLOCK TABLES

這裡，我們用一個 SELECT 語句取出初始數據，通過一些計算，用 UPDATE 語句將新值更新到表中。包含有 WRITE 關鍵字的 LOCK TABLE 語句可以保證在 UNLOCK TABLES 命令被執行之前，不會有其它的訪問來對 inventory 進行插入、更新或者刪除的操作。

6、使用外鍵

鎖定表的方法可以維護數據的完整性，但是它卻不能保證數據的關聯性。這個時候我們就可以使用外鍵。例如，外鍵可以保證每一條銷售記錄都指向某一個存在的客戶。在這裡，外鍵可以把customerinfo 表中的CustomerID映射到salesinfo表中CustomerID，任何一條沒有合法CustomerID的記錄都不會被更新或插入到salesinfo中。

CREATE TABLE customerinfo

(

CustomerID INT NOT NULL ,

PRIMARY KEY ( CustomerID )

) TYPE = INNODB;

CREATE TABLE salesinfo

(

SalesID INT NOT NULL,

CustomerID INT NOT NULL,

PRIMARY KEY(CustomerID, SalesID),

FOREIGN KEY (CustomerID) REFERENCES customerinfo

(CustomerID) ON DELETECASCADE

) TYPE = INNODB;

注意例子中的參數“ON DELETE CASCADE”。該參數保證當 customerinfo 表中的一條客戶記錄被刪除的時候，salesinfo 表中所有與該客戶相關的記錄也會被自動刪除。如果要在 MySQL 中使用外鍵，一定要記住在創建表的時候將表的類型定義為事務安全表 InnoDB類型。該類型不是 MySQL 表的默認類型。定義的方法是在 CREATE TABLE 語句中加上 TYPE=INNODB。如例中所示。

7、使用索引

索引是提高數據庫性能的常用方法，它可以令數據庫服務器以比沒有索引快得多的速度檢索特定的行，尤其是在查詢語句當中包含有MAX(), MIN()和ORDERBY這些命令的時候，性能提高更為明顯。那該對哪些字段建立索引呢？一般說來，索引應建立在那些將用於JOIN, WHERE判斷和ORDER BY排序的字段上。盡量不要對數據庫中某個含有大量重複的值的字段建立索引。對於一個ENUM類型的字段來說，出現大量重複值是很有可能的情況，例如customerinfo中的“province”.. 字段，在這樣的字段上建立索引將不會有什麼幫助；相反，還有可能降低數據庫的性能。我們在創建表的時候可以同時創建合適的索引，也可以使用ALTER TABLE或CREATE INDEX在以後創建索引。此外，MySQL

從版本3.23.23開始支持全文索引和搜索。全文索引在MySQL 中是一個FULLTEXT類型索引，但僅能用於MyISAM 類型的表。對於一個大的數據庫，將數據裝載到一個沒有FULLTEXT索引的表中，然後再使用ALTER TABLE或CREATE INDEX創建索引，將是非常快的。但如果將數據裝載到一個已經有FULLTEXT索引的表中，執行過程將會非常慢。

8、優化的查詢語句

絕大多數情況下，使用索引可以提高查詢的速度，但如果SQL語句使用不恰當的話，索引將無法發揮它應有的作用。下面是應該注意的幾個方面。首先，最好是在相同類型的字段間進行比較的操作。在MySQL 3.23版之前，這甚至是一個必須的條件。例如不能將一個建有索引的INT字段和BIGINT字段進行比較；但是作為特殊的情況，在CHAR類型的字段和VARCHAR類型字段的字段大小相同的時候，可以將它們進行比較。其次，在建有索引的字段上盡量不要使用函數進行操作。

例如，在一個DATE類型的字段上使用YEAE()函數時，將會使索引不能發揮應有的作用。所以，下面的兩個查詢雖然返回的結果一樣，但後者要比前者快得多。

SELECT * FROM order WHERE YEAR(OrderDate)2001;

SELECT * FROM order WHERE OrderDate”2001-01-01″;

同樣的情形也會發生在對數值型字段進行計算的時候：

SELECT * FROM inventory WHERE Amount/724;

SELECT * FROM inventory WHERE Amount24*7;

上面的兩個查詢也是返回相同的結果，但後面的查詢將比前面的一個快很多。第三，在搜索字符型字段時，我們有時會使用 LIKE 關鍵字和通配符，這種做法雖然簡單，但卻也是以犧牲系統性能為代價的。例如下面的查詢將會比較表中的每一條記錄。

SELECT * FROM books

WHERE name like “MySQL%”

但是如果換用下面的查詢，返回的結果一樣，但速度就要快上很多：

SELECT * FROM books

WHERE name=”MySQL”and name”MySQM”

最後，應該注意避免在查詢中讓MySQL進行自動類型轉換，因為轉換過程也會使索引變得不起作用。