Quantum Bit

关系型数据库基础语句及性能调优

6/6/2021 关系型数据库OracleMySQL索引SQL优化存储过程

# 1. Oracle数据库基础

# 1.1 Oracle基本概念

# 1.1.1 SQL语句分类

  • DQL（数据查询语言）select
  • DML（数据操作语言）insert、delete、update
  • DDL（数据定义语言）create、drop、alter
  • DCL（数据控制语言）grant、revoke
  • TCL（事务控制语言）commit、rollback

# 1.1.2 视图

视图概念及基本语句：

  • 虚表，是从一个或几个基本表（或视图）导出的表
  • 只存放视图的定义，不存放视图对应的数据
  • 基表中的数据发生变化，从视图中查询出的数据也随之改变
CREATE VIEW 视图名 AS SELECT 查询语句;  // 创建视图
ALTER VIEW 视图名 AS SELECT 查询语句;   // 修改视图
SELECT * FROM 视图名;                  // 查询视图
DROP VIEW 视图名;                      // 删除视图
1
2
3
4

# 1.1.3 主键和外键

主键（PK）：是表中的一个或多个字段，它的值唯一的标识表中的某一条记录（组成主键的列中不能包含空值null），一个表最多只能有一个主键。（唯一性、非空性）

外键（FK）：表示的是两个关系之间的相关联系。如果关系R中的某属性组不是R的主键，但确是另一个关系S的主键，则该属性组是关系R的外键（R和S可以是同一关系）。其中R叫做参照关系，S叫做被参照关系。外键的取值允许重复。（允许重复、允许空值）

注：联合主键

  • 数据库的每张表只能有一个主键，不可能有多个主键。
  • 所谓的一张表多个主键，我们称之为联合主键（就是用多个字段一起作为一张表的主键）。
  • 主键的作用是保证数据的唯一性和完整性，同时通过主键检索表能够增加检索速度。

# 1.2 Oracle增删查改

# 1.2.1 新增数据

//直接插入
insert into tablename(fieldname1, fieldname2) values(value1,value2);
//从其他表获取值插入，需列数和类型一致
insert into tablenamea(fieldname1, fieldname2) select fieldname3,fieldname4 from tablenameb;
//创建数据表时插入
create table tablenamea as select fieldname1，fieldname2 from tablenameb;
1
2
3
4
5
6

# 1.2.2 删除数据

//删除表中全部数据（不删除表结构），可回滚
delete from tablename;  
//删除表中符合条件的数据（不删除表结构），可回滚
delete from tablename where filedname expression value; 
//删除表中全部数据（不删除表结构），比delete快，不可回滚
truncate table tablename;  
//删除表中全部数据及表结构，不可回滚
drop table tablename;
1
2
3
4
5
6
7
8

# 1.2.3 查找数据

[1] 单表查询

select select_list
  from table_list
  [where] [where_clause]
  [and] [and_clause]
  [group_by] [group_by_clause]
  [having] [condition]
  [order_by] [order_by_clause]
1
2
3
4
5
6
7

[2] 连接查询

//交叉连接--得到两张表的笛卡尔积
select * from table_1 a, table_2 b;
//自连接--是连接表与自身的连接
select * from table_1 a, table_1 b
//内连接--返回来自源表中的相关的行，用关系运算符过滤（并不是加了inner join的才叫内连接）
eg：select * from hr.countries t where region_id=1
//外连接
left join…on  左外连接(左边的表不加限制)
right join…on 右外连接(右边的表不加限制)
full join…on  全外连接(左右两表都不加限制)
(+)写法：+表示补充，即哪个表有加号，这个表就是匹配表（eg：如果加号写在右表，左表就是全部显示，是左连接）
1
2
3
4
5
6
7
8
9
10
11

[3] 模糊查询

%表示任意个字符；_表示任意单个字符（有且仅有），通常用来限制字符串长度；[]表示其中的某一个字符；[^]表示除了其中的字符的所有字符

SELECT * FROM table_name WHERE column_1 LIKE '%三';
SELECT * FROM table_name WHERE column_1 LIKE '三%';
SELECT * FROM table_name WHERE column_1 LIKE '%三%';
SELECT * FROM table_name WHERE column_1 LIKE '_好_';      
SELECT * FROM table_name WHERE column_1 LIKE '[张李王]三';  //表示column_1中有匹配张三、李三、王三的都可以
SELECT * FROM table_name WHERE column_1 LIKE '[^张李王]三';  //表示column_1中有匹配除了张三、李三、王三的其他三都可以
1
2
3
4
5
6

[4] 常用语句

// 设置别名
select sysdate time from dual;  或  select sysdate as time from dual;
// 查询结果排序
order by fieldname desc|asc   （desc降序、asc升序）
// 关系操作符
<,>,<>,!=, <=,=, >=           （<>与!=都是不等于，完全等同）     
// 比较操作符
is null, between…and…, in,exists,like   （_代表一个字符，%代表多个字符）
// 逻辑操作符
and, or, not
// 分组
group by  一般和聚合函数一起使用才有意义
having  (给group by添加限制条件)    eg:group by age having count(*)>1
// 连接运算符
union  取并集，去重
union all 取并集，不去重
intersect 取交集
minus 用来返回前面查询减去后面查询的部分
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

# 1.2.4 修改数据

//修改满足条件的数据
update tablename set fieldname1 = value1 where fieldname2 = value2;
1
2

# 1.3 Oracle常用函数

# 1.3.1 数值型函数

