海量数据的数据库表设计细则

川长思鸟来 2022-09-10 12:20 20阅读 0赞

## 1. 表分类设计和命名规则 ##

一般海量数据就需要对表进行分类，主要分为如下：

*  **主数据，其数据量基本稳定，不随时间而线性增长**。比如，分公司，产品，经销商。 这种数据库表，我们一般以 **tm\_** 作为表名的前缀, 意思是 table of master data。
 *  **系统级数据，其数据量基本稳定，不随时间而线性增长**。比如，用户权限控制，配置参数。 这种数据库表，我们一般以 **ts\_** 作为表名的前缀, 意思是 table of system。
 *  **日志数据表，随时间而线性增长**，但会安排定时任务定期删除旧数据，保持总体数据量稳定。 这种数据库表，我们一般以 **tl\_** 作为表名的前缀, 意思是 table of log。
 *  **接口数据表，随时间而线性增长**，但会安排定时任务定期删除旧数据，保持总体数据量稳定。 这种数据库表，我们一般以 **ti\_** 作为表名的前缀, 意思是 table of interface。
 *  **业务交易数据，随时间而线性增长**，用户平常关注最近若干天的数据，少数情况下会查阅很久以前的数据。 这种数据库表，我们一般以 **tt\_** 作为表名的前缀, 意思是 table of transaction data。
 *  **关系数据**，可能是以上 1,2,5 的关系表，我们分别以 **tmr\_, tsr\_, ttr\_** 作为表名的前缀。

通常，数据量大的，就是上述的业务交易数据。

## 2. 表主键设计 ##

在主键设计上有自增和雪花ID或者UUID等几种方案，建议如下：

*  业务相关的表或者主表尽量采用雪花ID，方便后续进行分表操作。
 *  关联表可以采用自增ID，因为即使分表，它也是跟随主表进行分落。

## 3. 字段类型设计 ##

表字段类型和长度要进行严格的选择，因为随着后续数据量越来越大，对磁盘空间要求越来越大，比如：

*  可以使用的几种整数类型：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT分别使用8，16，24，32，64位存储空间。尽量使用满足需求的最小数据类型。
 *  尽量使用枚举或整数代替字符串类型。
 *  varchar根据实际内容长度保存数据。
 *  尽量使用TIMESTAMP而非DATETIME，TIMESTAMP占用4个字节，DATETIME占用8个字节。
 *  单表不要有太多字段，建议在20以内，对常用字段和非常用字段进行分离，形成业务表和明细表。
 *  字段尽量避免NULL，NULL不利于索引,要用特殊的字节来标注，能非空就非空，或者设置默认值。

总结：整型>date,time>char,varchar>blob(存储从二进制文件)。

## 4. 表索引设计 ##

### 4.1 普通索引 ###

普通索引(由**关键字KEY或INDEX定义**的索引)的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件(WHERE column = …)或排序条件(ORDER BY column)中的数据列创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列(如一个整数类型的数据列)来创建索引，它不要求数据必须唯一。

