1. 一些常用的 MySQL 命令

#連接MySQL
mysql -h 127.0.0.1 -u UserName -p  pwd -P 3306
#創(chuàng)建新用戶
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
#賦權(quán)限,%表示所有(host)：
grant all privileges on *.* to 'username'@'%';
#修改密碼
update user set password=password("123456") where user='root';
#查看當前用戶的權(quán)限
show grants for root@"%";
#顯示所有數(shù)據(jù)庫
show databases; 
#打開數(shù)據(jù)庫
use dbname； 
#查看庫中有哪些表
show tables
#顯示表mysql數(shù)據(jù)庫中user表的列信息）
describe user
#查看連接（包括用戶、正在執(zhí)行的操作、狀態(tài)等）
show processlist 
#刷新連接
flush privileges
#關(guān)閉某連接
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權(quán)限 Host列表示那個Ip可以連接，User表示用戶，后面的字段是權(quán)限
select * from mysql.user;
#查看全局服務(wù)器關(guān)閉非交互連接之前等待活動的秒數(shù)
show global variables like "wait_timeout";
#設(shè)置全局服務(wù)器關(guān)閉非交互連接之前等待活動的秒數(shù)（默認8小時不發(fā)送命令自動斷連）
set global wait_timeout=28800; 

開發(fā)當中我們大多數(shù)時候用的都是長連接，把連接放在 Pool 內(nèi)進行管理，但是長連接有時候會導(dǎo)致 MySQL 占用內(nèi)存飆升，這是因為 MySQL 在執(zhí)行過程中臨時使用的內(nèi)存是管理在連接對象里面的。這些資源會在連接斷開的時候才釋放。所以如果長連接累積下來，可能導(dǎo)致內(nèi)存占用太大，被系統(tǒng)強行殺掉（OOM)，從現(xiàn)象看就是 MySQL 異常重啟了。 怎么解決這類問題呢？  1、定期斷開長連接。 使用一段時間，或者程序里面判斷執(zhí)行過一個占用內(nèi)存的大查詢后，斷開連接，之后要查詢再重連。  2、如果你用的是 MySQL 5.7 或更新版本，可以在每次執(zhí)行一個比較大的操作后，通過執(zhí)行 mysql_reset_connection 來重新初始化連接資源。 這個過程不需要重連和重新做權(quán)限驗證，但是會將連接恢復(fù)到剛剛創(chuàng)建完時的狀態(tài)。

為什么說MySQL查詢緩存是否雞肋？

• 使用場景極少，表一改動就需要重新維護
• innodb，MyISAM 等引擎層有 buffer_pool 會自動緩存查詢頻繁的數(shù)據(jù)
• 可以使用第三方中間件代替
• LRU淘汰策略

#my.cnf配置文件中，一般將my.cnf參數(shù) query_cache_type 設(shè)置成 DEMAND
query_cache_type有3個值 0代表關(guān)閉查詢緩存OFF，1代表開啟ON，2（DEMAND）代表當sql語句中有SQL_CACHE 關(guān)鍵詞時才緩存

2.MySQL的內(nèi)部組件結(jié)構(gòu)

 連接MySQL的過程：

• 1.完成經(jīng)典的 TCP 握手建立連接
• 2.驗證用戶登錄用戶名密碼
• 3.驗證連接權(quán)限，是否運行該Ip連接（User表中的Host字段）
• 4.開辟專屬 session 空間，連接后默認長連接，無操作8小時有效
• 5.將user表權(quán)限加入專屬空間
• 6.每次執(zhí)行命令在專屬空間中查找是否有權(quán)限進行操作（權(quán)限修改后，如不重新連接，權(quán)限仍然不會改變，即使刷新連接也是如此)

MySQL優(yōu)化器與執(zhí)行計劃

工作過程：

• 1.詞法分析、語法分析、語義檢查
• 2.預(yù)處理階段(查詢重寫等)
• 3.查詢優(yōu)化階段(可詳細劃分為邏輯優(yōu)化、物理優(yōu)化兩部分)
• 4.查詢優(yōu)化器優(yōu)化依據(jù)，來自于代價估算器估算結(jié)果(它會調(diào)用統(tǒng)計信息作為計算依據(jù))
• 5.交由執(zhí)行器執(zhí)行

SQL執(zhí)行過程

• 1.客戶端提交一條語句
• 2.先在查詢緩存（相當于一個Map，SQL語句是Key，結(jié)果集是Map）查看是否存在對應(yīng)的緩存數(shù)據(jù)，如有則直接返回(一般有的可能性極小，因此一般建議關(guān)閉查詢緩存)。MySQL 8.0開始取消了緩存器，5.0 默認關(guān)閉
• 3.交給解析器處理，解析器會將提交的語句生成一個解析樹。
• 4.預(yù)處理器會處理解析樹，形成新的解析樹。這一階段存在一些SQL改寫的過程。
• 5.改寫后的解析樹提交給查詢優(yōu)化器。查詢優(yōu)化器生成執(zhí)行計劃。
• 6.執(zhí)行計劃交由執(zhí)行引擎調(diào)用存儲引擎接口，完成執(zhí)行過程。這里要注意，MySQL的Server層和Engine層是分離的。
• 7.最終的結(jié)果由執(zhí)行引擎返回給客戶端，如果開啟查詢緩存的話，則會緩存

