Mysql建表、索引、函数、查询使用中的坑！！！

I’m back！Fighting!

0. 序言

- Mysql建表原则

具体内容可参阅Mysql的建表规范与注意事项这篇文章
本内容参照MySql表、索引设计参考规范归纳

以下只说明特别注意点：
基础规范：
（1）表存储引擎强烈建议使用InnoDB；
（2）表字符集默认使用utf8，必要时候使用utf8mb4；（utf8通用，无乱码风险，汉字3字节，英文1字节）
（3）禁止使用存储过程，视图，触发器，Event；
（4）禁止在数据库中存储大文件，例如照片，可以将大文件存储在对象存储系统，数据库中存储路径；
命名规范：
（5）库名，表名，列名必须用小写，采用下划线分隔，必须见名知义，长度不要超过32字符；
（6）库备份必须以bak为前缀以日期为后缀，从库必须以-s为后缀，备库必须以-ss为后缀；
表设计规范：
（7）单实例表个数必须控制在2000个以内，单表分表个数必须控制在1024个以内；
（8）表必须有主键，推荐使用UNSIGNED整数为主键；（删除无主键的表，如果是row模式的主从架构，从库会挂住）
（9）建议将大字段，访问频度低的字段拆分到单独的表中存储，分离冷热数据；
列设计规范：
（10）区分使用tinyint/int/bigint，分别占用1/4/8字节，区分用char/varchar；（字段长度固定用char，字段长度相差较大用varchar）
（11）区分使用datetime/timestamp，分别占用5/4字节，存储时间（精确到秒）建议使用TIMESTAMP类型；（存储年使用YEAR，存储日期使用DATE，存储时间使用datetime）
（12）必须把字段定义为NOT NULL并设默认值；
（13）NULL需更多的存储空间，NULL只能采用 IS NULL或者 IS NOT NULL，而在=/!=/in/not in时有大坑！！！
（14）使用varchar(20)存储手机号，不要使用整数；
（15）使用TINYINT来代替ENUM；（ENUM增加新值要进行DDL操作）
（补）存储精确浮点数必须使用DECIMAL替代FLOAT和DOUBLE；
（补）整型定义中不添加长度，比如使用INT，而不是INT(4)；
（补）尽可能不使用TEXT、BLOB类型；
索引规范：
（16）唯一索引使用 uniq_[字段名] 来命名；
（17）非唯一索引使用 idx_[字段名] 来命名；
（18）单张表索引数量建议控制在5个以内，组合索引字段数不建议超过5个；
（19）不建议在频繁更新的字段上建立索引；
（20）非必要不要进行JOIN查询，如果要进行JOIN查询，被JOIN的字段必须类型相同，并建立索引；
（21）理解组合索引最左前缀原则，避免重复建设索引，如果建立了(a,b,c)，相当于建立了(a), (a,b), (a,b,c)；
SQL规范：
（22）禁止使用select *，只获取必要字段；
（23）insert必须指定字段，禁止使用insert into T values()；
（24）禁止在where条件列使用函数或者表达式；（导致不能命中索引，全表扫描）
（25）禁止负向查询以及%开头的模糊查询；（导致不能命中索引，全表扫描）
（26）禁止大表JOIN和子查询；
（27）同一个字段上的OR必须改写为IN，IN的值必须少于50个；
（28）WHERE条件中的非等值条件（IN、BETWEEN、<、<=、>、>=）会导致后面的条件使用不了索引；

- Mysql不同引擎下查询对比

本内容参照mysql时间字段创建索引和不创建索引查询性能分析归纳得出。
表格中完整查询语句 “select count(id) from test_table where …”。

