MySQL常用命令與內部元件及SQL優化詳情

1. 一些常用的 MySQL 命令

#連線MySQL
mysql -h 127.0.0.1 -u UserName -p  pwd -P 3306
#建立新使用者
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
#賦許可權,%表示所有(host)：
grant all privileges on *.* to 'username'@'%';
#修改密碼
update user set password=password("123456") where user='root';
#檢視當前使用者的許可權
show grants for root@"%";
#顯示所有資料庫
show databases; 
#開啟資料庫
use dbname； 
#檢視庫中有哪些表
show tables
#顯示錶mysql資料庫中user表的列資訊）
describe user
#檢視連線（包括使用者、正在執行的操作、狀態等）
show processlist 
#重新整理連線
flush privileges
#關閉某連線
kill id
#查詢庫中所有的表
select * from information_schema.tables where table_schema='zhebase';
#查詢表資訊（欄位，欄位型別，是否為空，編碼，備註等）
select * from information_schema.columns where table_schema='zhebase' and table_name='student_inndb';
#檢視MySQL許可權 Host列表示那個Ip可以連線，User表示使用者，後面的欄位是許可權
select * from mysql.user;
#檢視全域性伺服器關閉非互動連線之前等待活動的秒數
show global variables like "wait_timeout";
#設定全域性伺服器關閉非互動連線之前等待活動的秒數（預設8小時不傳送命令自動斷連）
set global wait_timeout=28800; 

開發當中我們大多數時候用的都是長連線，把連線放在 Pool 內進行管理，但是長連線有時候會導致 MySQL 佔用記憶體飆升，這是因為 MySQL 在執行過程中臨時使用的記憶體是管理在連線物件裡面的。這些資源會在連線斷開的時候才釋放。所以如果長連線累積下來，可能導致記憶體佔用太大，被系統強行殺掉（OOM)，從現象看就是 MySQL 異常重啟了。 怎麼解決這類問題呢？  1、定期斷開長連線。 使用一段時間，或者程式裡面判斷執行過一個佔用記憶體的大查詢後，斷開連線，之後要查詢再重連。  2、如果你用的是 MySQL 5.7 或更新版本，可以在每次執行一個比較大的操作後，通過執行 mysql_reset_connection 來重新初始化連線資源。 這個過程不需要重連和重新做許可權驗證，但是會將連線恢復到剛剛建立完時的狀態。

為什麼說MySQL查詢快取是否雞肋？

• 使用場景極少，表一改動就需要重新維護
• innodb，MyISAM 等引擎層有 buffer_pool 會自動快取查詢頻繁的資料
• 可以使用第三方中介軟體代替
• LRU淘汰策略

#my.cnf組態檔中，一般將my.cnf引數 query_cache_type 設定成 DEMAND
query_cache_type有3個值 0代表關閉查詢快取OFF，1代表開啟ON，2（DEMAND）代表當sql語句中有SQL_CACHE 關鍵詞時才快取

2.MySQL的內部元件結構

 連線MySQL的過程：

• 1.完成經典的 TCP 握手建立連線
• 2.驗證使用者登入使用者名稱密碼
• 3.驗證連線許可權，是否執行該Ip連線（User表中的Host欄位）
• 4.開闢專屬 session 空間，連線後預設長連線，無操作8小時有效
• 5.將user表許可權加入專屬空間
• 6.每次執行命令在專屬空間中查詢是否有許可權進行操作（許可權修改後，如不重新連線，許可權仍然不會改變，即使重新整理連線也是如此)

MySQL優化器與執行計劃

工作過程：

• 1.詞法分析、語法分析、語意檢查
• 2.預處理階段(查詢重寫等)
• 3.查詢優化階段(可詳細劃分為邏輯優化、物理優化兩部分)
• 4.查詢優化器優化依據，來自於代價估算器估算結果(它會呼叫統計資訊作為計算依據)
• 5.交由執行器執行

SQL執行過程

