怎样去写一手好SQL

• MySQL机能
• 数据表计划
• 索引优化
• SQL优化
• 其他数据库

博主认真的项目首要回收阿里云数据库MySQL，最近频仍呈现慢SQL告警，执行时刻最长的竟然高达5分钟。导出日记后说明，首要缘故起因竟然是没有掷中索引和没有分页处理赏罚。其拭魅这长短常初级的错误，我不禁后背一凉，团队成员的技能程度亟待进步啊。改革这些SQL的进程中，总结了一些履历分享给各人，假若有错误接待品评指正。

MySQL机能

最大数据量

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

《阿里巴巴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。

固然其时查询速率还拼凑，跟着数据不绝增添，有朝一日一定不堪重负。分库分表是个周期长而风险高的大活儿，应该尽也许在当前布局上优化，好比进级硬件、迁徙汗青数据等等，其实没辙了再分。对分库分表感乐趣的同窗可以阅读分库分表的根基头脑。

最大并发数

并发数是指统一时候数据库能处理赏罚几多个哀求，由max_connections和max_user_connections抉择。max_connections是指MySQL实例的最大毗连数，上限值是16384，max_user_connections是指每个数据库用户的最大毗连数。

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 = 100 
max_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。

（编辑：湖南网）

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