1.命名规范
1.库名、表名、字段名必须使用小写字母，并采用下划线分割。
a)mysql有配置参数lower_case_table_names，不可动态更改，linux系统默认为 0，即库表名以实际情况存储，大小写敏感。如果是1，以小写存储，大小写不敏感。如果是2，以实际情况存储，但以小写比较。
b)如果大小写混合使用，可能存在abc，abc，abc等多个表共存，容易导致混乱。
c)字段名显示区分大小写，但实际使⽤用不区分，即不可以建立两个名字一样但大小写不一样的字段。
d)为了统一规范， 库名、表名、字段名使用小写字母。
2.库名、表名、字段名禁止超过32个字符。
库名、表名、字段名支持最多64个字符，但为了统一规范、易于辨识以及减少传输量，禁止超过32个字符。
3.使用innodb存储引擎。
innodb引擎是mysql5.5版本以后的默认引擘，支持事务、行级锁，有更好的数据恢复能力、更好的并发性能，同时对多核、大内存、ssd等硬件支持更好，支持数据热备份等，因此innodb相比myisam有明显优势。
4.库名、表名、字段名禁止使用mysql保留字。
当库名、表名、字段名等属性含有保留字时，sql语句必须用反引号引用属性名称，这将使得sql语句书写、shell脚本中变量的转义等变得⾮非常复杂。
5.禁止使用分区表。
分区表对分区键有严格要求；分区表在表变大后，执⾏行ddl、sharding、单表恢复等都变得更加困难。因此禁止使用分区表，并建议业务端手动sharding。
6.建议使用unsigned存储非负数值。
同样的字节数，非负存储的数值范围更大。如tinyint有符号为 -128-127，无符号为0-255。
7.建议使用int unsigned存储ipv4。
用unsinged int存储ip地址占用4字节，char(15)则占用15字节。另外，计算机处理整数类型比字符串类型快。使用int unsigned而不是char(15)来存储ipv4地址，通过mysql函数inet_ntoa和inet_aton来进行转化。ipv6地址目前没有转化函数，需要使用decimal或两个bigint来存储。
例如:
我要评论
select inet_aton('209.207.224.40'); 3520061480
select inet_ntoa(3520061480); 209.207.224.40
8.强烈建议使用tinyint来代替enum类型。
enum类型在需要修改或增加枚举值时，需要在线ddl，成本较高；enum列值如果含有数字类型，可能会引起默认值混淆。
9.使用varbinary存储大小写敏感的变长字符串或二进制内容。
varbinary默认区分大小写，没有字符集概念，速度快。
10.int类型固定占用4字节存储
例如int(4)仅代表显示字符宽度为4位，不代表存储长度。数值类型括号后面的数字只是表示宽度而跟存储范围没有关系，比如int(3)默认显示3位，空格补齐，超出时正常显示，python、java客户端等不具备这个功能。
11.区分使用datetime和timestamp。
存储年使用year类型。存储日期使用date类型。 存储时间(精确到秒)建议使用timestamp类型。
datetime和timestamp都是精确到秒，优先选择timestamp，因为timestamp只有4个字节，而datetime8个字节。同时timestamp具有自动赋值以及⾃自动更新的特性。注意：在5.5和之前的版本中，如果一个表中有多个timestamp列，那么最多只能有一列能具有自动更新功能。
如何使用timestamp的自动赋值属性?
a)自动初始化，而且自动更新：
column1 timestamp default current_timestamp on updatecurrent_timestamp
b)只是自动初始化：
column1 timestamp default current_timestamp
c)自动更新，初始化的值为0：
column1 timestamp default 0 on update current_timestamp
d)初始化的值为0：
column1 timestamp default 0
12.所有字段均定义为not null。
a)对表的每一行，每个为null的列都需要额外的空间来标识。
b)b树索引时不会存储null值，所以如果索引字段可以为null，索引效率会下降。
c)建议用0、特殊值或空串代替null值。
mysql使用技巧
1.将大字段、访问频率低的字段拆分到单独的表中存储，分离冷热数据。
有利于有效利用缓存，防⽌止读入无用的冷数据，较少磁盘io，同时保证热数据常驻内存提⾼高缓存命中率。
2.禁止在数据库中存储明文密码。
采用加密字符串存储密码，并保证密码不可解密，同时采用随机字符串加盐保证密码安全。
3.表必须有主键，推荐使用unsigned自增列作为主键。
表没有主键，innodb会默认设置隐藏的主键列；没有主键的表在定位数据行的时候非常困难，也会降低基于行复制的效率。
4.禁止冗余索引。
索引是双刃剑，会增加维护负担，增⼤大io压力。(a,b,c)、(a,b)，后者为冗余索引。可以利用前缀索引来达到加速目的，减轻维护负担。
5.禁止重复索引。
primary key a;uniq index a;重复索引增加维护负担、占用磁盘空间，同时没有任何益处。
6.不在低基数列上建立索引，例如“性别”。
大部分场景下，低基数列上建立索引的精确查找，相对于不建立索引的全表扫描没有任何优势，而且增大了io负担。
7.合理使用覆盖索引减少io，避免排序。
覆盖索引能从索引中获取需要的所有字段，从⽽而避免回表进行二次查找，节省io。
innodb存储引擎中，secondary index(非主键索引，又称为辅助索引、二级索引)没有直接存储行地址，而是存储主键值。
如果用户需要查询secondary index中所不包含的数据列，则需要先通过secondary index查找到主键值，然后再通过主键查询到其他数据列，因此需要查询两次。覆盖索引则可以在⼀一个索引中获取所有需要的数据，因此效率较高。
例如select email，uid from user_email where uid=xx，如果uid不是主键，适当时候可以将索引添加为index(uid，email)，以获得性能提升。
8.用in代替or。sql语句中in包含的值不应过多，应少于1000个。
in是范围查找，mysql内部会对in的列表值进行排序后查找，比or效率更高。
9.表字符集使用utf8，必要时可申请使用utf8mb4字符集。
a)utf8字符集存储汉字占用3个字节，存储英文字符占用一个字节。
b)utf8统一而且通用，不会出现转码出现乱码风险。
c)如果遇到emoj等表情符号的存储需求，可申请使用utf8mb4字符集。
10.用union all代替union。
union all不需要对结果集再进行排序。
11.禁止使用order by rand()。
order by rand()会为表增加一个伪列，然后用rand()函数为每一行数据计算出rand()值，然后基于该行排序，这通常都会生成磁盘上的临时表，因此效率非常低。建议先使用rand()函数获得随机的主键值，然后通过主键获取数据。
12.建议使用合理的分页方式以提高分页效率。
假如有类似下面分页语句:
select * from table order by time desc limit 10000，10;
这种分页方式会导致大量的io，因为mysql使用的是提前读取策略。
推荐分页方式:
select * from table where time<last_time order by time desc limit 10. select * from table inner join (select id from table order by time limit 10000，10) as t using(id)
13.select只获取必要的字段，禁⽌止使用select *。
减少网络带宽消耗；
能有效利用覆盖索引；
表结构变更对程序基本无影响。
14.sql中避免出现now()、rand()、sysdate()、current_user()等不确定结果的函数。
语句级复制场景下，引起主从数据不一致；不确定值的函数，产⽣生的sql语句无法利用query cache。
15.采用合适的分库分表策略。例如千库十表、十库百表等。
采用合适的分库分表策略，有利于业务发展后期快速对数据库进行水平拆分，同时分库可以有效利⽤用mysql的多线程复制特性。
16.减少与数据库交互次数，尽量采用批量sql语句。
使用下面的语句来减少和db的交互次数:
a)insert ... on duplicate key update
b)replace into
c)insert ignore
d)insert into values()
17.拆分复杂sql为多个小sql，避免大事务。
简单的sql容易使⽤用到mysql的query cache；减少锁表时间特别是myisam；可以使用多核 cpu。
18.对同一个表的多次alter操作必须合并为一次操作。
mysql对表的修改绝大部分操作都需要锁表并重建表，而锁表则会对线上业务造成影响。为减少这种影响，必须把对表的多次alter操作合并为一次操作。例如，要给表t增加一个字段b，同时给已有的字段aa建立索引，
通常的做法分为两步：
alter table t add column b varchar(10);
然后增加索引：
alter table t add index idx_aa(aa);
正确的做法是：
alter table t add column b varchar(10),add index idx_aa(aa);
19.避免使用存储过程、触发器、视图、自定义函数等。
这些高级特性有性能问题，以及未知bug较多。业务逻辑放到数据库会造成数据库的ddl、scale out、sharding等变得更加困难。
20.禁止有super权限的应用程序账号存在。
安全第一。super权限会导致read only失效，导致较多诡异问题而且很难追踪。
21.不要在mysql数据库中存放业务逻辑。
数据库是有状态的服务，变更复杂而且速度慢，如果把业务逻辑放到数据库中，将会限制业务的快速发展。建议把业务逻辑提前，放到前端或中间逻辑层，而把数据库作为存储层，实现逻辑与存储的分离。
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，谢谢大家对移动技术网的支持。如果你想了解更多相关内容请查看下面相关链接
如对本文有疑问， 点击进行留言回复！！
服务器Centos部署MySql并连接Navicat过程详解
网友评论