mysql知识点介绍

一、MySQL数据库存储引擎

mysql的安装使用实战可参考：https://editor.csdn.net/md/?articleId=121929642

1.常用存储引擎
InnoDB(常用事务型，默认)
MyISAM（常用非事务型)
CSV（CSV格式）
Archive（允许增加查看不允许修改的非事务型）
Memory（易失性非事务型）
NDB（集群）

2.InnoDB与MyISAM的区别
MyISAM ：非事务,不支持MVCC,不支持外键，支持全文索引，非聚集索引，查询速度快,表锁，不会出现死锁 – 适合小数据，小并发
Innodb:支持事务，支持MVCC,支持外键，不支持全文索引，聚集索引，支持并发，适用于插入和更新操作比较多的应用；行锁设计 – 适合大数据，大并发

一张表，里面有 ID 自增主键，当 insert 了 17 条记录之后，删除了第 15,16,17 条记录，
再把 Mysql 重启，再 insert 一条记录，这条记录的 ID 是 18 还是 15 ？
(1)如果表的类型是 MyISAM，那么是 18
因为 MyISAM 表会把自增主键的最大 ID 记录到数据文件里，重启 MySQL 自增主键的最大
ID 也不会丢失
（2）如果表的类型是 InnoDB，那么是 15
InnoDB 表只是把自增主键的最大 ID 记录到内存中，所以重启数据库或者是对表进行
OPTIMIZE 操作，都会导致最大 ID 丢失

二、MySQL与Oracle区别

1.MySQL是开源的（免费的）中小型数据库，Oracle是收费的oracle是大型的数据库。

2.MYSQL有自动增长的数据类型(auto_increment)，插入记录时不用操作此字段，会自动获得数据值。ORACLE没有自动增长的数据类型，一般使用Sequence。

3.事务：MySQL对于事务默认是不支持的，只有某些存储引擎中如：innodb可以支持。 而Oracle对于事务是完全支持，不管是OLTP还是OLAT（OLTP是面向顾客的,用于事务和查询处理；OLAP是面向市场的,用于数据分析）都是支持的。

4.MYSQL里可以用双引号包起字符串，ORACLE里只可以用单引号包起字符串。

5.分页：MYSQL使用LIMIT 开始位置, 记录个数。ORACLE使用只有一个ROWNUM字段标明它的位置, 并且只能用ROWNUM<100, 不能用ROWNUM>80。

6.日期：MYSQL日期字段分DATE和TIME两种，ORACLE日期字段只有DATE，包含年月日时分秒信息，用当前数据库的系统时间为SYSDATE, 精确到秒。

7.ORACLE里like ‘%字符串%’ 不走索引, 用字符串比较函数 instr(字段名,‘字符串’)>0 会得到更精确的查找结果。Mysql只有like’%’会使用索引

8.MYSQL的非空字段也有空的内容，ORACLE里定义了非空字段就不容许有空的内容。按MYSQL的NOT NULL来定义ORACLE表结构, 导数据的时候会产生错误。因此导数据时要对空字符进行判断，如果为NULL或空字符，需要把它改成一个空格的字符串。

9.MySQL是单进程多线程，Oracle是多进程(在Windows下也是单进程)，Windows 下只能使用一个用户，或者你可以在不同用户之间做切换，而Linux是多用户或者相同用户可以同时连接操作系统。
.

三、Mysql索引

1.分类：单列索引/组合索引/全文索引/空间索引

组合索引遵循最左前缀集合：
（1）3列索引(col1,col2,col3)，则已经对(col1)、(col1,col2)、(col1,col2,col3)上建立了索引；
（2）=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式
（3）mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配（此时他的范围相当于是无穷大），比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到（只要范围查询的c放最后面就行）。

2.存储结构
可以在这个网址演示各种数据结构：
https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

红黑树（二叉树）：通过Hash值比对，小于等于的放左边，大于的放右边。由于树的高度没有限制，性能较差，一般不用作索引。
BTree:一般在2-4层，每个节点都存有数据
B+Tree:一般在2-4层，只有末端节点（叶子节点）存有数据（每个节点能存更多的key），且设置了顺序访问指针。非叶子节点由n个键值key和n个指针point组成。

B+树的优势：
1、更加高效的单元素查找：
a、首先B+树的中间节点不存储数据，所以同样大小的磁盘页可以容纳更多的节点元素（比如相同大小磁盘页存非叶子节点，b树能存4千个，b+树能存1万个），如此一来，相同数量的数据下，B+树就相对来说要更加矮胖些，磁盘IO的次数更少。
b、由于只有叶子节点才保存数据，B+树每次查询都要到叶子节点；而B树每次查询则不一样，最好的情况是根节点，最坏的情况是叶子节点，没有B+树稳定。
2、叶子节点形成有顺链表，范围查找性能更优：
B树要一个个元素查找，B+树直接查上下限元素，然后通过指针根据范围遍历。

