如何設(shè)置合理的mysql的參數(shù)

[client]

目前創(chuàng)新互聯(lián)已為上千多家的企業(yè)提供了網(wǎng)站建設(shè)、域名、網(wǎng)頁空間、網(wǎng)站托管、服務(wù)器租用、企業(yè)網(wǎng)站設(shè)計(jì)、同德網(wǎng)站維護(hù)等服務(wù)，公司將堅(jiān)持客戶導(dǎo)向、應(yīng)用為本的策略，正道將秉承"和諧、參與、激情"的文化，與客戶和合作伙伴齊心協(xié)力一起成長(zhǎng)，共同發(fā)展。

port = 3306

socket = /tmp/mysql.sock

[mysqld]

port = 3306

socket = /tmp/mysql.sock

basedir = /usr/local/mysql

datadir = /data/mysql

pid-file = /data/mysql/mysql.pid

user = mysql

bind-address = 0.0.0.0

server-id = 1 #表示是本機(jī)的序號(hào)為1,一般來講就是master的意思

skip-name-resolve

# 禁止MySQL對(duì)外部連接進(jìn)行DNS解析，使用這一選項(xiàng)可以消除MySQL進(jìn)行DNS解析的時(shí)間。但需要注意，如果開啟該選項(xiàng)，

# 則所有遠(yuǎn)程主機(jī)連接授權(quán)都要使用IP地址方式，否則MySQL將無法正常處理連接請(qǐng)求

#skip-networking

back_log = 600

# MySQL能有的連接數(shù)量。當(dāng)主要MySQL線程在一個(gè)很短時(shí)間內(nèi)得到非常多的連接請(qǐng)求，這就起作用，

# 然后主線程花些時(shí)間(盡管很短)檢查連接并且啟動(dòng)一個(gè)新線程。back_log值指出在MySQL暫時(shí)停止回答新請(qǐng)求之前的短時(shí)間內(nèi)多少個(gè)請(qǐng)求可以被存在堆棧中。

# 如果期望在一個(gè)短時(shí)間內(nèi)有很多連接，你需要增加它。也就是說，如果MySQL的連接數(shù)據(jù)達(dá)到max_connections時(shí)，新來的請(qǐng)求將會(huì)被存在堆棧中，

# 以等待某一連接釋放資源，該堆棧的數(shù)量即back_log，如果等待連接的數(shù)量超過back_log，將不被授予連接資源。

# 另外，這值（back_log）限于您的操作系統(tǒng)對(duì)到來的TCP/IP連接的偵聽隊(duì)列的大小。

# 你的操作系統(tǒng)在這個(gè)隊(duì)列大小上有它自己的限制（可以檢查你的OS文檔找出這個(gè)變量的最大值），試圖設(shè)定back_log高于你的操作系統(tǒng)的限制將是無效的。

max_connections = 1000

#

MySQL的最大連接數(shù)，如果服務(wù)器的并發(fā)連接請(qǐng)求量比較大，建議調(diào)高此值，以增加并行連接數(shù)量，當(dāng)然這建立在機(jī)器能支撐的情況下，因?yàn)槿绻B接數(shù)越多，

介于MySQL會(huì)為每個(gè)連接提供連接緩沖區(qū)，就會(huì)開銷越多的內(nèi)存，所以要適當(dāng)調(diào)整該值，不能盲目提高設(shè)值。可以過'conn%'通配符查看當(dāng)前狀態(tài)的連接

數(shù)量，以定奪該值的大小。

max_connect_errors = 6000

# 對(duì)于同一主機(jī)，如果有超出該參數(shù)值個(gè)數(shù)的中斷錯(cuò)誤連接，則該主機(jī)將被禁止連接。如需對(duì)該主機(jī)進(jìn)行解禁，執(zhí)行：FLUSH HOST。

open_files_limit = 65535

# MySQL打開的文件描述符限制，默認(rèn)最小1024;當(dāng)open_files_limit沒有被配置的時(shí)候，比較max_connections*5和ulimit -n的值，哪個(gè)大用哪個(gè)，

# 當(dāng)open_file_limit被配置的時(shí)候，比較open_files_limit和max_connections*5的值，哪個(gè)大用哪個(gè)。

table_open_cache = 128

# MySQL每打開一個(gè)表，都會(huì)讀入一些數(shù)據(jù)到table_open_cache緩存中，當(dāng)MySQL在這個(gè)緩存中找不到相應(yīng)信息時(shí)，才會(huì)去磁盤上讀取。默認(rèn)值64

# 假定系統(tǒng)有200個(gè)并發(fā)連接，則需將此參數(shù)設(shè)置為200*N(N為每個(gè)連接所需的文件描述符數(shù)目)；

# 當(dāng)把table_open_cache設(shè)置為很大時(shí)，如果系統(tǒng)處理不了那么多文件描述符，那么就會(huì)出現(xiàn)客戶端失效，連接不上

max_allowed_packet = 4M

# 接受的數(shù)據(jù)包大小；增加該變量的值十分安全，這是因?yàn)閮H當(dāng)需要時(shí)才會(huì)分配額外內(nèi)存。例如，僅當(dāng)你發(fā)出長(zhǎng)查詢或MySQLd必須返回大的結(jié)果行時(shí)MySQLd才會(huì)分配更多內(nèi)存。

# 該變量之所以取較小默認(rèn)值是一種預(yù)防措施，以捕獲客戶端和服務(wù)器之間的錯(cuò)誤信息包，并確保不會(huì)因偶然使用大的信息包而導(dǎo)致內(nèi)存溢出。

binlog_cache_size = 1M

# 一個(gè)事務(wù)，在沒有提交的時(shí)候，產(chǎn)生的日志，記錄到Cache中；等到事務(wù)提交需要提交的時(shí)候，則把日志持久化到磁盤。默認(rèn)binlog_cache_size大小32K

max_heap_table_size = 8M

