事務(wù)（Transaction），就是將一組SQL語句放在同一批次內(nèi)去執(zhí)行，如果一個SQL語句出錯，則該批次內(nèi) 的所有SQL都將被取消執(zhí)行。

一個事務(wù)中如果有一個數(shù)據(jù)庫操作失敗，那么整個事務(wù)的所有數(shù)據(jù)庫操作都會失敗，數(shù)據(jù)庫數(shù)據(jù)就會回滾到該事務(wù)開始之前的狀態(tài)。

MySQL數(shù)據(jù)庫中僅InnoDB和BDB類型的數(shù)據(jù)庫表支持事務(wù)。

1.Atomic 原子性

意味著數(shù)據(jù)庫中的 事務(wù)執(zhí)行是作為原 子粒度。即不可再 分，整個語句要么 執(zhí)行，要么不執(zhí)行 。

2.Consist 一致性

即在事務(wù)開始之前和 事務(wù)結(jié)束以后，數(shù)據(jù)庫的完整性約束沒有被破壞。注：過程中的一致性是不保證的

3. Isolated 隔離性

事務(wù)的執(zhí)行是互不干擾的，一個事務(wù)不可能看到其他事務(wù)運行時，中間某一時刻的數(shù)據(jù)。

4.Durable 持久性

意味著在事務(wù)完成以后 ，該事務(wù)所對數(shù)據(jù)庫所 作的更改便持久的保存 在數(shù)據(jù)庫之中，并不會被回滾。

1.START TRANSACTION

開始一個事務(wù),標(biāo)記事務(wù)的起始點。

2.SET AUTOCOMMIT

?使用該語句來改變自動提交模式，等于0時關(guān)閉自動提交模式，等于1時開啟自動提交模式。默認為1，使用事務(wù)時為0。

3.COMMIT

提交一個事務(wù)給數(shù)據(jù)庫。

4.ROLLBACK

將事務(wù)回滾，數(shù)據(jù)回到本次事務(wù)的初始狀態(tài)。

01 關(guān)閉MySQL自動提交? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? SET AUTOCOMMIT =0；

02 ?開啟一個事務(wù)，標(biāo)記事務(wù)的起始點 ? ??????????????? START TRANSACTION;

03-1 向數(shù)據(jù)庫提交事務(wù)? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? COMMIT;

03-2 將事務(wù)回滾，所有的數(shù)據(jù)庫操作被取消????????? ?ROLLBACK；

04 開啟MySQL自動提交? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?SET AUTOCOMMIT =1；

?事務(wù)的原子性、一致性和持久性由事務(wù)的 redo 日志和undo 日志來保證。

? REDO LOG 稱為 重做日志 ，提供再寫入操作，恢復(fù)提交事務(wù)修改的頁操作，用來保證事務(wù)的持久性。

? UNDO LOG 稱為 回滾日志 ，回滾行記錄到某個特定版本，用來保證事務(wù)的原子性、一致性。

?1.什么是事務(wù)的隔離性：

為了讓不同的事務(wù)之間相互不存在干擾，就需要對事務(wù)的操作進行隔離， 事務(wù)的隔離性也就是將操作同一個數(shù)據(jù)的事務(wù)相互分離，讓操作之間分開有序的執(zhí)行。

2.用什么方式實現(xiàn)事務(wù)的隔離性:

通常數(shù)據(jù)庫里都是采用鎖的機制，保證事務(wù)之間的隔離性。

鎖分類：

基于鎖的屬性分類：共享鎖（讀鎖、S鎖）、排他鎖（寫鎖，X鎖）。

基于鎖的粒度分類：表鎖、行鎖(記錄鎖、間隙鎖、臨鍵鎖)。

基于鎖的狀態(tài)分類：意向共享鎖、意向排它鎖。

事務(wù)并發(fā)問題：

在事務(wù)并發(fā)執(zhí)行的時候，如果不進行事務(wù)隔離，那么就會產(chǎn)生臟寫、臟讀、 重復(fù)讀、幻讀的問題。

