MYSQL應(yīng)用手冊(cè)_v0.2

1、Mysql應(yīng)用手冊(cè)1 前言1.1 開(kāi)發(fā)和維護(hù)功能，介紹以下專(zhuān)題： · 常用數(shù)據(jù)類(lèi)型· 函數(shù)· 過(guò)程· ORACLE到MYSQL模型遷移· 常見(jiàn)錯(cuò)誤· 常用工具· 應(yīng)用設(shè)計(jì)規(guī)則1.2 數(shù)據(jù)庫(kù)管理功能，介紹以下專(zhuān)題· 安裝部署· 常用管理工具· 語(yǔ)法優(yōu)化工具1.3 附錄· ORACLE和MYSQL編寫(xiě)常用SQL及函數(shù)差異2 開(kāi)發(fā)維護(hù)篇2.1 SQL語(yǔ)法MYSQL基本參照標(biāo)準(zhǔn)SQL，而ORACLE除了SQL語(yǔ)法，還有PL/SQL標(biāo)準(zhǔn)。兩者在一些常用SQL語(yǔ)句和函數(shù)存在一些較少的差異，詳見(jiàn)附錄

2、一ORACLE和MYSQL常用SQL及函數(shù)差異，在此不再做介紹。2.2 常用數(shù)據(jù)類(lèi)型常用數(shù)據(jù)類(lèi)型分?jǐn)?shù)值（含整數(shù)和浮點(diǎn)數(shù)），字符串（含大數(shù)據(jù)），時(shí)間。2.2.1 數(shù)值類(lèi)型存儲(chǔ)(字節(jié))范圍（有符號(hào)）范圍（無(wú)符號(hào)）用途TINYINT1 -1281270255小整數(shù)值SMALLINT2 -32 76832 767065 535大整數(shù)值MEDIUMINT3-8 388 6088 388 607016 777 215大整數(shù)值INT或INTEGER4 -2 147 483 6482 147 483 64704 294 967 295大整數(shù)值BIGINT8 -9 233 372 036 854 775 808

3、9 223 372 036 854 775 807018 446 744 073 709 551 615極大整數(shù)值FLOAT4 (-3.402 823 466 E+38，1.175 494 351 E-38)0(1.175 494 351 E-38，3.402 823 466 351 E+38)0(1.175 494 351 E-38，3.402 823 466 E+38)單精度浮點(diǎn)數(shù)值DOUBLE8 (1.797 693 134 862 315 7 E+308，2.225 073 858 507 201 4 E-308)0(2.225 073 858 507 201 4 E-308，1.79

4、7 693 134 862 315 7 E+308)0(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)雙精度浮點(diǎn)數(shù)值DECIMAL對(duì)DECIMAL(M,D) ，如果M>D，為M+2否則為D+2依賴(lài)于M和D的值依賴(lài)于M和D的值小數(shù)值說(shuō)明： 定義了整數(shù)值的顯示寬度（如：INT(4)）。表示可選顯示寬度規(guī)定用于顯示寬度小于指定的列寬度的值時(shí)從左側(cè)填滿(mǎn)寬度，它不限制列的保存范圍，也不限制列中超過(guò)指定列寬度的值的顯示。浮點(diǎn)值定義如float(7,4)，表示有3位整數(shù)，最多保存4位小數(shù)，超過(guò)4位小數(shù)會(huì)被四舍五入，而超過(guò)3位整數(shù)

5、位的值會(huì)報(bào)錯(cuò)。建議浮點(diǎn)數(shù)統(tǒng)一使用DECIMAL，對(duì)精度計(jì)算更精準(zhǔn)，且以二進(jìn)制方式存儲(chǔ)。2.2.2 DATE類(lèi)型存儲(chǔ)(字節(jié))范圍格式用途DATE31000-01-019999-12-31YYYY-MM-DD日期值TIME3'-838:59:59''838:59:59'HH:MM:SS時(shí)間值或持續(xù)時(shí)間YEAR119012155YYYY年份值DATETIME81000-01-01 00:00:009999-12-31 23:59:59YYYY-MM-DD HH:MM:SS混合日期和時(shí)間值TIMESTAMP8UTC 1970-01-01 00:00:002038-01-

6、19 03：14：07YYYYMMDD HHMMSS混合日期和時(shí)間值，時(shí)間戳說(shuō)明：TIME不僅可以用于表示一天的時(shí)間(必須小于24小時(shí))，還可能為某個(gè)事件過(guò)去的時(shí)間或兩個(gè)事件之間的時(shí)間間隔(可以大于24小時(shí)，或者甚至為負(fù))。TIMESTAMP類(lèi)型支持默認(rèn)自動(dòng)更新時(shí)間戳：· 只指定缺省值：timestamp default current_timestamp· 定義缺省值并隨記錄變更而更新 timestamp default current_timestamp on update current_ttimestamp2.2.3 字符串類(lèi)型存儲(chǔ)（字節(jié)）用途CHAR1255 定長(zhǎng)

