MySQL高级知识总结

最新推荐文章于 2024-07-19 11:09:04 发布

guluuwater

最新推荐文章于 2024-07-19 11:09:04 发布

阅读量179

点赞数

分类专栏： mysql 文章标签：数据库 mysql 索引

本文链接：https://blog.csdn.net/qq_30634979/article/details/115336908

版权

mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

存储引擎

InnoDB和MyISAM的区别？

锁：MyISAM只支持表级锁，InnoDB支持表级锁和行级锁

事务和崩溃后的安全恢复：MyISAM强调性能，每次的查询都具有原子性，但是不支持事务；InnoDB支持事务、回滚和崩溃修复。

外键：MyISAM不支持外键，InnoDB支持外键

MVCC：InnoDB支持MVCC（多版本并发控制）

索引

什么是索引？

**索引是通过对数据库表的一列或者多列的值进行排序实现能够帮助MySQL高效获取数据的一种数据结构，**它可以大大提高数据库的检索速度。就好比一本书的目录一样，通过目录能够快速找到想要的内容，通过索引可以快速检索到需要的数据。

索引的分类

单列索引：一个索引只能包含一列
- 普通索引：
- 唯一索引
- 主键索引
组合索引
全文索引

索引的优缺点？

优点：

提高检索的效率，降低数据库IO成本
通过索引列对数据排序，降低数据库排序的成本，减小CPU的消耗

缺点：

索引信息是在索引文件中的，索引文件需要存储在磁盘上，占用存储空间
虽然索引提高了检索速度，但却降低了更新速度（update、insert、delete操作），因为在更新表的时候MySQL不仅要保存更新的数据，还要更新索引文件中的数据和结构。
创建和维护索引需要耗费时间，并且这个时间随着数据量的增多而增加

索引的结构

MySQL中的索引结构使用的是BTree索引和哈希索引。单值查询比较多的时候哈希索引性能较好，其他情况使用BTree索引。

索引在MySQL的存储引擎层实现，在MySQL常用的两个存储引擎MyISAM和InnoDB中，对于B+Tree的实现是不相同的。

B树和B+树的区别：

n叉B+Tree每个节点最多含有n个key，n叉BTree最多含有n-1个key
B+Tree的叶子节点保存所有的key的信息，并按照Key的大小顺序排列
在B+Tree的所有的非叶子节点都可以看作是key的所有部分
B+Tree只有叶子节点保存key信息，查询任何key都要从root走向叶子节点，所以B+Tree的查询效率更稳定

MySQL的查询缓存

当执行完全相同的SQL语句的时候，如果缓存中有之前查过的数据，就会先从缓存中查询数据，提高查询性能。

**开启查询缓存以后，MySQL的缓存系统会跟踪查询中涉及到的每张表，如果表中的数据发生改变，那么缓存中和该表相关的数据都会清除。**因此，修改比较频繁的表不适合做查询缓存。

缓存虽然能够提升查询性能，但是也会带来额外的开销，因为每次查询后都要做一次缓存操作，并且当表中的数据改变的时候，还要清除缓存，所以在使用查询缓存的时候要谨慎，尤其是在增删改数据比较密集的情况中，更要谨慎考虑。

MySQL8.0中把查询缓存的功能砍掉了，因为用的不多。

如何避免索引失效？

1. 全值匹配

查询的时候对索引中的所有列都指定具体值，也就是条件查询的时候，索引对应的所有列都有值的话就可以避免索引失效

2. 最左前缀法则

最左前缀法则：查询从索引的最左前列开始，并且不能跳过索引中的列。（与where后面连接条件的顺序无关，谁先谁后都一样）

在使用多个列创建组合索引的时候，相当于创建了多个索引，比如使用name status address三个字段创建一个组合索引idx_name_sta_addr，就相当于创建了三个索引：

name
name + status
name + status + address

所以遵守最左前缀法则可以避免索引失效。比如查询条件中是name+address，就不会走索引了（跳过了status)

3. 范围查询后面的字段，将不走索引

where status > ‘1’ and address = ‘北京’，那么address字段将不会走索引

4. 在索引列上进行运算，会使索引失效

where substring(name,3,2) = ‘科技’ ；会使得索引失效

5. 字符串格式的字段，不加引号会使索引失效

实际上就是上一条的规则，因为在MySQL中对于字符串字段不加引号的话底层会自动帮我们加上，属于进行了运算，索引失效

**6. 尽量使用覆盖索引，避免使用select ***

使用覆盖索引的意思就是，查询的字段全部在索引范围内。

7. or前后必须都是索引字段

使用or连接两个查询字段的时候，两个字段必须都是索引才能使索引生效，如果一个是索引字段，一个不是，那么该语句就不会走索引。

8. 以%开头的Like模糊查询，索引失效

如果是尾部有%就不会失效，头部有%就会失效

9. 使用索引比检索全表慢的时候不走索引

事务

事务是一组sql语句的逻辑操作，要么全部成功，要么全部失败，这就是事务。

事务的特性（ACID）

