SQL编码规范

一、目的

SQL编码规范是数据库设计和维护的重要组成部分，它们有助于确保数据库的结构清晰、性能优化、易于维护和扩展。为提高SQL代码的质量，减少错误，提升性能，特制定本规范。

二、适用范围

研发部所有开发人员。

三、遵循原则

1.    简洁性：SQL语句应该尽可能简洁，避免不必要的复杂性和冗余。

2.    标准化：遵循SQL标准，使用标准的语法和函数，以提高代码的可移植性。

3.    性能优化：合理使用索引，避免全表扫描，减少不必要的资源消耗。

4.    可维护性：代码应易于维护和升级，避免硬编码值，使用配置和变量代替。

四、主要要求

1.      命名规范：

    a.    使用有意义的、描述性的表和列名。

    b.    表名、列名、索引名等使用小写字母，单词之间用下划线分隔。

    c.    视图、存储过程、函数等名称应该能够反映其功能和用途。

    d.    使用一致的命名约定，如使用前缀或后缀来区分类型。

2.      注释

    a.    对复杂的查询、存储过程、触发器等添加详细注释，说明其功能、输入、输出和业务逻辑。

    b.    保持注释的及时更新，与代码变更同步。

3.      关键字和保留字：SQL关键字和保留字应使用大写，以区分其他标识符。

4.      代码格式

    a.    使用缩进来提高代码的可读性，每个层次缩进一个制表位或两个空格。

    b.    将每个SQL语句单独放在一行，或者合理地分成多行，以提高可读性。

    c.    在合适的地方使用空行分隔不同的逻辑部分。

5.      数据类型

    a.    选择合适的数据类型，避免不必要的空间浪费。

    b.    对于字符串，根据实际需要选择合适长度的VARCHAR或TEXT。

    c.    对于日期和时间，使用专门的日期时间类型。

6.      索引：

    a.    为经常用于查询条件的列创建索引。

    b.    避免过度索引，因为索引会降低插入、更新和删除操作的性能。

7.      查询优化：

    a.    避免使用SELECT *，而是明确指定所需的列。

    b.    在WHERE子句中使用有效的条件，减少不必要的数据扫描。

    c.    使用JOIN代替子查询，以提高查询性能。

8.      事务处理：

    a.    保持事务的原子性、一致性、隔离性和持久性。

    b.    避免长时间运行的事务，以减少锁定和死锁的可能性。

9.      安全：

    a.    防止SQL注入，使用参数化查询或预处理语句。

    b.    控制数据库权限，确保用户只能访问他们需要的数据。

五、优化原则

1.   不要select *

通常在程序里面，是不建议使用select *的，具体使用哪些字段，查哪些字段即可，主要有以下几方面原因：

性能问题：当查询大量数据时，使用 SELECT * 会返回表中的所有列，这可能导致大量数据的传输，并增加处理时间。如果只需要表中的特定列，明确指定这些列名可以显著提高查询性能。

数据泄露风险：如果查询结果意外地暴露了敏感数据（例如，通过日志、错误消息或调试工具），使用 SELECT * 可能会暴露表中的所有列，而不是只暴露需要的列。这增加了数据泄露的风险。

数据库大小：随着时间的推移，表中的列可能会增加或减少。使用 SELECT * 可能会导致查询返回不必要的数据。

维护问题：如果表的结构发生变化（例如，添加或删除列），使用 SELECT * 的查询将不会自动更新。这可能会导致查询返回错误的结果或导致应用程序错误。

2.   查询条件避免使用or

在语句中使用 OR条件可能会导致性能问题，尤其是在数据库大表中，当使用OR连接多个查询条件时，查询优化解释器就可能无法有效地使用索引，导致全表扫描，从而降低查询性能，总体而言，使用OR可能会导致以下几方面的问题：

索引失效：使用 OR 条件连接列时，如果列上有索引，查询优化器可能会决定不使用这些索引，因为优化器认为全表扫描更快，这会导致查询性能下降。

