-
系統(tǒng)層面(基本不用動币叹，看了下贵白，買的云服務(wù)器基本都已經(jīng)優(yōu)化過了)
內(nèi)核相關(guān)參數(shù)(/etc/sysctl.conf)
網(wǎng)絡(luò)相關(guān)
- net.core.somaxconn = 65535
- net.core.netdev_max_backlog = 65535
- net.ipv4.tcp_max_syn_backlog = 65535
- net.ipv4.tcp_fin_timeout = 10
- net.ipv4.tcp_tw_resue = 1
- net.ipv4.tcp_tw_recycle = 1
緩沖區(qū)
- net.core.wmem_default = 87380
- net.core.wmem_max = 16777216
- net.core.rmem_default = 87380
- net.core.rmem_max = 16777216
失效鏈接
- net.ipv4.tcp_keepalive_time = 120
- net.ipv4.tcp_keepalive_intvl = 30
- net.ipv4.tcp_keepalive_probes = 3
內(nèi)存
- kernel.shmmax = 4294967295
linux內(nèi)核參數(shù)中最重要的參數(shù)之一,用于定義單個共享內(nèi)存段的最大值.
- vm.swappiness = 0
交換分區(qū)
增加資源限制(/etc/security/limit.conf)
* soft nofile 65535
* hard nofile 65535
加到limi.conf文件末尾
* 表示對所有用戶有效
soft 指的是當(dāng)前系統(tǒng)生效的設(shè)置
hard 表明系統(tǒng)中所能設(shè)定的最大值
nofile表示所限制的資源是打開文件的最大數(shù)目
65535 限制的數(shù)量
磁盤調(diào)度策略(/sys/block/devname/queue/scheduler)
cat /sys/block/sda/queue/scheduler
noop anticipatory deadline [cfq]
-
mysql優(yōu)化
-
優(yōu)化思路(數(shù)據(jù)來自localhost,centos7,2G RAM,1cpu)
- 連接 Connections
mysql> show variables like 'max_connections'; +-----------------+-------+ | variable_name | value | +-----------------+-------+ | max_connections | 151 | +-----------------+-------+ 查一下服務(wù)器響應(yīng)的最大連接數(shù) mysql> show global status like 'max_used_connections'; +----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | Max_used_connections | 14 | +----------------------+-------+ 可見我使用的連接數(shù)沒有達(dá)到最大連接數(shù),比較理想的是 max_used_connections / max_connections * 100% ≈ 85% 最大連接數(shù)占上限連接數(shù)的85％左右帆赢，如果發(fā)現(xiàn)比例在10%以下罚随，mysql服務(wù)器連接數(shù)上限設(shè)置的過高了下硕。- 線程 Thread
mysql> show global status like 'thread%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Threadpool_idle_threads | 0 | | Threadpool_threads | 0 | | Threads_cached | 0 | | Threads_connected | 11 | | Threads_created | 1818 | | Threads_running | 1 | +-------------------------+-------+ 如果我們在mysql服務(wù)器配置文件中設(shè)置了thread_cache_size丁逝，當(dāng)客戶端斷開之后，服務(wù)器處理此客戶的線程將會緩存起來以響應(yīng)下一個客戶而不是銷毀（前提是緩存數(shù)未達(dá)上限）梭姓。 threads_created表示創(chuàng)建過的線程數(shù)霜幼，如果發(fā)現(xiàn)threads_created值過大的話，表明mysql服務(wù)器一直在創(chuàng)建線程誉尖，這也是比較耗資源罪既，可以適當(dāng)增加配置文件中thread_cache_size值， 查詢服務(wù)器 thread_cache_size 配置： mysql> show variables like 'thread_cache_size'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | thread_cache_size | 0 | +-------------------+-------+ 說明我需要配置thread_cahce_size的值- 緩存cache
- 文件打開數(shù)
mysql> show global status like 'open_files'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Open_files | 23 | +---------------+-------+ mysql> show variables like 'open_files_limit'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | open_files_limit | 1024 | +------------------+-------+ 比較合適的設(shè)置：open_files / open_files_limit * 100% <= 75%- 數(shù)據(jù)表
# 打開數(shù) open_tables mysql> show global status like 'open%tables%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Open_tables | 47 | | Opened_tables | 28 | +---------------+-------+ open_tables: 打開表的數(shù)量 opened_tables: 打開過的表數(shù)量 如果 opened_tables 數(shù)量過大铡恕，說明配置中 table_open_cache值可能太小琢感，我們查詢一下服務(wù)器table_open_cache值: mysql> show variables like 'table_open_cache'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | table_open_cache | 400 | +------------------+-------+ 比較合適的值為： open_tables / opened_tables * 100% >= 85% open_tables / table_cache * 100% <= 95%# 臨時表 tmp_table mysql> show global status like 'created_tmp%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Created_tmp_disk_tables | 151 | | Created_tmp_files | 6 | | Created_tmp_tables | 1260 | +-------------------------+-------+ 每次創(chuàng)建臨時表，created_tmp_tables 增加没咙，如果是在磁盤上創(chuàng)建臨時表猩谊，created_tmp_disk_tables也增加,created_tmp_files表示mysql服務(wù)創(chuàng)建的臨時文件文件數(shù)，比較理想的配置是： created_tmp_disk_tables / created_tmp_tables * 100% <= 25% 比如上面的服務(wù)器 created_tmp_disk_tables / created_tmp_tables * 100% ＝ 11.98%祭刚，應(yīng)該相當(dāng)好了牌捷。我們再看一下mysql服務(wù)器對臨時表的配置： mysql> show variables where variable_name in ('tmp_table_size','max_heap_table_size'); 只有 256mb 以下的臨時表才能全部放內(nèi)存墙牌，超過的就會用到硬盤臨時表。表級鎖 mysql> show global status like 'table_locks%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Table_locks_immediate | 28141 | | Table_locks_waited | 0 | +-----------------------+-------+ table_locks_immediate 表示立即釋放表鎖數(shù)暗甥， table_locks_waited 表示需要等待的表鎖數(shù)喜滨， 如果 table_locks_immediate / table_locks_waited > 5000，最好采用innodb引擎撤防，因為innodb是行鎖而myisam是表鎖虽风，對于高并發(fā)寫入的應(yīng)用innodb效果會好些。表掃描 mysql> show global status like 'handler_read%'; +--------------------------+----------+ | Variable_name | Value | +--------------------------+----------+ | Handler_read_first | 1078 | | Handler_read_key | 65237 | | Handler_read_last | 0 | | Handler_read_next | 11517 | | Handler_read_prev | 0 | | Handler_read_rnd | 5257 | | Handler_read_rnd_deleted | 200 | | Handler_read_rnd_next | 10240972 | +--------------------------+----------+ 服務(wù)器完成的查詢請求次數(shù): mysql> show global status like 'com_select'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Com_select | 24328 | +---------------+-------+ 計算表掃描率： 表掃描率 ＝ handler_read_rnd_next / com_select 如果表掃描率超過 4000寄月，說明進行了太多表掃描辜膝，很有可能索引沒有建好，增加 read_buffer_size 值會有一些好處漾肮，但最好不要超過8mb厂抖。key_buffer_size key_buffer_size是對myisam表性能影響最大的一個參數(shù)，下面一臺以myisam為主要存儲引擎服務(wù)器的配置: mysql> show variables like 'key_buffer_size'; +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | key_buffer_size | 134217728 | +-----------------+-----------+ 分配了 128mb 內(nèi)存給 key_buffer_size 克懊，我們再看一下 key_buffer_size 的使用情況: mysql> show global status like 'key_read%'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Key_read_requests | 56 | | Key_reads | 2 | +-------------------+-------+ 一共有 56個 索引讀取請求忱辅，有 2個 請求在內(nèi)存中沒有找到直接從硬盤讀取索引，計算索引未命中緩存的概率： key_cache_miss_rate ＝ key_reads / key_read_requests * 100% 比如上面的數(shù)據(jù)谭溉，key_cache_miss_rate為3.6%墙懂，100個索引讀取請求才有3個直接讀硬盤，已經(jīng)很bt了扮念，key_cache_miss_rate在0.1%以下都很好（每1000個請求有一個直接讀硬盤）损搬，如果key_cache_miss_rate在0.01%以下的話，key_buffer_size分配的過多柜与，可以適當(dāng)減少场躯。 【注意】key_read_buffer 默認(rèn)值為 8M 。在專有的數(shù)據(jù)庫服務(wù)器上旅挤，該值可設(shè)置為 RAM * 1/4 mysql服務(wù)器還提供了key_blocks_*參數(shù)： mysql> show global status like 'key_blocks_u%'; +-------------------+--------+ | Variable_name | Value | +-------------------+--------+ | Key_blocks_unused | 107169 | | Key_blocks_used | 2 | +-------------------+--------+ key_blocks_unused 表示未使用的緩存簇(blocks)數(shù) key_blocks_used 表示曾經(jīng)用到的最大的blocks數(shù) 我這臺緩存完全沒用到，因為是本機伞鲫，數(shù)據(jù)量極少 假如所有的緩存都用到了粘茄，要么增加 key_buffer_size，要么就是過渡索引了秕脓，把緩存占滿了柒瓣。比較理想的設(shè)置： key_blocks_used / (key_blocks_unused + key_blocks_used) * 100% ≈ 80%排序使用情況 sort_buffer mysql> show global status like 'sort%'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Sort_merge_passes | 0 | | Sort_range | 0 | | Sort_rows | 9486 | | Sort_scan | 1449 | +-------------------+-------+ sort_merge_passes 包括兩步。mysql 首先會嘗試在內(nèi)存中做排序吠架，使用的內(nèi)存大小由系統(tǒng)變量 sort_buffer_size 決定芙贫，如果它的大小不夠把所有的記錄都讀到內(nèi)存中，mysql 就會把每次在內(nèi)存中排序的結(jié)果存到臨時文件中傍药，等 mysql 找到所有記錄之后磺平，再把臨時文件中的記錄做一次排序魂仍。這再次排序就會增加 sort_merge_passes。實際上拣挪，mysql 會用另一個臨時文件來存再次排序的結(jié)果擦酌，所以通常會看到 sort_merge_passes 增加的數(shù)值是建臨時文件數(shù)的兩倍。因為用到了臨時文件菠劝，所以速度可能會比較慢赊舶，增加 sort_buffer_size 會減少 sort_merge_passes 和 創(chuàng)建臨時文件的次數(shù)。但盲目的增加 sort_buffer_size 并不一定能提高速度赶诊，見 搜索how fast can you sort data with mysql?（需要翻墻） 另外笼平，增加read_rnd_buffer_size(3.2.3是record_rnd_buffer_size)的值對排序的操作也有一點的好處。查詢緩存 mysql> show global status like 'qcache%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Qcache_free_blocks | 0 | | Qcache_free_memory | 0 | | Qcache_hits | 0 | | Qcache_inserts | 0 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 0 | | Qcache_queries_in_cache | 0 | | Qcache_total_blocks | 0 | +-------------------------+-------+ mysql 查詢緩存變量解釋： qcache_free_blocks：緩存中相鄰內(nèi)存塊的個數(shù)舔痪。數(shù)目大說明可能有碎片寓调。flush query cache會對緩存中的碎片進行整理，從而得到一個空閑塊辙喂。 qcache_free_memory：緩存中的空閑內(nèi)存捶牢。 qcache_hits：每次查詢在緩存中命中時就增大 qcache_inserts：每次插入一個查詢時就增大。命中次數(shù)除以插入次數(shù)就是命中比率巍耗。 qcache_lowmem_prunes：緩存出現(xiàn)內(nèi)存不足并且必須要進行清理以便為更多查詢提供空間的次數(shù)秋麸。這個數(shù)字最好長時間來看；如果這個數(shù)字在不斷增長炬太，就表示可能碎片非常嚴(yán)重灸蟆，或者內(nèi)存很少。（上面的 free_blocks和free_memory可以告訴您屬于哪種情況） qcache_not_cached：不適合進行緩存的查詢的數(shù)量亲族，通常是由于這些查詢不是 select 語句或者用了now()之類的函數(shù)炒考。 qcache_queries_in_cache：當(dāng)前緩存的查詢（和響應(yīng)）的數(shù)量。 qcache_total_blocks：緩存中塊的數(shù)量霎迫。 我們再查詢一下服務(wù)器關(guān)于query_cache的配置： mysql> show variables like 'query_cache%'; +------------------------------+---------+ | Variable_name | Value | +------------------------------+---------+ | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 0 | | query_cache_strip_comments | OFF | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | +------------------------------+---------+ 各字段的解釋： query_cache_limit：超過此大小的查詢將不緩存 query_cache_min_res_unit：緩存塊的最小大小 query_cache_size：查詢緩存大小 query_cache_type：緩存類型斋枢，決定緩存什么樣的查詢，示例中表示不緩存 select sql_no_cache 查詢 query_cache_wlock_invalidate：當(dāng)有其他客戶端正在對myisam表進行寫操作時知给，如果查詢在query cache中瓤帚，是否返回cache結(jié)果還是等寫操作完成再讀表獲取結(jié)果。 query_cache_min_res_unit的配置是一柄”雙刃劍”涩赢，默認(rèn)是4kb戈次，設(shè)置值大對大數(shù)據(jù)查詢有好處，但如果你的查詢都是小數(shù)據(jù)查詢筒扒，就容易造成內(nèi)存碎片和浪費怯邪。 如果查詢緩存碎片率超過20%，可以用flush query cache整理緩存碎片花墩，或者試試減小query_cache_min_res_unit悬秉，如果你的查詢都是小數(shù)據(jù)量的話澄步。 查詢緩存利用率 = (query_cache_size - Qcache_free_memory) / query_cache_size * 100% 查詢緩存利用率在25%以下的話說明query_cache_size設(shè)置的過大，可適當(dāng)減新酢驮俗；查詢緩存利用率在80％以上而且qcache_lowmem_prunes > 50的話說明query_cache_size可能有點小，要不就是碎片太多允跑。 查詢緩存命中率 = (qcache_hits - qcache_inserts) / qcache_hits * 100% 我本機沒有設(shè)置緩存 - 其他
慢查詢 在mysqld下加入慢查詢配置 log-slow-queries = /var/lib/mysql/mysql-slow.log long_query_time = 2 日志文件一定要有寫權(quán)限,配置需要重啟數(shù)據(jù)庫 mysql> show variables like '%slow%'; +---------------------+--------------------------------------------------------------------------------------------------------------+ | Variable_name | Value | +---------------------+--------------------------------------------------------------------------------------------------------------+ | log_slow_filter | admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk | | log_slow_queries | ON | | log_slow_rate_limit | 1 | | log_slow_verbosity | | | slow_launch_time | 2 | | slow_query_log | ON | | slow_query_log_file | /var/lib/mysql/mysql-slow.log | +---------------------+--------------------------------------------------------------------------------------------------------------+ mysql> show global status like '%slow%'; +---------------------+-------+ | Variable_name | Value | +---------------------+-------+ | Slow_launch_threads | 0 | | Slow_queries | 0 | +---------------------+-------+ 可以查看日志王凑，分析有問題的sql語句 -
基本配置優(yōu)化(針對innodb存儲引擎)(必須要優(yōu)化的，默認(rèn)配置有問題)
# innodb緩沖池聋丝，保證數(shù)據(jù)是從內(nèi)存而不是硬盤讀取索烹，越大越好，一般設(shè)置成內(nèi)存的80% innodb_buffer_pool_size = 100000M # 此參數(shù)確定數(shù)據(jù)日志文件的大小弱睦，以M為單位百姓，更大的設(shè)置可以提高性能，但也會增加恢復(fù)故障數(shù)據(jù)庫所需的時間,一開始就把innodb_log_file_size設(shè)置成512M(這樣有1GB的redo日志)會使你有充裕的寫操作空間况木。如果你知道你的應(yīng)用程序需要頻繁的寫入數(shù)據(jù)并且你使用的時MySQL 5.6垒拢，你可以一開始就把它設(shè)置成4G innodb_log_file_size = 4096M # 連接數(shù)大小，如果程序出現(xiàn)'Too many connections'的錯誤就需要調(diào)整這個值,max_connection值被設(shè)高了(例如1000或更高)之后一個主要缺陷是當(dāng)服務(wù)器運行1000個或更高的活動事務(wù)時會變的沒有響應(yīng)火惊。在應(yīng)用程序里使用連接池或者在MySQL里使用進程池有助于解決這一問題求类。 max_connections = 3000 # 0為off，1為on屹耐，這項設(shè)置告知InnoDB是否需要將所有表的數(shù)據(jù)和索引存放在共享表空間里,或者為每張表的數(shù)據(jù)單獨放在一個.ibd文件,表多的時候關(guān)閉尸疆，要不然文件太多，表少開啟 innodb_file_per_table = 1 # 默認(rèn)值為1惶岭，表示InnoDB完全支持ACID特性寿弱。當(dāng)你的主要關(guān)注點是數(shù)據(jù)安全的時候這個值是最合適的，比如在一個主節(jié)點上按灶。但是對于磁盤（讀寫）速度較慢的系統(tǒng)症革，它會帶來很巨大的開銷，因為每次將改變flush到redo日志都需要額外的fsyncs鸯旁。將它的值設(shè)置為2會導(dǎo)致不太可靠（reliable）因為提交的事務(wù)僅僅每秒才flush一次到redo日志地沮，但對于一些場景是可以接受的，比如對于主節(jié)點的備份節(jié)點這個值是可以接受的羡亩。如果值為0速度就更快了，但在系統(tǒng)崩潰時可能丟失一些數(shù)據(jù)：只適用于備份節(jié)點危融。 innodb_flush_log_at_trx_commit = 1 # 數(shù)據(jù)和日志寫入硬盤的方式畏铆，默認(rèn)值就行，如果你有硬件RAID控制器吉殃，并且其獨立緩存采用write-back機制辞居，并有著電池斷電保護楷怒，那么應(yīng)該設(shè)置配置為O_DIRECT innodb_flush_method = fdatasync # 這項配置決定了為尚未執(zhí)行的事務(wù)分配的緩存。其默認(rèn)值（1MB）一般來說已經(jīng)夠用了瓦灶，但是如果你的事務(wù)中包含有二進制大對象或者大文本字段的話鸠删，這點緩存很快就會被填滿并觸發(fā)額外的I/O操作≡籼眨看看Innodb_log_waits狀態(tài)變量刃泡，如果它不是0，增加innodb_log_buffer_size innodb_log_buffer_size = 1M # 設(shè)置query_cache_size = 0（現(xiàn)在MySQL 5.6的默認(rèn)值）并利用其他方法加速查詢：優(yōu)化索引碉怔、增加拷貝分散負(fù)載或者啟用額外的緩存（比如memcache或redis）烘贴。 query_cache_size = 0 # 禁止MySQL對外部連接進行DNS解析，使用這一選項可以消除MySQL進行DNS解析的時間撮胧。但需要注意桨踪，如果開啟該選項，則所有遠(yuǎn)程主機連接授權(quán)都要使用IP地址方式芹啥，否則MySQL將無法正常處理連接請求锻离！ skip-name-resolve-
配置詳解
# 在MYSQL暫時停止響應(yīng)新請求之前，短時間內(nèi)的多少個請求可以被存在堆棧中墓怀。如果系統(tǒng)在短時間內(nèi)有很多連接汽纠，則需要增大該參數(shù)的值，該參數(shù)值指定到來的TCP/IP連接的監(jiān)聽隊列的大小捺疼。默認(rèn)值50疏虫。 back_log = 600 # 設(shè)置每個主機的連接請求異常中斷的最大次數(shù)，當(dāng)超過該次數(shù)啤呼，MYSQL服務(wù)器將禁止host的連接請求卧秘，直到mysql服務(wù)器重啟或通過flush hosts命令清空此host的相關(guān)信息。 max_connect_errors = 6000 # 指示表調(diào)整緩沖區(qū)大小官扣。table_cache 參數(shù)設(shè)置表高速緩存的數(shù)目翅敌。每個連接進來，都會至少打開一個表緩存惕蹄。因此蚯涮， table_cache 的大小應(yīng)與 max_connections 的設(shè)置有關(guān)。例如卖陵，對于 200 個#并行運行的連接遭顶，應(yīng)該讓表的緩存至少有 200 × N ，這里 N 是應(yīng)用可以執(zhí)行的查詢#的一個聯(lián)接中表的最大數(shù)量泪蔫。此外棒旗，還需要為臨時表和文件保留一些額外的文件描述符。 table_open_cache = 614 # 設(shè)置在網(wǎng)絡(luò)傳輸中一次消息傳輸量的最大值撩荣。系統(tǒng)默認(rèn)值 為1MB铣揉，最大值是1GB饶深，必須設(shè)置1024的倍數(shù)。 max_allowed_packet = 32M # Sort_Buffer_Size 是一個connection級參數(shù)逛拱，在每個connection（session）第一次需要使用這個buffer的時候敌厘，一次性分配設(shè)置的內(nèi)存。 # Sort_Buffer_Size 并不是越大越好朽合，由于是connection級的參數(shù)俱两，過大的設(shè)置+高并發(fā)可能會耗盡系統(tǒng)內(nèi)存資源。例如：500個連接將會消耗 500*sort_buffer_size(8M)=4G內(nèi)存 # Sort_Buffer_Size 超過2KB的時候旁舰，就會使用mmap() 而不是 malloc() 來進行內(nèi)存分配锋华，導(dǎo)致效率降低。 # explain select*from table where order limit箭窜；出現(xiàn)filesort # 屬重點優(yōu)化參數(shù) sort_buffer_size = 2M # 用于表間關(guān)聯(lián)緩存的大小毯焕，和sort_buffer_size一樣，該參數(shù)對應(yīng)的分配內(nèi)存也是每個連接獨享磺樱。 join_buffer_size = 2M # 服務(wù)器線程緩存這個值表示可以重新利用保存在緩存中線程的數(shù)量,當(dāng)斷開連接時如果緩存中還有空間,那么客戶端的線程將被放到緩存中,如果線程重新被請求纳猫，那么請求將從緩存中讀取,如果緩存中是空的或者是新的請求，那么這個線程將被重新創(chuàng)建,如果有很多新的線程竹捉，增加這個值可以改善系統(tǒng)性能.通過比較 Connections 和 Threads_created 狀態(tài)的變量芜辕，可以看到這個變量的作用。設(shè)置規(guī)則如下：1GB 內(nèi)存配置為8块差，2GB配置為16侵续，3GB配置為32，4GB或更高內(nèi)存憨闰，可配置更大状蜗。 thread_cache_size = 300 # 設(shè)置thread_concurrency的值的正確與否, 對mysql的性能影響很大, 在多個cpu(或多核)的情況下，錯誤設(shè)置了thread_concurrency的值, 會導(dǎo)致mysql不能充分利用多cpu(或多核), 出現(xiàn)同一時刻只能一個cpu(或核)在工作的情況鹉动。thread_concurrency應(yīng)設(shè)為CPU核數(shù)的2倍. 比如有一個雙核的CPU, 那么thread_concurrency的應(yīng)該為4; 2個雙核的cpu, thread_concurrency的值應(yīng)為8 # 屬重點優(yōu)化參數(shù) thread_concurrency = 8 # 對于使用MySQL的用戶轧坎，對于這個變量大家一定不會陌生。前幾年的MyISAM引擎優(yōu)化中泽示，這個參數(shù)也是一個重要的優(yōu)化參數(shù)缸血。但隨著發(fā)展，這個參數(shù)也爆露出來一些問題械筛。機器的內(nèi)存越來越大捎泻，人們也都習(xí)慣性的把以前有用的參數(shù)分配的值越來越大。這個參數(shù)加大后也引發(fā)了一系列問題埋哟。我們首先分析一下 query_cache_size的工作原理：一個SELECT查詢在DB中工作后笆豁，DB會把該語句緩存下來，當(dāng)同樣的一個SQL再次來到DB里調(diào)用時，DB在該表沒發(fā)生變化的情況下把結(jié)果從緩存中返回給Client渔呵。這里有一個關(guān)建點，就是DB在利用Query_cache工作時砍鸠，要求該語句涉及的表在這段時間內(nèi)沒有發(fā)生變更扩氢。那如果該表在發(fā)生變更時，Query_cache里的數(shù)據(jù)又怎么處理呢爷辱？首先要把Query_cache和該表相關(guān)的語句全部置為失效录豺，然后在寫入更新。那么如果Query_cache非常大饭弓，該表的查詢結(jié)構(gòu)又比較多双饥，查詢語句失效也慢，一個更新或是Insert就會很慢弟断，這樣看到的就是Update或是Insert怎么這么慢了咏花。所以在數(shù)據(jù)庫寫入量或是更新量也比較大的系統(tǒng)，該參數(shù)不適合分配過大阀趴。而且在高并發(fā)昏翰，寫入量大的系統(tǒng)，建議把該功能禁掉刘急。 # 重點優(yōu)化參數(shù)(主庫 增刪改-MyISAM) query_cache_size = 64M # 指定單個查詢能夠使用的緩沖區(qū)大小棚菊，缺省為1M query_cache_limit = 4M # 默認(rèn)是4KB，設(shè)置值大對大數(shù)據(jù)查詢有好處叔汁，但如果你的查詢都是小數(shù)據(jù)查詢统求，就容易造成內(nèi)存碎片和浪費 # 查詢緩存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100% # 如果查詢緩存碎片率超過20%，可以用FLUSH QUERY CACHE整理緩存碎片据块，或者試試減小query_cache_min_res_unit码邻，如果你的查詢都是小數(shù)據(jù)量的話。 # 查詢緩存利用率 = (query_cache_size – Qcache_free_memory) / query_cache_size * 100% # 查詢緩存利用率在25%以下的話說明query_cache_size設(shè)置的過大瑰钮，可適當(dāng)減小;查詢緩存利用率在80%以上而且Qcache_lowmem_prunes > 50的話說明query_cache_size可能有點小冒滩，要不就是碎片太多。 # 查詢緩存命中率 = (Qcache_hits – Qcache_inserts) / Qcache_hits * 100% query_cache_min_res_unit = 2k # 設(shè)置MYSQL每個線程的堆棧大小浪谴，默認(rèn)值足夠大开睡，可滿足普通操作」冻埽可設(shè)置范圍為128K至4GB篇恒，默認(rèn)為192KB。 thread_stack = 192K # 設(shè)定默認(rèn)的事務(wù)隔離級別.可用的級別如下: # READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE # 1.READ UNCOMMITTED-讀未提交2.READ COMMITTE-讀已提交3.REPEATABLE READ -可重復(fù)讀4.SERIALIZABLE -串行 # 重要概念 transaction_isolation = READ-COMMITTED # tmp_table_size 的默認(rèn)大小是 32M凶杖。如果一張臨時表超出該大小胁艰，MySQL產(chǎn)生一個 The table tbl_name is full 形式的錯誤，如果你做很多高級 GROUP BY 查詢，增加 tmp_table_size 值腾么。如果超過該值奈梳，則會將臨時表寫入磁盤。 tmp_table_size = 256M # 批定用于索引的緩沖區(qū)大小解虱，增加它可以得到更好的索引處理性能攘须，對于內(nèi)存在4GB左右的服務(wù)器來說，該參數(shù)可設(shè)置為256MB或384MB殴泰。 key_buffer_size = 256M # MySql讀入緩沖區(qū)大小于宙。對表進行順序掃描的請求將分配一個讀入緩沖區(qū)，MySql會為它分配一段內(nèi)存緩沖區(qū)悍汛。read_buffer_size變量控制這一緩沖區(qū)的大小捞魁。如果對表的順序掃描請求非常頻繁，并且你認(rèn)為頻繁掃描進行得太慢，可以通過增加該變量值以及內(nèi)存緩沖區(qū)大小提高其性能。和sort_buffer_size一樣诡挂，該參數(shù)對應(yīng)的分配內(nèi)存也是每個連接獨享秆剪。 read_buffer_size = 1M # MySql的隨機讀（查詢操作）緩沖區(qū)大小。當(dāng)按任意順序讀取行時(例如，按照排序順序)，將分配一個隨機讀緩存區(qū)。進行排序查詢時宣吱，MySql會首先掃描一遍該緩沖，以避免磁盤搜索瞳别，提高查詢速度征候，如果需要排序大量數(shù)據(jù)，可適當(dāng)調(diào)高該值祟敛。但MySql會為每個客戶連接發(fā)放該緩沖空間疤坝，所以應(yīng)盡量適當(dāng)設(shè)置該值，以避免內(nèi)存開銷過大馆铁。 read_rnd_buffer_size = 16M # 批量插入數(shù)據(jù)緩存大小跑揉，可以有效提高插入效率，默認(rèn)為8M bulk_insert_buffer_size = 64M # MyISAM表發(fā)生變化時重新排序所需的緩沖 myisam_sort_buffer_size = 128M # 如果一個表擁有超過一個索引, MyISAM 可以通過并行排序使用超過一個線程去修復(fù)他們. # 這對于擁有多個CPU以及大量內(nèi)存情況的用戶,是一個很好的選擇. myisam_max_extra_sort_file_size = 10G myisam_repair_threads = 1 # 自動檢查和修復(fù)沒有適當(dāng)關(guān)閉的 MyISAM 表 myisam_recover # 這個參數(shù)用來設(shè)置 InnoDB 存儲的數(shù)據(jù)目錄信息和其它內(nèi)部數(shù)據(jù)結(jié)構(gòu)的內(nèi)存池大小埠巨，類似于Oracle的library cache历谍。這不是一個強制參數(shù)，可以被突破辣垒。 innodb_additional_mem_pool_size = 16M # 這對Innodb表來說非常重要望侈。Innodb相比MyISAM表對緩沖更為敏感。MyISAM可以在默認(rèn)的 key_buffer_size 設(shè)置下運行的可以勋桶，然而Innodb在默認(rèn)的 innodb_buffer_pool_size 設(shè)置下卻跟蝸牛似的脱衙。由于Innodb把數(shù)據(jù)和索引都緩存起來侥猬，無需留給操作系統(tǒng)太多的內(nèi)存，因此如果只需要用Innodb的話則可以設(shè)置它高達(dá) 70~80% 的可用內(nèi)存捐韩。一些應(yīng)用于 key_buffer 的規(guī)則有 — 如果你的數(shù)據(jù)量不大退唠，并且不會暴增，那么無需把 innodb_buffer_pool_size 設(shè)置的太大了 innodb_buffer_pool_size = 2048M # 表空間文件 重要數(shù)據(jù) innodb_data_file_path = ibdata1:1024M:autoextend # 文件IO的線程數(shù)荤胁，一般為 4铜邮，但是在 Windows 下，可以設(shè)置得較大寨蹋。 innodb_file_io_threads = 4 # 服務(wù)器有幾個邏輯CPU就設(shè)置為幾，建議用默認(rèn)設(shè)置扔茅，一般為8. innodb_thread_concurrency = 8 # 如果將此參數(shù)設(shè)置為1已旧，將在每次提交事務(wù)后將日志寫入磁盤。為提供性能召娜，可以設(shè)置為0或2运褪，但要承擔(dān)在發(fā)生故障時丟失數(shù)據(jù)的風(fēng)險。設(shè)置為0表示事務(wù)日志寫入日志文件玖瘸，而日志文件每秒刷新到磁盤一次秸讹。設(shè)置為2表示事務(wù)日志將在提交時寫入日志，但日志文件每次刷新到磁盤一次雅倒。 innodb_flush_log_at_trx_commit = 2 # 此參數(shù)確定些日志文件所用的內(nèi)存大小璃诀，以M為單位。緩沖區(qū)更大能提高性能蔑匣，但意外的故障將會丟失數(shù)據(jù).MySQL開發(fā)人員建議設(shè)置為1－8M之間 innodb_log_buffer_size = 16M # 此參數(shù)確定數(shù)據(jù)日志文件的大小劣欢，以M為單位，更大的設(shè)置可以提高性能裁良，但也會增加恢復(fù)故障數(shù)據(jù)庫所需的時間 innodb_log_file_size = 128M # 為提高性能凿将，MySQL可以以循環(huán)方式將日志文件寫到多個文件。推薦設(shè)置為3M innodb_log_files_in_group = 3 # Buffer_Pool中Dirty_Page所占的數(shù)量价脾，直接影響InnoDB的關(guān)閉時間牧抵。參數(shù)innodb_max_dirty_pages_pct 可以直接控制了Dirty_Page在Buffer_Pool中所占的比率，而且幸運的是innodb_max_dirty_pages_pct是可以動態(tài)改變的侨把。所以犀变，在關(guān)閉InnoDB之前先將innodb_max_dirty_pages_pct調(diào)小，強制數(shù)據(jù)塊Flush一段時間座硕，則能夠大大縮短 MySQL關(guān)閉的時間弛作。 innodb_max_dirty_pages_pct = 90 # InnoDB 有其內(nèi)置的死鎖檢測機制，能導(dǎo)致未完成的事務(wù)回滾华匾。但是映琳，如果結(jié)合InnoDB使用MyISAM的lock tables 語句或第三方事務(wù)引擎,則InnoDB無法識別死鎖机隙。為消除這種可能性，可以將innodb_lock_wait_timeout設(shè)置為一個整數(shù)值萨西，指示 MySQL在允許其他事務(wù)修改那些最終受事務(wù)回滾的數(shù)據(jù)之前要等待多長時間(秒數(shù)) innodb_lock_wait_timeout = 120 # 指定一個請求的最大連接時間有鹿，對于4GB左右的內(nèi)存服務(wù)器來說，可以將其設(shè)置為5-10谎脯。 wait_timeout = 10 -
-
表結(jié)構(gòu)優(yōu)化
- 數(shù)據(jù)類型選擇
數(shù)據(jù)庫操作中最為耗時的操作就是 IO 處理葱跋，大部分?jǐn)?shù)據(jù)庫操作 90% 以上的時間都花在了 IO 讀寫上面。所以盡可能減少 IO 讀寫量源梭，可以在很大程度上提高數(shù)據(jù)庫操作的性能娱俺。我們無法改變數(shù)據(jù)庫中需要存儲的數(shù)據(jù)，但是我們可以在這些數(shù)據(jù)的存儲方式方面花一些心思废麻。下面的這些關(guān)于字段類型的優(yōu)化建議主要適用于記錄條數(shù)較多荠卷，數(shù)據(jù)量較大的場景，因為精細(xì)化的數(shù)據(jù)類型設(shè)置可能帶來維護成本的提高烛愧，過度優(yōu)化也可能會帶來其他的問題：- 數(shù)字類型：非萬不得已不要使用DOUBLE油宜，不僅僅只是存儲長度的問題，同時還會存在精確性的問題怜姿。同樣慎冤，固定精度的小數(shù)，也不建議使用DECIMAL沧卢，建議乘以固定倍數(shù)轉(zhuǎn)換成整數(shù)存儲蚁堤，可以大大節(jié)省存儲空間，且不會帶來任何附加維護成本但狭。對于整數(shù)的存儲违寿，在數(shù)據(jù)量較大的情況下，建議區(qū)分開 TINYINT / INT / BIGINT 的選擇熟空，因為三者所占用的存儲空間也有很大的差別藤巢，能確定不會使用負(fù)數(shù)的字段，建議添加unsigned定義息罗。當(dāng)然掂咒，如果數(shù)據(jù)量較小的數(shù)據(jù)庫，也可以不用嚴(yán)格區(qū)分三個整數(shù)類型迈喉。
- 字符類型：非萬不得已不要使用 TEXT 數(shù)據(jù)類型绍刮，其處理方式?jīng)Q定了他的性能要低于char或者是varchar類型的處理。定長字段挨摸，建議使用 CHAR 類型孩革，不定長字段盡量使用 VARCHAR，且僅僅設(shè)定適當(dāng)?shù)淖畲箝L度得运，而不是非常隨意的給一個很大的最大長度限定膝蜈，因為不同的長度范圍锅移，MySQL也會有不一樣的存儲處理。
- 時間類型：盡量使用TIMESTAMP類型饱搏，因為其存儲空間只需要 DATETIME 類型的一半非剃。對于只需要精確到某一天的數(shù)據(jù)類型，建議使用DATE類型推沸，因為他的存儲空間只需要3個字節(jié)备绽，比TIMESTAMP還少。不建議通過INT類型類存儲一個unix timestamp 的值鬓催，因為這太不直觀肺素，會給維護帶來不必要的麻煩，同時還不會帶來任何好處宇驾。
- ENUM & SET：對于狀態(tài)字段压怠，可以嘗試使用 ENUM 來存放，因為可以極大的降低存儲空間飞苇，而且即使需要增加新的類型，只要增加于末尾蜗顽，修改結(jié)構(gòu)也不需要重建表數(shù)據(jù)布卡。如果是存放可預(yù)先定義的屬性數(shù)據(jù)呢？可以嘗試使用SET類型雇盖，即使存在多種屬性忿等，同樣可以游刃有余，同時還可以節(jié)省不小的存儲空間崔挖。
- LOB類型：強烈反對在數(shù)據(jù)庫中存放 LOB 類型數(shù)據(jù)贸街，雖然數(shù)據(jù)庫提供了這樣的功能，但這不是他所擅長的狸相，我們更應(yīng)該讓合適的工具做他擅長的事情薛匪，才能將其發(fā)揮到極致。在數(shù)據(jù)庫中存儲 LOB 數(shù)據(jù)就像讓一個多年前在學(xué)校學(xué)過一點Java的營銷專業(yè)人員來寫 Java 代碼一樣脓鹃。
- 字符編碼
字符集直接決定了數(shù)據(jù)在MySQL中的存儲編碼方式逸尖，由于同樣的內(nèi)容使用不同字符集表示所占用的空間大小會有較大的差異，所以通過使用合適的字符集瘸右，可以幫助我們盡可能減少數(shù)據(jù)量娇跟，進而減少IO操作次數(shù)。- 純拉丁字符能表示的內(nèi)容太颤，沒必要選擇 latin1 之外的其他字符編碼苞俘，因為這會節(jié)省大量的存儲空間。
- 如果我們可以確定不需要存放多種語言龄章，就沒必要非得使用UTF8或者其他UNICODE字符類型吃谣，這回造成大量的存儲空間浪費乞封。
- MySQL的數(shù)據(jù)類型可以精確到字段，所以當(dāng)我們需要大型數(shù)據(jù)庫中存放多字節(jié)數(shù)據(jù)的時候基协，可以通過對不同表不同字段使用不同的數(shù)據(jù)類型來較大程度減小數(shù)據(jù)存儲量歌亲，進而降低 IO 操作次數(shù)并提高緩存命中率。
- 適當(dāng)拆分
有些時候澜驮，我們可能會希望將一個完整的對象對應(yīng)于一張數(shù)據(jù)庫表陷揪，這對于應(yīng)用程序開發(fā)來說是很有好的，但是有些時候可能會在性能上帶來較大的問題杂穷。
當(dāng)我們的表中存在類似于 TEXT 或者是很大的 VARCHAR類型的大字段的時候悍缠，如果我們大部分訪問這張表的時候都不需要這個字段，我們就該義無反顧的將其拆分到另外的獨立表中耐量，以減少常用數(shù)據(jù)所占用的存儲空間飞蚓。這樣做的一個明顯好處就是每個數(shù)據(jù)塊中可以存儲的數(shù)據(jù)條數(shù)可以大大增加，既減少物理 IO 次數(shù)廊蜒，也能大大提高內(nèi)存中的緩存命中率趴拧。
上面幾點的優(yōu)化都是為了減少每條記錄的存儲空間大小，讓每個數(shù)據(jù)庫中能夠存儲更多的記錄條數(shù)山叮，以達(dá)到減少 IO 操作次數(shù)著榴，提高緩存命中率。下面這個優(yōu)化建議可能很多開發(fā)人員都會覺得不太理解屁倔，因為這是典型的反范式設(shè)計脑又，而且也和上面的幾點優(yōu)化建議的目標(biāo)相違背。 - 適度冗余
為什么我們要冗余锐借？這不是增加了每條數(shù)據(jù)的大小问麸，減少了每個數(shù)據(jù)塊可存放記錄條數(shù)嗎？
確實钞翔，這樣做是會增大每條記錄的大小严卖，降低每條記錄中可存放數(shù)據(jù)的條數(shù)，但是在有些場景下我們?nèi)匀贿€是不得不這樣做：
被頻繁引用且只能通過 Join 2張（或者更多）大表的方式才能得到的獨立小字段
這樣的場景由于每次Join僅僅只是為了取得某個小字段的值布轿，Join到的記錄又大妄田，會造成大量不必要的 IO，完全可以通過空間換取時間的方式來優(yōu)化驮捍。不過疟呐，冗余的同時需要確保數(shù)據(jù)的一致性不會遭到破壞，確保更新的同時冗余字段也被更新东且。 - 盡量使用 NOT NULL
NULL 類型比較特殊启具，SQL 難優(yōu)化。雖然 MySQL NULL類型和 Oracle 的NULL 有差異珊泳，會進入索引中鲁冯，但如果是一個組合索引拷沸，那么這個NULL 類型的字段會極大影響整個索引的效率。此外薯演，NULL 在索引中的處理也是特殊的撞芍，也會占用額外的存放空間。
很多人覺得 NULL 會節(jié)省一些空間跨扮，所以盡量讓NULL來達(dá)到節(jié)省IO的目的序无，但是大部分時候這會適得其反，雖然空間上可能確實有一定節(jié)省衡创，倒是帶來了很多其他的優(yōu)化問題帝嗡，不但沒有將IO量省下來，反而加大了SQL的IO量璃氢。所以盡量確保 DEFAULT 值不是 NULL哟玷，也是一個很好的表結(jié)構(gòu)設(shè)計優(yōu)化習(xí)慣。
- 數(shù)據(jù)類型選擇
-
慢查詢優(yōu)化
- 對查詢進行優(yōu)化一也，應(yīng)盡量避免全表掃描巢寡，首先應(yīng)考慮在 where 及 order by 涉及的列上建立索引
- 應(yīng)盡量避免在 where 子句中使用 != 或 <> 操作符，否則引擎將放棄使用索引而進行全表掃描
- 應(yīng)盡量避免在 where 子句中對字段 null 值判斷椰苟，否則將導(dǎo)致引擎放棄使用索引而進行全表掃描抑月，如：
select id from t where num is null; # 可以在num上設(shè)置默認(rèn)值0，確保表中num列沒有null值尊剔，然后這樣查詢： select id from t where num = 0;- 應(yīng)盡量避免在 where 子句中使用 or 來連接條件，否則將導(dǎo)致引擎放棄使用索引而進行全表掃描菱皆，如：
select id from t where num = 10 or num = 20; # 可以這樣查詢: select id from t where num = 10 union all select id from t where num = 20;- 下面的查詢也將導(dǎo)致全表掃描：
select id from t where name like '%abc%'; # 若要提高效率须误，可以考慮全文檢索- in 和 not in 也要慎用，否則會導(dǎo)致全表掃描仇轻，如：
select id from t where num in(1,2,3); # 對于連續(xù)的數(shù)值京痢，能用 between 就不要用 in 了： select id from t where num between 1 and 3;- 如果在 where 子句中使用參數(shù)，也會導(dǎo)致全表掃描篷店。因為SQL只有在運行時才會解析局部變量祭椰，但優(yōu)化程序不能將訪問計劃的選擇推遲到運行時，它必須在編譯時進行選擇疲陕，然而方淤，如果在編譯時建立訪問計劃，變量的值還是未知的蹄殃，因而無法作為索引選擇的輸入項携茂。如以下語句將進行全表掃描：
select id from t where num=@num; # 可以改為強制查詢使用索引： select id from t with(index(索引名)) where num=@num;- 應(yīng)盡量避免在 where 子句中對字段進行表達(dá)式操作，這將導(dǎo)致引擎放棄使用索引而進行全表掃描诅岩。如：
select id from t where num/2=100; 應(yīng)改為： select id from t where num=100*2;- 應(yīng)盡量避免在 where 子句中對字段進行函數(shù)操作讳苦，這將導(dǎo)致引擎放棄使用索引而進行全表掃描带膜。如：
select id from t where substring(name,1,3)='abc'; 查詢name以abc開頭的id select id from t where datediff(dat,createdate,'2005-11-30')=0 查詢'2005-11-30'生成的id應(yīng)改為： select id from t where name like'abc%'; select id from t where createdate>='2005-11-30' and createdate<'2005-12-1';- 不要在 where 子句中的"="左邊進行函數(shù)、算數(shù)運算或其他表達(dá)式運算鸳谜，否則系統(tǒng)將可能無法正確使用索引膝藕。
- 在使用索引字段作為條件時，如果該索引是復(fù)合索引咐扭，那么必須使用到該索引中的第一個字段作為條件才能保證系統(tǒng)使用該索引芭挽，否則該索引將不會被使用，并且應(yīng)盡可能的讓字段順序與索引順序相一致草描。
- 不要寫一些沒有意義的查詢览绿，如需要生成一個空表結(jié)構(gòu)：
select col1,col2 into #t from t where 1=0; # 這類代碼不會返回任何結(jié)果集，但是會消耗系統(tǒng)資源穗慕，應(yīng)改成這樣： create table #t(...);- 很多時候用 exists 代替 in 是一個好的選擇：
select num from a where num in(select num from b); 用下面的語句替換： select num from a where exists(select 1 from b where num=a.num); # 如果只是檢查表中是否有數(shù)據(jù)效率上 1>anycol>*- 并不是所有索引對查詢都有效饿敲，SQL是根據(jù)表中數(shù)據(jù)來進行查詢優(yōu)化的，當(dāng)索引列有大量數(shù)據(jù)重復(fù)時逛绵，SQL查詢可能不會去利用索引怀各，如一表中有字段sex，male术浪、female幾乎各一半瓢对，那么即使在sex上建了索引也對查詢效率起不了作用。
- 索引并不是越多越好胰苏，索引固然可以提高相應(yīng)的 select 的效率硕蛹，但同時也降低了 insert 和 update 的效率，因為 insert 和 update 時有可能會重建索引硕并，所以怎么建索引也需要慎重考慮法焰，視具體情況而定。一個表的索引數(shù)最好不要超過6個倔毙，若太多則應(yīng)考慮一些不常使用到的列上建立索引是否有必要埃仪。
- 應(yīng)盡可能的避免更新 clustered 索引數(shù)據(jù)列，因為 clustered 索引數(shù)據(jù)列的順序就是表記錄的物理存儲順序陕赃，一旦該列值改變將導(dǎo)致整個表記錄的順序的調(diào)整卵蛉，會耗費相當(dāng)大的資源。若應(yīng)用系統(tǒng)需要頻繁更新 clustered 索引數(shù)據(jù)列么库，那么需要考慮是否應(yīng)將該索引建為 clustered 索引傻丝。
- 盡量使用數(shù)字型字段，若只含數(shù)值信息的字段盡量不要設(shè)計為字符型诉儒，這會降低查詢和連接的性能桑滩，并會增加存儲開銷。這是因為引擎在處理查詢和連接時會逐個比較字符串中的每一個字符，而對于數(shù)字型而言只需要比較一次就夠了运准。
- 盡可能地使用 varchar/nvarchar 代替 char/nchar幌氮，因為首先變長字段存儲空間小，可以節(jié)省存儲空間胁澳，其次對于查詢來說该互，在一個相對較小的字段內(nèi)搜索效率顯然要高些。
- 任何地方都不要使用 select * from t;用具體的字段列代替"*"韭畸，不要返回用不到的任何字段宇智。
- 盡量使用表變量來代替臨時表。如果表變量包含大量數(shù)據(jù)胰丁，請注意索引非常有限(只有主鍵索引).
- 避免頻繁創(chuàng)建和刪除臨時表随橘，以減少系統(tǒng)表資源的消耗。
- 臨時表并不是不可使用锦庸，適當(dāng)?shù)厥褂盟鼈兛梢允鼓承├谈行Щ幔纾?dāng)需要重復(fù)引用大型表或常用表中的某個數(shù)據(jù)集時甘萧。但是萝嘁，對于一次性事件，最好使用導(dǎo)出表扬卷。
- 在新建臨時表時牙言，如果一次性插入數(shù)據(jù)量很大，那么可以使用 select into 代替 create table,避免造成大量log怪得。
- 如果使用到了臨時表咱枉，在存儲過程的最后務(wù)必將所有的臨時表顯示刪除，先 truncate table,然后 drop table,這樣可以避免系統(tǒng)表的較長時間鎖定徒恋。
- 盡量避免使用游標(biāo)蚕断，因為游標(biāo)的效率較差，如果游標(biāo)操作的數(shù)據(jù)超過一萬行因谎，那么就應(yīng)該考慮改寫基括。
- 使用基于游標(biāo)的方法或臨時表方法之前颜懊，應(yīng)先尋找基于集的解決方案來解決問題财岔，基于集的方法通常更有效。
- 與臨時表一樣河爹，游標(biāo)并不是不可使用匠璧。對于小型數(shù)據(jù)集使用 FAST_FORWARD 游標(biāo)通常要優(yōu)于其他逐行處理方法，尤其是在必須引用幾個表才能獲得所需的數(shù)據(jù)時咸这。在結(jié)果集中包含“合計”的例程通常要比使用游標(biāo)執(zhí)行的速度快夷恍，如果開發(fā)時間允許，基于游標(biāo)的方法和基于集的方法都可以嘗試一下媳维，看哪一種方法的效果更好酿雪。
- 在所有的存儲過程和觸發(fā)器的開始處設(shè)置 SET NOCOUNT ON遏暴，在結(jié)束時設(shè)置 SET NOCOUNT OFF。無需在執(zhí)行存儲過程和觸發(fā)器的每個語句后向客戶端發(fā)送 DONE_IN_PROC 消息指黎。
- 盡量避免向客戶端返回大量數(shù)據(jù)朋凉，若數(shù)據(jù)量過大，應(yīng)該考慮相應(yīng)需求是否合理醋安。
- 盡量避免大事務(wù)操作杂彭，提高系統(tǒng)并發(fā)能力。
注意事項：
select Count (*)和Select Count(1)以及Select Count(column)區(qū)別 一般情況下吓揪，Select Count (*)和Select Count(1)兩著返回結(jié)果是一樣的 假如表沒有主鍵(Primary key), 那么count(1)比count(*)快亲怠， 如果有主鍵的話，那主鍵作為count的條件時候count(主鍵)最快 如果你的表只有一個字段的話那count(*)就是最快的 count(*) 跟 count(1) 的結(jié)果一樣柠辞，都包括對NULL的統(tǒng)計团秽，而count(column) 是不包括NULL的統(tǒng)計索引列上計算引起的索引失效及優(yōu)化措施以及注意事項 創(chuàng)建索引、優(yōu)化查詢以便達(dá)到更好的查詢優(yōu)化效果钾腺。但實際上徙垫，MySQL有時并不按我們設(shè)計的那樣執(zhí)行查詢。MySQL是根據(jù)統(tǒng)計信息來生成執(zhí)行計劃的放棒，這就涉及索引及索引的刷選率姻报，表數(shù)據(jù)量，還有一些額外的因素间螟。 Each table index is queried, and the best index is used unless the optimizer believes that it is more efficient to use a table scan. At one time, a scan was used based on whether the best index spanned more than 30% of the table, but a fixed percentage no longer determines the choice between using an index or a scan. The optimizer now is more complex and bases its estimate on additional factors such as table size, number of rows, and I/O block size. 簡而言之吴旋，當(dāng)MYSQL認(rèn)為符合條件的記錄在30%以上，它就不會再使用索引厢破，因為mysql認(rèn)為走索引的代價比不用索引代價大荣瑟，所以優(yōu)化器選擇了自己認(rèn)為代價最小的方式。事實也的確如此 是MYSQL認(rèn)為記錄是30%以上摩泪，而不是實際MYSQL去查完再決定的笆焰。都查完了，還用什么索引凹印嚷掠？！ MYSQL會先估算荞驴，然后決定是否使用索引不皆。