7、字符串VARCHAR165535變長(zhǎng)字符串TINYBLOB1255不超過(guò) 255 個(gè)字符的二進(jìn)制字符串TINYTEXT1255短文本字符串BLOB165535二進(jìn)制形式的長(zhǎng)文本數(shù)據(jù)TEXT165535長(zhǎng)文本數(shù)據(jù)MEDIUMBLOB116777215二進(jìn)制形式的中等長(zhǎng)度文本數(shù)據(jù)MEDIUMTEXT116777215中等長(zhǎng)度文本數(shù)據(jù)LOGNGBLO進(jìn)制形式的極大文本數(shù)據(jù)LONGTEX大文本數(shù)據(jù)說(shuō)明： 存儲(chǔ)大小參照字段保存值為字母，如果字符集是UTF8，CHAR存儲(chǔ)大小最多為255*3字節(jié)。 字符串的存儲(chǔ)依賴(lài)于字符集，一般字符集選擇通用的UTF8（

8、字符占用存儲(chǔ)最小1個(gè)字節(jié)，最高3個(gè)字節(jié)）。注意在實(shí)際保存值的在存儲(chǔ)方面的區(qū)別。2.3 函數(shù)2.3.1 日期和時(shí)間相關(guān)獲取當(dāng)前系統(tǒng)日期和時(shí)間：now(): 在語(yǔ)句執(zhí)行開(kāi)始得到值sysdate()：在函數(shù)執(zhí)行開(kāi)始時(shí)得到最新值localtime()：在語(yǔ)句執(zhí)行開(kāi)始得到值獲取當(dāng)前日期（年月日）：curdate(),current_date()獲取當(dāng)前時(shí)間（時(shí)分秒）：curtime(),current_time()獲取當(dāng)前時(shí)間（按格式）：Hour(time)：小時(shí)minute(time):分鐘month(date)：月份year(date)：年份date_format(date,format): 格式化

9、時(shí)間（%Y 年（YYYY）%m 月（MM 00.12）%d 日（DD 00.31）%H 小時(shí)（HH 00.24）%i 分鐘（00.59）%s 秒（00.59）%f 微秒（000000.999999）計(jì)算日期：加減：date_add(date,INTERVAL expr type)，type指second,minute,hour,day,week,month,year等；例如：select date_add(sysdate(),interval -1 day);2.3.2 字符串函數(shù)返回大寫(xiě)字符串：ucase(aaa) 或者upper(aaa);返回小寫(xiě)字符串：lcase(AAA) 或者lowe

10、r(AAA);返回字符串長(zhǎng)度：length(AAA)；截?cái)嗫崭瘢?ltrim( asss ) ,rtrim( aaa ), trim( aaa );字符串連接：concat(s1,s2,);字符串替換：insert(s1,x,y,s2) 將s1第x個(gè)位置開(kāi)始，共y個(gè)字符的子串替換成s2;返回字符串指定值：left(s1,x)返回s1最左邊的x個(gè)字符；right(s1,x)返回s1最右邊的x個(gè)字符。字符截?。簊ubstring(s1,m,n)取s1從第m位置開(kāi)始的余下所有或長(zhǎng)度為n的字符串；2.3.3 類(lèi)型轉(zhuǎn)換函數(shù)cast(s1 as type): 指定的類(lèi)型如： binary,char,dat

11、e,time,datetime,signed,unsigned。convert(s1,type): 指定的類(lèi)型如：binary,char,date,time,datetime,signed,unsigned。2.3.4 其它函數(shù)database():返回當(dāng)前連接的數(shù)據(jù)庫(kù)名user():返回當(dāng)前連接串信息。2.4 過(guò)程過(guò)程保存在服務(wù)端，有很強(qiáng)的靈活性，執(zhí)行速度較快，減少網(wǎng)絡(luò)流量。但在實(shí)時(shí)性的并發(fā)會(huì)話(huà)中不建議使用。多用于統(tǒng)計(jì)，測(cè)試，監(jiān)控等任務(wù)。2.4.1 示例DELIMITER $CREATE PROCEDURE proc1()  BEGIN SELECT COUNT

12、(*) FROM a;  END $ DELIMITER ; 2.4.2 語(yǔ)法說(shuō)明· 在后臺(tái)mysql客戶(hù)端程序（mysql）窗口中，“；”表示語(yǔ)句結(jié)束符，如果是創(chuàng)建過(guò)程，需要使用DELIMITER以改變語(yǔ)句結(jié)束符，如“$”，最后再改回“；”。· 變量：定義局部變量declare v_i int default 0;賦值：set v_i=0;定義用戶(hù)變量：set v_i=0; 用戶(hù)變量必須以開(kāi)頭， 此變量可以在過(guò)程內(nèi)外使用。變量作用域的范圍以end結(jié)束。· 條件語(yǔ)句：if v_i =0 thenselect 0;e

