Mysql優(yōu)化

優(yōu)化由誰參與?

在進(jìn)行數(shù)據(jù)庫優(yōu)化時(shí)，應(yīng)由數(shù)據(jù)庫管理員、業(yè)務(wù)部門代表、應(yīng)用程序架構(gòu)師、應(yīng)用程序設(shè)計(jì)人員、應(yīng)用 程序開發(fā)人員、硬件及系統(tǒng)管理員、存儲(chǔ)管理員等，業(yè)務(wù)相關(guān)人員共同參與。

優(yōu)化的思路
  • 優(yōu)化的方向
    在數(shù)據(jù)庫優(yōu)化上有兩個(gè)主要方向：即安全與性能。
    安全 ---> 數(shù)據(jù)安全性
    性能 ---> 數(shù)據(jù)的高性能訪問
mysql優(yōu)化方向:

我們把數(shù)據(jù)庫優(yōu)化分為四個(gè)緯度：硬件，系統(tǒng)配置，數(shù)據(jù)庫表結(jié)構(gòu)，SQL及索引,成本由高到低,

  • 硬件： CPU、內(nèi)存、存儲(chǔ)、網(wǎng)絡(luò)設(shè)備等
  • 系統(tǒng)配置： 服務(wù)器系統(tǒng)、數(shù)據(jù)庫服務(wù)參數(shù)等
  • 數(shù)據(jù)庫表結(jié)構(gòu)： 高可用、分庫分表、讀寫分離、存儲(chǔ)引擎、表設(shè)計(jì)等
  • Sql及索引： sql語句、索引使用等
  • 從優(yōu)化成本進(jìn)行考慮：硬件>系統(tǒng)配置>數(shù)據(jù)庫表結(jié)構(gòu)>SQL及索引 北
  • 從優(yōu)化效果進(jìn)行考慮：硬件<系統(tǒng)配置<數(shù)據(jù)庫表結(jié)構(gòu)<SQL及索引
1.3.3 優(yōu)化的工具

msyqladmin #mysql客戶端，可進(jìn)行管理操作 mysqlshow #功能強(qiáng)大的查看shell命令 show [SESSION | GLOBAL] variables #查看數(shù)據(jù)庫參數(shù)信息 SHOW [SESSION | GLOBAL] STATUS #查看數(shù)據(jù)庫的狀態(tài)信息 SHOW ENGINE INNODB STATUS Innodb #引擎的所有狀態(tài) information_schema #獲取元數(shù)據(jù)的方法 SHOW PROCESSLIST #查看當(dāng)前所有連接session狀態(tài) explain #獲取查詢語句的執(zhí)行計(jì)劃 how index #查看表的索引信息 slow-log #記錄慢查詢語句 mysqldumpslow #分析slowlog文件的
不常用但好用的工具
zabbix #監(jiān)控主機(jī)、系統(tǒng)、數(shù)據(jù)庫（部署zabbix監(jiān)控平臺(tái)） mysqlslap #分析慢日志 sysbench #壓力測試工具 workbench #管理、備份、監(jiān)控、分析、優(yōu)化工具（比較費(fèi)資源） pt-query-digest #分析慢日志 mysql profiling #統(tǒng)計(jì)數(shù)據(jù)庫整體狀態(tài)工具 Performance Schema mysql #性能狀態(tài)統(tǒng)計(jì)的數(shù)據(jù)

數(shù)據(jù)庫調(diào)優(yōu)方案:
  • 應(yīng)急調(diào)優(yōu)的思路：
    針對突然的業(yè)務(wù)辦理卡頓，無法進(jìn)行正常的業(yè)務(wù)處理！需要立馬解決的場景！
  1. show processlist（查看鏈接session狀態(tài)）
  2. explain(分析查詢計(jì)劃)，show index from table（分析索引）
  3. 通過執(zhí)行計(jì)劃判斷，索引問題（有沒有、合不合理）或者語句本身問題
  4. show status like '%lock%'; # 查詢鎖狀態(tài)
  5. SESSION_ID; # 殺掉有問題的session
  • 常規(guī)調(diào)優(yōu)的思路：
    針對業(yè)務(wù)周期性的卡頓，例如在每天10-11點(diǎn)業(yè)務(wù)特別慢，但是還能夠使用，過了這段時(shí)間就好了。
  1. 查看slowlog，分析slowlog，分析出查詢慢的語句。
  2. 按照一定優(yōu)先級(jí)，進(jìn)行一個(gè)一個(gè)的排查所有慢語句。
  3. 分析top sql，進(jìn)行explain調(diào)試，查看語句執(zhí)行時(shí)間。
  4. 調(diào)整索引或語句本身。
