Mysql与redis总结

最新推荐文章于 2024-05-27 10:20:43 发布

Matrix70

最新推荐文章于 2024-05-27 10:20:43 发布

阅读量830

点赞数

分类专栏： MySQL 文章标签： mysql redis 数据库性能优化 Powered by 金山文档

本文为博主原创文章，未经博主允许，不得转载！！

本文链接：https://blog.csdn.net/qq_52128187/article/details/128811520

版权

MySQL 专栏收录该内容

18 篇文章 0 订阅

订阅专栏

1.1 MySql & Redis

1.1 Mysql 执行流程和执行引擎

MySql 查询过程

MyISAM 与 InnoDB

对比	MyISAM	InnoDB
外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作	行锁,操作时只锁某一行，不对其它行有影响，适合高并发的操作
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响

1.2 Mysql 索引

数据结构：B+Tree
一般来说能够达到range就可以算是优化了
哈希索引
尽量避免索引失效

1.3 b-tree和b+tree的区别

B-树的关键字、索引和记录是放在一起的， B+树的非叶子节点中只有关键字和指向下一个节点的索引，记录只放在叶子节点中。
在B-树中，越靠近根节点的记录查找时间越快，只要找到关键字即可确定记录的存在；而B+树中每个记录的查找时间基本是一样的，都需要从根节点走到叶子节点，而且在叶子节点中还要再比较关键字。

1.4 redis是单线程的，为什么那么快

完全基于内存，绝大部分请求是纯粹的内存操作，非常快速。
数据结构简单，对数据操作也简单，Redis中的数据结构是专门进行设计的
采用单线程，避免了不必要的上下文切换和竞争条件，也不存在多进程或者多线程导致的切换而消耗 CPU，不用去考虑各种锁的问题，不存在加锁释放锁操作，没有因为可能出现死锁而导致的性能消耗
使用多路I/O复用模型，非阻塞IO
使用底层模型不同，它们之间底层实现方式以及与客户端之间通信的应用协议不一样，Redis直接自己构建了VM 机制，因为一般的系统调用系统函数的话，会浪费一定的时间去移动和请求

1.5 MySQL 的事务

事务的基本要素（ACID）

原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位
一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏。比如A向B转账，不可能A扣了钱，B却没收到。
隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。
持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。

1.6 事务的并发问题（面试重点）

事务的并发问题

脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致
幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。
小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

MySQL事务隔离级别

事务隔离级别脏读不可重复读幻读
读未提交（read-uncommitted）是是是
读提交（read-committed）否是是开始事务
可重复读（repeatable-read）否否是需改全局隔离级别为可重复读级别
串行化（serializable）否否否

1.7 Mysql 优化

尽量避免使用子查询

避免函数索引

用IN 来替换 OR

MySQL对于IN做了相应的优化，即将IN中的常量全部存储在一个数组里面，而且这个数组是排好序的。但是如果数值较多，产生的消耗也是比较大的。
再例如：select id from table_name where num in(1,2,3) 对于连续的数值，能用 between 就不要用 in 了；再或者使用连接来替换。

LIKE前缀%号、双百分号、_下划线查询非索引列或*无法使用到索引（模糊查询），如果查询的是索引列则可以

读取适当的记录LIMIT M,N，而不要读多余的记录

select id,name from table_name limit866613, 20

使用上述sql语句做分页的时候，可能有人会发现，随着表数据量的增加，直接使用limit分页查询会越来越慢。
优化的方法如下：可以取前一页的最大行数的id，然后根据这个最大的id来限制下一页的起点。比如此列中，上一页最大的id是866612。

select id,name from table_name where id> 866612limit20

避免数据类型不一致

分组统计可以禁止排序sort，总和查询可以禁止排重union all

避免随机取记录

禁止不必要的ORDER BY排序

批量 INSERT 插入

不要使用NOT等负向查询条件

你可以想象一下，对于一棵B+树，根节点是40，如果你的条件是等于20，就去左面查，你的条件等于50，就去右面查，但是你的条件是不等于66，索引应该咋办？还不是遍历一遍才知道。

尽量不用 select *

SELECT *增加很多不必要的消耗（cpu、io、内存、网络带宽）；

增加了使用覆盖索引的可能性；

当表结构发生改变时，前者也需要经常更新。所以要求直接在select后面接上字段名。

区分in 和 exists

select * from 表A where id in (select id from 表B)

上面sql语句相当于

select * from 表A where exists(select * from 表B where 表B.id=表A.id)

区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），
如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询。
所以 IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。

优化Group By 语句

如果对group by语句的结果没有排序要求，要在语句后面加 order by null（group 默认会去排序）；

尽量让group by过程用上表的索引，确认方法是explain结果里没有Using temporary 和 Using filesort；

如果group by需要统计的数据量不大，尽量只使用内存临时表；也可以通过适当调大tmp_table_size参数，来避免用到磁盘临时表；

