一、事務

1、資料庫事務是指：作為單個邏輯作業單元執行的一系列操作（SQL陳述句），這些操作要么全部執行，要么全部不執行

2、事務管理是每個資料庫（oracle、mysql、db等）都必須實作的，

3、作用：保證了對資料操作的安全性

#還錢的例子
     A用銀行卡給B的支付寶轉賬1000
          1 將A銀行卡賬戶的資料減1000塊
          2 將B支付寶賬戶的資料加1000塊
      
         在操作多條資料的時候可能會出現某幾條操作不成功的情況 ,一條不成功就不會成功

4、事務的四大特性

ACID
  A:原子性（atomicity)
      一個事務是一個不可分割的單位，事務中包含的諸多操作
      要么同時成功要么同時失敗
  C:一致性（consistency)
      事務必須是使資料庫從一個一致性的狀態變到另外一個一致性的狀態
      一致性跟原子性是密切相關的
  I:隔離性（isolation）
      一個事務的執行不能被其他事務干擾
      （即一個事務內部的操作及使用到的資料對并發的其他事務是隔離的，并發執行的事務之間也是互相不干擾的）
  D:持久性（durability）
      也叫"永久性"
      一個事務一旦提交成功執行成功 那么它對資料庫中資料的修改應該是永久的
      接下來的其他操作或者故障不應該對其有任何的影響

5、如何使用事務

# 事務相關的關鍵字
  # 1 開啟事務
  start transaction;
  # 2 回滾(回到事務執行之前的狀態)
  rollback;
  # 3 確認(確認之后就無法回滾了)
  commit;
  
  """模擬轉賬功能"""
  create table user(
      id int primary key auto_increment,
      name char(16),
      balance int
  );
  insert into user(name,balance) values
  ('jason',1000),
  ('egon',1000),
  ('tank',1000);
  
  
  # 1 先開啟事務
  start transaction;
  # 2 多條sql陳述句
  update user set balance=900 where name='jason';
  update user set balance=1010 where name='egon';
  update user set balance=1090 where name='tank';
  
  """
  總結
      當你想讓多條sql陳述句保持一致性 要么同時成功要么同時失敗 
      你就應該考慮使用事務
  """

二、索引原理與慢查詢優化

ps:資料都是存在與硬碟上的，查詢資料不可避免的需要進行IO操作

1、索引:就是一種資料結構，類似于書的目錄，意味著以后在查詢資料的應該先找目錄再找資料，而不是一頁一頁的翻書，從而提升查詢速度降低IO操作

2、索引在MySQL中也叫“鍵”,是存盤引擎用于快速查找記錄的一種資料結構

　　  * primary key
　　  * unique key
　　 * index key

  注意： foreign key不是用來加速查詢用的，不在我們的而研究范圍之內

上面的三種key，前面兩種除了可以增加查詢速度之外各自還具有約束條件，而最后一種index key沒有任何的約束條件，只是用來幫助你快速查詢資料

3、本質

通過不斷的縮小想要的資料范圍篩選出最終的結果，同時將隨機事件(一頁一頁的翻)
變成順序事件(先找目錄、再找資料)
也就是說有了索引機制，我們可以總是用一種固定的方式查找資料
4、一張表中可以有多個索引(多個目錄)

5、索引雖然能夠幫助你加快查詢速度但是也有缺點

　　1 當表中有大量資料存在的前提下創建索引速度會很慢
　　2 在索引創建完畢之后對表的查詢性能會大幅度的提升但是寫的性能也會大幅度的降低

ps:索引不要隨意的創建！！！

6、b+樹

為什么說B+樹比B樹更適合做作業系統的資料庫索引和檔案索引？

（1）B+樹的磁盤讀寫的代價更低

B+樹內部結點沒有指向關鍵字具體資訊的指標，這樣內部結點相對B樹更小，

（2）B+樹的查詢更加的穩定

因為非終端結點并不是最終指向檔案內容的結點，僅僅是作為葉子結點中關鍵字的索引，這樣所有的關鍵字的查找都會走一條從根結點到葉子結點的路徑，所有的關鍵字查詢長度都是相同的，查詢效率相當，