事務(wù)的隔離級別：

?1. READ_UNCOMMITTED ??????? 讀未提交

2. READ_COMMITTED ????????????? 讀提交（不可重復(fù)讀）

3. REPEATABLE_READ??????????????? 可重復(fù)讀

4. SERIALIZABLE?????????????????????? 串行化

每個隔離級別都針對事務(wù)并發(fā)問題中的一種或幾種進行解決，事務(wù)級別越高，解決的 并發(fā)事務(wù)問題也就越多，同時也意味著加的鎖就越多，所以性能也會越差。

1.臟寫（ Dirty Write ）

對于兩個事務(wù) Session A、Session B，如果事務(wù)Session A 修改了 另一個 未提交 事務(wù)Session B 修 改過 的數(shù)據(jù)，那就意味著發(fā)生了 臟寫

2.臟讀（ Dirty Read ）

對于兩個事務(wù) Session A、Session B，Session A 讀取 了已經(jīng)被 Session B 更新 但還 沒有被提交的字段。 之后若 Session B 回滾 ，Session A 讀取 的內(nèi)容就是 臨時且無效 的。 Session A和Session B各開啟了一個事務(wù)，Session B中的事務(wù)先將studentno列為1的記錄的name 列更新 為'張三'，然后Session A中的事務(wù)再去查詢這條studentno為1的記錄，如果讀到列name的值為' 張三'，而 Session B中的事務(wù)稍后進行了回滾，那么Session A中的事務(wù)相當(dāng)于讀到了一個不存在的數(shù)據(jù)， 這種現(xiàn)象 就稱之為 臟讀 。 事務(wù)并發(fā)時出現(xiàn)的問題

3.不可重復(fù)讀（ Non-Repeatable Read ）

對于兩個事務(wù)Session A、Session B，Session A 讀取 了一個字段，然后 Session B 更新 了該字段。 之后 Session A 再次讀取 同一個字段， 值就不同 了。那就意味著發(fā)生了不可重復(fù)讀。 我們在Session B中提交了幾個 隱式事務(wù) （注意是隱式事務(wù)，意味著語句結(jié)束事務(wù)就提交了），這些事務(wù) 都修改了studentno列為1的記錄的列name的值，每次事務(wù)提交之后，如果Session A中的事務(wù)都可以查看 到最新的值，這種現(xiàn)象也被稱之為 不可重復(fù)讀 。

?4.幻讀（ Phantom ）

對于兩個事務(wù)Session A、Session B, Session A 從一個表中 讀取 了一個字段, 然后 Session B 在該表中 插 入 了一些新的行。 之后, 如果 Session A 再次讀取 同一個表, 就會多出幾行。那就意味著發(fā)生了幻讀。 Session A中的事務(wù)先根據(jù)條件 studentno > 0這個條件查詢表student，得到了name列值為'張三'的記錄； 之后Session B中提交了一個 隱式事務(wù) ，該事務(wù)向表student中插入了一條新記錄；之后Session A中的事務(wù) 再根據(jù)相同的條件 studentno > 0查詢表student，得到的結(jié)果集中包含Session B中的事務(wù)新插入的那條記 錄，這種現(xiàn)象也被稱之為 幻讀 。我們把新插入的那些記錄稱之為 幻影記錄 。

?MVCC （Multiversion Concurrency Control），多版本并發(fā)控制。顧名思義，MVCC 是通過數(shù)據(jù)行的多個 版 本管理來實現(xiàn)數(shù)據(jù)庫的 并發(fā)控制 。這項技術(shù)使得在InnoDB的事務(wù)隔離級別下執(zhí)行 一致性讀 操作有了保證。換言之，就是為了查詢一些正在被另一個事務(wù)更新的行，并且可以看到它們被更新之前的值，這樣 在做查詢的時候就不用等待另一個事務(wù)釋放鎖。

MVCC 的實現(xiàn)依賴于：隱藏字段、Undo Log、Read View。