13、lseif v_i=1 thenselect 1;elseselect null;end if;· 循環(huán)1:case v_iwhen 0 thenwhen 1 thenelse.end case;· 循環(huán)2：while v_i<6 doselect v_i;set v_i=v_i+1;end while;執(zhí)行前檢查條件· 循環(huán)3：repeatselect v_i;set v_i=v_i+1;until v_i>5end repeat;執(zhí)行后檢查條件· 循環(huán)3：LOOP_LABLE:loopselect v_i;set v_i=v_i+1;if

14、 v_i>5 thenleave LOOP_LABLE;end if;end loop;· 參數(shù)，有IN，OUT，INOUT類(lèi)型：如create procedure proc1(in p_a int);· 執(zhí)行過(guò)程：call proc1(); 沒(méi)有參數(shù)；call proc1(2); 帶有參數(shù)；call proc1(v_i); 使用用戶(hù)變量；· 可以在當(dāng)前屏幕直接輸出結(jié)果，這一點(diǎn)比PL/SQL方便。· 注釋?zhuān)?- name ; 必須以“-”加空格開(kāi)始。2.4.3 動(dòng)態(tài)SQL示例：DELIMITER $CREATE PROCEDURE proc2(in

15、 v_i int) BEGIN declare v_ret int ;declare cur_sel cursor for select a from a;declare continue handler for not found set v_ret=1;set v_ret=0;open cur_sel;repeatfetch cur_sel into v_i;select v_i;until v_retend repeat;close cur_sel;END;$ DELIMITER ;2.5 ORACLE到MYSQL模型遷移針對(duì)遷移方案，主要從數(shù)據(jù)模型轉(zhuǎn)換（主要在字段類(lèi)型，約束屬性等）來(lái)說(shuō)

16、明差異要點(diǎn)。2.5.1 數(shù)據(jù)模型轉(zhuǎn)換 字段類(lèi)型對(duì)照POWERDESIGN自動(dòng)轉(zhuǎn)換PDM后，ORACLE版本和MYSQL版本的所有數(shù)據(jù)類(lèi)型對(duì)照關(guān)系如下： ORACLE模型mysql模型CHARcharNUMBERNumeric type(int,bigint)DATEdatetimeNVARCHAR2varcharVARCHAR2BLOBlongblobMYSQL版本從節(jié)約存儲(chǔ)空間的角度，numeric類(lèi)型可以分成tinyint（占1個(gè)字節(jié)），smallint（占2個(gè)字節(jié)），mediumint（占3個(gè)字節(jié)），int（占4個(gè)字節(jié)），bigint（占8個(gè)字節(jié)），而numeric是每個(gè)數(shù)

17、據(jù)占1個(gè)字節(jié)；日期字段由于業(yè)務(wù)大多數(shù)都會(huì)精確到時(shí)分秒，選擇datetime類(lèi)型；字符串類(lèi)型選擇varchar；由于BLOB 在ORACLE版本中最大能支持4GB，選擇longblob類(lèi)型。 約束屬性Powerdesigner工具轉(zhuǎn)換成MYSQL版本時(shí)，約束屬性會(huì)自動(dòng)按MYSQL方式創(chuàng)建，有少數(shù)差異，見(jiàn)下節(jié)說(shuō)明。2.5.2 差異MYSQL版本數(shù)據(jù)模型創(chuàng)建需要注意以下要點(diǎn)：· 在19位以下 整數(shù)，需要轉(zhuǎn)換成相應(yīng)的整型（tinyint，smallint，mediumint，int，bigint）來(lái)節(jié)約存儲(chǔ)空間。· 日期類(lèi)型（DATE）需要指定為datetime。&#

18、183; 注釋語(yǔ)句，POWERDESIGNER工具會(huì)自動(dòng)轉(zhuǎn)換成MYSQL版本語(yǔ)句。· PDM中的一些字段（填入序列號(hào)值），POWERDESIGNER工具會(huì)自動(dòng)添加auto_increment（類(lèi)序列號(hào)屬性）。需要手工去除這種屬性，業(yè)務(wù)采用框架推出的序列號(hào)使用方案。· datetime的缺省值是now()，不能使用sysdate()。· 創(chuàng)建主鍵所指定的數(shù)據(jù)類(lèi)型所占存儲(chǔ)不能太大，會(huì)報(bào)錯(cuò)。比如使用varchar(512)來(lái)創(chuàng)建組合主鍵。mysql5.6版本最多不能超過(guò)767字節(jié)（UTF8字符集3倍指定字段類(lèi)型）。· 創(chuàng)建索引同主鍵一樣。mysql5.6最多版

