MySQL 十大最佳化方法

1. 選取最適用的欄位屬性

表中欄位的寬度設得盡可能小：char的上限為255 位元組（固定佔用空間），varchar 的上限65535 位元組（實際佔用空間），text 的上限為65535。 char 比 varchar 處理效率高。

盡量把欄位設為 NOT NULL，執行查詢的時候，資料庫不用去比較 NULL 值。

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

連接（JOIN）之所以更有效率一些，是因為MySQL 不需要在記憶體中建立臨時表來完成這個邏輯上的需要兩個步驟的查詢工作（聯合查詢的條件加索引更快）。

3. 使用聯合(UNION) 來取代手動建立的暫存資料表

把需要使用暫存資料表的兩個或更多的SELECT 查詢合併的一個查詢中。

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 開始之前的狀態。

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、使用外鍵

鎖定表的方法可以維護資料的完整性，但是它卻無法保證資料的關聯性。這時候我們就可以使用外鍵。例如，外鍵可以保證每一筆銷售記錄都指向某一個存在的客戶。在這裡，外鍵可以把 c​​ustomerinfo 表中的 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”.. 字段，在這樣的字段上建立索引將不會有什麼幫助；相反，還有可能降低資料庫的效能。

普通索引（由關鍵字 KEY 或 INDEX 定義的索引）的唯一任務是加快資料的存取速度。因此，應該只為那些最常出現在查詢條件（WHEREcolumn=）或排序條件（ORDERBYcolumn）中的資料列建立索引。

唯一索引的好處：一是簡化了MySQL 對這個索引的管理工作，這個索引也因此而變得更有效率；二是MySQL 會在有新記錄插入資料表時，自動檢查新記錄的這個欄位的值是否已經在某個記錄的這個欄位裡出現過了；如果是，MySQL 將拒絕插入那筆新記錄。也就是說，唯一索引可以保證資料記錄的唯一性。在許多場合，建立唯一索引的目的往往不是為了提高存取速度，而只是為了避免資料出現重複。

8. 最佳化的查詢語句

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;

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

9. 索引失效狀況

like 以 % 开头，索引无效；当 like 前缀没有 %，后缀有 % 时，索引有效。

or 语句前后没有同时使用索引。当 or 左右查询字段只有一个是索引，该索引失效，只有当 or 左右查询字段均为索引时，才会生效。

组合索引，不是使用第一列索引，索引失效。

数据类型出现隐式转化。如 varchar 不加单引号的话可能会自动转换为 int 型，使索引无效，产生全表扫描。

在索引字段上使用 not，a8093152e673feb7aba1828c43532094，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 优化方法： keya8093152e673feb7aba1828c435320940 改为 key>0 or key562387249b991e085ea202fe92f7872ehttps://blog.csdn.net/yhl_jxy/article/details/88570154

一、分表的分类（单表记录条数达到百万到千万级别时就要使用分表）

1. 纵向分表

文章标题，作者，分类，创建时间等，是变化频率慢，查询次数多，而且最好有很好的实时性的数据，我们把它叫做冷数据。

浏览量，回复数等，类似的统计信息，或者别的变化频率比较高的数据，我们把它叫做活跃数据。

首先存储引擎的使用不同，冷数据使用 MyIsam 可以有更好的查询数据。活跃数据，可以使用 Innodb , 可以有更好的更新速度。

就是把原来一张表里的字段，冷数据的字段和活跃数据的字段分别建立 2 张表来管理。

2. 横向分表

把大的表结构，横向切割为同样结构的不同表，如，用户信息表，user_1,user_2 等，表结构是完全一样。

二、慢查询

show variables like 'slow%';
show global status like 'slow%';

使用 mysqlreport；

正确使用索引：explain 分析查询语句，组合索引，索引副作用（占空间、update）

开启慢查询日志、使用慢查询分析工具 mysqlsla；

索引缓存、索引代价（插入更新索引）；

表锁，行锁，行锁副作用（update 多时候变慢），在 select 和 update 混合的情况下，行锁巧妙解决了读写互斥的问题；

开启使用查询缓存；

修改临时表内存空间；

开启线程池；

MySQL Query 语句优化的基本思路和原则

1、优化需要优化的 Query；

2、定位优化对象的性能瓶颈；

3、明确优化目标；

4、从 Explaing 入手；

5、多使用 Profile；

6、永远用小结果集推动大的结果集；

7、尽可能在索引中完成排序；

8、只取自己需要的 Columns；

9、仅仅使用最有效的过滤条件；

10、尽可能避免复杂的 Join 和子查询。

推荐教程：《MySQL教程》

以上是MySQL 十大最佳化方法的詳細內容。更多資訊請關注PHP中文網其他相關文章！