性能下降：在数据库大表中，使用OR条件可能显著的导致查询性能下降，因为数据库需要扫描更多的数据来查找满足条件的行。

无法利用索引优势：即使某些列上有索引，查询优化器也无法有效地使用这些索引来加速查询，因为OR条件导致无法利用索引的优势。

SQL语句中应该尽量避免使用OR，可改为以下方式使用：

使用 UNION 替代 OR：将OR 条件拆分为多个查询，并使用 UNION 将结果合并。每个查询可以单独优化并使用索引，从而提高性能。

创建复合索引：如果经常在多个列上使用 OR 条件进行查询，可以考虑创建一个复合索引来加速查询。复合索引包含所有涉及的列，可以使查询优化器更有效地使用索引。

重构查询：尝试重新编写查询，避免使用 OR 条件连接列。可以使用其他逻辑或联接操作来达到相同的效果。

分析查询执行计划：使用数据库提供的工具explain分析查询的执行计划，查看是否可以优化索引或查询结构来提高性能。

3.   使用数字型替代字符串型

ID主键使用int或者bigint类型，性别，状态类等字段通常可以使用tinyint类型。使用数字类型，具体有以下方面的优点：

节省存储空间（通常比字符串类型更紧凑）。

计算速度快（数字类型的操作通常比字符串操作更快）。

容易进行数值比较和排序。

4.   使用varchar替代char

char：定长。不论实际存储的字符串长度如何，都会占用固定长度的空间。例如，CHAR(10) 会始终占用 10 个字符的空间，即使实际存储的数据只有 5 个字符。由于是定长的，所以在某些情况下可能比 VARCHAR 更快，尤其是当数据长度变化不大时。适用于那些长度几乎不变或长度变化不大的字段，例如星期（'星期一' 到 '星期日'）。CHAR 类型的字段默认值只接受字符串（'')。如果插入的数据长度小于定义的长度，MySQL 会自动在其右侧添加空格以达到定义的长度。

varchar：变长。根据实际存储的字符串长度来分配空间。例如，VARCHAR(10) 可以存储最多 10 个字符的字符串，但实际使用的空间会根据实际数据长度而变化。对于经常变长的数据，VARCHAR 可能更有优势，因为它不会浪费空间来存储不需要的数据。适用于长度经常变化或长度不固定的字段，例如用户名、地址或描述字段。VARCHAR 类型的字段默认值只接受字符串（''）或 NULL。如果插入的数据长度小于定义的长度，MySQL 会截断超出的部分。

5.   避免使用!=和<>

为了确保最大的兼容性和可移植性，最好使用 ANSI SQL 标准中定义的操作符。

性能：!= 或 <> 操作符通常无法有效利用索引，因为它们选择除了某个特定值之外的所有值，这可能涉及到表中的大多数行，尤其是当被比较的列没有索引或者被排除的值很少时。

相比之下，使用 = 或其他范围查询（如 <, <=, >, >=）通常可以更好地利用索引，因为它们可以快速定位到索引中的特定区域。

当表数据量随时间增长时，!= 或 <> 查询的性能可能会逐渐变差，因为返回的数据集可能会变得越来越大。

维护：如果代码需要在不同的数据库系统之间迁移，使用标准操作符可以简化迁移过程，因为它们在不同的系统中具有一致的行为。

尽管通常建议避免使用 != 或 <>，在某些特定情况下，如果需要排除少数几个值并且对性能影响不大时，它们仍然可以使用。

6.   用内连代替左连及右连

inner join ：内连接，只保留两张表中完全匹配的结果集；

left join： 会返回左表所有的行，即使在右表中没有匹配的记录；

right join：会返回右表所有的行，即使在左表中没有匹配的记录；

如果inner join是等值连接，返回的行数比较少，所以性能相对会好一点；

使用了左连接，左边表数据结果尽量小，条件尽量放到左边处理，意味着返回的行数可能比较少；

这是mysql优化原则，就是小表驱动大表，小的数据集驱动大的数据集，从而让性能更优；