?InnoDB就是通過MVCC機制解決可重復(fù)讀中的幻讀問題。

?數(shù)學(xué)函數(shù) 用作常規(guī)的數(shù)學(xué)運算

字符串函數(shù) 對于字符串類型的字段處理

日期時間函數(shù) 對于日期和時間類型的字段進行處理

聚合函數(shù) 常用于GROUP BY從句的 SELECT查詢中

自定義函數(shù) 根據(jù)實際需求自定義函數(shù)

MySQL函數(shù)，是一種控制流程函數(shù)，屬于數(shù)據(jù)庫用語言。

常用的日期函數(shù)

CURDATE() 返回當(dāng)前的日期HOUR(time)返回time的小時值(0~23)

CURTIME() 返回當(dāng)前的時間MINUTE(time)返回time的分鐘值(0~59)

NOW()返回當(dāng)前的日期和時間SECOND(time)返回time的秒(0~59)

DAYOFWEEK(dat e) 返回date所代表的一星期中 的第幾天(1~7)

DATE_FORMAT(d ate,fmt) 依照指定的fmt格式格式化 日期date值 DAYOFMONTH( date) 返回date是一個月的第幾天 (1~31)

DAYOFYEAR(dat e) 返回date是一年的第幾天 (1~366)

WEEK(date)返回日期date為一年中第 幾周(0~53)

DAY(date)返回date的日 MONTH(date) 返回date的月份值(1~12)

YEAR(date)返回日期date的年份 (1000~9999)

查詢兩個日期的時間差

DATEDIFF(expr1,expr2)

-- select datediff(now(), '2020-9-1 15:30:30');

-- select datediff('2020-9-1 15:30:30',now()); TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

-- year / --month / -- DAY

# select TIMESTAMPDIFF(DAY,now(),'2020-9-1 15:30:30')

# select TIMESTAMPDIFF(DAY,'2020-9-1 15:30:30',now())

-- HOUR

# select TIMESTAMPDIFF(HOUR,now(),'2020-9-1 15:30:30')

# select TIMESTAMPDIFF(HOUR,'2020-9-1 15:30:30',now())

-- MINUTE

# select TIMESTAMPDIFF(MINUTE,now(),'2020-9-1 15:30:30')

?# select TIMESTAMPDIFF(MINUTE,'2020-9-1 15:30:30',now())

-- SECOND

?# select TIMESTAMPDIFF(SECOND,now(),'2020-9-1 15:30:30')

# select TIMESTAMPDIFF(SECOND,'2020-9-1 15:30:30',now())

查詢距離時間的間隔時間

select NOW(), NOW() + INTERVAL 1 year

select DATE_ADD(NOW(),INTERVAL 1 year)

CONCAT(s1,s2...,s n)???? 將s1,s2...,sn連接成字符串

CONCAT_WS(sep, s1,s2...,sn) 將s1,s2...,sn連接成字符串，并用sep 字符間隔 POSITION(subs trin str) 返回子串substr在字符串str中第一次 出現(xiàn)的位置，從1開始 LOWER(str)返回將字符串str中所有字符改變?yōu)樾?寫后的結(jié)果

UPPER(str)返回將字符串str中所有字符轉(zhuǎn)變?yōu)榇髮懞蟮慕Y(jié)果

ABS(x)返回x的絕對值

LOG(x,y)返回x的以y為底的對數(shù)

BIN(x)返回x的二進制

MOD(x,y)返回x/y的模（余數(shù)）

CEILING(x)返回大于x的最小整數(shù)值PI()返回pi的值（圓周率）

?EXP(x) 返回值e（自然對數(shù)的底）的x次方

SIGN(x) 返回代表數(shù)字x的符號的值

FLOOR(x)返回小于x的最大整數(shù)值

ROUND(x,y)返回參數(shù)x的四舍五入的有y位小 數(shù)的值

GREATEST(x1,x2,. ..,xn)返回集合中最大的值