函数 解释 输入 输出
abs(n) 绝对值 select abs(-100) from dual; 100
sign(n) 正负判断（正数返回1、0返回0、负数返回-1） select sign(-30) from dual; -1
mod(m,n) 取余（m除以n的余数， n为0返回m） select mod(7,3) from dual; 1
round(m,n) 四舍五入（m是操作数，n是四舍五入到第几位） select round(100.256,2) from dual; 100.26
trunc(m,n) 截断（m是操作数，n是截取到第几位） select trunc(100.256,2) from dual; 100.25
power(m, n) 指数函数（返回m的n次幂) select power(3,2) from dual; 9
exp(n) 指数函数（返回e的n次幂） select exp(2) from dual; 7.38905609893065
log(m,n） 对数函数（返回以m为底n的对数） select log(2,8) from dual; 3
ln(n) 对数函数（返回n的自然对数） select ln(exp(2)) from dual; 2
sqrt(n) 求n的算术平方根 select sqrt(4) from dual; 2

# 1.3.2 字符型函数

函数 解释 输入 输出
lower(string) 大写转小写 select lower('FUN')from dual; fun
upper(string) 小写转大写 select upper('fun')from dual; FUN
initcap(string) 首字母大写 select initcap('fun')from dual; Fun
trim(string)，Itrim(string)，rtrim(string) 去左右空格 select trim(' adams ')from dual; adams
to_number(string) 字符转数字 select to_number('123') from dual; 123
replace(string,oldstr,newstr) 替换字符串 select replace('jackandjue','j','bl') from dual; blackandblue
instr(string,m) 测试子串是否存在（存在输出为子串初次出现的位置，不存在返回0） select instr('worldwide','d') from dual; 5
|| 字符串连接 select 'hello'||'world' from dual; helloworld
substr(string,m,n) 截取子字符串（m代表从第几位开始取，n代表取几位） select substr('abcdefg',5,3) from dual; efg
length(string) 返回字符串的长度 select length('abc') from dual; 3
rpad(expr1,n,expr2) 在expr1右边用expr2填充，直到长度为 n select rpad('1',5,'0') from dual; 10000
lpad(expr1,n,expr2) 在expr1左边用expr2填充，直到长度为 n select lpad('1',5,'0') from dual; 00001
nvl(null,'0') 空值转换 select nvl(null,'0') from dual; 0
decode(input,value,result ,default_result) 它将输入数值与函数中的参数列表相比较，根据输入值返回一个对应值。 select decode(to_char(sysdate,'mm'),'01','1月','02','2月','03','3月','4-12月') from dual; 如果当前为1-3月返回当前月数，否则返回4-12月
to_char(wm_concat(column)) 是将查询结果列合并成一行，之间用逗号分隔显示 select to_char(wm_concat(name)) from hr.sys_code where code = 'LEVEL' 省级,地市级,区县级
sys_guid() 产生并返回一个全球唯一的标识符，经常用来做表的主键 select sys_guid() from dual BC28432D40E 16746E0531C 33010AB136
distinct column 记录去重 略 略
rownum 截取记录数 略 略

# 1.3.3 日期型函数

函数 解释 输入 输出
sysdate 系统的当前日期和时间，包括年月日和时分秒 select sysdate from dual; 2020/11/12 16:30:11
to_char (date,'format') 日期按照format格式转为字符串 select to_char(sysdate,'yyyymmddhh24miss') from dual; 20201112152348
to_date (string，'format') 字符串按照format格式转为日期 select to_date(20201112152348,'yyyymmddhh24miss') from dual; 2020/11/12 15:23:48
months_between(date1, date2) 返回给定日期之间的月数（date1：结束时间，date2：开始时间） select months_between(to_date('202011','yyyymm'), to_date('202007',' yyyymm')) from dual; 4
add_months(date, integer) 日期加指定月 select add_months(sysdate,2) from dual; 2021/1/12 16:00:28
last_day(date) 返回某月最后一天 select last_day(sysdate) from dual; 2020/11/30 16:04:30
next_day(date, int) 返回一周后的某天（int为星期几） select next_day(sysdate, 1) from dual; 2020/11/15 17:01:39

# 1.3.4 聚合函数

函数 解释 输入 输出
avg(column_name) 平均值 略 略
count(* 或 column_name) 求记录数量 略 略
max(column_name) 求最大值 略 略
min(column_name) 求最小值 略 略
sum(column_name) 求和 略 略

# 1.4 Oracle模版语句

# 1.4.1 查询指定时间段下的记录

字段类型为varchar2：

select time from table where to_char(to_date(time,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd')=to_char(sysdate,'yyyy-mm-dd')--本日
select time from table where to_char(to_date(time,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd')=to_char(sysdate-1,'yyyy-mm-dd')--昨日
select time from table where to_char(to_date(time,'yyyy-mm-dd hh24:mi:ss'),'iw')=to_char(sysdate,'iw')--本周
select time from table where to_char(to_date(time,'yyyy-mm-dd hh24:mi:ss'),'iw')=to_char(sysdate-7,'iw')--上周
select time from table where to_char(to_date(time,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm')=to_char(sysdate,'yyyy-mm') --本月
select time from table where to_char(to_date(time,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm')=to_char(add_months(sysdate,-1),'yyyy-mm') --上月
select time from table where to_char(to_date(time,'yyyy-mm-dd hh24:mi:ss'),'q')=to_char(sysdate,'q')  --本季
select time from table where to_char(to_date(time,'yyyy-mm-dd hh24:mi:ss'),'q')=to_char(add_months(sysdate,-3),'q')  --上季
select time from table where to_char(to_date(time,'yyyy-mm-dd hh24:mi:ss'),'yyyy')=to_char(sysdate,'yyyy')  --本年
select time from table where to_char(to_date(time,'yyyy-mm-dd hh24:mi:ss'),'yyyy')=to_char(add_months(sysdate,-12),'yyyy')  --去年
1
2
3
4
5
6
7
8
9
10

字段类型为date：

select time from table where to_char(time,'yyyy-mm-dd')=to_char(sysdate,'yyyy-mm-dd')--本日
select time from table where to_char(time,'yyyy-mm-dd')=to_char(sysdate-1,'yyyy-mm-dd')--昨日
select time from table where to_char(time,'iw')=to_char(sysdate,'iw')--本周
select time from table where to_char(time,'iw')=to_char(sysdate-7,'iw')--上周
select time from table where to_char(time,'yyyy-mm')=to_char(sysdate,'yyyy-mm') --本月
select time from table where to_char(time,'yyyy-mm')=to_char(add_months(sysdate,-1),'yyyy-mm') --上月
select time from table where to_char(time,'q')=to_char(sysdate,'q')  --本季
select time from table where to_char(time,'q')=to_char(add_months(sysdate,-3),'q')  --上季
select time from table where to_char(time,'yyyy')=to_char(sysdate,'yyyy')  --本年
select time from table where to_char(time,'yyyy')=to_char(add_months(sysdate,-12),'yyyy')  --去年
1
2
3
4
5
6
7
8
9
10

# 1.4.2 查找用户与表的所属关系

查找某用户的所有表

select table_name from all_all_tables where owner = '大写用户名'
1

查询某用户下有某字段的所有表

select * from dba_tab_columns a where a.OWNER = '大写用户名' and a.COLUMN_NAME = '大写字段名';
1

查询某表的所属用户

select owner from dba_tables where table_name=upper('大写表名');
1

# 1.4.3 在查询结果中新增一列序号

select row_number() over (order by a.字段 asc) as number,a.* from 表名 a
1

# 1.4.4 查找删除重复记录

查找重复记录：

1、查找全部重复记录
select * from 表 where 重复字段 in (select 重复字段 from 表 group by 重复字段 having count(*)>1)
2、查找重复记录只显示一条
select * from 表 where id in (select max(id) from 表 group by 重复字段)
1
2
3
4

删除重复记录：

1、删除全部重复记录
delete 表 where 重复字段 in (select 重复字段 from 表 group by 重复字段 having count(*)>1)
2、删除重复记录只保留一条
delete 表 where id not in (select max(id) from 表 group by 重复字段)
1
2
3
4

# 1.4.5 解决数据库锁表问题

查询锁表

select b.owner, b.object_name, a.session_id, a.locked_mode
  from v$locked_object a, dba_objects b
 where b.object_id = a.object_id;
1
2
3

查询出用以解锁锁表的alter语句

 select 'alter system kill session '''||b.sid||','||b.serial#||''';' 
  from v$locked_object a, v$session b
 where a.session_id = b.sid
 order by b.logon_time;
1
2
3
4

然后执行查出来的用以解除锁表的alter语句

# 1.4.6 建立dblink跨库查询数据

用途：跨库查询数据、跨库导入导出数据

前提：两个库之间的网络是通的

方式：创建dblink

create  public  database link 创建的dblink名
　　connect to 另一个库的用户名 identified by 另一个库的密码 
　　using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 另一个库的地址)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))';
1
2
3

使用：在原有sql基础上加上@dblink名即可（在原库中查询）

另注：dblink的查询与删除

select * from dba_db_links  --查询
drop public database link 建立的dblink名  --删除
1
2

# 1.4.7 查询数据表的创建时间

在sys用户中dba_objects这个视图中有一个created字段，这个字段记录着表的创建时间。

select created from dba_objects where object_name = 'table_name'
1

# 1.4.8 查询sql语句执行记录

select t.sql_text, t.first_load_time
  from v$sqlarea t
 where 1 = 1
   and t.first_load_time like 'yyyy-MM-dd%' --筛选某时间
   and t.parsing_schema_name in ('大写用户名') --筛选某用户
 order by t.first_load_time desc
1
2
3
4
5
6

# 1.4.9 使用merge语句批量增删改

适用范围：在Oracle数据库中可用，在MySQL中不可用，适用于数据量比较大的情况。

使用场景：基于源表对目标表做INSERT，UPDATE，DELETE操作。

基本语法：

MERGE INTO target_table
USING source_table
ON condition
WHEN MATCHED THEN 
operation
WHEN NOT MATCHED THEN 
operation;
1
2
3
4
5
6
7

说明：

  • 最后语句分号不可以省略，且源表既可以是一个表也可以是一个子查询语句。
  • 在MERGE MATCHED 操作中，只能允许执行UPDATE 或者DELETE 语句。 在MERGE NOT MATCHED 操作中，只允许执行INSERT 语句

应用实例：

merge into oe.promotions_backup a
using oe.promotions b
on (a.promo_id = b.promo_id)
when matched then
  update set a.promo_name = b.promo_name
when not matched then
  insert (promo_id, promo_name) values (b.promo_id, b.promo_name);
1
2
3
4
5
6
7

# 1.4.10 比较两数据表的差异

[1] 比较表结构

(select column_name  
          from user_tab_columns  
         where table_name = 'T_A'  
        minus  
        select column_name  
          from user_tab_columns  
         where table_name = 'T_B')  
union   
(select column_name  
         from user_tab_columns  
        where table_name = 'T_B'  
       minus  
       select column_name  
         from user_tab_columns  
        where table_name = 'T_A');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

[2] 比较表数据

(select * from T_A  minus select * from T_B)  
union   
(select * from T_B  minus select * from T_A)
1
2
3

# 1.4.11 与序列有关的查询

[1] 查询当前用户下已创建的序列

select * from user_sequences;
1

[2] 查询所有序列及其所属的用户

select sequence_owner,sequence_name from dba_sequences
1

[3] 查询序列的下一个值

select 用户名.序列名.nextval from dual;
1

[4] 删除序列

drop sequence 用户名.序列名
1

注：在生成编号后，序列对象与其生成的编号之间没有延续关系，因此可以删除序列对象，即使生成的编号仍在使用。

# 1.4.12 查询数据库版本

select * from v$version;
1

# 1.5 Oracle常见问题

# 1.5.1 时间戳转日期

解决方案：直接对时间戳字段+0即可

举例：select systimestamp + 0 from dual

# 1.5.2 不存在的 LOB值 问题

报错原因：执行sql时报错”不存在的 LOB 值”，原因为wm_concat查询出的是LOB类型，而oralce的SQL语句中若查询了LOB字段是不能使用distinct，union，和group by等关键字的。

解决方案：将sql脚本中的to_char(wm_concat(字段))换成listagg(字段, ',') within group(order by 字段) 即可

举例：将to_char(wm_concat(distinct b.bmdbh)) yxclbh替换成listagg(b.bmdbh, ',') within group (order by b.bmdbh) yxclbh

# 1.5.3 批量插入数据

用sql把要要插入的字段查出来（不同的查出来，相同的写死），insert进去。

insert into 表名 (字段1, 字段2, 字段3)
select 字段1, 字段2, 字段3 from ...
1
2

# 1.5.4 除数为0的解决办法

利用decode函数，当b = 0时，返回0，否则才返回a/b的结果。

select decode(b,0,0,a/b) from dual;
1

# 1.5.5 直接查询clob格式的数据

可以通过to_char()将clob转换成字符串直接查询出来，诸如：select to_char(column_name) from table

如果报错 “缓冲区对于CLOB到CHAR转换或BLOB到RAW转换而言太小（实际：xxx，最大：4000）”，可以使用substr()进行截取

注：如果clob中有中文会占两个字符，所以可以除以2进行截取，诸如：select to_char(substr(column_name,0,2000)) from table

# 1.5.6 清理数据库内存的只需计划

需求情景：当遇到sql在plsql执行快，但是在程序里执行慢的问题时。

执行命令：alter system flush shared_pool

注意事项：测试库可以随便搞，生产环境下必须在晚上没有业务的时候执行。

# 1.5.7 单字段去重，然后再加列序号

用group by来实现，distinct弄不了，示例如下：

select row_number() over(order by a.列名 asc) as value,a.列名 as name
  from (select 列名 from 表名 group by 列名) a
1
2

# 1.5.8 将表的一列数据赋值给另一列

update table set column_name1=column_name2
1

# 1.5.9 拼接单引号

单引号'在sql中存在转义问题，应使用chr(39)进行拼接。

替换单引号--需要将a,b 替换为 'a','b' 可以这么写：

select chr(39) || REPLACE('a,b',',',chr(39) || ',' || chr(39)) || chr(39) from dual
1

# 1.6 Oracle语句对比

# 1.6.1 drop、truncate、delete的对比

truncate和delete只删除表数据（truncate比delete删除快），而drop则删除整个表结构和数据

delete语句为DML，drop和truncate为DLL，delete能回滚而drop和truncate不能回滚

# 1.6.2 in与exists的对比

select * from A where id in (select id from B);    --适用于A>>B
select * from A where exists (select 1 from B where A.id=B.id);  --适用于B>>A
1
2

以上两种语句功能相同，in是在内存里遍历比较，而exists需要查询数据库。

所以当B表数据量较大时，exists效率优于in；反之A表数据量较大时，in优于exists。

# 1.6.3 select 1 与select * 的对比

select 1的查询结果全是1，它的效率优于select *，如果你只是为了判断一定条件下是否有数据（如判断是否存在），则选用select 1的写法，一般与exists语句搭配使用

# 1.6.4 case ... when ... then ... else ... end与decode的对比

case ... when ... then ... else ... end （适用于各种判断情形）

select case when t.a > 1 then '成功' else '失败' end '其他' from table t;   --可以有多组when...then
1

decode（只适用于等于判断情形）

select decode(to_char(sysdate,'mm'),'01','1月','02','2月','03','3月','04','4月','5-12月') from dual;
1

注：case ... when ... then ... else ... end语句的结果如果要起别名应放在end之后（as xxx）

# 1.6.5 外连接以及（+）号用法

左外连接：left join是以左表的记录为基础的，示例中t_A可以看成左表，t_B可以看成右表,它的结果集是t_A表中的全部数据，再加上t_A表和t_B表匹配后的数据。换句话说，左表(t_A)的记录将会全部表示出来，而右表t_B只会显示符合搜索条件的记录。

用（+）来实现： 这个+号可以这样来理解： + 表示补充，即哪个表有加号，这个表就是匹配表。如果加号写在右表，左表就是全部显示，所以是左连接。

select * from t_A a left join t_B b on a.id = b.id;
select * from t_A a,t_B b where a.id=b.id(+);
1
2

右外连接：它和left join的结果刚好相反,是以右表t_B为基础的。它的结果集是t_B表所有记录，再加上t_A和t_B匹配后的数据。

select * from t_A a right join t_B b on a.id = b.id;
select * from t_A a,t_B b where a.id(+)=b.id;
1
2

全外连接：full join左表和右表都不做限制，所有的记录都显示， 全外连接不支持（+）写法。

select * from t_A a full join t_B b on a.id = b.id;
1

注：(+)写法不能和or同用，但left join on可以

# 1.6.6 union与union all的对比

union与union all的作用都是将多个结果合并在一起显示出来，区别如下：

  • union会自动压缩多个结果集合中的重复结果。
  • union all则将所有的结果全部显示出来，不管是不是重复。

# 1.6.7 delete from 与 delete的对比

二者作用完全相同，delete from的写法更规范一些。

# 1.6.8 for update 和select t.*,t.rowid编辑数据的对比

for update是加锁查询，如果未及时commmit，会导致锁表。

select t.*,t.rowid table 是不加锁查询，不会锁表。

# 2. MySQL数据库基础

# 2.1 MySQL与Oracle的不同之处

# 2.1.1 基本语法差异

[1] MySQL的外连接不支持(+)的写法，应使用left join等原生写法。

[2] MySQL拼接字符串应使用concat(str1,str2)， 数字与字符串的拼接用 || 的写法会出问题。

[3] MySQL不支持decode的写法，用case...when...then...else...end来替代。

CASE WHEN total = '0' THEN '暂无数据' ELSE total END total,
1

[4] 分页查询：MySQL是直接在SQL语句中写"select... from ...where...limit x, y"就可以实现分页，而Oracle则是需要用到伪列ROWNUM和嵌套查询。

[5] MySQL字符串转数字，在字符串的末尾加0即可（如：'100' + 0）

  • 注：直接用数字字符串比较数值会有问题（比如price<='10000'，过滤出的只有10000），需要转换。

# 2.1.2 基本函数差异

[1] MySQL不支持to_char和to_date函数，应使用date_format函数转换日期与字符串。

把字符串转为日期格式
select date_format('2011-09-20 08:30:45', '%Y-%m-%d %H:%i:%S');              
把日期转为字符串格式
select date_format(now(),'%Y-%m-%d %H:%i:%S');
1
2
3
4

[2] MySQL不支持listagg函数进行逗号分隔查询，可以使用group_concar()来实现。

select `code`,group_concat(`name` separator ',') from `sys_code` group by `code`
1

# 2.2 MySQL模板语句

# 2.2.1 与字段处理相关的模板语句

[1] 查询某个表的所有字段名

以列表的形式查出来

select COLUMN_NAME from information_schema.COLUMNS where table_name = '表名' and table_schema = '数据库名';
1

以逗号分割形式查出来

SELECT GROUP_CONCAT(COLUMN_NAME SEPARATOR ",") FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = '数据库名' AND TABLE_NAME = '表名'
1
2

注：group_concat可用于将查询结果以逗号分隔字符串的形式返回。

[2] 根据某一字段去除重复数据

DELETE
FROM
	表名
WHERE
	id NOT IN (
		SELECT
			dt.minno
		FROM
			(
				SELECT
					MIN(id) AS minno
				FROM
					表名
				GROUP BY
					用于判定重复的字段名
			) dt
	)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

[3] 如果某字段为null，则用另一字段替代

IFNULL() 函数用于判断第一个表达式是否为 NULL，如果为 NULL 则返回第二个参数的值，如果不为 NULL 则返回第一个参数的值。

// update_time为null时用create_time代替
ifnull(update_time, create_time)
// content为null时用''代替
ifnull(content,'')
1
2
3
4

[4] 一次性替换某个字段中的多个字符

// 查找对比
SELECT 
  template, 
  REPLACE(REPLACE(REPLACE(REPLACE(template, 
  '#1', 'target1'),
  '#2', 'target2'),
  '#3', 'target3'),
  '#4', 'target4') as replace_result
from my_table
where ...

// 数据替换
update my_table
set 
  template = 
  REPLACE(REPLACE(REPLACE(REPLACE(template, 
  '#1', 'target1'),
  '#2', 'target2'),
  '#3', 'target3'),
  '#4', 'target4')
where ...
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

[5] 根据指定字符截取字符串

// 截取第2个“.”之前的所有字符  www.demo
SELECT SUBSTRING_INDEX('www.demo.com', '.', 2) from dual;
// 截取倒数第2个“.”之后的所有字符  demo.com
SELECT SUBSTRING_INDEX('www.demo.com', '.', -2) from dual;
1
2
3
4

[6] 将逗号分隔的字段从一行拆分成多行

创建测试数据：

CREATE TABLE `company` (
  `id` int(20) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  `shareholder` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `company` VALUES ('1', '阿里巴巴', '马云');
INSERT INTO `company` VALUES ('2', '淘宝', '马云,孙正义');
1
2
3
4
5
6
7

查询语句：

SELECT
	a.name,
	substring_index(
		substring_index(
			a.shareholder,
			',',
			b.help_topic_id + 1
		),
		',' ,- 1
	) AS shareholder
FROM
	company a
JOIN mysql.help_topic b ON b.help_topic_id < (
	length(a.shareholder) - length(
		REPLACE (a.shareholder, ',', '')
	) + 1
)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

返回结果：

name	    shareholder
阿里巴巴	 马云
淘宝	    马云
淘宝	    孙正义
1
2
3
4

[6] 将多行数据合并成一行

创建测试数据：

CREATE TABLE GRADE (
  ID int NOT NULL AUTO_INCREMENT,
  USER_NAME varchar(20) NOT NULL,
  COURSE varchar(20) NOT NULL,
	COURSE_CODE varchar(20) NOT NULL,
  SCORE float DEFAULT '0',
  PRIMARY KEY (ID)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO GRADE(USER_NAME, COURSE, COURSE_CODE, SCORE) VALUES
("张三", "语文", "Chinese", 98),
("张三", "数学", "math", 86),
("张三", "英语", "English", 92),
("李四", "语文", "Chinese", 76),
("李四", "数学", "math", 89),
("李四", "英语", "English", 32),
("王五", "语文", "Chinese", 78),
("王五", "数学", "math", 91),
("王五", "英语", "English", 56);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

查询语句：

SELECT USER_NAME,GROUP_CONCAT(COURSE SEPARATOR '、') COURSE FROM GRADE GROUP BY USER_NAME;
1

返回结果：

USER_NAME   COURSE
张三         语文、数学、英语
李四         语文、数学、英语
王五         语文、数学、英语
1
2
3
4

# 2.2.2 与时间相关的模板语句

[1] 查询指定时间范围内的数据

// 今天
select * from 表名 where to_days(时间字段名) = to_days(now());

// 昨天
SELECT * FROM 表名 WHERE TO_DAYS(NOW()) - TO_DAYS(时间字段名) <= 1

// 近7天
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名)

// 近30天
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名)

// 本月
SELECT * FROM 表名 WHERE DATE_FORMAT(时间字段名, '%Y%m') = DATE_FORMAT CURDATE() , '%Y%m')

// 上一月
SELECT * FROM 表名 WHERE PERIOD_DIFF(date_format(now( ) , '%Y%m') , date_format(时间字段名, '%Y%m' )) =1

// 查询本季度数据
select * from 表名 where QUARTER(时间字段名)=QUARTER(now());

// 查询上季度数据
select * from 表名 where QUARTER(时间字段名)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));

// 查询本年数据
select * from 表名 where YEAR(时间字段名)=YEAR(NOW());

// 查询上年数据
select * from 表名 where year(时间字段名)=year(date_sub(now(),interval 1 year));

// 查询当前这周的数据
SELECT * FROM 表名 WHERE YEARWEEK(date_format(时间字段名,'%Y-%m-%d')) = YEARWEEK(now());

// 查询上周的数据
SELECT * FROM 表名 WHERE YEARWEEK(date_format(时间字段名,'%Y-%m-%d')) = YEARWEEK(now())-1;

// 查询上个月的数据
select * from 表名 where DATE_FORMAT(时间字段名,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') ; 

//查询当前月份的数据 
select * from 表名 where date_format(时间字段名,'%Y-%m')=date_format(now(),'%Y-%m')

// 查询距离当前现在6个月的数据
select * from 表名 where 时间字段名 between date_sub(now(),interval 6 month) and now();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

[2] 日期与时间戳相互转换

// 日期转时间戳
select UNIX_TIMESTAMP('2022-03-04 04:37:44') from dual;

// 时间戳转日期
select FROM_UNIXTIME(1646339864) from dual;
1
2
3
4
5

[3] 对表中的某个时间字段加去或减少一段时间

为日期增加一个时间间隔：date_add()；为日期减少一个时间间隔：date_sub()，时间间隔选项如下：

1 microsecond 1毫秒、1 second 1秒、1 minute 1分钟、1 hour 1小时
1 day 1天、1 week 1周、1 month 1月、1 quarter 1季、1 year 1年
1
2

完整示例：对table_name表的time字段加3天

update table_name a set a.time = date_add(a.time, interval 3 day)
1

[4] 将日期转成指定格式的字符串

DATE_FORMAT(insert_time,'%Y-%m-%d %H:%i:%s')
1

# 2.3 MySQL常见问题

# 2.3.1 锁表问题

问题描述：多人共同操作一张数据表，有人执行sql语句的时候没有commit，导致锁表，其他人都没法对该表进行修改了。

show OPEN TABLES where In_use > 0  //查询是否锁表
show processlist   // 查看锁表进程
kill [ID] // 杀死锁表进程
1
2
3

# 2.3.2 外键约束问题

问题描述：删除表数据时报错：1451 -Cannot delete or update a parent row: a foreign key constraint fails

解决办法：先使外键约束失效，执行SQL， 再恢复约束。

$ set foreign_key_checks=0; //关闭外键检查
$ 执行sql语句
$ set foreign_key_checks=1; //开启外键检查
1
2
3

# 2.3.3 存在则更新不存在则插入

使用 replace 代替 insert，即可实现"存在则更新，不存在则插入"的需求。

$ replace into table_name(col_name, ...) values(...)
$ replace into table_name(col_name, ...) select ...
$ replace into table_name set col_name=value, ...
1
2
3

另注：insert ignore into 可以实现“如果表中如果已经存在相同的记录，则忽略当前新数据”的需求。

# 2.3.4 连接超时时间

问题描述：The last packet sent successfully to the server was xxx milliseconds ago. is longer than the server configured value of 'wait_timeout'.

解决办法：wait_timeout和interactive_timeout的默认值是28800（8h），可以把它改长一些。以下是临时修改的方案，永久修改的话需要修改数据库配置文件。

$ show global variables like '%timeout%'
$ show session variables like '%timeout%'

$ set global wait_timeout=288000;
$ set global interactive_timeout=288000;

$ set session wait_timeout=288000;
$ set session interactive_timeout=288000;
1
2
3
4
5
6
7
8

# 2.3.5 大表数据量查询问题

大数据情景，如果对数据量精度没有太高要求，只是查一下当前的数据体量大致是多少，可以加一个EXPLAIN。

$ select count(1) from table               // 会涉及全表扫描，所以特别的慢。
$ EXPLAIN select count(1) from table       // 会根据存储结构，大致的估算数据量，会存在数据误差，毫秒级速度返回。
1
2

大表数据量查询

# 2.3.6 字符集编码问题

[1] MySQL执行包含union的SQL语句报错：Illegal mix of collations for operation UNION

原因：union连接的表排序规则不一致

解决：保证表和字段的编码规则、排序规则一致即可

[2] MySQL批量修改表和表内字段的字符集和排序规则

批量修改表

SELECT
    CONCAT( 'ALTER TABLE ', TABLE_NAME, ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;' ) 
FROM
    information_schema.TABLES 
WHERE
    TABLE_SCHEMA = '数据库名';
1
2
3
4
5
6

批量修改字段

SELECT
    CONCAT(
        'ALTER TABLE `',
        TABLE_NAME,
        '` MODIFY `',
        COLUMN_NAME,
        '` ',
        DATA_TYPE,
        '(',
        CHARACTER_MAXIMUM_LENGTH,
        ') CHARACTER SET utf8 COLLATE utf8_unicode_ci',
        ( CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END ),
        ';' 
) 
FROM
    information_schema.COLUMNS 
WHERE
    TABLE_SCHEMA = '数据库名' 
    AND (
    DATA_TYPE = 'varchar' 
    OR DATA_TYPE = 'char')
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

说明：将上述代码的数据库名、字符集、字符排序规则换成自己需要的，执行上述sql生成用于具体修改的sql语句。

注意：该批量修改方法会导致已添加的注释丢失，如果介意，请勿使用。

[3] 未使用utf8mb4字符集编码导致表情包数据写入出错问题

问题描述：同步数据时，如果字段里存在表情包，字符集编码不正确的话会导致同步出错。

前提条件：需要 >= MySQL 5.5.3版本

select version();
1

解决办法：可通过以下方式解决

--修改表默认的字符集和所有字符列的字符集
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci
1
2

# 3. 索引基础及优化

# 3.1 索引简介

索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址，在数据十分庞大的时候，索引可以大大加快查询的速度，这是因为使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据。

索引的优缺点：

  • 优点：可以快速检索，减少I/O次数，加快检索速度；根据索引分组和排序，可以加快分组和排序。
  • 缺点：索引本身也是表，因此会占用存储空间，一般来说，索引表占用的空间是数据表的1.5倍；索引表的维护和创建需要时间成本，这个成本随着数据量增大而增大；构建索引会降低数据表增删改操作的效率，因为在修改数据表的同时还需要修改索引表。

# 3.2 MySQL关于索引的语法

# 3.2.1 创建索引

在创建表的时候添加索引

CREATE TABLE mytable(  
    ID INT NOT NULL,   
    username VARCHAR(16) NOT NULL,  
    INDEX [indexName] (username(length))  
);
1
2
3
4
5

在创建表以后添加索引

ALTER TABLE my_table ADD [UNIQUE] INDEX index_name(column_name);
或者
CREATE INDEX index_name ON my_table(column_name);
1
2
3

注意事项：

1）索引需要占用磁盘空间，因此在创建索引时要考虑到磁盘空间是否足够。

2）创建索引时需要对表加锁，因此实际操作中需要在业务空闲期间进行。

# 3.2.2 删除索引

DROP INDEX my_index ON tablename；
或者
ALTER TABLE table_name DROP INDEX index_name;
1
2
3

# 3.2.3 查看表中的索引

SHOW INDEX FROM tablename
1

# 3.2.4 查看查询语句使用索引的情况

// explain 加查询语句
explain SELECT * FROM table_name WHERE column_1='123';
1
2

# 3.2 索引的分类

常见的索引类型有：主键索引、唯一索引、普通索引、全文索引、组合索引

# 3.2.1 主键索引

即主索引，根据主键pk_clolum（length）建立索引，不允许重复，不允许空值。

ALTER TABLE 'table_name' ADD PRIMARY KEY pk_index('col');
1

# 3.2.2 唯一索引

用来建立索引的列的值必须是唯一的，允许空值。

ALTER TABLE 'table_name' ADD UNIQUE index_name('col');
1

# 3.2.3 普通索引

用表中的普通列构建的索引，没有任何限制。

ALTER TABLE 'table_name' ADD INDEX index_name('col');
1

# 3.2.4 全文索引

用大文本对象的列构建的索引。

ALTER TABLE 'table_name' ADD FULLTEXT INDEX ft_index('col');
1

# 3.2.5 组合索引

用多个列组合构建的索引，这多个列中的值不允许有空值。

ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3');
1

遵循“最左前缀”原则，把最常用作为检索或排序的列放在最左，依次递减，组合索引相当于建立了col1，<col1,col2>，<col1,col2,col3>三个索引，其它的列，比如<col2,col3>，<col1,col3>，col2，col3等等都是不能使用索引的。

在使用组合索引的时候可能因为列名长度过长而导致索引的key太大，导致效率降低，在允许的情况下，可以只取col1和col2的前几个字符作为索引。如下示例表示使用col1的前4个字符和col2的前3个字符作为索引。

ALTER TABLE 'table_name' ADD INDEX index_name(col1(4),col2（3));
1

# 3.3 索引的实现原理

MySQL支持诸多存储引擎，而各种存储引擎对索引的支持也各不相同，因此MySQL数据库支持多种索引类型，如BTree索引，B+Tree索引，哈希索引，全文索引等等。

# 3.3.1 哈希索引

只有memory（内存）存储引擎支持哈希索引，哈希索引用索引列的值计算该值的hashCode，然后在hashCode相应的位置存执该值所在行数据的物理位置，因为使用散列算法，因此访问速度非常快，但是一个值只能对应一个hashCode，而且是散列的分布方式，因此哈希索引不支持范围查找和排序的功能。

# 3.3.2 全文索引

FULLTEXT（全文）索引，仅可用于MyISAM和InnoDB，针对较大的数据，生成全文索引非常的消耗时间和空间。对于文本的大对象，或者较大的CHAR类型的数据，如果使用普通索引，那么匹配文本前几个字符还是可行的，但是想要匹配文本中间的几个单词，那么就要使用LIKE %word%来匹配，这样需要很长的时间来处理，响应时间会大大增加，这种情况，就可使用时FULLTEXT索引了，在生成FULLTEXT索引时，会为文本生成一份单词的清单，在索引时及根据这个单词的清单来索引。

FULLTEXT可以在创建表的时候创建，也可以在需要的时候用ALTER或者CREATE INDEX来添加：

// 创建表的时候添加FULLTEXT索引
CTREATE TABLE my_table(
    id INT(10) PRIMARY KEY,
    name VARCHAR(10) NOT NULL,
    my_text TEXT,
    FULLTEXT(my_text)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

// 创建表以后，在需要的时候添加FULLTEXT索引
ALTER TABLE my_table ADD FULLTEXT INDEX ft_index(column_name);
1
2
3
4
5
6
7
8
9
10

全文索引的查询也有自己特殊的语法，而不能使用LIKE %查询字符串%的模糊查询语法

SELECT * FROM table_name MATCH(ft_index) AGAINST('查询字符串');
1

注意事项：

1）对于较大的数据集，把数据添加到一个没有FULLTEXT索引的表，然后添加FULLTEXT索引的速度比把数据添加到一个已经有FULLTEXT索引的表快。

2）5.6之前的MySQL自带的全文索引只能用于MyISAM存储引擎，如果是其它数据引擎全文索引不会生效。5.6之后InnoDB存储引擎开始支持全文索引。

3）在MySQL中，全文索引支队英文有用，目前对中文还不支持。5.7版本之后通过使用ngram插件开始支持中文。

4）在MySQL中，如果检索的字符串太短则无法检索得到预期的结果，检索的字符串长度至少为4字节，此外，如果检索的字符包括停止词，那么停止词会被忽略。

# 3.3.3 BTree索引和B+Tree索引

[1] BTree索引

BTree是平衡搜索多叉树，设树的度为2d（d>1），高度为h，那么BTree要满足以一下条件：

  • 每个叶子结点的高度一样，等于h；
  • 每个非叶子结点由n-1个key和n个指针point组成，其中d<=n<=2d,key和point相互间隔，结点两端一定是key；
  • 叶子结点指针都为null；
  • 非叶子结点的key都是[key,data]二元组，其中key表示作为索引的键，data为键值所在行的数据；

BTree的结构如下：

BTree索引

在BTree的结构下，就可以使用二分查找的查找方式，查找复杂度为h*log(n)，一般来说树的高度是很小的，一般为3左右，因此BTree是一个非常高效的查找结构。

[2] B+Tree索引

B+Tree是BTree的一个变种，设d为树的度数，h为树的高度，B+Tree和BTree的不同主要在于：

  • B+Tree中的非叶子结点不存储数据，只存储键值；
  • B+Tree的叶子结点没有指针，所有键值都会出现在叶子结点上，且key存储的键值对应data数据的物理地址；
  • B+Tree的每个非叶子节点由n个键值key和n个指针point组成；

B+Tree的结构如下：

B+Tree索引

B+Tree相比于BTree的优点：

1）磁盘读写代价更低

一般来说B+Tree比BTree更适合实现外存的索引结构，因为存储引擎的设计专家巧妙的利用了外存（磁盘）的存储结构，即磁盘的最小存储单位是扇区，而操作系统的块通常是整数倍的sector，操作系统以页为单位管理内存，一页通常默认为4K，数据库的页通常设置为操作系统页的整数倍，因此索引结构的节点被设计为一个页的大小，然后利用外存的“预读取”原则，每次读取的时候，把整个节点的数据读取到内存中，然后在内存中查找，已知内存的读取速度是外存读取I/O速度的几百倍，那么提升查找速度的关键就在于尽可能少的磁盘I/O，那么可以知道，每个节点中的key个数越多，那么树的高度越小，需要I/O的次数越少，因此一般来说B+Tree比BTree更快，因为B+Tree的非叶节点中不存储数据，就可以存储更多的key。

2）查询速度更稳定

由于B+Tree非叶子节点不存储数据，因此所有的数据都要查询至叶子节点，而叶子节点的高度都是相同的，因此所有数据的查询速度都是一样的。

[3] 带顺序索引的B+Tree索引

很多存储引擎在B+Tree的基础上进行了优化，添加了指向相邻叶节点的指针，形成了带有顺序访问指针的B+Tree，这样做是为了提高区间查找的效率，只要找到第一个值那么就可以顺序的查找后面的值。

带顺序索引的B+Tree的结构如下：

带顺序索引的B+Tree

# 3.3.4 聚簇索引和非聚簇索引

分析了MySQL的索引结构的实现原理，然后再来看看具体的存储引擎怎么实现索引结构的，MySQL中最常见的两种存储引擎分别是MyISAM和InnoDB，分别实现了非聚簇索引和聚簇索引。

  • 聚簇索引的解释是：聚簇索引的顺序就是数据的物理存储顺序

  • 非聚簇索引的解释是：索引顺序与数据物理排列顺序无关

在索引的分类中，我们可以按照索引的键是否为主键来分为“主索引”和“辅助索引”，使用主键键值建立的索引称为“主索引”，其它的称为“辅助索引”。因此主索引只能有一个，辅助索引可以有很多个。

MyISAM——非聚簇索引

  • MyISAM存储引擎采用的是非聚簇索引，非聚簇索引的主索引和辅助索引几乎是一样的，只是主索引不允许重复，不允许空值，他们的叶子结点的key都存储指向键值对应的数据的物理地址。
  • 非聚簇索引的数据表和索引表是分开存储的。
  • 非聚簇索引中的数据是根据数据的插入顺序保存。因此非聚簇索引更适合单个数据的查询。插入顺序不受键值影响。
  • 只有在MyISAM中才能使用FULLTEXT索引。（mysql5.6以后innoDB也支持全文索引）

InnoDB——聚簇索引

  • 聚簇索引的主索引的叶子结点存储的是键值对应的数据本身，辅助索引的叶子结点存储的是键值对应的数据的主键键值。因此主键的值长度越小越好，类型越简单越好。
  • 聚簇索引的数据和主键索引存储在一起。
  • 聚簇索引的数据是根据主键的顺序保存。因此适合按主键索引的区间查找，可以有更少的磁盘I/O，加快查询速度。但是也是因为这个原因，聚簇索引的插入顺序最好按照主键单调的顺序插入，否则会频繁的引起页分裂，严重影响性能。
  • 在InnoDB中，如果只需要查找索引的列，就尽量不要加入其它的列，这样会提高查询效率。

使用主索引的时候，更适合使用聚簇索引，因为聚簇索引只需要查找一次，而非聚簇索引在查到数据的地址后，还要进行一次I/O查找数据。

因为聚簇辅助索引存储的是主键的键值，因此可以在数据行移动或者页分裂的时候降低成本，因为这时不用维护辅助索引。但是由于主索引存储的是数据本身，因此聚簇索引会占用更多的空间。

聚簇索引在插入新数据的时候比非聚簇索引慢很多，因为插入新数据时需要检测主键是否重复，这需要遍历主索引的所有叶节点，而非聚簇索引的叶节点保存的是数据地址，占用空间少，因此分布集中，查询的时候I/O更少，但聚簇索引的主索引中存储的是数据本身，数据占用空间大，分布范围更大，可能占用好多的扇区，因此需要更多次I/O才能遍历完毕。

下图可以形象的说明聚簇索引和非聚簇索引的区别：

聚簇索引和非聚簇索引的区别

从上图中可以看到聚簇索引的辅助索引的叶子节点的data存储的是主键的值，主索引的叶子节点的data存储的是数据本身，也就是说数据和索引存储在一起，并且索引查询到的地方就是数据（data）本身，那么索引的顺序和数据本身的顺序就是相同的；

而非聚簇索引的主索引和辅助索引的叶子节点的data都是存储的数据的物理地址，也就是说索引和数据并不是存储在一起的，数据的顺序和索引的顺序并没有任何关系，也就是索引顺序与数据物理排列顺序无关。

此外MyISAM和innoDB的区别总结如下：

  • InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
  • MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM和innoDB引擎对比

此外，Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引。

# 3.4 索引的使用策略

# 3.4.1 什么时候要使用索引

  • 主键自动建立唯一索引；
  • 经常作为查询条件在 WHERE 或者 ORDER BY 语句中出现的列要建立索引；
  • 作为排序的列要建立索引；
  • 查询中与其他表关联的字段，外键关系建立索引；
  • 高并发条件下倾向组合索引；
  • 用于聚合函数的列可以建立索引，例如使用了max(column_1)或者count(column_1)时的column_1就需要建立索引。

# 3.4.2 什么时候不要使用索引

  • 经常增删改的列不要建立索引；
  • 有大量重复的列不建立索引；
  • 表记录太少不要建立索引。只有当数据库里已经有了足够多的测试数据时，它的性能测试结果才有实际参考价值。如果在测试数据库里只有几百条数据记录，它们往往在执行完第一条查询命令之后就被全部加载到内存里，这将使后续的查询命令都执行得非常快——不管有没有使用索引。只有当数据库里的记录超过了1000条、数据总量也超过了MySQL服务器上的内存总量时，数据库的性能测试结果才有意义。

# 3.4.3 索引失效的情况

  • 在组合索引中不能有列的值为NULL，如果有，那么这一列对组合索引就是无效的。
  • 在一个SELECT语句中，索引只能使用一次，如果在WHERE中使用了，那么在ORDER BY中就不会用了。
  • LIKE操作中，'%aaa%'不会使用索引，也就是索引会失效，但是‘aaa%’可以使用索引。
  • 在索引的列上使用表达式或者函数会使索引失效，例如：select * from users where YEAR(date)<2022，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：select * from users where date<'2022-01-01'。
  • 在查询条件中使用不等于会导致索引失效。特别的是如果对主键索引使用!=则不会使索引失效，如果对主键索引或者整数类型的索引使用<或者>符号不会使索引失效。
  • 在查询条件中使用IS NULL或者IS NOT NULL会导致索引失效。
  • 字符串不加单引号会导致索引失效。更准确的说是类型不一致会导致失效，比如字段email是字符串类型的，使用WHERE email=99999 则会导致索引失效，应该改为WHERE email='99999'。
  • 在查询条件中使用OR连接多个条件会导致索引失效，除非OR连接的每个条件都加上索引，这时应该改为两次查询，然后用UNION ALL连接起来。
  • 如果排序的字段使用了索引，那么SELECT的字段也要是索引字段，否则索引失效。特别的是如果排序的是主键索引则select * 不会导致索引失效。
  • 尽量不要包括多列排序，如果一定要，最好为这些列构建组合索引。

# 3.5 索引的优化

# 3.5.1 最左前缀原则

索引的最左前缀和和B+Tree中的“最左前缀原理”有关，举例来说就是如果设置了组合索引<col1,col2,col3>那么以下3种情况可以使用索引：col1，<col1,col2>，<col1,col2,col3>，其它的列，比如<col2,col3>，<col1,col3>，col2，col3等等都是不能使用索引的。

根据最左前缀原则，我们一般把排序分组频率最高的列放在最左边，以此类推。

# 3.5.2 带索引的模糊查询优化

在上面已经提到，使用LIKE进行模糊查询的时候，'%aaa%'不会使用索引，也就是索引会失效。如果是这种情况，只能使用全文索引来进行优化。

为检索的条件构建全文索引，然后使用。

SELECT * FROM tablename MATCH(index_colum) ANGAINST(‘word’);
1

# 3.5.3 使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

# 4. 存储过程基础及使用

# 4.1 存储过程简介

存储过程是在大型数据库系统中，一组为了完成特定功能的SQL语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来调用存储过程。

存储过程的优缺点：

  • 优点：预编译SQL，提升执行效率；可以隐藏执行逻辑，只暴露名称和参数；相较于程序来说，修改起来更加便捷。
  • 缺点：随着SQL行数的增加，维护复杂度呈线性提升；无法调试，迭代过程中风险较高。

# 4.2 MySQL关于储存过程的语法

# 4.2.1 创建存储过程

如下是一个简单的查询存储过程示例（//处不是多余的，注意事项里会讲解）

DELIMITER //
 CREATE PROCEDURE GetAllUsers()
   BEGIN
   SELECT * FROM user;
   END //
 DELIMITER ;
1
2
3
4
5
6

注意事项：

  • 第一个命令是DELIMITER //，与存储过程语法无关。DELIMITER语句将标准分隔符（分号;）更改为另一个符号（示例里用了//）。在END关键字之后，我们使用分隔符// 来标识存储过程的结束。最后一个命令DELIMITER;将分隔符更改回分号。
  • 使用CREATE PROCEDURE 语句来创建一个新的存储过程，在CREATE PROCEDURE 语句后指定存储过程的名称。
  • BEGIN 和 END 之间的部分称为存储过程的主体。将SQL语句放在这里用于处理业务逻辑。

# 4.2.2 查看存储过程

可以使用如下命令查看指定的数据库中有哪些存储过程：

select name from mysql.proc where db='数据库名';
1

使用 SHOW PROCEDURE STATUS 加上存储过程名（注意不带括号）查看具体的存储过程状态。

 SHOW PROCEDURE STATUS LIKE 'GetAllUsers';
1

# 4.2.3 调用存储过程

要调用存储过程，使用 CALL 加上存储过程名（需要带上括号）即可。

CALL GetAllUsers();
1

# 4.2.4 删除存储过程

要删除存储过程，使用 DROP PROCEDURE 加上存储过程名（注意不带括号）即可。

DROP PROCEDURE GetAllUsers;
1

# 5. SQL优化与高可用架构

# 5.1 SQL查询性能优化

# 5.1.1 关于全表扫描的注意点

[1] != 与 <>：=和<>都是不等于的意思，应尽量避免在 where 子句中使用 != 或 <> 操作符，否则会放弃使用索引而进行全表扫描。

[2] like的‘%%’ 与 ‘%’：like '%a%'将导致全表扫描，like '%a'可以用到索引，但最好是使用instr关键词进行替换查询。

[3] where子句：尽量避免在 where 子句中对字段进行表达式操作或函数操作，否则系统将可能无法正确使用索引。

[4] select *语句：尽量不要使用 select * from table ，用具体的字段列表替换*，不要返回用不到的字段，避免全盘扫描。

# 5.1.2 关键字替换查询

[1] in与exists：根据A和B表数据量的大小来选择合适的关键字，如果数据量数量级相近则用哪个都可以。

select * from A where id in (select id from B);                    --适用于A>>B
select * from A where exists (select 1 from B where A.id = B.id);  --适用于B>>A
1
2

注：in里面不要再关联数据量特别大的主表了。

[2] like与instr：like的写法都是低效的，可以用类似于instr(code, 'test') > 0的语句进行替换。

[3] or与union、in：or的写法都是低效的，通通可以用union、in等关键字进行替换。

[4] union与union all：当我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候，尽量使用union all而不是union。

# 5.1.3 MySQL的语句执行顺序

MySQL的语句执行顺序如下：

from -> on -> join -> where -> group by -> avg,sum.... -> having -> select -> distinct -> order by -> limit

# 5.2 高可用数据架构

# 5.2.1 主备式架构

主备式架构是双机部署中最简单的一种架构了，几乎市面上所有的数据库系统都会自带这个主备功能。主机作为日常提供数据读写服务的机器，备机并不提供线上服务，但会实时的将主机的数据同步过来。一旦主机出了故障，通过人工的方式，手动的将主机踢下线，将备机改为主机来继续提供服务。

优点是几乎不需要做什么开发改造，各类数据库就支持这种模式，部署维护起来也简单，并没有引入额外的系统复杂度和瓶颈。缺点是主备架构会造成严重浪费资源，而且当主机出现故障的时候，需要人工去干预，处理还不一定及时。

# 5.2.2 主从式架构

主从式架构中的从机虽然也在随时随刻提供服务，但是它只提供读服务，并不提供写服务。主机会实时的将线上数据同步到从机，以保证从机能够正常的提供读操作。当主机出现故障后，从机能够自动检测发现，同时从机将自己迅速切换为主机，将原来的主机立即下线服务，或转换为从机状态。

MySQL主从架构

主从式架构的优点是可以保证高可用、水平扩展、读写分离、数据备份。但使用该架构要实现“主从双机自动切换”，在设计时要考虑“主机与从机之间的状态如何判断”、“切换策略问题”、“数据冲突问题”。

# 5.2.3 主主式架构

主主式架构（又称为互为主从架构）是指两台机器自己都是主机，并且也都是作为对方的从机。两台机器都提供完整的读写服务，因此无需切换，客户机在调用的时候随机挑选一台即可，当其中一台宕机了，另外一台还可以继续服务。

采用互为主从架构有个复杂点就是，因为两台主机都接受写数据，那就需要将写的最新数据实时的同步给对方，需要将数据进行两台主机的双向复制。而双向复制不可避免的会在一定程度上带来数据延迟、极端情况下甚至有数据丢失等问题。在实际业务中，有些业务数据对一致性要求是非常高的，并不能接受数据的延迟、丢失，因此这类业务也不适合互为主从的模式，比如金融业务。

# 6. 参考资料

[1] sql中drop、truncate和delete的区别 from 博客园 (opens new window)

[2] SQL查询中in和exists的区别分析 from 简书 (opens new window)

[3] 数据库-Oracle条件判断语句 from 开源中国 (opens new window)

[4] Oracle查询今天、昨天、本周、上周、本月、上月数据 from CSDN (opens new window)

[5] Oracle 查询当天、当周、当月和当季度的所有记录 from CSDN (opens new window)

[6] sql select * ,select 1, select count(*) ,select count(1)的区别 from CSDN (opens new window)

[7] Oracle PL\SQL操作（四）索引与约束 from go4it (opens new window)

[8] Oracle左连接、右连接、全外连接以及（+）号用法 from 何海洋 (opens new window)

[9] MySQL之——查询重复记录、删除重复记录方法大全 from CSDN (opens new window)

[10] ORACLE系统视图 from 博客园 (opens new window)

[11] ora-01950:对表空间XXX无权限 from CSDN (opens new window)

[12] 数据库中一张表可以有多少个主键？from CSDN (opens new window)

[13] date和timestamp相互转换 from Hider1214 (opens new window)

[14] ORA-22922: 不存在的 LOB 值解决方法 from 百度经验 (opens new window)

[15] Oracle对表空间 USERS 无权限问题 from CSDN (opens new window)

[16] 只有dba才能导入由其他dba导出的文件的解决方案 from 博客园 (opens new window)

[17] 使用plsql创建表空间和用户 from CSDN (opens new window)

[18] for update 和select t.*,t.rowid编辑数据的区别 from CSDN (opens new window)

[19] Oracle中除数为0的解决办法 from CSDN (opens new window)

[20] ORA-22835 缓冲区对于 CLOB 到 CHAR 转换或 BLOB 到 RAW 转换而言太小 from 博客园 (opens new window)

[21] oracle 创建新表，并复制旧表数据 from CSDN (opens new window)

[22] oracle 查看 、创建、删除 dblink from CSDN (opens new window)

[23] Oracle中查看某个表的创建时间 from CSDN (opens new window)

[24] 查询oracle数据库操作记录 from CSDN (opens new window)

[25] SQL高级知识——MERGE INTO from 知乎 (opens new window)

[26] oracle中比较两表表结构差异和数据差异的方法 from CSDN (opens new window)

[27] oracle中查找某个表属于哪个用户 from 百度知道 (opens new window)

[28] Oracle单引号拼接和替换 from 博客园 (opens new window)

[29] mysql DATE_FORMAT函数用法 from 博客园 (opens new window)

[30] MySQL中实现Oracle listagg函数 from CSDN (opens new window)

[31] sql注入时union出错（Illegal mix of collations for operation UNION）from CSDN (opens new window)

[32] 数据库怎么复制一个表中的全部字段/数据库复制一张表的全部字段（带逗号）的方法 from CSDN (opens new window)

[33] Mysql 更新时间（加上或者减去一段时间）from CSDN (opens new window)

[34] MySql批量修改表和表内字段的字符集和排序规则 from segmentfault (opens new window)

[35] mysql根据某一字段去除重复数据 from 51CTO (opens new window)

[36] mysql 查询当天、本周，本月，上一个月的数据 from CSDN (opens new window)

[37] mysql 一次替换多个变量 from CSDN (opens new window)

[38] Mysql字符串截取总结：left()、right()、substring()、substring_index() from 博客园 (opens new window)

[39] mysql根据逗号将一行数据拆分成多行数据 from CSDN (opens new window)

[40] python实现mysql插入数据时，判断是否存在，不存在就插入，重复跳过 from CSDN (opens new window)

[41] 深入理解MySQL索引原理和实现——为什么索引可以加速查询 from CSDN (opens new window)

[42] 深入浅出数据库索引原理 from 知乎 (opens new window)

[43] 数据库索引原理，读懂这篇文章就可以跟面试官掰掰手腕了 from 墨天轮 (opens new window)

[44] MySQL索引优化看这篇就够了 from 知乎 (opens new window)

[45] MySQL索引如何优化？二十条铁则送给你！from 51CTO (opens new window)

[46] 基于mysql全文索引的深入理解 from 博客园 (opens new window)

[47] BTree和B+Tree详解 from CSDN (opens new window)

[48] MyISAM与InnoDB两者之间区别与选择，详细总结，性能对比 from 博客园 (opens new window)

[49] 深入浅出数据库存储 from Github (opens new window)

[50] mysql存储过程详细教程 from 简书 (opens new window)

[51] 聊聊存储过程的优缺点以及使用场景 from Ken的杂谈 (opens new window)

[52] MySQL 存储过程 from 菜鸟教程 (opens new window)

[53] Mysql 存储过程实例详解 from 博客园 (opens new window)

[54] MySQL 存储过程实例 from begtut (opens new window)

[55] MySQL 主从架构原理 from Info (opens new window)

[56] 数据架构:从主备,主主到集群的高可用方案 from CSDN (opens new window)

[57] 关于sql和MySQL的语句执行顺序 from CSDN (opens new window)

Last Updated: 5/4/2025, 4:42:45 PM