数据库相关知识点总结

1、 基本语句

删除数据表中数据

truncate table student;

2、 常见优化方案

2.1 业务代码中，需要根据一个或多个条件，查询是否存在记录，不关心有多少条记录。

SQL不再使用count，而是改用LIMIT 1，让数据库查询时遇到一条就返回，不要再继续查找还有多少条了, 业务代码中直接判断是否非空即可。

##### SQL写法:
SELECT 1 FROM table WHERE a = 1 AND b = 2 LIMIT 1

##### Java写法:
Integer exist = xxDao.existXxxxByXxx(params);
if ( exist != NULL ) {
    //当存在时，执行这里的代码
} else {
    //当不存在时，执行这里的代码
}

2.2 Group By 优化

（1） 使用场景：当我们交友平台在线上运行一段时间后，为了给平台用户在搜索好友时，在搜索结果中推荐并置顶他感兴趣的好友，这时候，我们会对用户的行为做数据分析，根据分析结果给他推荐其感兴趣的好友。
有一张用户浏览好友记录的明细表t_user_view，该表的表结构如下：

CREATE TABLE `t_user_view` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `user_id` bigint(20) DEFAULT NULL COMMENT '用户id',
  `viewed_user_id` bigint(20) DEFAULT NULL COMMENT '被查看用户id',
  `viewed_user_sex` tinyint(1) DEFAULT NULL COMMENT '被查看用户性别',
  `viewed_user_age` int(5) DEFAULT NULL COMMENT '被查看用户年龄',
  `create_time` datetime(3) DEFAULT CURRENT_TIMESTAMP(3),
  `update_time` datetime(3) DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_user_viewed_user` (`user_id`,`viewed_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

最简单的SQL分析法：对用户过去查看好友的性别和年龄进行统计，按照年龄进行分组得到统计结果。依据该结果，给用户推荐计数最高的某个性别及年龄的好友。

SELECT viewed_user_age as age, count(*) as num FROM t_user_view WHERE user_id = 1 AND viewed_user_age BETWEEN 18 AND 22 AND viewed_user_sex = 1 GROUP BY viewed_user_age

在Extra这一列中出现了三个Using，这3个Using代表了《导读》中的groupBy语句分别经历了3个执行阶段：

Using where：通过搜索可能的idx_user_viewed_user索引树定位到满足部分条件的viewed_user_id，然后，回表继续查找满足其他条件的记录
Using temporary：使用临时表暂存待groupBy分组及统计字段信息
Using filesort：使用sort_buffer对分组字段进行排序

优化方案：新增索引，避免临时表对分组字段的统计，及sort_buffer对分组和统计字段排序。

当然，如果实在无法避免使用临时表，那么，尽量调大tmp_table_size，避免使用磁盘临时表统计分组字段。

ALTER TABLE `t_user_view` ADD INDEX `idx_user_age_sex` (`user_id`, `viewed_user_age`, `viewed_user_sex`);

2.3 Update

(1) 在一条UPDATE语句中，如果要更新多个字段，字段间不能使用“AND”，而应该用逗号分隔。

2.4 反斜杠 \

（1） 在mysql的like语法中，like后边的字符串除了会在语法解析时转义一次外，还会在正则匹配时进行第二次的转义。因此如果期望最终匹配到""，就要反转义两次，也就是由"\\“到”\“再到”"。
如果是普通的精确查询（=），则无需第二次的正则转义，和INSERT语句一样。
（2） MySql中escape使用
mysql中通配符’ _ ‘与’ % '分别代表任意一个字符和任意多个字符

select * from user where user_name like '/_hello' escape '/'; # 声明/之后的字符不做为通配符使用

参考链接：https://www.cnblogs.com/leizia/p/14327958.html

2.5 尽量避免使用 IN 和 NOT IN

如果是确定且有限的集合时，可以使用。如 IN （0，1，2）。
NULL不等于任何非空的值
1、用 EXISTS 或 NOT EXISTS 代替

select *  from test1 
   where EXISTS (select * from test2  where id2 = id1 )

select *  FROM test1  
 where NOT EXISTS (select * from test2  where id2 = id1 )

2、用JOIN 代替

 select id1 from test1 
   INNER JOIN test2 ON id2 = id1 
   
 select id1 from test1 
   LEFT JOIN test2 ON id2 = id1 
   where id2 IS NULL

参考链接：https://mp.weixin.qq.com/s/FYzZHz3Ftx8bhyAXY2avIQ