中文字幕日韩精品一区二区免费_精品一区二区三区国产精品无卡在_国精品无码专区一区二区三区_国产αv三级中文在线

mysql怎么優(yōu)化原理 mysql優(yōu)化原則

數(shù)據(jù)庫如何優(yōu)化

body{

成都創(chuàng)新互聯(lián)公司是一家專業(yè)提供廈門企業(yè)網(wǎng)站建設(shè),專注與網(wǎng)站設(shè)計制作、網(wǎng)站制作、HTML5、小程序制作等業(yè)務(wù)。10年已為廈門眾多企業(yè)、政府機構(gòu)等服務(wù)。創(chuàng)新互聯(lián)專業(yè)網(wǎng)站設(shè)計公司優(yōu)惠進行中。

line-height:200%;

}

如何優(yōu)化MySQL數(shù)據(jù)庫

當MySQL數(shù)據(jù)庫邂逅優(yōu)化,它有好幾個意思,今天我們所指的是性能優(yōu)化。

我們究竟該如何對MySQL數(shù)據(jù)庫進行優(yōu)化呢?下面我就從MySQL對硬件的選擇、Mysql的安裝、my.cnf的優(yōu)化、MySQL如何進行架構(gòu)設(shè)計及數(shù)據(jù)切分等方面來說明這個問題。

1.服務(wù)器物理硬件的優(yōu)化

1)磁盤(I/O),MySQL每一秒鐘都在進行大量、復雜的查詢操作,對磁盤的讀寫量可想而知,所以推薦使用RAID1+0磁盤陣列,如果資金允許,可以選擇固態(tài)硬盤做RAID1+0;

2)cpu對Mysql的影響也是不容忽視的,建議選擇運算能力強悍的CPU。

2.MySQL應該采用編譯安裝的方式

MySQL數(shù)據(jù)庫的線上環(huán)境安裝,我建議采取編譯安裝,這樣性能會較大的提升。

3.MySQL配置文件的優(yōu)化

1)skip

-name

-resolve,禁止MySQL對外部連接進行DNS解析,使用這一選項可以消除MySQL進行DNS解析的時間;

2)back_log

=

384,back_log指出在MySQL暫時停止響應新請求之前,短時間內(nèi)的多少個請求可以被存在堆棧中,對于Linux系統(tǒng)而言,推薦設(shè)置小于512的整數(shù)。

3)如果key_reads太大,則應該把my.cnf中key_buffer_size變大,保持key_reads/key_read_requests至少在1/100以上,越小越好。

4.MySQL上線后根據(jù)status狀態(tài)進行適當優(yōu)化

1)打開慢查詢?nèi)罩究赡軙ο到y(tǒng)性能有一點點影響,如果你的MySQL是主-從結(jié)構(gòu),可以考慮打開其中一臺從服務(wù)器的慢查詢?nèi)罩?,這樣既可以監(jiān)控慢查詢,對系統(tǒng)性能影響也會很小。

2)MySQL服務(wù)器過去的最大連接數(shù)是245,沒有達到服務(wù)器連接數(shù)的上限256,應該不會出現(xiàn)1040錯誤。比較理想的設(shè)置是:Max_used_connections/max_connections

*

100%

=85%

5.MySQL數(shù)據(jù)庫的可擴展架構(gòu)方案

1)MySQL

cluster,其特點為可用性非常高,性能非常好,但它的維護非常復雜,存在部分Bug;

2)DRBD磁盤網(wǎng)絡(luò)鏡像方案,其特點為軟件功能強大,數(shù)據(jù)可在底層塊設(shè)備級別跨物理主機鏡像,且可根據(jù)性能和可靠性要求配置不同級別的同步。

mysql數(shù)據(jù)庫怎么優(yōu)化,有幾方面的優(yōu)化

在開始演示之前,我們先介紹下兩個概念。

概念一,數(shù)據(jù)的可選擇性基數(shù),也就是常說的cardinality值。

查詢優(yōu)化器在生成各種執(zhí)行計劃之前,得先從統(tǒng)計信息中取得相關(guān)數(shù)據(jù),這樣才能估算每步操作所涉及到的記錄數(shù),而這個相關(guān)數(shù)據(jù)就是cardinality。簡單來說,就是每個值在每個字段中的唯一值分布狀態(tài)。

