資料庫

目錄

資料庫

SQL陳述句

范式

主鍵外鍵

索引

事務

mysql

常用陳述句

啟動關閉連接資料庫

函式

DELETE與TRUNCATE的區別

索引

sql優化

表優化

查看和中止sql

慢查詢日志

存盤引擎

分表磁區

• SQL陳述句

  • DDL陳述句
    • 關鍵字——CREATE、ALTER、DROP、TRUNCATE等
    • 使用——DDL主要定義或改變表的結構，表之間的鏈接和約束等，在建表實使用
  • DML陳述句
    • 關鍵字——SELECT、UPDATE、INSERT、DELETE
    • 使用——用來對資料庫里的資料進行操作的語言
  • DCL陳述句
    • 關鍵字——GRANT、REVOKE等
    • 使用——用來設定或更改資料庫用戶或角色權限的陳述句

• 范式

  • 作用——減少冗余
  • 第一范式——要滿足屬性不可拆分
  • 第二范式——消除非主屬性對主屬性的部分依賴
  • 第三范式——要求一個關系中不包含已在其它關系已包含的非主關鍵字資訊
  • BCNF范式——在3NF基礎上消除對主碼子集的依賴
  • 反范式——沒有冗余的資料庫未必是最好的資料庫，有時為了提高運行效率和性能，低范式標準，適當保留冗余資料

• 主鍵外鍵

  • 主鍵——表的一個特殊欄位，該欄位能惟一地標識該表中的每條資訊；可以沒有主鍵，有主鍵不能為空；
  • 外鍵——特殊欄位，欄位sno是一個表A的屬性，且依賴于表B的主鍵；外鍵必須依賴于已存在表的主鍵；可以為空值

• 索引

  • 創建洗掉索引
    • 創建索引——ALTER TABLE table_name ADD INDEX idx_1(code);
    • 洗掉索引——DROP INDEX idx_1 ON table_name ;
  • 定義——索引由資料庫表中一列或多列組合而成，其作用是提高對表中資料的查詢速度；
  • 缺點——索引不是萬能的！會使資料修改操作變慢，占用相當大的空間，使用索引有限制
  • 優點——索引可以加快資料檢索操作
  • mysql中主鍵(primary key)和唯一鍵(unique)區別
    • 主鍵索引——保證唯一性,不允許為空,一個表中至多一個
    • 唯一索引——保證唯一性,允許一個空值,可以有多個
  • mysql指定索引 from tables force 索引名

• 事務

  • 概念：事務是應用程式中一系列嚴密的操作，所有操作必須成功完成，否則在每個操作中所作的所有更改都會被撤消
  • 特性
    • 原子性：整個事務中的所有操作，要么全部完成，要么全部不完成，不可能停滯在中間某個環節，事務在執行程序中發生錯誤，會被回滾（Rollback）到事務開始前的狀態，就像這個事務從來沒有執行過一樣
    • 一致性：在事務開始之前和事務結束以后，資料庫的完整性約束沒有被破壞
    • 隔離性：隔離狀態執行事務，使它們好像是系統在給定時間內執行的唯一操作，
    • 持久性：在事務完成以后，該事務所對資料庫所作的更改便持久的保存在資料庫之中，并不會被回滾

