sql优化方法及sql部分函数语法

一、sql性能优化

1、在表中建立索引，优先考虑where、group by使用到的字段；

2、尽量避免使用select *，返回无用的字段会降低查询效率，使用具体的字段代替*；

3、尽量避免使用in 和not in，会导致数据库引擎放弃索引进行全表扫描；

4、使用like的时候要注意是否会导致全表扫，有的时候会需要进行一些模糊查询例如：select id from table where username like ‘%hollis%’关键词%hollis%，由于hollis前面用到了“%”，因此该查询会使用全表扫描，除非必要，否则不要在关键词前加%；

5、尽量避免使用!=或<>操作符。在where语句中使用!=或<>，引擎将放弃使用索引而进行全表扫描。

注意：索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率、并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引。

 二、sql函数语法

注意：以下可能仅适用于mysql或postgresql

1.清空表并重置id从1开始

truncate table table_name

2.去重

distinct、group by

3.查看建表语句

show create table table_name

4.复制表

create  table  new_table_name  select  *  from  old_table_name

5.导出表数据

mysqldump -u用户名 -p 密码 数据库名 表名> 导出的文件名

6.导出数据库数据

mysqldump -u用户名 -p密码 数据库名 > 导出的文件名

7.创建视图

create view vw_info1 as select id, phone from info;

create view vw_info2(user_id, user_phone) as select id, phone from info;

这两个视图没有太大的区别，唯一的区别就是vw_info2可以自定义字段的名称。

7.pgsql的一些函数用法

（1）截取字符串：

         substring()  参数可根据长度截取，正则截取等

（2）转换数据类型

        cast(timestrap as character varying(256))

        CAST( value AS type )

value	描述
DATE	日期，格式为 'YYYY-MM-DD'.
DATETIME	日期加具体的时间，格式为 'YYYY-MM-DD HH:MM:SS'.
TIME	时间，格式为 'HH:MM:SS'.
CHAR	字符型
SIGNED	int
UNSIGNED	无符号int
BINARY	二进制型
DECIMAL	float型

（3）取值

       coalesce(1,2)

8.mysql的模糊查询，针对存储的时数字逗号分隔的字符串很好用

    MySQL手册中find_in_set函数的语法解释：

  FIND_IN_SET(str,strlist)

    str 要查询的字符串

    strlist 字段名 参数以”,”分隔 如 (1,2,6,8,10,22)

    eg: FIND_IN_SET(1,ids)  ids:"1,2,11,12"  此时只会匹配1，不会匹配11，12

    查询字段(strlist)中包含(str)的结果，返回结果为null或记录

    假如字符串str在由N个子链组成的字符串列表strlist 中，则返回值的范围在 1 到 N 之间。 一个字符串列表就是一个由一些被 ‘,' 符号分开的子链组成的字符串。如果第一个参数是一个常数字符串，而第二个是type SET列，则FIND_IN_SET() 函数被优化，使用比特计算。 如果str不在strlist 或strlist 为空字符串，则返回值为 0 。如任意一个参数为NULL，则返回值为 NULL。这个函数在第一个参数包含一个逗号(‘,')时将无法正常运行。

9. 将数字明确地转化为字符串，可使用 CAST()或 CONCAT()函数，CAST() 比较可取。

mysql> SELECT 38.8, CAST(38.8 AS CHAR);

        -> 38.8, '38.8'

mysql> SELECT 38.8, CONCAT(38.8);

        -> 38.8, '38.8'

10. 创建视图（https://blog.csdn.net/chengjianghao/article/details/86477207）

      作用：提高了重用性，就像一个函数。如果要频繁获取user的name和goods的name

                 对数据库重构，却不影响程序的运行。假如因为某种需求，需要将user拆房表usera和表userb

                提高了安全性能。可以对不同的用户，设定不同的视图。例如：某用户只能获取user表的name和age数据，不能获取sex数据。

create view other as select au.roleType, ar.role_name  from user as au, roles as ar where au.roleType=ar.id and ar.is_enable=1 and au.isDelete=0;

自身实践与参考多方资料整理，持续更新记录中......