1.2SQL語句優(yōu)化
  • 盡量少用數(shù)據(jù)庫自帶的函數(shù)
  • 連續(xù)條件用Between ..and 不用in
  • update語句 如果只改1,2個(gè)字段不要update全部字段消耗性能
  • 盡量使用數(shù)字類型,若只含數(shù)值信息字段不要涉及為字符型
  • 不建議經(jīng)常用select * from ,不要返回用不到的字段
  • 不帶條件的count會(huì)全表掃描
2 查詢優(yōu)化
2.1 MySQL查詢流程

數(shù)據(jù)庫查詢的流程:
① 客戶端將查詢發(fā)送到服務(wù)器；
② 服務(wù)器檢查查詢緩存，如果找到了，就從緩存中返回結(jié)果，否則進(jìn)行下一步。
③ 服務(wù)器解析，預(yù)處理。
④ 查詢優(yōu)化器優(yōu)化查詢
⑤ 生成執(zhí)行計(jì)劃，執(zhí)行引擎調(diào)用存儲(chǔ)引擎API執(zhí)行查詢
⑥服務(wù)器將結(jié)果發(fā)送回客戶端。

mysql.png

查詢緩存 在解析一個(gè)查詢語句之前，如果查詢緩存是打開的，那么MySQL會(huì)優(yōu)先檢查這個(gè)查詢是否命 中查詢緩存中的數(shù)據(jù)，如果命中緩存直接從緩存中拿到結(jié)果并返回給客戶端。這種情況下，查詢不會(huì)被 解析，不用生成執(zhí)行計(jì)劃，不會(huì)被執(zhí)行。
語法解析和預(yù)處理器 MySQL通過關(guān)鍵字將SQL語句進(jìn)行解析，并生成一棵對應(yīng)的“解析樹”。MySQL解 析器將使用MySQL語法規(guī)則驗(yàn)證和解析查詢。
查詢優(yōu)化器 語法書被校驗(yàn)合法后由優(yōu)化器轉(zhuǎn)成查詢計(jì)劃，一條語句可以有很多種執(zhí)行方式，后返回相 同的結(jié)果。優(yōu)化器的作用就是找到這其中好的執(zhí)行計(jì)劃。
查詢執(zhí)行引擎 在解析和優(yōu)化階段，MySQL將生成查詢對應(yīng)的執(zhí)行計(jì)劃，MySQL的查詢執(zhí)行引擎則根據(jù) 這個(gè)執(zhí)行計(jì)劃來完成整個(gè)查詢。常使用的也是比較多的引擎是MyISAM引擎和InnoDB引擎。 mysql5.5開始的默認(rèn)存儲(chǔ)引擎已經(jīng)變更為innodb了

2.2 查詢優(yōu)化
  • EXPLAIN 分析查詢，通過定位分析性能的瓶頸，才能更好的優(yōu)化數(shù) 據(jù)庫系統(tǒng)的性能。
EXPLAIN SELECT * FROM t_table WHERE score = 60
2.2.1 慢查詢
  • 慢查詢?nèi)罩鹃_啟
    在配置文件my.cnf或my.ini中在[mysqld]一行下面加入兩個(gè)配置參數(shù)
log-slow-queries=/data/mysqldata/slow-query.log 
long_query_time=5

log-slow-queries參數(shù)為慢查詢?nèi)罩敬娣诺奈恢茫话氵@個(gè)目錄要有mysql的運(yùn)行帳號(hào)的可寫權(quán)限，一 般都將這個(gè)目錄設(shè)置為mysql的數(shù)據(jù)存放目錄；
long_query_time=5中的5表示查詢超過五秒才記錄；
還可以在my.cnf或者my.ini中添加log-queries-not-using-indexes參數(shù)，表示記錄下沒有使用索引的查 詢。
慢查詢分析
我們可以通過打開log文件查看得知哪些SQL執(zhí)行效率低下 ，從日志中，可以發(fā)現(xiàn)查詢時(shí)間超過5 秒的 SQL，而小于5秒的沒有出現(xiàn)在此日志中。
如果慢查詢?nèi)罩局杏涗泝?nèi)容很多，可以使用mysqldumpslow工具（MySQL客戶端安裝自帶）來對慢查 詢?nèi)罩具M(jìn)行分類匯總。mysqldumpslow對日志文件進(jìn)行了分類匯總，顯示匯總后摘要結(jié)果。
2.2.2 EXPLAIN

  • EXPLAIN可以幫助開發(fā)人員分析SQL問題，EXPLAIN顯示了MySQL如何使用使用SQL執(zhí)行計(jì)劃，可以幫 助開發(fā)人員寫出更優(yōu)化的查詢語句。使用方法，在select語句前加上Explain就可以了：
    EXPLAIN SELECT * FROM products