# 定義了用戶可以創(chuàng)建的內(nèi)存表(memory table)的大小。這個(gè)值用來計(jì)算內(nèi)存表的最大行數(shù)值。這個(gè)變量支持動(dòng)態(tài)改變

tmp_table_size = 16M

# MySQL的heap（堆積）表緩沖大小。所有聯(lián)合在一個(gè)DML指令內(nèi)完成，并且大多數(shù)聯(lián)合甚至可以不用臨時(shí)表即可以完成。

# 大多數(shù)臨時(shí)表是基于內(nèi)存的(HEAP)表。具有大的記錄長(zhǎng)度的臨時(shí)表 (所有列的長(zhǎng)度的和)或包含BLOB列的表存儲(chǔ)在硬盤上。

#

如果某個(gè)內(nèi)部heap（堆積）表大小超過tmp_table_size，MySQL可以根據(jù)需要自動(dòng)將內(nèi)存中的heap表改為基于硬盤的MyISAM表。

還可以通過設(shè)置tmp_table_size選項(xiàng)來增加臨時(shí)表的大小。也就是說，如果調(diào)高該值，MySQL同時(shí)將增加heap表的大小，可達(dá)到提高聯(lián)接查

詢速度的效果

read_buffer_size = 2M

# MySQL讀入緩沖區(qū)大小。對(duì)表進(jìn)行順序掃描的請(qǐng)求將分配一個(gè)讀入緩沖區(qū)，MySQL會(huì)為它分配一段內(nèi)存緩沖區(qū)。read_buffer_size變量控制這一緩沖區(qū)的大小。

# 如果對(duì)表的順序掃描請(qǐng)求非常頻繁，并且你認(rèn)為頻繁掃描進(jìn)行得太慢，可以通過增加該變量值以及內(nèi)存緩沖區(qū)大小提高其性能

read_rnd_buffer_size = 8M

# MySQL的隨機(jī)讀緩沖區(qū)大小。當(dāng)按任意順序讀取行時(shí)(例如，按照排序順序)，將分配一個(gè)隨機(jī)讀緩存區(qū)。進(jìn)行排序查詢時(shí)，

# MySQL會(huì)首先掃描一遍該緩沖，以避免磁盤搜索，提高查詢速度，如果需要排序大量數(shù)據(jù)，可適當(dāng)調(diào)高該值。但MySQL會(huì)為每個(gè)客戶連接發(fā)放該緩沖空間，所以應(yīng)盡量適當(dāng)設(shè)置該值，以避免內(nèi)存開銷過大

sort_buffer_size = 8M

# MySQL執(zhí)行排序使用的緩沖大小。如果想要增加ORDER BY的速度，首先看是否可以讓MySQL使用索引而不是額外的排序階段。

# 如果不能，可以嘗試增加sort_buffer_size變量的大小

join_buffer_size = 8M

# 聯(lián)合查詢操作所能使用的緩沖區(qū)大小，和sort_buffer_size一樣，該參數(shù)對(duì)應(yīng)的分配內(nèi)存也是每連接獨(dú)享

thread_cache_size = 8

# 這個(gè)值（默認(rèn)8）表示可以重新利用保存在緩存中線程的數(shù)量，當(dāng)斷開連接時(shí)如果緩存中還有空間，那么客戶端的線程將被放到緩存中，

# 如果線程重新被請(qǐng)求，那么請(qǐng)求將從緩存中讀取,如果緩存中是空的或者是新的請(qǐng)求，那么這個(gè)線程將被重新創(chuàng)建,如果有很多新的線程，

# 增加這個(gè)值可以改善系統(tǒng)性能.通過比較Connections和Threads_created狀態(tài)的變量，可以看到這個(gè)變量的作用。(–表示要調(diào)整的值)

# 根據(jù)物理內(nèi)存設(shè)置規(guī)則如下：

# 1G — 8

# 2G — 16

# 3G — 32

# 大于3G — 64

query_cache_size = 8M

#MySQL的查詢緩沖大小（從4.0.1開始，MySQL提供了查詢緩沖機(jī)制）使用查詢緩沖，MySQL將SELECT語句和查詢結(jié)果存放在緩沖區(qū)中，

# 今后對(duì)于同樣的SELECT語句（區(qū)分大小寫），將直接從緩沖區(qū)中讀取結(jié)果。根據(jù)MySQL用戶手冊(cè)，使用查詢緩沖最多可以達(dá)到238%的效率。

# 通過檢查狀態(tài)值'Qcache_%'，可以知道query_cache_size設(shè)置是否合理：如果Qcache_lowmem_prunes的值非常大，則表明經(jīng)常出現(xiàn)緩沖不夠的情況，

# 如果Qcache_hits的值也非常大，則表明查詢緩沖使用非常頻繁，此時(shí)需要增加緩沖大小；如果Qcache_hits的值不大，則表明你的查詢重復(fù)率很低，

# 這種情況下使用查詢緩沖反而會(huì)影響效率，那么可以考慮不用查詢緩沖。此外，在SELECT語句中加入SQL_NO_CACHE可以明確表示不使用查詢緩沖

query_cache_limit = 2M

#指定單個(gè)查詢能夠使用的緩沖區(qū)大小，默認(rèn)1M

key_buffer_size = 4M

#指定用于索引的緩沖區(qū)大小，增加它可得到更好處理的索引(對(duì)所有讀和多重寫)，到你能負(fù)擔(dān)得起那樣多。如果你使它太大，

# 系統(tǒng)將開始換頁并且真的變慢了。對(duì)于內(nèi)存在4GB左右的服務(wù)器該參數(shù)可設(shè)置為384M或512M。通過檢查狀態(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%'獲得)。注意：該參數(shù)值設(shè)置的過大反而會(huì)是服務(wù)器整體效率降低

ft_min_word_len = 4

# 分詞詞匯最小長(zhǎng)度，默認(rèn)4

transaction_isolation = REPEATABLE-READ

# MySQL支持4種事務(wù)隔離級(jí)別，他們分別是：

# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE.

