MySQL数据库SQL开发规范

MySQL数据库SQL开发规范

1、范围

2、规范性引用文件

3、术语和定义

3.1.索引策略

3.1.1 独立的列

• 独立列 是指索引列不能是表达式的一部分，也不能是函数的参数。 尽可能简化where条件，始终将搜索列单独放置在比较符号一侧：
  如 :

mysql> select id from ipdata where id + 1 = 5; --无法使用索引列

mysql> select ... where TO_DAYS(CURRENT_DATE) - TO_DAY(CREATE_DATE)<=10; --无法使用索引

正确写法：

mysql> select id,value from tab where gmt_created >=DATA_SUB(now(),interval 10 day );

3.1.2 禁止重复索引

primary key ID;uniq index ID;重复索引增加维护负担、占用磁盘空间,同时没有任何益处。

3.1.3 不在低基数列上建立索引,例如"性别"

通常情况下,对于低基数列上建立索引的精确查找,相对于不建立索引的全表扫描没有任何优势,而且增大了IO负担。

3.1.4 索引选择性

selectivity = distinct Values / total Rows

索引的选择性是指，不重复的索引值和数据表的记录总数的比值。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。

唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

3.1.5 前缀索引

对于BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。

3.1.6 多列索引

为每个列创建独立的索引，或者按照错误的顺序创建多列索引。

在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能。对于下面的查询where条件，这两个单列索引都是不好的选择：

select film_id, actor_id from table1 where actor_id=1 or film_id=1;

在老的MySQL版本中，MySQL会对这个查询使用全表扫描。除非改写成两个查询UNION的方式。

select film_id, actor_id from table1 where actor_id=1

union all

select film_id, actor_id from table1 where film_id=1 andactor_id<>1;

MySQL5.0和更新的版本引入了一种叫“索引合并”的策略，查询能够同时使用这两个单列索引进行扫描，并将结果合并。这种算法有三个变种：OR条件的联合（union），AND条件的相交（intersection），组合前两种情况的联合及相交。索引合并策略有时候是一种优化的结果，但实际上更多时候说明了表上的索引建得很糟糕：

（1）当出现服务器对多个索引做相交操作时（多个AND条件），通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。

（2）当出现服务器对多个索引做联合操作时（多个OR条件），通常需要耗费大量的CPU和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高，需要合并扫描返回的大量数据的时候。

（3）如果在explain中看到有索引合并，应该好好检查一下查询和表的结构，看是不是已经是最优的。

3.1.7 覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，就称之为“覆盖索引”。MySQL利用索引返回select列表中的字段，而不必根据索引再次回表读取数据页。

  select  id,status from tab where id=2

  alter table add keyind_t_id_status (id,status);

不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以MySQL只能用B-Tree索引做覆盖索引。另外，不同的存储引擎实现覆盖索引的方式也不同，而且不是所有的引擎都支持覆盖索引。

3.1.8 组合索引

和覆盖索引类似对查询语句中多个常用字段建立索引，当然，创建组合索引并不是说就须要将查询条件中的所有字段都放在一个索引中，还应该尽量让一个索引被多个 Query 语句利用，尽量减少同一个表上的索引数量，减少因为数据更新带来的索引更新成本，同时还可以减少因为索引所消耗的存储空间。

3.1.7 尽量避免NULL

（1）尽可能把字段定义为NOT NULL,可以放置一个默认值,如’’,0等。

（2）MySQL 难以优化NULL列。NULL列会使索引统计和值更加复杂。

（3）NULL列需要更多的存储空间，还需要在MYSQL内部进行特殊处理。

（4）NULL列加索引，每条记录都需要一个额外的字节，还导致MyISAM中固定大小的索引变成可变大小的索引。

4、SQL编写总则

4.1 SQL语句应正确、规范、高效和最优

4.2 同一项目的SQL书写格式应统一

4.3 应尽最大可能避免非常复杂的SQL

4.4 查询条件中的条件，都需要使用绑定变量来实现

SQL查询的where条件中的变更都需要便宜绑定变量来实现，禁止接受任何外部传入内容

对于不变的常量条件，请使用常量，不使用变量。

4.5 应确保变量和参数的类型和大小写与数据库中表所对应的数据列相匹配

4.6 使用SELECT时，应指出列名，尽量不要使用列的序号或用“*”代替所有的列名

