MySQL 部分整理

yy6468發表於2019-01-10
MySql

mysql部分資料整理

1.事務

1、原子性（Atomicity）：事務開始後所有操作，要麼全部做完，要麼全部不做，不可能停滯在中間環節。事務執行過程中出錯，會回滾到事務開始前的狀態，所有的操作就像沒有發生一樣。也就是說事務是一個不可分割的整體，就像化學中學過的原子，是物質構成的基本單位。

2、一致性（Consistency）：事務開始前和結束後，資料庫的完整性約束沒有被破壞 。比如A向B轉賬，不可能A扣了錢，B卻沒收到。

3、隔離性（Isolation）：同一時間，只允許一個事務請求同一資料，不同的事務之間彼此沒有任何干擾。比如A正在從一張銀行卡中取錢，在A取錢的過程結束前，B不能向這張卡轉賬。

4、永續性（Durability）：事務完成後，事務對資料庫的所有更新將被儲存到資料庫，不能回滾。

2.事務的併發問題

1、髒讀：事務A讀取了事務B更新的資料，然後B回滾操作，那麼A讀取到的資料是髒資料

2、不可重複讀：事務 A 多次讀取同一資料，事務 B 在事務A多次讀取的過程中，對資料作了更新並提交，導致事務A多次讀取同一資料時，結果 不一致。

3、幻讀：系統管理員A將資料庫中所有學生的成績從具體分數改為ABCDE等級，但是系統管理員B就在這個時候插入了一條具體分數的記錄，當系統管理員A改結束後發現還有一條記錄沒有改過來，就好像發生了幻覺一樣，這就叫幻讀。

小結：不可重複讀的和幻讀很容易混淆，不可重複讀側重於修改，幻讀側重於新增或刪除。解決不可重複讀的問題只需鎖住滿足條件的行，解決幻讀需要鎖表

3.事務隔離級別

事務隔離級別 髒讀 不可重複讀 幻讀
讀未提交（read-uncommitted） 是 是 是
讀提交（read-committed） 否 是 是
可重複讀（repeatable-read） 否 是 是
序列讀（serializable） 否 否 否

mysql預設的事務隔離級別為可重複讀（repeatable-read）

4.mysql樂觀鎖與悲觀鎖

4.1.1 悲觀鎖

悲觀鎖：，它指的是對資料被外界（包括本系統當前的其他事務，以及來自外部系統的事務處理）修改持保守態度，因此，在整個資料處理過程中，將資料處於鎖定狀態。悲觀鎖的實現，往往依靠資料庫提供的鎖機制（也只有資料庫層提供的鎖機制才能真正保證資料訪問的排他性，否則，即使在本系統中實現了加鎖機制，也無法保證外部系統不會修改資料）。
注：要使用悲觀鎖，我們必須關閉mysql資料庫的自動提交屬性，因為MySQL預設使用autocommit模式，也就是說，當你執行一個更新操作後，MySQL會立刻將結果進行提交。

4.1.2 Row Lock與Table Lock（行鎖與表鎖）

MySQL InnoDB預設Row-Level Lock，所以只有「明確」地指定主鍵，MySQL 才會執行Row lock (只鎖住被選取的資料) ，否則MySQL 將會執行Table Lock (將整個資料表單給鎖住)。

4.1.3 悲觀鎖的缺點

悲觀鎖大多數情況下依靠資料庫的鎖機制實現，以保證操作最大程度的獨佔性。如果加鎖的時間過長，其他使用者長時間無法訪問，影響了程式的併發訪問性，同時這樣對資料庫效能開銷影響也很大，特別是對長事務而言，這樣的開銷往往無法承受

4.2.1 樂觀鎖

樂觀鎖假設認為資料一般情況下不會造成衝突，所以在資料進行提交更新的時候，才會正式對資料的衝突與否進行檢測，如果發現衝突了，則讓返回使用者錯誤的資訊，讓使用者決定如何去做。

4.2.2 樂觀鎖的實現方法

使用資料版本（Version）記錄機制實現，這是樂觀鎖最常用的一種實現方式。何謂資料版本？即為資料增加一個版本標識，一般是透過為資料庫表增加一個數字型別的 “version” 欄位來實現。當讀取資料時，將version欄位的值一同讀出，資料每更新一次，對此version值加一。當我們提交更新的時候，判斷資料庫表對應記錄的當前版本資訊與第一次取出來的version值進行比對，如果資料庫表當前版本號與第一次取出來的version值相等，則予以更新，否則認為是過期資料

1.查詢出商品資訊

select (status,status,version) from t_goods where id=#{id}

2.根據商品資訊生成訂單

3.修改商品status為2

update t_goods 

set status=2,version=version+1

where id=#{id} and version=#{version};

5 索引

5.1 索引型別

1.普通索引 : 最基本的索引，它沒有任何限制

    直接建立索引
    CREATE INDEX index_name ON table(column(length))

    –修改表結構的方式新增索引
    ALTER TABLE table_name ADD INDEX index_name ON (column(length))

    –建立表的時候同時建立索引
    CREATE TABLE `table` (
        `id` int(11) NOT NULL AUTO_INCREMENT ,

        `title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,

        `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,

        `time` int(10) NULL DEFAULT NULL ,

        PRIMARY KEY (`id`),

        INDEX index_name (title(length))
    )

    –刪除索引
    DROP INDEX index_name ON table