• 1.使用者端提交一條語句
• 2.先在查詢快取（相當於一個Map，SQL語句是Key，結果集是Map）檢視是否存在對應的快取資料，如有則直接返回(一般有的可能性極小，因此一般建議關閉查詢快取)。MySQL 8.0開始取消了快取器，5.0 預設關閉
• 3.交給解析器處理，解析器會將提交的語句生成一個解析樹。
• 4.前處理器會處理解析樹，形成新的解析樹。這一階段存在一些SQL改寫的過程。
• 5.改寫後的解析樹提交給查詢優化器。查詢優化器生成執行計劃。
• 6.執行計劃交由執行引擎呼叫儲存引擎介面，完成執行過程。這裡要注意，MySQL的Server層和Engine層是分離的。
• 7.最終的結果由執行引擎返回給使用者端，如果開啟查詢快取的話，則會快取

詞法分析器原理

詞法分析器分成6個主要步驟完成對sql語句的分析  1、詞法分析  2、語法分析  3、語意分析  4、構造執行樹  5、生成執行計劃  6、計劃的執行

查詢優化器

• 負責生成 SQL 語句的有效執行計劃的資料庫元件
• 優化器是資料庫的核心價值所在，它是資料庫的“大腦”
• 優化SQL，某種意義上就是理解優化器的行為
• 優化的依據是執行成本（CBO）
• 優化器工作的前提是瞭解資料，工作的目的是解析SQL，生成執行計劃
• 只要有WHERE的地方就會用到查詢優化器，並非SELECT獨有

舉例：

Select  EMPLOYEE.Name , WELFARE.Bonus From  EMPLOYEE , WELFARE  Where  EMPLOYEE.Seniority > 5  And  EMPLOYEE.Seniority = WELFARE.Seniority ;
Select  EMPLOYEE.Name , WELFARE.Bonus From  EMPLOYEE , WELFARE  Where  EMPLOYEE.Seniority > 5  And  EMPLOYEE.Seniority = WELFARE.Seniority   And  EMPLOYEE.Seniority > 5;

查詢重寫： 因為第一條將EMPLOYEE中Seniority > 5 的行與 WELFARE 中的所有行作外連線再來找 Seniority 相等的行，而第二條則是將 EMPLOYEE 中 Seniority > 5 的行和 WELFARE 中 Seniority > 5 的行作外連線再來找 Seniority 相等的行，第二條語句只有更少的行參與外連線，效率更高。寫 SQL 時查詢優化器自動重寫。

4. SQL執行順序

(7) SELECT (8) DISTINCT <select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP <group_by_list>
(6) HAVING <having_condition>
(9) ORDER BY <order_by_list>
(10) LIMIT <limit_number>

5.MySQL資料型別選擇

在設計表時，選擇資料型別時一般先確定大的型別（數位，字串，時間，二進位制），然後再根據有無符號、取值範圍、是否定長等確定具體的資料型別。在設計時，儘量使用更小的資料型別以達到更優的效能。並且在定義時儘量使用 NOT NULL，避免 NULL 值。

數值型別

首先了解：

• 1 byte = 8 bit (1位元組等於8位元，當需要符號時，符號佔用1位)
• float 的指數位有8位元，尾數位有23位，符號位 1 位，float 的指數範圍，為 -127~+128，按二補數的形式來劃分。有效位數 7 位
• double 有效位數 15 位
• 對DECIMAL(M,D) ，如果 M>D，為 M+2 否則為 D+2 位元組

建議：

• 如果整型資料沒有負數，如ID號，建議指定為UNSIGNED無符號型別，容量可以擴大一倍。
• 建議使用TINYINT代替ENUM、BITENUM、SET。
• 避免使用整數的顯示寬度，不要用INT(10)類似的方法指定欄位顯示寬度，直接用 INT。使用顯示寬度後會不足自動填充0，但對查詢無影響，查詢結果不會自動填充0。
• DECIMAL最適合儲存準確度要求高，而且用於計算的資料，比如價格。但是在使用DECIMAL型別的時候，注意長度設定。
• 建議使用整型型別來運算和儲存實數。
• 整數通常是最佳的資料型別，因為它速度快，並且能使用AUTO_INCREMENT。