### b+樹

  """
  只有葉子節點存放的是真實的資料 其他節點存放的是虛擬資料 僅僅是用來指路的
  樹的層級越高查詢資料所需要經歷的步驟就越多(樹有幾層查詢資料就需要幾步)，所以要想查詢快，樹的層數越少越好
  
  一個磁盤塊存盤是有限制的
  為什么建議你將id欄位作為索引
      占得空間少 一個磁盤塊能夠存盤的資料多
      那么久降低了樹的高度 從而減少查詢次數
  """
 

  ### 聚集索引(primary key)

  """
  聚集索引指的就是主鍵 
  Innodb  只有兩個檔案  直接將主鍵存放在了idb表中 
  MyIsam  三個檔案  單獨將索引存在一個檔案
  """

  ### 輔助索引(unique,index)

  查詢資料的時候不可能一直使用到主鍵，也有可能會用到name,password等其他欄位

  那么這個時候你是沒有辦法利用聚集索引，這個時候你就可以根據情況給其他欄位設定輔助索引(也是一個b+樹）


  """
  葉子節點存放的是資料對應的主鍵值
      先按照輔助索引拿到資料的主鍵值
      之后還是需要去主鍵的聚集索引里面查詢資料
  """
  

  ### 覆寫索引

  在輔助索引的葉子節點就已經拿到了需要的資料

  
  # 給name設定輔助索引
  select name from user where name='jason';
  # 非覆寫索引
  select age from user where name='jason';

7、測驗索引是否有效的代碼

 #1. 準備表
  create table s1(
  id int,
  name varchar(20),
  gender char(6),
  email varchar(50)
  );
  
  #2. 創建存盤程序，實作批量插入記錄
  delimiter $$ #宣告存盤程序的結束符號為$$
  create procedure auto_insert1()
  BEGIN
      declare i int default 1;
      while(i<3000000)do
          insert into s1 values(i,'jason','male',concat('jason',i,'@oldboy'));
          set i=i+1;
      end while;
  END$$ #$$結束
  delimiter ; #重新宣告分號為結束符號
  
  #3. 查看存盤程序
  show create procedure auto_insert1\G 
  
  #4. 呼叫存盤程序
  call auto_insert1();
  ?```
  
  ?``` mysql 
  # 表沒有任何索引的情況下
  select * from s1 where id=30000;
  # 避免列印帶來的時間損耗
  select count(id) from s1 where id = 30000;
  select count(id) from s1 where id = 1;
  
  # 給id做一個主鍵
  alter table s1 add primary key(id);  # 速度很慢
  
  select count(id) from s1 where id = 1;  # 速度相較于未建索引之前兩者差著數量級
  select count(id) from s1 where name = 'jason'  # 速度仍然很慢
  
  
  """
  范圍問題
  """
  # 并不是加了索引，以后查詢的時候按照這個欄位速度就一定快   
  select count(id) from s1 where id > 1;  # 速度相較于id = 1慢了很多
  select count(id) from s1 where id >1 and id < 3;
  select count(id) from s1 where id > 1 and id < 10000;
  select count(id) from s1 where id != 3;
  
  alter table s1 drop primary key;  # 洗掉主鍵 單獨再來研究name欄位
  select count(id) from s1 where name = 'jason';  # 又慢了
  
  create index idx_name on s1(name);  # 給s1表的name欄位創建索引
  select count(id) from s1 where name = 'jason'  # 仍然很慢！！！
  """
  再來看b+樹的原理，資料需要區分度比較高，而我們這張表全是jason，根本無法區分
  那這個樹其實就建成了“一根棍子”
  """
  select count(id) from s1 where name = 'xxx';  
  # 這個會很快，我就是一根棍，第一個不匹配直接不需要再往下走了
  select count(id) from s1 where name like 'xxx';
  select count(id) from s1 where name like 'xxx%';
  select count(id) from s1 where name like '%xxx';  # 慢 最左匹配特性
  
  # 區分度低的欄位不能建索引
  drop index idx_name on s1;
  
  # 給id欄位建普通的索引
  create index idx_id on s1(id);
  select count(id) from s1 where id = 3;  # 快了
  select count(id) from s1 where id*12 = 3;  # 慢了  索引的欄位一定不要參與計算
  
  drop index idx_id on s1;
  select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';
  # 針對上面這種連續多個and的操作，mysql會從左到右先找區分度比較高的索引欄位，先將整體范圍降下來再去比較其他條件
  create index idx_name on s1(name);
  select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';  # 并沒有加速
  
  drop index idx_name on s1;
  # 給name，gender這種區分度不高的欄位加上索引并不難加快查詢速度
  
  create index idx_id on s1(id);
  select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';  # 快了  先通過id已經講資料快速鎖定成了一條了
  select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  # 慢了  基于id查出來的資料仍然很多，然后還要去比較其他欄位
  
  drop index idx_id on s1
  
  create index idx_email on s1(email);
  select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  # 快 通過email欄位一劍封喉 
#### 聯合索引

  select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  
  # 如果上述四個欄位區分度都很高，那給誰建都能加速查詢
  # 給email加然而不用email欄位
  select count(id) from s1 where name='jason' and gender = 'male' and id > 3; 
  # 給name加然而不用name欄位
  select count(id) from s1 where gender = 'male' and id > 3; 
  # 給gender加然而不用gender欄位
  select count(id) from s1 where id > 3; 
  
  # 帶來的問題是所有的欄位都建了索引然而都沒有用到，還需要花費四次建立的時間
  create index idx_all on s1(email,name,gender,id);  # 最左匹配原則，區分度高的往左放
  select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  # 速度變快

8、查詢優化神器-explain

執行計劃：讓mysql預估執行操作(一般正確)
    all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
    id,email
    
    慢：
        select * from userinfo3 where name='alex'
        
        explain select * from userinfo3 where name='alex'
        type: ALL(全表掃描)
            select * from userinfo3 limit 1;
    快：
        select * from userinfo3 where email='alex'
        type: const(走索引)

9、慢查詢優化的基本步驟

0.先運行看看是否真的很慢，注意設定SQL_NO_CACHE
1.where條件單表查，鎖定最小回傳記錄表，這句話的意思是把查詢陳述句的where都應用到表中回傳的記錄數最小的表開始查起，單表每個欄位分別查詢，看哪個欄位的區分度最高
2.explain查看執行計劃，是否與1預期一致（從鎖定記錄較少的表開始查詢）
3.order by limit 形式的sql陳述句讓排序的表優先查
4.了解業務方使用場景
5.加索引時參照建索引的幾大原則
6.觀察結果，不符合預期繼續從0分析

10、慢日志管理

慢日志
            - 執行時間 > 10
            - 未命中索引
            - 日志檔案路徑
            
        配置：
            - 記憶體
                show variables like '%query%';
                show variables like '%queries%';
                set global 變數名 = 值
            - 組態檔
                mysqld --defaults-file='E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\my-default.ini'
                
                my.conf內容：
                    slow_query_log = ON
                    slow_query_log_file = D:/....
                    
                注意：修改組態檔之后，需要重啟服務

轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/391.html

標籤：Python

上一篇：Python pandas 實作無縫銜接Bokeh

下一篇：Python 零基礎快速入門！

MYSQL——事務，索引原理與慢查詢優化

一、事務

二、索引原理與慢查詢優化