引言

最近在学习数据库相关内容的知识，在GitHub上找到一个百万数据量的数据库。通常，由于数据量少的原因，查询全表数据也是十分的快。但是在引入这个数据量极大的数据库后，这一切似乎都发生了些许了改变。原本的0.00sec就可以完成的全表查询，现在可能需要1.3S左右才可能完成查询。对于数据量很是庞大时，优化也就成了一门必修课了。
接下来，我将从数据库的表创建、插入数据、删除数据、检索数据以及更新数据这四个方面。来简单聊聊关于它们的使用时需要注意的事项与简单的优化。

创表规范

在数据库中，表的创建才是一切的开端。那么，一个经过精良设计的表会在后续的数据库的使用中，将会是对数据库性能与安全的一个极大的保证。我想参照如下的几条建议，可能会更加方便快速的设计出一个性能与安全不错的表。

选用正确的数据类型
大部分情况下，我们需要根据这个数据的本身特性来决定其类型。若是使用了不相关的数据类型，则可能导致了更多的内存空间的浪费，甚至是错误的发生。
举例来说，我们都曾可能有过这样的一个经历: 使用char类型或varchar来存储一个日期时间。显然这也并不是错误，但这可能会导致错误的发生。即我们无法保证输入的数据一定是符合日期时间类型的，而这可能会导致数据库中存储了一些无效甚至是错误的数据。对于这种时间类型的特性，我们可以使用Mysql中提供的DATATIME来定义它。
使用 CHAR(1) 替代 VARCHAR(1)
若是存储单个字符，推荐使用CHAR(1),而非使用VARCHAR(1)。因为VARCHAR会占用额外的字节来存储信息。
其实这两个类型很相似，但是从存储大小与格式来看，它们则是完全不同的。CHAR最多可以存储255个字符，而VARCHAR最高可达65535个字符。对于CHAR类型来说，存入多少就是多少。相对而言，当字节数小于255时，会使用一个额外字节来存储这个字节长度;若是字节数超过255时，则会使用到两个字节来存储这个字符的长度。
使用CAHR类型存储合适大小的数据
举例来说:若是字符串长度小于1000，但却使用CHAR(1000)来替代VARCHAR(1000)类型，这就会导致过多的空间被闲置与浪费
时间格式类型的选用
通常来说，我们会使用DATETIME或是DATE类型将时间格式固定为:YYYY-MM-DD或是ISO标准的模式，用以适配我们的引擎。但若是使用其它格式的时间，在数据库中就难以对其进行排序操作。若是需要存储时间(精确到秒)，建议使用TIMESTAMP类型，因为TIMESTAMP使用4字节，而DATETIME使用8个字节
字段的定义
定义字段时，最好将其定义为NOT NULL。创表时确定字符集为utf8mb4,并指定所使用的引擎。

插入规范

在日常的工作中，我们常常会遇到批量将数据插入到数据库中的情况，这个时候我们就需要根据不同的情况选择不再的策略。Mysql中提供了两个方式，让我们可以向table中插入数据:insert、replace。接下来，我们就对这两种插入方式，分享一下我们认为所需要注意的规范。我们的之后将会操作如下的一个表:

mysql> CREATE TABLE demo (
    -> id int not null auto_increment comment '自增主键',
    -> name varchar(20) not null comment '姓名',
    -> gender varchar(30) not null comment '年级',
    -> addr varchar(40) not null comment '家庭住址',
    -> status int not null default '1' comment '0:未注册入学;1:已注册入学',
    -> primary key (id)
    -> ) engine=InnoDB default charset=utf8mb4;

插入一条数据
在最初学习数据库时，我们就知道insert可以实现数据的插入，如下操作所示:

mysql> insert into demo (
    -> id, name, gender, addr, status
    -> ) values (
    -> 1, 'Rabbet', '三年级二班','Jack Road', 1);
Query OK, 1 row affected (0.01 sec)

mysql> select * from demo;
+----+--------+-----------------+-----------+--------+
| id | name   | gender          | addr      | status |
+----+--------+-----------------+-----------+--------+
|  1 | Rabbet | 三年级二班      | Jack Road |      1 |
+----+--------+-----------------+-----------+--------+
1 row in set (0.00 sec)

对于数据量较少时，这样做确实是没有什么问题。但是，我们思考一下数据库操作方式。
通过连接器连接到数据库中，这样一个TCP连接就建立完成了。那么对应的每一条命令的，都会通过网络发送到Mysql Server解析运行。如果有成千上万的插入命令需要执行时，那么我们难道还需要进行这上千万次的传输操作？即使是存在连接池，这其中的传输的时间也是不可被忽略的。
显然，在庞大的数据量面前，insert单条插入就不能算是一个优选。