19、本不能超過(guò)767字節(jié)（UTF8字符集3倍指定字段類(lèi)型）。· 字段的注釋不要超過(guò)1024個(gè)字符（mysql5.6版本）。如果需要超長(zhǎng)的注釋?zhuān)ㄗh新建一張描述注釋的表· UD用戶(hù)下單表的較大的varchar字段（如：varchar(2000)）需要轉(zhuǎn)換為text或者blob（每行包括含帶有varchar，最大長(zhǎng)度不能大于65535字節(jié)）。· 特別注意合理設(shè)計(jì)表索引和主鍵，這會(huì)影響DML語(yǔ)句運(yùn)行是行鎖還是表鎖？MYSQL是基于索引條件檢索數(shù)據(jù)時(shí)加行鎖，否則加表鎖（這一點(diǎn)與ORACLE不同）。2.6 常見(jiàn)錯(cuò)誤（客戶(hù)端）ERROR 1040超過(guò)最大連接數(shù)。需要增加max_c

20、onnections參數(shù)的值ERROR 1041內(nèi)存不足。需要檢查配置中的內(nèi)存設(shè)置（特別注意：innodb_buffer_pool_size）。一般不要超過(guò)物理內(nèi)存的75%ERROR 1042無(wú)效的主機(jī)名。常見(jiàn)的連接方式為：mysql -u test -p -h IP。注意mysql.user表中是否有對(duì)應(yīng)原主機(jī)的連接。ERROR 1044數(shù)據(jù)庫(kù)用戶(hù)權(quán)限不足。需要管理員增加權(quán)限。ERROR 1045數(shù)據(jù)庫(kù)服務(wù)器/數(shù)據(jù)庫(kù)用戶(hù)名/數(shù)據(jù)庫(kù)名/數(shù)據(jù)庫(kù)密碼錯(cuò)誤。分別仔細(xì)檢查用戶(hù)名和密碼和數(shù)據(jù)庫(kù)名是否正確ERROR 1046沒(méi)有選擇數(shù)據(jù)庫(kù)。任何SQL操作前需要先選擇數(shù)據(jù)庫(kù)?？蛻?hù)端連接時(shí)使用（use dat

21、abase_name）ERROR 1049數(shù)據(jù)庫(kù)不存在ERROR 1050表已經(jīng)存在。創(chuàng)建的表已經(jīng)存在ERROR 1051表不存在ERROR 1054字段不存在ERROR 1064不支持的SQL語(yǔ)法。出現(xiàn)此種錯(cuò)誤，需要仔細(xì)檢查語(yǔ)句，錯(cuò)誤提示一般會(huì)顯示錯(cuò)誤的位置ERROR 1130沒(méi)有連接數(shù)據(jù)庫(kù)的權(quán)限。需要增加權(quán)限ERROR 1133數(shù)據(jù)庫(kù)用戶(hù)不存在，需要先創(chuàng)建用戶(hù)ERROR 1149SQL語(yǔ)法錯(cuò)誤ERROR 1205加鎖超時(shí)。需要查看系統(tǒng)中鎖。一般使用show processlist 可以看到運(yùn)行中的鎖表語(yǔ)句ERROR 1264字段溢出報(bào)錯(cuò)2.7 常用工具2.7.1 客戶(hù)端（mysql）工具m

22、ysql可以連接數(shù)據(jù)庫(kù)，下面是常用操作。· 新建一個(gè)連接： mysql -hlocalhost -uroot -p -P 3306 ;其中test表示打開(kāi)的數(shù)據(jù)庫(kù)。· 直接執(zhí)行語(yǔ)句：-e “sql”;· 默認(rèn)是自動(dòng)提交，通過(guò)如下方式：mysql>select autocommit;mysql>set autocommit=0; #關(guān)閉自動(dòng)提交· 查看進(jìn)程信息:mysql>show processlist;使用help show命令可以查看很多命令信息。 2.7.2 數(shù)據(jù)庫(kù)管理工具（mysqladmin）· 停止數(shù)據(jù)庫(kù)；mysq

23、ladmin uroot p shutdown· 查看進(jìn)程信息：mysqladmin uroot p processlist· 殺掉進(jìn)程：mysqlamdin -uroot -p kill id1,id2,id32.7.3 數(shù)據(jù)庫(kù)日志管理(mysqlbinlog)· 查看test數(shù)據(jù)庫(kù)的操作日志：mysqlbinlog -d test -s master-bin.000001.· 結(jié)果輸出到文件：-r out_test.txt· 客戶(hù)端導(dǎo)入數(shù)據(jù): mysql uroot p test <out_test.txt2.7.4 數(shù)據(jù)導(dǎo)入工具（m

