一、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);