RAND()返回０到１內(nèi)的隨機值,可以通過 提供一個參數(shù)(種子)使RAND()隨 機數(shù)生成 TRUNCATE(x,y)返回數(shù)字x截短為y位小數(shù)的結(jié)果

AVG(col) 返回指定列的平均值

COUNT(col) 返回指定列中非NULL值的個數(shù)

MIN(col) 返回指定列的最小值

MAX(col) 返回指定列的最大值

SUM(col) 返回指定列的所有值之和

GROUP_CONCAT(col) 返回由屬于一組的列值連接組合而成的結(jié)果

?什么是慢查詢？

MySQL默認10秒內(nèi)沒有響應(yīng)SQL結(jié)果,則為慢查詢 ?？梢匀バ薷腗ySQL慢查詢默認時間

?Mysql對慢查詢的操作：

//顯示到mysql數(shù)據(jù)庫的連接數(shù)

?Show status like 'connections';

--查看慢查詢的狀態(tài)

Show variables like '%slow_query%';

--設(shè)置慢查詢的到表mysql.slow_log

Set global log_output='TABLE';

--設(shè)置慢查詢的時間

Set global long_query_time=3;

--開啟慢查詢

Set global slow_query_log='ON';

--慢查詢的次數(shù)

?Show status like 'slow_queries';

--慢查詢記錄

Select * From mysql.slow_log;

--慢查詢sql語句

Select convert(sql_textusingutf8) sql_textfrommysql.slow_log

--關(guān)閉慢查詢

Set global slow_query_log='OFF';

索引是對數(shù)據(jù)庫表中一列或多列的值進行排序的一種結(jié)構(gòu)，使用索引可快速訪問數(shù)據(jù)庫表中的特定信息。

高效性

利用索引可以提高數(shù)據(jù)庫的查詢效率.

完整性

用戶可以加速表和表之間的連接， 實現(xiàn)表與表之間的參照完整性

唯一性

索引可以確保所查的數(shù)據(jù)的唯一性

特殊能力

通過使用索引，可以在查詢過程中，使用優(yōu)化隱藏器，提高系統(tǒng)性能。

索引有它的缺點

?1.雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對表進行INSERT、 UPDATE和DELETE。

2.因為更新表時，MySQL不僅要保存數(shù)據(jù)，還要保存一下索引文件。建立索引會占用磁盤 空間的索引文件。

3. 如果你在一個大表上創(chuàng)建了多種組合索引，索引文件的會膨脹很快。索引文件大小會比數(shù)據(jù)文件還要大。

?索引只是提高效率的一個因素，如果你的MySQL有大數(shù)據(jù)量的表，就需要花時間研究建立 最優(yōu)秀的索引，或優(yōu)化查詢語句。

索引的分類

主鍵索引

在數(shù)據(jù)庫關(guān)系圖中為表定義一個主鍵將自動創(chuàng)建主鍵索引。

常規(guī)索引

最基本的索引類型，沒有唯一性之類的限制。

唯一索引

不允許具有索引值相同的行，從而禁止重復(fù)的索引或鍵值。

全文索引

搜索引擎的關(guān)鍵技術(shù)，用于檢索文本信息，可以是詞語或者段落。

全文索引的注意：

?1.Mysql 5.6之前版本，只有myisam支持全文索引，5.6之后，Innodb和myisam均支持全文索引。

2.只有char、varchar、text類型字段能創(chuàng)建全文索引。

3.當(dāng)大量寫入數(shù)據(jù)時，建議先寫入數(shù)據(jù)，后再建立全文索引，提高效率。

4.Mysql內(nèi)置ngram 解析器，可以解析中日韓三國文字。有漢字的一定要啟用它。

5.英文分詞用空格，逗號；中文分詞用 ngram_token_size 設(shè)定.

索引的管理

查看索引

SHOW INDEX(或KEYS) FROM 表名;

刪除索引

ALTER TABLE 表名 DROP PRIMARYKEY;