比如表t1有100行記錄,其中一列為f1。f1中唯一值的個數(shù)可以是100個,也可以是1個,當然也可以是1到100之間的任何一個數(shù)字。這里唯一值越的多少,就是這個列的可選擇基數(shù)。

那看到這里我們就明白了,為什么要在基數(shù)高的字段上建立索引,而基數(shù)低的的字段建立索引反而沒有全表掃描來的快。當然這個只是一方面,至于更深入的探討就不在我這篇探討的范圍了。

概念二,關(guān)于HINT的使用。

這里我來說下HINT是什么,在什么時候用。

HINT簡單來說就是在某些特定的場景下人工協(xié)助MySQL優(yōu)化器的工作,使她生成最優(yōu)的執(zhí)行計劃。一般來說,優(yōu)化器的執(zhí)行計劃都是最優(yōu)化的,不過在某些特定場景下,執(zhí)行計劃可能不是最優(yōu)化。

比如:表t1經(jīng)過大量的頻繁更新操作,(UPDATE,DELETE,INSERT),cardinality已經(jīng)很不準確了,這時候剛好執(zhí)行了一條SQL,那么有可能這條SQL的執(zhí)行計劃就不是最優(yōu)的。為什么說有可能呢?

來看下具體演示

譬如,以下兩條SQL,

A:

select * from t1 where f1 = 20;

B:

select * from t1 where f1 = 30;

如果f1的值剛好頻繁更新的值為30,并且沒有達到MySQL自動更新cardinality值的臨界值或者說用戶設(shè)置了手動更新又或者用戶減少了sample page等等,那么對這兩條語句來說,可能不準確的就是B了。

這里順帶說下,MySQL提供了自動更新和手動更新表cardinality值的方法,因篇幅有限,需要的可以查閱手冊。

那回到正題上,MySQL 8.0 帶來了幾個HINT,我今天就舉個index_merge的例子。

示例表結(jié)構(gòu):

mysql desc t1;+------------+--------------+------+-----+---------+----------------+| Field ? ? ?| Type ? ? ? ? | Null | Key | Default | Extra ? ? ? ? ?|+------------+--------------+------+-----+---------+----------------+| id ? ? ? ? | int(11) ? ? ?| NO ? | PRI | NULL ? ?| auto_increment || rank1 ? ? ?| int(11) ? ? ?| YES ?| MUL | NULL ? ?| ? ? ? ? ? ? ? ?|| rank2 ? ? ?| int(11) ? ? ?| YES ?| MUL | NULL ? ?| ? ? ? ? ? ? ? ?|| log_time ? | datetime ? ? | YES ?| MUL | NULL ? ?| ? ? ? ? ? ? ? ?|| prefix_uid | varchar(100) | YES ?| ? ? | NULL ? ?| ? ? ? ? ? ? ? ?|| desc1 ? ? ?| text ? ? ? ? | YES ?| ? ? | NULL ? ?| ? ? ? ? ? ? ? ?|| rank3 ? ? ?| int(11) ? ? ?| YES ?| MUL | NULL ? ?| ? ? ? ? ? ? ? ?|+------------+--------------+------+-----+---------+----------------+7 rows in set (0.00 sec)

表記錄數(shù):

mysql select count(*) from t1;+----------+| count(*) |+----------+| ? ?32768 |+----------+1 row in set (0.01 sec)

這里我們兩條經(jīng)典的SQL:

SQL C:

select * from t1 where rank1 = 1 or rank2 = 2 or rank3 = 2;

SQL D:

select * from t1 where rank1 =100 ?and rank2 =100 ?and rank3 =100;

表t1實際上在rank1,rank2,rank3三列上分別有一個二級索引。

那我們來看SQL C的查詢計劃。

顯然,沒有用到任何索引,掃描的行數(shù)為32034,cost為3243.65。

