写一手好 SQL 很有须要

其拭魅这长短常初级的错误，我不禁后背一凉，团队成员的技能程度亟待进步啊。改革这些SQL的进程中，总结了一些履历分享给各人，假若有错误接待品评指正。

MySQL机能

最大数据量

抛开数据量和并发数，谈机能都是耍混混 。MySQL没有限定单表最大记录数，它取决于操纵体系对文件巨细的限定。

文件体系单文件巨细限定FAT32最大4G NTFS最大64GB NTFS5.0最大2TB EXT2块巨细为1024字节，文件最大容量16GB;块巨细为4096字节，文件最大容量2TBEXT3块巨细为4KB，文件最大容量为4TB EXT4理论可以大于16TB

《阿里巴巴Java开拓手册》提出单表行数高出500万行可能单表容量高出2GB，才保举分库分表。机能由综合身分抉择，抛开营业伟大度，影响水平依次是硬件设置、MySQL设置、数据表计划、索引优化。500万这个值仅供参考，并非铁律。博主曾经操纵过高出4亿行数据的单表，分页查询最新的20笔记录耗时0.6秒，SQL语句大抵是 select field_1,field_2 from table where id < #{prePageMinId} order by id desc limit 20，prePageMinId是上一页数据记录的最小ID。固然其时查询速率还拼凑，跟着数据不绝增添，有朝一日一定不堪重负。分库分表是个周期长而风险高的大活儿，应该尽也许在当前布局上优化，好比进级硬件、迁徙汗青数据等等，其实没辙了再分。对分库分表感乐趣的同窗可以阅读分库分表的根基头脑。

最大并发数

并发数是指统一时候数据库能处理赏罚几多个哀求，由maxconnections和maxuserconnections抉择。**maxconnections是指MySQL实例的最大毗连数，上限值是16384，maxuserconnections是指每个数据库用户的最大毗连数。MySQL会为每个毗连提供缓冲区，意味着耗损更多的内存。假如毗连数配置太高硬件吃不用，太低又不能充实操作硬件。一样平常要求两者比值高出10%，计较要领如下：

max_used_connections / max_connections * 100% = 3/100 *100% ≈ 3% 

查察最大毗连数与相应最大毗连数：

show variables like '%max_connections%';show variables like '%max_user_connections%'; 

在设置文件my.cnf中修改最大毗连数

[mysqld]max_connections = 100max_used_connections = 20 

查询耗时0.5秒

提议将单次查询耗时节制在0.5秒以内，0.5秒是个履历值，源于用户体验的 3秒原则 。假如用户的操纵3秒内没有相应，将会憎恶乃至退出。相应时刻=客户端UI渲染耗时+收集哀求耗时+应用措施处理赏罚耗时+查询数据库耗时，0.5秒就是留给数据库1/6的处理赏罚时刻。

实验原则

对比NoSQL数据库，MySQL是个娇气懦弱的家伙。它就像体育课上的女同窗，一点纠纷就和同窗闹别扭(扩容难)，跑两步就气喘吁吁(容量小并发低)，经常身材不适要告假(SQL束缚太多)。现在各人城市搞点漫衍式，应用措施扩容比数据库要轻易得多，以是实验原则是 数据库少干活，应用措施多干活 。

充实操作但不滥用索引，须知索引也耗损磁盘和CPU。

不保举行使数据库函数名目化数据，交给应用措施处理赏罚。

不保举行使外键束缚，用应用措施担保数据精确性。

写多读少的场景，不保举行使独一索引，用应用措施担保独一性。

恰当冗余字段，实行建设中间表，用应用措施计较中间功效，用空间换时刻。

不应承执行十分耗时的事宜，共同应用措施拆分成更小的事宜。

预估重要数据表(好比订单表)的负载和数据增添态势，提前优化。

数据表计划

数据范例

数据范例的选择原则：更简朴可能占用空间更小。

假如长度可以或许满意，整型只管行使tinyint、smallint、medium_int而非int。

假如字符串长度确定，回收char范例。

假如varchar可以或许满意，不回收text范例。

精度要求较高的行使decimal范例，也可以行使BIGINT，好比准确两位小数就乘以100后生涯。

只管回收timestamp而非datetime。

对比datetime，timestamp占用更少的空间，以UTC的名目储存自动转换时区。

停止空值

MySQL中字段为NULL时依然占用空间，会使索引、索引统计越发伟大。从NULL值更新到非NULL无法做到原地更新，轻易产生索引破碎影响机能。尽也许将NULL值用故意义的值取代，也能停止SQL语句内里包括 is not null的判定。

text范例优化

因为text字段储存大量数据，表容量会很早涨上去，影响其他字段的查询机能。提议抽取出来放在子内外，用营业主键关联。

索引优化

索引分类

平凡索引：最根基的索引。

组合索引：多个字段上成立的索引，可以或许加快复合查询前提的检索。

独一索引：与平凡索引相同，但索引列的值必需独一，应承有空值。

组合独一索引：列值的组合必需独一。

主键索引：非凡的独一索引，用于独一标识数据表中的某一笔记录，不应承有空值，一样平常用primary key束缚。

全文索引：用于海量文本的查询，MySQL5.6之后的InnoDB和MyISAM均支持全文索引。因为查询精度以及扩展性不佳，更多的企业选择Elasticsearch。

索引优化

分页查询很重要，假如查询数据量高出30%，MYSQL不会行使索引。

单表索引数不高出5个、单个索引字段数不高出5个。

字符串可行使前缀索引，前缀长度节制在5-8个字符。

字段独一性太低，增进索引没故意义，如：是否删除、性别。

公道行使包围索引，如下所示：select loginname, nickname from member where login_name = ?

loginname, nickname两个字段成立组合索引，比login_name简朴索引要更快

SQL优化

分批处理赏罚

（编辑：河北网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!