函数补充、用户自定义函数、索引、一些细枝末节

函数 - 封装经常使用且相对独立的功能

一、获取帮助

- help 'functions'; / ? functions;
- help 'window functions'; / ? window functions;
- help 'first_value'; / ? first_value;

二、常用函数

1.聚合函数

~ 描述性统计信息 - sum() / avg() / count() / max() / min() / var_pop() / var_samp() / stddev_pop() / stddev_samp()
~ 其他 - group_concat() / json_arrayagg() / json_objectagg()

2.数值函数

~ 绝对值和符号函数 - abs() / sign()
~ 取整和四舍五入 - ceiling() / floor() / round() / truncate()
~ 三角和反三角函数 - sin() / cos() / tan() / cot() / asin() / acos() / atan()
~ 指数和对数 - pow() / exp() / log() / log10() / log2()
~ 随机数 - rand()
~ 弧度和角度 - degrees() / radians()
~ 平方根 - sqrt()

3.字符串函数

~ 长度 - char_length() / length()
~ 拼接 - concat()
~ 大小写 - lower() / upper()
~ 取子串 - left() / right() / substring()
~ 替换 - replace()
~ 修剪 - trim() / ltrim() / rtrim()
~ 定位 - instr() / locate()
~ 填充 - lpad() / rpad()
~ BASE64编解码 - from_base64() / to_base64()
~ 反转 - reverse()

4.日期时间函数

~ 获取日期和时间 - current_timestamp() / current_date() / current_time()
~ 获取部分信息 - date() / time() / year() / quarter() / month() / day() / weekday() / hour() / minute() / second()
~ 计算时间差 - datediff() / timestampdiff()
~ 添加间隔 - date_add() / date_sub()
~ 格式化日期时间 - date_format()

date_format的补充

%a	    缩写星期名
%b	    缩写月名
%c	    月，数值
%D	    带有英文前缀的月中的天
%d	    月的天，数值(00-31)
%e	    月的天，数值(0-31)
%f	    微秒
%H	    小时 (00-23)
%h	    小时 (01-12)
%I	    小时 (01-12)
%i	    分钟，数值(00-59)
%j	    年的天 (001-366)
%k	    小时 (0-23)
%l	    小时 (1-12)
%M	    月名
%m	    月，数值(00-12)
%p	    AM 或 PM
%r	    时间，12-小时（hh:mm:ss AM 或 PM）
%S	    秒(00-59)
%s	    秒(00-59)
%T	    时间, 24-小时 (hh:mm:ss)
%U	    周 (00-53) 星期日是一周的第一天
%u	    周 (00-53) 星期一是一周的第一天
%V	    周 (01-53) 星期日是一周的第一天，与 %X 使用
%v	    周 (01-53) 星期一是一周的第一天，与 %x 使用
%W	    星期名
%w	    周的天 （0=星期日, 6=星期六）
%X	    年，其中的星期日是周的第一天，4 位，与 %V 使用
%x	    年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y	    年，4 位
%y	    年，2 位

5.流程控制函数

~ if() / ifnull() / nullif()

6.窗口函数

~ 排序 - rank() / dense_rank() / row_number() / percent_rank()
~ 取数 - lag() / lead() / first_value() / last_value() / nth_value()

7.其他函数

~ 类型转换 - cast() / convert()
~ 信息获取 - current_role() / current_user() / database() / last_insert_id() / version()
~ 全局唯一标识符 - uuid() / uuid_to_bin() / bin_to_uuid()

三、用户自定义函数（UDF）

1.学院介绍长度如果超过50个字符就截断并添加省略号，没超过50个字符就完整返回

-- 创建函数
delimiter $$
create function trancate_string(original_string longtext, max_length int unsigned)
returns longtext deterministic
begin
    if char_length(original_string) > max_length then
        return concat(left(original_string, max_length), '……');
    else
        return original_string;
    end if;
end $$
delimiter ;

-- 使用函数
select col_id,
       col_name,
       trancate_string(col_intro, 10)
from colleges;

2.保护用户的名字

-- 创建函数
delimiter $$
create function trancate_name(original_string longtext)
returns longtext deterministic
begin
    if char_length(original_string) > 2 then
        return concat(
               left(original_string, 1),
               repeat('*', char_length(original_string) - 2),
               right(original_string, 1));
    else
        return concat(left(original_string, 1), '*');
    end if;
end $$
delimiter ;

-- 使用函数
select stu_id, trancate_name(stu_name)
from students;

四、索引（类似于书的目录加速查询）

1.执行计划 - explain

- select_type
- table
- type - 查询类型
    ~ const - 常量级查询 - 性能最好
    ~ eq_ref - 唯一索引扫描 - 性能很好
    ~ ref - 非唯一索引扫描 - 性能比较好
    ~ range - 索引范围扫描 - 性能还行
    ~ index - 索引全扫描 - 性能很差
    ~ ALL - 全表扫描 - 性能最差
- possible_keys - 可能用到的索引
- key - 实际用到的索引
- key_len - 索引的长度
- rows - 预估扫描的行数 - 行数越少查询效率越高
- extra - 额外的信息

