我們在面試的時候經(jīng)常被問到你如何對數(shù)據(jù)庫優(yōu)化？動不動就分庫分表，但是實際上有幾個有分庫分表的經(jīng)驗呢？下面我們將介紹優(yōu)化數(shù)據(jù)庫的各個階段。

一、SQL語句優(yōu)化

sql語句的優(yōu)化是我們優(yōu)化數(shù)據(jù)庫的第一個階段，也是要最先考慮的方案，成本最低，見效最快的方案。

1.通過慢查詢?nèi)罩?#xff0c;找到我們的慢sql

2.通過EXPLAIN分析執(zhí)行計劃，使用索引。

慢查詢?nèi)罩鹃_啟

vim /etc/my.cnf

加入如下三行：

slow_query_log=ON //開啟慢查詢

slow_query_log_file=/var/lib/mysql/slow.log //慢查詢?nèi)罩疚恢?/p>

long_query_time=3 //達到多少秒的sql就記錄日志,這里是3s

//重啟

systemctl restart mysqld;

執(zhí)行計劃分析

[圖片上傳失敗...(image-5181eb-1586497621890)]

id：值越大越先執(zhí)行，id相同，從上到下執(zhí)行

key：使用的索引，為空就是不使用

type：

? all：全表掃描

? index：索引全掃描，MySQL遍歷掙個索引來查詢匹配的行，跟all相比就差了個排序，因為索引本來就是有序的

? range：索引范圍掃描，常見于、>=、between等操作符

? ref：使用非唯一索引或唯一索引的前綴掃描，返回匹配的單行數(shù)據(jù)，這個就是我們平時理解的索引查詢方式B+樹二分法查詢

? eq_ref：類似ref，區(qū)別就在于使用的索引是唯一索引，簡單來說，就是多表連接中使用primary key或者unique index作為關(guān)聯(lián)條件。

? const/system：單表中最多有一個匹配行，查詢起來非常迅速，常見于根據(jù)primary key或者唯一索引unique index進行的單表查詢

? null：mysql不用訪問表或者索引，直接就能夠得到查詢的結(jié)果，例如select 1+2 as result。

Extra：執(zhí)行情況的說明和描述，包含不適合在其他列中顯示但是對執(zhí)行計劃非常重要的額外信息，常用取值如下：

? Using index：直接訪問索引就取到了數(shù)據(jù)，高性能的表現(xiàn)。

? Using where：直接在主鍵索引上過濾數(shù)據(jù)，必帶where子句，而且用不上索引

? Using index condition：先條件過濾索引，再查數(shù)據(jù)，

? Using filesort：使用了外部文件排序 只要見到這個 就要優(yōu)化掉

? Using temporary：創(chuàng)建了臨時表來處理查詢 只要見到這個 也要盡量優(yōu)化掉

SQL執(zhí)行順序

[圖片上傳失敗...(image-eec99-1586497621891)]

不是絕對的有時候，優(yōu)化器也會執(zhí)行where過濾些數(shù)據(jù)在join

優(yōu)化爭議無數(shù)的count()

count(1)、count()、count(列)在innodb引擎中

? count(1)和count()直接就是統(tǒng)計主鍵，他們兩個的效率是一樣的。如果刪除主鍵，他們都走全表掃描。

? 如果count(列)中的字段是索引的話，count(列)和count()一樣快，否則count(列)走全表掃描。

MyiSAM引擎的count(*)，因為MyiSAM有記錄當前的總行數(shù)，所以直接取該值就行，快得一逼，但是這個要在沒有where條件的情況下，當統(tǒng)計帶有where條件的查詢，那么mysql的count()和其他存儲引擎就沒有什么不同了

優(yōu)化filesort

當我們使用order by進行排序的時候可能會出現(xiàn)Using filesort，這個時候我們就要將這個優(yōu)化掉

mysql排序方式有2種

? 直接通過有序索引返回數(shù)據(jù)，這種方式的extra顯示為Using Index,不需要額外的排序，操作效率較高。

? 對返回的數(shù)據(jù)進行排序，也就是通常看到的Using filesort，filesort是通過相應的排序算法，將數(shù)據(jù)放在sort_buffer_size系統(tǒng)變量設(shè)置的內(nèi)存排序區(qū)中進行排序，如果內(nèi)存裝載不下，它就會將磁盤上的數(shù)據(jù)進行分塊，再對各個數(shù)據(jù)塊進行排序，然后將各個塊合并成有序的結(jié)果集。

SELECT * FROM DB.TB WHERE ID=2222 AND FID IN (9,8,3,13,38,40) ORDER BY INVERSE_DATE LIMIT 0, 5

建立一個索引 IDX(ID,FID ,INVERSE_DATE)這個時候就會出現(xiàn)Using where; Using filesort。

因為建立索引的時候是id排序后，id相同再排FID，當FID有序后，當FID相同在排INVERSE_DATE。

這里id是固定，所以我們重新建立一個索引(ID,INVERSE_DATE)，這樣就不會出現(xiàn)Using filesort。

優(yōu)化limit 分頁

select * from product limit 10, 20 0.016秒

select * from product limit 100, 20 0.016秒

select * from product limit 1000, 20 0.047秒

select * from product limit 10000, 20 0.094秒

select * from product limit 400000, 20 3.229秒

可以看到隨著條數(shù)的增加，時間增長

一般優(yōu)化這個有兩種

