MySQL 对于千万级的大表要怎么优化?
https://www.zhihu.com/question/19719997
MYSQL优化主要分为以下四大方面:
设计:存储引擎,字段类型,范式与逆范式
功能:索引,缓存,分区分表。
架构:主从复制,读写分离,负载均衡。
合理SQL:测试,经验。
列类型之数值
(1)整型
MySQL数据库支持五种整型类型,包括:TINYINT、SMALLINT、MEDIUMINT、INT和BIGINT五种。
整型类型占用空间和取值范围
类型 字节 最小值 最大值
TINYINT 1 有符号:-128 无符号:0 有符号:127 无符号:255
SMALLINT 2有符号:-32768无符号:0有符号:32767无符号:65535
MEDIUMINT 3有符号:-8388608无符号:0有符号:8388607无符号:16777215
INT/INTEGER 4有符号:-2147483648无符号:0有符号:2147483647无符号:4294967295
BIGINT 8 有符号:-9223372036854775808无符号:0 有符号:9223372036854775807无符号:18446744073709551615
五种整型的适用场景:
TINYINT,年龄,包含在0~255之间;
SMALLINT,端口号,包含在0~65535之间;
MEDIUMINT,中小型网站注册会员,1600万够用;
INT,身份证编号,42亿可以用很久;
BIGINT,Twitter微博量,几百亿
(2)浮点型(非精确)
MySQL数据库支持两种浮点类型:FLOAT(单精度)和DOUBLE(双精度)两种
浮点型(非精确)占用空间和取值范围
类型 字节 范围
FLOAT 4 正数范围:1.175494351E-38~3.402823466E+38,负数范围:-3.402823466E+38~-1.175494351E-38
DOUBLE 8 正数范围:1.7976931348623157E-308~2.2250738585072014E+308
负数范围:-2.2250738585072014E+308~-1.7976931348623157E-308
(3)定点型(精确)
浮点型由于内部的存储方式是数值,导致它在一定程度上取得的是近似值而非精确值。如果使用定点型,那么就可以精确取得小数部分,因为它内部存储方式是字符串形式。
定点型(精确)占用空间和取值范围
类型 字节 范围
DECIMAL/NUMERIC M+2 M最大65位,D最大30位。
创建一个定点型格式:DECIMAL(M,D),表示小数点D位,整数部分M位及M位内。
2.列类型之日期
MySQL数据库中有五个可用的日期时间数据类型,分别为:DATE、DATETIME、TIME、YEAR、TIMESTAMP。
日期时间类型占用空间和取值范围
类型 字节 最小值 最大值
YEAR 1 1901 2155
TIME 3 -838:59:59838:59:59
DATE 4 1000-01-01 9999-12-31
TIMESTAMP 4 1970-01-01 00:00:00 2038-01-19 03:14:07
DATETIME 8 1000-01-01 00:00:00 9999-12-31 23:59:59
TIMESTAMP有几个特点:
a.当更新一条数据的时候,设置此类型根据当前系统更新可自动更新时间;
b.如果插入一条NULL,也会自动插入当前系统时间;
c.创建字段时,系统会自动给一个默认值;
d.会根据当前时区来存储和查询时间,存储时对当前时区进行转换,查询时再转换为当前的时区。
//查看当前时区
SHOW VARIABLES LIKE ‘time_zone’;
//设置为东九区,查询时间就会加1小时
SET time_zone=’+9:00’;
DATE占用3个字节,包含年月日,范围和DATETIME一样。DATE长度是0,无法设置。
YEAR占用1个字节,包年年份,长度默认为4位,无法设置。
TIME占用3个字节,包含时分秒,长度0到6之间,用于设置微秒。对于TIME的范围的时是-838到838的原因,是因为TIME类型不但可以保存一天的时,还可以包含时间之间的间隔。
综上考虑:使用datetime,当然也可以使用int(11)来保存时间戳。
关于INT(11)存放时间戳的优点如下:
a.INT占4个字节,DATETIME占8个字节;
b.INT存储索引的空间比DATETIME小,查询快,排序效率高;
c.在计算机时间差等范围问题,比较方便。
3.列类型之字符
字符集校对规则utf8_general_ci表示校对时不区分大小写,相对的cs表示区分大小写。还有一个bin结尾的是字节比较。而general是地区名,这里是通用,utf8表示编码。如果是gbk,可以使用gbk_chinese_ci,如果是utf8则用utf8_general。MySQL提供了多种对字符数据的存储类型,包括:CHAR、VARCHAR、VARBINARY、BLOB、TEXT、ENUM和SET等多种字符类型。
(1)CHAR是保存定长字符串,而VARCHAR则是保存变长字符串。CHAR(5)表示必须保存5个字符,而VARCHAR(5)则表示最大保存字符为5。如果是UTF8编码下,长度为5的CHAR类型,最多可以存储15字节,也就是5个汉字的内容。因为一个汉字占3个字节。
由于CHAR类型是定长,MySQL会根据定义的长度进行分配空间,在处理速度上比VARCHAR快的多,所以适合存储例如手机、身份证这种定长的字符,否则就会造成浪费。那么CHAR类型最大可以插入255个字符,最多可以存储765个字节。
(2)BINARY和VARBINARY是采用二进制存储的,没有字符集概念,意义在于防止字符集的问题导致数据丢失,存储中文会占用两个字符,会乱码,半截会问号。因为是采用二进制存储,在比较字符和排序的时候,都是二进制进行的,所以只有需要操作二进制时才需要使用。
(3)八种适合文本内容的大数据类型:TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、TINYBLOG、BLOB、MEDIUMTEXT、LONGTEXT。
综上:短文本定长用char,变长用varchar,长文本用text
4.列类型之属性
无符号(UNSIGNED)和填充零(ZEROFILL),还有是否为空、默认值、主键、自动编号。
严格模式
我们使用的是WAMP集成环境,默认安装的情况下,是非严格模式,用于部署阶段。而开发调试阶段,强烈建议使用严格模式,方便开发中调试将问题及时暴露出来。因为在非严格模式下将NULL插入NOTNULL等非法操作都是被运行的。设置严格模式只要打开my.ini文件,在末尾添加一句:
sql-mode=”STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”
然后,重启服务器即可。检查SQL_MODE状态
SELECT @@global.sql_mode