mysql explain ?format=json select * from t1 ?where rank1 =1 or rank2 = 2 or rank3 = 2\G*************************** 1. row ***************************EXPLAIN: { ?"query_block": { ? ?"select_id": 1, ? ?"cost_info": { ? ? ?"query_cost": "3243.65" ? ?}, ? ?"table": { ? ? ?"table_name": "t1", ? ? ?"access_type": "ALL", ? ? ?"possible_keys": [ ? ? ? ?"idx_rank1", ? ? ? ?"idx_rank2", ? ? ? ?"idx_rank3" ? ? ?], ? ? ?"rows_examined_per_scan": 32034, ? ? ?"rows_produced_per_join": 115, ? ? ?"filtered": "0.36", ? ? ?"cost_info": { ? ? ? ?"read_cost": "3232.07", ? ? ? ?"eval_cost": "11.58", ? ? ? ?"prefix_cost": "3243.65", ? ? ? ?"data_read_per_join": "49K" ? ? ?}, ? ? ?"used_columns": [ ? ? ? ?"id", ? ? ? ?"rank1", ? ? ? ?"rank2", ? ? ? ?"log_time", ? ? ? ?"prefix_uid", ? ? ? ?"desc1", ? ? ? ?"rank3" ? ? ?], ? ? ?"attached_condition": "((`ytt`.`t1`.`rank1` = 1) or (`ytt`.`t1`.`rank2` = 2) or (`ytt`.`t1`.`rank3` = 2))" ? ?} ?}}1 row in set, 1 warning (0.00 sec)

我們加上hint給相同的查詢,再次看看查詢計劃。

這個時候用到了index_merge,union了三個列。掃描的行數(shù)為1103,cost為441.09,明顯比之前的快了好幾倍。

mysql explain ?format=json select /*+ index_merge(t1) */ * from t1 ?where rank1 =1 or rank2 = 2 or rank3 = 2\G*************************** 1. row ***************************EXPLAIN: { ?"query_block": { ? ?"select_id": 1, ? ?"cost_info": { ? ? ?"query_cost": "441.09" ? ?}, ? ?"table": { ? ? ?"table_name": "t1", ? ? ?"access_type": "index_merge", ? ? ?"possible_keys": [ ? ? ? ?"idx_rank1", ? ? ? ?"idx_rank2", ? ? ? ?"idx_rank3" ? ? ?], ? ? ?"key": "union(idx_rank1,idx_rank2,idx_rank3)", ? ? ?"key_length": "5,5,5", ? ? ?"rows_examined_per_scan": 1103, ? ? ?"rows_produced_per_join": 1103, ? ? ?"filtered": "100.00", ? ? ?"cost_info": { ? ? ? ?"read_cost": "330.79", ? ? ? ?"eval_cost": "110.30", ? ? ? ?"prefix_cost": "441.09", ? ? ? ?"data_read_per_join": "473K" ? ? ?}, ? ? ?"used_columns": [ ? ? ? ?"id", ? ? ? ?"rank1", ? ? ? ?"rank2", ? ? ? ?"log_time", ? ? ? ?"prefix_uid", ? ? ? ?"desc1", ? ? ? ?"rank3" ? ? ?], ? ? ?"attached_condition": "((`ytt`.`t1`.`rank1` = 1) or (`ytt`.`t1`.`rank2` = 2) or (`ytt`.`t1`.`rank3` = 2))" ? ?} ?}}1 row in set, 1 warning (0.00 sec)

我們再看下SQL D的計劃:

不加HINT,

mysql explain format=json select * from t1 where rank1 =100 and rank2 =100 and rank3 =100\G*************************** 1. row ***************************EXPLAIN: { ?"query_block": { ? ?"select_id": 1, ? ?"cost_info": { ? ? ?"query_cost": "534.34" ? ?}, ? ?"table": { ? ? ?"table_name": "t1", ? ? ?"access_type": "ref", ? ? ?"possible_keys": [ ? ? ? ?"idx_rank1", ? ? ? ?"idx_rank2", ? ? ? ?"idx_rank3" ? ? ?], ? ? ?"key": "idx_rank1", ? ? ?"used_key_parts": [ ? ? ? ?"rank1" ? ? ?], ? ? ?"key_length": "5", ? ? ?"ref": [ ? ? ? ?"const" ? ? ?], ? ? ?"rows_examined_per_scan": 555, ? ? ?"rows_produced_per_join": 0, ? ? ?"filtered": "0.07", ? ? ?"cost_info": { ? ? ? ?"read_cost": "478.84", ? ? ? ?"eval_cost": "0.04", ? ? ? ?"prefix_cost": "534.34", ? ? ? ?"data_read_per_join": "176" ? ? ?}, ? ? ?"used_columns": [ ? ? ? ?"id", ? ? ? ?"rank1", ? ? ? ?"rank2", ? ? ? ?"log_time", ? ? ? ?"prefix_uid", ? ? ? ?"desc1", ? ? ? ?"rank3" ? ? ?], ? ? ?"attached_condition": "((`ytt`.`t1`.`rank3` = 100) and (`ytt`.`t1`.`rank2` = 100))" ? ?} ?}}1 row in set, 1 warning (0.00 sec)