一次插入多条数据
回顾之前，单条插入所存在的缺陷:需要为其建立多条连接，然后传输N连接，由于连接池的存在，可以忽略连接的影响，但是庞大的传输量也是无法被忽视的。因此，我们可以从这个方面下手进行优化的考虑。
若是一个养猪人，它养了1000头猪。若是它准备将这些猪全部卖出,它可以有如下两种策略:

一个车装载一头猪，送到集市卖掉

一个车装载100头猪，送到集市去卖掉
比较这两种策略，我想大家都倾向于选择第二和方案。因为，这样的效率高且时间成本相较于第一种低得多。
应用这样的策略，我们也可以使用insert进行批量的插入数据:

mysql> insert into demo
    -> (id, name, gender, addr, status)
    -> values
    -> (2, 'b', '三年级二班', 'Jack Road', 1),
    -> (3, 'c', '三年级二班', 'Jack Road', 1),
    -> (4, 'd', '三年级二班', 'Jack Road', 1);

预处理,显然是一个更好的选择。预编译仅会解析一次查询，若多次执行这个语句，这个开销将不再。

小结
需要根据情况设置一次传输的数据量。毕竟数据量越大，在网络中的通信传输时间就越久，发生问题的可能性就越大。
除了网络的限制，机器的配置同样也会存在瓶颈。就算sql语句写的再不错，也不会有太高的效率。

删除数据

数据清空
如果需要清空一个表中所存储的所有数据，并保存表的结构。最好的办法就是使用truncate,而非使用delete。这是由于truncate不走事务，不会锁表，也不会产生大量日志写入日志文件中。具体的写法如下所示:
1
truncate table table_name
执行这条语句后，将会立即释放磁盘空间，并重置auto_increment的值。但是对于Delete删除来说，它是不释放磁盘空间的，后续insert的数据会覆盖到原先删除的数据上。
delete+limit巨能打
delete limit 语法如下所示:
1
2
3
4
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [[AS] tbl_alias]
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
这里需要注意的一个问题:当需要使用order by 排序时，必须使用order by + limit联用，否则order by将会被优化器所优化掉。
加了limit到底有什么好处呢？如下条SQL语句为例:
1
delete from demo where gender='三年级二班';

避免语句的错误，导致全表删除的风险。降低错删的代价，以更少的时间使用binlog日志进行恢复
避免长事务，delete执行MySQL将会涉及行加写锁与Gap锁，所有DML语句相关行会被锁住。若是删除量巨大，则会某些业务直接无法使用
delete 数据量大时，不加limit很容易将CPU打满，导致执行的效率越来越慢。

通常加锁都是索引的情况，若是gender这一行没有索引，则有可能引发锁表的情况的发生，即使gender='三年级二班'仅有一条数据。

检索数据

开启慢日志
可以通过开启慢日志，找到查询执行时间长的SQL语句。并对其进行优化,慢查询时间阈值可以在mysql进行设置。
不做列运算
举例来说:
1
select id where id+1=10;
任何对列的操作都将导致全表扫描，它包括数据库中所提供的函数、计算表达式等等
SQL语句简单化
一条SQL语句只能在一个CPU上运算。拆分简化SQL语句可以减少锁的时间，从而避免了一个大的SQL执行语句堵塞了数据库的情况。
避免使用select *查询
避免使用它，是因为它会返回一些无用字段。其低效率的原因大致如下所示:

不需要的列会增加传输时间与网络开销
对于无用的大字段，如:varchar、blob、text,会增加IO操作
失去MySQL优化器”覆盖索引”策略优化的可能

少用JOIN
首先JOIN本身的效率就是一个硬伤，一旦数据过大这个效率就难以保证了。
如果是JOIN的话，它是走嵌套查询的。小表驱动大表，且通过索引字段进行关联。如果表记录比较少的话，还是OK的。大的话业务逻辑中可以控制处理。

数据更新

执行UPDATE前，一定记得先写好WHERE条件，或者先准备好完整的SQL再执行，或者开启sql_safe_updates，或者执行前都先加上BEGIN（误操作了还能回滚事务）

总结

这里，我们仅是讲了一些笔者所认为的规范。当然，可能还会存在比这更加严苛的规范存在。不过，在遵循以上条件的情况下。我想这大概可以写出一个基本符合规范的SQL语句了。
其中，关于一些内容如:少用JOIN、避免使用select * 查询，并没有展开讨论。之后，我们在后续的文章中补上的。最后，祝大家中秋快乐！