24、ysqlimport） · 導(dǎo)入數(shù)據(jù):mysqlimport -uroot -p test -fields-enclosed-by=' -fields-terminated-by=, t_test；-fields-enclosed-by 表示數(shù)據(jù)的引用符-fields-terminated-by 表示分隔符，默認(rèn)的分隔符是跳格符（Tab）。t_test的記錄如下：1,'1'2,'2'3,32.7.5 數(shù)據(jù)導(dǎo)出工具(mysqldump)· 導(dǎo)出指定表: mysqldump -n -t -uroot -p -B test -tables

25、t_test (-n 不生成建庫(kù)語(yǔ)句 -t不生成建表語(yǔ)句 B指定數(shù)據(jù)庫(kù)名 tables指定表) · 生成mysqlimport可導(dǎo)入文件：mysqldump -h -P 3306 -uroot -p1 -add-drop-table -opt -q -T ./ test a自動(dòng)生成文件：a.sql 建表語(yǔ)句 。a.txt文本格式，mysqlimport可以直接使用。2.7.6 錯(cuò)誤原因說(shuō)明（perror）· perror 1045:查看1045的錯(cuò)誤信息。2.7.7 文本替換工具(replace)mysql自帶對(duì)文本中一個(gè)或多個(gè)字符串進(jìn)行替換的工具。&#

26、183; 直接替換并覆蓋原文件。replace s1 n_s1 s2 n_s2 - file1 file2 #對(duì)file1,file中的s1替換成n_s1，s2替換成n_s2。· 替換并顯示在標(biāo)準(zhǔn)輸出上replace s1 n_s1 s2 n_s2 < file 2.8 應(yīng)用設(shè)計(jì)規(guī)則以下針對(duì)實(shí)時(shí)性要求高的業(yè)務(wù)場(chǎng)景。2.8.1 表設(shè)計(jì)建議· 表必須定義主鍵，建議取整形。· 不使用外鍵。· 事務(wù)要求高時(shí)使用INNODB存儲(chǔ)引擎，字符集為utf8。· 表中增加記錄創(chuàng)建時(shí)間和修改時(shí)間字段，方便數(shù)據(jù)的維護(hù)。· 單表數(shù)據(jù)多，考慮分表。小表比

27、大表在數(shù)據(jù)處理上有較快的速度。2.8.2 字段設(shè)計(jì)建議· 定義非空，避免NULL數(shù)據(jù)，利于數(shù)據(jù)庫(kù)的查詢(xún)優(yōu)化。· 主鍵必須非空，并且定義缺省值。· 選擇優(yōu)先級(jí)：小整形大整形日期固定長(zhǎng)度字符串變長(zhǎng)字符串注：參見(jiàn)了“MYSQL常用數(shù)據(jù)類(lèi)型”· 浮點(diǎn)選擇decimal類(lèi)型。· 盡量避免使用BLOB和TEXT，選擇拆分表（以主鍵加大數(shù)據(jù)類(lèi)型方式）或其它方式存儲(chǔ)。2.8.3 索引設(shè)計(jì)建議· 極小數(shù)據(jù)的表不添加索引。· 索引字段 不能為空（含復(fù)合索引）。· 索引字段的值盡量是重復(fù)率盡量低。· 索引字段為常出現(xiàn)在wher

28、e條件的字段名。· 單表索引不易過(guò)多，以免增加查詢(xún)優(yōu)化時(shí)間。· 并發(fā)環(huán)境高的環(huán)境不要使用外鍵，容易帶來(lái)死鎖。2.8.4 綜合開(kāi)發(fā)設(shè)計(jì)建議以下只對(duì)實(shí)時(shí)性和并發(fā)性高的場(chǎng)景。· 禁止在索引列使用函數(shù)或數(shù)學(xué)運(yùn)算，會(huì)導(dǎo)致查詢(xún)時(shí)不讀取索引。· 禁止使用“%”作為前綴模糊條件查詢(xún)數(shù)據(jù)。· 禁止在條件語(yǔ)句中使用比較不同的數(shù)據(jù)類(lèi)型（如數(shù)字與字符比較，編寫(xiě)成數(shù)字對(duì)數(shù)字，字符對(duì)字符）。· 避免使用字符類(lèi)型存儲(chǔ)數(shù)字。· 避免大SQL，拆分成單一SQL。· 避免使用select *。· 避免使用子查詢(xún)。· 復(fù)合索引使用條