存储引擎	是否建索引	索引字段	时间字段类型	查询语句	耗时(秒)	备注
MyISAM引擎	否	d_int	int	where d_int > 400000 AND d_int < 600000;	0.078	无索引，MyISAM引擎效能较高
MyISAM引擎	否	d_int	int	where d_int > UNIX_TIMESTAMP(‘1970-01-05 23:06:40’) AND d_int < (‘1970-01-08 06:40:00’);	0.078	无索引，MyISAM引擎效能较高
MyISAM引擎	否	d_timestamp	timestamp	where d_timestamp > ‘1970-01-05 23:06:40’ AND d_timestamp < ‘1970-01-08 06:40:00’;	0.4368	直接和日期比较查询效率，低于UNIX_TIMESTAMP内置函数查询
MyISAM引擎	否	d_timestamp	timestamp	where UNIX_TIMESTAMP(d_timestamp) > 400000 AND UNIX_TIMESTAMP(d_timestamp) < 600000;	0.078	UNIX_TIMESTAMP内置函数查询效率，高出直接和日期比较
MyISAM引擎	否	d_datetime	datetime	where d_datetime > ‘1970-01-05 23:06:40’ AND d_datetime < ‘1970-01-08 06:40:00’;	0.137	直接和日期比较查询效率，高于UNIX_TIMESTAMP内置函数查询
MyISAM引擎	否	d_datetime	datetime	where UNIX_TIMESTAMP(d_datetime) > 400000 AND UNIX_TIMESTAMP(d_datetime) < 600000;	0.7498	UNIX_TIMESTAMP内置函数查询效率，低出直接和日期比较
MyISAM引擎	是	d_int	int	where d_int > 400000 AND d_int < 600000;	0.39	有索引，MyISAM引擎效能反而低
MyISAM引擎	是	d_int	int	where d_int > UNIX_TIMESTAMP(‘1970-01-05 23:06:40’) AND d_int < (‘1970-01-08 06:40:00’);	0.3824	有索引，MyISAM引擎效能反而低
MyISAM引擎	是	d_timestamp	timestamp	where d_timestamp > ‘1970-01-05 23:06:40’ AND d_timestamp < ‘1970-01-08 06:40:00’;	0.5696	有无索引区别不大，直接和日期比较查询效率，低于UNIX_TIMESTAMP内置函数查询
MyISAM引擎	是	d_timestamp	timestamp	where UNIX_TIMESTAMP(d_timestamp) > 400000 AND UNIX_TIMESTAMP(d_timestamp) < 600000;	0.078	有无索引区别不大，UNIX_TIMESTAMP内置函数查询效率，高出直接和日期比较
MyISAM引擎	是	d_datetime	datetime	where d_datetime > ‘1970-01-05 23:06:40’ AND d_datetime < ‘1970-01-08 06:40:00’;	0.4508	有索引的效率反而低
MyISAM引擎	是	d_datetime	datetime	where UNIX_TIMESTAMP(d_datetime) > 400000 AND UNIX_TIMESTAMP(d_datetime) < 600000;	0.7614	有索引的效率反而低
InnoDB引擎	否	d_int	int	where d_int > 400000 AND d_int < 600000;	0.3198	无索引，InnoDB引擎性能较MyISAM引擎降低
InnoDB引擎	否	d_int	int	where d_int > UNIX_TIMESTAMP(‘1970-01-05 23:06:40’) AND d_int < (‘1970-01-08 06:40:00’);	0.3092	无索引，InnoDB引擎性能较MyISAM引擎降低
InnoDB引擎	否	d_timestamp	timestamp	where d_timestamp > ‘1970-01-05 23:06:40’ AND d_timestamp < ‘1970-01-08 06:40:00’;	0.7092	直接和日期比较查询效率，低于UNIX_TIMESTAMP内置函数查询
InnoDB引擎	否	d_timestamp	timestamp	where UNIX_TIMESTAMP(d_timestamp) > 400000 AND UNIX_TIMESTAMP(d_timestamp) < 600000;	0.316	UNIX_TIMESTAMP内置函数查询效率，高出直接和日期比较
InnoDB引擎	否	d_datetime	datetime	where d_datetime > ‘1970-01-05 23:06:40’ AND d_datetime < ‘1970-01-08 06:40:00’;	0.3834	直接和日期比较查询效率，高于UNIX_TIMESTAMP内置函数查询
InnoDB引擎	否	d_datetime	datetime	where UNIX_TIMESTAMP(d_datetime) > 400000 AND UNIX_TIMESTAMP(d_datetime) < 600000;	0.9794	UNIX_TIMESTAMP内置函数查询效率，低出直接和日期比较
InnoDB引擎	是	d_int	int	where d_int > 400000 AND d_int < 600000;	0.0522	有索引，InnoDB引擎性能较MyISAM有大幅提高
InnoDB引擎	是	d_int	int	where d_int > UNIX_TIMESTAMP(‘1970-01-05 23:06:40’) AND d_int < (‘1970-01-08 06:40:00’);	0.0624	有索引，InnoDB引擎性能较MyISAM有大幅提高
InnoDB引擎	是	d_timestamp	timestamp	where d_timestamp > ‘1970-01-05 23:06:40’ AND d_timestamp < ‘1970-01-08 06:40:00’;	0.1776	直接和日期比较查询效率，高于UNIX_TIMESTAMP内置函数查询
InnoDB引擎	是	d_timestamp	timestamp	where UNIX_TIMESTAMP(d_timestamp) > 400000 AND UNIX_TIMESTAMP(d_timestamp) < 600000;	0.2944	UNIX_TIMESTAMP内置函数查询效率，低出直接和日期比较
InnoDB引擎	是	d_datetime	datetime	where d_datetime > ‘1970-01-05 23:06:40’ AND d_datetime < ‘1970-01-08 06:40:00’;	0.082	直接和日期比较查询效率，高于UNIX_TIMESTAMP内置函数查询
InnoDB引擎	是	d_datetime	datetime	where UNIX_TIMESTAMP(d_datetime) > 400000 AND UNIX_TIMESTAMP(d_datetime) < 600000;	0.9994	UNIX_TIMESTAMP内置函数查询效率，低出直接和日期比较