• mysql

  • 常用陳述句

    • 資料匯出
      • sql匯出：SELECT * FROM new_product INTO OUTFILE 'D:/Program Files (x86)/export_data/new_product.sql' fields terminated by '|' ;
    • 查看資料庫版本——select version() from dual;
    • mysql8.0之后支持row_numer——ROW_NUMBER() over(PARTITION by dynastyid ) rn

  • 啟動關閉連接資料庫

    • 啟動 mysql 服務：service mysqld start
    • 查看mysqld 狀態：mysqld status
    • 關閉mysql服務：service mysql stop
    • 重啟mysql服務：service restart stop
    • 連接資料庫：mysql -u root -p pwd

  • 函式

    • GROUP_CONCAT()函式——group by 的聚合函式，將group by產生的同一個分組中的值連接起來，回傳一個字串結果，可以指定分隔符
    • IF(expr,v1,v2)函式——if判斷類似3目運算子，expr運算式正確回傳v1,錯誤回傳v2
    • IFNULL(v1,v2)函式——空值轉換函式

  • DELETE與TRUNCATE的區別

    • DELETE是可以帶WHERE的，所以支持條件洗掉；而TRUNCATE只能洗掉整個表;
    • DELETE是DML，操作時原資料會被放到 rollback segment中，可以被回滾；而TRUNCATE是DDL，操作時不會進行存盤，不能進行回滾;
    • 在資料量比較小的情況下，DELETE和TRUNCATE的清理速度差別不是很大，但是資料量很大的時候TRUNCATE優勢大

  • 索引

    • MERGE:當使用視圖時，會把查詢視圖的陳述句和創建視圖的陳述句合并起來，形成一條陳述句，最后再從基表中查詢
      • 視圖中存在
      • 匯總函式或視窗函式（SUM()、MIN()、MAX()、COUNT()等）
      • DISTINCT
      • GROUP BY
      • HAVING
      • LIMIT
      • UNION或UNION ALL等不會使用索引
    • TEMPTABLE:當使用視圖時，會把創建視圖的陳述句的查詢結果當成一張臨時表，再從臨時表中進行篩選
    • UNDEFINED:未定義，自動，讓系統幫你選

  • sql優化

    • 索引優化
      • 索引使用情況
        • 配置全值,對索引中索引列都指定具體值;
        • 配置值得范圍,對索引的值能夠進行范圍查找;
        • 匹配最左前綴,僅僅使用索中的最左邊列進行查找,如col1+col2+col3的聯合索引,能被包含col1、(col1+col2)、(col1+col2+col3)的等值查詢利用到
        • 僅對索引查詢,當查詢的列都在索引欄位中時,查詢效率更高;
      • 不能使用索引的情況
        • 以%開頭的like查詢不能使用B-Tree索引
        • 資料型別出現隱式轉換時不能使用索引
        • 復合索引使用需滿足最左原則
        • 如果mysql估計使用索引比全表掃描慢,則不在使用索引
        • 用or分割開的條件
    • sql陳述句優化
      • 執行計劃
        • 陳述句——EXPLAIN SELECT陳述句
        • id:選擇識別符號——id值越大優先級越高，越先被執行;id相同時，執行順序由上至下
        • select_type:表示查詢的型別
          • SIMPLE(簡單SELECT，不使用UNION或子查詢等)
          • PRIMARY(子查詢中最外層查詢，查詢中若包含任何復雜的子部分，最外層的select被標記為PRIMARY)
          • UNION(UNION中的第二個或后面的SELECT陳述句)
          • DEPENDENT UNION(UNION中的第二個或后面的SELECT陳述句，取決于外面的查詢)
          • UNION RESULT(UNION的結果，union陳述句中第二個select開始后面所有select)
          • SUBQUERY(子查詢中的第一個SELECT，結果不依賴于外部查詢)
          • DEPENDENT SUBQUERY(子查詢中的第一個SELECT，依賴于外部查詢)
          • DERIVED(派生表的SELECT, FROM子句的子查詢)
          • UNCACHEABLE SUBQUERY(一個子查詢的結果不能被快取，必須重新評估外鏈接的第一行)
        • table:輸出結果集的表
        • partitions:匹配的磁區
        • type:表示表的連接型別——ALL、index、range、 ref、eq_ref、const、system、NULL（從左到右，性能從差到好）
        • possible_keys:表示查詢時，可能使用的索引
        • key:表示實際使用的索引
        • key_len:索引欄位的長度——不損失精確性的情況下，長度越短越好
        • ref:列與索引的比較
        • rows:掃描出的行數——估算出結果集行數，表示MySQL根據表統計資訊及索引選用情況，估算讀取的行數
        • filtered:按表條件過濾的行百分比
        • Extra:執行情況的描述和說明
          • Range checked for each record (index map: 0x4) (匹配欄位型別，編碼不相符等)
          • MySQL發現沒有使用好的索引，但是發現在前面的表的列值已知之后，可能會使用一些索引， 對于上表中的每一行組合，MySQL檢查是否可以使用range或index_merge訪問方法來檢索行，
          • https://www.cnblogs.com/MYSQLZOUQI/articles/3837828.html
          • converting HEAP to ondisk
          • 該執行緒正在將內部臨時表從 MEMORY 表轉換為磁盤表
          • Using join buffer (Block Nested Loop),
          • Using join buffer (Batched Key Access)
            • Block Nested-Loop Join演算法：將外層回圈的行/結果集存入join buffer, 內層回圈的每一行與整個buffer中的記錄做比較，從而減少內層回圈的次數，優化器管理引數optimizer_switch中中的block_nested_loop引數控制著BNL是否被用于優化器，默認條件下是開啟，若果設定為off，優化器在選擇 join方式的時候會選擇NLJ(Nested Loop Join)演算法，
            • Batched Key Access原理：對于多表join陳述句，當MySQL使用索引訪問第二個join表的時候，使用一個join buffer來收集第一個操作物件生成的相關列值，BKA構建好key后，批量傳給引擎層做索引查找，key是通過MRR介面提交給引擎的（mrr目的是較為順序）MRR使得查詢更有效率，要使用BKA，必須調整系統引數optimizer_switch的值，batched_key_access設定為on，因為BKA使用了MRR，因此也要打開MRR (參考http://www.cnblogs.com/chenpingzhao/p/6720531.html)

  • 表優化

    • 分析表 ANALYZE
    • 檢查表 check
    • 優化表 OPTIMIZE——通過可以消除洗掉和更新造成的磁盤碎片，從而減少空間的浪費 只讀鎖

  • 查看和中止sql

    • 查看正在運行的sql select * from information_schema.PROCESSLIST where info is not null;
    • 中止正在運行的sql kill process_id;

  • 慢查詢日志

    • 查詢是否開啟慢查詢日志(slow_query_log)——陳述句:show variables like "%slow%;
    • 開啟慢查詢日志——陳述句:set global slow_query_log = on
    • 查詢慢查詢閾值(long_query_time)——陳述句:show variables like "%long%;
    • 修改慢查詢閾值——陳述句:set global long_query_time = 5

  • 存盤引擎

    • 查看存盤引擎
      • 查看所有支持的 engine：show engines;
      • 查看當前庫的 engine：show variables like '%engine%;
    • innodb
      • 最常用，支持事務、回滾、自增、外鍵
      • 表結構存在.frm 檔案中
      • 資料和索引存在表空間中，聚集索引方式方式
      • 讀寫效率稍差，占用空間大
    • myisam
      • 表結構存在.frm 檔案中
      • .myd 存盤資料, .myi 存盤索引，采用非聚集索引方式
      • 快速，占空間小，不支持事務和并發
    • mysql索引底層資料結構采用B+樹
      • hash很快，但每次IO只能取一個數
      • AVL和紅黑樹，在大量資料的情況下，IO操作還是太多
      • B樹每個節點記憶體儲的是資料，因此每個節點存盤的分支太少
      • B+節點存盤的是索引+指標(參考指向下一個節點)，可以存盤大量索引，同時最終資料存盤在葉子節點，并且有參考橫向鏈接，可以在2-3次的IO操作內完成千萬級別的表操作，
      • 建議索引是是自增長數字，這樣適合范圍查找
    • mysql回表
      • 主鍵索引——InnoDB 是聚集索引方式，因此資料和索引都存盤在同一個檔案里，首先 InnoDB 會根據主鍵 ID 作為 KEY 建立索引 B+樹，B+樹的葉子節點存盤的是主鍵 ID 對應的資料
      • 非主鍵索引——葉子節點存盤的資料的是主鍵 KEY，拿到主鍵 KEY 后，InnoDB 才會去主鍵索引樹里根據剛在 非主鍵索引樹找到的主鍵 KEY 查找到對應的資料，

  • 分表磁區

    • 磁區
      • 概念：是把一個資料表的檔案和索引分散存盤在不同的物理檔案中
      • 磁區型別
        • RANGE磁區：基于屬于一個給定連續區間的列值，把多行分配給磁區
        • LIST磁區：類似于按RANGE磁區，區別在于LIST磁區是基于列值匹配一個離散值集合中的某個值來進行選擇
        • HASH磁區：基于用戶定義的運算式的回傳值來進行選擇的磁區，該運算式使用將要插入到表中的這些行的列值進行計算，這個函式可以包含MySQL 中有效的、產生非負整數值的任何運算式，
        • KEY磁區：類似于按HASH磁區，區別在于KEY磁區只支持計算一列或多列，且MySQL服務器提供其自身的哈希函式，必須有一列或多列包含整數值，
    • 原理：mysql通過磁區把資料保存到不同的檔案里，同時索引也是磁區的，相對于未磁區的表來說，磁區后單獨的資料庫檔案索引檔案的大小都明顯降低，效率則明顯的提示了，
    • 磁區的限制
      • 主鍵或者唯一索引必須包含磁區欄位，如primary key (id,username),不過innoDB的大組建性能不好
      • 很多時候，使用磁區就不要在使用主鍵了，否則可能影響性能
      • 只能通過int型別的欄位或者回傳int型別的運算式來磁區，通常使用year或者to_days等函式（mysql 5.6 對限制開始放開了）
      • 每個表最多1024個磁區，而且多磁區會大量消耗記憶體
      • 磁區的表不支持外鍵，相關的邏輯約束需要使用程式來實作
      • 磁區后，可能會造成索引失效，需要驗證磁區可行性
    • 分表
      • 概念：分表和磁區類似，區別是，磁區是把一個邏輯表檔案分成幾個物理檔案后進行存盤，而分表則是把原先的一個表分成幾個表，進行分表查詢時可以通過union或者視圖，
      • 分表規則
        • 水平分割
          • 水平分分割最為常用，水平分割通常是指切分到另外一個資料庫或表中，
      • 垂直分割
        • 把常用、不常用的欄位分開放
        • 把大欄位獨立存放在一個表中

思維導圖：https://www.processon.com/view/link/607a41256376891132d9c9ec

密碼：4URW