2.唯一索引 : 與普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值

     –建立唯一索引
    CREATE UNIQUE INDEX indexName ON table(column(length))

    –修改表結構
    ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))

    –建立表的時候直接指定
    CREATE TABLE `table` (
        `id` int(11) NOT NULL AUTO_INCREMENT ,

        `title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,

        `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,

        `time` int(10) NULL DEFAULT NULL ,

         PRIMARY KEY (`id`),

         UNIQUE indexName (title(length))

    );

3 全文索引（FULLTEXT）:僅可用於 MyISAM 表 , (CHAR、VARCHAR或TEXT列)

–建立表的適合新增全文索引
    CREATE TABLE `table` (

    `id` int(11) NOT NULL AUTO_INCREMENT ,

    `title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,

    `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,

    `time` int(10) NULL DEFAULT NULL ,

    PRIMARY KEY (`id`),

    FULLTEXT (content)

    );

    –修改表結構新增全文索引
    ALTER TABLE article ADD FULLTEXT index_content(content)

    –直接建立索引
    CREATE FULLTEXT INDEX index_content ON article(content)

4 聯合索引（最左字首原則）

ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10))

這樣的組合索引，其實是相當於分別建立了下面兩組組合索引：

–title,time

–title

5 主鍵索引 ： 不可以為空 ， 唯一

5.1.2 索引的缺點

雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對錶進行INSERT、UPDATE和DELETE。因為更新表時，MySQL不僅要儲存資料，還要儲存一下索引檔案。建立索引會佔用磁碟空間的索引檔案。一般情況這個問題不太嚴重，但如果你在一個大表上建立了多種組合索引，索引檔案的會膨脹很快。

5.2 索引最佳化建議
1 索引不會包含有NULL值的列 : 只要列中包含有NULL值都將不會被包含在索引中，複合索引中只要有一列含有NULL值，那麼這一列對於此複合索引就是無效的。所以我們在資料庫設計時不要讓欄位的預設值為NULL。

2 使用短索引 ： 短索引不僅可以提高查詢速度而且可以節省磁碟空間和I/O操作。例如，如果有一個CHAR(255)的列，如果在前10個或20個字元內，多數值是惟一的，那麼就不要對整個列進行索引。

3. 索引列排序：MySQL查詢只使用一個索引，因此如果where子句中已經使用了索引的話，那麼order by中的列是不會使用索引的。因此資料庫預設排序可以符合要求的情況下不要使用排序操作；儘量不要包含多個列的排序，如果需要最好給這些列建立複合索引。

4. like語句操作 ： like “%aaa%” 不會使用索引而like “aaa%”可以使用索引。

5. 不要在列上進行運算 : 例如：select * from users where YEAR(adddate)<2007，將在每個行上進行運算，這將導致索引失效而進行全表掃描，因此我們可以改成：select * from users where adddate<’2007-01-01′

MySQL只對一下運算子才使用索引：<,<=,=,>,>=,between,in,以及某些時候的like(不以萬用字元%或開頭的情形)。而理論上每張表裡面最多可建立16個索引，不過除非是資料量真的很多，否則過多的使用索引也不是那麼好玩的，比如我剛才針對text型別的欄位建立索引的時候，系統差點就卡死了。

5.1聚簇索引和非聚簇索引

非聚簇索引 (myisam) :主鍵索引與 二級索引 ,葉子頁面上儲存著 資料儲存的地址資訊,每次透過索引檢索到所需行號後，還需要透過葉子上的磁碟地址去磁碟內取資料

聚簇索引(innodb) : 主鍵索引的葉子頁面上儲存的所有資料 ， 二級索引葉子頁面上儲存著主鍵id，只需要透過主鍵id去主鍵索引上面取資料 ， 不需要回行取資料

這樣便避免了回行操作所帶來的時間消耗。 使得 InnoDB 在某些查詢上比 MyISAM 還要快！

關於查詢時間，一般認為 MyISAM 犧牲了功能換取了效能，查詢更快。但事實並不一定如此。多數情況下，MyISAM 確實比 InnoDB 查的快 。但是查詢時間受多方面因素影響。InnoDB 查詢變慢得原因是因為支援事務、回滾等等，使得 InnoDB的葉子頁面實際上還包含有事務id（換句話說就是版本號） 以及回滾指標。

聚簇索引是按照資料存放的物理位置為順序的，而非聚簇索引就不一樣了；聚簇索引能提高多行檢索的速度，而非聚簇索引對於單行的檢索很快。

MyISAM的 索引檔案.MYI 和 資料檔案.MYD 是分開儲存的 是相對獨立的

InnoDB的資料檔案只有 資料結構檔案.frm 和 資料檔案.idb 其中.idb中存放的是資料和索引資訊 是存放在一起的

部分概念：

回行：透過葉子上的磁碟地址去磁碟內取資料

參考文章：

http://www.cnblogs.com/crazylqy/p/7615388....
https://www.cnblogs.com/huanongying/p/7021...
https://www.cnblogs.com/loveyouyou616/p/76...

本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章