–直接创建索引(length表示使用名称前1ength个字符)  
    CREATE INDEX index_name ON table_name(column_name(length))  
    
    –修改表结构的方式添加索引  
    ALTER TABLE table_name ADD INDEX index_name ON (column_name)  
    
    –创建表的时候同时创建索引  
    CREATE TABLE `table_name` (  
    `id` int(11) NOT NULL AUTO_INCREMENT ,  
    `title` char(255) NOT NULL ,  
    PRIMARY KEY (`id`),  
    INDEX index_name (title)  
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;  
    
    –删除索引  
    DROP INDEX index_name ON table_name;  
      
    建立复合索引 。  
    CREATE INDEX mytable_categoryid_userid ON mytable (category_id,user_id);  
      注意命名时的习惯了吗？使用"表名_字段1名_字段2名"的方式

### 4.2 唯一索引 ###

与普通索引类似，不同的就是：**索引列的值必须唯一，但允许有空值（注意和主键不同）**。如果是组合索引，则列值的组合必须唯一，创建方法和普通索引类似。

如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。这么做的好处：**一是简化了MySQL对这个索引的管理工作，这个索引也因此而变得更有效率；二是MySQL会在有新记录插入数据表时，自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了**；如果是，MySQL将拒绝插入那条新记录。也就是说，唯一索引可以保证数据记录的唯一性。事实上，在许多场合，人们创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。

–创建唯一索引  
    CREATE UNIQUE INDEX index_name ON table_name(column_name)  
    
    –修改表结构  
    ALTER TABLE table_name ADD UNIQUE index_name ON (column_name)  
    
    –创建表的时候直接指定  
    CREATE TABLE `table_name` (  
    `id` int(11) NOT NULL AUTO_INCREMENT ,  
    `title` char(255) NOT NULL ,  
    PRIMARY KEY (`id`),  
    UNIQUE index_name (title)  
    );

### 4.3 主键索引 ###

主索引与唯一索引的唯一区别是：前者在定义时使用的关键字是**PRIMARY**而不是UNIQUE。

### 4.4 外键索引 ###

如果为某个外键字段定义了一个外键约束条件，MySQL就会定义一个内部索引来帮助自己以最有效率的方式去管理和使用外键约束条件。

创建表指定
    mysql> CREATE TABLE tb_emp6
        -> (
        -> id INT(11) PRIMARY KEY,
        -> name VARCHAR(25),
        -> deptId INT(11),
        -> salary FLOAT,
        -> CONSTRAINT fk_emp_dept1
        -> FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
        -> );
    Query OK, 0 rows affected (0.37 sec)
    
    修改表结构
    mysql> ALTER TABLE tb_emp2
        -> ADD CONSTRAINT fk_tb_dept1
        -> FOREIGN KEY(deptId)
        -> REFERENCES tb_dept1(id);
    Query OK, 0 rows affected (1.38 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    删除外键
    mysql> ALTER TABLE tb_emp2
        -> DROP FOREIGN KEY fk_tb_dept1;
    Query OK, 0 rows affected (0.19 sec)
    Records: 0  Duplicates: 0  Warnings: 0

### 4.5 全文索引（FULLTEXT） ###

**fulltext索引仅可用于 MyISAM 表**；他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建，或是随后使用ALTER TABLE 或CREATE INDEX被添加。  
对于较大的数据集，将你的资料输入一个没有FULLTEXT索引的表中，然后创建索引，其速度比把资料输入现有FULLTEXT索引的速度更为快。不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。  
文本字段上的普通索引只能加快对出现在字段内容最前面的字符串(也就是字段内容开头的字符)进行检索操作。如果字段里存放的是由几个、甚至是多个单词构成的较大段文字，普通索引就没什么作用了。这种检索往往以LIKE %word%的形式出现，这对MySQL来说很复杂，如果需要处理的数据量很大，响应时间就会很长。

不过暂时不建议在mysql中使用全文索引，因为目前Mysql的全文索引效率还是比较低。

–创建表的适合添加全文索引  
    CREATE TABLE `table_name` (  
    `id` int(11) NOT NULL AUTO_INCREMENT ,  
    `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,  
    PRIMARY KEY (`id`),  
    FULLTEXT (content)  
    );  
    –修改表结构添加全文索引  
    ALTER TABLE table_name ADD FULLTEXT index_name(column_name)  
    –直接创建索引  
    CREATE FULLTEXT INDEX index_name ON table_name (column_name)

## 5. 常见的数据库引擎对比和选择 ##

目前广泛使用的是MyISAM和InnoDB两种引擎：

*  MyISAM
    
    MyISAM引擎是MySQL 5.1及之前版本的默认引擎，它的特点是：
    
    1.  不支持行锁，读取时对需要读到的所有表加锁，写入时则对表加排它锁
    2.  不支持事务
    3.  不支持外键
    4.  不支持崩溃后的安全恢复
    5.  在表有读取查询的同时，支持往表中插入新纪录
    6.  支持BLOB和TEXT的前500个字符索引，支持全文索引
    7.  支持延迟更新索引，极大提升写入性能
    8.  对于不会进行修改的表，支持压缩表，极大减少磁盘空间占用
 *  InnoDB
    
    InnoDB在MySQL 5.5后成为默认索引，它的特点是：
    
    1.  支持行锁，采用MVCC来支持高并发
    2.  支持事务
    3.  支持外键
    4.  支持崩溃后的安全恢复
    5.  不支持全文索引

**总体来讲，MyISAM适合SELECT密集型的表，而InnoDB适合INSERT和UPDATE密集型的表。**  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBALS0tLS0tLeaxn-a5li0tLS0tLS0_size_20_color_FFFFFF_t_70_g_se_x_16]

## 6. 常见sql优化 ##

*  使用limit对查询结果的记录进行限定
 *  避免select \*，将需要查找的字段列出来
 *  使用连接（join）来代替子查询
 *  拆分大的delete或insert语句
 *  可通过开启慢查询日志来找出较慢的SQL
 *  不做列运算：SELECT id WHERE age + 1 =10，任何对列的操作都将导致表扫描，它包括数据库教程函数、计算表达式等等，查询时要尽可能将操作移至等号右边
 *  sql语句尽可能简单：一条sql只能在一个cpu运算；大语句拆小语句，减少锁时间；一条大sql可以堵死整个库
 *  OR改写成IN：OR的效率是n级别，IN的效率是log(n)级别，in的个数建议控制在200以内
 *  不用函数和触发器，在应用程序实现
 *  避免%xxx式查询
 *  少用JOIN
 *  使用同类型进行比较，比如用’123’和’123’比，123和123比
 *  尽量避免在WHERE子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描
 *  对于连续数值，使用BETWEEN不用IN：SELECT id FROM t WHERE num BETWEEN 1 AND 5
 *  列表数据不要拿全表，要使用LIMIT来分页，每页数量也不要太大

## 7. 分页查询优化 ##

*  直接使用数据库提供的 SQL 语句 limit 来分页。
    
        SELECT * FROM USER LIMIT 2,4; 
        表示从第三行开始查询四行数据。
    
    适用于数据量较少的情况。缺点是，很明显不加条件的话进行的是全表扫描，速度会很慢。Limit 限制的是从结果集的 M 位置处取出 N 条输出，其余抛弃。
 *  建立主键或唯一索引，利用索引(假设每页 10 条)。
    
        SELECT * FROM 表名称 WHERE id_pk > (pageNum*10) LIMIT M
    
    适用于数据量较多的情况。这样方法，会使用索引扫描，速度会很快。如果你的数据查询出来并不是按照 pk\_id 排序的，那么就有可能漏掉数据的情况。所以使用的时候要注意。
 *  基于索引再排序。
    
        SELECT * FROM 表名称 WHERE id_pk > (pageNum*10) ORDER BY id_pk ASC LIMIT M
    
    这种方法适用于数据量较多的情况。最好 ORDER BY 后的列对象是主键或唯一索引，使得 ORDER BY 操作能利用索引。  
    这种方法的特点是索引扫描，速度会很快。  
    ![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBALS0tLS0tLeaxn-a5li0tLS0tLS0_size_18_color_FFFFFF_t_70_g_se_x_16]
 *  基于索引使用 prepare（第一个问号表示 pageNum，第二个 ？表示每页元组数）。  
    这个 SQL 很厉害，适合海量数据的场景。这个查询会使用索引扫描，速度会很快。prepare 语句又比一般的查询语句快一点。
    
        PREPARE stmt_name FROM SELECT * FROM 表名称 WHERE id_pk > (？* ？) ORDER BY id_pk ASC LIMIT M
 *  利用 MySQL 支持 ORDER 操作可以利用索引快速定位部分元组，避免全表扫描。
    
    比如: 读第1000到1019行元组(pk是主键/唯一键).
    
        SELECT * FROM your_table WHERE pk>=1000 ORDER BY pk ASC LIMIT 0,20

**所有的分页优化本质其实是基于索引来进行查询，利用索引来进行优化。**

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBALS0tLS0tLeaxn-a5li0tLS0tLS0_size_20_color_FFFFFF_t_70_g_se_x_16]: /images/20220829/b8bc5233aec6406281d4d404fc448fe4.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBALS0tLS0tLeaxn-a5li0tLS0tLS0_size_18_color_FFFFFF_t_70_g_se_x_16]: /images/20220829/8230426407e64cadb497167f90236b95.png