# 如沒有指定，MySQL默認(rèn)采用的是REPEATABLE-READ，ORACLE默認(rèn)的是READ-COMMITTED

log_bin = mysql-bin

binlog_format = mixed

expire_logs_days = 30 #超過30天的binlog刪除

log_error = /data/mysql/mysql-error.log #錯(cuò)誤日志路徑

slow_query_log = 1

long_query_time = 1 #慢查詢時(shí)間 超過1秒則為慢查詢

slow_query_log_file = /data/mysql/mysql-slow.log

performance_schema = 0

explicit_defaults_for_timestamp

#lower_case_table_names = 1 #不區(qū)分大小寫

skip-external-locking #MySQL選項(xiàng)以避免外部鎖定。該選項(xiàng)默認(rèn)開啟

default-storage-engine = InnoDB #默認(rèn)存儲(chǔ)引擎

innodb_file_per_table = 1

# InnoDB為獨(dú)立表空間模式，每個(gè)數(shù)據(jù)庫的每個(gè)表都會(huì)生成一個(gè)數(shù)據(jù)空間

# 獨(dú)立表空間優(yōu)點(diǎn)：

# 1．每個(gè)表都有自已獨(dú)立的表空間。

# 2．每個(gè)表的數(shù)據(jù)和索引都會(huì)存在自已的表空間中。

# 3．可以實(shí)現(xiàn)單表在不同的數(shù)據(jù)庫中移動(dòng)。

# 4．空間可以回收（除drop table操作處，表空不能自已回收）

# 缺點(diǎn)：

# 單表增加過大，如超過100G

# 結(jié)論：

# 共享表空間在Insert操作上少有優(yōu)勢(shì)。其它都沒獨(dú)立表空間表現(xiàn)好。當(dāng)啟用獨(dú)立表空間時(shí)，請(qǐng)合理調(diào)整：innodb_open_files

innodb_open_files = 500

# 限制Innodb能打開的表的數(shù)據(jù)，如果庫里的表特別多的情況，請(qǐng)?jiān)黾舆@個(gè)。這個(gè)值默認(rèn)是300

innodb_buffer_pool_size = 64M

# InnoDB使用一個(gè)緩沖池來保存索引和原始數(shù)據(jù), 不像MyISAM.

# 這里你設(shè)置越大,你在存取表里面數(shù)據(jù)時(shí)所需要的磁盤I/O越少.

# 在一個(gè)獨(dú)立使用的數(shù)據(jù)庫服務(wù)器上,你可以設(shè)置這個(gè)變量到服務(wù)器物理內(nèi)存大小的80%

# 不要設(shè)置過大,否則,由于物理內(nèi)存的競(jìng)爭(zhēng)可能導(dǎo)致操作系統(tǒng)的換頁顛簸.

# 注意在32位系統(tǒng)上你每個(gè)進(jìn)程可能被限制在 2-3.5G 用戶層面內(nèi)存限制,

# 所以不要設(shè)置的太高.

innodb_write_io_threads = 4

innodb_read_io_threads = 4

# innodb使用后臺(tái)線程處理數(shù)據(jù)頁上的讀寫 I/O(輸入輸出)請(qǐng)求,根據(jù)你的 CPU 核數(shù)來更改,默認(rèn)是4

# 注:這兩個(gè)參數(shù)不支持動(dòng)態(tài)改變,需要把該參數(shù)加入到my.cnf里，修改完后重啟MySQL服務(wù),允許值的范圍從 1-64

innodb_thread_concurrency = 0

# 默認(rèn)設(shè)置為 0,表示不限制并發(fā)數(shù)，這里推薦設(shè)置為0，更好去發(fā)揮CPU多核處理能力，提高并發(fā)量

innodb_purge_threads = 1

# InnoDB中的清除操作是一類定期回收無用數(shù)據(jù)的操作。在之前的幾個(gè)版本中，清除操作是主線程的一部分，這意味著運(yùn)行時(shí)它可能會(huì)堵塞其它的數(shù)據(jù)庫操作。

# 從MySQL5.5.X版本開始，該操作運(yùn)行于獨(dú)立的線程中,并支持更多的并發(fā)數(shù)。用戶可通過設(shè)置innodb_purge_threads配置參數(shù)來選擇清除操作是否使用單

# 獨(dú)線程,默認(rèn)情況下參數(shù)設(shè)置為0(不使用單獨(dú)線程),設(shè)置為 1 時(shí)表示使用單獨(dú)的清除線程。建議為1

innodb_flush_log_at_trx_commit = 2

# 0：如果innodb_flush_log_at_trx_commit的值為0,log buffer每秒就會(huì)被刷寫日志文件到磁盤，提交事務(wù)的時(shí)候不做任何操作（執(zhí)行是由mysql的master thread線程來執(zhí)行的。

# 主線程中每秒會(huì)將重做日志緩沖寫入磁盤的重做日志文件(REDO LOG)中。不論事務(wù)是否已經(jīng)提交）默認(rèn)的日志文件是ib_logfile0,ib_logfile1

# 1：當(dāng)設(shè)為默認(rèn)值1的時(shí)候，每次提交事務(wù)的時(shí)候，都會(huì)將log buffer刷寫到日志。

# 2：如果設(shè)為2,每次提交事務(wù)都會(huì)寫日志，但并不會(huì)執(zhí)行刷的操作。每秒定時(shí)會(huì)刷到日志文件。要注意的是，并不能保證100%每秒一定都會(huì)刷到磁盤，這要取決于進(jìn)程的調(diào)度。

# 每次事務(wù)提交的時(shí)候?qū)?shù)據(jù)寫入事務(wù)日志，而這里的寫入僅是調(diào)用了文件系統(tǒng)的寫入操作，而文件系統(tǒng)是有 緩存的，所以這個(gè)寫入并不能保證數(shù)據(jù)已經(jīng)寫入到物理磁盤

