linux 下怎么優(yōu)化mysql占用內(nèi)存？

修改mysql配置文件，優(yōu)化緩存大小和連接數(shù)連接方式，優(yōu)化sql語句 ,記得mysql好像是有工具可以查看最占用資源的sql語句，找到他，優(yōu)化他。

創(chuàng)新互聯(lián)為客戶提供專業(yè)的網(wǎng)站設(shè)計制作、成都做網(wǎng)站、程序、域名、空間一條龍服務(wù)，提供基于WEB的系統(tǒng)開發(fā). 服務(wù)項目涵蓋了網(wǎng)頁設(shè)計、網(wǎng)站程序開發(fā)、WEB系統(tǒng)開發(fā)、微信二次開發(fā)、成都手機(jī)網(wǎng)站制作等網(wǎng)站方面業(yè)務(wù)。

安裝好mysql后，配制文件應(yīng)該在/usr/local/mysql/share/mysql目錄中，配制文件有幾個，有my-huge.cnf my-medium.cnf my-large.cnf my-small.cnf,不同的流量的網(wǎng)站和不同配制的服務(wù)器環(huán)境，當(dāng)然需要有不同的配制文件了。

一般的情況下，my-medium.cnf這個配制文件就能滿足我們的大多需要；一般我們會把配置文件拷貝到/etc/my.cnf 只需要修改這個配置文件就可以了，使用mysqladmin variables extended-status _u root _p 可以看到目前的參數(shù)，有3個配置參數(shù)是最重要的，即key_buffer_size,query_cache_size,table_cache。

key_buffer_size只對MyISAM表起作用，

key_buffer_size指定索引緩沖區(qū)的大小，它決定索引處理的速度，尤其是索引讀的速度。一般我們設(shè)為16M,實際上稍微大一點的站點　這個數(shù)字是遠(yuǎn)遠(yuǎn)不夠的，通過檢查狀態(tài)值Key_read_requests和Key_reads,可以知道key_buffer_size設(shè)置是否合理。比例 key_reads / key_read_requests應(yīng)該盡可能的低，至少是1:100，1:1000更好（上述狀態(tài)值可以使用SHOW STATUS LIKE ‘key_read%’獲得）。 或者如果你裝了phpmyadmin 可以通過服務(wù)器運(yùn)行狀態(tài)看到,筆者推薦用phpmyadmin管理mysql，以下的狀態(tài)值都是本人通過phpmyadmin獲得的實例分析:

這個服務(wù)器已經(jīng)運(yùn)行了20天

key_buffer_size _ 128M

key_read_requests _ 650759289

key_reads - 79112

比例接近1:8000 健康狀況非常好

MySQL8.0：死鎖日志改進(jìn)

為什么會出現(xiàn)這種情況？這是不是bug？

必須不是bug，我們來復(fù)現(xiàn)這一死鎖場景：

死鎖邏輯：

session1 等待獲取的鎖 S Lock 阻塞了 session2 將要獲取的鎖，這在 MySQL8.0 中就會顯示成 session1 持有的鎖，同時也是 session1 等待的鎖。就是這樣。

mysql GROUP BY、DISTINCT、ORDER BY語句優(yōu)化

GROUP BY、DISTINCT、ORDERBY這幾類子句比較類似，GROUP BY默認(rèn)也是要進(jìn)行ORDERBY排序的，筆者在本書中 把它們歸為一類，優(yōu)化的思路也是類似的。

可以考慮的優(yōu)化方式如下。

1、盡量對較少的行進(jìn)行排序。

2、如果連接了多張表，ORDERBY的列應(yīng)該屬于連接順序的第一張表。

3、利用索引排序，如果不能利用索引排序，那么EXPLAIN查詢語句將會看到有filesort。

4、GROUP BY、ORDERBY語句參考的列應(yīng)該盡量在一個表中，如果不在同一個表中，那么可以考慮冗余一些列，或者合并表。

5、需要保證索引列和ORDERBY的列相同，且各列均按相同的方向進(jìn)行排序。

6、增加sort_buffer_size。 sort_buffer_size是為每個排序線程分配的緩沖區(qū)的大小。增加該值可以加快ORDERBY或GROUP BY操作。但是，這是為每 個客戶端分配的緩沖區(qū)，因此不要將全局變量設(shè)置為較大的值，因為每個需要排序的連接都會分配sort_buffer_size大小的內(nèi)存。

7、增加read_rnd_buffer_size。 當(dāng)按照排序后的順序讀取行時，通過該緩沖區(qū)讀取行，從而避免搜索硬盤。將該變量設(shè)置為較大的值可以大大改進(jìn)ORDER BY的性能。但是，這是為每個客戶端分配的緩沖區(qū)，因此你不應(yīng)將全局變量設(shè)置為較大的值。相反，只用為需要運(yùn)行大查詢 的客戶端更改會話變量即可。

8、改變tmpdir變量指向基于內(nèi)存的文件系統(tǒng)或其他更快的磁盤。 如果MySQL服務(wù)器正作為復(fù)制從服務(wù)器被使用，那么不應(yīng)將“--tmpdir”設(shè)置為指向基于內(nèi)存的文件系統(tǒng)的目錄，或者當(dāng)服務(wù) 器主機(jī)重啟時將要被清空的目錄。因為，對于復(fù)制從服務(wù)器，需要在機(jī)器重啟時仍然保留一些臨時文件，以便能夠復(fù)制臨時表 或執(zhí)行LOADDATAINFILE操作。如果在服務(wù)器重啟時丟失了臨時文件目錄下的文件，那么復(fù)制將會失敗。

9、指定ORDERBY NULL。 默認(rèn)情況下，MySQL將排序所有GROUP BY的查詢，如果想要避免排序結(jié)果所產(chǎn)生的消耗，可以指定ORDERBY NULL。 例如：SELECT count(*) cnt, cluster_id FROM stat GROUP BY cluster_id ORDER BY NULL LIMIT 10; ·

10、優(yōu)化GROUP BY WITHROLLUP。 GROUP BY WITHROLLUP可以方便地獲得整體分組的聚合信息（superaggregation），但如果存在性能問題，可以考慮在應(yīng)用層實現(xiàn)這個功能，這樣往往會更高效，伸縮性也更佳。

11、使用非GROUP BY的列來代替GROUP BY的列。 比如，原來是“GROUP BYxx_name,yy_name”，如果GROUP BYxx_id可以得到一樣的結(jié)果，那么使用GROUP BYxx_id也是可 行的。

12、可以考慮使用Sphinx等產(chǎn)品來優(yōu)化GROUP BY語句，一般來說，它可以有更好的可擴(kuò)展性和更佳的性能。

面試中常問：mysql數(shù)據(jù)庫做哪些優(yōu)化也提高mysql性能

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

概念一，數(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個，當(dāng)然也可以是1到100之間的任何一個數(shù)字。這里唯一值越的多少，就是這個列的可選擇基數(shù)。

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

概念二，關(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ǔn)確了，這時候剛好執(zhí)行了一條SQL，那么有可能這條SQL的執(zhí)行計劃就不是最優(yōu)的。為什么說有可能呢？

來看下具體演示

譬如，以下兩條SQL，

A：

select * from t1 where f1 = 20;

B：

select * from t1 where f1 = 30;

如果f1的值剛好頻繁更新的值為30，并且沒有達(dá)到MySQL自動更新cardinality值的臨界值或者說用戶設(shè)置了手動更新又或者用戶減少了sample page等等，那么對這兩條語句來說，可能不準(zhǔn)確的就是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。