数据查询优化-表优化

表结构优化

单表优化

字段

保存数据长度不确定的情况下使用VARCHAR

Varchar往往用来保存可变长度的字符串。我们只是给其固定了一个最大值，然后系统会根据实际存储的数据量来分配合适的存储空间。
char数据类型与VARCHAR数据类型不同，其采用的是固定长度的存储方式。当数据保存时，即使其没有达到最大的长度，系统也会为其分配这么多的存储空间。

需要注意的是，varchar虽然是可变长度，但也不能随意设置。虽然使用varchar可以保证根据数据实际使用分配磁盘空间。但是在被加载到内存中，内存使用的是字符类型中定义的长度。而不是实际使用长度。这样当需要大量加载这些字段的时候（排序或缓存）。就会产生较大的内存消耗。

在满足业务的情况下尽量使用tinyint、smallint、medium_int作为整数类型而非int，如果非负则加上UNSIGNED

使用tinyint、smallint、medium_int替换int主要是他们占用了更小的空间。

类型	占用空间描述
int	范围是-2^31 (-2,147,483,648) 到 2^31 - 1 (2,147,483,647) 的整型数据，占用4个字节
mediumint	中等大小整数，有符号的范围是-8388608到8388607，占用3个字节
smallint	有符号的范围是-2^15 (-32,768) 到 2^15 - 1 (32,767) 的整型数据，占用2个字节
tinyint	有符号的范围是-128 - 127的整型数据，占用1个字节

使用枚举或整数代替字符串类型

数字类型相对字符串类型要简单的多，当使用索引的时候，使用数据类型比字符串类型效率要高。

而对于枚举，在建立这个字段时，mysql内部会建立一张hash结构的map表，来保存枚举索引和对应值。此时对此字段进行保存，存储在里面的不是这个字符，而是对应的索引，除非enum的个数超过了一定数量，否则数据所占的存储空间也总是1字节。

尽量使用timestamp而非datetime

两者主要有下面的区别

占用空间的区别

timestamp 占用4字节，而datetime占用8字节。而两者

时间范围的区别

timestamp可以描述的时间范围是’1970-01-01 00:00:01.000000’ 至 ‘2038-01-19 03:14:07.999999’
datetime可以描述的时间范围是’1000-01-01 00:00:00.000000’ to ‘9999-12-31 23:59:59.999999’

时区问题

timestamp以utc的格式储存，它会自动检索当前时区并进行转换。如果储存时的时区和检索时的时区不一样，那么拿出来的数据也不一样。
datetime不会进行时区的检索，对于datetime来说，存什么拿到的就是什么。

默认值

如果存进去的是NULL，timestamp会自动储存当前时间，而datetime会储存NULL。

单表不要有太多字段

MySql物理存储的结构，由段-区-页-行组成。每个区是1M大小，由连续的64个16k的页组成，每个页又由N行组成。每个页16k，在MySql内存加载过程中，数据加载的最小单位是页。所以每个页可以保存的行越多，加载的页就越少。实际使用时根据业务场景预估单表每个字段实际长度和和字段数量，进行字段设计。
当然根据大家网上的分享，一般维持在20个以下是比较稳妥的。

避免使用NULL字段，很难查询优化且占用额外索引空间

假如字段中存在null的内容时候，在有些业务中我们不得不使用not null的判断。而使用not null会导致索引的失效。并且，对于MySQL，null不能使用=,<,>这样的运算符，对null做算术运算的结果都是null，count时不会包括null行等。并且null比空字符串需要更多存储空间。所以一般建表的时候最好为字段设置一个默认值。

用整型来存IP

使用整型来保存IP比使用varchar节省更多的存储空间。并且MySql提供了函数，进行数据转换。inet_ntoa()函数：将整数转成IP地址；inet_aton()函数：将IP地址转成整数；可以通过函数式放在右边保证索引可用。

EXPLAIN SELECT inet_ntoa(ip)
FROM  ip_test
WHERE ip = inet_aton('127.0.0.1');

+----+-------------+---------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | ip_test | NULL       | ref  | index         | index | 4       | const |    1 |   100.00 | Using index |
+----+-------------+---------+------------+------+---------------+-------+---------+-------+------+----------+-------------+

索引和主外键

外键

对于外键的设置，小型系统和对性能要求并不高的系统中，外键是必须的。但是外键也会明显的影响数据访问的性能。所以在一些对性能要求比较高的系统中，表结构中可以不需要外键，相关约束，由程序来保证。

查询为主

为了提供数据的访问速度，对于经常作为where中的字段，需要设置字段的索引。

写数据为主

对于频繁被修改的字段，最好不要设置索引，因为每次修改数据库都会修改其索引，降低了写数据的效率。

引擎优化

对于日常开发，我们接触的最多的存储引擎是：InnoDB和MyIsam。

• MyIsam引擎是MySQL 5.1及之前版本的默认引擎
• InnoDB在MySQL 5.5后成为默认索引

他们区别是

功能	MyIsam	InnoDB
行锁	不支持行锁，读取时对需要读到的所有表加锁，写入时则对表加排它锁	支持行锁，采用MVCC来支持高并发
事务	不支持	支持
外键	不支持	支持
安全恢复	不支持崩溃后的安全恢复	支持
索引	支持延迟更新索引，极大提升写入性能	不支持全文索引
数索引	支持	支持
数据缓存	不支持	支持
其他	支持压缩表，减少磁盘空间占用

一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求。所以根据上面的对比，MyIsam适合SELECT密集型的表，而InnoDB适合INSERT和UPDATE密集型的表。

数据拆分

当数据库中数据渐渐增多的时候，除了常规的优化以外，我们还可以对数据进行分区分库分表。

