MySql高级
MySql基础
数据库的概述：是一个数据管理系统
存储：按照特定的规则存储
管理：sql增删改查操作
数据库的类型：
关系型数据库
非关系型数据库
数据库的环境搭建：
安装服务器端（sqlyog）
可视化客户端工具
sql：
DDL：创建数据表结构
DML：对数据的增，删，改
DQL：对数据的查询（查询结果的处理：排序，多表关联，条件查询，模糊查询，数量限制，分组，函数）
MySql高级
MySql引擎
引擎就是数据库在处理数据时的机制
不同的引擎执行策略/机制不同
不同的需求选用不同的引擎，达到最优选择
查询mysql支持的引擎 Show Engines；
修改引擎
方式1：将mysql.ini中default-storage-engine=InnoDB，重启服务.
方式2：（建表时修改） Create table 表名（…）Engine = MYSIAM;
方式3：（建表后修改） Alter table 表名 Engine = INNODB；
InnoDB
InnoDB是一个事务型的存储引擎，有行级锁和外键约束
特点：处理大容量数据库系统，支持事务，并发大，支持外键约束，主键自增（以前版本不支持全文检索）
适用场景：需要数据库事务时，经常更新的表，处理多重并发的更新请求，只有他支持外键;支持自增列属性auto_increment。
MyISAM
当Insert（插入）或Update（修改）数据时，即写操作需要锁定整个表，效率会低一些。查询快
特点：不支持事务，不支持外键，不支持行级锁
适用场景：读操作远远大于写操作且不需要数据库事务的支持
查询快
索引
方便数据的查询类似于书中的目录
创建索引的目的是为了优化数据库的查询速度。其中，用户创建的索引指向数据库中具体数据所在位置。当用户通过索引查询数据库中的数据时，不需要遍历所有数据库中的所有数据。这样，大幅度提高了查询效率。
索引类似于书的目录，在一本书前面加上目录，查找内容时不必逐页翻阅就能够快速地找到所需的内容。借助索引，执行查询时不必扫描整个表就能够快速地找到所需要的数据。
优点： 方便查询，降低了IO成本
缺点： 索引也需要占用磁盘空间，同时会降低表的更新速度，修改，删除，新增时需要对索引进行维护（例：删除某条 数据时，索引也要做出改变）
索引分类：
主键索引：设定为主键后数据库会自动建立索引
Alter table 表名 add Primary Key 表名（列名）；
删除主键索引：
Alter table 表名 drop Primary Key；
单值索引：即一个索引只包含单个列，一个表可以有多个单列索引，
创建单值索引：
Create Index 索引名 on 表名（列名）；
删除索引：
Drop index 索引名；
唯一索引：索引列的值必须是唯一，允许为null
创建单值索引：
Create Unique index 索引名 on 表名（列名）；
删除索引：
Drop index 索引名；
复合索引：一个索引包含多个列 ，在数据库操作期间，复合索引比单值索引所需要的开销更小(对于相同的多个列建索引),当表的行数远大于索引列的数目时可以使用复合索引
创建符合索引：
Create index 索引名 on 表名（列1，列2，……）；
查看表的索引 Show index from 表名；
索引创建原则：
适合创建索引的情况：
- 主键
- 外键
- 频繁作为查询条件的字段（where后的语句）
- 排序字段
不适合创建索引的情况：
- 表记录太少（例：年级表）
- 经常增删改的表
- 不做为查询条件的（where条件用不到的）
- 重复数据非常多的（例：性别）
索引数据结构：
索引数据结构：B+Tree
B+树：一个结点可以存储多个数据，非叶子结点只存储索引，不存数据
所有数据都在叶子结点存储，而且数据与数据之间有连接（像链表一样）
叶子结点之间有连续性，适合范围查询（ id>15）
树的高度固定，查询快
不用哈希结构：虽然快，但是散列的不连续，条件范围查询不适应（例：id>10）
拿主键索引举例：如果使用树，红黑树（深度太大，查询慢）
聚簇索引：
找到了索引就找到了数据，索引和数据一一对应，主键就是聚簇索引
select No，name，sex from student where id = ‘10’；使用主键索引，直接就能找到数据
不需要回表查询的也是聚簇索引（直接找到）
select No from student where No= ‘10’；使用非主键索引查询，结果只是查询索引列，没有查询其他列。
使用非主键索引查询结果只是查询索引列，没有查询其他列
非聚簇索引：
回表查询的（查两次）
索引的存储和数据的存储是分离的，先通过非主键索引查询到主键，再通过主键回表查询到需要的数据
select No，name，sex from student where No = ‘10’；使用非主键索引查询，结果还要查询本列的其他数据，需要通过主键回表查询
事务
就是一次对数据库的一个完整的操作过程，这个过程中包含一次或多次操作，要么都完成，要么都失败，如果事务在操作的过程中发生错误，会被回滚到事务开始前的状态，就像这个事务从来没有被执行过一样。
提交事务： 一次对数据库的整个操作完成了，保证对数据库操作的原子性。
只有使用INNODB引擎的数据库和表才支持事务。
事务用来管理insert，update，delete语句
作用：用来维护数据库的完整性
事务特性
- 原子性：要么都成立，要么都失败，保证操作的原子性
- 持久性：事务提交后，数据在硬盘上定格，不能回滚
- 隔离性：多事务并发执行时，隔离机制
- 一致性：在事务开始之前到事务结束之后（多次操作之后），最终数据与预期值一致（原子性，持久性，隔离性都是为了保证一致性）
事务设置
默认情况下,MySQL启用自动提交模式（变量autocommit为ON）。这意味着,只要你执行DML操作的语句,MySQL会立即隐式提交事务。
session 会话系统变量
global 全局系统变量
1.直接用SET来改变MySQL的自动提交模式:
set session / global autocommit = 0； 禁止自动提交
set session / global autocommit = 1； 开启自动提交
2.当禁止自动提交时，用BEGIN,ROLLBACK,COMMIT来手动实现
begin；/ start transaction；开始一个事务
insert into dept (name) value ('人事部')；对数据库的操作语句
rollback； 事务回滚（将上面的sql执行结果取消，回到事务开始前的状态）
commit； 提交事务
并发事务处理带来的问题
脏读：A事务读到B事务未提交事务的数据，一旦B回滚，A事务读到的就是脏数据。
不可重复读：A事务第一次读取数据后，B 事务对数据进行了修改并提交事务，A事务又进行了第二次读取，两次读取的数据结果不一致。
脏读与不可重复读的区别：
脏读：未提交的数据
不可重复读：已提交的数据
幻读：在事务A中按照某个条件先后两次查询数据库，两次查询结果的条数不同
不可重复读与幻读的区别
可以通俗的理解为：前者是数据变了，后者是数据的行数变了。
事务隔离级别
INNODB的事务隔离级别：
读未提交（read uncommitted）：A事务读到B事务未提交的数据，B事务一旦回滚，A事务就读到脏数据
带来问题：脏读，幻读，不可重复读
读已提交（read committed）：A事务读到B事务已提交的数据
解决脏读问题
带来问题：不可重复读（前后两次读到的数据不一致）
可重复读（repeatable read MySql 默认隔离级别）：同一个事务中多次读取相同的数据返回的结果是一样的。
解决了脏读和不可重复读
带来问题：幻读
串行化（serializable）：当一个事务在执行时，会把整个表锁住，其他事务不能操作。
避免了以上所有问题
锁机制
表锁：隔离级别为串行化时使用表级锁
行锁：当隔离级别不能串行化，多个事务对同一个数据进行写操作时，会把这一行数据锁住。
InnoDB：支持表锁，行锁
MyISAM：只支持表锁
以下以InnoDB为例：
行锁
只对当前操作的行进行加锁。
特点：开销大，加锁慢，会出现死锁，锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
共享锁（S）：（又称读锁）读锁可以同时 有多个事务查询，在S锁释放之前不能对数据对象进行修改。
加共享锁使用语句：select … lock in share mode;
排它锁（X）：（又称写锁）update, delete, insert都会自动给涉及到的数据添加排它锁，只能有一个事务对此行操作。
select语句默认不会加任何锁类型，如果加排他锁可以使用：select … for update；
表锁
对当前操作的整张表加锁。
特点： 开销小，加锁快，不会出现死锁，锁定粒度大，发出锁冲突的概率最高，并发度最低
MVCC
MVCC（多版本并发控制 Multi-Version Concurrent Control）是MySql提高性能的一种方式，
使用Undo log记录版本链，，它的聚簇索引记录中都包含两个必要的隐藏列。
trx_id：每次对某条聚簇索引记录进行改动时，都会把对应的事务id赋值给trx_id隐藏列。
roll_pointer：每次对某条聚簇索引记录进行改动时，都会把旧的版本写入到undo日志中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息。
对该记录每次更新后，都会将旧值放到一条undo日志中，就算是该记录的一个旧版本，随着更新次数的增多，所有的版本都会被roll_pointer属性连接成一个链表，我们把这个链表称之为版本链，版本链的头节点就是当前记录最新的值。另外，每个版本中还包含生成该版本时对应的事务id，这个信息很重要。
ReadView
修改，删除前都需要进行版本记录，回滚时需要并发读
读未提交：是从版本链中读取，而且读取的是最新版本（此版本可撤销）
串行化：加锁了直接读表中的数据。
读已提交：每次读取数据前，都会生成ReadView（将所有事务操作记录在版本链中拍了个照），造成了不可重复读。
可重复读：第一次读取时生成了ReadView，解决了不可重复读
视图
视图是基于查询的虚拟表。通俗的理解，视图就是一条SELECT语句执行后返回的结果集。
SELECT语句所查询的表称为视图的基表，而查询的结果集称为虚拟表，视图本身并不存储具体的数据，视图的数据存在于视图的基表中，基本表数据发生了改变，视图的数据也会跟着改变。
使用视图是为了方便复杂的查询语句。基本思路是将复杂的查询语句定义在视图内部，然后对视图进行查询，从而简化复杂的查询语句。
定义视图
create view 视图名 as 查询语句
使用视图
select * from 视图名
删除视图
drop view 视图名
存储过程
如果实现用户的某些需求时，需要编写一组复杂的SQL语句才能实现，那么可以将这组复杂的SQL语句集编写在数据库中，由JDBC调用来执行这组SQL语句。把编写在数据库中的SQL语句集称为存储过程。
存储过程（PROCEDURE）是事先经过编译并存储在数据库中的一段SQL语句的集合。调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。
存储过程类似于JAVA语言中的方法，需要先定义，使用时需要调用。存储过程可以定义参数，参数分为IN、OUT、INOUT类型三种类型。IN类型的参数表示接收调用者传入的数据，OUT类型的参数表示向调用者返回数据，INOUT类型的参数既可以接收调用者传入的参数，也可以向调用者返回数据。
MySQL定义存储过程
创建存储过程的语法格式
语法解析：
1.存储过程的参数分为in，out，inout三种类型。
2.in代表输入参数（默认情况下为in参数），表示该参数的值必须由调用程序指定。
3.out代表输出参数，表示该参数的值经存储过程计算后，将out参数的计算结果返回给调用程序。
4.inout代表即是输入参数，又是输出参数，表示该参数的值即可以由调用程序指定，又可以将inout参数的计算结果返回给调用程序。
5.存储过程中的语句必须包含在begin和end之间。6.declare中用来声明变量,变量默认赋值使用default,语句块中改变变量值,使用set变量=值;
存储过程的使用
定义一个存储过程：
定义一个有参存储过程：
流程控制语句
if else：
case when：
循环：
使用存储过程插入信息：
在Java中调用存储过程
在mybatis中调用存储过程
jdbcType必须指定
函数
函数语法
注意：
1.参数列表包含两部分：参数名参数类型
2.函数体：肯定会有return语句，如果没有会报错
3.函数体中仅有一句话，则可以省略 begin end
4.使用 delimter 语句设置结束标记
设置函数可以没有参数
SET GLOBAL log_bin_trust_function_creators = TRUE;
删除函数
DROP FUNCTION 函数名;
不带参数：
带参数并在查询语句中调用函数：
触发器
触发器（trigger）是一种特殊的存储过程，其特殊性在于它并不需要用户直接调用，而是在对表添加、修改、删除之前或者之后自动执行的存储过程。
触发器具有以下特点：
1.与表相关联
触发器定义在特定的表上，这个表称为触发器表。
2.自动激活触发器
当对表中的数据执行INSERT、UPDATE或DELETE操作时，如果对表上的这个特定操作定义了触发器，该触发器自动执行，这是不可撤销的。
3.不能直接调用
与存储过程不同，触发器不能被直接调用，也不能传递或接受参数。
4.作为事务的一部分
触发器与激活触发器的语句一起做为对一个单一的事务来对待，可以从触发器中的任何位置回滚。
定义触发器的语法规则：
语法解析：
1.触发器名称：是用来标识触发器的，由用户自定义。
2.触发时机：其值是before或after。
3.触发事件：其值是insert，update和delete
4.表名称：标识建立触发器的表名，即在哪张表上建立触发器
5.语句：是触发器程序体，触发器程序可以使用begin和end作为开始和结束，中间包含多条语句；
案例删除用户时,自动触发删除用户菜单关系
新增用户时，自动向其他表插入数据
在行级触发器代码中，可以使用old和new访问到该行的旧数据和新数据，old和new是对应表的行记录类型变量。
Sql优化
为什么要对sql进行优化
随着业务数据量的增多，SQL的执行效率对程序的运行效率的影响逐渐增大，此时对SQL的优化就很有必要。
SQL优化的一些方法
1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
2.应尽量避免索引失效
2.1在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num = 0
2.2应尽量避免在where子句中使用 != 或 < > 操作符，否则将引擎放弃使用索引而进行全表扫描。
2.3应尽量避免在where子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num = 10 or num = 20
2.4 in 和 not in 也要慎用，否则会导致全表扫描，如：
select id from t where num in (1,2,3)
对于连续的数值，能用 between 就不要用 in 了：
select id from t where num between 1 and 3
2.5下面的查询也将导致全表扫描：
select id from t where name like '%abc%'
2.6应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where num/2=100
应改为:
select id from t where num = 100*2
2.7应尽量避免在 where 子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where substring(name,1,3) = 'abc' --name以abc开头的id
应改为:
select id from t where name like 'abc%'
3.索引并不是越多越好，索引固然可以提高相应的select的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。
一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
4.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。
这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
5.尽可能的使用 varchar 代替 char，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
6.任何地方都不要使用 selec * t from t，用具体的字段列表代替“*”，不要返回用不到的任何字段。
7.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。
执行计划
使用 EXPLAIN 关键字可以模拟优化器执行SQL语句，分析查询语句或是结构的性能瓶颈。在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询会返回执行计划的信息，而不是执行SQL。
EXPLAIN SELECT * FROM USER WHERE id = 1
测试期间设置关闭对衍生表的合并优化：
SET SESSION optimizer_switch = 'derived_merge = off';
EXPLAIN SELECT t.* FROM (SELECT * FROM USER u WHERE u.type = 1) t
expain 出来的信息有12列：
id:选择标识符
select_type:表示查询的类型。
table:输出结果集的表
partitions:匹配的分区
type:表示表的连接类型
possible_keys:表示查询时，可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
ref:列与索引的比较
rows:扫描出的行数(估算的行数)
filtered:按表条件过滤的行百分比
Extra:执行情况的描述和说明
id
SELECT 识别符。这是 SELECT 的查询序列号 id 如果相同，可以认为是一组，从上往下顺序执行；
在所有组中，id 值越大，优先级越高，越先执行。
select_type
表示查询中每个select子句的类型
(1)SIMPLE(简单SELECT，不使用UNION或子查询等)
(2)PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为 PRIMARY)(3)UNION(UNION中的第二个或后面的SELECT语句)
(4)DEPENDENTUNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)
(5)UNIONRESULT(UNION的结果，union语句中第二个select开始后面所有select)
(6)SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)
(7)DEPENDENTSUBQUERY(子查询中的第一个SELECT，依赖于外部查询)
(8)DERIVED(派生表的SELECT,FROM子句的子查询)
(9)UNCACHEABLESUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)
type
对表访问方式，表示 MySQL 在表中找到所需行的方式，又称“访问类型”。常用的类型有：ALL、index、range、ref、eq_ref、const、system、NULL（从左到右，性能从差到好）
ALL ：Full Table Scan，MySQL将遍历全表以找到匹配的行
index : Full Index Scan，index 与 AL L区别为 index 类型只遍历索引树
range : 只检索给定范围的行，使用一个索引来选择行
ref : 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
eq_ref : 类似 ref ，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用 primary key 或者 unique key 作为关联条件
const 、system : 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system 是 const 类型的特例，当查询的表只有一行的情况下，使用 system
NULL : MySQL 在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。
ref
列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