# 默認(rèn)值1是為了保證完整的ACID。當(dāng)然，你可以將這個(gè)配置項(xiàng)設(shè)為1以外的值來換取更高的性能，但是在系統(tǒng)崩潰的時(shí)候，你將會(huì)丟失1秒的數(shù)據(jù)。

# 設(shè)為0的話，mysqld進(jìn)程崩潰的時(shí)候，就會(huì)丟失最后1秒的事務(wù)。設(shè)為2,只有在操作系統(tǒng)崩潰或者斷電的時(shí)候才會(huì)丟失最后1秒的數(shù)據(jù)。InnoDB在做恢復(fù)的時(shí)候會(huì)忽略這個(gè)值。

# 總結(jié)

# 設(shè)為1當(dāng)然是最安全的，但性能頁是最差的（相對(duì)其他兩個(gè)參數(shù)而言，但不是不能接受）。如果對(duì)數(shù)據(jù)一致性和完整性要求不高，完全可以設(shè)為2，如果只最求性能，例如高并發(fā)寫的日志服務(wù)器，設(shè)為0來獲得更高性能

innodb_log_buffer_size = 2M

# 此參數(shù)確定些日志文件所用的內(nèi)存大小，以M為單位。緩沖區(qū)更大能提高性能，但意外的故障將會(huì)丟失數(shù)據(jù)。MySQL開發(fā)人員建議設(shè)置為1－8M之間

innodb_log_file_size = 32M

# 此參數(shù)確定數(shù)據(jù)日志文件的大小，更大的設(shè)置可以提高性能，但也會(huì)增加恢復(fù)故障數(shù)據(jù)庫所需的時(shí)間

innodb_log_files_in_group = 3

# 為提高性能，MySQL可以以循環(huán)方式將日志文件寫到多個(gè)文件。推薦設(shè)置為3

innodb_max_dirty_pages_pct = 90

# innodb主線程刷新緩存池中的數(shù)據(jù)，使臟數(shù)據(jù)比例小于90%

innodb_lock_wait_timeout = 120

# InnoDB事務(wù)在被回滾之前可以等待一個(gè)鎖定的超時(shí)秒數(shù)。InnoDB在它自己的鎖定表中自動(dòng)檢測(cè)事務(wù)死鎖并且回滾事務(wù)。InnoDB用LOCK TABLES語句注意到鎖定設(shè)置。默認(rèn)值是50秒

bulk_insert_buffer_size = 8M

# 批量插入緩存大小， 這個(gè)參數(shù)是針對(duì)MyISAM存儲(chǔ)引擎來說的。適用于在一次性插入100-1000+條記錄時(shí)， 提高效率。默認(rèn)值是8M。可以針對(duì)數(shù)據(jù)量的大小，翻倍增加。

myisam_sort_buffer_size = 8M

# MyISAM設(shè)置恢復(fù)表之時(shí)使用的緩沖區(qū)的尺寸，當(dāng)在REPAIR TABLE或用CREATE INDEX創(chuàng)建索引或ALTER TABLE過程中排序 MyISAM索引分配的緩沖區(qū)

myisam_max_sort_file_size = 10G

# 如果臨時(shí)文件會(huì)變得超過索引，不要使用快速排序索引方法來創(chuàng)建一個(gè)索引。注釋：這個(gè)參數(shù)以字節(jié)的形式給出

myisam_repair_threads = 1

# 如果該值大于1，在Repair by sorting過程中并行創(chuàng)建MyISAM表索引(每個(gè)索引在自己的線程內(nèi))

interactive_timeout = 28800

# 服務(wù)器關(guān)閉交互式連接前等待活動(dòng)的秒數(shù)。交互式客戶端定義為在mysql_real_connect()中使用CLIENT_INTERACTIVE選項(xiàng)的客戶端。默認(rèn)值：28800秒（8小時(shí)）

wait_timeout = 28800

# 服務(wù)器關(guān)閉非交互連接之前等待活動(dòng)的秒數(shù)。在線程啟動(dòng)時(shí)，根據(jù)全局wait_timeout值或全局interactive_timeout值初始化會(huì)話wait_timeout值，

# 取決于客戶端類型(由mysql_real_connect()的連接選項(xiàng)CLIENT_INTERACTIVE定義)。參數(shù)默認(rèn)值：28800秒（8小時(shí)）

# MySQL服務(wù)器所支持的最大連接數(shù)是有上限的，因?yàn)槊總€(gè)連接的建立都會(huì)消耗內(nèi)存，因此我們希望客戶端在連接到MySQL Server處理完相應(yīng)的操作后，

# 應(yīng)該斷開連接并釋放占用的內(nèi)存。如果你的MySQL Server有大量的閑置連接，他們不僅會(huì)白白消耗內(nèi)存，而且如果連接一直在累加而不斷開，

# 最終肯定會(huì)達(dá)到MySQL Server的連接上限數(shù)，這會(huì)報(bào)'too many connections'的錯(cuò)誤。對(duì)于wait_timeout的值設(shè)定，應(yīng)該根據(jù)系統(tǒng)的運(yùn)行情況來判斷。

# 在系統(tǒng)運(yùn)行一段時(shí)間后，可以通過show processlist命令查看當(dāng)前系統(tǒng)的連接狀態(tài)，如果發(fā)現(xiàn)有大量的sleep狀態(tài)的連接進(jìn)程，則說明該參數(shù)設(shè)置的過大，

# 可以進(jìn)行適當(dāng)?shù)恼{(diào)整小些。要同時(shí)設(shè)置interactive_timeout和wait_timeout才會(huì)生效。

[mysqldump]

quick

max_allowed_packet = 16M #服務(wù)器發(fā)送和接受的最大包長(zhǎng)度

[myisamchk]

key_buffer_size = 8M

sort_buffer_size = 8M

read_buffer = 4M

write_buffer = 4M

如何對(duì)mysql服務(wù)器進(jìn)行調(diào)優(yōu)

1、選取最適用的字段屬性

