優化mysql數據庫的經歷總結。本站提示廣大學習愛好者：（優化mysql數據庫的經歷總結）文章只能為提供參考，不一定能成為您想要的結果。以下是優化mysql數據庫的經歷總結正文
1、拔取最實用的字段屬性
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/7<24;
SELECT * FROM inventory WHERE Amount<24*7;
下面的兩個查詢也是前往雷同的成果，但前面的查詢將比後面的一個快許多。第三，在搜刮字符型字段時，我們有時會應用 LIKE 症結字和通配符，這類做法固然簡略，但卻也是以就義體系機能為價值的。例以下面的查詢將會比擬表中的每筆記錄。
SELECT * FROM books
WHERE name like "MySQL%"
然則假如換用上面的查詢，前往的成果一樣，但速度就要快上許多：
SELECT * FROM books
WHERE name>="MySQL"and name<"MySQM"
最初，應當留意防止在查詢中讓MySQL停止主動類型轉換，由於轉換進程也會使索引變得不起感化。
