多表查询
1、隐式内连接与显式内连接（交集）
select * from emp e,dept d where e.dept_id =d.id; select * from emp join dept on emp.dept_id=dept.id;2、左外连接和右外连接
左外连接：左表+交集
3、自连接
自己连接自己进行查询，可以内连接/外连接
select a.name,b.name from emp a,emp b where a.managerid=b.id;4、联合查询 union/union all
把多次查询结果合并起来，形成一个新的查询结果集
select * from emp where salary<5000 union select * from emp where age>50;union查询的结果进行去重 union直接进行合并
对于联合查询：多张表列数必须保持一致，字段类型保持一致
5、子查询
标量子查询 >、<、=、>=、<=、<>(相当于!=)
select * from emp where dept_id=(select id from dept where name='销售部' );列子查询 in、not in、any（子查询返回列表中，有任意一个满足就行）、some、all（子查询返回列表全部满足才行）
select * from emp where dept_id in (select id from dept where name='销售部' or name='市场部' );行子查询
select * from emp where (salary,managerid)=(select salary,managerid from emp where name='张无忌');表子查询 子查询返回的结果是多行多列
select * from emp where (job,salary) in (select job，salary from emp where name='鹿杖客' or name='宋远桥'); select e.*,d.* from (select * from emp where entrydata>'2016-01-01') e left join dept d on e.dept_id=d.id;
存储引擎
1、MySQL体系结构
连接层：
最上层是一些客户端和链接服务，主要完成一些连接处理、授权认证、及相关的安全方案。
服务层：
第二层架构主要完成大多数核心服务功能，如SQL接口，并完成缓存查询，SQL的分析和优化。所有跨缓存储引擎的功能，如过程、函数等。
引擎层：
负责MySQL中数据的存储，建立索引，更新和查询数据等技术的实现。 不同存储引擎索引不同。
存储层：
将数据存储到文件系统上，并完成与存储引擎的交互。（文件和日志：redo，undo在这里）
2、存储引擎
存储引擎是存储数据、建立索引、更新和查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，也就是同一个数据库的不同表可以选择不同存储引擎。存储引擎也被称为表类型。
show engines;
InnoDB存储引擎
DML操作遵循ACID模型，支持事务（DML数据的增删改）
行级锁
支持外键
支持B+索引
逻辑存储结构：段页式存储 表空间-段-区-页-行
对应磁盘文件：tableName.ibd
每张表对应这样一个ibd表空间文件，存储表的结构、数据和索引
MyISAM存储引擎
不支持事务，不支持外键
支持表锁，不支持行锁
支持B+索引
访问速度快
Memory存储引擎
存储在内存中，只能将这些表作为临时表或缓存使用
默认hash索引，支持B+索引
3、存储引擎的选择
InnoDB：如果应用对事务的完整性，并发条件下数据的一致性有比较高的要求
MyISAM：应用以增查为主，很少更新、删除，并对事务完整性、并发性要求不高。被Mongodb替代
Memory：被redis替代
索引
1、索引概述
索引：帮助mysql高效获取数据的有序的数据结构
2、索引结构
B+Tree索引
Hash索引：底层使用hash表，只支持精确匹配，不支持范围查询
R-Tree空间索引：MyISAM引擎的特殊索引类型，主要用于地理空间数据类型
Full-text全文索引：通过建立倒排索引，快速匹配文档。类似于es
B Tree-多路平衡查找树
树的度数：子节点的个数
一个度为5的B 树，每个节点最多有4个key（能存储4个值），5个指针
插入元素中间元素向上分裂
B+ Tree
所有元素存储在叶子节点
叶子节点形成一个单项链表
非叶子节点存储索引
MySQL索引数据结构对B+ Tree进行优化，将叶子节点单向链表升级为双向链表，每个节点是一个页
为什么选择B+ Tree
相较于二叉树，层级更少搜索效率高
相较于B Tree，无论是叶子节点还是非叶子节点，都会存储数据，这样导致一页中存储的key和指针减少，只能增加树的高度，导致性能降低。
相较于hash索引，B+Tree支持范围匹配及排序操作。
3、索引分类
在InnoDB存储引擎中，根据索引的存储形式，又可以分为：
主键索引 针对表中主键创建的索引 默认自动创建，只能有一个 primary 唯一索引 避免同一个表中某数据列中的值重复 可以有多个 unique 常规索引 快速定位特定数据 可以有多个 全文索引 查找文本中的关键字，而不是比较索引中的值 可以有多个 fulltext
聚集索引
（Clustered Index）
将数据存储与索引放到了一起，索引结构的叶子节点保存了行数据 必须有，只能有一个 二级索引
（Secondary Index）
将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键 可以有多个 聚集索引选取规则：
如果存在主键，主键索引就是聚集索引
如果不存在主键，将使用第一个唯一索引作为聚集索引
如果都没有，InnoDB会自动生成一个rowid作为隐藏的聚集索引
select * from user where name='Arm';
name字段是二级索引，先查询二级索引找到对应的主键值，在回表查询走聚集索引通过id找到所有数据
4、索引语法
create [unique|fulltext] index index_name on table_name(index_col_name...)
如果只有一个字段称为单列索引，如果多个字段称为联合索引
show index from table_name;
drop index index_name on table_name;5、SQL性能分析
查看数据库insert、update、delete、select访问频次
show global status like 'Com_______'
①慢查询日志
#开启慢查询日志 slow_query_log=1 #设置慢日志时间 long_query_time=2②profile详情
能在做sql优化时，了解时间都耗费到哪里了
#开启 set profiling=1 #查看全部sql耗时情况 show profiles; #查看指定query_id的sql语句各个阶段的耗时情况 show profile for query query_id; #查看指定query_id的SQL语句cpu使用情况 show profile cpu for query query_id;③explain执行计划
explain或则desc命令获取MySQL如何执行select语句执行计划。
语法：在select语句前面加explain或desc
explain执行计划各字段含义：
id select查询的序列号，表示查询中操作表的顺序（id值相同，执行顺序从上到下；id值不同，值越大，越先执行） select_type 表示select查询的类型，常见有simple（简单表，即不使用表连接或子查询）、primary（主查询，即外层的查询）、union（union中的第二个或后面的查询语句）、subquery（语句中包含了子查询） type sql连接的类型，性能由好到坏为null、system、const、eq_ref、ref、range、index、all
null（不查询表）、system（查询系统中的表）、const（单表根据主键查询或唯一索引查询）、eq_ref（联表查询根据主键或唯一索引）、ref（索引查询）、range（范围查询）、index（索引树扫描）、all（全盘扫描）
possible_key 可能用到的索引 key 实际用到的索引 key_len 表示索引中使用的字节数，该值为最大可能长度，并非实际使用长度 ref 当使用索引列等值查询时，与索引列进行等值匹配对象信息 rows 执行查询的行数，预估值 filtered 返回结果长度占需要读取行数的百分比，越大越好 Extra 额外的优化建议
Using where/Using Index 查找使用了索引，需要的数据都在索引列中能找到，不需要回表查询
Using index condition 查找使用了索引，但是需要回表查询
6、索引使用
最左前缀法则
联合索引最左边的列必须存在，与放的位置无关，如果跳过了某一列，后面的索引失效
联合索引 A=1 B=2 C=3 select * from table where A='' and B='' and C=''; 全部正常大小1+2+3 select * from table where B='' and A='' and C=''; 全部正常大小1+2+3 select * from table where A='' and C=''; C失效，因为没B，大小1 select * from table B='' and C=''; 全部失效，必须有A才走索引
索引失效的情况
1、联合索引中，出现范围查询（> <），范围查询右侧索引失效
select * from table where A='' and B>10 and C='' 此时，C失效规避：尽量使用>=或<=
2、在索引上运算，索引失效
select * from user where substring(phone,10,2)='15'; 手机号后两位是15的用户：此时phone的唯一索引将失效3、字符串字段不加引号，索引失效
4、头部模糊匹配，索引失效，尾部模糊匹配，不会失效
有索引 select * from user where profession like "软件%"; 索引失效 select * from user where profession like "%软件";5、or连接的条件：如果一侧有索引，一侧没有索引，索引失效，只有两边都有才走所索引
6、数据分布影响：如果MySQL评估使用索引比全表更慢，则不使用索引
7、两个单列索引
name和phone是单列索引 select id,name,phone from user where phone='333' and name='lee'; 此时会走phone的单列索引，name不会走索引，会发生回表查询
SQL提示
建议MySQL使用/忽略索引：
use index:建议使用 select * from user use index(index_name)where profession=''; ignore index:忽略 force index必须
索引使用原则
1、覆盖索引
尽量使用覆盖索引（查询中使用了索引，并且需要返回的列，在索引中已经全部找到），减少使用select * （select * 很容易出现回表查询）
覆盖索引不需要回表查询 eg：name,age是联合索引，二级索引 select name，age，id from table where name='lee' and age=89 ; 根据name和age查询，走二级索引，二级索引的key是name和age，节点保存的就是id（主键），不需要回表查询。 select name,id,age,gender from table where name='lee'; 根据name查询，走二级索引，但是找不到gender，需要根据id走聚集索引，这样发生了回表查询。2、前缀索引
当字段类型为（varchar，text）时，有时候需要索引很长的字符串，会让索引变得很大，查询时，浪费大量磁盘io，影响查询效率。此时，可以将字符串一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。
create index index_name on table_name(column_name(pre_index));
SQL优化
1、插入数据
1、批量插入代替单次插入：每次单次插入会与数据库建立连接进行网络传输
2、手动事务提交：多次插入数据后统一提交事务
3、主键顺序插入：主键按顺序插入优于乱序
4、大批量数据插入：load指令
2、主键优化
在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（index organized table IOT）。
1、插入数据时，尽量选择主键顺序插入，顺序插入会依次填充页，乱序插入可能产生页分裂现象。当删除某个值时，并不会直接从物理上删除，而是标记为删除允许被其他记录声明使用，当删除的记录达到阈值（默认50％）时，会发生页合并。
2、尽量不使用uuid做主键，因为其无序，其他自然主键如身份证号也不使用
3、避免对主键修改。
3、order by优化
①Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都是FileSortpaix
②Using index：通过有序索引顺序扫描直接返回有序顺序。
create index idx_user_age_phone_ad on user(age asc,phone desc); #根据age升序排序，当age相同时，根据phone降序排序 explain select id,age,phone from user order by age asc,phone desc;1、根据排序字段建立合适的索引，多字段排序遵循最左前缀法则，但是最左列必须方法最左边
2、使用覆盖索引
3、多字段排序，一个升序一个降序，注意联合索引创建规则（asc/desc）
4、如果不可避免出现filesort，大数据排序时，可适当增加排序缓冲区大小sort_buffer_size(默认246k)
4、group by优化
使用索引，最左前缀法则
5、limit优化
分页越往后，查询时长越大
select * from user limit 2000000,10;当limit 1000000，10时，MySQL会先扫描满足条件的1000010行，扔掉前面的1000000行，返回后面的10行，所以扫描行越多，效率越慢。
优化：使用覆盖索引和子查询
select * from user limit 1000000,10; select * from user u1,(select id from user order by id limit 1000000,10) u2 where u1.id=u2.id;
6、count优化
1、select *和select全部字段 区别
没区别，select * 会增加查询分析器解析成本
2、count(*)、count(1)、count（字段）区别
count(*)和count(常量)会统计null
count(字段)不会统计null
MyISAM引擎把一个表的总数放在了磁盘上，因此执行count(*)会直接返回这个数，效率高
InnoDB引擎，执行count(*)时，需要把数据一行一行取出来，然后累计计数
7、update优化
更新条件要为索引字段，否则会从行锁升级为表锁
name没有索引： update user set name='lee' where name='jack'（此时id是2）; 此时由于name没有索引，会锁整张表 update user set name='li' where id=4; 不会更新成功，因为表锁没有释放 create index idx_user_name on user(name) ; update user set name='lee' where name='jack'; 此时，锁的是这一行，其他更新语句可以执行InnoDB的行锁，是为索引加的锁，不是为记录加的锁。
锁
1、概述
锁是计算机协调多个进程或线程并发访问某一资源的机制
分类：
全局锁：锁定数据库中所有表
表级锁：每次操作锁定整张表
行级锁：每次操作锁定对应行数据
2、全局锁
使用场景：全库数据备份，加锁之后只能读，不能写
#加全局锁 flush tables with read lock; #数据备份 mysqldump -uroot -p123456 database_name>file_name.sql #解锁 unlock tables;
3、表级锁
分类：
1、表锁
2、元数据锁（meta data lock，MDL）
3、意向锁
表锁
分为两类：
表共享读锁（read lock）
表独占写锁（write lock）
#加锁 lock tables 表名 read/write #释放锁 unlock tables/客户端断开连接读锁：客户端1加了读锁，客户端1及其他客户端都能读取，都不能写
写锁：客户端1加了写锁，客户端1能够读写，其他客户端不能读写
元数据锁
MDL 加锁过程系统自动控制，在访问一张表时会自动加上。为了避免DML（表数据增删改）与DDL（表结构的创建、修改、删除）冲突。（简单的说，crud时不能修改表结构）
意向锁
为了避免DML（insert、delete、update）执行时，加的行锁与表锁的冲突，在InnoDB中引入意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查.
分类：
意向共享锁（IS）：由select...lock in share mode添加
意向排他锁（IX）：由insert、delete、update添加
兼容性与互斥性：
意向共享锁：与表锁读锁兼容，与表锁写锁互斥
意向排他锁：与表锁读锁、写锁都互斥。意向锁之间不会互斥。
4、行级锁
每次操作锁定对应行的数据。应用于InnoDB存储引擎。
InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁实现的，而不是对记录加锁。
分类：
行锁（Record Lock）：锁定单行记录，防止其他事务进行update和delete。在rc、rr隔离级别下支持。解决了脏读、不可重复读
间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在rr隔离级别下都支持。
临键锁（Next-Key Lock）：行锁+间隙锁的结合（该行记录及左边间隙）。在rr隔离级别下支持
InnoDB提供两种类型的行锁
共享锁（S）：事务1获得了一行数据的共享锁，能进行读操作，事务2也能获得这行数据的共享锁，但不能获得排他锁
排他锁（X）： 事务1获得一行数据的排他锁，能进行写操作，但其他事务不能获得读锁和写锁。
语句加锁情况：
insert、update、delete 自动加排他锁
select 不加任何锁
select...lock in share mode 共享锁
select...for update 排他锁
间隙锁和临键锁
默认情况下 ，InnoDB在Repeatable read事务隔离级别运行，InnoDB使用next-key锁进行搜索和索引扫描，以防止幻读
InnoDB的行锁是针对索引加的锁，不通过索引条件索引数据，InnoDB会将行锁升级为表锁
索引上的等值查询（唯一索引），给不存在的记录加锁时，优化为间隙锁。
update user set name='lee' where id=5;（没有主键为5的） 此时，4和6之间加的就是间隙锁，防止insert导致幻读索引上等值查询（普通索引），向右遍历时最后一个值不满足查询要求时，临键锁退化为间隙锁
#id=2 age=2；id=3 age=3 ；id=4 age=4；age字段是普通索引 select * from user where age=3 lock in share mode; 会将id为3的数据加行锁，将23数据加临键锁，将34之间加间隙锁索引上范围查询（唯一索引）
#id:1~10~15 select * from user where id>=10 lock in share mode; id为10的加行锁，id为11的加临键锁，11之后到正无穷加临键锁
事务原理
事务
事务是一组操作的集合，是一个不可分割的工作单位，事务会将所有操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。
特性：ACID
原子性：事务是不可分割的最小单元，要么同时成功，要么同时失败
一致性：事务完成时，所有数据必须保持一致状态
隔离性：数据库系统提供隔离机制，保证事务在不受外部环境并发操作影响的独立环境下运行（隔离级别：读未提交、读已提交、可重复读、串行化）
持久性：事务一旦提交或回滚，对数据的影响是永久的
如何保证事务特性：
原子性：undo log
持久性：redo log
一致性：undo log和redo log
隔离性：锁和MVCC
redo log
重做日志，记录的是事务提交时数据页的物理修改，用于实现事务的持久性。
该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log file），前者是在内存中，后者在磁盘中。当事务提交后会把所有修改信息都保存到该日志文件中，用于刷新脏页到磁盘，发生错误时，进行数据恢复使用。
MySQL的InnoDB引擎中分为内存结构和磁盘结构，磁盘结构存放数据文件（xxx.ibd）,内存结构中有缓冲池，缓冲池中缓冲了数据页的信息。当客户端发起了事务操作（update等），首先在缓冲池中查找，有没有需要操作（更新）的数据，没有的话有磁盘文件加载到缓冲池，接下来直接操作缓冲区中的数据，并将数据页的变化记录到redo log buffer中，当事务提交时，redo log buffer中的记录直接刷新到磁盘中。在择机将buffer pool中的脏数据刷新到磁盘中，如果此过程中出错，就用redo log file进行数据的恢复。
为什么每次提交时不直接将buffer pool中变更的数据刷新到磁盘，而是需要redo log？
答：性能问题 。redo log是日志文件，日志是追加的，是顺序磁盘io。而直接刷新脏数据是随机磁盘io，性能不好。
undo log
回滚日志，用于记录数据被修改前的信息，作用包含两个：提供回滚 和 MVCC(多版本并发控制)
undo log销毁：undo log在事务执行时产生，事务提交时，并不会立即删除undo log，这些日志可能还作用于MVCC
undo log存储：undo log采用段的方式进行管理和记录，存放在回滚段（rollback segment）中，内部包含1024个undo log段。
MVCC
当前读
读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如：select...lock in share mode(共享锁),select...for update、update、insert、delete（排他锁）都是一种当前读。
快照读
简单的select就是快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。
Read Committed：每次select，都生成一个快照读
Repeatable Read：开启事务后第一个select语句是生成快照读，第二次查询的是缓存数据。
Serializable：快照读会退化为当前读。
MVCC
多版本并发控制（Multi-Version Concurrency Control）。指维护一个数据的多个版本，使得读写操作没有冲突，快照读为MySQL实现MVCC提供一个非阻塞读的功能。MVCC的具体实现依赖于数据库记录中三个隐藏字段、undo log日志、readView。
MVCC实现原理
记录中的隐藏字段
创建表时，MySQL会自动加上三个隐藏字段:
DB_TRX_ID 最近修改事务ID，记录插入这条记录或者最后一次修改该记录的事务ID DB_POLL_ID 回滚指针，指向这条记录的上一个版本，用于配合undo log，指向上一个版本 DB_ROW_ID 隐藏主键，如果表结构没有指定主键，将生成该隐藏字段
readView
ReadView（读视图）是快照读SQL执行时MVCC提供数据的依据，记录并维护系统当前未提交的事务id。
ReadView包含四个核心字段：
m_ids 当前活跃的事务id集合 min_trx_id 最小活跃事务id max_trx_id 预分配事务id，当前最大事务id+1（因为事务是自增的） creator_trx_id ReadView创建者的事务id
不同的隔离级别，生成ReadView的时机不同：
Read Committed:在事务中每一次执行快照读时生成ReadView
Repeatable Read:仅在事务第一次执行快照读时生成ReadView，后续复用该ReadView
1280
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