在不必要的查询中使用“*“，且需要GROUP BY 或ORDER BY 的时候，禁止使用select * 一次取出所有的字段。对于表连接的Join语句，禁止使用select * 来进行查询，除非明确获得DBA允许。含有text字段的表，当不南大要取出text字段时，也禁止使用select * 进行查询。

1）进行group by 或order by的时间不允许例用select * 是为了确保MySQL能够使用最新的优化排序算法

2）JOIN语句不允许使用select *是为了防止仅仅只需要索引即可完成的查询需要回表取数

3）存在TEXT字段表，在不需要取出TEXT字段的时候，不允许使用SELECT * ,因为text字段是存储在和普通记录不一样的物理位置，会造成大量的io操作。

4）避免因增删字段而没有修改相关SQL及相关代码导致程序BUG

4.7 使用INSERT时，应指定插入的字段名称，不应不指定字段名直接插入VALUES.

4.8 使用SQL语名连接多表时，应使用表的别名来引用列。

    SELECT 

        EL.NAME,

        DP.PT_NAME

    FROM

    EMPLOYEE EL,DEPT DP

    WHERE

    EL.DEPT_ID=DP.ID;

4.9 SQL语句应避免对大表的全表扫描，对大表的操作应尽量使用索引

4.10 SQL语句应避免不必要的排序

4.11 避免多个范围条件索引

4.12 使SQL查询结果应尽量缓存

    --查询缓存不开启

    SELECT USERNAME FROM USER WHERESIGNUP_DATE >= CURDATE();

    --查询缓存开启

    $TOADY=date('Y-m-d')

    SELECT USERNAME FROM USER WHERESIGNUP_DATE >= '$TODAY';

4.13 在含有子查询的SQL语句中应减少对表的查询

4.14 SQL中尽可能避免多表联合复杂查询

4.15 应将SQL语句中的数据库函数、计算表达式等尽量放置在等号的右边-----

    WHERE语句中如果索引是函数的一部分，优化器将不会使用索引而使用全表扫描：

    SELECT …… FROM DEPT WHERE SALARY* 12 >100000;  --无法使用索引

    SELECT …… FROM DEPT WHERESALARY>100000/12; --可能正常使用索引

4.16 按业务需要使用事务，应保持事务简短，避免大事务

4.17 尽量不用>、<操作，用>=，<=代替

4.18 慎用Distinct，能少用就少用

4.19 Union尽量少用，尽量使用union all

4.20 join表时，尽量使用相同的列类型，并将其索引

4.21 当使用通配符“%”或“_”作为查询字符串的第一个字符时，索引将不会被使用

    SELECT ID FROM MEMBER WHEREREALNAME LIKE '%Kervin'; --无法使用索引

    SELECT ID FROM MEMBER WHEREREALNAME LIKE '_Kervin'; --无法使用索引

 

    SELECT ID FROM MEMBER WHEREREALNAME LIKE 'Kervin%'; --使用索引

    SELECT ID FROM MEMBER WHEREREALNAME LIKE 'Kervin_'; --使用索引

4.22 使用where子句代替having

4.23 不在索引列上进行数学运算或函数运算

禁止在where条件出现的过滤字段上使用任何函数类型或格式转换，正确的做法是把传入比较的值转换为列类型所需要的。

错误写法

select username from member where date_formate(login_time,'%Y-%m-%d')='2014-12-15'

正确定法

select username from member wherelogin_time=date_formate('2014-12-15','%Y-%m-%d')

4.24 杜绝任何隐式类型转换

在所有SQL中，where条件中必须使用和过滤字段完全一致的数据类型，杜绝任何隐式类型转换，避免造成因为数据类型不匹配而导致执行计划出错，造成性能问题。

建议所有时间类型字段在iBatis中均以时间类型传入，或以字符串传入然后通过时间函数转换字符串为合法的时间格式。

4.25 表连接规范

基本原则：

所有非外连接SQL(即inner join)，请把关联表统一写到from字句中，关联条件与过滤条件统一写到where字句中。

出于代码可读性原因，所有外连接SQL语句中，请一律使用left join，禁止使用right join.

4.30 分页查询规范

基本原则：

    分页查询语句全部都需要带有排序条件，除非明确要求不使用任何排序来随机展示数据。

多表join的分页语句，如果过滤条件在单个表上，需要先分页再join(前提条件是关联表之间的记录是一一对应关系，否则可能会返回的记录数目少于或多于page offset的值。)