总结与建议：
（1）MyISAM引擎下，推荐在无索引进行查询，未建立索引效率从高到低：
int > UNIX_TIMESTAMP(timestamp) > datetime（直接和时间比较）>timestamp（直接和时间比较）>UNIX_TIMESTAMP(datetime) 。

（2）InnoDB引擎下，推荐在有索引进行查询，不建议在无索引下进行查询，建立索引效率从高到低：
int > datetime（直接和时间比较） > timestamp（直接和时间比较）> UNIX_TIMESTAMP(timestamp) > UNIX_TIMESTAMP(datetime)。

对于MyISAM引擎，采用 UNIX_TIMESTAMP(timestamp) 比较；对于InnoDB引擎，建立索引，采用 int 或 datetime直接时间比较。

- Mysql查询语句执行计划

1. MySQL时间类型字段的范围查询不走索引了？

• 情况一：时间索引使用函数会失效

# 索引失效sql，create_time使用了DATE_FORMAT()函数
SELECT * FROM test_table WHERE DATE_FORMAT(create_time,"%Y-%m-%d") >= '2020-10-27';

# 索引生效sql
SELECT * FROM test_table WHERE create_time >= str_to_date('2020-10-27', '%Y-%m-%d')

• 情况二：查询优化器影响
  我们建的索引并不是总会起作用的，中间有查询优化器插足，它会判断一个查询SQL是否走索引查得更快，若是，就走索引，否则做全表扫描。以前有个百分比（30%）决定SQL是走索引还是走全表扫描，就是说如果总共有100行记录，走索引查询出来的记录超过30条，那还不如不走索引了。但是现在MySQL不这么干了，不只通过这个百分比来决定走不走索引，而是要参考更多因素来做决定。

未完待续。。。。。