日期和時間

 建議：

• MySQL 能儲存的最小時間粒度為秒。
• 建議用 DATE 資料型別來儲存日期。MySQL 中預設的日期格式是 yyyy-MM-dd。
• 用 MySQL 的內時間型別 DATE、TIME、DATETIME 來儲存時間，而不是使用字串。
• 當資料格式為 TIMESTAMP 和 DATETIME 時，可以用 CURRENT_TIMESTAMP 作為預設（MySQL5.6以後）， MySQL 會自動返回記錄插入的確切時間。
• TIMESTAMP 是 UTC 時間戳，與時區相關。
• DATETIME 的儲存格式是一個 YYYYMMDD HH:MM:SS 的整數，與時區無關。
• 除非有特殊需求，一般的公司建議使用 TIMESTAMP，比DATETIME更節約空間，大公司使用DATETIME，因為要用考慮 TIMESTAMP 將來的時間上限（1970-2037）問題。
• 不要使用 Unix 的時間戳儲存為整數值，處理起來極其不方便。

字串

建議

• 字串的長度相差較大用 VARCHAR；字串短，且所有值都接近一個長度用 CHAR。
• CHAR 和 VARCHAR 適用於包括人名、郵政編碼、電話號碼和不超過255個字元長度的任意字母數位組合。那些 要用來計算的數位不要用 VARCHAR 型別儲存，因為可能會導致一些與計算相關的問題。換句話說，可能影響到計算的準確性和完整性。
• 儘量少用 BLOB 和 TEXT，如果實在要用可以考慮將 BLOB 和 TEXT 欄位單獨存一張表，用 id 關聯。
• BLOB 系列儲存二進位制字串，與字元集無關。TEXT 系列儲存非二進位制字串，與字元集相關。
• BLOB 和 TEXT 都不能有預設值。

6.MySQL優化

MySQL優化分類

• 減少磁碟IO 全表掃描 臨時表 紀錄檔、資料塊 fsync
• 減少網路頻寬 返回資料過多 互動次數過多
• 降低CPU消耗 排序分組：order by, group by 聚合函數：max,min,count,sum.. 邏輯讀

優化方法

• 建立索引減少掃描量
• 調整索引減少計算量
• 索引覆蓋（減少不必存取的列，避免回表查詢）
• SQL改寫
• 干預執行計劃

SQL優化原則

減少存取量： 資料存取是資料庫系統最核心功能，所以 IO 是資料庫系統中最容易出現效能瓶頸，減少 SQL 存取 IO 量是 SQL 優化的第一步；資料塊的邏輯讀也是產生CPU開銷的因素之一。

• 減少存取量的方法：建立合適的索引、減少不必存取的列、使用索引覆蓋、語句改寫。

減少計算操作： 計算操作進行優化也是SQL優化的重要方向。SQL 中排序、分組、多表連線操作等計算操作都是十分消耗 CPU 的。

• 減少 SQL 計算操作的方法：排序列加入索引、適當的列冗餘、SQL 拆分、計算功能拆分。

EXPLAIN 檢視執行計

 type列，連線型別。一個好的SQL語句至少要達到range級別。杜絕出現all級別。

• 1. system：表只有一行記錄，const型別的特例，基本不會出現，可以忽略
• 2. const：通過索引一次就查詢出來了，const用於比較primary key或者unique索引。只需匹配一行資料，所有很快。如果將主鍵置於where列表中，mysql就能將該查詢轉換為一個const
• 3. eq_ref：唯一性索引掃描，對於每個索引鍵，表中只有一條記錄與之匹配。常見於主鍵 或 唯一索引掃描。
• 4. ref：非唯一性索引掃描，返回匹配某個單獨值的所有行。本質是也是一種索引存取，它返回所有匹配某個單獨值的行，然而他可能會找到多個符合條件的行，所以它應該屬於查詢和掃描的混合體。
• 5. range：只檢索給定範圍的行，使用一個索引來選擇行。key列顯示使用了那個索引。一般就是在where語句中出現了bettween、<、>、in等的查詢。這種索引列上的範圍掃描比全索引掃描要好。只需要開始於某個點，結束於另一個點，不用掃描全部索引
• 6. index：Full Index    Scan，index與ALL區別為index型別只遍歷索引樹。這通常為ALL塊，應為索引檔案通常比資料檔案小。（Index與ALL雖然都是讀全表，但index是從索引中讀取，而ALL是從硬碟讀取）
• 7. all：Full Table Scan，遍歷全表以找到匹配的行

