SQL優化隨記（一）建表

SQL優化隨記（一）建表

關鍵字：多表OR單表，索引，分區分表

（1）多表OR單表（寫下個人經驗）

• 多表的優點（相對單表）

1. 規範，方便管理閱讀；
2. 可優化減少表記錄數量；
3. 可減輕對應表的查詢IO消耗。

• 多表的缺點（相對單表）

1. 查詢時，需經常使用統計函數，資源消耗較大；
2. 表設計時，要求較高（需要考慮欄位關聯標準和資料庫框架的實現）。

闡述（啰嗦）：一切從實際需求出發，綜合考慮。例如：經常需要查詢統計的表可以使用單表的模式，不考慮範式啥的，使用率較高的合計數據使用欄位（金額，數量等）裝入。目前使用單表的例子比較多，多用於mysql。

（2）索引

• 唯一索引：唯一欄位，可有效提高查詢效率
• 普通索引：根據實際情況，經常使用的查詢條件可添加（估計提高30%）
• 可以有效降低查詢耗費， 減少CPU消耗（執行計劃可以體現，彩筆者曾經見過一個查詢直接讓CPU達到99%）

• 註意事項：
  1.索引欄位不能為空，否則失效
  2.索引欄位只要是有範圍數據，一般情況下都可以走索引（所以in是走索引的）
  3.不盲目建立索引，影響DDL操作的效率
  4.索引可以優化排序，貼個簡單的demo

  1.建立表和數據，執行索引：
  create table test_01 (
     xx number(6),
     oo number(6)
  
  2.建立索引
  create index idx_01 on test_01(xx asc,oo asc);
  
  3.準備數據
  begin 
    for i in 1..100 
          loop
            insert into test_01(xx,oo)
            values(i,101-i);
          end loop;
          commit;
      end;
  
  4.執行查詢
  select xx,oo 
  from test_01 
  where xx between 1 and 50 and oo between 50 and 99
  order by xx asc,oo asc;

  5.查看執行計劃
  

（3）分表分區

• 分表，彩筆者認為更多的還是在於制定策略
  舉個慄子：
  把一個業務表分成5個，假設寫入的業務數據與用戶數據相關聯，以此為基礎：
  策略由後臺代碼執行，簡單的將用戶ID（主鍵int）對5求餘，餘數的值既代表記錄應該寫入哪張表（餘1寫入1表，以此類推）

• 分區，oracle和mysql都支持分區的建立，彩筆者雖然以前使用oracle，但是目前使用的是mysql，所以這裡以mysql為例。
  再舉個慄子：
  分區儘量在建立表的時候就開始制定策略建立，否則後期修改建立分區會鎖表，影響線上操作
  創建一個表，以年份為主分區，以月份為子分區，年份寫死為2016~2031（相信一個項目可以跑十多年，應該也可以下線了）

   CREATE TABLE `test02` (
    `id` int(10) NOT NULL AUTO_INCREMENT,
    `image_url` varchar(100) DEFAULT NULL ,
    `create_date` date NOT NULL ,
    `create_month` TINYINT DEFAULT 0 ,
      PRIMARY KEY(`id`,create_date,create_month)
      )ENGINE=InnoDB AUTO_INCREMENT=2  DEFAULT CHARSET=utf8
      PARTITION BY RANGE (year(create_date))
      SUBPARTITION BY HASH (create_month) 
      SUBPARTITIONS 12 (
          PARTITION p2016 VALUES LESS THAN (2017),
          PARTITION p2017 VALUES LESS THAN (2018),
          PARTITION p2018 VALUES LESS THAN (2019),
          PARTITION p2019 VALUES LESS THAN (2020),
          PARTITION p2020 VALUES LESS THAN (2021),
          PARTITION p2021 VALUES LESS THAN (2022),
          PARTITION p2022 VALUES LESS THAN (2023),
          PARTITION p2023 VALUES LESS THAN (2024),
          PARTITION p2024 VALUES LESS THAN (2025),
          PARTITION p2025 VALUES LESS THAN (2026),
          PARTITION p2026 VALUES LESS THAN (2027),
          PARTITION p2027 VALUES LESS THAN (2028),
          PARTITION p2028 VALUES LESS THAN (2029),
          PARTITION p2029 VALUES LESS THAN (2030),
          PARTITION p2030 VALUES LESS THAN (2031),
          PARTITION pmax VALUES LESS THAN MAXVALUE
      );

  備註：將id,create_date,create_month同時作為主鍵的原因是：不這麼做會mysql會報錯：[Err] 1503 - A PRIMARY KEY must include all columns in the table's partitioning function；具體可以參考mysql提供的官方文檔
  查看（分區）執行計劃（1）

   EXPLAIN PARTITIONS SELECT * from test02;

  如表partitons所示：查詢了所有分區
  

  查看（分區）執行計劃（2）

  添加查詢條件，包括年份和月份
  EXPLAIN PARTITIONS  
      SELECT * from test02 
      where CREATE_date >  '2016-01-01' 
      and CREATE_date < '2016-03-01' 
      and create_month in (1,2);

  如表partitons所示：查詢了兩個分區