29、件語(yǔ)句中必須增加索引的第一個(gè)字段。· 索引列盡量避免更新操作。· 避免在服務(wù)端進(jìn)行復(fù)雜計(jì)算，遷移到客戶(hù)端處理。· 盡量少使用存儲(chǔ)過(guò)程和觸發(fā)器。· count(*)是統(tǒng)計(jì)整行的記錄，count(col_a)是統(tǒng)計(jì)整列非空值的記錄。3 數(shù)據(jù)庫(kù)管理篇3.1 安裝部署3.1.1 單數(shù)據(jù)庫(kù)安裝 安裝程序包服務(wù)端： rpm -ivh MySQL-server-5.6.13-1.rhel5.x86_64.rpm客戶(hù)端： rpm -ivh MySQL-client-5.6.13-1.rhel5.x86_64.rpm 驗(yàn)證mysql -help

30、 啟動(dòng)數(shù)據(jù)庫(kù)· 創(chuàng)建主用戶(hù)groupadd mysqluseradd -g mysql mysql· 創(chuàng)建f文件（注意mysql默認(rèn)查找的f路徑，以避免讀取錯(cuò)誤配置文件。mysqlduser = mysqlport = 3306basedir = /usrdatadir = /data1/mysql1/3306/datasocket = /data1/mysql1/3306/proc/mysql_3306.sockpid-file= /data1/mysql1/3306/proc/mysql_3306.pidtmpdir = /data1/mysql1/330

31、6/tmp# replserver-id = 1# Logginglog_bin = /data1/mysql1/3306/log/binlog/master_3306_binlog-error = /data1/mysql1/3306/log/error/error_3306.logslow_query_log_file = /data1/mysql1/3306/log/slow/slow_3306.loggeneral_log_file = /data1/mysql1/3306/log/general/general_3306.logrelay_log = /data1/mysql1/33

32、06/log/relay/relay_3306.logrelay_log_info_file = /data1/mysql1/3306/log/relay/relay_log_3306.infoexpire_logs_days=3explicit_defaults_for_timestamp=truecharacter-set-server = utf8init_connect = 'SET NAMES utf8'open_files_limit = 30000max_connections = 5000max_user_connections = 5020autocommit

33、 = 1skip-name-resolvemax_allowed_packet = 64M# InnoDBinnodb_buffer_pool_size = 2Ginnodb_flush_method = O_DIRECTinnodb_thread_concurrency = 64innodb_io_capacity = 8000innodb_read_io_threads = 16innodb_write_io_threads = 16#innodb_change_buffering = insertsinnodb_log_buffer_size = 16Minnodb_log_group_

34、home_dir = /data1/mysql1/3306/innodb/loginnodb_log_files_in_group = 3innodb_log_file_size = 512Minnodb_data_home_dir = /data1/mysql1/3306/innodb/datainnodb_data_file_path = ibdata1:1024M;ibdata2:1024M:autoextendinnodb_open_files = 30000# MyISAMkey_buffer_size = 32M# Otherquery_cache_size = 128Mtmp_t

35、able_size = 256Mmax_heap_table_size = 256Mthread_cache_size = 64bulk_insert_buffer_size = 64Mmax_binlog_cache_size = 256Mmax_binlog_size = 500Mtransaction_isolation = read-committedbinlog_format = rowlower_case_table_names = 1sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLEStable_open_cache = 30

36、262table_definition_cache = 65535connect_timeout =28800net_write_timeout = 300net_read_timeout = 300# Remove leading # to set options mainly useful for reporting servers.# The server defaults are faster for transactions and fast SELECTs.# Adjust sizes as needed, experiment to find the optimal values

37、.join_buffer_size = 64Ksort_buffer_size = 64Kread_buffer_size = 64Kread_rnd_buffer_size = 64K· 安裝數(shù)據(jù)庫(kù)：mysql_install_db -basedir=/usr -datadir=/data01/mysql/data -user=mysql -defaults-file=/home/mysql/f · 啟動(dòng)數(shù)據(jù)庫(kù)mysqld_safe -defaults-file=/home/mysql/f &· 停止數(shù)據(jù)庫(kù)mysqladmin -h -

38、P 3306 -uroot -p shutdown (安裝后的初始密碼為空)下載服務(wù)： http:/ftp.jaist.ac.jp/pub/mysql/Downloads/MySQL-5.6/3.1.2 主從部署 安裝程序包同單數(shù)據(jù)庫(kù)安裝 主服務(wù)安裝· 創(chuàng)建f配置文件 （注意替換其中的路徑）mysqlddatadir = /data01/mysql/datapid-file=/home/mysql/my_etc/master.pidsocket=/home/mysql/my_etc/master.socklog-error=/data01/mysql/lo