key列，使用到的索引名。如果沒有選擇索引，值是NULL。 key_len列，索引長度。 rows列，掃描行數。該值是預估值。 extra列，詳細說明。注意，常見的不太友好的值，如下：Using filesort，Using temporary。

processlist干預執行計劃

• show [full] processlist
• information_schema.processlist copy to tmp table： 出現在某些alter table語句的copy table操作 Copying to tmp table on disk： 由於臨時結果集大於tmp_table_size，正在將臨時表從記憶體儲存轉為磁碟儲存以此節省記憶體 converting HEAP to MyISAM： 執行緒正在轉換內部MEMORY臨時表到磁碟MyISAM臨 時表 Creating sort index： 正在使用內部臨時表處理select查詢 Sorting index： 磁碟排序操作的一個過程 Sending data ： 正在處理SELECT查詢的記錄，同時正在把結果傳送給使用者端 Waiting for table metadata lock: 等待後設資料鎖

SELECT語句務必指明欄位名稱

SELECT * 增加很多不必要的消耗（CPU、IO、記憶體、網路頻寬） 直接使用select欄位名稱還增加了使用覆蓋索引的可能性

• 如果排序欄位沒有用到索引，就儘量少排序
• 分頁時要選擇合理的方式

select id,name from customer limit 100000, 10 //查詢從十萬條開始的20條資料

上述程式碼，隨著分頁的後移，效率越來越慢，優化方法如下：可以取上一頁的最大行數的 id（前提是ID 遞增，且非聯合主鍵，一般不建議設定聯合主鍵，主鍵前面都可以加上ID作為主鍵），然後根據這個最大的 ID 來限制下一頁的起點。或者通過索引查 id，在通過id查詢出資料

合理使用in和exits

select * from A where id in (select id from B)
select * from A where exists(select id from B where id=A.id)

in先執行子查詢再執行主查詢，exits先執行主查詢再執行子查詢。如果子查詢得出的結果集記錄較少，主查詢中的表較大且又有索引時應該用in反之如果外層的主查詢記錄較少，子查詢中的表大，又有索引時使用exists

原則：小表驅動大表

關於not in 和not exists

如果查詢語句使用了 not in 那麼內外表都會放棄索引進行全表掃描；而 not extsts 的子查詢依然能用到表上的索引。所以 not exists 都比 not in 要快。也可以使用一些方法轉換邏輯來進行優化

//原SQL語句：
select name from A where A.id not in (select B.id from B)
//優化後的SQL語句：
select name from A Left join B on where A.id = A.id where B.id is null

order by排序欄位和where條件要匹配（關於聯合索引）

當 where 條件和 order by 排序欄位不匹配時，即使where條件中用到了索引，但執行 order by 時仍然會進行全表掃描（索引只能生效一個，且遵循最左匹配原則）;order by後的索引生效時（索引本質是倒排表）效率會得到極大的提升。

select a,b,c from customer where a = 'xxx' and b = 'xxx' order by c;

• 1.最左字首匹配原則：在MySQL建立聯合索引時會遵守最左字首匹配原則，即最左優先，在檢索資料時從聯合索引的最左邊
• 開始匹配。
• 2.當a,b,c為聯合索引時遵循最左匹配原則，即：a,ab,abc索引都會生效,但b,c,bc,ac等不會生效（執行計劃會使用到，
• type列為index，掃描索引樹，效率相對於最左匹配的索引效率極低），所以定要注意索引順序，最常用的最段要放在最前面。
• 3.例如，建立一個a,b聯合索引，它的索引樹圖如下。由圖可以看出a值是有序的（1，1，2，2，3，3），b值是無序的，但是在a值相等的情況下b值又是有序的。由此可以看出MySQL建立聯合索引時首先會對聯合索引的最左邊第一個欄位排序，在第一個欄位的排序基礎上，然後在對第二個欄位進行排序。所以b單獨作為條件時，索引是無效的。
• 4.當a,b,c三個索引都用到時，只有全匹配，無論順序如何，索引是有效的，MySQL執行計劃會對其進行優化，自動使用最優方案執行。

