MySQL數(shù)據(jù)庫基本的三個優(yōu)化法則是什么?

（1）系統(tǒng)服務(wù)優(yōu)化，把MySQL的key_buffer、cache_buffer、query_cache等增加容量

在潼關(guān)等地區(qū)，都構(gòu)建了全面的區(qū)域性戰(zhàn)略布局，加強(qiáng)發(fā)展的系統(tǒng)性、市場前瞻性、產(chǎn)品創(chuàng)新能力，以專注、極致的服務(wù)理念，為客戶提供做網(wǎng)站、網(wǎng)站制作 網(wǎng)站設(shè)計制作按需求定制制作,公司網(wǎng)站建設(shè),企業(yè)網(wǎng)站建設(shè),成都品牌網(wǎng)站建設(shè),網(wǎng)絡(luò)營銷推廣,成都外貿(mào)網(wǎng)站制作,潼關(guān)網(wǎng)站建設(shè)費(fèi)用合理。

（2）給所有經(jīng)常查詢的字段增加適當(dāng)?shù)乃饕?/p>

（3）優(yōu)化SQL語句，減少Ditinct、Group、Join等等語句的操作

mysql---索引優(yōu)化

索引就是為特定的mysql字段進(jìn)行一些特定的算法排序，比如二叉樹的算法和哈希算法，哈希算法是通過建立特征值，然后根據(jù)特征值來快速查找。

1.普通索引:(index)最基本的索引，沒有任何限制? 目的:加快數(shù)據(jù)的查詢速度

2.唯一索引:(unique)? 與"普通索引"類似，不同的就是：索引列的值必須唯一，但允許有空值。

3.主鍵索引(primary key) 它 是一種特殊的唯一索引，不允許有空值。

4.復(fù)合索引:index(a,b,c)? 為了更多的提高mysql效率可建立組合索引，遵循”最左前綴“原則。

5.全文索引:fulltext? 僅可用于 MyISAM 表，針對較大的數(shù)據(jù)，生成全文索引很耗時耗空間。

第一類是myisam存儲引擎使用的叫做b-tree結(jié)構(gòu)，

第二類是innodb存儲引擎使用的叫做聚簇結(jié)構(gòu)（也是一種 b-tree）。 如下圖：

注意：

1.myisam不需要回行處理?

2.innodb不需要回行處理,直接可以獲取數(shù)據(jù),因為innodb的儲存引擎是包含了數(shù)據(jù)和索引文件的,其主鍵索引包含了數(shù)據(jù),(唯一索引及普通索是沒有直接包含數(shù)據(jù)的)

1、索引列不能參與計算

有索引列參與計算的查詢條件對索引不友好（甚至無法使用索引），如from_unixtime(create_time) = '2014-05-29'。

原因很簡單，如何在節(jié)點(diǎn)中查找到對應(yīng)key？如果線性掃描，則每次都需要重新計算，成本太高；如果二分查找，則需要針對from_unixtime方法確定大小關(guān)系。

因此，索引列不能參與計算。上述from_unixtime(create_time) = '2014-05-29'語句應(yīng)該寫成create_time = unix_timestamp('2014-05-29')。

2、最左前綴匹配

如有索引(a, b, c, d)，查詢條件a = 1 and b = 2 and c 3 and d = 4，則會在每個節(jié)點(diǎn)依次命中a、b、c，無法命中d。也就是最左前綴匹配原則。

3、冗余和重復(fù)索引

冗余索引是指在相同的列上按照相同的順序創(chuàng)建的相同類型的索引，應(yīng)當(dāng)盡量避免這種索引，發(fā)現(xiàn)后立即刪除。比如有一個索引(A,B)，再創(chuàng)建索引(A)就是冗余索引。冗余索引經(jīng)常發(fā)生在為表添加新索引時，比如有人新建了索引(A,B)，但這個索引不是擴(kuò)展已有的索引(A)

4、避免多個范圍條件

? ? select user.* from user where login_time '2017-04-01' and age between 18 and 30;

比如想查詢某個時間段內(nèi)登錄過的用戶：它有兩個范圍條件，login_time列和age列，MySQL可以使用login_time列的索引或者age列的索引，但無法同時使用它們 .

5、覆蓋索引 (能擴(kuò)展就不新建)

如果一個索引包含或者說覆蓋所有需要查詢的字段的值，那么就沒有必要再回表查詢，這就稱為覆蓋索引。覆蓋索引是非常有用的工具，可以極大的提高性能，因為查詢只需要掃描索引會帶來許多好處：