SELECT * FROM product WHERE ID > =(select id from product limit 866613, 1) limit 20 0.2秒

SELECT * FROM product a JOIN (select id from product limit 866613, 20) b ON a.ID = b.id

加一個參數(shù)來輔助，標記分頁的開始位置：可以是上一次分頁最大時間等，這里用id

SELECT * FROM product WHERE id > 800000 LIMIT 20

帶有where的語句

select id from collect where vtype=1 limit 1000,10;

索引應該這樣建立(vtype，id)，不要建成(id，vtype)

優(yōu)化子查詢

常見的優(yōu)化方式

? join的時候使用小表作為主表，驅(qū)動表。

select * from a join b on a.id=b.aid where a.create_time>xxx and b.create_time>xxxx

當a根據(jù)創(chuàng)建時間過濾后的條數(shù)和b根據(jù)過濾時間的條數(shù)，做比較。。不是直接a，b表做比較

? 不要在列上做運算where a-10 = 20 這樣不使用索引，換成 where a=20+10

? 類型要一樣 where a=123 如果a是varchar類型，這樣就不會使用索引 換成 where a=‘123’

? IN適合于外表大而內(nèi)表小的情況；EXISTS適合于外表小而內(nèi)表大的情況，這樣效率會高的

? 能夠用BETWEEN的就不要用IN

? 能夠用DISTINCT的就不用GROUP BY

? 避免使用!=或＜＞、IS NULL或IS NOT NULL、IN ，NOT IN等這樣的操作符.

? select 列，使用覆蓋索引，減少回表查詢.

一張表最多只存多少數(shù)據(jù)，為什么使用B+樹，不使用B樹

深入理解mysql B+樹

優(yōu)化器選擇不使用索引

SELECT creator_name,run_time FROM oa_crm_log ORDER BY creator_name limit 10

[圖片上傳失敗...(image-292c4a-1586497621891)]

SELECT creator_name,run_time FROM oa_crm_log ORDER BY creator_name limit 10000

[圖片上傳失敗...(image-7e0c4-1586497621891)]

第一條使用了索引，第二條沒有使用索引。為什么呢？

這第二條是因為我們這個是非聚集索引，掃描完索引之后還需要，根據(jù)id去隨機讀取磁盤(10000次)

而隨機讀取的性能是很差的。所以sql優(yōu)化器判斷之后使用全表掃描(順序讀取磁盤性能還是高的)

第一條雖然也是這樣，但是只需要查詢10條隨機讀取磁盤的次數(shù)(10次)，相對比較少，所以sql優(yōu)化器判斷之后使用了索引

優(yōu)化：我們可以使用覆蓋索引，讓我們b+樹的索引存儲了索引key，這樣我們就不用在回表去查詢了

建立(creator_name，run_time)的聯(lián)合索引

滿足了使用索引的原則，mysql還是可能會棄用索引，因為有些查詢即使使用索引，也會出現(xiàn)大量的隨機io，相對于從數(shù)據(jù)記錄中的順序io開銷更大。

join原理 NLJ、BNL、MRR、BKA

顛覆最左原則

t_article表 索引 idnex001(creator_id,updator_id,upator)

select * from t_article where updator_id = 1

select updator_id from t_article where updator_id = 1

這兩條sql會使用索引嗎？根據(jù)我們理解的mysql最左原則，兩條sql都不會使用索引。但是事實卻不是。

第一條，不是使用索引

[圖片上傳失敗...(image-3242e0-1586497621891)]

第二條使用type 為index的索引

[圖片上傳失敗...(image-5d10b6-1586497621891)]

index：這種類型表示是mysql會對整個該索引進行掃描。要想用到這種類型的索引，對這個索引并無特別要求，只要是索引，或者某個復合索引的一部分，mysql都可能會采用index類型的方式掃描。但是呢，缺點是效率不高，mysql會從索引中的第一個數(shù)據(jù)一個個的查找到最后一個數(shù)據(jù)，直到找到符合判斷條件的某個索引。

所以上面兩條都滿足使用index的原則。

第一條沒有使用索引是因為我們查詢select * 的話，輔助索引還需要到主鍵索引進行隨機查詢。。優(yōu)化器認為順序掃描更優(yōu)，所以沒有使用索引

第二條就不需要在要主鍵索引進行隨機查詢，所以使用了index類型的索引。

mysql 聚簇與非聚簇索引

二、引入緩存

在sql優(yōu)化搞不定的時候，我們才需要考慮引入緩存，但我們要知道當引入緩存的時候系統(tǒng)的復雜性增加了，同時也會引入很多問題，比如數(shù)據(jù)庫和緩存一致性問題等等。

這里很多問題都寫過了。。參照下面各個鏈接

mybatis的二級緩存、ehcache本地緩存

這個比較簡單省略

redis的分布式緩存

Redis安裝及持久化

數(shù)據(jù)庫和緩存不一致的方案

刪除緩存還是更新緩存

先操作緩存(刪除緩存)還是數(shù)據(jù)庫

緩存穿透、擊穿、雪崩

緩存重建沖突(分布式鎖)、使用雙層nginx提高緩存命中

三、讀寫分離

數(shù)據(jù)庫主從不一致

從庫和緩存不一致(雙淘汰方案)

四、分區(qū)表

五、垂直拆分

六、水平拆分

總結(jié)

以上是生活随笔為你收集整理的对mysql优化关注_MySQL优化看这篇就对了的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。