?DROP INDEX 索引名 ON 表名;

?ALTER TABLE 表名 DROP INDEX 索引名;

修改索引

ALERT TABLE 表名 ADD 索引類型（數(shù)據(jù)列名）;

ALTER TABLE add FULLTEXT INDEX (字段名1，字段2，，) [ WITH PARSER ngram];

優(yōu)化原則—創(chuàng)建索引

?1.【強制】InnoDB表必須主鍵為id int/bigint auto_increment，且主鍵值 禁止被更新 。

?2.【強制】InnoDB和MyISAM存儲引擎表，索引類型必須為 BTREE 。

?3.【建議】主鍵的名稱以 pk 開頭，唯一鍵以 uni 或 uk 開頭，普通索引以 idx 開頭，一律 使用小寫格式，以字段的名稱或縮寫作為后綴。

4.【建議】多單詞組成的columnname，取前幾個單詞首字母，加末單詞組成column_name。如: sample 表 member_id 上的索引：idx_sample_mid。

?5.【建議】單個表上的索引個數(shù) 不能超過6個 。

?6.【建議】在建立索引時，多考慮建立 聯(lián)合索引 ，并把區(qū)分度最高的字段放在最前面。

7.【建議】在多表 JOIN 的SQL里，保證被驅(qū)動表的連接列上有索引，這樣JOIN 執(zhí)行效率最高。

8.【建議】建表或加索引時，保證表里互相不存在 冗余索引 。 比如：如果表里已經(jīng)存在key(a,b)， 則key(a)為冗余索引，需要刪除。

優(yōu)化原則—SQL編寫

?1.【強制】程序端SELECT語句必須指定具體字段名稱，禁止寫成 *。

?2.【建議】程序端insert語句指定具體字段名稱，不要寫成INSERT INTO t1 VALUES(…)。 3.【建議】除靜態(tài)表或小表（100行以內(nèi)），DML語句必須有WHERE條件，且使用索引查找。

?4.【建議】INSERT INTO…VALUES(XX),(XX),(XX).. 這里XX的值不要超過5000個。 值過多雖然上線很 快，但會引起主從同步延遲。

5.【建議】SELECT語句不要使用UNION，推薦使用UNION ALL，并且UNION子句個數(shù)限制在5個以 內(nèi)。

?6.【建議】線上環(huán)境，多表 JOIN 不要超過5個表。

?7.【建議】減少使用ORDER BY，和業(yè)務(wù)溝通能不排序就不排序，或?qū)⑴判蚍诺匠绦蚨巳プ?。ORDER BY、GROUP BY、DISTINCT 這些語句較為耗費CPU，數(shù)據(jù)庫的CPU資源是極其寶貴的。

8.【建議】包含了ORDER BY、GROUP BY、DISTINCT 這些查詢的語句，WHERE 條件過濾出來的結(jié)果 集請保持在1000行以內(nèi)，否則SQL會很慢。

?9.【建議】對單表的多次alter操作必須合并為一次 對于超過100W行的大表進行alter table，必須經(jīng)過DBA審核，并在業(yè)務(wù)低峰期執(zhí)行，多個alter需整 合在一起。 因為alter table會產(chǎn)生 表鎖 ，期間阻塞對于該表的所有寫入，對于業(yè)務(wù)可能會產(chǎn)生極 大影響。

10.【建議】批量操作數(shù)據(jù)時，需要控制事務(wù)處理間隔時間，進行必要的sleep。

11.【建議】事務(wù)里包含SQL不超過5個。 因為過長的事務(wù)會導(dǎo)致鎖數(shù)據(jù)較久，MySQL內(nèi)部緩存、連接消耗過多等問題。

12.【建議】事務(wù)里更新語句盡量基于主鍵或UNIQUE KEY，如UPDATE… WHERE id=XX; 否則會產(chǎn)生間隙鎖，內(nèi)部擴大鎖定范圍，導(dǎo)致系統(tǒng)性能下降，產(chǎn)生死鎖。