MySQL 可以很好的支持大數(shù)據(jù)量的存取，但是一般說來，數(shù)據(jù)庫中的表越小，在它上面執(zhí)行的查詢也就會(huì)越快。因此，在創(chuàng)建表的時(shí)候，為了獲得更好的性能，我們可以將表中字段的寬度設(shè)得盡可能小。例如，在定義郵政編碼這個(gè)字段時(shí)，如果將其設(shè)置為CHAR(255),顯然給數(shù)據(jù)庫增加了不必要的空間，甚至使用VARCHAR這種類型也是多余的，因?yàn)镃HAR(6)就可以很好的完成任務(wù)了。同樣的，如果可以的話，我們應(yīng)該使用MEDIUMINT而不是BIGIN來定義整型字段。

另外一個(gè)提高效率的方法是在可能的情況下，應(yīng)該盡量把字段設(shè)置為NOT NULL，這樣在將來執(zhí)行查詢的時(shí)候，數(shù)據(jù)庫不用去比較NULL值。

對(duì)于某些文本字段，例如“省份”或者“性別”，我們可以將它們定義為ENUM類型。因?yàn)樵贛ySQL中，ENUM類型被當(dāng)作數(shù)值型數(shù)據(jù)來處理，而數(shù)值型數(shù)據(jù)被處理起來的速度要比文本類型快得多。這樣，我們又可以提高數(shù)據(jù)庫的性能。

2、使用連接（JOIN）來代替子查詢(Sub-Queries)

MySQL 從4.1開始支持SQL的子查詢。這個(gè)技術(shù)可以使用SELECT語句來創(chuàng)建一個(gè)單列的查詢結(jié)果，然后把這個(gè)結(jié)果作為過濾條件用在另一個(gè)查詢中。例如，我們要將客戶基本信息表中沒有任何訂單的客戶刪除掉，就可以利用子查詢先從銷售信息表中將所有發(fā)出訂單的客戶ID取出來，然后將結(jié)果傳遞給主查詢，如下所示：

DELETE FROM customerinfo

WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )

使用子查詢可以一次性的完成很多邏輯上需要多個(gè)步驟才能完成的SQL操作，同時(shí)也可以避免事務(wù)或者表鎖死，并且寫起來也很容易。但是，有些情況下，子查詢可以被更有效率的連接（JOIN）.. 替代。例如，假設(shè)我們要將所有沒有訂單記錄的用戶取出來，可以用下面這個(gè)查詢完成：

SELECT * FROM customerinfo

WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )

如果使用連接（JOIN）.. 來完成這個(gè)查詢工作，速度將會(huì)快很多。尤其是當(dāng)salesinfo表中對(duì)CustomerID建有索引的話，性能將會(huì)更好，查詢?nèi)缦拢?/p>

SELECT * FROM customerinfo

LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo.

CustomerID

WHERE salesinfo.CustomerID IS NULL

連接（JOIN）.. 之所以更有效率一些，是因?yàn)?MySQL不需要在內(nèi)存中創(chuàng)建臨時(shí)表來完成這個(gè)邏輯上的需要兩個(gè)步驟的查詢工作。

3、使用聯(lián)合(UNION)來代替手動(dòng)創(chuàng)建的臨時(shí)表

MySQL 從 4.0 的版本開始支持 UNION 查詢，它可以把需要使用臨時(shí)表的兩條或更多的 SELECT 查詢合并的一個(gè)查詢中。在客戶端的查詢會(huì)話結(jié)束的時(shí)候，臨時(shí)表會(huì)被自動(dòng)刪除，從而保證數(shù)據(jù)庫整齊、高效。使用 UNION 來創(chuàng)建查詢的時(shí)候，我們只需要用 UNION作為關(guān)鍵字把多個(gè) SELECT 語句連接起來就可以了，要注意的是所有 SELECT 語句中的字段數(shù)目要想同。下面的例子就演示了一個(gè)使用 UNION的查詢。

SELECT Name, Phone FROM client

UNION

SELECT Name, BirthDate FROM author

UNION

SELECT Name, Supplier FROM product

4、事務(wù)

盡管我們可以使用子查詢（Sub-Queries）、連接（JOIN）和聯(lián)合（UNION）來創(chuàng)建各種各樣的查詢，但不是所有的數(shù)據(jù)庫操作都可以只用一條或少數(shù)幾條SQL語句就可以完成的。更多的時(shí)候是需要用到一系列的語句來完成某種工作。但是在這種情況下，當(dāng)這個(gè)語句塊中的某一條語句運(yùn)行出錯(cuò)的時(shí)候，整個(gè)語句塊的操作就會(huì)變得不確定起來。設(shè)想一下，要把某個(gè)數(shù)據(jù)同時(shí)插入兩個(gè)相關(guān)聯(lián)的表中，可能會(huì)出現(xiàn)這樣的情況：第一個(gè)表中成功更新后，數(shù)據(jù)庫突然出現(xiàn)意外狀況，造成第二個(gè)表中的操作沒有完成，這樣，就會(huì)造成數(shù)據(jù)的不完整，甚至?xí)茐臄?shù)據(jù)庫中的數(shù)據(jù)。要避免這種情況，就應(yīng)該使用事務(wù)，它的作用是：要么語句塊中每條語句都操作成功，要么都失敗。換句話說，就是可以保持?jǐn)?shù)據(jù)庫中數(shù)據(jù)的一致性和完整性。事物以BEGIN 關(guān)鍵字開始，COMMIT關(guān)鍵字結(jié)束。在這之間的一條SQL操作失敗，那么，ROLLBACK命令就可以把數(shù)據(jù)庫恢復(fù)到BEGIN開始之前的狀態(tài)。

BEGIN;

INSERT INTO salesinfo SET CustomerID=14;

UPDATE inventory SET Quantity=11

WHERE item='book';

COMMIT;

事務(wù)的另一個(gè)重要作用是當(dāng)多個(gè)用戶同時(shí)使用相同的數(shù)據(jù)源時(shí)，它可以利用鎖定數(shù)據(jù)庫的方法來為用戶提供一種安全的訪問方式，這樣可以保證用戶的操作不被其它的用戶所干擾。