7.   合理使用group by

选择适当的列进行分组：确定哪些列最适合进行分组。通常，分组列应该是那些在WHERE子句中用于过滤数据的列，或者在聚合函数中使用的列。

避免在GROUP BY子句中使用不必要的列，这可能导致性能下降。

使用聚合函数：

GROUP BY通常与聚合函数（如COUNT(), SUM(), AVG()等）一起使用，以对每个分组执行计算。

确保在使用聚合函数时选择正确的列。例如，如果你想计算每个部门的员工数量，你应该在GROUP BY子句中使用部门列，并在聚合函数中使用员工ID列。

排序结果：使用ORDER BY子句对分组后的结果进行排序。这有助于按照特定的顺序显示结果，使数据更易于理解。

避免使用隐式类型转换：确保在比较和连接不同数据类型的列时使用适当的类型转换。避免隐式类型转换可能导致意外的结果或性能问题。

优化查询性能：考虑查询的执行计划和索引使用情况。使用EXPLAIN关键字可以帮助分析查询的执行计划，并确定是否需要优化索引或查询结构。

尽量减少在GROUP BY子句中使用非索引列，这有助于提高查询性能。

注意NULL值处理：在处理包含NULL值的列时，要特别小心。GROUP BY和聚合函数通常对NULL值有特定的处理方式。可以和IFNULL函数结合使用。

合理使用子查询和临时表：在某些情况下，将复杂的GROUP BY查询分解为多个步骤，使用子查询或临时表，可以提高查询性能和可读性。

8.   表连接及索引

表连接不宜太多，一般5个以内

ü  关联的表个数越多，编译的时间和开销也就越大

ü  每次关联内存中都生成一个临时表，应该把连接表拆开成较小的几个执行，可读性更高

ü  如果一定需要连接很多表才能得到数据，那么意味着这是个糟糕的设计了

ü  阿里规范中，建议多表联查三张表以下

索引不宜太多，一般5个以内

ü  索引并不是越多越好，虽其提高了查询的效率，但却会降低插入和更新的效率；

ü  索引可以理解为就是一张表，其可以存储数据，其数据就要占空间；

ü  索引表的数据是排序的，排序也是要花时间的；

ü  insert或update时有可能会重建索引，如果数据量巨大，重建将进行记录的重新排序，所以建索引需要慎重考虑，视具体情况来定；

ü  一个表的索引数最好不要超过5个，若太多需要考虑一些索引是否有存在的必要。

索引创建原则：

1、主键是天然是索引。

2、占用存储空间少的字段更适合选作索引的关键字。例如，与字符串相比，整数字段占用的存储空间较少，因此，较为适合选作索引关键字。

3、存储空间固定的字段更适合选作索引的关键字。与 text 类型的字段相比， char 类型的字段较为适合选作索引关键字。

4、Where 子句中经常使用的字段应该创建索引，分组字段或者排序字段应该创建索引，两个表的连接字段应该创建索引。

5、更新频繁的字段不适合创建索引，不会出现在 where 子句中的字段不应该创建索引。

6、经常与其他表进行连接的表，在连接字段上应该建立索引；

7、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引。

9.   索引最左原则

如果使用了联合索引（即多个列的索引），则最左边的列在索引中最有用。也就是说，只有当查询条件中使用了复合索引的最左边的列时，索引才会被有效地利用。

例如，假设有一个联合索引(A, B, C)，如果查询条件中只涉及列A，那么索引会被完全利用；如果查询条件中同时涉及列A和列B，那么索引仍然会被利用；但如果查询条件中只涉及列B或列C，那么索引不会被利用。

再例如，假设你有一个订单表，其中包含customer_id和order_date两列，并且你经常根据顾客ID和时间范围来查询订单。在这种情况下，创建一个以customer_id为第一列，order_date为第二列的复合索引会是一个很好的选择。

