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ù)據(jù)庫連接的最大數(shù)量 query_cache_size0 (不打開）16M 查詢緩存區(qū)的最大長度，按照當(dāng)前需求，一倍一倍 增加，本選項(xiàng)比較重要 sort_buffer_size512K16M 每個線程的排序緩存大小，一般按照內(nèi)存可以設(shè)置 為2M以上，推薦是16M，該選項(xiàng)對排序order by， group by起作用 record_buffer128

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

3、_si ze 16M128M 設(shè)置,恢復(fù),修改表的時候使用的緩沖大小， 值不要設(shè)的太大 2021/3/109 選項(xiàng)缺省值推薦值說明 innodb_buffer_pool_size32M1GInnoDB使用一個緩沖池來保存索引和原始數(shù)據(jù), 這 里你設(shè)置越大,你在存取表里面數(shù)據(jù)時所需要的磁盤 I/O越少，一般是內(nèi)存的一半，不超過2G，否則系 統(tǒng)會崩潰，這個參數(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 代表日志寫 入日志文件在每次提交后,但是日志文件只有大約每秒才會刷 新到磁盤上. 對速度影響比較大，同時也關(guān)系數(shù)據(jù)完整性 innodb_log_file_size8M256M在日志組中每個日志文件的大小, 一般是 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.對于連續(xù)的數(shù)值，使用對于連續(xù)的數(shù)值，使用between代替代替in 11.盡量不用觸發(fā)器，盡量不用觸發(fā)器，10. where 字句中盡量不要使用字句中盡量不要使用CASE條件條件 特別是在大數(shù)據(jù)表上特別是在大數(shù)據(jù)表上 2021/3/1017 使用使用 LIMIT 條件是一個好的方法條件是一個好的方法 while (1) /每次只做每次只做1000條條 mysql_query(DELETE FROM logs WHERE lo

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

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

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

10、s=1（如果你想要在索引如果你想要在索引1上排序）。如果只有上排序）。如果只有 一個索引，想要根據(jù)該索引的次序讀取所有的記錄，這是使查詢更快一個索引，想要根據(jù)該索引的次序讀取所有的記錄，這是使查詢更快 的一個好方法。但是請注意，第一次對一個大表按照這種方法排序時的一個好方法。但是請注意，第一次對一個大表按照這種方法排序時 將花很長時間！將花很長時間！ 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的每個輸

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

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ù) 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