詞法分析器原理

詞法分析器分成6個主要步驟完成對sql語句的分析  1、詞法分析  2、語法分析  3、語義分析  4、構(gòu)造執(zhí)行樹  5、生成執(zhí)行計劃  6、計劃的執(zhí)行

查詢優(yōu)化器

• 負責(zé)生成 SQL 語句的有效執(zhí)行計劃的數(shù)據(jù)庫組件
• 優(yōu)化器是數(shù)據(jù)庫的核心價值所在，它是數(shù)據(jù)庫的“大腦”
• 優(yōu)化SQL，某種意義上就是理解優(yōu)化器的行為
• 優(yōu)化的依據(jù)是執(zhí)行成本（CBO）
• 優(yōu)化器工作的前提是了解數(shù)據(jù)，工作的目的是解析SQL，生成執(zhí)行計劃
• 只要有WHERE的地方就會用到查詢優(yōu)化器，并非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 時查詢優(yōu)化器自動重寫。

4. SQL執(zhí)行順序

(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數(shù)據(jù)類型選擇

在設(shè)計表時，選擇數(shù)據(jù)類型時一般先確定大的類型（數(shù)字，字符串，時間，二進制），然后再根據(jù)有無符號、取值范圍、是否定長等確定具體的數(shù)據(jù)類型。在設(shè)計時，盡量使用更小的數(shù)據(jù)類型以達到更優(yōu)的性能。并且在定義時盡量使用 NOT NULL，避免 NULL 值。

數(shù)值類型

首先了解：

• 1 byte = 8 bit (1字節(jié)等于8位，當需要符號時，符號占用1位)
• float 的指數(shù)位有8位，尾數(shù)位有23位，符號位 1 位，float 的指數(shù)范圍，為 -127~+128，按補碼的形式來劃分。有效位數(shù) 7 位
• double 有效位數(shù) 15 位
• 對DECIMAL(M,D) ，如果 M>D，為 M+2 否則為 D+2 字節(jié)

建議：

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

日期和時間

 建議：

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

字符串

建議

• 字符串的長度相差較大用 VARCHAR；字符串短，且所有值都接近一個長度用 CHAR。
• CHAR 和 VARCHAR 適用于包括人名、郵政編碼、電話號碼和不超過255個字符長度的任意字母數(shù)字組合。那些 要用來計算的數(shù)字不要用 VARCHAR 類型保存，因為可能會導(dǎo)致一些與計算相關(guān)的問題。換句話說，可能影響到計算的準確性和完整性。
• 盡量少用 BLOB 和 TEXT，如果實在要用可以考慮將 BLOB 和 TEXT 字段單獨存一張表，用 id 關(guān)聯(lián)。
• BLOB 系列存儲二進制字符串，與字符集無關(guān)。TEXT 系列存儲非二進制字符串，與字符集相關(guān)。
• BLOB 和 TEXT 都不能有默認值。

6.MySQL優(yōu)化

MySQL優(yōu)化分類

• 減少磁盤IO 全表掃描 臨時表 日志、數(shù)據(jù)塊 fsync
• 減少網(wǎng)絡(luò)帶寬 返回數(shù)據(jù)過多 交互次數(shù)過多
• 降低CPU消耗 排序分組：order by, group by 聚合函數(shù)：max,min,count,sum.. 邏輯讀

優(yōu)化方法

• 創(chuàng)建索引減少掃描量
• 調(diào)整索引減少計算量
• 索引覆蓋（減少不必訪問的列，避免回表查詢）
• SQL改寫
• 干預(yù)執(zhí)行計劃

SQL優(yōu)化原則

減少訪問量： 數(shù)據(jù)存取是數(shù)據(jù)庫系統(tǒng)最核心功能，所以 IO 是數(shù)據(jù)庫系統(tǒng)中最容易出現(xiàn)性能瓶頸，減少 SQL 訪問 IO 量是 SQL 優(yōu)化的第一步；數(shù)據(jù)塊的邏輯讀也是產(chǎn)生CPU開銷的因素之一。

• 減少訪問量的方法：創(chuàng)建合適的索引、減少不必訪問的列、使用索引覆蓋、語句改寫。

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

• 減少 SQL 計算操作的方法：排序列加入索引、適當?shù)牧腥哂唷QL 拆分、計算功能拆分。

EXPLAIN 查看執(zhí)行計

 type列，連接類型。一個好的SQL語句至少要達到range級別。杜絕出現(xiàn)all級別。

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

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

processlist干預(yù)執(zhí)行計劃