不建議使用%字首模糊查詢

使用like '%name%'或者like '%name'會導致索引失效而導致全表掃描。但使用like 'name%'不會。

解決方法：

• - 1.使用全文索引 
• - 2.使用Elasticsearch等搜尋工具（不怎麼修改的欄位才建議使用，實際是倒排索引）

注意：  1.全文索引的儲存引擎一定是Myisam,InnoDB沒有全文索引  2.全文索引對中文不太友好

//建立全文索引
ALTER TABLE cust ADD FULLTEXT INDEX idx_cust_address ('cust_address');
//使用全文索引
select name from cust where match(cust_address) against('湖南');

倒排索引是一種索引資料結構：從文字資料內容中提取出不重複的單詞進行分詞，每1個單詞對應1個ID對單詞進行區分，還對應1個該單詞在那些檔案中出現的列表 把這些資訊組建成索引。倒排索引還記錄了該單詞在檔案中出現位置、頻率（次數/TF）用於快速定位檔案和對搜素結果進行排序。

關於範圍查詢

對於聯合索引來說，如果存在範圍查詢，比如between、>、<等條件時，會造成後面的索引欄位失效

避免在where子句中對欄位進行null值判斷及!=和<>

對於null的判斷以及!=和<>會導致引擎放棄使用索引而進行全表掃描。

關於OR

儘量使用union all或者是union方式來代替or。  union和union all的區別主要是union需要將結果集合並後再進行過濾操作過濾掉重複資料，這就會涉及到排序，增加大量的CPU運算，加大資源消耗及延遲。使用union all的前提條件是兩個結果集沒有重複資料。

只需要一條資料的時候，使用limit 1

可以使EXPLAIN中type列達到const型別

分段查詢

在一些使用者選擇頁面中，可能一些使用者選擇的時間範圍過大，造成查詢緩慢。主要的原因是掃描行數過多。這個時候可以通過程式，分段進行查詢，迴圈遍歷，將結果合併處理進行展示。

避免在where子句中對欄位進行表示式及函數操作

應避免在where子句中對欄位進行函數等操作，這將導致引擎放棄使用索引而進行全表掃描。

//原SQL
select id,name from customer where salary/2 > 5000;
//優化後
select id,name from customer where salary > 5000*2;

儘量使用 inner join，避免 left join

參與聯合查詢的表至少為2張表，一般都存在大小之分。如果連線方式是inner join，在沒有其他過濾條件的情況下,MySQL會自動選擇小表作為驅動表，但是left join在驅動表的選擇上遵循的是左邊驅動右邊的原則，即left join左邊的表名為驅動表。

IN包含的值不應過多

MySQL對於IN做了相應的優化，即將IN中的常數全部儲存在一個陣列裡面，而且這個陣列是排好序的。但是如果數值較多，產生的消耗也是比較大的。再例如：select id from t where num in(1,2,3) 對於連續的數值，能用between就不要用in了。或者使用連線來替換。

關於索引

• 對查詢進行優化，應儘量避免全表掃描，首先應考慮在 where 及 order by 涉及的列上建立索引。
• 並不是所有索引對查詢都有效，SQL是根據表中資料來進行查詢優化的。當資料列差不多時（如男、女等）索引也無法優化查詢效率。
• 索引並不是越多越好，經常進行查詢的列建議新增索引，但經常進行修改的列不建議新增索引。在增刪改操作會對索引進行維護，降低執行效率，且索引需要佔用資料庫資源

到此這篇關於MySQL常用命令與內部元件及SQL優化詳情的文章就介紹到這了,更多相關MySQL優化內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！