39、g/mysql_error.loglog-bin=/data01/mysql/log/master-binlog-bin-index=/data01/mysql/log/master-bin.indextmpdir=/data01/mysql/tmpdirinnodb_data_home_dir=/data01/mysql/datainnodb_log_group_home_dir=/data01/mysql/logcharacter-set-server=utf8user=mysqlserver_id=1port=3400# master db portbinlog-format=ROWbi

40、nlog-rows-query-log-events=1sync_binlog=1log-slave-updates=truemax_binlog_size=1Gmax_relay_log_size=1Gexpire_logs_days=3binlog_cache_size=1Mgtid-mode=onenforce-gtid-consistency=truemaster-info-repository=TABLErelay-log-info-repository=TABLEsync-master-info=1master-verify-checksum=1slave-sql-verify-c

41、hecksum=1lower_case_table_names=1transaction_isolation =read-committedinnodb_file_per_table=oninnodb_buffer_pool_size =256M # buffer poolinnodb_buffer_pool_instances=8innodb_flush_log_at_trx_commit=1innodb_data_file_path=ibdata1:100M;ibdata2:100M:autoextendinnodb_file_io_threads=4innodb_thread_concu

42、rrency=12innodb_additional_mem_pool_size=64Minnodb_log_buffer_size = 8Minnodb_log_file_size=256Minnodb_log_files_in_group=3innodb_max_dirty_pages_pct=80innodb_flush_method=O_DIRECTinnodb_autoextend_increment = 128Minnodb_read_io_threads=16innodb_write_io_threads=16innodb_io_capacity=1000innodb_io_ca

43、pacity_max=4000query_cache_type=0max_connect_errors=10000max_connections=2000character-set-server=utf8sql_mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION· 安裝數(shù)據(jù)庫(kù)：mysql_install_db -basedir=/usr -datadir=/data01/mysql/data -user=mysql -defaults-file=/home/mysql/f·

44、 啟動(dòng)數(shù)據(jù)庫(kù)mysqld_safe -defaults-file=/home/mysql/f &· 復(fù)制數(shù)據(jù)庫(kù)文件打包datadir目錄下的文件，復(fù)制到從數(shù)據(jù)庫(kù)的datadir目錄下面。· 登錄數(shù)據(jù)庫(kù)，創(chuàng)建復(fù)制用戶(hù)mysql>GRANT REPLICATION SLAVE ON *.* TO 'repl_user''%' IDENTIFIED BY 'repl_user'mysql>flush privileges; 從服務(wù)安裝· 創(chuàng)建f配置文件 （注意替換其中的路徑）mysqldd

45、atadir=/data01/mysql/datapid-file=/home/mysql/my_etc/slave.pidsocket=/home/mysql/my_etc/slave.socklog-error=/data01/mysql/log/slave_error.logtmpdir=/data01/mysql/tmpdirlog-bin=/data01/mysql/log/slave-binlog-bin-index=/data01/mysql/log/slave-bin.indexinnodb_data_home_dir=/data01/mysql/datainnodb_log_

46、group_home_dir=/data01/mysql/loguser=mysqlserver_id=1001port=4400report-port=4400binlog-format=ROWbinlog-rows-query-log-events=1sync_binlog=1log-slave-updates=truemax_binlog_size=1Gmax_relay_log_size=1Gexpire_logs_days=3binlog_cache_size=1Mgtid-mode=onenforce-gtid-consistency=truemaster-info-reposit

47、ory=TABLErelay-log-info-repository=TABLEsync-master-info=1master-verify-checksum=1slave-sql-verify-checksum=1read-only=1lower_case_table_names=1transaction_isolation =read-committedinnodb_file_per_table=oninnodb_buffer_pool_size =1024M #innodb_buffer_pool_instances=8innodb_flush_log_at_trx_commit=1i

48、nnodb_data_file_path=ibdata1:100M;ibdata2:100M:autoextendinnodb_file_io_threads=4innodb_thread_concurrency=12innodb_additional_mem_pool_size=64Minnodb_log_buffer_size = 8Minnodb_log_file_size=256Minnodb_log_files_in_group=3innodb_max_dirty_pages_pct=80innodb_flush_method=O_DIRECTinnodb_autoextend_in

49、crement = 128Minnodb_read_io_threads=16innodb_write_io_threads=16innodb_io_capacity=1000innodb_io_capacity_max=4000query_cache_type=0max_connect_errors=10000max_connections=2000character-set-server=utf8sql_mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION· 安裝數(shù)據(jù)庫(kù)：mysql

50、_install_db -basedir=/usr -datadir=/data01/mysql/data -user=mysql -defaults-file=/home/mysql/f· 啟動(dòng)數(shù)據(jù)庫(kù)mysqld_safe -defaults-file=/home/mysql/f &· 登錄數(shù)據(jù)庫(kù)，啟動(dòng)復(fù)制功能mysql>CHANGE MASTER TO MASTER_HOST='43', MASTER_PORT=3400, MASTER_USER='repl_user',MASTER_PASSWORD=

