本文目錄一覽：
怎麼優化MySQL資料庫
1、選取最適用的欄位屬性,儘可能減少定義欄位長度,盡量把欄位設置NOT NULL,例如’省份,性別’,最好設置為ENUM
2、使用連接（JOIN）來代替子查詢:
a.刪除沒有任何訂單客戶:DELETE FROM customerinfo WHERE customerid NOT in(SELECT customerid FROM orderinfo)
b.提取所有沒有訂單客戶:SELECT FROM customerinfo WHERE customerid NOT in(SELECT customerid FROM orderinfo)
c.提高b的速度優化:SELECT FROM customerinfo LEFT JOIN orderid customerinfo.customerid=orderinfo.customerid
WHERE orderinfo.customerid IS NULL
3、使用聯合(UNION)來代替手動創建的臨時表
a.創建臨時表:SELECT name FROM `nametest` UNION SELECT username FROM `nametest2`
4、事務處理:
a.保證數據完整性,例如添加和修改同時,兩者成立則都執行,一者失敗都失敗
mysql_query(“BEGIN”);
mysql_query(“INSERT INTO customerinfo (name) VALUES (‘$name1’)”;
mysql_query(“SELECT * FROM `orderinfo` where customerid=”.$id”);
mysql_query(“COMMIT”);
5、鎖定表,優化事務處理:
a.我們用一個 SELECT 語句取出初始數據，通過一些計算，用 UPDATE 語句將新值更新到表中。
包含有 WRITE 關鍵字的 LOCK TABLE 語句可以保證在 UNLOCK TABLES 命令被執行之前，
不會有其它的訪問來對 inventory 進行插入、更新或者刪除的操作
mysql_query(“LOCK TABLE customerinfo READ, orderinfo WRITE”);
mysql_query(“SELECT customerid FROM `customerinfo` where id=”.$id);
mysql_query(“UPDATE `orderinfo` SET ordertitle=’$title’ where customerid=”.$id);
mysql_query(“UNLOCK TABLES”);
6、使用外鍵,優化鎖定表
a.把customerinfo里的customerid映射到orderinfo里的customerid,
任何一條沒有合法的customerid的記錄不會寫到orderinfo里
CREATE TABLE customerinfo
(
customerid INT NOT NULL,
PRIMARY KEY(customerid)
)TYPE = INNODB;
CREATE TABLE orderinfo
(
orderid INT NOT NULL,
customerid INT NOT NULL,
PRIMARY KEY(customerid,orderid),
FOREIGN KEY (customerid) REFERENCES customerinfo
(customerid) ON DELETE CASCADE
)TYPE = INNODB;
注意:’ON DELETE CASCADE’,該參數保證當customerinfo表中的一條記錄刪除的話同時也會刪除order
表中的該用戶的所有記錄,注意使用外鍵要定義事務安全類型為INNODB;
7、建立索引:
a.格式:
(普通索引)-
創建:CREATE INDEX 索引名 ON tablename (索引欄位)
修改:ALTER TABLE tablename ADD INDEX [索引名] (索引欄位)
創表指定索引:CREATE TABLE tablename([…],INDEX[索引名](索引欄位))
(唯一索引)-
創建:CREATE UNIQUE 索引名 ON tablename (索引欄位)
修改:ALTER TABLE tablename ADD UNIQUE [索引名] (索引欄位)
創表指定索引:CREATE TABLE tablename([…],UNIQUE[索引名](索引欄位))
(主鍵)-
它是唯一索引,一般在創建表是建立,格式為:
CREATA TABLE tablename ([…],PRIMARY KEY[索引欄位])
8、優化查詢語句
a.最好在相同欄位進行比較操作,在建立好的索引欄位上盡量減少函數操作
例子1:
SELECT * FROM order WHERE YEAR(orderDate)2008;(慢)
SELECT * FROM order WHERE orderDate”2008-01-01″;(快)
例子2:
SELECT * FROM order WHERE addtime/724;(慢)
SELECT * FROM order WHERE addtime24*7;(快)
例子3:
SELECT * FROM order WHERE title like “%good%”;
SELECT * FROM order WHERE title=”good” and name”good”;
mysql資料庫如何優化，優化了哪些功能
mysql的優化大的有兩方面：
1、配置優化
配置的優化其實包含兩個方面的：操作系統內核的優化和mysql配置文件的優化
1)系統內核的優化對專用的mysql伺服器來說，無非是內存實用、連接數、超時處理、TCP處理等方面的優化，根據自己的硬體配置來進行優化，這裡不多講；
2)mysql配置的優化，一般來說包含：IO處理的常用參數、最大連接數設置、緩存使用參數的設置、慢日誌的參數的設置、innodb相關參數的設置等，如果有主從關係在設置主從同步的相關參數即可，網上的相關配置文件很多，大同小異，常用的設置大多修改這些差不多就夠用了。
2、sql語句的優化
1、 盡量稍作計算
Mysql的作用是用來存取數據的，不是做計算的，做計算的話可以用其他方法去實現，mysql做計算是很耗資源的。
2.盡量少 join
MySQL 的優勢在於簡單，但這在某些方面其實也是其劣勢。MySQL 優化器效率高，但是由於其統計信息的量有限，優化器工作過程出現偏差的可能性也就更多。對於複雜的多表 Join，一方面由於其優化器受限，再者在 Join 這方面所下的功夫還不夠，所以性能表現離 Oracle 等關係型資料庫前輩還是有一定距離。但如果是簡單的單表查詢，這一差距就會極小甚至在有些場景下要優於這些資料庫前輩。
3.盡量少排序
排序操作會消耗較多的 CPU 資源，所以減少排序可以在緩存命中率高等 IO 能力足夠的場景下會較大影響 SQL的響應時間。
對於MySQL來說，減少排序有多種辦法，比如：
通過利用索引來排序的方式進行優化
減少參與排序的記錄條數
非必要不對數據進行排序
mysql 參數調優（10）之 tmp_table_size 優化臨時表
tmp_table_size默認16M。tmp_table_size如果過小，存不下了就會存到磁碟上。對於group by會有性能影響。
下面的sql EXPLAIN 如下，出現了Using temporary。表示查詢會利用臨時表。
在默認tmp_table_size大小16M下執行：
查看臨時表統計信息，Created_tmp_disk_tables 為0，Created_tmp_tables 為1表示上訴sql執行後生產了一張內存里的臨時表。
將tmp_table_size 調從16M調整為16K
再次執行,查詢時間從4變成了18秒
重新統計
再次查看status，這次有在磁碟上創建1個臨時表。
設置為32M
Percona Server中的臨時表信息會記錄到慢查詢日誌
由於MySQL慢查詢日誌里沒有使用臨時表的信息，這就給我們診斷性能問題帶來了一些不便，第三方的版本如Percona Server，在慢查詢里可以有更詳細的信息，將會記錄臨時表使用的情況，從而有助於我們診斷和調優。
mysql8中對臨時表有較大的優化
臨時表引擎使用innodb（default 磁碟）和temptable(default 內存)
怎麼進行mysql資料庫優化？
有八個方面可以對mysql進行優化：
1、選取最適用的欄位屬性
MySQL可以很好的支持大數據量的存取，但是一般說來，資料庫中的表越小，在它上面執行的查詢也就會越快。因此，在創建表的時候，為了獲得更好的性能，我們可以將表中欄位的寬度設得儘可能小。
2. 使用連接（JOIN）來代替子查詢(Sub-Queries)
MySQL從4.1開始支持SQL的子查詢。這個技術可以使用SELECT語句來創建一個單列的查詢結果，然後把這個結果作為過濾條件用在另一個查詢中。
3、使用聯合(UNION)來代替手動創建的臨時表
MySQL從4.0的版本開始支持union查詢，它可以把需要使用臨時表的兩條或更多的select查詢合併的一個查詢中。在客戶端的查詢會話結束的時候，臨時表會被自動刪除，從而保證資料庫整齊、高效。
4、事務
儘管我們可以使用子查詢（Sub-Queries）、連接（JOIN）和聯合（UNION）來創建各種各樣的查詢，但不是所有的資料庫操作都可以只用一條或少數幾條SQL語句就可以完成的。更多的時候是需要用到一系列的語句來完成某種工作。但是在這種情況下，當這個語句塊中的某一條語句運行出錯的時候，整個語句塊的操作就會變得不確定起來。設想一下，要把某個數據同時插入兩個相關聯的表中，可能會出現這樣的情況：第一個表中成功更新後，資料庫突然出現意外狀況，造成第二個表中的操作沒有完成，這樣，就會造成數據的不完整，甚至會破壞資料庫中的數據。要避免這種情況，就應該使用事務，它的作用是：要麼語句塊中每條語句都操作成功，要麼都失敗
5、鎖定表
儘管事務是維護資料庫完整性的一個非常好的方法，但卻因為它的獨佔性，有時會影響資料庫的性能，尤其是在很大的應用系統中。由於在事務執行的過程中，資料庫將會被鎖定，因此其它的用戶請求只能暫時等待直到該事務結束。其實，有些情況下我們可以通過鎖定表的方法來獲得更好的性能。
6、使用外鍵
鎖定表的方法可以維護數據的完整性，但是它卻不能保證數據的關聯性。這個時候我們就可以使用外鍵。
7、使用索引
索引是提高資料庫性能的常用方法，它可以令資料庫伺服器以比沒有索引快得多的速度檢索特定的行，尤其是在查詢語句當中包含有MAX(),MIN()和ORDERBY這些命令的時候，性能提高更為明顯。
8、優化的查詢語句
絕大多數情況下，使用索引可以提高查詢的速度，但如果SQL語句使用不恰當的話，索引將無法發揮它應有的作用。
原創文章，作者：小藍，如若轉載，請註明出處：https://www.506064.com/zh-tw/n/286334.html
微信掃一掃 
支付寶掃一掃 