Mysql基础知识之数据库SQL开发规范
建议使用预编译语句进行数据库操作
mysql> PREPARE stmt FROM 'SELECT SQRT(POW(, 2)+ Pow(, 2)As hypotenuse mysql> SET @a= 3: T@b=4 mysql> EXECUTE stmt USING @a, @b: mysql> DEALLOCATE PREPARE stmt
建议使用预编译语句进行数据库操作的好处
只传参数,比传递SQL语句更高效
相同语句可以一次解析,多次使用,提高处理效率
防范sql注入
避免数据类型的隐式转换
隐式转换会导致索引失效,例如下面案例id本为整数型,sql语句为字符串类型,这种就会触发隐式转换
select name, phone from customerwhere id =’111′
合理利用存在索引,而不是盲目增加索引
避免使用双%号的查询条件,后缀%不影响索引的使用。如 a like ‘%123%’
一个SQL只能利用到复合索引中的一列进行范围查询
使用 left join或 not exists来优化 not in操作
数据库设计时,应该要对以后扩展进行考虑
程序连接不同的数据库使用不同的账号,禁止跨库查询
为数据库迁移和分库分表留出余地
降低业务耦合度
避免权限过大而产生的安全风险
禁止使用 SELECT*必须使用 SELECT<字段列表>查询
消耗更多的CPU和O以及网络带宽资源
无法使用覆盖索引
可减少表结构变更带来的影响
禁止使用不含字段列表的 INSERT语句
错误示例最好不要这样插入数据 insert into t values (‘a’,’b’,’c’ )
正确示例,建议这样插入数据操作 insert into t (c1, c2, c3) values (‘a’,’b’,’c’ )
可减少表结构变更带来的影响
避免使用子查询,可以把子查询优化为join操作(关联查询)
子查询的结果集无法使用索引
子查询会产生临时表操作,如果子查询数据量大则严重影响效率
消耗过多的cPU及io资源导致慢查询增多
避免使用JoN关联太多的表
每Join一个表会多占用一部分内存( join_buffer_size)
会产生临时表操作,影响查询效率
MySQL最多允许关联61个表建议不超过5个
减少同数据库的交互次数
数据库更适合处理批量操作,例如查询取出一条数据与查询取出100条数据对于MySQL所消耗的性能是查不多的
合并多个相同的操作到一起,可以提高处理效率,alter table t1 add column c1 int, change column c2 c2 int
使用in代替or
in的值不要超过500个
in操作可以有效的利用索引
禁止使用 order by rand() 进行随机排序
会把表中所有符合条件的数据装载到内存中进行排序
会消耗大量的cPU和及内存资源
推荐在程序中获取一个随机值,然后从数据库中获取数据的方式
WHERE从句中禁止对列进行函数转换和计算
对列进行函数转换或计算会导致无法使用索引
错误示例 where date( createtime)=20160901
正确示例 where createtime >=20160901 and createtime< 20160902
在明显不会有重复值时使用 UNION ALL 而不是 UNION
UNION会把所有数据放到临时表中后再进行去重操作
UNION ALL不会再对结果集进行去重操作
拆分复杂的大SQL为多个小SQL,大SQL是指逻辑比较复杂比较占用大量cpu进行计算的SQL
MySQL一个SQL只能使用一个CPU进行计算
SQL拆分后可以通过并行执行来提高处理效率