mysql性能優(yōu)化

1、2021/3/101 2021/3/102 2021/3/103 2021/3/104 2021/3/105 2021/3/106 2021/3/107 選項(xiàng)缺省值推薦值說明 max_connections1001024 MySQL服務(wù)器同時(shí)處理的數(shù)據(jù)庫連接的最大數(shù)量 query_cache_size0 (不打開）16M 查詢緩存區(qū)的最大長(zhǎng)度，按照當(dāng)前需求，一倍一倍 增加，本選項(xiàng)比較重要 sort_buffer_size512K16M 每個(gè)線程的排序緩存大小，一般按照內(nèi)存可以設(shè)置 為2M以上，推薦是16M，該選項(xiàng)對(duì)排序order by， group by起作用 record_buffer128

2、K16M 每個(gè)進(jìn)行一個(gè)順序掃描的線程為其掃描的每張表分 配這個(gè)大小的一個(gè)緩沖區(qū)，可以設(shè)置為2M以上 table_cache64512 為所有線程打開表的數(shù)量。增加該值能增加mysqld 要求的文件描述符的數(shù)量。MySQL對(duì)每個(gè)唯一打開 的表需要2個(gè)文件描述符。 2021/3/108 選項(xiàng)缺省值推薦值說明 key_buffer_size8M256M 用來存放索引區(qū)塊的緩存值, 建議128M以上， 不要大于內(nèi)存的30% read_buffer_size128K16M 用來做MyISAM表全表掃描的緩沖大小. 為 從數(shù)據(jù)表順序讀取數(shù)據(jù)的讀操作保留的緩存 區(qū)的長(zhǎng)度 myisam_sort_buffer

3、_si ze 16M128M 設(shè)置,恢復(fù),修改表的時(shí)候使用的緩沖大小， 值不要設(shè)的太大 2021/3/109 選項(xiàng)缺省值推薦值說明 innodb_buffer_pool_size32M1GInnoDB使用一個(gè)緩沖池來保存索引和原始數(shù)據(jù), 這 里你設(shè)置越大,你在存取表里面數(shù)據(jù)時(shí)所需要的磁盤 I/O越少，一般是內(nèi)存的一半，不超過2G，否則系 統(tǒng)會(huì)崩潰，這個(gè)參數(shù)非常重要 innodb_additional_mem_p ool_size 2M128MInnoDB用來保存 metadata 信息, 如果內(nèi)存是4G， 最好本值超過200M innodb_flush_log_at_trx_co mmit 1

4、0 0 代表日志只大約每秒寫入日志文件并且日志文件刷新到磁 盤; 1 為執(zhí)行完沒執(zhí)行一條SQL馬上commit; 2 代表日志寫 入日志文件在每次提交后,但是日志文件只有大約每秒才會(huì)刷 新到磁盤上. 對(duì)速度影響比較大，同時(shí)也關(guān)系數(shù)據(jù)完整性 innodb_log_file_size8M256M在日志組中每個(gè)日志文件的大小, 一般是 innodb_buffer_pool_size的25%，官方推薦是 innodb_buffer_pool_size 的 40-50%, 設(shè)置大一點(diǎn)來 避免在日志文件覆寫上不必要的緩沖池刷新行為 innodb_log_buffer_size128K8M用來緩沖日志數(shù)據(jù)的

5、緩沖區(qū)的大小. 推薦是8M，官 方推薦該值小于16M，最好是 1M-8M 之間 2021/3/1010 2021/3/1011 2021/3/1012 2021/3/1013 2021/3/1014 http:/ 2021/3/1015 2021/3/1016 select id from uinfo_jifen where jifen/60 10000; 優(yōu)化后優(yōu)化后: Select id from uinfo_jifen where jifen600000; select uid from imid where datediff(create_time,2011-11-22)=0 優(yōu)化后優(yōu)

6、化后 select uid from imid where create_time =2011-11-21 and create_time2011-11-23; 8. 9.對(duì)于連續(xù)的數(shù)值，使用對(duì)于連續(xù)的數(shù)值，使用between代替代替in 11.盡量不用觸發(fā)器，盡量不用觸發(fā)器，10. where 字句中盡量不要使用字句中盡量不要使用CASE條件條件 特別是在大數(shù)據(jù)表上特別是在大數(shù)據(jù)表上 2021/3/1017 使用使用 LIMIT 條件是一個(gè)好的方法條件是一個(gè)好的方法 while (1) /每次只做每次只做1000條條 mysql_query(DELETE FROM logs WHERE lo

7、g_date = 2009-11-01 LIMIT 1000); if (mysql_affected_rows() = 0) / 沒得可刪了，退出！沒得可刪了，退出！ break; / 每次都要休息一會(huì)兒每次都要休息一會(huì)兒 usleep(50000); 2021/3/1018 2021/3/1019 InnoDB要掃描一遍整要掃描一遍整 個(gè)表來計(jì)算有多少行，但是個(gè)表來計(jì)算有多少行，但是MyISAM只要簡(jiǎn)單的讀出保存只要簡(jiǎn)單的讀出保存 好的行數(shù)即可。注意的是，當(dāng)好的行數(shù)即可。注意的是，當(dāng)count(*)語句包含語句包含 where條件條件 時(shí)，兩種表的時(shí)，兩種表的操作操作是一樣的。是一樣的。

8、2021/3/1020 SELECT查詢的速度查詢的速度 要想使一個(gè)較慢速要想使一個(gè)較慢速SELECT . WHERE更快，應(yīng)首先檢查是否更快，應(yīng)首先檢查是否 能增加一個(gè)索引。你可以使用能增加一個(gè)索引。你可以使用EXPLAIN語句來確定語句來確定SELECT語句使用語句使用 哪些索引。哪些索引。 為了幫助為了幫助MySQL更好地優(yōu)化查詢，在一個(gè)裝載數(shù)據(jù)后的表上更好地優(yōu)化查詢，在一個(gè)裝載數(shù)據(jù)后的表上 運(yùn)行運(yùn)行ANALYZE TABLE或或myisamchk -analyze。這樣為每一個(gè)索引更這樣為每一個(gè)索引更 新指出有相同值的行的平均行數(shù)的值（當(dāng)然，如果只有一個(gè)索引，這新指出有相同值的行的平均

9、行數(shù)的值（當(dāng)然，如果只有一個(gè)索引，這 總是總是1。）。）MySQL使用該方法來決定當(dāng)你聯(lián)接兩個(gè)基于非常量表達(dá)式使用該方法來決定當(dāng)你聯(lián)接兩個(gè)基于非常量表達(dá)式 的表時(shí)選擇哪個(gè)索引。你可以使用的表時(shí)選擇哪個(gè)索引。你可以使用SHOW INDEX FROM tbl_name并并 檢查檢查Cardinality值來檢查表分析結(jié)果。值來檢查表分析結(jié)果。myisamchk -description - verbose可以顯示索引分布信息?？梢燥@示索引分布信息。 要想根據(jù)一個(gè)索引排序一個(gè)索引和數(shù)據(jù)，使用要想根據(jù)一個(gè)索引排序一個(gè)索引和數(shù)據(jù)，使用myisamchk - sort-index -sort-record

10、s=1（如果你想要在索引如果你想要在索引1上排序）。如果只有上排序）。如果只有 一個(gè)索引，想要根據(jù)該索引的次序讀取所有的記錄，這是使查詢更快一個(gè)索引，想要根據(jù)該索引的次序讀取所有的記錄，這是使查詢更快 的一個(gè)好方法。但是請(qǐng)注意，第一次對(duì)一個(gè)大表按照這種方法排序時(shí)的一個(gè)好方法。但是請(qǐng)注意，第一次對(duì)一個(gè)大表按照這種方法排序時(shí) 將花很長(zhǎng)時(shí)間！將花很長(zhǎng)時(shí)間！ 2021/3/1021 n如果在如果在SELECT語句前放上關(guān)鍵詞語句前放上關(guān)鍵詞EXPLAIN，MySQL將解釋它如何處將解釋它如何處 理理SELECT，提供有關(guān)表如何聯(lián)接和聯(lián)接的次序。，提供有關(guān)表如何聯(lián)接和聯(lián)接的次序。 EXPLAIN的每個(gè)輸

11、出行提供一個(gè)表的相關(guān)信息，并且每個(gè)行包括下面的列的每個(gè)輸出行提供一個(gè)表的相關(guān)信息，并且每個(gè)行包括下面的列: idSELECT識(shí)別符。這是SELECT 的查詢序列號(hào) select_typ e SELECT類型 table輸出的行所引用的表type聯(lián)接類型 possible _keys possible_keys列指出MySQL 能使用哪個(gè)索引在該表中找到行 keykey列顯示MySQL實(shí)際決定 使用的鍵（索引） key_lenkey_len列顯示MySQL決定使 用的鍵長(zhǎng)度 refref列顯示使用哪個(gè)列或常數(shù) 與key一起從表中選擇行 rowsrows列顯示MySQL認(rèn)為它執(zhí)行 查詢時(shí)必須檢查的

12、行數(shù) Extra該列包含MySQL解決查詢的 詳細(xì)信息 2021/3/1022 2021/3/1023 2021/3/1024 2021/3/1025 2021/3/1026 2021/3/1027 n控制流程函數(shù)控制流程函數(shù) n字符串比較函數(shù)字符串比較函數(shù) n數(shù)學(xué)函數(shù)數(shù)學(xué)函數(shù) n日期和時(shí)間函數(shù)日期和時(shí)間函數(shù) n全文搜索功能函數(shù)全文搜索功能函數(shù) n加密函數(shù)加密函數(shù) n信息函數(shù)信息函數(shù) n其他函數(shù)其他函數(shù) 詳細(xì)介紹參見詳細(xì)介紹參見mysql函數(shù)函數(shù) 2021/3/1028 n控制流程函數(shù)控制流程函數(shù) CASE WHEN THEN 函數(shù)函數(shù) IF 函數(shù)函數(shù) IFNULL 函數(shù)函數(shù) n全文搜索功能函數(shù)全文搜索功能函數(shù) SELECT * FROM category WHERE MA TCH (catn