MYSQL 那点破事！索引、SQL调优，2024年最新你还看不懂吗？

最新推荐文章于 2024-11-08 22:59:30 发布

彩虹的颜

最新推荐文章于 2024-11-08 22:59:30 发布

阅读量507

点赞数 8

分类专栏： 2024年程序员学习文章标签： mysql sql 数据库

本文链接：https://blog.csdn.net/m0_74931167/article/details/137562406

版权

195 篇文章 0 订阅

订阅专栏

答案：死锁的四个必要条件：1、互斥 2、请求与保持 3、环路等待 4、不可剥夺。

合理的设计索引，区分度高的列放到组合索引前面，使业务 SQL 尽可能通过索引定位更少的行，减少锁竞争。
调整业务逻辑 SQL 执行顺序，避免 update/delete 长时间持有锁的 SQL 在事务前面。
避免大事务，将大事务拆成多个小事务
以固定的顺序访问表和行。比如两个更新数据的事务，事务 A 更新数据的顺序为 1，2;事务 B 更新数据的顺序为 2，1。这样更可能会造成死锁。
在并发比较高的系统中，不要显式加锁，特别是是在事务里显式加锁。如 select … for update 语句，如果是在事务里（运行了 start transaction 或设置了autocommit 等于0）,那么就会锁定所查找到的记录。
尽量用主键/索引去查找记录
优化 SQL 和表设计，减少同时占用太多资源的情况。比如说，避免多个表join，将复杂 SQL 分解为多个简单的 SQL。

数据库的隔离级别？

答案：读未提交、读已提交、可重复读（mysql的默认级别，每次读取结果都一样，但是有可能产生幻读）、串行化。

Mysql有哪些类型的索引？

答案：

普通索引：一个索引只包含一个列，一个表可以有多个单列索引。
唯一索引：索引列的值必须唯一，但允许有空值
复合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
聚簇索引：也称为主键索引，是一种数据存储方式。B+Tree结构，非叶子节点包含健值和指针，叶子节点包含索引列和行数据。一张表只能有一个聚簇索引。
非聚簇索引：不是聚簇索引，就是非聚簇索引。叶子节点只是存索引列和主键id。如果sql还要返回除了索引列的其他字段信息，需要回表，第一次索引一般是顺序IO，回表的操作属于随机IO。回表的次数越多，性能越差。此时我们推荐覆盖索引

什么是覆盖索引和回表？

答案：

1、覆盖索引，指的是在一次查询中，一个索引包含所有需要查询的字段的值，可能是返回值或where条件

select buyer_id from order where money>100

假如我们创建了一个(money，buyer_id)的联合索引，索引的叶子节点包含了buyer_id的信息，则不会再回表查询。

2、回表，指查询时一些字段值拿不到，需要到主键索引B+树再查一次。

Mysql的最左前缀原则？

答案：即最左优先，在检索数据时从联合索引的最左边开始匹配，直到遇到范围查询（如：> 、< 、between、like等）

例子：where a = 1 and b = 2 and c > 3 and d = 4 ，如果建立(a,b,c,d)组合索引，d是用不到索引的；如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

线上SQL的调优经验？

答案：

官方为什么建议采用自增id 作为主键？

答案：自增id是连续的，插入过程也是顺序的，总是插入在最后，减少了页分裂，有效减少数据的移动。所以尽量不要使用字符串（如：UUID）作为主键。

索引为什么采用B+树，而不用B-树，红黑树？

答案：提升查询速度，首先要减少磁盘IO次数，也就是要降低树的高度。

平衡二叉树、红黑树，都属于二叉树。时间复杂度为O(n)，当表的数据量上千万时，树的深度很深，mysql读取时消耗大量 IO。另外，InnoDB引擎采用页为单位读取，每个节点一页，但是二叉树每个节点储存一个关键词，导致空间浪费。
B-树，非叶子节点存储数据，占用较多空间，导致每个节点的指针少很多，无形增加了树的深度。
B+树数据都存储在叶子节点，非叶子节点只存储健值+指针，索引树更加扁平，三层深度可以支持千万级表存储。同时叶子节点之间通过链表关联，范围查找更快。
更多内容，参考 mysql 一棵 B+ 树能存多少条数据？

事务的特性有哪些？

答案：ACID。