简介
mysql是一个高性能的、多线程、关系型数据库。
1.事务的ACID
这个先说一下ACID，必须得知道：
（1）Atomic：原子性，就是一堆SQL，要么一起成功，要么都别执行，不允许某个SQL成功了，某个SQL失败了，这就是扯淡，不是原子性。
（2）Consistency：一致性，这个是针对数据一致性来说的，就是一组SQL执行之前，数据必须是准确的，执行之后，数据也必须是准确的。别搞了半天，执行完了SQL，结果SQL对应的数据修改没给你执行，那不是坑爹么。
（3）Isolation：隔离性，这个就是说多个事务在跑的时候不能互相干扰，别事务A操作个数据，弄到一半儿还没弄好呢，结果事务B来改了这个数据，导致事务A的操作出错了，那不就搞笑了。
（4）Durability：持久性，事务成功了，就必须永久对数据的修改是有效的，别过了一会儿数据自己没了，不见了，那就好玩儿了。
2.隔离级别的理解
脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中
脏写就是说我两个事务来写同一行数据，一个事务写数据h被另一个事务回滚，导致数据错了
幻读是指查到了之前没有的一批数据
（1）读未提交，Read Uncommitted：这个很坑爹，就是说某个事务还没提交的时候，修改的数据，就让别的事务给读到了，这就恶心了，很容易导致出错的。这个也叫做脏读。是不允许发生脏写的，可能发生脏读，不可重复读，幻读。
（2）读已提交，Read Committed（不可重复读）：这个比上面那个稍微好一点，但是一样比较尴尬，就是说事务A在跑的时候， 先查询了一个数据是值1，然后过了段时间，事务B把那个数据给修改了一下还提交了，此时事务A再次查询这个数据就成了值2了，这是读了人家事务提交的数据啊，所以是读已提交。这个也叫做不可重复读，就是所谓的一个事务内对一个数据两次读，可能会读到不一样的值。不会发生脏写和脏读 RC
（3）可重复读，Read Repeatable（默认隔离级别）：这个就是比上面那个再好点儿，就是说事务A在执行过程中，对某个数据的值，无论读多少次都是值1；哪怕这个过程中事务B修改了数据的值还提交了，但是事务A读到的还是自己事务开始时这个数据的值。
会发生幻读的
（4）串行化：幻读，不可重复读和可重复读都是针对两个事务同时对某条数据在修改，但是幻读针对的是插入，比如某个事务把所有行的某个字段都修改为了2，结果另外一个事务插入了一条数据，那个字段的值是1，然后就尴尬了。第一个事务会突然发现多出来一条数据，那个数据的字段是1。如果要解决幻读，就需要使用串行化级别的隔离级别，所有事务都串行起来，不允许多个事务并行操作。
3.表字段设计
1.小数数字类型：对精度有要求比较敏感类业务，例如金额之类的可以用 decimal（精度30位），float 4bytes（精度7位） double 8bytes（精度15）
2.整数 ： tinyint -128 ~ 127，smallint （-32768,32767）保存长度5位，INT可以存10位长度，bigint 则是 极大整数 一般不会用到
3.字符：char是定长的其余空间用空格补，适合存储所有值都接近同一个长度，不确定长度通常都用varchar（255字节），text更长 2的16次方，BLOB 类型区分大小写，而 TEXT 不区分大小写
4.时间：datetime 和 timestamp （选）类型所占的存储空间不同，前者8字节，后者4字节，timestamp 显示与时区有关，datetime可以保存的时间跨度更大，但是timestamp一般够了
5.尽量设置 not null，不能使用=,<,>这样的运算符，null比空字符串需要更多的存储空间等。
4. 数据库三大范式
第一范式(1NF)：保持列原子性，每个列都不可以再拆分。
第二范式(2NF)：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。
第三范式(3NF)：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。
MVCC机制 多版本并发控制 Multi-Version Concurrency Control
1.首先他是基于undo log 版本链的，那undo log 版本链是 每个事务都会在undo log 中 生成一条记录，这条记录会中有自增id字段，事务创建id，事务删除id（null 或者 删除的事务创建id），在版本链里面新的事务会指向上个事务（用来回滚），一个事务创建后 每次查询就会根据事务创建id去查询undo log，查询的机制就是 事务创建id <= 当前事务id < 事务删除id， 就是说 我只能查询的到 在当前事务之前的数据 并且 是 当前事务之后删除的。
简述在MySQL 数据库中 MyISAM 和InnoDB 的区别
InnoDB 存储引擎
undo log 是数据发生更新的时候 会对旧数据保存的一个备份，以免发生数据丢失，那产生的流程是什么样的？ 这个要从 InnoDB 存储引擎内部原理讲起。
一个sql执行的时候：
process on 图流程
索引的查询过程，数据都是保存在数据页的，那数据页的结构是什么？
主键的索引实际上就是主键目录（索引页中 ，索引页就是每个树非叶子节点），这个主键目录呢，就是把每个数据页的页号，还有数据页里最小的主键值放在一起，组成一个索引的目录，如下图所示。
所以我们用索引根据索引目录就可以直接找到数据页（二分法）
数据根据索引的大小排序分布在数据页中，数据页之间是双链表，数据页中有两个部分，一个是页目录，一个是数据槽，找一条数据就是先找到数据页再根据页目录（二分法）直接定位到数据槽，然后就可以获取数据了。
所以当你的索引不是自增而是乱序，那么就是出现 数据槽的移动，页目录的变动，数据页双链表的变动 这个就是页分裂。所以这条的性能是比较差的。
B+树 --》 索引页 -- 》数据页号 -- 》数据页 -- 》页目录--主键--》槽位 --》 数据
B+树的节点其实就是索引页（最小id值 对应 数据页号）
各个树结构
二叉树 查询 更新 的时间复杂度均为O（log(N)）
小的放右边节点，大的放左边节点，容易引起树结构的倾斜，这样查询效率就比较低。
红黑树，平衡二叉树
在二叉数的特点上在加了一个规则 就是每个节点的左右子树的高度差不能超过1.
单旋转（左旋或者右旋），双旋转
https://blog.csdn.net/javazejian/article/details/53892797
B树
节点可以有m颗树
概括来说是一个节点可以拥有多于2个子节点的二叉查找树
因为B树节点元素比平衡二叉树要多，所以B树数据结构相比平衡二叉树数据结构实现减少磁盘IO的操作。
因为一个节点可以有多个节点二叉树，所以层级少，所以可以减少io次数
B+树
规则：
1.根节点是黑色 并且 叶子节点是空的也是黑色
2.红色节点下的节点是黑色 红黑相间
3.一个节点下的黑色节点数量要一样
与B树的区别是：
1.非叶结点仅具有索引作用，跟记录有关的信息均存放在叶结点中。
2.树的所有叶结点构成一个有序链表，可以按照关键码排序的次序遍历全部记录。
3.叶子结点相当于是存储（关键字）数据的数据层；
B+树叶子节点是有序相邻的，相邻的元素可能在内存中不相邻，而B树则需要进行每一层的递归遍历，所以缓存命中性没有B+树好。
因为非叶子节点不存数据，所以B+树使用的空间更小，叶子节点有序所以范围查询性能好
B+树 --》 索引页 -- 》数据页号 -- 》数据页 -- 》页目录--主键--》槽位 --》 数据
主键索引和普通索引 查询的区别
主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。
非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。
区别：基于主键索引查询的话直接根据id查索引树，索引树的叶子节点就是数据，只要查到一个数据就会停止继续检索（因为主键是唯一），但是普通索引查询的话，它的叶子节点是主键id并且需要查找下一个记录，直到碰到不满足条件的记录，所以还是需要再去主键索引树搜索一次，这个过程称为回表。
覆盖索引是什么？
执行的语句里查询的字段正好是索引没有其他字段。
死锁
事务 A 和事务 B 在互相等待对方的资源释放，就是进入了死锁状态。
两种策略：
- 直接进入等待，直到超时。超时时间可以通过参数 innodb_lock_wait_timeout 来设置
- 另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑
死锁检测
一般采取主动检测死锁，死锁检测要耗费大量的 CPU 资源。
1.如果你能确保这个业务一定不会出现死锁，可以临时把死锁检测关掉。
2.控制并发度，对于相同行的更新，在进入引擎之前排队，同一个事务中操作就采取不同事务去操作
3.将一行改成逻辑上的多行来减少锁冲突。还是以影院账户为例，可以考虑放在多条记录上，比如 10 个记录，影院的账户总额等于这 10 个记录的值的总和。这样每次要给影院账户加金额的时候，随机选其中一条记录来加。
4.不同事务喜欢锁等待，那就把一个事务中的两个操作分成两个事务来做
减少死锁的主要方向，就是控制访问相同资源的并发事务量。
锁的优化
1.读写分离
2.减少锁持有时间
3.顺序性获取资源
锁的类型
共享锁（行级别）： lock in share mode，共享锁和独占锁是互斥的。共享锁和共享锁是不互斥的。
独占锁（行级别）：其实是X锁，也就是Exclude独占锁，更新数据时默认加 （当你执行增删改的时候默认加行锁）
LOCK TABLES xxx READ：这是加表级共享锁 （锁表）
LOCK TABLES xxx WRITE：这是加表级独占锁（锁表）
索引的使用规则
1.联合索引 和 全值匹配 ： where 条件的字段 顺序和 联合索引字段顺序一致并且都是等值匹配。
2.最左列匹配：where 条件的字段 不一定要和联合索引都一样字段，只要最左列一样就可以用到。
3.最左前缀匹配： 例如 like ‘1%’
4.范围查找规则： 范围查找要用最左列 才能使用到索引。
5.等值匹配+范围匹配： 最左列 等值 + 第二列 范围 也是可以使用到索引
6.排序和分组用到索引：排序的字段要和索引的字段顺序一样（最左列），并且升序或者降序一致，最好加上条件过滤或者limit 不然直接回表，全表扫描还更快（查询的字段比较多），最好的是查询的字段是索引的字段（覆盖索引）
7.尽量可以用到索引下推的特性，避免回表
8. or ,！= ,< >, is null，函数， 索引失效
口诀
全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
like百分写最后，覆盖索引不写星；
join连接类型同，order条件非表达式；
不等空值or和'0/1'，索引失效要少用。
索引的设计
1.在java业务开发完的时候 根据sql的where，group，order by 来设计 索引
2.不考虑 字段值基数比较少的字段做索引（为什么）如果mysql发现查询出来的数据量太大就会直接进行全表扫描(所以有些时候会说为什么索引失效了（一些范围查询）)，因为走索引后还需要根据主键进行二次查询，所以查询语句一样，过滤的条件不一样也会导致是否走索引
3.索引字段太大考虑用前缀索引
4.不要设计太多索引，更新慢
5.主键用自增，这样主键值有序，数据页和索引不会频繁页分裂
6.寻找出所有查询中固定的查询字段（作联合索引，就算他的基数比较低）
7.如果联合索引中的某个字段没被用到，可以在where 条件中加上字段 并且 涵盖所有类型（in 所有枚举）。
8.范围索引要放最后一个，因为范围条件之后的字段索引不生效（在组合索引中，除了使用了between匹配外，采用>、<、like等进行匹配都会导致后面的列无法走索引，因为通过以上方式匹配到的数据是不可知的。）
9.如果有涉及到多个范围查询，可以考虑将范围查询设置成枚举类型：比如查询用户是否7天内登陆过，不根据最后登陆时间而是直接更新字段的值来代表是否七天内登陆过
10.牛逼设计之辅助索引----------解决2.问题 字段基数少的索引
比如 select * from user where sex = ‘0’ order by create_date 这个时候只有设计一个联合索引（sex，create_date ） 就可以了，因为索引中的字段也是排序好的 所以order by 的create_date 也会用到索引
11.如果索引不能全部覆盖，一定要选一个索引是查询出来数据量比较少的比较好。
12.没有索引来做排序 聚合 union distinct （基于临时表）的优化，这样会把所有数据写到磁盘中完成排序和聚合操作
核心：尽量用一两个复杂的多字段联合索引抗下80%以上的查询，然后再用一两个辅助索引抗下剩余的20%非典型查询，保证99%以上的查询都能充分利用到索引。
索引下推（创建索引的时候 考虑是否可以建复合索引下推的联合索引）
经常需要通过username查询age或通过age查询username,这时候，删掉（username,password）索引后，我们需要创建新的索引，我们有两种选择
1、（username,age）联合索引+age字段索引
2、（age,username）联合索引+username单字段索引
一般来说，username字段比age字段大的多，所以，我们应选择第一种，索引占用空间较小。（两种查法都需要用到 这两个字段的联合索引，那么就考虑用哪个字段放前面，大的字段放前面，那么大字段做查询条件 就可以用到复合索引，只有再建一个小的字段的索引就可以，减少占用空间）
例子："select * from user_table where username like '张%' and age > 10 mysql 5.6 版本 会直接在复合索引中 查询复合name 和 age 两个条件的主键id，然后回表查询就可以了，mysql 5.6 之前，是查询复合name条件的主键id，然后回表查询出这些id的数据，在根据age 过滤这些数据。
mysql优化
1.表连接优化：关联查询时流程：1.先查询出前面的表的数据（如果where后面有条件就会根据条件过滤），2.根据查询出来的数据 和 后面的表的关联条件 进行关联查询 （如果where 后面带有条件 会再根据条件在过滤），所以知道这个关联查询的流程我们很明确的知道，前面表的数据 都要跟后面表去关联匹配，就像两层循环， 前面表 是驱动表 外层数据越少 循环的次数就越少，性能就越高，所以优化的点就是 数据少的那个表作为 驱动表
2.很多sql，先跟A表关联，查询某个表的符合条件的数据，然后最终结构还是需要A表中的数据，就在跟A表连接查询出来， 优化后就只直接查询出A表符合条件的数据再和B表关联，一些就是sql的写法有问题导致没必要的多次查询。
3.索引优化：
1.有时候mysql会为了避免使用普通索引后再回表 直接使用主键索引，但是又时候查询条件查询的数据比较少或者是根本就没有符合条件的 直接把全表扫描了一遍，就要扫描很多数据反而比较慢。指定索引 force index。
2.深度分页，把语句改成用主键索引去扫描（查结果只有id） 相当于根据where条件扫描主键索引查询出具体满足条件的id，然后把这个语句作为子查询，外层sql根据id查询需要的数据。因为深度分页直接查数据，肯定用不了主键索引，那肯定就是走普通索引然后又回表。
4.数据库结构优化
5.配置优化
6.硬件优化
7.字段最好 not null
8.使用join代替子查询，mysql 不需要在创建临时表，也可以用子查询代替表连接，具体看用子查询时 是不是会将数据过滤非常多
explan 执行计划
type 查询类型 ： 常用的类型有： ALL、index（普通索引叶子节点（遍历））、range、 ref（二级索引（普通索引））、eq_ref、const （（主键或者唯一索引）速度最快）、system、NULL（从左到右，性能从差到好）
possible_keys： 可能用到的索引
Key： key列显示MySQL实际决定使用的键（索引），必然包含在possible_keys中，如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。
rows: 估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数
mysql profiling 工具 分析sql 执行过程 和 耗时
表连接 条件 放在where 后面 和 on 后面的区别
不管on上的条件是否为真都会返回left或right表中的记录，full则具有left和right的特性的并集。 而inner jion没这个特殊性，则条件放在on中和where中，返回的结果集是相同的。
所以在on上面查询的结果条数和左表数量一样，放在where后面条数则会被进行过滤
on是在生成连接表的起作用的，where是生成连接表之后对连接表再进行过滤。
https://www.cnblogs.com/shamo89/p/11065172.html
有些sql执⾏很慢，怎么分析？要怎么优化？
1.⾸先要打开mysql的慢查询⽇志，慢查询⽇志会统计出系统执⾏sql后出现的⼀些慢sql汇总，然后就拿出慢sql来分析了
2.explain 查看执行计划是否用了索引，如果没⾛索引就新建对应的索引就可以了。
3.innodb存储引擎⾥⾯的buffer pool⼊⼿，调优对应的buffer pool数量参数（加大内存并且调整buffer pool个数）
4.索引的结构 B+树和原理，避免查询的sql频繁的进⾏回表操作，主键的值是否是有序的
5.索引的规则，⼏种索引的类型，怎样命中索引，怎么避免频繁 回表（覆盖索引）
SQL 注入漏洞产生的原因？如何防止？
sql注入的原因：请求参数带有一些特殊字符一些关键词：update delete之类的，需要对参数进行过滤和校验，mysql 开启配置文件中的 magic_quotes_gpc 和 magic_quotes_runtime 设置 特殊字符转引号，提高安全性， sql不要省略引号
主从同步
搭建主从复制，都是采取半同步的复制方式的，这个半同步的意思，就是说，主库写入数据，日志进入binlog之后，起码得确保 binlog日志复制到从库了，再告诉客户端说本次写入事务成功了
两种方式：rpl_semi_sync_master插件，GTID搭建方式
从库同步数据太慢：让从库也用多线程并行复制数据就可以了，这样从库复制数据的速度快了，延迟就会很低了
MySQL 5.7就已经支持并行复制了，可以在从库里设置slave_parallel_workers>0，然后把slave_parallel_type设置为LOGICAL_CLOCK
可能是因为并发太高导致主从复制延时：可以扩充主库，这样并发就降下来了
MyCat或者Sharding-Sphere 都可以做主从复制 读写分离 主从切换
大数据量表分库分表
例子： 1亿的数据量，大约数据在1G到几G，其实就服务器来说没什么压力。两台服务器，100张表、
表路由（根据userid）：根据userid来hash后对表取模，路由到一个表里去。可以让数据均匀分散。
没有userid的情况：
1.常规方案是建立一个索引映射表，就是说搞一个表结构为（username, userid）的索引映射表，把username和userid一一映射，然后针对username再做一次分库分表，把这个索引映射表可以拆分为比如100个表分散在两台服务器里。
2.对用户表进行复杂的搜索： 基本上就是要对用户数据表进行binlog监听，把要搜索的所有字段同步到Elasticsearch里去，建立好搜索的索引。通过Elasticsearch去进行复杂的多条件搜索，ES是适合干这个事儿的，然后定位到一批userid，通过userid回到分库分表环境里去找出具体的用户数据。
28法则 估算日活 订单量
一般用户量到了一定程度增长量就不高了，但是订单量不同，会一直增长，所以一般订单量都是最多的。订单表是必须要做分库分表的。
一般会有不同维度去查询：
userid查询订单：做userid和订单id的映射表，先查到订单id 再去订单表获取具体数据
如果是其他复杂查询：先把这些条件字段都同步到es，在es进行聚合查询获取订单id，然后在根据订单id去查询mysql
跨库查询分页：
1.中间件解决
2.查询条件也保存到映射表中
3.es来做
mysql 同步 es ： logstash canal，es 官方提供 go-mysql-elasticsearch，
logstash-input-jdbc：数据量大有性能问题，支持全量增量
logstashlogstash-input-jdbc： 支持增量全量，不支持删除
canal： 不支持全量同步，基于binlog，实时同步
kafka_connector: 稳定，不支持删除
go-mysql-elasticsearch：不稳定，有bug
分库分表后的ID设置
1.系统里每次得到一个 id，都是往一个库的一个表里插入一条没什么业务含义的数据，然后获取一个数据库自增的一个 id
3.UUID UUID 太长了、占用空间大，作为主键性能太差了；更重要的是，UUID 不具有有序性，会导致 B+ 树索引在写的时候有过多的随机写操作（连续的 ID 可以产生部分顺序写）
4.获取系统当前时间 并发很高的时候，比如一秒并发几千，会有重复的情况，这个是肯定不合适的。基本就不用考虑了。
5.snowflake 算法是 twitter 开源的分布式 id 生成算法，采用 Scala 语言实现，是把一个 64 位的 long 型的 id，1 个 bit 是不用的，用其中的 41 bits 作为毫秒数，用 10 bits 作为工作机器 id，12 bits 作为序列号。
- 1 bit：不用，为啥呢？因为二进制里第一个 bit 为如果是 1，那么都是负数，但是我们生成的 id 都是正数，所以第一个 bit 统一都是 0。
- 41 bits：表示的是时间戳，单位是毫秒。41 bits 可以表示的数字多达
2^41 - 1，也就是可以标识2^41 - 1个毫秒值，换算成年就是表示 69 年的时间。 - 10 bits：记录工作机器 id，代表的是这个服务最多可以部署在 2^10 台机器上，也就是 1024 台机器。但是 10 bits 里 5 个 bits 代表机房 id，5 个 bits 代表机器 id。意思就是最多代表
2^5个机房（32 个机房），每个机房里可以代表2^5个机器（32 台机器）。 - 12 bits：这个是用来记录同一个毫秒内产生的不同 id，12 bits 可以代表的最大正整数是
2^12 - 1 = 4096，也就是说可以用这个 12 bits 代表的数字来区分同一个毫秒内的 4096 个不同的 id。
时间戳 + 机房id + 机器id + 同一毫秒内 序号累加（4096）
Mysql 8.0
1.通用表达式 能让语句编写更加方便语义更清晰
2.窗口函数 类似count 和 sum 但是他是将函数的结果放在每一行的数据中 而不是 统计成一行数据
575
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
