mysql提高

轻飘飘的树海

已于 2022-09-01 12:01:29 修改

阅读量239

点赞数

文章标签： mysql 数据库大数据 b树

于 2022-08-26 23:09:37 首次发布

本文链接：https://blog.csdn.net/m0_46843134/article/details/126551452

版权

一、存储引擎层：

mysql5.5之前版本为MyISAM主要适合多查询，少更新。

5.5之后默认引擎为InnoDB，支持事务，对数据要求高，具有提交、回滚和恢复崩溃等能力来保护用户数据，支持外键，实现了四个隔离级别。

二、B树：多路平衡🌲（以InnoDB为例）

B+树：

b+：由b树改进而来，所以b树有的功能b+都有，最后一层的叶子节点指向下一个节点的指针，形成一个有序链表。

·只有叶子节点会存储数据，而搜索到关键字也不会直接返回，也仍然会回到最后一层的叶子节点。

·每个叶子节点增加了一个指向下一个叶子节点的指针，他最后一个数据指向下一个叶子节点的第一个数据，形成有序链表结构。

·关键字的数量和路数相等。

b+特点：扫库、扫表能力更强，效率稳定，磁盘读写能力更强，排序--链表。

三、索引优化：

主键索引（聚集索引）：叶子结点直接存储整条数据，也就是说👉🏻，索引搜索到叶子节点之后就可以直接返回数据了。只需扫描一个b+树（每一个表都必须有且只有一个主键，lnnodb会自动选择一个非空的唯一索引列为主键）

非聚集索引：非聚集索引的叶子节点存储的是当前索引的键值和主键索引的键值。也就是说👉🏻，查询数据的时候，获取到非聚集索引的叶子节点只能拿到当前索引值和主键索引值。要扫两个b+树。

注：不要用select * ，因为无法用到覆盖索引（只需要查询当前索引的值和主键索引的值，不需要查其他数据。）

创建索引格式：

#创建普通索引
CREATE INDEX index_name ON table_name (column_name);

#创建唯一索引
CREATE UNIQUE INDEX index_name ON table_name (column_name);

#创建前缀索引（对指定字段前 10 位作为索引，前缀索引可以节省空间）
CREATE INDEX index_name ON table_name (column_name(10));

#创建多列联合索引
CREATE INDEX index_name ON table_name (column_name1,column_name2);

三种最常见的索引优化方案：索引下推（ICP）、多范围读（MRR）、索引合并（INDEX MERGE）

索引下推：一般是存储引擎遍历索引定位行，再返回给service层，由service层计算行where的条件。在启用 ICP 的情况下，where中条件有索引，则sql服务器将where下推到存储引擎，通过存储引擎使用索引条目来计算已推入的索引条件，再读取行。ICP可以减少存储引擎必须访问基表的次数和 MySQL 服务器必须访问存储引擎的次数。

多范围读：先查询得到的辅助索引键值存放于缓存之中，数据对应辅助索引值，之后根据主键进行重排序，再回表（非聚集索引从叶子节点拿到数据（主键的键值）之后，还需要再根据主键键值去扫描主键索引的 B+ 树）就是顺序IO。

索引合并优化：MySQL 在 5.0 及之后的版本引入了的一种优化方案。这个意思就是我们在一个表中建立了很多单列索引，然后查询的时候同时用到了多列作为条件，MySQL 能够识别并分别使用单列索引进行扫描，然后将结果合并。

四、ACID与事物

A（Atomicity）原子性：要么都成功，要么都失败。

C（Consistent）一致性：事务开始之前和事务结束之后，数据库的完整性约束都没有被破坏，事务执行的前后都是合法的数据状态。

I（Isolation）隔离性：每个事务之间的操作应该相互隔离，互不干扰。

D（Durable）持久性：事务提交成功应该是持久的，即使是数据库重启，服务器宕机等情况发生，数据都不会丢失。

事物的分类：

扁平事务（最简单也是最常用）：要么全部成功，要么什么都不做，平常我们使用的事务绝大多数都属于扁平事务。

带有保存点的扁平事务：在指定位置定义好保存点，这样当事务处理到后面报错的时候，我们就可以不需要回滚整个事务，而是回滚到我们自定义好的某一个保存点。

begin;
insert into table values();
savepoint A;
insert.....;
rollback to A;
commit;
select * ...;--变成回滚前的数据了

链事务、嵌套事务、分布式事务

五、四大隔离级别

未提交读（Read Uncommitted）：RU，表示一个事务可以读取到其他事务未提交的数据，这种也叫做脏读。未提交读是最低的隔离级别，等于没有隔离，基本上没有数据库会使用这个级别。

已提交读（Read Committed）:表示一个事务只能读取到其他事务已提交的数据（已提交读是 Oracle 和 SQL Server 数据库默认的隔离级别）。就是说在一个事务里面，执行同样的查询，会出现两次不一样的结果。这种隔离级别解决了未提交读产生的脏读问题，但是会出现不可重复读的问题。

可重复读（Repeatable Read）:解决了不可重复读问题（有时候一个事务中出现不可重复读会影响到系统），就是说在同一个事务中，不管在任何时候执行相同的查询语句，结果都是一样的（对于如何实现可重复读我们在稍后介绍）。

串行化（Serializable）:隔离的最高级别，也就是说所有的事务都是串行执行的，也就不存在并发事务，脏读，可重复读和幻读问题自然也就没有了。

六、count区别

MyISAM 引擎中会把每张表的总行数记录在磁盘中，每次执行 count(*) 时，直接取出这个数返回即可，效率非常高。但是，InnoDB （因为事物特性）执行 count(*) 的时候需要一行行的把数据从存储引擎里面读出来，然后进行累积计数，最终返回计数结果。

count(字段)：如果这个字段被定义为 NOT NULL，InnoDB 在遍历整张表时，一行行的取出该字段，判断到不会为 NULL，则按行累加；如果这个字段没有定义为 NOT NULL，那么在取出字段之后还要再进行一次判断，判断到不为 NULL，才会进行累加。

count(主键 id)：InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 Server 层。Server 层拿到 id 后，判断是不可能为空的，则按行累加。

count(1)：InnoDB 引擎会遍历整张表，但不取值。

结论：count(*) ≈ count(1) > count(主键 id) > count(字段)。

两表连接：SELECT table1.column, table2.column
FROM table1, table2 WHERE table1.column1 = table2.column2;

asc升可省，desc降

查询前10个，来自深圳员工的姓名、年龄、城市，并且按照年龄小到大排序:select name,age,city from staff where city = '深圳' order by age limit 10;