• show [full] processlist
• information_schema.processlist copy to tmp table： 出現(xiàn)在某些alter table語句的copy table操作 Copying to tmp table on disk： 由于臨時結(jié)果集大于tmp_table_size，正在將臨時表從內(nèi)存存儲轉(zhuǎn)為磁盤存儲以此節(jié)省內(nèi)存 converting HEAP to MyISAM： 線程正在轉(zhuǎn)換內(nèi)部MEMORY臨時表到磁盤MyISAM臨 時表 Creating sort index： 正在使用內(nèi)部臨時表處理select查詢 Sorting index： 磁盤排序操作的一個過程 Sending data ： 正在處理SELECT查詢的記錄，同時正在把結(jié)果發(fā)送給客戶端 Waiting for table metadata lock: 等待元數(shù)據(jù)鎖

SELECT語句務(wù)必指明字段名稱

SELECT * 增加很多不必要的消耗（CPU、IO、內(nèi)存、網(wǎng)絡(luò)帶寬） 直接使用select字段名稱還增加了使用覆蓋索引的可能性

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

select id,name from customer limit 100000, 10 //查詢從十萬條開始的20條數(shù)據(jù)

上述代碼，隨著分頁的后移，效率越來越慢，優(yōu)化方法如下：可以取上一頁的最大行數(shù)的 id（前提是ID 遞增，且非聯(lián)合主鍵，一般不建議設(shè)置聯(lián)合主鍵，主鍵前面都可以加上ID作為主鍵），然后根據(jù)這個最大的 ID 來限制下一頁的起點。或者通過索引查 id，在通過id查詢出數(shù)據(jù)

合理使用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先執(zhí)行子查詢再執(zhí)行主查詢，exits先執(zhí)行主查詢再執(zhí)行子查詢。如果子查詢得出的結(jié)果集記錄較少，主查詢中的表較大且又有索引時應(yīng)該用in反之如果外層的主查詢記錄較少，子查詢中的表大，又有索引時使用exists

原則：小表驅(qū)動大表

關(guān)于not in 和not exists

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

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

order by排序字段和where條件要匹配（關(guān)于聯(lián)合索引）

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

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

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

不建議使用%前綴模糊查詢

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

解決方法：

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

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

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

倒排索引是一種索引數(shù)據(jù)結(jié)構(gòu)：從文本數(shù)據(jù)內(nèi)容中提取出不重復(fù)的單詞進行分詞，每1個單詞對應(yīng)1個ID對單詞進行區(qū)分，還對應(yīng)1個該單詞在那些文檔中出現(xiàn)的列表 把這些信息組建成索引。倒排索引還記錄了該單詞在文檔中出現(xiàn)位置、頻率（次數(shù)/TF）用于快速定位文檔和對搜素結(jié)果進行排序。

關(guān)于范圍查詢

對于聯(lián)合索引來說，如果存在范圍查詢，比如between、>、<等條件時，會造成后面的索引字段失效

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

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

關(guān)于OR

盡量使用union all或者是union方式來代替or。  union和union all的區(qū)別主要是union需要將結(jié)果集合并后再進行過濾操作過濾掉重復(fù)數(shù)據(jù)，這就會涉及到排序，增加大量的CPU運算，加大資源消耗及延遲。使用union all的前提條件是兩個結(jié)果集沒有重復(fù)數(shù)據(jù)。

只需要一條數(shù)據(jù)的時候，使用limit 1

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

分段查詢

在一些用戶選擇頁面中，可能一些用戶選擇的時間范圍過大，造成查詢緩慢。主要的原因是掃描行數(shù)過多。這個時候可以通過程序，分段進行查詢，循環(huán)遍歷，將結(jié)果合并處理進行展示。

避免在where子句中對字段進行表達式及函數(shù)操作

應(yīng)避免在where子句中對字段進行函數(shù)等操作，這將導(dǎo)致引擎放棄使用索引而進行全表掃描。

//原SQL
select id,name from customer where salary/2 > 5000;
//優(yōu)化后
select id,name from customer where salary > 5000*2;

盡量使用 inner join，避免 left join

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

IN包含的值不應(yīng)過多

MySQL對于IN做了相應(yīng)的優(yōu)化，即將IN中的常量全部存儲在一個數(shù)組里面，而且這個數(shù)組是排好序的。但是如果數(shù)值較多，產(chǎn)生的消耗也是比較大的。再例如：select id from t where num in(1,2,3) 對于連續(xù)的數(shù)值，能用between就不要用in了。或者使用連接來替換。

關(guān)于索引

• 對查詢進行優(yōu)化，應(yīng)盡量避免全表掃描，首先應(yīng)考慮在 where 及 order by 涉及的列上建立索引。
• 并不是所有索引對查詢都有效，SQL是根據(jù)表中數(shù)據(jù)來進行查詢優(yōu)化的。當數(shù)據(jù)列差不多時（如男、女等）索引也無法優(yōu)化查詢效率。
• 索引并不是越多越好，經(jīng)常進行查詢的列建議添加索引，但經(jīng)常進行修改的列不建議添加索引。在增刪改操作會對索引進行維護，降低執(zhí)行效率，且索引需要占用數(shù)據(jù)庫資源

到此這篇關(guān)于MySQL常用命令與內(nèi)部組件及SQL優(yōu)化詳情的文章就介紹到這了,更多相關(guān)MySQL優(yōu)化內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論