MySQL常見語法類問題
1厨诸、為啥update執(zhí)行后沒有生效呢零酪？
場景如下：
mysql> select col1,col2 from test;
+------+------+
| col1 | col2 |
+------+------+
| 0 | hot |
+------+------+
開發(fā)童鞋想將col1列的值改為：1 茶鹃， col2列的值改為：haha ，他的update語句如下：
update test set col1 = 1 and col2 = 'haha' WHERE updatetime > '2017-08-03';
他反饋如上SQL運(yùn)行時(shí)沒有報(bào)錯(cuò)钧排，但SQL執(zhí)行后沒有生效敦腔。。
??原因是SQL語法有誤喲
正確的語法是set 后的兩個(gè)字段之間應(yīng)該用英文逗號(hào)分隔恨溜，上面用了 and 喔符衔，and 引發(fā)的“靈異事件”。糟袁。判族。正確的SQL長這樣：
update test set col1 = 1,col2 = 'haha' WHERE updatetime > '2017-08-03';
那為啥錯(cuò)誤寫法的SQL沒有也沒有報(bào)錯(cuò)呢？
因?yàn)楸籑ySQL理解成了：
update test set col1 = 1 and col2 = 'haha' WHERE updatetime > '2017-08-03';
=>
update test set col1 = (1 and col2 = 'haha') WHERE updatetime > '2017-08-03';
=>
update test set col1 = (1 and 0) WHERE updatetime > '2017-08-03';
=>
update test set col1 = 0 WHERE updatetime > '2017-08-03';
所以项戴，這種情況下形帮，錯(cuò)誤的update執(zhí)行后相當(dāng)于啥都沒改（ 假設(shè)col1是字符串類型，那么如上錯(cuò)誤SQL就將col1列的字段內(nèi)容改成0了呀周叮，即業(yè)務(wù)數(shù)據(jù)改錯(cuò)了??太恐怖了１绯拧！）
2仿耽、varchar(N)可以放多少個(gè)中文字符呢合冀？
varchar(n) 表示n個(gè)字符，無論漢字和英文项贺，MySql都能存入 n 個(gè)字符君躺，僅實(shí)際字節(jié)長度有所區(qū)別。
MySQL5.0以上版本开缎，utf8字符集棕叫， varchar(n) 可以存n個(gè)中文字符。
【Tips】：
varchar 字段是將實(shí)際內(nèi)容單獨(dú)存儲(chǔ)在聚簇索引之外奕删，內(nèi)容開頭用1到2個(gè)字節(jié)表示實(shí)際長度（長度超過255時(shí)需要2個(gè)字節(jié)）俺泣，因此最大長度不能超過65535。
UTF-8：一個(gè)漢字 = 3個(gè)字節(jié)完残，英文是一個(gè)字節(jié)
GBK： 一個(gè)漢字 = 2個(gè)字節(jié)砌滞，英文是一個(gè)字節(jié)
在utf-8狀態(tài)下，varchar(N),N的最大值為： (65535-2)/3=21844坏怪，所以贝润，漢字最多可以存 21844個(gè)字符串, 英文也為 21844個(gè)字符串。
在 gbk 狀態(tài)下铝宵，varchar(N),N的最大值為： (65535-2)/2=32766打掘，此時(shí)华畏，漢字最多可以存 32766個(gè)字符串，英文也為 32766個(gè)字符串尊蚁。
3亡笑、帶子查詢的update被退單了诞丽，咋辦呢瑰步？
帶子查詢的SQL：
update tablename1 set idCardNo = (select idcard_no from tablename2 where id = bankCardId) where createtime > '2016-01-01 00:00:00';
上面的SQL怎么去子查詢呢？
第一種方法：
update tablename1,tablename2 set tablename1.idCardNo=tablename2.idcard_no where tablename1.bankCardId=tablename2.id and tablename1.createtime > '2016-01-01 00:00:00' ;
HOHO 是滴亮瓷，可以醬紫寫琴锭，還可以醬紫寫喔：
第二種方法：
update tablename1 A inner join tablename2 B on A.bankCardId=B.id set A.idCardNo=B.idcard_no where A.createtime > '2016-01-01 00:00:00' ;
你木有看錯(cuò)晰甚，確實(shí)可以這么寫，啦啦啦～(￣▽￣～)(～￣▽￣)～
一般帶子查詢的SQL可以試試看用join的方式改寫喲~
Tips：
1决帖、為啥不讓提交帶子查詢的更新呢厕九？
因?yàn)閹ё硬樵兊母骆i表時(shí)間可能會(huì)很長，不安全喲~
特別當(dāng)表比較大的時(shí)候地回，高風(fēng)險(xiǎn)扁远，基本無法操作。刻像。
2畅买、以上兩種改寫帶子查詢update的寫法，為啥推薦第一種呢细睡？
因?yàn)槟菢訉懳覀兛梢杂霉ぞ吒鶕?jù)主鍵ID分段更新大表谷羞， 既安全又快速還方便^_^
4、為什么InnoDB表建議用無業(yè)務(wù)含義的自增列做主鍵?
如果InnoDB表的數(shù)據(jù)寫入順序能和B+樹索引的葉子節(jié)點(diǎn)順序一致的話纹冤，這時(shí)候存取效率是最高的洒宝，也就是當(dāng)表中有自增 主鍵時(shí)购公，存取效率最高（減少B+樹分裂次數(shù)）萌京。無業(yè)務(wù)含義自增主鍵的好處：
1. 自增主鍵以利于插入性能的提高；
2. 自增主鍵設(shè)計(jì)(int,bigint)可以降低二級(jí)索引空間宏浩，提升二級(jí)索引的內(nèi)存命中率知残；
3. 自增主鍵可以減小page的碎片，提升空間和內(nèi)存的使用比庄。
表中需要確保唯一性的字段加唯一索引UK即可求妹。
5、索引怎么命名呀佳窑？
唯一索引：uk_<column_name>_<column_name>_······
一般索引：idx_<column_name>_<column_name>_······
（column_name 可與建表sql腳本中字段名保持一致制恍，若字段名過長，可用字段名縮寫）
Tips：
索引中注意字段順序喲神凑，分辨率高的字段放左側(cè)净神。
6何吝、同一個(gè)表的DDL操作腫么寫在一個(gè)alter table中呀？
某表新增字段鹃唯、新增索引爱榕、刪除冗余索引：
alter table tablename
add column type tinyint(1) not null default '0' COMMENT '類型',
add index IX_col1_col2(col1,col2),
drop index IX_col1;
7、where條件后的字段順序會(huì)影響索引使用嗎坡慌？
不影響的喲^_^
索引優(yōu)化Tips:
目標(biāo)：利用最小的索引成本找到最需要的行記錄黔酥。
原則：
* 最左前綴原則：MySQL會(huì)一直向右匹配直到遇到范圍查詢(>、<洪橘、between跪者、like)就停止匹配，比如a=1 and b=2 and c>3 and d=4 如果建立(a,b,c,d)順序的索引梨树，d是用不到索引的坑夯，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調(diào)整
* 避免重復(fù)索引：idx_abc多列索引,相當(dāng)于創(chuàng)建了(a)單列索引抡四，(a,b)組合索引以及(a,b,c)組合索引柜蜈。不在索引列使用函數(shù) 如 max(id)> 10 ,id+1>3 等
* 盡量選擇區(qū)分度高的列作為前綴索引：區(qū)分度的公式是count(distinct col)/count(*)，表示字段不重復(fù)的比例指巡，比例越大我們掃描的記錄數(shù)越少
8淑履、int（11）和 int（4）有啥區(qū)別？
int(N)藻雪，int代表整型秘噪，（N）代表字段的寬度，而不是存儲(chǔ)在數(shù)據(jù)庫中的具體的長度勉耀。他們之間除了在存儲(chǔ)的時(shí)候稍微有區(qū)別外指煎，在我們使用的時(shí)候是沒有區(qū)別的。加上zerofill后, 可以看到兩者區(qū)別便斥，比如 int(4) zerofill,你插入到數(shù)據(jù)庫里的是10,則實(shí)際插入為0010,即在前面補(bǔ)充加了00至壤。如果int(4)和int(11)不加zerofill,則它們沒有什么區(qū)別。
INT[(N)] [UNSIGNED] [ZEROFILL] N默認(rèn)為11
帶符號(hào)的范圍是-2147483648到2147483647枢纠。無符號(hào)的范圍是0到4294967295像街。
有圖有真相(*^__^*) 哦：
mysql> create table test1(col1 int(4) zerofill);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test1(col1) values(10);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test1;
+------+
| col1 |
+------+
| 0010 |
+------+
1 row in set (0.00 sec)
mysql> create table test2(col2 int(11) zerofill);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test2(col2) values(10);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test2;
+-------------+
| col2 |
+-------------+
| 00000000010 |
+-------------+
1 row in set (0.00 sec)
mysql> create table test3(col3 int(4),col4 int(11),col5 int);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into test3(col3,col4,col5) values(10,10,10);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test3;
+------+------+------+
| col3 | col4 | col5 |
+------+------+------+
| 10 | 10 | 10 |
+------+------+------+
1 row in set (0.00 sec)
9、varchar(N)不是變長字符串類型嗎晋渺，為啥建議N盡量辛铩？
主要是出于性能考慮木西。MySQL InnoDB類型的表畴栖，在進(jìn)行排序和創(chuàng)建臨時(shí)表一類的內(nèi)存操作時(shí)，會(huì)使用表定義中N的長度申請(qǐng)內(nèi)存八千，不會(huì)看字段具體用了多少空間哦吗讶。假設(shè)一頁可以放16K數(shù)據(jù)挪挤，那么N越小可以是不是可以放到內(nèi)存頁中的數(shù)據(jù)就更多了呢。查內(nèi)存可比查磁盤快了N個(gè)數(shù)量級(jí)关翎，醬紫還可以減少磁盤IO操作啦~
10扛门、select * 為啥不建議用?
- 不需要的字段會(huì)增加數(shù)據(jù)傳輸?shù)臅r(shí)間；
- 表中有大字段時(shí)纵寝，比如longtext這樣的大字段讀取到返回都是很慢的（當(dāng)字段長度超過768字節(jié)论寨，會(huì)把超出的數(shù)據(jù)放到溢出頁，因此讀取這條記錄會(huì)增加一次io操作）;
- select * 杜絕了索引覆蓋的可能性爽茴；
- 比如：select a from tablename where b=123456789 和 select * from tbname where b=123456789葬凳，表中有索引IX_b_a。前者要比后者的查詢速度快室奏，因?yàn)樗璧淖侄味伎梢栽谒饕现苯幽玫交鹧妫辉傩枰x取這條記錄了。
所以胧沫，從數(shù)據(jù)庫里讀出越多的數(shù)據(jù)昌简，查詢就會(huì)變得越慢。還是養(yǎng)成需要什么就取什么的好的習(xí)慣~\(≧▽≦)/~啦啦啦
11绒怨、為啥字段要盡量not null,不建議用NULL纯赎？
是時(shí)候祭出MySQL官方文檔啦：
NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.
所以，為什么捏：
- 可空列需要更多的存儲(chǔ)空間南蹂，需要一個(gè)額外字節(jié)作為判斷是否為NULL的標(biāo)志位犬金；
- 還需要MySQL進(jìn)行內(nèi)部特殊處理。MySQL難以優(yōu)化引用可空列查詢六剥，它會(huì)使索引晚顷、索引統(tǒng)計(jì)和值更加復(fù)雜；
- 還會(huì)導(dǎo)致MYISAM 中固定大小的索引變成可變大小的索引（雖然MyISAM引擎不推薦用了疗疟，哈哈）该默；
所以，最好總是讓字段保持not null哈秃嗜。
Tips：
1. 比如int权均、bigint類型顿膨，默認(rèn)值可以用0锅锨，字符串類型默認(rèn)值可以用'';
2. 如果是索引字段，一定要定義為not null喔;
12恋沃、limit及分頁查詢?nèi)绾蝺?yōu)化必搞？
limit offset,N 當(dāng)offset非常大時(shí)，效率很低囊咏。
原因是MySQL并不是跳過offset行然后單取N行恕洲，而是取offset+N行塔橡，返回時(shí)拋棄 offset 行，返回N行霜第，效率較低葛家。當(dāng)offset越大時(shí)，效率越低泌类。
優(yōu)化辦法：
- 1）從業(yè)務(wù)上優(yōu)化
辦法:
0. 應(yīng)避免在數(shù)據(jù)庫中做分頁癞谒；
1. 若無法避免，前端加緩存刃榨，減少落到庫的查詢操作弹砚；
2. 不允許翻過太多頁，比如不允許翻過100頁枢希；
3. 一次預(yù)取多頁桌吃，降低查詢次數(shù)。比如之前是limit 1000000苞轿，10茅诱，更新為一次取200頁： limit 1000000，2000搬卒；
- 2）不用offset让簿，用條件查詢
舉個(gè)栗子：
優(yōu)化前：
select id,name from test limit 1000000,10;
優(yōu)化后：
select name from test where id>1000000 limit 10;
記錄上次查詢最新or最大的id值，向后追溯 M行記錄秀睛。比如：條件中增加主鍵自增ID范圍尔当；
- 3）使用“延遲關(guān)聯(lián)”優(yōu)化
何謂"延遲關(guān)聯(lián)" ：通過使用覆蓋索引查詢返回需要的主鍵,再根據(jù)主鍵關(guān)聯(lián)原表獲得需要的數(shù)據(jù)；
栗子：
優(yōu)化前：
SELECT id,col1,col2,col3,col4,col5 FROM test where updatetime >='2017-08-08 00:00:01' ORDER BY id asc LIMIT 10000000,20;
優(yōu)化后：
select a.id,a.col1,a.col2,a.col3,a.col4,a.col5 from test ,(select id from test where updatetime >='2017-08-08 00:00:01' ORDER BY id asc LIMIT 10000000,20) tmp where test.id=tmp.id;
優(yōu)化后蹂安，查詢效率會(huì)比之前提高幾倍椭迎。
總結(jié)一下優(yōu)化思路：
避免數(shù)據(jù)量大時(shí)掃描過多的記錄。
使用有索引的列或主鍵進(jìn)行order by田盈；
記錄上次返回的主鍵畜号，在下次查詢時(shí)使用主鍵過濾。
- 4）使用Sphinx 搜索優(yōu)化
13允瞧、Online DDL啥原理呀简软？
目前，我司是通過Percona工具pt-online-schema-change（簡稱為pt-osc)來做在線DDL的述暂，通過改進(jìn)原生ddl的方式痹升，達(dá)到不鎖表在線修改表結(jié)構(gòu)。
其原理如下：
1. 創(chuàng)建新表_table_new
2. 修改新表_table_new add column/add index（此時(shí)新表是空表畦韭，DDL很快）
3. 創(chuàng)建insert/update/delete相關(guān)的3個(gè)觸發(fā)器
4. copy已有的數(shù)據(jù)疼蛾，通過觸發(fā)器將增量數(shù)據(jù)同步到新表
5. rename table，同時(shí)rename 原表table和新表_table_new
6. 刪除舊表table_old( 可以設(shè)置保留舊表)
7. 刪除觸發(fā)器
【使用限制】：
表中不能有觸發(fā)器艺配。因?yàn)橐粋€(gè)表上不能同時(shí)有2個(gè)相同類型的觸發(fā)器察郁。
使用 pt-osc 實(shí)際alter table過程中還是會(huì)引起 data meta lock 問題衍慎，所以不是完全不鎖表，可以理解為鎖表的時(shí)間變短了皮钠。
（還有另一種“Online” DDL工具：gh—ost稳捆，它是github的dba開源一款使用go語言開發(fā)的MySQL在線改表工具，解決了目前采用pt-online-schema-change會(huì)遇到的一些比如死鎖問題麦轰。拋棄了pt-online-schema-change使用trigger來同步增量數(shù)據(jù)的方法眷柔，通過假扮成slave獲取row格式的binlog的方式來獲取增量數(shù)據(jù)，思路很新穎原朝。不過gh—ost在新增唯一索引時(shí)會(huì)刪除重復(fù)數(shù)據(jù)驯嘱，這點(diǎn)比較坑，所以我們不用喳坠。）
14、索引越多越好嗎壕鹉？
非也非也~
原因：
1剃幌、索引會(huì)使數(shù)據(jù)修改操作變慢，還會(huì)在硬盤上占用相當(dāng)大空間晾浴。MySQL在運(yùn)行 時(shí)也要消耗資源維護(hù)索引负乡，因此索引并不是越多越好。要建立合適的索引喲~
2脊凰、有的索引建了也有可能根本沒用不到抖棘，可以用explain去確認(rèn)下喲~
15、哪種情況不建議建索引呀狸涌？
表記錄較少切省，如2000行以下；
索引的選擇性較低帕胆，一般需要高于0.1喲朝捆。Index Selectivity = count(distinct column)/count(*) ；
HOHO 是滴懒豹，如上情況可以不用建索引哈~
PS：請(qǐng)善待存在MySQL中的數(shù)據(jù)喔^_^ ~