MyISAM和InnoDB都是使用的B+Tree。
(1)innodb的b+树的叶子节点是存放数据的，myisam的b+树的叶子节点是存放指针的。
(2)Innodb是聚簇索引，必须要有主键，一定会基于主键查询，但是辅助索引就会查询两次(先找到主键，再找数据)，myisam是非聚簇索引，索引和数据是分离的，索引里保存的是数据地址的指针，主键索引和辅助索引是分开的，没有什么区别。

索引覆盖：只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。

四、MySQL性能优化

首先我们了解下Mysql中一条sql执行过程：
1.发送查询语句到mysql数据库
2.判断查询是否命中缓存
3.对查询sql进行解析和预处理
4.查询优化器生成最优的执行计划
5.存储引擎执行最优的执行计划
6.将查询结果返回给客户端

1.性能优化思路：
(1)慢查询（需要手动开启）获取费时间的SQL语句；
(2)使用Explain查看执行计划；
(3)使用Show profile查看性能问题（耗时）。

2.MySQl的explain(执行计划)
Possible_keys:可能会用到的索引
Key:真实用到的索引
Key_len:索引使用的字节数，可以判断联合索引有多少列被选择了
type列性能：system > const（只需匹配一行数据） > eq_ref（唯一性索引扫描） > ref （非唯一性索引扫描）> fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range（给定范围内使用索引） > index（只遍历索引树） > ALL（匹配全表）
Extra:里面有两种场景需要重点优化
①using filesort文件排序，在order by排序过程中，
order by的字段不是索引字段;
order by的字段是索引字段,但在select中没有使用覆盖索引；
Order by中同时使用了升序和降序排序；
Order by多个字段时不是按照最左前缀原则
②using temporary使用了临时表保存中间结果
　如果GROUP BY 的列没有索引,产生临时表.
　如果GROUP BY时,SELECT的列不止GROUP BY列一个,并且GROUP BY的列不是主键 ,产生临时表.
　如果GROUP BY的列有索引,ORDER BY的列没索引.产生临时表.
　如果GROUP BY的列和ORDER BY的列不一样,即使都有索引也会产生临时表.
　如果GROUP BY或ORDER BY的列不是来自JOIN语句第一个表.会产生临时表.
　如果DISTINCT 和 ORDER BY的列没有索引,产生临时表.
如果group by 的列没有索引,必产生内部临时表,
如果order by 与group by为不同列时,或多表联查时order by ,group by 包含的列不是第一张表的列,将会产生临时表
优化方法：filesort把不必要的排序去掉，必要的加索引，temporary就是尽量减少这个临时表数据

3.覆盖索引:
解释一： 就是select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。
解释二： 索引是高效找到行的一个方法，当能通过检索索引就可以读取想要的数据，那就不需要再到数据表中读取行了。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫 做覆盖索引。
解释三：是非聚集组合索引的一种形式，它包括在查询里的Select、Join和Where子句用到的所有列（即建立索引的字段正好是覆盖查询语句[select子句]与查询条件[Where子句]中所涉及的字段，也即，索引包含了查询正在查找的所有数据）。

4.Mysql索引失效场景：
1.查询条件有or;
2.Like是以%开头，最左匹配原则
3.查询的列上有运算或函数
4.字符串查询时不加引号
5.关联查询里面字段编码格式不一致
6.联合主键里没遵循最左前缀原则

5.sql优化细节：
（1）查询语句中不要使用 *;
（2）减少使用IN或者NOT IN ,使用exists，not exists或者关联查询语句替代;
（3）尽量减少子查询，使用关联查询（left join,right join,inner join）替代;
（4）Or的查询尽量用 union或者union all 代替;
（5）合理的增加冗余的字段（减少表的联接查询）;
（6）增加中间表进行优化，对数据量比较大的联合查询尽量先用一些条件过滤掉一部分数据;
（7）建表的时候能使用数字类型的字段就使用数字类型（type,status…），数字类型的字段作为条件查询比字符串的快;
（8）Mysql的Where条件执行顺序是从左到右（oracle是从右到左）,那些可以过滤掉最大数量记录的条件写在WHERE子句的前面；
（9）尽量减少数据库连接次数（批量查询）
（10）esists/not exists可以用left join优化
当esists/not exists中的数据量很大时，查询会很慢，此时可以采用left join优化。
（exists要考虑数据重复的问题，not exists不用）
比如A表中存在，B表中不存在的数据，用A left join B 然后where后接B.key is null。
(11)用group by优化distinct
(12)如果查询经常执行，并且数据源的一部分数据在查询中保持不变，那么可以考虑使用临时表或物化视图来存储这部分数据。这样，查询就可以在这个较小的数据集上执行，而不是整个大数据集。

