遊戲陪玩app開發中，Mysql的sql優化方法

雲豹科技程式設計師發表於2021-11-18
遊戲APPMySql優化

1、選擇最合適的欄位屬性

在遊戲陪玩app開發中用到的Mysql是一種關係型資料庫，可以很好地支援大資料量的儲存，但是一般來說，資料庫中的表越小，在它上面執行的查詢也就越快。因此，在建立表的時候，為了獲得更好的效能，我們可以將表中欄位的寬度捨得儘可能小。

例如：在定義郵政編碼這個欄位時，如果將其設定為char(255)，顯然給資料庫增加了不必要的空間，甚至使用varchar這種型別也是多餘的，因為char(6)就可以很好地完成了任務。同樣的如果可以的話，我們應該是用MEDIUMINT而不是BIGINT來定義整形欄位。

2、儘量把欄位設定為NOT NULL

在可能的情況下，儘量把欄位設定為NOT NULL,這樣在將來執行遊戲陪玩app開發中查詢的時候，資料庫不用去比較NULL值。對於某些文字欄位來說，例如“省份”或者“性別”，我們可以將他們定義為ENUM(列舉)型別。因為在遊戲陪玩app開發的MySQL中，ENUM型別被當做數值型資料來處理，而數值型資料被處理起來的速度要比文字型別要快得多。這樣我們又可以提高資料庫的效能。

3、使用連線(JOIN)來代替子查詢(Sub-Queries)

MySQL從4.1開始支援SQL的子查詢。這個技術可以使用select語句來建立一個單例的查詢結果，然後把這個結果作為過濾條件用在另一個查詢中。例如：我們要將遊戲陪玩app開發的客戶基本資訊表中沒有任何訂單的客戶刪除掉，就可以利用子查詢先從銷售資訊表中將所有發出訂單的客戶id取出來，然後將結果傳遞給主查詢，如下圖所示：
在這裡插入圖片描述
如果使用連線(JOIN)來完成這個工作，速度將會快很多，尤其是當salesinfo表中對CustomerID建有索引的話，效能將會更好，查詢如下：
在這裡插入圖片描述
連線(JOIN)之所以更有效率一些，是因為遊戲陪玩app開發中的MySQL不需要在記憶體中建立臨時表來完成這個邏輯上 需要兩個步驟的查詢工作。

另外：如果遊戲陪玩app開發中有很多JOIN查詢，你應該確認兩個表中JOIN的欄位是被建立過索引的。這樣MySQL內部 會啟動為你優化JOIN的SQL語句的機制。而且這些被用來JOIN的欄位，應該是相同的型別的。
例如：如果你要把DECIMAL欄位和一個INT欄位JOIN在一起，MySQL就無法使用他們的索引。對於那些STRING型別，還需要有相同的字符集才行。(兩個表的字符集可能不相同)。

inner join內連線也叫做等值連線，left/right join是外連結。

SELECT A.id,A.name,B.id,B.name FROM A LEFT JOIN B ON A.id=B.id;
SELECT A.id,A.name,B.id,B.name FROM A RIGHT JOIN ON B A.id= B.id;
SELECT A.id,A.name,B.id,B.name FROM A INNER JOIN ON A.id =B.id;

經過多方面的證實inner join效能比較快，因為inner join是等值連線，或許返回的行數比較少。但是我們要記得有些語句隱形的用到了等值連線，如：

SELECT A.id,A.name,B.id,B.name FROM A,B WHERE A.id = B.id;

推薦：在遊戲陪玩app開發中能用inner join連線誒就用inner join連線。