如果数据量实在太大，使用SQL_BIG_RESULT这个提示，来告诉优化器直接使用排序算法（直接用磁盘临时表）得到group by的结果。

1.8 索引

1.8.1 索引优化

用小结果集驱动大结果集，将筛选结果小的表（在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与join的各个字段的总数据量，数据量小的那个表，就是“小表”）首先连接，再去连接结果集比较大的表，尽量减少join语句中的Nested Loop的循环总次数
优先优化Nested Loop的内层循环（也就是最外层的Join连接），因为内层循环是循环中执行次数最多的，每次循环提升很小的性能都能在整个循环中提升很大的性能；
对被驱动表的join字段上建立索引；
当被驱动表的join字段上无法建立索引的时候，设置足够的Join Buffer Size。
尽量用inner join(因为其会自动选择小表去驱动大表).避免 LEFT JOIN (一般我们使用Left Join的场景是大表驱动小表)和NULL，那么如何优化Left Join呢？
条件中尽量能够过滤一些行将驱动表变得小一点，用小表去驱动大表
右表的条件列一定要加上索引（主键、唯一索引、前缀索引等），最好能够使type达到range及以上（ref,eq_ref,const,system）
适当地在表里面添加冗余信息来减少join的次数
使用更快的固态硬盘
性能优化，left join 是由左边决定的，左边一定都有，所以右边是我们的关键点，建立索引要建在右边。当然如果索引是在左边的，我们可以考虑使用右连接，
select * from atable left join btable on atable.aid=btable.bid;//最好在bid上建索引

1.8.2 避免索引失效

1.最佳左前缀法则
如果索引了多列，要遵守最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列。Mysql查询优化器会对查询的字段进行改进，判断查询的字段以哪种形式组合能使得查询更快，所有比如创建的是(a,b)索引，查询的是(b,a)，查询优化器会修改成(a,b)后使用索引查询。
2.不在索引列上做任何操作
(计算、函数、(自动or手动)类型转换)，会导致索引失效而转向全表扫描。
3.存储引擎不能使用索引中范围条件右边的列。
如这样的sql: select * from user where username='123' and age>20 and phone='1390012345',其中username, age, phone都有索引，只有username和age会生效，phone的索引没有用到。
4.尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))
如select age from user减少select *
5.mysql在使用不等于(!= 或者 <>)的时候无法使用索引会导致全表扫描。
6.is null, is not null 也无法使用索引，在实际中尽量不要使用null。
7.like 以通配符开头(‘%abc..’)mysql索引失效会变成全表扫描的操作。
所以最好用右边like 'abc%'。如果两边都要用，可以用select age from user where username like '%abc%',其中age是必须是索引列，才可让索引生效假如index(a,b,c), where a=3 and b like 'abc%' and c=4，a能用，b能用，c不能用，类似于不能使用范围条件右边的列的索引对于一棵B+树来讲，如果根是字符def，如果通配符在后面，例如abc%，则应该搜索左面，例如efg%，则应该搜索右面，如果通配符在前面%abc，则不知道应该走哪一面，还是都扫描一遍吧。
8.字符串不加单引号索引失效
9.少用or，用它来连接时会索引失效
10.尽量避免子查询，而用join
11.在组合索引中，将有区分度的索引放在前面
如果没有区分度，例如用性别，相当于把整个大表分成两部分，查找数据还是需要遍历半个表才能找到，使得索引失去了意义。
12.避免在 where 子句中对字段进行 null 值判断
对于null的判断会导致引擎放弃使用索引而进行全表扫描。

1.9 MySql 实现高可用

通过keepalived 的虚拟路由协议实现Mysql 的高可用（主主双机热备）
通过mycat 也可以实现Mysql 的高可用和读写分离（主备）

1.10 Mysql的三范式

第一范式（1NF）：每一列都是不可分割的原子数据项（什么意思，每一项都不可分割，像下面的表格就能分割，所以它连第一范式都算不上）
第二范式：在1NF基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）
第三范式（3NF）：在2NF的基础上，任何的非主属性不依赖于其他非主属性（在第二范式基础上消除传递依赖）
巴斯-科德范式（BCNF）：
在1NF基础上，任何非主属性不能对主键子集依赖（在3NF基础上消除对主码子集的依赖）

2.1 Redis

2.1.1 缓存穿透、缓存雪崩、缓存击穿

缓存穿透

缓存穿透是指查询一个一定不存在的数据。由于缓存命不中时会去查询数据库，查不到数据则不写入缓存，这将导致这个不存在的数据每次请求都要到数据库去查询，造成缓存穿透。
解决方案：
① 是将空对象也缓存起来，并给它设置一个很短的过期时间，最长不超过5分钟
② 采用布隆过滤器，将所有可能存在的数据哈希到一个足够大的bitmap中，一个一定不存在的数据会被这个bitmap拦截掉，从而避免了对底层存储系统的查询压力