数据库查询优化过程（索引、分区、分表、分库）_分区 索引 拆表

一个大表，大概控制在25个字段左右差不多。

账本表：
 1.按地市分区 9个分区
 2. 复合索引（账户ID,账本类型）
    唯一索引（地市,账本ID）

--------------------------

查询优化过程

1.索引。数据量增多的时候，查询速度开始变慢，这个时候一般会考虑到用索引

复合索引（A,B）相当于：

索引（A）

索引（A,B）

最左边的优先约束，因此最左边的定义约束数据量多的字段。

2.分区。加了索引，查询速度提升了一点，但是由于数据量还是大，查询还是不够快，考虑分区，把数据打到不同的物理区域上。

 分区：同一张表数据打在不同的物理区域。
   适合：数据量大，但访问量不大的时候
3.分表。加了分区之后，数据量还是大，这个时候考虑分表。 

分表：拆成多张表
   适合：数据量大，访问量大的时候，考虑分区分表。
4. 分库。单台的数据库空间不够了，或者分表后I/O瓶颈，性能还是上不去，考虑分库。
   适合：单台DB空间不够的时候；访问量增加，单台无法支撑。
   解决：单台DB的I/O瓶颈

总结：查询慢，优先考虑索引-->分区-->分表分库

索引

普通索引、唯一索引、复合索引。

索引的数据结构

1.为什么不用数组

数组查询效率是很快，但是插入很慢，从中间插入需要整体往后挪动。

2.为什么不用链表

链表查询慢，需要从头遍历，肯定不适合索引。

3.为什么不用二叉树（红黑树、AVL树）

二叉树容易导致单边数，就变成链表了，也不适合索引。

二叉树只有两个节点，树的高度必然会很高，所以二叉树都不适合。

4.为什么不用b树

b树是多叉树，但是他的叶子节点和非叶子节点都会有索引值和数据。

b+树非叶子节点放索引值，叶子节点放数据，这样节点可以放更多的索引值，树的高度更小。并且呢，b+树有一个向右的指针更加方便检索。

 

分区

1.分区就是数据打到不同的物理区域上。

2.注意分区字段是不能更改的

什么时候使用分区表：
1、表的大小超过2GB。
2、表中包含历史数据，新的数据被增加都新的分区中。

create table pay_record(
  pay_id VARCHAR2(15),
  out_user_id number(15),
  in_user_id number(15),
  amount number(10),
  status number(1),
  pay_time date,
  create_time date
)
partition by range(pay_time) 
(
partition pay_time_20210411 values less than (to_date('20210411','yyyyMMdd')),
partition pay_time_20210412 values less than (to_date('20210412','yyyyMMdd')),
partition pay_time_20210413 values less than (to_date('20210413','yyyyMMdd')),
partition pay_time_20210414 values less than (to_date('20210414','yyyyMMdd')),
partition pay_time_20210415 values less than (to_date('20210415','yyyyMMdd'))
);
---两个字段作为分区
drop table pay_record;
create table pay_record(
  pay_id VARCHAR2(15),
  home_city number(3),
  out_user_id number(15),
  in_user_id number(15),
  amount number(10),
  status number(1),
  pay_time date,
  create_time date
)
partition by range(home_city,pay_time) 
(
partition pay_time_20210411 values less than (591,to_date('20210411','yyyyMMdd')),
partition pay_time_20210412 values less than (591,to_date('20210412','yyyyMMdd')),
partition pay_time_20210413 values less than (591,to_date('20210413','yyyyMMdd')),
partition pay_time_20210414 values less than (591,to_date('20210414','yyyyMMdd')),
partition pay_time_20210415 values less than (591,to_date('20210415','yyyyMMdd')),
partition pay_time_59220210411 values less than (592,to_date('20210411','yyyyMMdd')),
partition pay_time_59220210412 values less than (592,to_date('20210412','yyyyMMdd')),
partition pay_time_59220210413 values less than (592,to_date('20210413','yyyyMMdd')),
partition pay_time_59220210414 values less than (592,to_date('20210414','yyyyMMdd')),
partition pay_time_59220210415 values less than (592,to_date('20210415','yyyyMMdd'))
);

分表

拆分原则：3年内，oracle单表达2000w，mysql单表达500万

拆表种类：水平分表、垂直分表。

水平分表：就是创建多个表结构相同的表。

垂直分表：就是一个表字段太多了，适当的拆分成多个结构不同的表。

分库

1.可以根据业务拆分到不同的库里。

2.需要考虑查询的时候不能跨库。

如何设计大数据量表

千万级：优先考虑分区、索引

亿级：考虑分库分表

1.首先要知道这个表是做什么操作：频繁读还是频繁写。

（1）不频繁读，比如定期去扫描这张表，这种没必要读写分离。

（2）频繁读写，读写分离环节数据库压力，读写分离需要考虑同步。

2.优先考虑分区跟索引

  （1）有时间性的，就通过时间字段增加分区。

  （2）分区字段是不能更改的，这一点要考虑进去，避免将来要修改的时候发现无法修改。

  （3）复合索引要把能过滤大量数据的字段放在前面。