分区

分区是根据规则，把一个表拆分成更小的部分，但是对于访问数据库应用而言，逻辑上只有一个表，但实际上这个表可能有多个物理分区组成。分区对应用来说是完全透明的，不影响应用的业务逻辑。分区引入了分区键，分区键用于根据某个规则让数据根据规则分布在不同分区。MySQL分区既可以对数据进行分区，也可以对索引进行分区。

注意：无论哪种分区，都限制分区键必须是唯一键/主键，当然除非你的表上没有任何主键/唯一键。

分区类型

分区类型	描述
range分区	基于一个连续的区间范围进行数据分配
list分区	类似range，区别在于list分区是居于枚举出的值列表分区
hash分区	根据自定义的表达式的返回值来进行分区
key分区	根据MySql提供的hash函数来进行分区

新建分区

• 新建range分区

CREATE TABLE `user_info_range` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `username` varchar(255) DEFAULT NULL,
   `email` varchar(20) DEFAULT NULL,
   `age` tinyint(4) NOT NULL,
   `type` int(11) DEFAULT NULL,
   `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
   PRIMARY KEY (`id`,`age`)
 ) 
 PARTITION BY RANGE COLUMNS(age) (
    PARTITION a VALUES LESS THAN (10),
    PARTITION b VALUES LESS THAN (30),
    PARTITION c VALUES LESS THAN (40),
    PARTITION d VALUES LESS THAN (50),
    PARTITION e VALUES LESS THAN MAXVALUE
);

• 新建list分区

CREATE TABLE `user_info_list` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `username` varchar(255) DEFAULT NULL,
   `email` varchar(20) DEFAULT NULL,
   `age` tinyint(4) DEFAULT NULL,
   `type` int(11) NOT NULL,
   `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
   PRIMARY KEY (`id`,`type`)
 ) 
 PARTITION BY LIST COLUMNS(type) (
    PARTITION a VALUES IN (1),
    PARTITION b VALUES IN (2),
    PARTITION c VALUES IN (3),
    PARTITION d VALUES IN (4),
    PARTITION e VALUES IN (5,6,7,8)
);

• 新建hash分区

CREATE TABLE `user_info_hash` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `username` varchar(255) DEFAULT NULL,
   `email` varchar(20) DEFAULT NULL,
   `age` tinyint(4) NOT NULL,
   `type` int(11) DEFAULT NULL,
   `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
   PRIMARY KEY (`id`,`age`)
 ) 
 PARTITION BY HASH (age) PARTITIONS 4 ;

• 新建key分区

CREATE TABLE `user_info_key` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `username` varchar(255) DEFAULT NULL,
   `email` varchar(20) DEFAULT NULL,
   `age` tinyint(4) NOT NULL,
   `type` int(11) DEFAULT NULL,
   `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
   PRIMARY KEY (`id`,`age`)
 ) 
 PARTITION BY KEY (age) PARTITIONS 4 ;

后续新增分区

alter TABLE `user_info_list` add PARTITION(
 PARTITION f VALUES IN (9) ENGINE = InnoDB
);

删除分区

alter table `user_info_list`  drop PARTITION f;

查看分区

select partition_name part, partition_expression expr, partition_description descr, table_rows
from information_schema.partitions 
where table_name = 'user_info_list';

返回内容

+------+--------+---------+------------+
| part | expr   | descr   | TABLE_ROWS |
+------+--------+---------+------------+
| a    | `type` | 1       |          0 |
| b    | `type` | 2       |          0 |
| c    | `type` | 3       |          0 |
| d    | `type` | 4       |          0 |
| e    | `type` | 5,6,7,8 |          0 |
+------+--------+---------+------------+

分区文件

新建完的分区可以在数据文件中看到生成相应的文件

分表

如果表中数据大到使用分区还是无法满足需要的时候，这个时候可以考虑使用分表。
一般来说是否进行分表，我们需要考虑几个方面：

1. 数据的使用频率，一张表中数据使用的频率可能不同，有的字段会经常被访问和操作。
2. 数据的独立性，数据是否被拆分后每一部分的数据查询的时候是否有关联。比如根据地区划分、根据日期进行划分的记录。

拆分方式

目前分表主要两种：垂直拆分和水平拆分。

垂直拆分

垂直拆分简单说就是讲一张表的字段进行拆分，把一张表的列拆分为多张表中。将大字段和访问频率低的字段进行拆分。这样减少每一行的数据，提高每次检索的行数，减少数据库查询次数。

当然垂直拆分也有它的弊端：拆分后因为要做数据关联，主键会出现冗余，需要管理冗余列，并会引起join操作。

水平拆分

水平拆分主要是对数据的拆分。我们根据数据中的一些规则将数据拆分至多张表中，这样可以避免单表存在大量数据。但是水平拆分会给应用增加复杂度，通常在查询是需要多个表名，查询所有数据需要union操作。
水平分割最重要的是找到分割的标准，不同的表应根据业务找出不同的标准，比如用户可以根据手机号进行拆分，也可以根据地址进行拆分。

分库

分库的思想就是要把一个数据库切分成多个部分放到不同的数据库(server)上，从而缓解单一数据库的性能问题。
其类似分表，把业务紧密的数据表切分放在一个数据库中，或者单表数据异常多，把表的数据按照某种规则切分到多个数据库中。
亦或者水平和垂直拆分一起使用。

读写分离

将数据库的读取和修改放置在不同的位置，也就是读写分离。使用读写分离可以显著提升数据库的并发负载能力。
一般来说都是通过主从复制的方式来同步数据，再通过读写分离来实现并发负载的提升。