原子性：一个事务的执行是一个原子操作，要么全部成功，要么全部失败。如果失败了，会回滚到之前的状态。
一致性：执行事务前后，数据保持一致。
隔离性：并发事务执行的时候各个事务是独立的，互不影响
持久性：事务被提交之后，对数据的改变是永久的

并发事务可能带来的问题

丢失更新：多个事务对同一数据修改的时候，后面事务提交的数据把前面事务提交的数据覆盖掉
**脏读：**一个事务读取到了另一个事务还没有提交的数据
不可重复读：同一个事务前后执行两次相同的查询操作，查询的数据结果不一致（侧重于数据的修改）
幻读：同一个事务前后执行两次查询操作，查询的结果集不一致（侧重于数据记录的数量不一致，如新增或者删除）

事务的隔离级别及要解决的问题

读未提交（Read Uncommitted） 最低的隔离级别，允许读取到事务未提交的数据，可能产生脏读、不可重复读、幻读
读已提交（Read Committed） 允许读取到事务已经提交的数据，阻止脏读，但是不可重复读和幻读还有可能发生
可重复读（Repeatable Read） 同一个事务多次对一个数据的读取结果是相同的，阻止了脏读、不可重复读，幻读还有可能会发生
串行化（Serializable） 事务的最高隔离级别，完全服从ACID，任意时刻只能有一个事务在执行，可以阻止脏读、不可重复读、幻读

表格

隔离级别	脏读	不可重复读	幻读
读未提交（Read Uncommitted）	√	√	√
读已提交（Read Committed）	×	√	√
可重复读（Repeatable Read）	×	×	√
串行化（Serializable）	×	×	×

MySQL的InnoDB存储引擎默认的隔离级别是可重复读。

注意：InnoDB在可重复读的隔离级别上使用的是Next-Key Lock算法，可以避免幻读的产生，这是与SQL标准不同的地方。即InnoDB的默认隔离级别是可重复读，但却达到了串行化的要求，并且比串行化的性能要高。

SQL语句执行的内部过程

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yTDcskXe-1617111680455)(C:\Users\zhq\AppData\Roaming\Typora\typora-user-images\image-20210327114313051.png)]

连接器：身份认证和权限相关
查询缓存：执行sql语句的时候会先查询缓存
分析器：在缓存中没有命中，就来到分析器。分析器的作用是分析这个sql语句是干嘛的
优化器：MySQL会选择一个它认为比较好的方案去执行
执行器：执行sql语句

SQL语句的执行顺序

SELECT DISTINCT <select list> 
FROM<left_table> <join_type> 
JOIN<right_table> ON <join_condition> 
WHERE<where_condition> 
GROUP BY <group_by_list> 
HAVING<having_condition> 
ORDER BY <order_by_condition> 
LIMIT<limit_params>

from：从哪个表中查数据，两个表的话就是左表和右表通过笛卡尔积生成一个虚拟表，后面的操作都是在虚拟表上操作。
on：on筛选器
join：添加外部行
where：where筛选器，筛选出满足条件的数据。这时候还没有分组，所以在此不能使用聚合函数（分组后才能使用聚合函数）
group by：分组
having：分组后过滤
select：查询
distinct：去重
order by：排序
limit：分页

SQL优化

查看sql的执行频率

show status

定位低效率的sql语句

有两种方式可以定位出低效率的sql语句

通过慢查询日志：查询出执行时间超过阈值的sql语句，sql查询结束后才可以查看
show processlist：可以实时查看当前正在执行的sql的执行情况

explain分析执行计划

查看MySQL如何执行select语句，包括如何连接以及连接的顺序

show profile分析SQL

查看执行sql语句的时间都花费在了哪里

trace分析优化器执行计划

MySQL锁

锁的分类

从锁的粒度角度

表级锁：锁的粒度最大，对当前操作的整张表加锁。实现简单、开销小、加锁快、不会出现死锁。表级锁有共享锁（读锁）和排他锁（写锁）
行级锁：锁的粒度最小，对当前操作的行加锁，实现复杂、开销大、加锁慢、可能出现死锁。行级锁有共享锁（读锁）和排他锁（写锁）。
页面锁：锁的粒度处于表锁和行锁之间，加锁快但是锁冲突容易多，一次锁定相邻的一组记录

从锁的级别角度

悲观锁

共享锁：读锁，其他事务不能修改加了读锁的数据，只能读取
排他锁：写锁，不能与其他锁并存，一个事务获取了一个数据行的排他锁，其他事务就不能获取共享锁和排他锁

乐观锁

在更新数据前，检查版本号是否发生变化。若变化则取消本次更新，否则就更新数据（版本号+1）

在MyISAM中，select会自动获取共享锁，update delete insert会自动获取排他锁

在InnoDB中：

普通的select 语句不会加任何锁，update delete insert 会自动获取排他锁
如果不走索引，行锁会升级为表锁
使用范围条件，而不是使用相等条件检索数据，并请求获取共享锁或排他锁的时候，InnoDB会给符合条件的数据加锁，如果某些键值在该范围内，但是不存在，（被成为间隙），InnoDB也会对这个间隙进行加锁，成为间隙锁(Next-Key锁)。那么有间隙锁存在的时候我们就不能往这个间隙中插入数据了。