命名规范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语句必须使用反引号来引用属性名，这会使在shell脚本中编写SQL语句和转义变量变得非常复杂。5.禁止使用分区表。分区表对分区键有严格的要求；分区表变大后，DDL、SHARDING、单表恢复变得更加困难。因此禁止使用分区表，在业务端推荐手动SHARDING。6、推荐使用UNSIGNED存储非负值。同样的字节数，非负存储的取值范围更大。例如，TINYINT有符号数为-128-127，无符号数为0-255。7、推荐使用INTUNSIGNED存储IPV4。使用UNSINGEDINT存储IP地址占用4个字节，而CHAR(15)占用15个字节。此外，计算机处理整数类型的速度比字符串类型快。使用INTUNSIGNED代替CHAR(15)来存储IPV4地址，并通过MySQL函数inet_ntoa和inet_aton进行转换。IPv6地址目前没有转换功能，需要使用DECIMAL或者两个BIGINT来存储。例如：SELECTINET_ATON('209.207.224.40');3520061480SELECTINET_NTOA(3520061480);209.207.224.408。强烈建议使用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个字节，而DATETIME有8个字节。同时，TIMESTAMP具有自动赋值和自动更新的特点。注意：在5.5及之前的版本中，如果一个表中有多个timestamp列，最多只有一个列可以有自动更新功能。如何使用TIMESTAMP的自动赋值属性？a)Automaticinitializationandautomaticupdate:column1TIMESTAMPDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPb)onlyautomaticinitialization:column1TIMESTAMPDEFAULTCURRENT_TIMESTAMPc)automaticupdate,theinitializedvalueis0:column1TIMESTAMPDEFAULT0ONUPDATECURRENT_TIMESTAMPd)allfieldsaredefinedasNOUPDATECURRENT_TIMESTAMPd)theinitialvalueis0:column1TIMESTAMPDEFAULTCURRENT_TIMESTAMPd)allfieldsaredefinedasNOUPDATECURRENT_TIMESTAMPd)column1TIMESTAMP无效的。a)对于表的每一行，每一列为NULL都需要额外的空间来标识。b)B树索引时不会存储NULL值，所以如果索引字段可以为NULL，索引效率会降低。c)建议用0、特殊值或空字符串替换NULL值。MySQL使用技巧1、将大字段和访问频率低的字段拆分成单独的表存储，冷热数据分离。有利于缓存的有效利用，避免读取无用的冷数据，减少磁盘IO，保证热数据常驻内存，提高缓存命中率。2、禁止在数据库中存储明文密码。使用加密字符串存储密码，保证密码不可解密，使用随机字符串加盐保证密码安全。3、表必须有主键，推荐使用UNSIGNED自增列作为主键。如果表没有主键，INNODB会默认设置隐藏主键列；对于没有主键的表，很难定位到数据行，也会降低行复制的效率。4、禁止冗余索引。索引是一把双刃剑，会增加维护负担，增加IO压力。(a,b,c),(a,b)，后者是冗余索引。可以使用前缀索引来达到加速和减轻维护负担的目的。5.禁止重复索引。主键a；唯一索引a；重复索引增加维护负担，占用磁盘空间，同时没有任何好处。6、不要在低基数的列上建索引，比如“gender”。在大多数场景下，在低基数列上精确查找索引与不带索引的全表扫描相比没有优势，反而增加了IO负担。7、合理使用覆盖索引，减少IO，避免排序。覆盖索引可以从索引中获取所有需要的字段，从而避免回表二次查找，节省IO。在INNODB存储引擎中，二级索引（non-primarykeyindex，也称辅助索引，secondaryindex）不直接存储行地址，而是存储主键值。如果用户需要查询未包含在二级索引中的数据列，需要先通过二级索引找到主键值，再通过主键查询其他数据列，所以需要两次查询。覆盖索引可以在一个索引中获取所有需要的数据，因此效率更高。比如SELECTemail,uidFROMuser_emailWHEREuid=xx，如果uid不是主键，可以在适当的时候加上索引为index(uid,email)来提高性能。8.将OR替换为IN。SQL语句中IN包含的值不宜太多，应小于1000。IN是范围搜索，MySQL内部对IN的列表值进行排序进行搜索，比OR效率更高。9、表格字符集使用UTF8，如有需要可申请使用UTF8MB4字符集。a)UTF8字符集占用3个字节存储汉字，1个字节存储英文字符。b)UTF8统一通用，转码不存在乱码风险。c)如果满足EMOJ等表情符号的存储要求，可以申请使用UTF8MB4字符集。10.将UNION替换为UNIONALL。UNIONALL不需要对结果集进行排序。11.禁止使用rand()排序。orderbyrand()会在表中添加一个伪列，然后使用rand()函数计算每一行数据的rand()值，然后根据行进行排序，这通常会生成一个临时表磁盘，所以效率很低。推荐使用rand()函数先获取一个随机的主键值，再通过主键获取数据。12、建议使用合理的分页方式，提高分页效率。如果有类似如下的分页语句：SELECT*FROMtableORDERBYTIMEDESCLIMIT10000,10;这种分页方式会造成很多io，因为MySQL采用了预读策略。推荐分页方式：SELECT*FROMtableWHERETIME13.SELECT只获取必要的字段，禁止SELECT*。减少网络带宽消耗；可以有效地使用覆盖索引；表结构的改变对程序基本没有影响。14、SQL中避免使用now()、rand()、sysdate()、current_user()等结果不确定的函数。语句级复制场景，造成主从数据不一致；取值不确定的函数生成的SQL语句不能使用QUERYCACHE。15、采用合适的分库分表策略。比如千库十表、十库百表等。采用合适的分库分表策略，有利于在业务发展后期快速水平拆分数据库，同时此时，分库可以有效利用MySQL的多线程复制特性。16、减少与数据库的交互次数，尽量使用批处理SQL语句。使用以下语句减少与db的交互次数：a)INSERT...ONDUPLICATEKEYUPDATEb)REPLACEINTOc)INSERTIGNOREd)INSERTINTOVALUES()17.将复杂SQL拆分为多个小SQL以避免大SQL交易。简单SQL好用MySQL的QUERYCACHE；减少表锁定时间，尤其是MyISAM；可以使用多核CPU。18.对同一张表的多个alter操作必须合并为一个操作。mysql大部分修改表的操作都需要锁表重建表，锁表会影响线上业务。为了减少这种影响，必须将对表的多个更改操作合并为一个操作。比如在表t中增加一个字段b，并为已有的字段aa建立索引，通常的方法分为两步：altertabletaddcolumnbvarchar(10);然后添加索引：altertabletaddindexidx_aa(aa);正确的做法是：altertabletaddcolumnbvarchar(10),addindexidx_aa(aa);19、避免使用存储过程、触发器、视图、自定义函数等，这些高级特性存在性能问题和许多未知的bug。将业务逻辑放在数据库中，会增加数据库的DDL、SCALEOUT、SHARDING等实现难度。20.禁止超级权限的应用账号存在。安全第一。超级权限会导致只读失败，导致更多怪异问题难以追查。21、不要将业务逻辑存储在MySQL数据库中。数据库是有状态服务，变化复杂缓慢。如果将业务逻辑放在数据库中，会限制业务的快速发展。建议把业务逻辑提前，放在前端或者中间逻辑层，以数据库作为存储层，实现逻辑和存储分离。