因此，在设计联合索引时，应该将查询中最常用于条件判断的列放在最左边，以提高索引的利用率。同时，如果查询中涉及到的列不在联合索引的最左边，可以考虑创建单列索引或重新设计复合索引。

10.  合理使用like查询

前缀查询：当使用LIKE进行查询时，如果通配符%放在前面，数据库将无法使用索引，导致全表扫描。例如，查询LIKE '%xyz'将导致全表扫描，而查询LIKE 'xyz%'可以利用索引。因此，尽量将通配符放在字符串的末尾。

避免使用过多的通配符：如果LIKE查询中通配符过多，可能会导致数据库无法有效地使用索引。例如，查询LIKE '%xyz%'可能导致全表扫描。尽量避免在LIKE查询中使用过多的通配符。

考虑使用全文搜索：对于需要执行复杂的文本搜索的应用程序，可以考虑使用MySQL的全文搜索功能。全文搜索提供了更高效和准确的文本搜索功能，可以处理更复杂的查询模式。

全文搜索功能是通过FULLTEXT索引实现的。在创建表时或者之后添加FULLTEXT索引到一个或多个CHAR、VARCHAR或TEXT类型的列上。

使用适当的字符集和校对规则：在执行LIKE查询时，选择适当的字符集和校对规则可以提高查询的准确性和性能。确保字符集和校对规则与您的数据和查询需求相匹配。

考虑使用覆盖索引：如果您的查询中同时使用了WHERE和ORDER BY子句，可以考虑创建覆盖索引，以减少数据读取的开销并提高查询性能。覆盖索引包含查询中所需的所有列，使得数据库可以直接从索引中获取所需的数据，而不是从数据表中读取。

避免在列上使用函数或表达式：在LIKE查询中，避免在列上使用函数或表达式，这会导致索引失效并可能导致全表扫描。例如，查询LIKE CONCAT(column_name, '%')或data_format(ProduceDate,'%Y-%m-%d')将无法利用索引。

注意数据类型匹配：确保LIKE查询中的数据类型与列的数据类型匹配。类型不匹配可能导致隐式类型转换，这可能会影响查询性能和结果准确性。

1.    分析sql的执行过程

type

   system：表仅有一行，基本用不到；

   const：表最多一行数据配合，主键查询时触发较多；

   eq_ref：对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型；

   ref：对于每个来自于前面表的行组合，所有匹配索引值的行将从这张表中读取；

  range：只检索给定范围的行，使用一个索引来选择行。当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比较关键字列时，可以使用range；

   index：该联接类型与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小；

   all：全表扫描；

   性能排名：system > const > eq_ref > ref > range > index > all。

   实际sql优化中，至少要达到ref或range级别。

Extra常用关键字

   Using index：只从索引树中获取信息，而不需要回表查询；

   Using where：WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行，如果Extra值不为Using where并且表联接类型为ALL或index，查询可能会有一些错误。需要回表查询。

   Using temporary：mysql常建一个临时表来容纳结果，典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时。

11.  热数据表多表冗余水平拆分

如订单表，可以按周，按月水平拆分。按周按月水平拆分还可以做多表冗余，比如一周一个表的同时，一天再一个表，当天的查当天表，其它查周表。多表写入，天表隔天删除。

12.  delete时不要全量删除，加时间条件

降低写错SQL的代价：

加limit，删错也只是丢失部分数据，可以通过binlog日志快速恢复的。

SQL效率可能更高：

SQL中加了时间，时间又带上索引的话，删除效率也会更高。

避免长事务：delete执行时,MySQL会将所有相关的行加写锁和间隙锁，所有执行相关行会被锁住，如果删除数量大，会直接影响相关业务无法使用。数据量大的话，容易把CPU占满。

锁表：一次性删除太多数据，可能造成锁表，会有lock wait timeout exceed的错误，建议分批操作。

13.  高效批量插入数据

用如下形式：insert into 表（字段1）values('值1'),('值2'),('值3');

反例：

应改为：

效率能有上万倍的提升，数据量越大提升越明显。