一、数据类型转换规范

1、基本原则

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

2、详细说明

所有Where条件的字段上不允许使用函数做类型转换,如有需要转换类型,只能转换数值或变量代入值,而不是转换字段。 最为常见的隐式类型转换常见于时间类型与字符串类型之间,建议所有时间类型字段均以时间类型传入,或者以字符串传入然后通过字符串转换时间函数进行转换,如下: 

select * from member@b@where member_create =str_to_date('20150701 01:02:03','%Y%m%d %H:%i:%s');

在表连接Query中,如果连接条件两端的数据类型不一致,必须保证将驱动表的连接条件数据类型转换为与被驱动表一致的数据类型。

select * from test_info a,test_customer b@b@where a.order_id =cast(b.order_id as unsigned int) and a.pay_type= 'cash ';

二、SELECT * 使用规范

1、 基本原则 - 严禁使用select *进行查询

2、详细说明

MySQL数据库进行Order By操作有两种算法：@b@第一种是直接取出所有需要返回字段(select后面的字段),存入内存中,然后排序(仅有需要排序的字段需要参与)。@b@第二种是先取出需要排序的字段,然后排序,再回表中取出其他的字段,就相当于所有数据都有两次磁盘IO。表现在MySQL执行计划中为FILESORT。@b@如果select后面的字段长度总和超过1024字节（即参数max_length_for_sort_data的默认值）或者字段中包括BLOB、TEXT字段，都将会使用第二种算法。所以order by的查询不允许使用select *。@b@join语句使用select *可能导致只需要访问索引即可完成的查询需要回表取数，所以禁止使用。

mysql> explain SELECT t1.a,t2.a FROM t1 LEFT JOIN t2?ON t1.a =t2.a;@b@+----+-------------+-------+-------+---------------+-----------+---------+--------------+------+-------------+@b@| id | select_type | table | type?| possible_keys | key | key_len | ref | rows | Extra |@b@+----+-------------+-------+-------+---------------+-----------+---------+--------------+------+-------------+@b@|1 | SIMPLE | t1 | index | NUL | idx_t1_ac | 66 | NUL | 5 |Using index|@b@|1 | SIMPLE | t2 | ref| idx_t2_a | idx_t2_a| 33 | test1.t1.a | 1 |Using index|@b@+----+-------------+-------+-------+---------------+-----------+---------+--------------+------+-------------+@b@@b@mysql> explain SELECT * FROM t1 LEFT JOIN t2 ON t1.a =t2.a;@b@+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------+@b@| id | select_type | table | type | possible_keys | key?| key_len | ref?| rows | Extra |@b@+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------+@b@|?1 | SIMPLE | t1| ALL?| NUL | NULL | NUL?| NULL |5 |NUL |@b@|?1 | SIMPLE | t2| ALL?| idx_t2_a | NULL | NUL?| NULL | 5 |Using where; Using join buffer (Block Nested Loop)|@b@+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------+

MySQL中的text类型字段和Oracle的clob一样,存储的时候不是和由其他普通字段类型的字段组成的记录存放在一起,而且读取效率本身也不如普通字段块。如果不需要取回text字段，又使用了select * ,会让完成相同功能的sql所消耗的io量大很多,而且增加部分的io效率也更低下。

三、字段函数使用规范

1、基本原则 - 在取出字段上可以使用相关函数（尽可能避免出现now(),rand(),sysdate(),current_user()等不确定结果的函数）,但是在Where条件中的过滤条件字段上严禁使用任何函数,包括数据类型转换函数。

2、详细说明 - 语句级(STATEMENT)复制场景下，引起主从数据不一致；不确定值的函数，产生的SQL语句无法利用QUERY CACHE。

错误的写法

Select member_create@b@from .. .@b@Where date_format(member_create, '%Y%m%d %H:%i:%s')= '20150701 00:00:0'

正确的写法

Select date_format(member_create, '%Y%m%d %H:%i:%s')@b@from .. .@b@Where member_create =str_to_date('20150701 00:00:00', '%Y%m%d %H:%i:s');

四、表连接规范

1、基本原则 - 所有外连接一律写成Left Join，而不要使用Right Join。 如无特殊需要，严禁开发人员使用STRAIGHT_JOIN。

2、详细说明

--错误的写法:@b@select  a.id,b.id  from b right join a where  a.id= b.a_id and ...@b@--正确的写法:@b@select a.id,b.id  from a left join b on  a.id = b.a_id where ...

五、子查询规范

1、基本原则 - 所有的半连接一律用in的写法，禁止使用exists的写法

2、详细说明

--比如说@b@select * from a where exists (select null from b where a.id = b.id)@b@@b@--应该改用如下写法：@b@select * from a where a.id in (select id from b);

六、随机取数规范

1、基本原则 - 严禁在MySQL数据库中使用RAND（）函数生成随机数，严禁开发人员直接使用ORDER BY RAND()取随机数，而应在应用中使用其他方法替换。ORDER BY rand()会将数据从磁盘中读取，进行排序，会消耗大量的IO和CPU。

2、详细说明 - 下面给出的是非必要写法。严禁直接在MYSQL中使用RAND()函数

--错误的写法：@b@Select id,name,age,sex@b@from member@b@order by rand() limit 10;@b@--正确的写法：@b@SELECT id,name,age,sex@b@FROM 'member'@b@WHERE id >= (SELECT floor(RAND() * (SELECT MAX(id) FROM 'member')))@b@ORDER BY id LIMIT 10;

其、其他规范

1、进行大批量操作时必须分批提交，每次数据量操作不能超过10W条。@b@2、WHERE条件中严禁在索引列上进行数学运算或函数运算。@b@3、用union all替换or/in。@b@4、严禁使用%前缀进行模糊前缀查询，可以使用%模糊后缀查询。如：select id,val from table where val like ‘name%’。@b@5、使用union all 代替union。(如果能接受重复数据的话)@b@6、Insert 语句必须指明字段名称，避免后期因为字段扩展，影响原有应用。@b@7、Insert语句使用bulk提交，values的个数不应该过多。bulk提交可以提高写效率，但个数过多，数据恢复需要时间过长。@b@8、拆分复杂的SQL为多个小SQL，避免大事务。@b@9、对同一表的多次alter操作必须合并为一次操作