第四章 读【schema与数据类型优化】有感

一、选择优化的数据类型

整数类型：

为整数类型指定宽度，如：int(11)，它不限制值的合法范围，只是规定了显示字符的个数。整数类型有UNSIGNED属性，如果不需要负数范围，最好使用该属性以提高存储数据上线。

字符串类型：

VARCHAR：存储可变字符串

CHAR：存储定长字符串，Mysql会自动删除末尾的空格

日期和时间类型：

DATATIME的时间范围从1001年到9999年，精度为秒，使用8位字节存储；

TIMEATAMP的时间范围从1970年到2038年，使用4位字节存储；

比较少用的类型：实数类型（浮点数）、位数据类型、选择标识符、特殊类型数据。尽量使用小而简单的合适数据类型。

二、范式和反范式

第一范式：同一张表中没有重复的列；

第二范式：每行记录拥有唯一标识；

第三范式：表与表字段没有冗余字段。

在现实开发中，范式和反范式和混合使用的。使用反范式可以避免关联查询，提高查询效率。

三、缓存表和汇总表

复杂的统计数据可以使用缓存表和汇总表，提高查询效率。

1、视图

2、计数器表

四、加快ALTER TABLE操作的速度

ALTER TABLE操作会锁表。Mysql大部分修改表结构操作是用新的结构创建一个空表，从旧表中查询出所有数据插入新表，然后删除旧表。这个操作需要花费很长时间，如果内存不足而表又很大，而且还有很多索引，则更加慢。

方法1：新建一张新表（不带索引），复制旧表数据插入新表，添加索引，删除旧表，将新表改名；

方法2：只修改.frm文件

五、中断执行时间长的sql

有时候执行脚本sql时，由于执行时间长，导致数据库所在服务器CPU飙升，更严重的导致线上服务查询缓慢影响用户使用，这时候需要立即中断执行sql。

方法1：

#查询正在运行的行程 show processlist; #根据进程id删除正在执行的sql kill id

方法2：

# 如果不知道sql对应的进程id select * from information_schema.`PROCESSLIST` where info is not null ORDER BY time desc;

1、id列：用户登录mysql时，系统分配的"connection_id"，可以使用函数connection_id()查看

2、user列：显示当前用户。如果不是root，这个命令就只显示用户权限范围的sql语句

3、host列：显示这个语句是从哪个ip的哪个端口上发的，可以用来跟踪出现问题语句的用户

4、db列：显示这个进程目前连接的是哪个数据库

5、command列：显示当前连接的执行的命令，一般取值为休眠（sleep），查询（query），连接（connect）等

6、time列：显示这个状态持续的时间，单位是秒

7、state列：显示使用当前连接的sql语句的状态，很重要的列。state描述的是语句执行中的某一个状态。一个sql语句，以查询为例，可能需要经过copying to tmp table、sorting result、sending data等状态才可以完成

8、info列：显示这个sql语句，是判断问题语句的一个重要依据