5、鎖定表

盡管事務(wù)是維護(hù)數(shù)據(jù)庫完整性的一個(gè)非常好的方法，但卻因?yàn)樗莫?dú)占性，有時(shí)會(huì)影響數(shù)據(jù)庫的性能，尤其是在很大的應(yīng)用系統(tǒng)中。由于在事務(wù)執(zhí)行的過程中，數(shù)據(jù)庫將會(huì)被鎖定，因此其它的用戶請(qǐng)求只能暫時(shí)等待直到該事務(wù)結(jié)束。如果一個(gè)數(shù)據(jù)庫系統(tǒng)只有少數(shù)幾個(gè)用戶

來使用，事務(wù)造成的影響不會(huì)成為一個(gè)太大的問題；但假設(shè)有成千上萬的用戶同時(shí)訪問一個(gè)數(shù)據(jù)庫系統(tǒng)，例如訪問一個(gè)電子商務(wù)網(wǎng)站，就會(huì)產(chǎn)生比較嚴(yán)重的響應(yīng)延遲。

其實(shí)，有些情況下我們可以通過鎖定表的方法來獲得更好的性能。下面的例子就用鎖定表的方法來完成前面一個(gè)例子中事務(wù)的功能。

LOCK TABLE inventory WRITE

SELECT Quantity FROM inventory

WHEREItem='book';

...

UPDATE inventory SET Quantity=11

WHEREItem='book';

UNLOCK TABLES

這里，我們用一個(gè) SELECT 語句取出初始數(shù)據(jù)，通過一些計(jì)算，用 UPDATE 語句將新值更新到表中。包含有 WRITE 關(guān)鍵字的 LOCK TABLE 語句可以保證在 UNLOCK TABLES 命令被執(zhí)行之前，不會(huì)有其它的訪問來對(duì) inventory 進(jìn)行插入、更新或者刪除的操作。

6、使用外鍵

鎖定表的方法可以維護(hù)數(shù)據(jù)的完整性，但是它卻不能保證數(shù)據(jù)的關(guān)聯(lián)性。這個(gè)時(shí)候我們就可以使用外鍵。例如，外鍵可以保證每一條銷售記錄都指向某一個(gè)存在的客戶。在這里，外鍵可以把customerinfo 表中的CustomerID映射到salesinfo表中CustomerID，任何一條沒有合法CustomerID的記錄都不會(huì)被更新或插入到 salesinfo中。

CREATE TABLE customerinfo

(

CustomerID INT NOT NULL ,

PRIMARY KEY ( CustomerID )

) TYPE = INNODB;

CREATE TABLE salesinfo

(

SalesID INT NOT NULL,

CustomerID INT NOT NULL,

PRIMARY KEY(CustomerID, SalesID),

FOREIGN KEY (CustomerID) REFERENCES customerinfo

(CustomerID) ON DELETECASCADE

) TYPE = INNODB;

注意例子中的參數(shù)“ON DELETE CASCADE”。該參數(shù)保證當(dāng) customerinfo 表中的一條客戶記錄被刪除的時(shí)候，salesinfo 表中所有與該客戶相關(guān)的記錄也會(huì)被自動(dòng)刪除。如果要在 MySQL 中使用外鍵，一定要記住在創(chuàng)建表的時(shí)候?qū)⒈淼念愋投x為事務(wù)安全表 InnoDB類型。該類型不是 MySQL 表的默認(rèn)類型。定義的方法是在 CREATE TABLE 語句中加上 TYPE=INNODB。如例中所示。

7、使用索引

索引是提高數(shù)據(jù)庫性能的常用方法，它可以令數(shù)據(jù)庫服務(wù)器以比沒有索引快得多的速度檢索特定的行，尤其是在查詢語句當(dāng)中包含有MAX(), MIN()和ORDERBY這些命令的時(shí)候，性能提高更為明顯。那該對(duì)哪些字段建立索引呢？一般說來，索引應(yīng)建立在那些將用于JOIN, WHERE判斷和ORDER BY排序的字段上。盡量不要對(duì)數(shù)據(jù)庫中某個(gè)含有大量重復(fù)的值的字段建立索引。對(duì)于一個(gè)ENUM類型的字段來說，出現(xiàn)大量重復(fù)值是很有可能的情況，例如 customerinfo中的“province”.. 字段，在這樣的字段上建立索引將不會(huì)有什么幫助；相反，還有可能降低數(shù)據(jù)庫的性能。我們?cè)趧?chuàng)建表的時(shí)候可以同時(shí)創(chuàng)建合適的索引，也可以使用ALTER TABLE或CREATE INDEX在以后創(chuàng)建索引。此外，MySQL

從版本3.23.23開始支持全文索引和搜索。全文索引在 MySQL 中是一個(gè)FULLTEXT類型索引，但僅能用于MyISAM 類型的表。對(duì)于一個(gè)大的數(shù)據(jù)庫，將數(shù)據(jù)裝載到一個(gè)沒有FULLTEXT索引的表中，然后再使用ALTER TABLE或CREATE INDEX創(chuàng)建索引，將是非常快的。但如果將數(shù)據(jù)裝載到一個(gè)已經(jīng)有FULLTEXT索引的表中，執(zhí)行過程將會(huì)非常慢。

8、優(yōu)化的查詢語句

絕大多數(shù)情況下，使用索引可以提高查詢的速度，但如果SQL語句使用不恰當(dāng)?shù)脑挘饕龑o法發(fā)揮它應(yīng)有的作用。下面是應(yīng)該注意的幾個(gè)方面。首先，最好是在相同類型的字段間進(jìn)行比較的操作。在MySQL 3.23版之前，這甚至是一個(gè)必須的條件。例如不能將一個(gè)建有索引的INT字段和BIGINT字段進(jìn)行比較；但是作為特殊的情況，在CHAR類型的字段和 VARCHAR類型字段的字段大小相同的時(shí)候，可以將它們進(jìn)行比較。其次，在建有索引的字段上盡量不要使用函數(shù)進(jìn)行操作。

