MySql语句优化以及索引介绍

最新推荐文章于 2024-10-01 17:33:05 发布

木木没有夕

最新推荐文章于 2024-10-01 17:33:05 发布

阅读量137

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/zz18532164242/article/details/105217760

版权

mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

1：mysql的存储引擎

InnoDB和MyISAM（主要是这两个其他可以忽略）

两者的特点和区别：

InnoDB:

灾难恢复性比较好；
支持事务，默认隔离级别为repeatable read。
使用的锁粒度为行级锁，可以支持更高的并发；
支持外键
在InnoDB中存在着缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度；
对于InnoDB类型的表，其数据的物理组织形式是聚簇表。所有的数据按照主键来组织。数据和索引放在一块，都位于B+数的叶子节点上；
5.6之后支持全文索引

MyISAM：

不支持事务；
不支持外键，如果强行增加外键，不会提示错误，只是外键不其作用；
默认的锁粒度为表级锁，所以并发度很差，加锁快，锁冲突较少，所以不太容易发生死锁；
支持全文索引（MySQL5.6之后，InnoDB存储引擎也对全文索引做了支持），但是MySQL的全文索引基本不会使用，对于全文索引，现在有其他成熟的解决方案，比如：ElasticSearch，Solr，Sphinx等。
数据库所在主机如果宕机，MyISAM的数据文件容易损坏，而且难恢复；

msyql中的事务隔离级别：

首先理解脏读、不可重复读和幻读的含义。

脏读：读到一个事务未提交的数据。

不可重复读：事务1读取了一行数据，但是事务未结束时，事务2对该数据进行修改了，事务1再次读取时，两次读取的数据不一样。

幻读：指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行。InnoDB存储引擎通过多版本并发控制（MVCC）解决了幻读的问题。

read uncommitted

总是读记录的最新版本数据，无论该版本是否已提交。

可能出现脏读、不可重复读、幻读。

无锁。

在业务中基本不会使用该级别。

read committed

事务中能看到其他事务已提交的修改。

可能出现不可重复读、幻读。

使用乐观锁（MVCC）。不使用范围锁。

是大多数数据库默认的隔离级别。

repeatable read

可对读取到的记录加锁 (记录锁)，同时保证对读取的范围加锁（范围锁），保证新的满足查询条件的记录不会被插入。

SQL规范下的repeatable read允许出现幻读，但InnoDB依靠范围锁，在repeatable read级别下也可避免幻读。

是InnoDB的默认隔离级别。

使用乐观锁（MVCC），使用范围锁。

seraliable

在操作的每一行数据上都加上锁，读取加S锁，DML加X锁。

使用悲观锁（LBCC）。

MVCC以及LBCC

MVCC(类似乐观锁的原理):多版本的并发控制协议

MVCC的实现，通过保存数据在某个时间点的快照来实现的。这意味着一个事务无论运行多长时间，在同一个事务里能够看到数据一致的视图。根据事务开始的时间不同，同时也意味着在同一个时刻不同事务看到的相同表里的数据可能是不同的。

在开始事务时，对每行数据添加一个版本号，在更新时假设数据不会发生变化，只在提交时对版本号进行对比，如果一直则成功，覆盖之前的数据，否则，提交失败，回滚。

另外, 对于read view快照的生成时机, 也非常关键, 正是因为生成时机的不同, 造成了RC,RR两种隔离级别的不同可见性;

在innodb中(默认repeatable read级别), 事务在begin/start transaction之后的第一条select读操作后, 会创建一个快照(read view), 将当前系统中活跃的其他事务记录记录起来;
在innodb中(默认repeatable committed级别), 事务中每条select语句都会创建一个快照(read view);

在RR级别下，快照读是通过MVVC(多版本控制)和undo log来实现的，当前读是通过加record lock(记录锁)和gap lock(间隙锁)来实现的。

innodb在快照读的情况下并没有真正的避免幻读, 但是在当前读的情况下避免了不可重复读和幻读!!!

LBCC:

LBCC全称Lock-Based Concurrent Control，即基于锁的并发控制，是一种悲观锁的实现。

LBCC中，对读会加S锁（共享锁），对写会加X锁（排它锁），即读读之间不阻塞，读写、写写之间会阻塞。

LBCC中的读是一致性锁定读，也称当前读：读取的是记录的最新版本，并且会对记录加锁。

快照读和当前读：

快照读：读取的是记录数据的可见版本（可能是过期的数据），不用加锁。

当前读：读取的是记录数据的最新版本，并且当前读返回的记录都会加上锁，保证其他事务不会再并发的修改这条记录。

mysql中的锁：

1：从粒度上分表锁和行锁。

2：从机制上分：乐观锁和悲观锁。

3：从类型上分：共享锁和排它锁

共享锁又称：读锁。当一个事务对某几行上读锁时，允许其他事务对这几行进行读操作，但不允许其进行写操作，也不允许其他事务给这几行上排它锁，但允许上读锁。

排它锁又称：写锁。当一个事务对某几个上写锁时，不允许其他事务写，但允许读。更不允许其他事务给这几行上任何锁。包括写锁（一般新增修改删除默认会加上排它锁）。

上共享锁的写法：lock in share mode

例如： select * from 表 where 条件 lock in share mode；

上排它锁的写法：for update

例如：select * from 表 where 条件 for update；

2：底层索引数据结构

B树和B+树的区别

B树：

B+树（叶子节点是双向指向的）：

hash:

二叉树：

mysql中B+树叶子节点中存储索引以及data数据，存储数据的地方叫做页，一个节点为一页（16kb）。

MySQL最终为什么要采用B+树存储索引结构呢，那么看看B-树和B+树在存储结构上有什么不同？

【1】B-树的每一个节点，存了关键字和对应的数据地址，而B+树的非叶子节点只存关键字，不存数据地址。因此B+树的每一个非叶子节点存储的关键字是远远多于B-树的，所以磁盘页能容纳更多节点元素，更“矮胖”。B+树的叶子节点存放关键字和数据，因此，从树的高度上来说，B+树的高度要小于B-树，使用的磁盘I/O次数少，因此查询会更快一些。
【2】B-树由于每个节点都存储关键字和数据，因此离根节点近的数据，查询的就快，离根节点远的数据，查询的就慢；B+树所有的数据都存在叶子节点上，因此在B+树上搜索关键字，找到对应数据的时间是比较平均的，没有快慢之分。
【3】在B-树上如果做区间查找，遍历的节点是非常多的；B+树所有叶子节点被连接成了有序链表结构，因此做整表遍历和区间查找是非常容易的。
【4】B+树只要遍历叶子节点就可以实现整棵树的遍历，而且在数据库中基于范围的查询是非常频繁的，而B树只能中序遍历所有节点，效率太低。

hash表优缺点：

优点：就如上述描述的这样，直接进行计算下标，直接查询单一数据非常快。

缺点：如果是进行select * from student where age>18;这样的范围查询的话，哈希索引就必须全表遍历，获得age数据，然后再依次进行比较，也就是相当于没有索引了。这样就不能优化查询效率了。

二叉树：

二叉树这个定义的本身就限制了它，即一个节点只能有两个子节点。所以当插入的数据非常多时，树的深度就会非常高。树的深度非常高的话就会影响查询效率。所以没有使用二叉树来当索引的。但是，它支持范围查询，因为二叉树是有序的。而且也可以提高查询效率，还是因为它是有序的，而且高度相比其它二叉树更平衡，通过二分法查询即可。但是，由于存储大量数据时高度太高，会影响效率。