MySQL开拓类型与行使能力总结
|
定名类型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来存储。 譬喻：
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的自动赋值属性?
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 13.SELECT只获取须要的字段，禁⽌止行使SELECT *。 镌汰收集带宽耗损; 能有用操作包围索引; 表布局改观对措施根基无影响。 14.SQL中停止呈现now()、rand()、sysdate()、current_user()等不确定功效的函数。 语句级复制场景下，引起主从数据纷歧致;不确定值的函数，产⽣生的SQL语句无法操作QUERY CACHE。 15.回收吻合的分库分表计策。譬喻千库十表、十库百表等。 回收吻合的分库分表计策，有利于营业成长后期快速对数据库举办程度拆分，同时分库可以有用利⽤用MySQL 的多线程复制特征。 16.镌汰与数据库交互次数，只管回收批量SQL语句。 行使下面的语句来镌汰和db的交互次数: a)INSERT ... ON DUPLICATE KEY UPDATEb)REPLACE INTOc)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数据库中存放营业逻辑。 数据库是有状态的处事，改观伟大并且速率慢，假如把营业逻辑放到数据库中，将会限定营业的快速成长。提议把营业逻辑提前，放到前端或中间逻辑层，而把数据库作为存储层，实现逻辑与存储的疏散。
（编辑：河北网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