1.索引條目遠(yuǎn)小于數(shù)據(jù)行大小，如果只讀取索引，極大減少數(shù)據(jù)訪問量2.索引是有按照列值順序存儲的，對于I/O密集型的范圍查詢要比隨機(jī)從磁盤讀取每一行數(shù)據(jù)的IO要少的多

6、選擇區(qū)分度高的列作索引

如，用性別作索引，那么索引僅能將1000w行數(shù)據(jù)劃分為兩部分（如500w男，500w女），索引幾乎無效。

區(qū)分度的公式是count(distinct ) / count(*)，表示字段不重復(fù)的比例，比例越大區(qū)分度越好。唯一鍵的區(qū)分度是1，而一些狀態(tài)、性別字段可能在大數(shù)據(jù)面前的區(qū)分度趨近于0。

7、刪除長期未使用的索引

場景一(覆蓋索引 5)

索引應(yīng)該建在選擇性高的字段上（鍵值唯一的記錄數(shù)/總記錄條數(shù)），選擇性越高索引的效果越好、價值越大，唯一索引的選擇性最高；

組合索引中字段的順序，選擇性越高的字段排在最前面；

where條件中包含兩個選擇性高的字段時，可以考慮分別創(chuàng)建索引，引擎會同時使用兩個索引（在OR條件下，應(yīng)該說必須分開建索引）；

不要重復(fù)創(chuàng)建彼此有包含關(guān)系的索引，如index1(a,b,c) 、index2(a,b)、index3(a)；

組合索引的字段不要過多，如果超過4個字段，一般需要考慮拆分成多個單列索引或更為簡單的組合索引；

不要濫用索引。因為過多的索引不僅僅會增加物理存儲的開銷，對于插入、刪除、更新操作也會增加處理上的開銷，而且會增加優(yōu)化器在選擇索引時的計算代價。

因此太多的索引與不充分、不正確的索引對性能都是毫無益處的。一言以蔽之，索引的建立必須慎重，對每個索引的必要性都應(yīng)該經(jīng)過仔細(xì)分析，要有建立的依據(jù)。

超詳細(xì)MySQL數(shù)據(jù)庫優(yōu)化

數(shù)據(jù)庫優(yōu)化一方面是找出系統(tǒng)的瓶頸,提高M(jìn)ySQL數(shù)據(jù)庫的整體性能,而另一方面需要合理的結(jié)構(gòu)設(shè)計和參數(shù)調(diào)整,以提高用戶的相應(yīng)速度,同時還要盡可能的節(jié)約系統(tǒng)資源,以便讓系統(tǒng)提供更大的負(fù)荷.

1. 優(yōu)化一覽圖

2. 優(yōu)化

筆者將優(yōu)化分為了兩大類,軟優(yōu)化和硬優(yōu)化,軟優(yōu)化一般是操作數(shù)據(jù)庫即可,而硬優(yōu)化則是操作服務(wù)器硬件及參數(shù)設(shè)置.

2.1 軟優(yōu)化

2.1.1 查詢語句優(yōu)化

1.首先我們可以用EXPLAIN或DESCRIBE(簡寫:DESC)命令分析一條查詢語句的執(zhí)行信息.

2.例:

顯示:

其中會顯示索引和查詢數(shù)據(jù)讀取數(shù)據(jù)條數(shù)等信息.

2.1.2 優(yōu)化子查詢

在MySQL中,盡量使用JOIN來代替子查詢.因為子查詢需要嵌套查詢,嵌套查詢時會建立一張臨時表,臨時表的建立和刪除都會有較大的系統(tǒng)開銷,而連接查詢不會創(chuàng)建臨時表,因此效率比嵌套子查詢高.

2.1.3 使用索引

索引是提高數(shù)據(jù)庫查詢速度最重要的方法之一,關(guān)于索引可以參高筆者M(jìn)ySQL數(shù)據(jù)庫索引一文,介紹比較詳細(xì),此處記錄使用索引的三大注意事項:

2.1.4 分解表

對于字段較多的表,如果某些字段使用頻率較低,此時應(yīng)當(dāng),將其分離出來從而形成新的表,

2.1.5 中間表

對于將大量連接查詢的表可以創(chuàng)建中間表,從而減少在查詢時造成的連接耗時.

2.1.6 增加冗余字段

類似于創(chuàng)建中間表,增加冗余也是為了減少連接查詢.

2.1.7 分析表,,檢查表,優(yōu)化表