3.0索引優(yōu)化

CREATE TABLE `tb_table` (  `id` int(11) NOT NULL 
AUTO_INCREMENT COMMENT '主鍵',  `name` varchar(20) 
DEFAULT NULL COMMENT '姓名',  `number` int(11) DEFAULT 
NULL COMMENT '編號(hào)',  PRIMARY KEY (`id`), 
 KEY `number` (`number`) ) //這里就是給number添加了索引
 ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT 
CHARSET=utf8;


drop procedure if exists tb_insert; CREATE PROCEDURE 
tb_insert() BEGIN DECLARE i INT; SET i = 0; START 
TRANSACTION; WHILE i < 10000 DO -- 然后再添加數(shù)據(jù)庫的數(shù)據(jù)，插入100萬條，再次測試有索引和沒有索引的查詢語句。    
INSERT INTO tb_table (`name`,`number`) VALUES (concat("張
三",i),i);    SET i = i+1; END WHILE; COMMIT; END;
call tb_insert();

SELECT * FROM tb_table WHERE number = 500000
通過上面的對比測試可以看出，索引是快速搜索的關(guān)鍵。MySQL索引的建立對于MySQL的高效運(yùn)行是 很重要的。對于少量的數(shù)據(jù)，沒有合適的索引影響不是很大，但是，當(dāng)隨著數(shù)據(jù)量的增加，性能會(huì)急劇 下降。

3.2 索引的類型
    1. 主鍵索引 PRIMARY KEY
      它是一種特殊的唯一索引，不允許有空值。一般是在建表的時(shí)候同時(shí)創(chuàng)建主鍵索引。
      PRIMARY KEY (id)
    1. 唯一索引 UNIQUE 唯一索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一。可以在創(chuàng)建 表的時(shí)候指定，也可以修改表結(jié)構(gòu)。
      UNIQUE KEY num (number) USING BTREE
    1. 普通索引 INDEX 這是基本的索引，它沒有任何限制?？梢栽趧?chuàng)建表的時(shí)候指定，也可以修改表結(jié)構(gòu)
      KEY num (number) USING BTREE
    1. 組合索引 INDEX 索引分單列索引和組合索引(聯(lián)合索引)。單列索引，即一個(gè)索引只包含單個(gè)列，一個(gè)表可以有多個(gè) 單列索引，但這不是組合索引。組合索引，即一個(gè)索引包含多個(gè)列。
      KEY num (number,name) USING BTREE
      注意，組合索引前面索引必須要先使用，后面的索引才能使用。要么就一起用
    1. 全文索引 FULLTEXT
      SELECT * FROM tb_table WHERE number = 500000
      PRIMARY KEY (id)
      UNIQUE KEY num (number) USING BTREE
      KEY num (number) USING BTREE
      KEY num (number,name) USING BTREE
      全文索引（也稱全文檢索）是目前搜索引擎使用的一種關(guān)鍵技術(shù)。它能夠利用分詞技術(shù)等多種算法 智能分析出文本文字中關(guān)鍵字詞的頻率及重要性，然后按照一定的算法規(guī)則智能地篩選出我們想要 的搜索結(jié)果。
3.3 失效索引
  • 不要在where字句中對字段null進(jìn)行null值判斷,否則會(huì)引起引擎放棄使用索引,全表掃描
  • 避免在where字句中使用!= 或<>操作符,否則會(huì)引起引擎放棄使用索引,全表掃描
  • 避免在where字句中用or來連接條件,如果一個(gè)字段沒有索引,將導(dǎo)致放棄使用索引,全表掃描
  • 不做列運(yùn)算
  • 查詢like,如果%aaa 不會(huì)走索引

mysql的分庫分表

當(dāng)一個(gè)表數(shù)據(jù)太過龐大,可以進(jìn)行分庫分表

  • 垂直切分和水平切分
    垂直切分: 將表按功能模塊,關(guān)系密切程度劃分,部署到不同的庫
    水平 切分
使用緩存

redis和ehche
場景: 短時(shí)間內(nèi)查詢多次且數(shù)據(jù)更新不頻繁,這個(gè)時(shí)候可以優(yōu)先從緩存查詢,查詢不到在從數(shù)據(jù)庫中查詢,存入緩存.適用于單機(jī)緩存

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容