黑马程序员 MySQL数据库入门到精通，从mysql安装到mysql高级、mys
操作数据库
内置函数
DDL操作（增删改）表和字段
DCL主要用来创建用户以及控制用户权限
DDL-表操作-修改
alter table 表名 add 字段名 类型(长度) [comment 注释] [约束]；
修改数据类型
alter table 表名 modify 字段名 新数据类型(长度);
修改字段名和字段类型
alter table 表名 change 旧字段名 新字段名 类型(长度)[comment 注释] [约束]；
删除字段
alter table 表名 drop 字段名；
修改表名
alter table 表名 rename to 新表名；
删除表
drop table [if exists] 表名；
删除指定表，并重新创建该表
truncate table 表名；
---------------------------------------------------
--------------------------------------
笛卡尔积：两张表所有数据的组合情况
select * from emp,dept where emp.dept_id = dept.id.（隐式内连接）
内连接查询两张表的交集
内连接和外连接有什么区别？
内连接查询的是多表之间的交集部分，外连接可以查询左表也可以查询右表，可以排除或包含交集。
左外连接可以改成右外连接，交换左右表就行。
自连接查询，可以是内连接查询也可以是外连接查询，用以查询两张表连接的部分，注意自连接必须取别名。
联合查询注意点 union 去重，union all 不去重，其次多张表查询的字段和列必须一样
外连接和单表查询能否相互替代？
不能，外连接是A表通过B表相关的条件来查询A表当中符合的记录。
======================================
存储引擎是基于表的不是基于库（数据库）的
Innodb和MyISAM的主要区别：
Innodb支持事务、外键，MyISAM和Memory都不支持。
Innodb支持行锁，MyISAM和Memory支持表锁
PS：Memory引擎基于内存来存储数据，由于内存断电会丢失数据，所以这些表只能作为临时表或缓存来使用。它的特点是支持hash索引
======================================
事务的4个特性：
原子性：事务是不可分隔的最小操作单位，要么全部成功，要么全部失败。
一致性：事务完成时，必须使所有的数据都保持一致状态。
隔离性：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
持久性：事务一旦提交或回滚，它对数据库的改变就是永久的。
原子性：转账过程只要有任何一步失败了，转账行为就失败了。
一致性：a账号给b账号转账，a账号不会多一块钱，b账号不会少一块钱。
隔离性：a事务和b事务是独立运行的，它们不会相互干扰。
持久性：对数据库的数据操作，影响是永久的。
并发事务问题
脏读：一个事务读到另一个事务还没有提交的数据。
不可重复读：一个事务先后读取同一个记录，但两次读取到的数据不同。
幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在。（也是明明记录不存在却无法插入的原因）
注意：脏读、幻读、不可重复读的共同点都是读到实际上不存在的数据。
各种隔离级别会出现的问题：
======================================
MyISAM使用场景：适用于以读写操作为主，少量的更新和删除操作，但现在更多用新Nosql数据库如Mongodb来满足这种需求。
Memory，通常用来做临时表或者缓存，一般可以用缓存数据库像redis缓存来满足这种需求。
=====================================
Mysql体系结构：连接层、服务层（sql解析和优化）、引擎层（不同的引擎的索引结构是不同的）、存储层（数据、索引、日志文件存储在磁盘当中）
不同的存储引擎影响数据的存储、获取、更新、查询的方式
======================================
“索引结构、索引分类、索引语法、SQL性能分析、索引使用、索引设计原则”
索引是什么？有什么作用？优缺点是什么？
索引是一种数据结构（有序），它是用来高效获取数据的。
索引可以提高查询效率和排序效率，缺点是降低了增删改的效率。
默认全表扫描
建立索引，维护索引
-------------------------------------------------------------------
B+tree索引（最常见的）、
Hash索引（不支持范围查询、只有精确匹配索引的列有效）、
R-tree空间索引（地理空间数据，用的少）、
Full-text全文索引（如倒排索引）
InnoDB支持b+tree、全文索引
MyISAM不支持hash索引，其余支持
Memory支持b+tree、hash索引，其余不支持
-------------------------------------------------------------------
二叉树缺点：顺序插入时，会形成一个单向链表，导致查询性能大大降低。大数据量情况下，层级较深，检索速度慢。
红黑树缺点：层级较深，检索速度慢。
B-Trees（多路平衡树【用key来划分范围查找】）
每个节点都会存放数据
-------------------------------------------------------------------
B+Tree
分页子节点只起到索引的作用
所有的元素都会出现在叶子节点
所有的数据都出现在叶子节点，叶子节点形成一个单向链表
MySQL索引数据结构对经典B+Tree进行了优化，在B+Tree基础上，增加了一个指向邻叶子的链表指针，形成了带有顺序指针的B+Tree，提高区间访问的性能。
======================================
hash索引结构
特点：
1.hash索引只能用于对等比较（=，in），不吹；范围查询（between，>，<）
2.无法利用索引完成排序操作
3.查询效率高，通常只需一次检索就可以，效率通常要高于B+Tree索引
InnoDB存储引擎支持自适应hash索引的功能，InnoDB根据存储引擎B+Tree索引在指定条件下自动构建hash索引。
------------------------------------------------------------------
为何mysql不选择b树？b+树有什么优势？
b树不论是叶子节点还是非叶子节点都会保存数据，这样会导致页中存储的键值减少，要保存同样的大量数据，只能增加树的高度，最后导致性能降低。
======================================
索引分类
如果表有主键，主键就是聚集索引
聚集索引下面挂的是一行数据
二级索引（也叫辅助索引一张表的第二个索引？）下面（叶子节点）挂的是该行数据的id。
“回表查询”指的是先根据二级索引找到对应的主键值，然后根据主键值再找聚集索引中的叶子节点拿到行数据
-------------------------------------------------------------------
为什么根据主键查询比非主键查询更快？
因为根据主键不用回表查询，而非主间需要先通过二级索引进行回表查询。
InnoDB主键索引的B+tree高度有多高？3层
*指针比key多一个，每一个节点能存放多少个key？
*主键占用的字节数*当前节点所能存储的key的数量*指针占用的空间
ps：公式关系即：指针所占用的空间+所有key所占用的空间=一张表的数据（3层树所承载的数据量，超过了就要增加树的深度导致性能下降）
=======================================索引语法
CREATE INDEX idx_user_name ON tb_user(name);
CREATE UNIQUE INDEX idx_user_phone ON tb_user(phone);
CRAETE INDEX idx_user_pro_age_sta ON tb_user(professsion,age,status);
CREATE INDEX idx_email ON tb_user(email);
SQL性能分析的目的是为了做SQL优化
如何查询数据库的增、删、改、查的频率？
SHOW GLOBAL STATUS LIKE 'Com____";
慢查询日志——定位SQL语句
慢查询日志的开关怎么打开？
show variable like 'slow_query_log";
修改配置文件：show_query_log=1开启
long_query_time=2 超过2秒设为慢查询
systemctl restart mysqld
tail -f localhost-slow.log
-------------------------------
profile详情
当前数据库是否支持profile查询？
select @@have_profiling;
查询profile查询范围
select @@profiling;
查询一条sql每个耗时阶段可不了解
explain执行计划
explain | desc + sql语句 即可分析sql语句
explain执行计划各字段的含义：
id表示不同SQL的执行顺序，id相同排前面先执行，id值不同，值大先执行
type表示连接类型，性能由好到差依次是
NULL、system、const、eq_ref、ref、range、index、all
possible_key表示可能用到的索引
key表示实际用到的索引
主要关注的字段如下表：
======================================
索引的使用规则
最左前缀法则，要想用到联合索引就得保证查询条件最左边的列存在（如where id=“” and name="" and ...），如果不存在，联合索引就会失效，如果中间跳过某一列，跳过的列会引起后面的索引列失效。
范围查询（右侧的列索引失效），联合索引中出现范围查询(<,>)，范围查询右侧的列所有失效（如where id=“” and age>30 and sex=0），但范围查询加等号如大于等于，右侧的索引就不会失效，业务允许的话可以加。
不要在索引列做运算，否则索引列将失效，比如对索引列进行函数计算substring（phone，0，2）
数值字符串不加引号导致索引失效
模糊查询，左模糊索引失效（%工程），右模糊不失效（软件%），左右模糊（%工程%）也失效。
or连接的条件，or连接的列只要有一个没有建立索引就会导致or关联的列索引失效
数据分布影响（特殊规则）
1.如果走索引的速度不如全表扫描就不会走索引
2.“is null”和“is not null”会不会走索引都不是固定的，数据库会根据数据分布情况来决策，取快优先
SQL（索引使用）提示，提示数据库使用某个索引或忽略某个索引或强制使用某个索引
eg:“select * from tb_user use index(idx_user_pro) where...." 建议数据库使用指定的索引
“select * from tb_user ignore index(idx_user_pro) where...."
“select * from tb_user force index(idx_user_pro) where...." 强制数据库使用指定的索引
覆盖索引，尽量使用覆盖索引（覆盖索引即查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到），避免使用select*（原因是全字段查询可能会涉及到二级索引没有覆盖的字段，导致回表查询降低了查询性能。）。
注意：聚集索引的叶子节点下储存的是行数据，辅助索引的叶子节点下储存的是id。
如果经过辅助索引的查询，又经过聚集索引来查询，就是回表查询。
实践（也是面试题）：
select id,username,pwd from tb_user where username="zhangsan";如何优化？
前缀索引，是提取索引的一部分前缀来建立索引， 它是用来解决长字符串或者大文本字段在整个字段进行索引的时候，索引的体积庞大造成浪费大量磁盘IO的情况，使用前缀索引可以降低索引的体积来提高索引效率。
eg：create index idx_xxx on tb(column(n));
前缀长度考量，可以根据不重复的索引值和数据表的记录总数的比值来决定，这种选择性越高查询效率越高。唯一索引的选择性是1，是性能最好的。
eg：select count(distinct email)/count(*) from tb;
select count(distinct substring()email)/count(*) from tb;
截取前9个字符测试选择性。
根据业务需求，如果要求选择性高，前缀就取长一点。
-------------------
单列索引&联合索引
数据库会优先使用单列索引，这可能会导致回表查询，因此我们在建立索引的时候应该优先考虑创建联合索引，避免使用单列索引，在创建联合索引的时候需要考虑索引的顺序，必须保证最左侧的列存在
=====================================
=====================================
SQL优化
- insert优化
- 主键优化
- order by优化
- group by优化
- limit优化
- count优化
- update优化
大批量插入load，默认关闭
--------------------------------------------------------------
由于在InnoDB存储引擎中表数据 都是根据主键的顺序来存放的，所以乱序插入数据时可能导致页分裂，删除数据时可能导致页合并。
--------------------------------------------
排序优化
创建索引时默认采用升序排列，如果排序时使用降序会使索引失效。
解决：
create index idx_user_age_pho_ad on tb_user(age asc, phone desc);
order by优化
- 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
- 尽量使用覆盖索引。
- 多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC)。
- 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size(默认256K）
------------------------------------------
-------------------------------------------------
limit优化
通过覆盖索引+子查询的方式来优化
eg：explain select * from tb_sku t,(select id from tb_sku order by id limit 2000000,10) a where.id=a.id;
eg2:select s.* from tb_sku s,(select id from tb_sku order by id limit 9000000,10) a where si.id=a.id;
count优化
- MyISAM引擎把一个表的总行数存在了磁盘上，因此 执行count(*)的时候会直接返回这个数，效率很高；
- InnoDB引擎执行count(*)的时候需要把数据一行一行地从引擎里面读出来，然后计数。
优化思路：自己维护计数
count的几种用法
count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果count函数的参数不是null，累计值就加1，否则不加，最后返回累计值。
用法：count(*)（性能最高） 、count(主键)、count(字段)、count(1)
-------------------------------------------------------
update优化
InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁。（id也是索引，所以id是行锁）
-------------------------------------------------------
总结
======================================
mysql存储对象——视图/存储过程/触发器
视图是一种虚拟表，不保存数据，只保存SQL逻辑
语法：create view view_name as sql语句
视图的作用：针对的是数据库用户（数据库维护角度）
---------------------------------------
存储过程，用以优化发送sql网络IO，减少网络交互
对SQL语句进行封装以便重用
存储函数只入参不出参，但有return返回值，存储过程能够完全取代存储函数，至于存储函数强制要求返回值，这既是优点也是缺点，但没有存储过程可以灵活选择。
------------------------------------
触发器的作用是监控数据库表中行的操作，可以把监控的结果记录到日志表当中。
总结：
======================================
数据库锁
锁是计算机协协调多个进程或线程并发访问某一资源的机制。由于数据是一种共享资源，因此并发访问会带来一致性和有效性的问题，而锁是影响并发访问性能的一个重要因素。
锁分类
全局锁，锁住数据库的所有表，数据库实例加锁，在备份的时候可加全局锁，只提供访问，不支持增删改操作。
表级锁，每次操作锁住整张表。
行级锁，每次操作锁住对应的行数据。
全局锁实现数据备份：
加锁：flush tables with read lock;
解锁：unlock tables;
不加锁实现数据备份：
mysqldomp --single-transation -h 192... db01 > 地址
-----------------------------------
表级锁分类：表锁、元数据锁、意向锁
表锁又分两类：表共享读锁、表独占写锁
语法：
1.加锁：lock tables 表名 read/write.
2.释放锁：unlock tables
读锁不会阻塞其他客户端的读，但会阻塞写，写锁既会阻塞其他客户端的读，也会阻塞其他客户端的写。（两方都不能写）
写锁会阻塞其他客户端的读写，不会阻塞自己的读写
----------------------------------
元数据锁
为了避免DML与DDL冲突，保证读写的正确性。
加锁是系统自动控制的
----------------------------------
意向锁（自动加）
用以解决InnoDB引擎下加表锁与行锁的冲突
使得使用表锁不用检查每行数据是否加锁，减少了表锁所需的逐行检查。
分类：
意向共享锁（IS）：与表锁共享锁兼容，与表锁排他锁互斥。
语法 select……lock in share mode添加；
意向排他锁（IX）：与表锁共享锁及排他锁都互斥，意向锁之间不会互斥。
crud……for update 添加；
-----------------------------------------------------------------
行级锁
分类：行锁、间隙锁、临建锁
行锁的分类
共享锁（s）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。
排它锁（x）：允许获取排它锁的事务更新数据，阻止其他事务获取相同数据集的共享锁和排它锁。
增删改自动加排它锁，读不加任何锁，
共享锁实现：SELECT&LOCK IN SHARE MODE
排它锁实现：SELECT&FOR UPDATE
------------------------------------
间隙锁和临键锁的唯一目的是防止其他事务插入间隙造成幻读现象，间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。
间隙锁锁的是间隙不包含对应的数据记录，而临键锁既会锁定包含的对应记录也会锁定该数据记录之前的部分间隙。
关于间隙锁和临键锁只要知道它为什么要加就行了。
-------------------------------------
总结：
======================================
InnoDB引擎详解
- 逻辑存储结构
- 架构
- 事务原理
- MVCC
逻辑存储结构
架构
---------------------------------------------------------------
----------------------------------------------------------------
后台线程
当我们业务在操作的时候那么会直接操作缓冲区，如果缓存区没有数据会将磁盘中的数据加载回来，然后再存储在缓冲区当中，我们在增删改查的时候都会去操作缓冲区，然后缓冲区的数据会以一定的频率或时机要通过后台线程去刷新到磁盘当中，然后在磁盘当中进行永久化的保存。
--------------------------------------------------------------------
事务原理
事务是一组操作的集合，这些操作要么同时成功，要么同时失败。
原子性：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
一致性：事务完成时，必须使所有的数据保持一致的状态。
隔离性：数据库提供的隔离机制来保证事务不受外部并发操作影响可在独立环境下运行。
隔离级别：读未提交、读已提交、可重复读（innodb默认）、串行化
持久性：事务一旦提交或回滚，对数据库中的数据的改变是永久的
事务原理：
事务的原子性、一致性、持久性是由redo log和undo log 来保障的，而隔离性是通过锁机制和MVCC（多版本并发控制）来保障的。
持久性是有redo log重做日志来保证的，重做日志由两部分组成分别是“重做日志缓冲”和“重做日志文件”，前者在内存中，后者在磁盘中，当事务提交时会把所有修改信息都存到该日志文件中，用于在刷新脏页到磁盘发生错误时进行数据恢复。
小结：redo log就是为了保证我们在进行脏页刷新发生错误时进行数据恢复，从而保证事务的持久性。
脏页：在内存中修改的数据页。
缓冲区的脏页直接刷新到磁盘中不好吗为什么还要通过redo log缓冲区去异步刷新？（性能差别大）
事务一般都会涉及操作随机数据页，因此这种操作会产生大量的随机磁盘IO，性能是很地下的，如果我们在进行操作的时候用到redo log不会把胀页直接刷新，先把日志异步刷新到磁盘中，
由于日志文件都是追加的，所以它是顺序磁盘IO，那么它的性能是要高于随机磁盘IO，这种机制就叫WAL（Write-Ahead Logging先写日志），过一段时间再将脏页数据刷入磁盘中。
----------------------------------------
undo log
它是用来解决失误的原子性，undo log也叫回滚日志，记录数据修改前的信息，主要的作用是提供回滚和多版本并发控制
undo log记录的是逻辑日志（记录原始数据），redo log记录的是物理日志
----------------------------------------
MVCC
基本概念
多版本并发控制是维护一个数据的多个版本使得读写操作没有冲突，具体实现还依赖三个隐式字段、undo log日志、readView。
当前读，即读取到的是最新的数据，加锁
快照读，即读取的到数据可能是最新数据，不加锁
----------------------------------------
事务id，回滚指针、隐藏主键
------------------------------------------
undo log 回滚日志
在插入数据时产生数据回滚日志。提交事务后会立即删除操作日志，而在修改和删除不会被立即删除，因为在快照读时还需要用到。
undo log版本链
当我们在进行查询的时候到底应该返回哪一个版本？这个不是由版本链来控制的，而是由readview来控制的。
------------------------------------------
readview
主要是用于记录并维护系统当前活跃的事务（未提交的）id。
mvcc提取数据的是依据以下四个核心字段
版本链数据访问规则不用记
----------------------------------------
总结：
=====================================
总结：
存储引擎、索引、常见SQL的优化、数据库对象（视图、存储过程、存储函数、触发器）、锁（全局锁、表级锁、行级锁）、InnoDB引擎
======================================
运维篇
- 日志
- 主从复制
- 分库分表
- 读写分离
日志
- 错误日志
- 二进制日志
- 查询日志
- 慢查询日志
查看错误日志：
show variables like '%log_error%'
查看错误日志前50行：
tail -50 /var/log/mysqld.log
--------------------------
二进制日志包含了DDL（SQL定义语句）和DML（增删改语句）语句
作用是1.灾备恢复 2.主从复制
show variables like '%log_bin%'
默认的日志格式是ROW，记录每一行的日志记录
--------------------------
查询日志
默认关闭(忙时很占用磁盘空间)，开启方式
在my.cnf文件添加general_log=1
general_log_file=mysql_query.log
-------------------------------------------------------------------主从复制
主从复制是指将主数据库的DDL和DML操作通过二进制日志传到从库服务器中，然后在从库上重新执行这些操作日志，从而使得从库与主库的数据保持同步。
增删改操作主库，查询请求操作从库，可降低主库的压力。
在从库进行备份，用全局锁去锁从库，主库依然可以进行增删改的相关操作，但数据同步可能会存在一定的延迟。
主从复制的原理
主库的数据是怎么同步到从库当中？
主库会把所有DML和DDL操作写入binlog日志，在从数据库当中有两个线程，其中一个IO线程就会去连接主库读取其中的binlog日志然后写入从库，这份写入的日志也叫中继日志，然后从库的另一个SQL线程会来读取中继日志，把中继日志中的数据变化反应到自身的数据变化，这样保证了主从数据数据同步。
主从复制-主库配置
- 修改配置文件配置读写权限
- 开放指定端口号
- 授予主从复制权限
- 查看二进制日志坐标
主从复制-从库配置 参考主库配置
重启mysql服务的指令：systemctl restart mysqld
===================================
分库分表
Mycat入门
Mycat配置
Mycat分片
Mycat管理及监控
用以解决性能瓶颈（IO瓶颈和CPU瓶颈）
分库和分表是粒度的不同，垂直拆分和水平拆分是维度的不同。
垂直分库垂直分表，水平分库水平分表
实现技术
Mycat不存储数据，只是处理分片数据
逻辑库配置
Mycat启动命令：bin/mycat start ，默认端口8066
------------------------------------
逻辑表
Mycat分片
跨库查询有会什么问题？
设置全局表可以跨库查询
按范围分片规则
分片规则（数据插入的分配规则）——
- 范围
- mod-long（取模），根据指定字段值与节点数量进行求模运算，根据运算结果来决定数据属于哪个分片。
- 一致性hash
- 枚举分片
- 应用指定算法
- 固定hash算法
- 字符串hash解析
- 按天分片
- 按自然月分片
这些分片规则需要了解它们的适用场景和特点
分片规则分类
主键为数字类型的字段适用范围、取模
主键为字符串适用一致性hash、固定hash
日期类型适用按自然月、按天
----------------------------------
Mycat管理与监控原理
--------------------------------------
Mycat管理工具
mycat的自带管理指令使用不方便，建议使用监控工具
---------------------------------------------------
读写分离
- 一主一从
- 一主一从读写分离
- 双主双从
- 双主双从读写分离
读写分离基于主从复制
增删改走主节点，查询走从节点
balance取值分配
===================================
课程总结：
