mysql 写入 细节_MySQL使用细节

************************************************************************

MySQL使用细节，包括部分常用函数以及注意如何提高数据库查询性能

************************************************************************

1、union (union all)：将子查询得到的结果集合合并

SELECT pb.PRO_BATCH_ID FROM MD_PRO_BATCH pb WHERE pb.PRO_BATCH_ID = #{proBatchId}

UNION ALL SELECT p.PRO_BATCH_ID FROM MD_PARTITION p WHERE p.PRO_BATCH_ID = #{proBatchId}

UNION ALL SELECT pm.PRO_BATCH_ID FROM MD_PRE_MAINTAIN pm WHERE pm.PRO_BATCH_ID = #{proBatchId}

2、ifNull(字段名，默认值)：判断字段是否为空，如果为空则返回默认值

IFNULL(t.PQI_UP_RATIO,999999999) > #{pqiRatio}

3、查询插入

insertinto md_large_measure_detail selectreplace(UUID(),'-',‘’),#{accountId},CORP_ID,ROW_INDEX,from md_large_measure_detail v where

v.large_measure_id in (select large_measure_id from md_large_measure where account_id='systemacountid00000000')

4、逻辑判断case when ...then...else... end，控制返回值

case

when age > 18 and age <= 50 then 'youngMan'

when age > 50 then 'oldMan'

else 'child'

end as type

5、exists(查询表达式)是否存在

SELECT

*

FROMmd_net_index aWHERE

EXISTS(SELECT

count(b.REMARK)FROMmd_net_batch bWHEREb.net_batch_id=a.NET_BATCH_ID

)and NET_INDEX_ID = '0c5e94c468a4489f8507ac70a1f4169e'

6、locate(String,subString,position)：类似java中String.indexOf()方法，求子字符串subString在字符串String中的索引位置，position可选：从第position位置开始查找

locate('helloWorld','o') = 5;

locate('helloWorld','o',6) = 7;

7、整数：ceil向上取整，floor向下取整

ceil(1.2) = 2;floor(1.2) = 1;

8、添加空格space(n)：添加n个空格

SELECT CONCAT(USER_NAME,SPACE(5),LOGIN_ID) USER_NAME from vc_user

9、善于使用explain查看sql执行计划，方便优化脚本

使用explain时会有这些属性

id select_type table type possible_keys key key_len ref Extra

10、sql中的in包含的值不宜过多

11、select务必指明字段

12、查询数据时避免全表扫描，首先应该在where和order by使用索引

13、尽量避免在where子句查询使用null判断，否则将导致查询放弃使用索引而进行全表扫描

select name from user where age is null;

select name from user where age = 0;

14、尽量避免在where子句中使用 != 或者 <> 操作符，否则将导致放弃索引而扫描全表。

15、应尽量避免在where使用or连接条件，如果其中一个条件不是使用索引字段，查询则会放弃其他索引字段而扫描全表

select id from user where age = 18 or name = 'zhangsan';

select id from user where age = 18

union all

select id from user where name = 'zhangsan';

16、对于in和not in也要慎重使用，否则也会将会导致全表扫描

select id from user where age in (18,19,20);

select id from user where age between 18 and 20;

select id from user where age in (select age from user)

select id from user a where exists (select id from user b where b.age = a.age);

17、模糊查询也会引起放弃索引

select id from user where name like '%adb%';

select id from user where name like 'abc%';

18、尽量避免在where子句查询时对字段进行表达式操作，否则将会放弃索引而进行全表扫描

select id from user where age + 2 = 18;

select id from user where age = 18 - 2;

19、尽量避免在where子句中使用函数进行操作，否则将会导致查询放弃索引从而全表查询

select id from user substring(name,1,3) = 'abc';

select id from user where name like 'abc%);

20、在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

21、不要在where子句中“=”左边进行表达式操作，否则导致无法正确使用索引。

22、使用update时，如果只更新1、2个字段就不要更新全部字段，频繁的调用会导致性能下降。

23、索引不是越多越好，索引虽然可以提高查询效率，但是不当的索引反而会影响性能，当进行insert和update时，索引需要对数据进行维护，过多的索引因此会降低数据库性能，所以建索引需要视情况而定