五、MySQL事务

1.事务四大特性ACID：
(1)原子性：构成事务的的所有操作必须是一个逻辑单元，要么全部执行，要么全部不执行。
(2)一致性（Consistency）：数据库在事务执行前后状态都必须是一致的。
(3)隔离性：事务之间不会相互影响。
(4)持久性（Durability）：事务执行成功后必须全部写入磁盘。

2.Mysql四大特性(ACID)实现
(1)原子性：undo log回滚日志，数据修改时生成undo log，存着相反逻辑的sql，回滚时就调用。
(2)一致性（Consistency）：联合原子性、隔离性和持久性来保证；数据库本身的屏障，如不能向整形插入字符串和字符串长度检查。
(3)隔离性：默认是可重复读，一个事务写操作对另一事务写操作通过锁实现，一个事务写操作对另一事务读操作通过mvcc实现。
(4)持久性（Durability）：通过redo log实现，mysql宕机时可以使用它恢复数据

3.Mvcc
Mvcc介绍：Multi-Version Concurrent Control，即多版本并发控制，快照读，通过版本链机制。
每个读操作会看到一个一致性的snapshot，并且可以实现非阻塞的读。MVCC允许数据具有多个版本，这个版本可以是时间戳或者是全局递增的事务ID，在同一个时间点，不同的事务看到的数据是不同的。innodb会为每一行添加两个字段，分别表示该行创建的版本和删除的版本，填入的是事务的版本号，这个版本号随着事务的创建不断递增。
(1) insert：将新插入的行的创建版本号设置为当前系统的版本号。
(2)select：满足以下两个条件innodb会返回该行数据：
①只会查找版本早于当前事务版本的数据行，确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的。
②该行的删除版本号大于当前版本或者为空。这可以确保事务读取到的行，在事务开始之前未被删除.
(3)delete：将要删除的行的删除版本号设置为当前系统的版本号。
(4)update：不执行原地update，而是转换成insert + delete。将旧行的删除版本号设置为当前版本号，并将新行insert同时设置创建版本号为当前版本号。—跟我们记录历史删除的数据有点像，直接改版本号

其中，写操作（insert、delete和update）执行时，需要将系统版本号递增。

由于旧数据并不真正的删除，所以必须对这些数据进行清理，innodb会开启一个后台线程执行清理工作，具体的规则是将删除版本号小于当前系统版本的行删除，这个过程叫做purge。

通过MVCC很好的实现了事务的隔离性，可以达到repeated read级别，要实现serializable还必须加锁。

4.并发操作的事务问题
(1)脏读：读未提交事务的数据。
(2)丢失更新：多个事务同时操作同一个数据时，前一个事务对数据更改但并未提交，后一个事务又对数据更改，前一个事务的数据更改就会丢失。–update
(3)不可重复读：当前一个事务事务刚读取数据后，后一个事务对该数据进行了修改，当前一个事务在对该数据时，得到的数据和前一次不一样。-- update
(4)幻读：前一个事务事务刚读取数据后，后一个事务对该数据进行了插入或删除，当前一个事务在对该数据时，得到的数据条数和前一次不一样。-- insert/delete

5.事务隔离级别
Read uncommitted(读未提交):最低级别
Read committed（读已提交）:可避免脏读 --oracle/db2默认
Repeatable read（可重复读）:可避免脏读/不可重复读 --mysql默认
Serializable（串行化）:可避免脏读/不可重复读/幻读的发生

为了保证性能，数据库隔离级别多为读已提交，不可重复读和幻读可用乐观锁/悲观锁等解决。
乐观锁：乐观锁的加锁机制比较宽松，大多是基于数据版本（ Version ）记录机制实现。数据每更新一次，版本号加一，提交的数据版本号等于数据库表当前版本号，则予以更新，否则认为是过期数据
悲观锁（排他写锁）：当我们要对一个数据库中的一条数据进行修改的时候，为了避免同时被其他人修改，最好的办法就是直接对该数据进行加锁以防止并发。

6.MySql锁
按锁的粒度分：行级锁/表级锁/页面锁
表级锁：开销小，加锁快，不会死锁；锁粒度大，易发生锁冲突，并发低
（InnoDB 行锁是通过给索引上的索引项加锁来实现的）
行级锁：与表锁反之
页面锁：介于两者之间