例如，在一個(gè)DATE類型的字段上使用YEAE()函數(shù)時(shí)，將會(huì)使索引不能發(fā)揮應(yīng)有的作用。所以，下面的兩個(gè)查詢雖然返回的結(jié)果一樣，但后者要比前者快得多。

SELECT * FROM order WHERE YEAR(OrderDate)2001;

SELECT * FROM order WHERE OrderDate"2001-01-01";

同樣的情形也會(huì)發(fā)生在對(duì)數(shù)值型字段進(jìn)行計(jì)算的時(shí)候：

SELECT * FROM inventory WHERE Amount/724;

SELECT * FROM inventory WHERE Amount24*7;

上面的兩個(gè)查詢也是返回相同的結(jié)果，但后面的查詢將比前面的一個(gè)快很多。第三，在搜索字符型字段時(shí)，我們有時(shí)會(huì)使用 LIKE 關(guān)鍵字和通配符，這種做法雖然簡(jiǎn)單，但卻也是以犧牲系統(tǒng)性能為代價(jià)的。例如下面的查詢將會(huì)比較表中的每一條記錄。

SELECT * FROM books

WHERE name like "MySQL%"

但是如果換用下面的查詢，返回的結(jié)果一樣，但速度就要快上很多：

SELECT * FROM books

WHERE name="MySQL"and name"MySQM"

最后，應(yīng)該注意避免在查詢中讓MySQL進(jìn)行自動(dòng)類型轉(zhuǎn)換，因?yàn)檗D(zhuǎn)換過程也會(huì)使索引變得不起作用。

新手如何調(diào)試 MySQL？看這一篇就夠了

前幾天看到姜老師的舊文 用 VSCode 編譯和調(diào)試 MySQL，每個(gè) DBA 都應(yīng) get 的小技能[1] , 文末留了一個(gè)思考題，如何修改源碼，自定義版本，使得 select version() 輸出自定義內(nèi)容

調(diào)試過程參考 macOS VSCode 編譯調(diào)試 MySQL 5.7[2]

內(nèi)部 Item 對(duì)象參考 從SQL語句到MySQL內(nèi)部對(duì)象[3]

源碼面前沒有秘密，建義對(duì) DB 感興趣的嘗試 debug 調(diào)試。本文環(huán)境為 mac + vscode + lldb

vscode 插件：

mysql 源碼：

補(bǔ)丁： MySQL = 8.0.21 需要對(duì) cmake/mysql_version.cmake 文件打補(bǔ)丁 (沒有嚴(yán)格測(cè)試所有版本)

創(chuàng)建 cmake-build-debug 目錄，后續(xù) mysql 編譯結(jié)果，以及啟動(dòng)后生成的文件都在這里

在 mysql 工程目錄下面創(chuàng)建 .vscode/settings.json 文件

內(nèi)容沒啥好說的，都是指定目錄及 boost 配置，其中 WITH_DEBUG 打開 debug 模式，會(huì)在 /tmp/debug.trace 生成 debug 信息

View - Command Palette - CMake: Configure 執(zhí)行后生成 cmake 配置

View - Command Palette - CMake: Build 編譯生成最終 mysql 相關(guān)命令

發(fā)現(xiàn)老版本編譯很麻煩，各種報(bào)錯(cuò)，mysql 5.7 代碼量遠(yuǎn)超過 5.5, 只能硬著頭皮看 5.7

首先初始化 my.cnf 配置，簡(jiǎn)單的就可以，共它均默認(rèn)

初始化數(shù)據(jù)文件，非安全模式，調(diào)試用

由于用 vscode 接管 mysql, 所以需要配置 .vscode/launch.json

然后點(diǎn)擊 run and debug mysqld

mysql 啟動(dòng)，看到輸出日志無異常，此時(shí)可以用 mysql-client 連接

首先在 sql_parser.cc:5435 處打斷點(diǎn)

mysql_parse 是 sql 處理的入口，至于 tcp connection 連接先可以忽略

執(zhí)行上述 sql 自動(dòng)跳轉(zhuǎn)到斷點(diǎn)處， Step Into , Step Over , Step Out 這些調(diào)試熟悉下即可

接下來分別調(diào)用主要函數(shù)： mysql_execute_command , execute_sqlcom_select , handle_query , select-join-exec() , Query_result_send::send_data , Item::send , Item_string:val_str , Protocol_text::store , net_send_ok

啟動(dòng) mysql 時(shí) init_common_variables 會(huì)初始化一堆變量，其中會(huì)調(diào)用 set_server_version 生成版本信息，修改這個(gè)就可以

看好條件編譯的是哪塊，修改即可， 重新 CMake: Build 編譯再運(yùn)行

這里不做過深分析，簡(jiǎn)單講

sql_yacc.cc 函數(shù) PTI_function_call_generic_ident_sys 解析 sql, 識(shí)別出 version() 是一個(gè)函數(shù)調(diào)用

find_native_function_builder 查找 hash 表，找到對(duì)應(yīng) version 函數(shù)注冊(cè)的單例工廠函數(shù)

mysql 啟動(dòng)時(shí)調(diào)用 item_create_init 將這些函數(shù) builder 注冊(cè)到 hash 表 native_functions_hash

MySQL 代碼太龐大，5.1 大約 100w 行，5.5 130w 行，5.7 以后 330w 行，只能挑重點(diǎn)讀源碼。最近很多群里的人在背八股，沒必要，有那時(shí)間學(xué)著調(diào)試下源碼，讀讀多好

原文出處：

mysql存儲(chǔ)過程怎么調(diào)

mysql調(diào)試存儲(chǔ)過程具體方法：

在你的存儲(chǔ)過程中加入如下語句：

SELECT 變量1,變量2;

然后用mysql自帶的cmd程序進(jìn)入mysql 下。