2.索引相关概念 - InnoDB - B+树

- 聚集索引 - 索引组织表 - 数据就根据主键排序放在索引中 - 只有一个
- 非聚集索引 - 二级索引 - 额外创建一棵树来加速数据检索 - 回表
- 前缀索引/复合索引
- 索引覆盖查询和回表
    ~ index covered query - 要投影的字段已经被索引覆盖到，此时不需要回表，性能非常好
    ~ 回表 - 投影的字段没有被索引完全覆盖到，需要重新通过主键定位到数据，性能会受到影响
- 函数索引
- 不可见索引
    ~ alter table table_name alter index index_name invisible;
- 索引设计要点 - 避免查询的时候索引失效了
    ~ 在作为查询条件的字段上建索引（覆盖到where子句）
    ~ 避免额外排序（索引覆盖到order by子句的字段）
    ~ 避免回表（让索引覆盖到查询要投影的列 - 复合索引）
    ~ 索引不是越多越好 - 索引加速了查询但是让增删改变得更慢 - 索引更新问题

五、细枝末节

1.变量

- 系统变量 - transaction_isolation / max_connections
- 用户变量 - @v_name
- 局部变量 - declare

2.数据备份

- 导出数据
    ~ 导出到文本文件 - select ... into outfile '...';
    ~ Python脚本代码 - CSV / Excel
    ~ 图形化客户端工具
- 导入数据
    ~ load data infile ...;
    ~ Python脚本代码 - CSV / Excel
    ~ 图形化客户端工具
- 导出SQL
    ~ mysqldump -u root -p school > school1.sql
    ~ mysqldump -u root -p --databases school > school2.sql
- 导入SQL
    ~ mysql -u root -p school < school1.sql
    ~ mysql -u root -p < school2.sql
- 停机冷备份 - 停机 - 复制数据文件
- 联机热备份
    ~ Percona XtraBackup
    ~ MySQL Enterprise Backup

3.临时表

- create temporary table ...;

4.虚拟列和计算列

- alter table tb_emp add column ann_sal int as (sal * 14 + comm * 12);
- alter table tb_emp add column ann_sal int generated always as (sal * 14 + comm * 12) stored;
- alter table tb_emp add column ann_sal int generated always as (sal * 14 + comm * 12) virtual;

5.触发器（trigger） - 千万不要用

- 可能存在行级锁升级为表锁的风险，严重影响性能（难道不担心锁表的风险吗）
- 可能存在诸多隐式操作，在不知情的情况下修改数据或者提交回滚事务，维护成本高

6.表分区 - 加速查询

- RANGE分区
- LIST分区
- HASH分区

7.主从复制 - 读写分离

8.分表分库 - 数据切片 - 数据库中间件 - Vitess / ShardingSphere / Cobar / Mycat / Gaea

9.JOIN连接算法

- 嵌套循环连接 - nested loop join - OLTP业务 - （驱动表）数据量小，语句简单，存在索引
    ~ 驱动表：左外连接时，左表是驱动表；右外连接时，右表是驱动表；内连接时，哪个表的数据更少，哪个表就是驱动表，可以通过explain进行查看
- 哈希连接 - hash join - OLAP业务 - 数据量大，关联表多，语句复杂，连接条件无索引

- OLTP - online transaction processing - 业务查询（开发） - 效率高、延迟低
- OLAP - online analysis processing - 分析查询（数据分析师）- 允许高延迟

10.CTE - Common Table Expression - 公共表表达式

- 使用场景 - 需要在同一查询中多次引用相同的子查询的结果
- 具体语法
    with cte_name (column1, column2, ...) as (
        select ... from ...
    )
    select ...
    from cte_name
    where ...;
- 递归查询
    with recursive Q as (
        select eno, ename, mgr 
          from tb_emp where eno = 7800
         union
        select P.eno, P.ename, P.mgr
          from tb_emp as P inner join Q on P.mgr = Q.eno 
    )
    select * from Q;

11.SQL注射攻击 - SQL Injection

12.范式理论和反范式设计

- 范式级别（规范程度）越高，数据冗余越低，数据操作异常越少
- 1NF - 列不可分割（单值列）- JSON类型显然是违背了1NF的设计
- 2NF - 满足1NF + 非主键列必须完全依赖于主键列
- 3NF - 满足2NF + 消除传递依赖
- 4NF - 满足3NF + 消除非平凡多值依赖
- 反范式 - 违反范式理论 - 增加冗余数据或违反范式规则来达到以下目标
    ~ 提高查询性能
    ~ 简化数据操作
    ~ 空间换取时间

13.建表必备的三个字段 - xxx_id / xxx_created / xxx_updated

14.慢查询日志

- show variables like '%slow_query%';
- set global slow_query_log = on;
- set global long_query_time = 3;

15.insert相关

- 如果有多个连接向同一个表插入数据，使用insert delayed可以获得更好的性能
- 将查询结果插入到表中 - insert into table_name ... select ...;

16.SQL查询提示

- use index - 建议使用指定的索引
- ignore index - 建议忽略掉指定的索引
- force index - 强制使用指定的索引