sql中的連線查詢有inner join(內連線）、left join(左連線)、right join（右連線）、full join（全連線）四種方式，它們之間其實並沒有太大區別，僅僅是查詢出來的結果有所不同。

例如我們有兩張表：
在這裡插入圖片描述

Orders表通過外來鍵Id_P和Persons表進行關聯。

inner join（內連線），在兩張表進行連線查詢時，只保留兩張表中完全匹配的結果集。

我們使用inner join對兩張表進行連線查詢，sql如下：

SELECT p.LastName, p.FirstName, o.OrderNo
FROM Persons p
INNER JOIN Orders o
ON p.Id_P=o.Id_P and 1=1　　--用and連線多個條件
ORDER BY p.LastName

查詢結果集：
在這裡插入圖片描述

此種連線方式Orders表中Id_P欄位在Persons表中找不到匹配的，則不會列出來。

注意：單純的select * from a,b是笛卡爾乘積。比如a表有5條資料，b表有3條資料，那麼最後的結果有5*3=15條資料。

但是如果對兩個表進行關聯:select * from a,b where a.id = b.id 意思就變了，此時就等價於：

select * from a inner join b on a.id = b.id。即就是內連線。

但是這種寫法並不符合遊戲陪玩app開發規範，可能只對某些資料庫管用，如sqlserver。推薦最好不要這樣寫。最好寫成inner join的寫法。

內連線查詢 (select * from a join b on a.id = b.id) 與 關聯查詢 (select * from a , b where a.id = b.id)的區別

left join,在兩張表進行連線查詢時，會返回左表所有的行，即使在右表中沒有匹配的記錄。
我們使用left join對兩張表進行連線查詢，sql如下：

SELECT p.LastName, p.FirstName, o.OrderNo
FROM Persons p
LEFT JOIN Orders o
ON p.Id_P=o.Id_P
ORDER BY p.LastName

查詢結果如下：

在這裡插入圖片描述

可以看到，左表（Persons表）中LastName為Bush的行的Id_P欄位在右表（Orders表）中沒有匹配，但查詢結果仍然保留該行。

right join,在兩張表進行連線查詢時，會返回右表所有的行，即使在左表中沒有匹配的記錄。
我們使用right join對兩張表進行連線查詢，sql如下：

SELECT p.LastName, p.FirstName, o.OrderNo
FROM Persons p
RIGHT JOIN Orders o
ON p.Id_P=o.Id_P
ORDER BY p.LastName

查詢結果如下：
在這裡插入圖片描述

Orders表中最後一條記錄Id_P欄位值為65，在左表中沒有記錄與之匹配，但依然保留。

full join,在兩張表進行連線查詢時，返回左表和右表中所有沒有匹配的行。
我們使用full join對兩張表進行連線查詢，sql如下：

SELECT p.LastName, p.FirstName, o.OrderNo
FROM Persons p
FULL JOIN Orders o
ON p.Id_P=o.Id_P
ORDER BY p.LastName

查詢結果如下：
在這裡插入圖片描述

查詢結果是left join和right join的並集。

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

MySQL從4.0版本開始支援union查詢，他可以把需要使用臨時表的兩條或更多的select查詢合在一個查詢中。在遊戲陪玩app開發的客戶端查詢會話結束的時候，臨時表會被自動刪除，從而保證資料庫整齊、高效。使用union來建立查詢的時候，我們只需要用union作為關鍵字把多個select語句連線起來就可以了，要注意的是所有select語句中的欄位數目要相同。下面一個例子就演示了一個使用union額查詢。
在這裡插入圖片描述
當我們可以確認不可能出現重複結果集或者不在乎重複結果集的時候儘量使用union all而不是union，因為union和union all的差異主要是前者需要將兩個或者多個結果集合並後再進行唯一性過濾操作，這就會涉及到排序，增加遊戲陪玩app開發中大量的CPU運算，增大資源消耗及延遲。

5、事務

儘管我們可以使用子查詢(Sub-Queries)、連線(JOIN)和聯合(UNION)來建立各種各樣的查詢，但不是所有遊戲陪玩app開發的資料庫操作，都可以只用一條或少數幾條就可以完成的。更多的時候是需要用一系列的語句來完成某種工作。但是在這種情況下，當這個語句塊中的某一條語句執行出錯的時候，整個語句塊的操作就會變得不確定起來。

設想一下，要把遊戲陪玩app開發的某個資料同時插入兩個相關聯的表中，可能會出現這樣的情況：第一個表中成功更新後，資料庫突然出現意外狀況，造成第二個表中的操作沒有完成，這樣就會造成資料的不完整，甚至會破壞資料庫中的資料。
要避免這種情況，就應該在遊戲陪玩app開發時使用事務，它的作用是要麼語句塊中每條語句都操作成功，要麼都失敗。換句話說，就是可以保持資料庫中的資料的一致性和完整性。事務以BEGIN關鍵字開始，COMMIT關鍵字結束。在這之間的一條SQL語句操作失敗，那麼Rollback命令就可以把資料庫恢復到begin開始之前的狀態。

BEGIN; INSERTINTOsalesinfoSETCustomerID=14;UPDATEinventorySETQuantity=11WHEREitem='book';COMMIT;

事務的另一個作用是當遊戲陪玩app開發中多個使用者同時使用相同的資料來源時，他可以使用鎖定資料庫的方式來為使用者提供一種安全的訪問機制，這樣可以保證使用者的操作不被其它的使用者所干擾。

一般來說，事務必須滿足四個條件(ACID)：原子性(Atomicity,或稱不可分割性)、一致性(Consistency)、隔離性(Isolation,又稱獨立性)、永續性(Durability).

  • 原子性：一個事物(transaction)中的所有操作，要麼全部完成，要麼全部不完成，不會結束在中間某個環節。遊戲陪玩app開發的事務在執行過程中發生錯誤，會被回滾(Rollback)到事務開始的狀態，就像這個事務從來沒有執行過一樣。
  • 一致性：在事務開始之前和事務結束之後，遊戲陪玩app開發的資料庫的完整性沒有被破壞。這表示寫入的資料必須完全符合所有的預設規則，這包含資料的精確度、串聯性以及後續資料庫可以自發性地完成預定的工作。
  • 隔離性：遊戲陪玩app開發的資料庫允許多個事務同時對其資料進行讀寫和修改的能力，隔離性可以防止多個事務併發執行時由於交叉執行而導致資料的不一致。事務隔離分為不同的級別，包括讀未提交(Readuncommitted)、讀已提交(Read committed)、可重複讀(repeateable read)和序列化(Serializable).
  • 永續性：事務處理結束後，對資料的修改就是永久的，即便系統故障也不會丟失。

事務的併發問題：

1、髒讀：事務A讀取了事務B更新的資料，然後B回滾操作，那麼A讀取到的資料就是髒資料

2、不可重複讀：遊戲陪玩app開發中的事務A多次讀取同一事物，事務B在事務A多次讀取的過程中，對資料做了更新並提交，導致事務A多次讀取同一資料時，結果不一致。

3、幻讀：遊戲陪玩app開發的系統管理員A將資料庫中的所有學生的成績從具體分數改為ABCDE等級，但是系統管理員B就在這個時候插入了一條具體分數的記錄，當系統管理員A改結束後發現還有一條記錄沒有改過來，就好像發生了幻覺一樣，這就叫幻讀。

小結：不可重複讀的和幻讀很容易混淆，不可重複讀側重於修改，幻讀側重於新增或刪除。解決不可重複讀的問題只需鎖住滿足條件的行，解決幻讀需要鎖表

MySQL事務隔離級別

在這裡插入圖片描述

事務控制語句：

  • BEGIN或START TRANSACTION：顯式的開啟一個事物。
  • COMMIT：也可以使用COMMIT WORK，不過二者是等價的。COMMIT會提交事務，並使已對資料庫進行的所有修改成為永久性的。
  • Rollback：也可以使用Rollback work，不過二者是等價的。回滾會結束遊戲陪玩app開發中使用者的事務，並撤銷正在進行的所有未提交的修改。
  • SAVEPOINT identifier：SAVEPOINT允許在事務中建立一個儲存點，一個事務中可以有很多個SAVEPOINT；
  • RELEASE SAVEPOINT identifier：刪除一個事物的儲存點,當沒有指定的儲存點時，執行該語句會丟擲一個異常。
  • ROLLBACK TO inditifier：把事務回滾到標記點。
  • SET TRANSACTION:用來設定事務的隔離級別。InnoDB儲存引擎提供事務的隔離級別有READ UNCOMMITTED、READCOMMITTED、REPEATABLE READ和SERLALIZABLE。

7、鎖定表

儘管在遊戲陪玩app開發中事務是維護資料庫完整性的一個非常好的方法，但卻因為他的獨佔性，有時會影響資料庫的效能，尤其是很大的應用系統中。由於在事務執行的過程中，資料庫將會被鎖定，因此其他的使用者請求只能暫時等待直到該事務結束。
如果遊戲陪玩app開發中的一個資料庫系統只有少數幾個使用者來使用，事務造成的影響不會成為太大的問題；但假設有成千上萬的使用者同時訪問一個資料庫系統，例如訪問一個電子商務網站，就會產生比較嚴重的響應延遲。
其實，有些情況下我們可以通過鎖定表的方式來獲得更好的遊戲陪玩app開發效能。下面的例子就是鎖定表的方法來完成前面一個例子中事務的功能。
在這裡插入圖片描述
這裡，我們用一個select語句取出初始資料，通過一些計算，用update語句將新值更新到表中。包含有WRITE關鍵字的LOCKTABLE語句可以保證在UNLOCKTABLES命令被執行之前，不會有其他訪問來對inventory進行插入、更新或者刪除的操作。

8、使用外來鍵

鎖定表的方法可以維護遊戲陪玩app開發中資料的完整性，但是他卻不能保證資料的關聯性。這個時候我們可以使用外來鍵。例如：外來鍵可以保證每一條銷售記錄都指向某一個存在的客戶。在這裡，外來鍵可以把customerinfo表中的customerid對映到salesinfo表中customerid，任何一條沒有辦法合法customerid的記錄都不會被跟新或插入到salesinfo中.

CREATE TABLE customerinfo(customerid int primary key) engine = innodb;
CREATE  TABLE   salesinfo( salesid int not null,customerid  int not null, primary key(customerid,salesid),foreign key(customerid)  references  customerinfo(customerid) on delete cascade)engine = innodb;

注意例子中的引數“on delete cascade”.該引數保證當customerinfo表中的一條客戶記錄也會被自動刪除。如果要在mysql中使用外來鍵，一定要記住在建立表的時候將表的型別定義為事務安全表InnoDB型別。該型別不是mysql表的預設型別。定義的方法是在CREATE TABLE語句中加上engine=innoDB。

9、使用索引

索引是提高遊戲陪玩app開發中資料庫效能的常用方法，他可以令資料庫伺服器比沒有索引快得多的速度檢索特定的行，尤其是在查詢語句當中包含有MAX(),MIN()和ORDERBY這些命令的時候，效能提高更為明顯。

那該對那些欄位進行索引呢？

一般來說，索引應該建立在那些將用於join，where判斷和orderby排序的遊戲陪玩app開發欄位上。儘量不要對資料庫中某個含有大量重複的值的欄位建立索引，對於一個ENUM型別的欄位來說，出現大量重複值是很有可能的情況。
例如customerinfo中的“province”…欄位，在這樣的欄位上建立索引將不會有什麼幫助；相反，還有可能降低遊戲陪玩app開發中資料庫的效能。我們在建立表的時候可以同時建立合適的索引，也可以使用ALTERTABLE或CREATEINDEX在以後建立索引。
此外，MySQL從版本3.23.23開始支援全文索引和搜尋。全文索引在MySQL中是一個FULLTEXT型別索引，但僅能用於MyISAM型別的表。對於一個大的資料庫，將資料裝載到一個沒有FULLTEXT索引的表中，然後再使用ALTERTABLE或CREATEINDEX建立索引，將是非常快的。但如果將資料裝載到一個已經有FULLTEXT索引的表中，執行過程將會非常慢。

10、優化de的查詢語句

1 不使用子查詢

例：

SELECT * FROM t1 WHERE id (SELECT id FROM t2 WHERE name=’hechunyang’);

子查詢在MySQL5.5版本里，內部執行計劃器是這樣執行的：先查外表再匹配內表，而不是先查內表t2，當外表的資料很大時，查詢速度會非常慢。
在MariaDB10/MySQL5.6版本里，採用join關聯方式對其進行了優化，這條SQL會自動轉換為

SELECT t1.* FROM t1 JOIN t2 ON t1.id = t2.id;

但請注意的是：優化只針對SELECT有效，對UPDATE/DELETE子查詢無效，遊戲陪玩app開發中的固生產環境應避免使用子查詢

2 避免函式索引

例：

SELECT * FROM t WHERE YEAR(d) >= 2016;

由於遊戲陪玩app開發中的MySQL不像Oracle那樣支援函式索引，即使d欄位有索引，也會直接全表掃描。
應改為—–>

SELECT * FROM t WHERE d >= ‘2016-01-01’;

3 用IN來替換OR

低效查詢

SELECT * FROM t WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30;

—–>高效查詢

SELECT * FROM t WHERE LOC_IN IN (10,20,30);

4 LIKE雙百分號無法使用到索引

SELECT * FROM t WHERE name LIKE ‘%de%’;

—–>

SELECT * FROM t WHERE name LIKE ‘de%’;

目前只有MySQL5.7支援全文索引（支援中文）

5 讀取適當的記錄LIMIT M,N

SELECT * FROM t WHERE 1;

—–>

SELECT * FROM t WHERE 1 LIMIT 10;

6 避免遊戲陪玩app開發中資料型別不一致

SELECT * FROM t WHERE id = ’19’;

—–>

SELECT * FROM t WHERE id = 19;

7 分組統計可以禁止排序

SELECT goods_id,count(*) FROM t GROUP BY goods_id;

預設情況下，遊戲陪玩app開發中的MySQL對所有GROUP BY col1，col2…的欄位進行排序。如果查詢包括GROUP BY，想要避免排序結果的消耗，則可以指定ORDER BY NULL禁止排序。
—–>

SELECT goods_id,count(*) FROM t GROUP BY goods_id ORDER BY NULL;

8 避免隨機取記錄

SELECT * FROM t1 WHERE 1=1 ORDER BY RAND() LIMIT 4;

MySQL不支援函式索引，會導致全表掃描
—–>

SELECT * FROM t1 WHERE id >= CEIL(RAND()*1000) LIMIT 4;

9 禁止不必要的ORDER BY排序

SELECT count(1) FROM user u LEFT JOIN user_info i ON u.id = i.user_id WHERE 1 = 1 ORDER BY u.create_time DESC;

—–>

SELECT count(1) FROM user u LEFT JOIN user_info i ON u.id = i.user_id;

10 批量INSERT插入

INSERT INTO t (id, name) VALUES(1,’Bea’);INSERT INTO t (id, name) VALUES(2,’Belle’);INSERT INTO t (id, name) VALUES(3,’Bernice’);

—–>

INSERT INTO t (id, name) VALUES(1,’Bea’), (2,’Belle’),(3,’Bernice’);

以上便是“遊戲陪玩app開發中，Mysql的sql優化方法”的全部內容，希望在大家進行遊戲陪玩aoo開發時有幫助。

本文轉載自網路，轉載僅為分享乾貨知識，如有侵權歡迎聯絡雲豹科技進行刪除處理
原文連結：https://blog.csdn.net/weixin_42047611/article/details/81772149


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/69996194/viewspace-2842959/，如需轉載，請註明出處，否則將追究法律責任。

相關文章