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所示：查询了两个分区**
![](http://images2015.cnblogs.com/blog/1107314/201702/1107314-20170216123530238-1655898087.jpg)