分析表主要是分析表中關(guān)鍵字的分布,檢查表主要是檢查表中是否存在錯誤,優(yōu)化表主要是消除刪除或更新造成的表空間浪費(fèi).

1. 分析表: 使用 ANALYZE 關(guān)鍵字,如ANALYZE TABLE user;

2. 檢查表: 使用 CHECK關(guān)鍵字,如CHECK TABLE user [option]

option 只對MyISAM有效,共五個參數(shù)值:

3. 優(yōu)化表:使用OPTIMIZE關(guān)鍵字,如OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE user;

LOCAL|NO_WRITE_TO_BINLOG都是表示不寫入日志.,優(yōu)化表只對VARCHAR,BLOB和TEXT有效,通過OPTIMIZE TABLE語句可以消除文件碎片,在執(zhí)行過程中會加上只讀鎖.

2.2 硬優(yōu)化

2.2.1 硬件三件套

1.配置多核心和頻率高的cpu,多核心可以執(zhí)行多個線程.

2.配置大內(nèi)存,提高內(nèi)存,即可提高緩存區(qū)容量,因此能減少磁盤I/O時間,從而提高響應(yīng)速度.

3.配置高速磁盤或合理分布磁盤:高速磁盤提高I/O,分布磁盤能提高并行操作的能力.

2.2.2 優(yōu)化數(shù)據(jù)庫參數(shù)

優(yōu)化數(shù)據(jù)庫參數(shù)可以提高資源利用率,從而提高M(jìn)ySQL服務(wù)器性能.MySQL服務(wù)的配置參數(shù)都在my.cnf或my.ini,下面列出性能影響較大的幾個參數(shù).

2.2.3 分庫分表

因為數(shù)據(jù)庫壓力過大，首先一個問題就是高峰期系統(tǒng)性能可能會降低，因為數(shù)據(jù)庫負(fù)載過高對性能會有影響。另外一個，壓力過大把你的數(shù)據(jù)庫給搞掛了怎么辦？所以此時你必須得對系統(tǒng)做分庫分表 + 讀寫分離，也就是把一個庫拆分為多個庫，部署在多個數(shù)據(jù)庫服務(wù)上，這時作為主庫承載寫入請求。然后每個主庫都掛載至少一個從庫，由從庫來承載讀請求。

2.2.4 緩存集群

如果用戶量越來越大，此時你可以不停的加機(jī)器，比如說系統(tǒng)層面不停加機(jī)器，就可以承載更高的并發(fā)請求。然后數(shù)據(jù)庫層面如果寫入并發(fā)越來越高，就擴(kuò)容加數(shù)據(jù)庫服務(wù)器，通過分庫分表是可以支持?jǐn)U容機(jī)器的，如果數(shù)據(jù)庫層面的讀并發(fā)越來越高，就擴(kuò)容加更多的從庫。但是這里有一個很大的問題：數(shù)據(jù)庫其實本身不是用來承載高并發(fā)請求的，所以通常來說，數(shù)據(jù)庫單機(jī)每秒承載的并發(fā)就在幾千的數(shù)量級，而且數(shù)據(jù)庫使用的機(jī)器都是比較高配置，比較昂貴的機(jī)器，成本很高。如果你就是簡單的不停的加機(jī)器，其實是不對的。所以在高并發(fā)架構(gòu)里通常都有緩存這個環(huán)節(jié)，緩存系統(tǒng)的設(shè)計就是為了承載高并發(fā)而生。所以單機(jī)承載的并發(fā)量都在每秒幾萬，甚至每秒數(shù)十萬，對高并發(fā)的承載能力比數(shù)據(jù)庫系統(tǒng)要高出一到兩個數(shù)量級。所以你完全可以根據(jù)系統(tǒng)的業(yè)務(wù)特性，對那種寫少讀多的請求，引入緩存集群。具體來說，就是在寫數(shù)據(jù)庫的時候同時寫一份數(shù)據(jù)到緩存集群里，然后用緩存集群來承載大部分的讀請求。這樣的話，通過緩存集群，就可以用更少的機(jī)器資源承載更高的并發(fā)。

一個完整而復(fù)雜的高并發(fā)系統(tǒng)架構(gòu)中，一定會包含：各種復(fù)雜的自研基礎(chǔ)架構(gòu)系統(tǒng)。各種精妙的架構(gòu)設(shè)計.因此一篇小文頂多具有拋磚引玉的效果,但是數(shù)據(jù)庫優(yōu)化的思想差不多就這些了.