call 你的存儲(chǔ)過程名(輸入?yún)?shù)1,@輸出參數(shù));(注:這里幫助下新同學(xué)，如果你的存儲(chǔ)過程有輸出變量，那么在這里只需要加 @ 然后跟任意變量名即可);

即可發(fā)現(xiàn)你的變量值被打印到了cmd下

mysql怎么調(diào)整輸入內(nèi)容的字體？

請(qǐng)把您的訴求講清楚，是要調(diào)整navicat的字體，還是要把指定字體文字?jǐn)?shù)據(jù)保存到數(shù)據(jù)庫？

如何在Windows下編譯或調(diào)試MySQL

用vs code 就可以了。

Visual Studio Code

Visual Studio Code（簡(jiǎn)稱VS Code）是由微軟開發(fā)的，同時(shí)支持Windows、Linux和macOS操作系統(tǒng)的開源文本編輯器。它支持調(diào)試，內(nèi)置了Git 版本控制功能，同時(shí)也具有開發(fā)環(huán)境功能，例如代碼補(bǔ)全（類似于IntelliSense）、代碼片段、代碼重構(gòu)等。該編輯器支持用戶自定義配置，例如改變主題顏色、鍵盤快捷方式、編輯器屬性和其他參數(shù)，還支持?jǐn)U展程序并在編輯器中內(nèi)置了擴(kuò)展程序管理的功能。

安裝LLDB

LLDB是LLVM編譯器的一部分，推薦使用Homebrew安裝LLVM工具集，不建議使用系統(tǒng)自帶的LLDB，安裝前必須先創(chuàng)建證書否則無法安裝，步驟如下：

創(chuàng)建完成后，開始安裝LLVM

brew install llvm --with-python@2?--with-lldb

安裝插件

VS Code自帶有debug功能，這里我推薦使用LLDB Debugger插件。

?接下來，為項(xiàng)目配置調(diào)試參數(shù)。

配置調(diào)試參數(shù)

使用VS Code打開MySQL源碼目錄，在側(cè)邊欄選擇debug欄目，添加配置，program輸入需要調(diào)試的程序路徑，這里選擇你編譯好的mysqld路徑，args輸入程序啟動(dòng)所需的參數(shù)，通常會(huì)指定mysqld的配置文件。這樣就配置好了，是不是很簡(jiǎn)單。

啟動(dòng)調(diào)試

點(diǎn)擊啟動(dòng)按鈕，啟動(dòng)后如果沒有設(shè)置斷點(diǎn)會(huì)mysqld會(huì)正常啟動(dòng)，如果觸發(fā)了斷點(diǎn)會(huì)如下圖顯示。

整個(gè)調(diào)試窗口基本分為六部分，所有的調(diào)試操作都在這里完成:

1:?顯示變量信息

2:?設(shè)置重點(diǎn)關(guān)注的變量

3:?顯示調(diào)用棧信息

4:?設(shè)置斷點(diǎn)信息，在代碼行號(hào)前也可以設(shè)置斷點(diǎn)

5:?代碼顯示區(qū)域，上方是調(diào)試按鈕，包括 continue/stepover/step in/step out/restart/stop

6:?調(diào)試終端輸入輸出區(qū)

斷點(diǎn)設(shè)置

在代碼行號(hào)前點(diǎn)擊即可在該行為設(shè)置斷點(diǎn)，也可以根據(jù)條件設(shè)置斷點(diǎn)。以設(shè)置ConditionalBreakpoint為例，當(dāng)程序啟動(dòng)后會(huì)按照你設(shè)置的條件表達(dá)式判斷是否觸發(fā)斷點(diǎn)。

Conditional Breakpoint這種方式用在目標(biāo)變量達(dá)到某條件時(shí)觸發(fā)斷點(diǎn)，其余則跳過繼續(xù)執(zhí)行。比如：設(shè)置變量等于目標(biāo)表名時(shí)觸發(fā)斷點(diǎn)，其余表則跳過，相對(duì)函數(shù)名斷點(diǎn)省去很多手工跳過操作。

遠(yuǎn)程調(diào)試

假如你想調(diào)試遠(yuǎn)程Linux服務(wù)器上的MySQL上面的方法就不合適了，這時(shí)需要遠(yuǎn)程調(diào)試。lldb和gdb都支持遠(yuǎn)程調(diào)試，這里以lldb為例。

需要先在遠(yuǎn)程主機(jī)上安裝lldb，使用yum安裝，源地址在這里

remote$ yum install -y llvm-toolset-7

安裝完成后，啟動(dòng)lldb-server

remote$ /opt/rh/llvm-toolset-7/root/usr/bin/lldb-serverplatform --listen?"*:9191"?--server

接下來，在VS Code調(diào)試界面中新增配置項(xiàng)。

{

"type":?"lldb",

"request":?"attach",

"name":?"Remote attach",

"program":?"~/mysql5626/usr/local/mysql/bin/mysqld",

"pid":"target_pid",

"initCommands": [

"platform select remote-linux",

"platform connect connect://remote_host:9191"

],

"sourceMap": {

"/export/home/pb2/build/sb_0-15908961-1436910670.17/mysql-5.6.26":?"/Users/hongbin/workbench/mysql-server"

}

},

program:?本機(jī)也要拷貝一份目標(biāo)程序，加載

pid:?填寫遠(yuǎn)程主機(jī)的mysqld進(jìn)程id

sourceMap:?填寫mysqld編譯的代碼路徑與本機(jī)代碼庫路徑的映射，這樣調(diào)試時(shí)代碼才可以和程序關(guān)聯(lián)在一起看

注意：記得調(diào)試前將代碼切換到與目標(biāo)程序版本一致的branch

文章名稱：mysql怎么調(diào),mysql如何調(diào)優(yōu)
本文地址：http://chinadenli.net/article46/dsegeeg.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供動(dòng)態(tài)網(wǎng)站、企業(yè)建站、建站公司、網(wǎng)站設(shè)計(jì)、服務(wù)器托管、品牌網(wǎng)站建設(shè)

廣告

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