51、'repl_user', master_auto_position=1;mysql>start slave;mysql>show slave statusG 出現(xiàn)如下信息表示主從復(fù)制成功： Slave_IO_Running: Yes Slave_SQL_Running: Yes3.2 管理工具(WINDOWS )3.2.1 MySQL WorkbenchMYSQL官方工具，可以操作MYSQL，同時(shí)可以設(shè)計(jì)的ER/數(shù)據(jù)庫(kù)建模工具。使用它設(shè)計(jì)和創(chuàng)建新的數(shù)據(jù)庫(kù)圖示，建立數(shù)據(jù)庫(kù)文檔，以及進(jìn)行復(fù)雜的MySQL 遷移（最新版本不支持oracle到mysql）。3.2.2 SQL

52、YOG第三方工具（非開(kāi)源）易于使用的、快速而簡(jiǎn)潔的圖形化管理MYSQL數(shù)據(jù)庫(kù)的工具。3.2.3 SQL DEVELOPERORACLE官方工具，支持同時(shí)操作 oracle,mysql數(shù)據(jù)庫(kù)。3.3 語(yǔ)句優(yōu)化工具3.3.1 Explain語(yǔ)法：explain select .Select_type:查詢(xún)類(lèi)型，主要以下幾種值SIMPLE:除子查詢(xún)或者 union之外的簡(jiǎn)單查詢(xún)。UNION： 第二個(gè)查詢(xún)開(kāi)始的所有select，第一個(gè)是PRIMARY；PRIMARY:子查詢(xún)中的最外層查詢(xún)，不是主鍵查詢(xún)。TYPE：查詢(xún)方式（以下說(shuō)明順序表示性能由好到差）CONST:表中最多會(huì)匹配一條記錄（如primar

53、y key 或者unique index）。eq_ref:多表查詢(xún)中使用primary key 或者unique index。ref:多表查詢(xún)中使用普通索引ref_no_null: 條件中包含對(duì)空值的查詢(xún)。index_merge: 索引合并優(yōu)化unique_subquery: IN后面是一個(gè)查詢(xún)主鍵字段的子查詢(xún)。index_subquery: 后面是一個(gè)查詢(xún)非唯一索引的子查詢(xún)。rang:查表的范圍查詢(xún)index:索引查詢(xún)ALL：全表掃描Possible_keys:可以使用的索引，如果是NULL，表示沒(méi)有使用到索引（但以Key為準(zhǔn)）。KEY:實(shí)際執(zhí)行選擇的索引，從Possible_keys中選擇

54、索引，如果沒(méi)有是NULL。key_len:索引字段的長(zhǎng)度。ROWS:掃描行的數(shù)量。EXTRA：執(zhí)行情況的說(shuō)明和描述。3.3.2 PROFILEProfiling:查詢(xún)?cè)\斷分析工具，可以知道CPU，IO，SWAP等信息。操作如下：mysql>set profiling=1;mysql>select count(*) from test;mysql>show profile cpu,block io for query 1;3.4 SQL性能檢查數(shù)據(jù)庫(kù)運(yùn)行中，定位當(dāng)前SQL性能分查看進(jìn)程信息得到運(yùn)行的SQL，通過(guò)慢日志查詢(xún)得到SQL運(yùn)行，然后通過(guò)前面介紹的explain工具和pr

55、ofile工具查看SQL運(yùn)行是否高效。3.4.1 查看進(jìn)程信息show processlist得到當(dāng)前會(huì)話(huà)的實(shí)時(shí)信息，其關(guān)鍵字段說(shuō)明：ID：連接ID，kill操作使用此值；USER：登錄用戶(hù)；HOST：客戶(hù)連接主機(jī)和端口DB:連接的缺省數(shù)據(jù)庫(kù)。COMMAND：當(dāng)前執(zhí)行命令：常用如sleep，Query，Connect。TIME：持續(xù)的時(shí)間。STATE：當(dāng)前SQL語(yǔ)句執(zhí)行的狀態(tài)（常見(jiàn)狀態(tài)后面介紹） INFO：顯示SQL語(yǔ)句（長(zhǎng)SQL可能不全）STATE常見(jiàn)狀態(tài) 列表：State說(shuō)明Sleep等待客戶(hù)端發(fā)送新請(qǐng)求，當(dāng)前空閑Updating搜索記錄并修改。Creating sort index創(chuàng)建臨時(shí)表，一般帶有order by操作Sending data查詢(xún)數(shù)據(jù)，并發(fā)送給客戶(hù)端User sleep客戶(hù)端發(fā)起的sle