按锁的功能分：共享读锁和排他写锁（设置方式：lock table XXX read/write）
InnoDB引擎锁机制：
共享锁（s）：加完锁后获得共享锁的数据只能读数据，不能修改数据，且不能加排他锁
排他锁（x）：加完锁后获得排他锁的数据可读数据，可修改数据，且不能加其他锁
意向共享锁（IS）：事务给数据加共享锁之前必须获得该表的IS锁 --mysql内部使用
意向排他锁(IX) ：事务给数据加排他锁之前必须获得该表的IX锁 --mysql内部使用

S锁和X锁都是行锁，意向锁都是表锁。update/insert/delete语句mysql会自动加排他锁。InnoDB行锁是通过索引上的索引项加锁实现的（在指向数据的第一个和最后一个索引之间加锁–间隙锁），select语句使用索引查数据时，才会使用行锁，否则使用表锁。

间隙锁：范围查找时，会将范围内的所有数据上锁，如1-5，数据只有1，3，5，那么2，4也会被锁住，此时无法插入2。 防止出现幻读，但是它会把锁定范围扩大。解决方法是innodb_locaks_unsafe_for_binlog设置为true来关闭间隙锁。

六、分库分表之MyCat实现

数据库表太大了怎么解决呢？
1)分区（如按时间分区）、分表(拆小表)、分库。

分表的时候可以按照冷(访问频次较少)热分离的方式拆表。

分库怎么解决数据一致的问题？
①可以建立全局id：创建一张序列表，每次要用ID的时候，直接从使用这个表中的自增ID，但是比较占用资源。
②uuid:无序查询效率低
③雪花snowflake算法:Twitter开源的分布式ID生成算法，结果是一个long型的ID
第一部分: 1bit位，用来表示符号位，一般情况下是0。
第二部分: 41bit位：表示的是时间戳，系统时间-开始时间，41位最大可用69年
第三部分：10bit，工作机器id，意味着这个服务最多可以部署在 2^10 台机器上，也就是 1024 台机器。
第四部分：12bit，递增序列，用来记录同毫秒内产生的不同id。
优点：性能高。不依赖于数据库，完全在内存中生成，索引效率高。
缺点：Ⅰ.机器上时钟回拨，会有问题。可以定义一个初始时间戳，在初始时间戳上自增解决。
Ⅱ.原标准实现代码中是直接抛异常，会暂停服务。
④redis原子自增id

2)读写分离。尤其是写入，放在新表中，定期进行同步。如果其中记录不断有update，最好将写的数据放在 redis中，定期同步

3)表的大文本字段分离出来，成为独立的新表。大文本字段，可以使用NOSQL数据库

4)优化架构，或优化SQL查询，避免联表查询，尽量不要用count(*), in，递归等消耗性能的语句

5)用内存缓存，或在前端读的时候，增加缓存数据库。重复读取时，直接从缓存中读取。

1.MyCat核心概念
Mycat：它是一个优秀的数据库中间件，其本质就是提供代理服务，对数据库进行访问，提供包括读写分离、分库分表等能力。

Schema:逻辑库,与Database对应。
Table:表，这里的表需要声明逻辑数据节点。在此可指定表的分片规则。
DataNode:逻辑数据节点，是存放表的具体物理节点（分片节点）。
DateSource:定义某个物理库的访问位置，用于捆绑到DataNode上。

2.MyCat存在的问题（最多支持跨俩个库的join）：
跨库join问题：
(1)拆分成多个select
(2)每个库都建立一个相同的全局表
(3)冗余字段
(4)E-R分片（将有关系的记录储存到一个库中）
分布式事务：强一致性事务（同步）；最终一致性事务（异步）-- 推荐
分布式主键：redis incr命令 – 推荐；snowfalke算法

七、如何解决数据的重复插入

1.单表单库：
（1）索引+唯一字段，当重复操作时，抛出异常，提示不能重复插入。但这么做服务会有异常，不够健壮。
（2）幂等：指可以相同参数重复执行，能获得相同结果，可以解决刚刚的不够健壮的问题，但是不适合大流量、高并发的场景。
伪代码如下

class UserService {
    public Result insertUser(User user) {
        // 幂等处理
        Result result= null;
        try {
            result= UserDAO.insertUser(user);
        } catch (DuplicateKeyException e) {
            result= UserDAO.selectUser(user);
        }
        return result;
    }
}

2.分库分表：redis分布式锁
1.加锁
jedis.set(lockKey, requestId, “NX”, “PX”, expireTime);
lockKey 使用 可确定数据唯一性的字段拼接而成
2.插入数据
result= UserDAO.insertUser(user);
3.解锁
jedis.eval(script, lockKey,requestId);