1. 左连接、右连接、等值连接
left join on , right join on
* on子句连接条件，把表中等值的记录连接在一起，但是不影响记录集合的数量
若是left_table中某记录无法在right_table找到，则以NULL代替
* where子句控制记录是否符合查询要求，过滤
on是局部条件，where全局条件
inner join on与where一致过滤（涉及优化）
2. SQL优化
a)显性连接优化
等值连接：显性连接(inner join on)和隐性连接(where)
* 隐性连接，在from过程中对所有表进行笛卡尔积，最终通过where过滤
select * from student s, SC sc, Course c
where s.id = sc.id and sc.cId = c.id
* 显性连接，在每次表连接时通过on过滤，筛选后的结果集合再和下一个表笛卡尔积，以此循环
select *
from (student inner join SC on s.id = sc.id)
inner join Course on c.id = sc.cId
b)驱动表
驱动表是由结果集的数据量来决定的。
* 指定了连接条件时，满足查询条件的记录行数少的表为驱动表
* 未指定连接条件时，行数少的表为驱动表
要想效率高，就是要以小结果集驱动大结果集
MySQL下：
(1)关键字EXPLAIN：为SQL出现的每张表返回一行信息来说明数据库优化器如何操作这张表
EXPLAIN
SELECT *
FROM ...
(2)关键字STRAIGHT_JOIN：按照我们要求的from表顺序执行
EXPLAIN
SELECT <STRAIGHT_JOIN> *
FROM ...
注意：应避免查询缓存，所以执行前手动清除（RESET QUERY CACHE）。
3. 事务特性(ACID)
原子性(atomic)：要么都执行，要么都不执行。
一致性(consistency)：从一个一致性状态到另一个一致性状态
隔离性(isolation)：多个并发事务之要相互隔离
持久性(durability):对数据的改变是永久的
4. 数据库隔离级别
多个事务读可能导致以下问题：
脏读：事务A读取事务B还没有提交的数据
不可重复读：一行被检索两次，并且该行中的值在不同的读取之间不同
幻读：当在事务处理过程中执行俩个相同的查询，并且第二个查询返回的行集合与第一个查询不同（两次读中间差有数据操作，不可重复读是修改了数据，幻读是插入了数据）
丢失更新 脏读 不可重复读 幻读
读未提交： 可能发生 可能发生 可能发生 可能发生
读已提交： 可能 不可能 可能 可能
可重复读： 不可能 不可能 不可能 可能
可串行化： 不可能 不可能 不可能 不可能
5. MySQL两种存储引擎的区别(事务、锁级别等等)，各自的适用场景
MYISMA：不支持外键，表锁
插入数据时，锁定整个表，查表总行数时，不需要全表扫描
INNODB:支持外键，行锁
查表总行数时，全表扫描
6. 索引有B+树索引和hash索引
hash：等值查询效率高，不能排序，不能进行范围查询
B+树：数据有序，范围查询
7. 聚集索引和非聚集索引
聚集索引：数据按索引顺序存储，子节点存储真实的物理数据
非聚集索引:存储指向真正数据行的指针；
8. 索引优缺点，什么时候用或不用
优点：提高查询效率
缺点：更新数据时效率低，因为要同时更新索引
对数据进行频繁的查询时建议使用索引，频繁的更新数据不建议使用索引
9. InnoDB索引和MyISMA索引的区别
a)主索引的区别：InnoDB的数据文件本身就是索引文件，而MyISMA的索引和数据是分开的
b)辅助索引的区别：InnoDB的辅助索引data域存储相应记录
10. 索引的底层实现（B+树，为何不用红黑树，B树） 重点**
红黑树：增加、删除时，红黑树会进行频繁的调整，来保证性质，费时
B树（B-树）：查询性能不稳定，查询结果高度不一致，每个节点保存指向真实数据的指针，相比B+树，每一层每个屋存储的元素更多，显得更高
B+树：相较于其他两树，显得更矮更宽，查询层次更浅
11. B+树的实现
m阶B+树的特征：
a)有k个子树的中间节点包含有k-1个元素(B树是k-1个元素)，每个元素不保存数据，只用来索引，所有数据都保存在叶子节点中
b)所有的叶子节点中包含了全部元素信息，及指向含这些元素记录的指针，且叶子节点本身依照关键字的大小 自小到大顺序连接
c)所有的中间节点元素都同时存在于子节点，在子节点元素中是最大（最小）元素
12. 为什么使用B+树
索引查找过程中会产生磁盘IO消耗，主要看IO次数，和磁盘存取原理有关
13. 锁
a)乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。乐观锁不能解决脏读的问题。 乐观：每次拿数据都认为别人不会修改，所以不上锁，但是更新的时候会判断一下在此期间别人有没有更新这个数据，可以使用版本号等机制。
乐观锁适用于多读的应用类型，可以提高吞吐量。
b)悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。
悲观，每次拿数据都认为别人会修改，所以每次都上锁，这样别人想拿这个数据就会阻塞，直到拿到锁。例如：行锁，表锁，读写锁等都是。
f)共享锁(读锁)：多个事务，只能读不能写，加lock in share mode
c)排他锁(写锁)：一个事务，只能写，其他事务不能读写，for update
d)行锁：作用于数据行
e)表锁：作用于表
14. 死锁解决
找到进程号， kill 进程
MYSQL数据库优化----------------------20200908
1.MySQL版本
2.MySQL底层原理：逻辑分层，数据库引擎
3.SQL优化：sql优化和索引的关系
4.索引
5.SQL性能问题，优化方法
6.优化案例
7.避免索引失效（优化失效）的一些原则
8.一些其他优化方法
9.SQL排查-慢查询日志
10.分析海量数据
11.锁机制：解决因资源共享而造成的并发问题
12.主从复制
1.逻辑分层：连接层、服务层、引擎层、存储层
select
连接层：提供与客户端连接的服务
服务层：提供各种用户使用的接口（select *）
提供SQL优化器（MySQL Query Optimizer），优化器进行优化
引擎层：提供各种存储数据的方式（innoDB、MyISAM）
InnoDB(默认）：事务优先，适合高并发操作，行锁
MyISAM：性能优先，表锁
存储层；存储数据
2.查询数据库引擎： 支持哪些引擎？ show engines;
查看当前使用的引擎： show variables like '%storage_engine%';
指定数据库对象的引擎：
create table tb(
id int(4) auto_increment,
name varchar(5),
dept varchar(5),
primary key(id)
)ENGINE=MyISAM, AUTO_INCREMENT=1
DEFAULT CHARSET=utf8;
3.SQL优化：
原因：性能低，执行时间太长、等待时间太长、sql语句太差（尤其连接查询）、索引失效、服务器参数设置不合理（缓冲区、线程数）
a. SQL编写过程：select dinstinct.. from .. join .. on .. where .. group by .. having .. order by .. limit
解析过程：from .. on .. join .. where.. group by .. having..select dinstinct..order by .. limit..
文章：www.cnblogs.com/annsshadow/p/5037667.html
b. SQL优化主要在于优化索引
索引：相当于书的目录
索引：关键字index是帮助高效获取数据的数据结构，索引是数据结构（树：B树、Hash树） MySQL是B+树
索引的缺点:
1. 索引本身很大，可以存储在内存/硬盘（通常在硬盘）
2. 索引不是所有情况都适合 （1）少量数据（2）频繁更新的字段（3）很少使用的列
3. 索引可以提高查询效率，会降低增删改的效率
索引优点：
1. 提高查询效率（本质是降低了IO的使用率）
2. 降低CPU使用率（.. order by ， 加了索引本身已经排好序的结构，因此排序时候可直接使用，不需要排序CPU使用率就降低了）
3层B+树就可以存放上百万条数据，数据都放在叶子节点
B+树中查询任意的数据次数：n次（B+树的高度）
4. 索引
分类：
单值索引：单列， 一个表可以有多个单值索引
唯一索引：唯一索引不能重复，（id）
复合索引：多列构成的索引（书的二级目录：找赵, 先找z,再zhao)，不是必须同时用（最左匹配）
创建索引：
方式一：create 索引类型 索引名 on 表（字段）
单值索引： create index dept_index on tb(dept);
唯一索引： create unique index name_index on tb(name);
复合索引： create index dept_name_index on tb(dept, name);
方式二：alter table 表 add 索引类型 索引名（字段）
单值索引： alter table tb add index dept_index(dept);
唯一索引： alter table tb add unique index name_index(name);
复合索引： alter table tb add index dept_name_index(dept, name);
DDL语句会自动提交 commit;
注意：如果一个字段是主键，默认是主键索引（唯一索引且不为NULL)
删除索引：drop index 索引名 on 表名
drop index name_index on tb;
查询索引： show index from 表;
5. SQL性能问题：
a. 分析SQL的执行计划：explain关键字，可以模拟SQL优化器执行SQL语句
b. MySQL查询优化器会干扰你们的优化
优化方法：官网（dev.mysql.com/doc/refman/5.5/en/optimization.html
explain select * from tb;
id：编号,大的优先查询
select_type：查询类型
table：表
type：类型
possible_keys：预测可能用到的索引
key：实际用到的索引
key_len：实际用到索引的长度
ref：表之间的引用
rows：通过索引查到的数据个数
Extra：额外的信息
准备数据：
create table course(
cid int(3),
cname varchar(20),
tid int(3)
);
create table teacher(
tid int(3),
tname varchar(20),
tcid int(3)
);
create table teacherCard(
tcid int(3),
tcdesc varchar(200)
);
insert into course values(1, 'java', 1);
insert into course values(2, 'html', 1);
insert into course values(3, 'sql', 2);
insert into course values(4, 'web', 3);
insert into teacher values(1, 'tz', 1);
insert into teacher values(2, 'tw', 2);
insert into teacher values(3, 'tl', 3);
insert into teacherCard values(1, 'tzdesc');
insert into teacherCard values(2, 'twdesc');
insert into teacherCard values(3, 'tldesc');
查询课程编号为2或教师证编号为3的老师信息
select t.* from teacher t, course c, teacherCard tc where t.tid=c.tid and t.tcid=tc.tcid and (c.cid=2 or tc.tcid=3);
(1)id值相同：从上往下顺序执行 t3-tc3-c4
teacher 插入三条后：tc3-c4-t6
表执行顺序因数量的个数改变而改变的原因：笛卡尔积
2 * 3 = 6 * 4 = 24
4 * 3 = 12 * 2 = 24 结果一样，中间不一样
数据小的表优先查询
(2)id值不同，越大越优先查询(本质：嵌套子查询时，先查内层)
查询教授sql课程老师的描述（desc）
explain select tc.tcdesc from teacherCard tc,teacher t, course c where tc.tcid = t.tcid and c.tid = t.tid and c.cname='sql';
将以上多表查询变为子查询形式：
explain select tc.tcdesc from teacherCard tc where tc.tcid =
(select t.tcid from teacher t where t.tid = (select tid from course where cname = 'sql'));
explain select t.tname, tc.tcdesc from teacher t, teacherCard tc where t.tcid = tc.tcid and t.tid = (select c.tid from course c where cname = 'sql');
(3)id有相同又有不同：id值越大越优先，id值相同上到下顺序执行
select_type:
PRIMARY：包含子查询SQL中的主查询（最外层）
SUBQUERY：包含子查询的子查询（非最外层)
SIMPLE：简单查询（不包含子查询、union）
DERIVED：衍生查询（在查询时用到了临时表）
a.from子查询，有且只有一张表
select cr.cname from (select * from course where tid in (1, 2)) cr;
b.在from子查询中，如果table1 union table2,则table1是DERIVED, table2是union
select cr.cname from (select * from course where tid = 1 union select * from course where tid = 2) cr;
UNION：上例b.
UNION RESULT：告知开发人员哪些表之间存在union查询
table：查询的表
type：查询类型
system > const > eq_ref > ref > fulltext > ref_of_null > index_merge > unique_subquery > index_subquery > range > index > all;
企业常用: system > const > eq_ref > ref > range > index > all，要对type进行优化的前提，有索引！！
其中system，const理想情况，实际能达到ref>range
system：只有一条数据的系统表，或者 衍生表只有一条数据的主查询（偶尔）
增加索引:alter table test01 add constraint id_pk primary key(id);
explain select * from (select * from test01)[衍生表] where id = 1;
const:仅仅能查到一条数据的SQL，用户primary key 或者unique索引
eq_ref：唯一性索引，对每个索引键的查询，返回匹配唯一行数据（每条有且只有一个，不能多，不能0）
select .. from .. where name = .. 常见于唯一索引和主键索引
teacher tcid 加唯一索引， tc.tcid加主键索引
select t.tcid from teacher t, teacherCard tc where t.tcid=tc.tcid;
以上SQL，用到的索引是t.tcid，即teacher表中的tcid字段；如果teacher表的数据个数和连接查询的数据个数一致（这里都是三条），则有可能满足eq_ref；
ref：非唯一性索引，对于每个索引键查询，返回匹配的所有行（0，多个）
insert into teacher values(4, 'tz', 4);
insert into teacherCard values(4, 'tz44');
测试:
alter table teacher add index name_idx(tname);
select * from teacher where tname='tz';
range：检索指定范围的行，where后面是一个范围查询（between，in:有时候会失效，转为无索引ALL，>< >=)
select * from teacher where tid < 3; (tid in (1, 2))
index：查询全部索引中的数据
select tid from teacher; -- tid是索引
all：查询全部表中的数据
select cname from course; -- cname不是索引，需要扫描全表
总结： system/const：结果只有一条数据
eq_ref：结果多条，但每条数据是唯一的
ref：结果多条，但每条数据是0条或者多条
possible_keys：可能用到的索引，预测的，可能不准
key：实际使用到的索引
key_len：索引的长度
name char(20), 若长度为0则索引没用，若为20则用了
作用：用于判断复合索引是否被完全使用
MySQL里面utf8一个字符占三个字节，gbk占两个字节，latin占一个
name char(20) not null 20*2=40
name1 char(20) 20*2+1=41 1个字节标识null
如果索引字段可以为null，则会用一个字节用于标识
drop index name1_idx on testkl;
增加复合索引：alter table testkl add index name_name1_idx(name, name1);
select * from testkl where name=''; --40
select * from testkl where name1=''; --81
varchar(20) --可变长长度用2个字节标识
alter table testkl add column name2 varchar(20);
alter table testkl add index name2_idx(name2);
select * from testkl where name2 = ''; --43 20*2+1+2
ref：注意与type中的区分，作用：指明当前表所参照的字段
select .. where a.c = b.x（引用b的x）; （其中b的x可以是常量const）
select * from course c, teacher t where c.tid=t.tid and t.tname='tw';
rows:被索引优化查询的数据个数
select * from course c, teacher t where c.tid = t.tid and t.tname = 'tz';
Extra：
using filesort(出现，标识性能消耗大，需要‘额外’的一次排序（查询）)，常见于order by语句中。
create table test02(
a1 char(3),
a2 char(3),
a3 char(3),
index idx_a1(a1),
index idx_a2(a2),
index idx_a3(a3)
);
select * from test02 where a1 ='' order by a1;
查完之后排序，正常
select * from test02 where a1 ='' order by a2;出现filesort
查完a1，根据a2排，先查了a2在排序，所以多一次
小结：对于单索引，如果排序和查询是同一字段，不会出现filesort
避免：where哪些字段就obder by字段
复合索引：原则不能跨列（最佳左前缀）
alter table test02 add index idx_a1a2a3(a1, a2, a3);
select * from test02 where a1='' order by a3; (跨了a2，出现filesort)
select * from test02 where a2='' order by a3; (跨了a1)
select * from test02 where a1='' order by a2; (没有filesort)
小结：避免,where和order by按照复合索引的顺序使用，不要跨列和乱序使用
using temporary:性能损耗大，用到了临时表。一般出现在group by语句中
select a1 from test02 where a1 in ('1','2','3') group by a1;
select a1 from test02 where a1 in ('1','2','3') group by a2;(出现) 要按a2分组
避免：查哪些列就用哪些列分组
解析过程：from on join where groupby having select order by limit
using index：性能提升，索引覆盖(覆盖索引)。不读取源文件，只从索引文件中获取数据，只要使用到的列都在索引里（不需要回表查询），就是索引覆盖using index
对possible_keys和key有影响，有where两者都有，否则key里有
select a1,a2 from test02 where a1='' or a2='';
select a1,a3 from test02 where a1='' or a3='';
using where（需要回原表查询）
impossible where：where语句永远false
using join buffer：作用，MySQL引擎使用了连接缓存
create table test03(
a1 int(3) not null,
a2 int(3) not null,
a3 int(3) not null,
a4 int(3) not null,
index idx_aaaa(a1, a2, a3, a4)
);
select a1,a2,a3,a4 from test03 where a1=1 and a3=3 and a4=4;
a1有效，跨a2，所以a3，a4索引失效，需回表查using where
where和order by拼起来看是否跨列，如果跨列则using filesort;
总结：（1）如果复合索引和使用的顺序全部一致，则复合索引全用，若部分一致（且不跨列使用），则使用部分索引
（2）where和order by拼起来，不要跨列使用
优化示例：
1.单表优化：
（1）索引不能跨列使用（最佳左前缀），保持索引的定义和使用的顺序一致性
（2）逐步优化
（3）将含in的范围查询，放到where最后，防止索引失效
2.两表优化
（1）小表驱动大表，索引建立在经常使用的字段
where 小表.ID = 大表.ID
.. on t.id = c.id将数据量小的表放左边
3.三表优化：
（1）小表驱动大表
（2）索引建立在常用的字段上
避免索引失效的一些原则：
（1）复合索引，不要跨列或无序使用（最佳左前缀）
（2）尽量使用全索引匹配
（3）不要再索引上进行任何操作（计算，函数，类型转化）
select .. where A.x*3=..;
（4）索引不能使用不等于(!= <>)或者is (not) null，否则自身以及右侧失效
（5）复合索引中有>号，失效
SQL优化是一种概率层面的优化
（6）一般情况，范围查询（< > in）索引，本身有用，之后的失效
（7）补救，尽量使用覆盖索引
select a,b,c from xx where a=.. and b=..
（8）like尽量以“常量”开头，不要以‘%’开头，否则索引失效
like ”1%“ like ”%1%“，
如果必须like %1%， 可以使用索引覆盖挽救一部分
select name from .. where name like "%1%";
（9）尽量不要类型转换（显式、隐式），索引失效
（10）尽量不使用or，索引失效，左边的也干掉了
一些其他的优化u方法：
（1）exist，in
如果主查询数据集大，使用in，否则exist效率高
（2）order by优化
using filesort：有两种算法（双路排序、单路排序）根据IO次数
MySQL4.1之前默认双路：扫描两次磁盘（1.从磁盘读取排序字段，对排序字段进行排序（再缓存区中排序） 2.扫描其他字段）
MySQL4.1之后默认单路（IO消耗性能）：一次读取全部字段，在buffer中进行排序，但单路排序会有一定隐患（有可能多次IO）数据量太大，缓存区容量不足，无法一次性读完，因此会”分片读取、多次读取“。
单路比双路占用更多buffer，单路排序如果数据大，可以考虑调大buffer的容量大小：set max_length_for_sort_data = 1024;
如果max_length_for_sort_data值太低，MySQL自动从单路->双路（太低：需要排序的列总大小超过了定义的字节数）
a.选择单路、双路，调整buffer容量大小
b.避免select *， 不索引覆盖了
c.复合索引，不跨列，避免using filesort
d.保证排序字段的排序一致性，全升、全降
SQL排查：
慢查询日志：MySQL提供的一种日志记录，用于记录响应时间超过阈值的SQL语句（long_query_time，默认10秒）
慢查询日志默认关闭，建议：调优时打开，最终部署时关闭
检查是否开启了慢查询日志： show variables like '%slow_query_log%';
临时开启：set global slow_query_log = 1; --在内存中开启
重启mysql服务恢复默认 service mysql restart;
临时设置阈值：set long_query_time=10；完毕后重登起效
show global status like '%slow_queries%';
单值索引、和复合索引举例
索引失效的原理：
like失效原理
or失效原理
复合索引失效原理
讲讲平衡二叉树、B树、B+树
B+树：双向链表
InnoDB是聚簇索引，MyISAM是非聚簇索引
索引查找流程
索引查找流程
索引失效原理
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
