網(wǎng)頁和網(wǎng)站做哪個好用嗎個人如何優(yōu)化網(wǎng)站有哪些方法
什么是事務(wù)?
事務(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ù)。
事務(wù)的ACID原則
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ù)庫之中,并不會被回滾。
MySQL實現(xiàn)事務(wù)的方法
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)。
MySQL實現(xiàn)事務(wù)的步驟
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ù)的原子性、一致性和持久性由事務(wù)的 redo 日志和undo 日志來保證。
? REDO LOG 稱為 重做日志 ,提供再寫入操作,恢復(fù)提交事務(wù)修改的頁操作,用來保證事務(wù)的持久性。
? UNDO LOG 稱為 回滾日志 ,回滾行記錄到某個特定版本,用來保證事務(wù)的原子性、一致性。
事務(wù)的隔離性
?1.什么是事務(wù)的隔離性:
為了讓不同的事務(wù)之間相互不存在干擾,就需要對事務(wù)的操作進行隔離, 事務(wù)的隔離性也就是將操作同一個數(shù)據(jù)的事務(wù)相互分離,讓操作之間分開有序的執(zhí)行。
2.用什么方式實現(xiàn)事務(wù)的隔離性:
通常數(shù)據(jù)庫里都是采用鎖的機制,保證事務(wù)之間的隔離性。
MySql中的鎖
鎖分類:
基于鎖的屬性分類:共享鎖(讀鎖、S鎖)、排他鎖(寫鎖,X鎖)。
基于鎖的粒度分類:表鎖、行鎖(記錄鎖、間隙鎖、臨鍵鎖)。
基于鎖的狀態(tài)分類:意向共享鎖、意向排它鎖。
事務(wù)的隔離級別
事務(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ù)問題也就越多,同時也意味著加的鎖就越多,所以性能也會越差。
事務(wù)并發(fā)時出現(xiàn)的問題
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)象也被稱之為 幻讀 。我們把新插入的那些記錄稱之為 幻影記錄 。
InnoDB的MVCC
?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ù)?
?數(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)
常用的字符串函數(shù)
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é)果
常用的數(shù)學(xué)函數(shù)
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()返回0到1內(nèi)的隨機值,可以通過 提供一個參數(shù)(種子)使RAND()隨 機數(shù)生成 TRUNCATE(x,y)返回數(shù)字x截短為y位小數(shù)的結(jié)果
常用的聚合函數(shù)
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)生死鎖。