加了HINT,

mysql explain format=json select /*+ index_merge(t1)*/ * from t1 where rank1 =100 and rank2 =100 and rank3 =100\G*************************** 1. row ***************************EXPLAIN: { ?"query_block": { ? ?"select_id": 1, ? ?"cost_info": { ? ? ?"query_cost": "5.23" ? ?}, ? ?"table": { ? ? ?"table_name": "t1", ? ? ?"access_type": "index_merge", ? ? ?"possible_keys": [ ? ? ? ?"idx_rank1", ? ? ? ?"idx_rank2", ? ? ? ?"idx_rank3" ? ? ?], ? ? ?"key": "intersect(idx_rank1,idx_rank2,idx_rank3)", ? ? ?"key_length": "5,5,5", ? ? ?"rows_examined_per_scan": 1, ? ? ?"rows_produced_per_join": 1, ? ? ?"filtered": "100.00", ? ? ?"cost_info": { ? ? ? ?"read_cost": "5.13", ? ? ? ?"eval_cost": "0.10", ? ? ? ?"prefix_cost": "5.23", ? ? ? ?"data_read_per_join": "440" ? ? ?}, ? ? ?"used_columns": [ ? ? ? ?"id", ? ? ? ?"rank1", ? ? ? ?"rank2", ? ? ? ?"log_time", ? ? ? ?"prefix_uid", ? ? ? ?"desc1", ? ? ? ?"rank3" ? ? ?], ? ? ?"attached_condition": "((`ytt`.`t1`.`rank3` = 100) and (`ytt`.`t1`.`rank2` = 100) and (`ytt`.`t1`.`rank1` = 100))" ? ?} ?}}1 row in set, 1 warning (0.00 sec)

對比下以上兩個,加了HINT的比不加HINT的cost小了100倍。

總結(jié)下,就是說表的cardinality值影響這張的查詢計劃,如果這個值沒有正常更新的話,就需要手工加HINT了。相信MySQL未來的版本會帶來更多的HINT。

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

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

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)于索引可以參高筆者MySQL數(shù)據(jù)庫索引一文,介紹比較詳細,此處記錄使用索引的三大注意事項:

2.1.4 分解表

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

2.1.5 中間表

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

2.1.6 增加冗余字段

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

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

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

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時間,從而提高響應速度.

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

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

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

2.2.3 分庫分表

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

2.2.4 緩存集群

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

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

本文名稱:mysql怎么優(yōu)化原理 mysql優(yōu)化原則
轉(zhuǎn)載來于:http://m.rwnh.cn/article18/dopphgp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供、品牌網(wǎng)站建設(shè)網(wǎng)站設(shè)計、全網(wǎng)營銷推廣動態(tài)網(wǎng)站、品牌網(wǎng)站設(shè)計

廣告

聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請盡快告知,我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場,如需處理請聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時需注明來源: 創(chuàng)新互聯(lián)

手機網(wǎng)站建設(shè)
库伦旗| 安仁县| 灯塔市| 邵东县| 台湾省| 武鸣县| 吉木乃县| 通道| 徐州市| 临汾市| 天峻县| 竹山县| 长子县| 凌海市| 伊吾县| 洪雅县| 肇州县| 安平县| 临泽县| 辽源市| 灵武市| 炎陵县| 当涂县| 黄骅市| 福安市| 体育| 呈贡县| 盐亭县| 银川市| 石景山区| 新巴尔虎右旗| 新郑市| 新郑市| 嵩明县| 东安县| 华宁县| 来凤县| 绵竹市| 柯坪县| 关岭| 靖州|