MySQL索引、基础补充以及优化笔记-下

会编程的老六

已于 2023-03-23 12:44:27 修改

阅读量124

点赞数

分类专栏： MySQL 文章标签： mysql 数据库 sql

于 2021-10-11 22:37:56 首次发布

本文链接：https://blog.csdn.net/qq_47815451/article/details/120713475

版权

MySQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

MySQL索引、基础补充以及优化笔记-下

数据库优化

索引

引起索引失效的注意事项

全值匹配（要遵守）
最佳左前缀法则（要遵守）
不再索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
存储引擎不能使用索引中范围条件右边的列
尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），尽量不使用select *
mysql使用不等于（！=或者 <>）的时候无法使用索引会导致全表扫描
is null,is not null 也无法使用索引
like以通配符开头（‘%abc…’）mysql索引失效会变成全表扫描的操作（百分号尽量写右边）
字符串不加单引号索引失效
少用or，用它来连接时索引会失效

explain命令中key_len值是索引字段的最大可能长度，为索引字段设置的大小✖当前编码下对应的字符集占用字节数

遵守上述需要遵守的规则，并避免剩余规则即索引优化操作。尽量避免索引失效问题。

查询优化

类似嵌套循环：

优化原则：小表驱动大表，即小的数据集驱动大的数据集

例子：

select * from A where id in (select id from B)
//等价于
for select id from B
for select * from A where A.id = B.id
//当B表的数据集小于A表的数据集时，用in优于exists

select * from A where exists (select 1 from B where B.id = A.id)
//等价于
for select * from A
for select * from B where B.id = A.id
//当A表的数据集小于B表的数据集时，用exists优于in

//mysql机读顺序，优先执行括号内的查询
//注意：A表与B表的ID字段应建立索引

order by 优化

order by 子句，尽量使用Index方式排序，避免使用FileSort方式排序

MySQL支持两种方式排序，FileSort和Index，Index效率高，它指MySQL扫描索引本身完成排序，FileSort方式效率较低。

order by满足两情况，会使用index方式排序：

order by 语句使用索引最左前列
使用where子句与order by 子句条件列组合满足索引最左前列

尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀
如果不在索引列上，filesort有两种算法：mysql就要启动双路排序和单路排序

双路：第一遍扫描出需要排序的字段，然后进行排序后，根据排序结果，第二遍再扫描一下需要select的列数据。这回引起大量的随即IO，效率不高，但是节约内存。排序使用quick sort。但是如果内存不够则会按照block进行排序，将排序结果写入磁盘文件，然后再将结果合并。

单路：即一遍扫描数据后将select需要的列数据以及排序的列数据都取出来，这样就不需要进行第二遍扫描了，当然内存不足时也会使用磁盘临时文件进行外排。因为要把所有字段都取出，所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并），排完再取sort_buffer大小，从而多次I/O。

MySQL根据max_length_for_sort_data来判断排序时使用一遍扫描还是两遍扫描。如果需要的列数据一行可以放入max_length_for_sort_data则使用一遍扫描否则使用两遍>扫描。MySQL根据sort_buffer_size来判断是否使用磁盘临时文件，如果需要排序的数据能放入sort_buffer_size则无需使用磁盘临时文件，此时explain只会输出using filesort 否则需要使用磁盘临时文件explain会输出using temporary;using filesort。

当看到MySQL的explain输出using filesort不要太过紧张，这说明排序的时候没有使用索引，如果输出using temporary;using filesort则需要引起注意了，说明使了磁盘。临时文件，效率会降低。一句话using filesort需要酌情优化。

优化策略：

增大sort_buffer_size 参数的设置，使得单路可以一次i/o就结束
增大max_length_for_sort_data参数的设置
order by 时 select * 是一个大忌，只query需要的字段：

group by 关键字优化

几乎与order by 一致

group by实质是先排序后进行分组，组照索引建的最佳左前缀
无法使用索引列时，应增大max_length_for_sort_data参数的设置+增大sort_buffer_size 参数的设置
where高于having，能写在where限定的条件就不要去having限定了

慢SQL优化

从数据库的慢查询文件进行分析，优化的方式主要就是修改sql写法和新增索引。

优化步骤：

先查看慢日志，获得具体哪条sql语句是慢sql

window 下为文件my.ini , linux 下为my.cnf 文件：

   [mysqld] //在此标识之后添加
   slow_query_log = 1;　　#开启慢日志地址
　　slow_query_log_file=/var/lib/mysql/atguigu-slow.log　　　#慢日志地址，缺省文件名host_name-slow.log
　　long_query_time=3;　　  #运行时间超过该值的SQL会被记录，默认值>10
　　log_output=FILE

配置完成后，重启MySQL服务：service mysqld restart

再使用explain sql 语句，进行对慢sql分析

这一步也很重要，具体就是对explain的使用：explain推荐阅读在这里就不过多叙述。

修改sql语句，或者增加索引。

判断是否是索引失效

优化数据库结构

将字段很多的表分解成多个表，将使用频率高的字段单独分离出来形成一个表，使用频率低的字段单独分离出来形成一张表
增加中间表，对于经常联合查询的表，通过建立中间表的方式把经常联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表查询

分解关联查询

将一个大的查询分解为多个小的查询。对关联查询进行分解，对每一个表进行一次单表查询，将结果在程序中进行关联。例如：

select * from tag join tag_post on tag_id = tag.id join post on tag_post.post_id = post.id where tag.tag = 'mysql';

分解为：

select * from tag where tag = 'mysql';
select * from tag_post where tag_id = 1234;
select * from post where post.id in (123,456,789);

优化limit分页

偏移量非常大的时候，尽可能的使用索引覆盖扫描，而不是查询所有的列，然后根据需要做一个关联操作在返回所需的列。

也可以给筛选字段加上索引，还可以先查询出主键id，通过id的值直接查询id后面的数据。甚至可以建立复合索引acct_id 和create_time 尽量避免引起filesort。

分库分表

一般就是垂直切分和水平切分，这是一种结果集描述的切分方式，是物理空间上的切分。

我们从面临的问题，开始解决，阐述：首先是用户请求量太大，我们就堆机器搞定（这不是本文重点）。

然后是单个库太大，这时我们要看是因为表多而导致数据多，还是因为单张表里面的数据多。

如果是因为表多而数据多，使用垂直切分，根据业务切分成不同的库。

如果是因为单张表的数据量太大，这时要用水平切分，即把表的数据按某种规则切分成多张表，甚至多个库上的多张表。

分库分表的顺序应该是先垂直分，后水平分。 因为垂直分更简单，更符合我们处理现实世界问题的方式。

垂直拆分

垂直分表

也就是“大表拆小表”，基于列字段进行的。一般是表中的字段较多，将不常用的，数据较大，长度较长（比如text类型字段）的拆分到“扩展表“。一般是针对那种几百列的大表，也避免查询时，数据量太大造成的“跨页”问题。
垂直分库

垂直分库针对的是一个系统中的不同业务进行拆分，比如用户User一个库，商品Producet一个库，订单Order一个库。切分后，要放在多个服务器上，而不是一个服务器上。为什么？我们想象一下，一个购物网站对外提供服务，会有用户，商品，订单等的CRUD。没拆分之前，全部都是落到单一的库上的，这会让数据库的单库处理能力成为瓶颈。按垂直分库后，如果还是放在一个数据库服务器上，随着用户量增大，这会让单个数据库的处理能力成为瓶颈，还有单个服务器的磁盘空间，内存，tps等非常吃紧。所以我们要拆分到多个服务器上，这样上面的问题都解决了，以后也不会面对单机资源问题。

水平拆分

水平分表

针对数据量巨大的单张表（比如订单表），按照某种规则（RANGE,HASH取模等），切分到多张表里面去。但是这些表还是在同一个库中，所以库级别的数据库操作还是有IO瓶颈。不建议采用。
水平分库分表

将单张表的数据切分到多个服务器上去，每个服务器具有相应的库与表，只是表中数据集合不同。水平分库分表能够有效的缓解单机和单库的性能瓶颈和压力，突破IO、连接数、硬件资源等的瓶颈。
水平分库分表切分规则
1. RANGE
  
  从0到10000一个表，10001到20000一个表；
2. HASH取模
  
  一个商场系统，一般都是将用户，订单作为主表，然后将和它们相关的作为附表，这样不会造成跨库事务之类的问题。取用户id，然后hash取模，分配到不同的数据库上。
3. 地理区域
  
  比如按照华东，华南，华北这样来区分业务，七牛云应该就是如此。
4. 时间
  
  按照时间切分，就是将6个月前，甚至一年前的数据切出去放到另外的一张表，因为随着时间流逝，这些表的数据被查询的概率变小，所以没必要和“热数据”放在一起，这个也是“冷热数据分离”。

MVCC知识点（源于JavaGuide，总结很全面）

脏读：事务读取到其他事务没有提交的数据

不可重复读：同一次事务中前后查询不一致的问题

幻读：一次事务中前后数据量发生变化，用户产生不可预料的问题。另一个事务前后查询相同数据时的不符合预期。

innodb 引擎中隔离级别为可重复读时，也可以杜绝幻读的可能性。

更改操作涉及字段只有在索引列范围之内时，才会加上行锁。否则就加上表锁，使得程序不具备并发性。

在MySQL innodb存储引擎下读已提交和可重复读基于MVCC（多版本并发控制）进行并发事务控制，MVCC是基于“数据版本”对并发事务进行访问。

在 Repeatable Read 和 Read Committed 两个隔离级别下，如果是执行普通的 select 语句（不包括 select ... lock in share mode ,select ... for update）则会使用 一致性非锁定读（MVCC）。并且在 Repeatable Read 下 MVCC 实现了可重复读和防止部分幻读。

可重复读则是对涉及到的数据加读锁和写锁，并持有到事务结束，但不会加范围锁。这样就会出现幻读的问题，即一个事务内执行两次范围查询，如果这两次查询之间有新的数据被插入，就会导致两次范围查询的结果不一致。

读已提交和可重复读的区别是他的读锁会在查询操作结束之后立刻释放掉，这样，在事务执行过程中，已经查询过的数据是可以被其他事务任意修改的，所以也就会有不可重复读的问题。

读未提交级别下，则完全不会加读锁。这样造成的问题是，由于读操作时不会去申请读锁，所以反而会导致能够读到其他事务上加了写锁的数据，也就会出现脏读的问题。

undo-log

当事务回滚时用于将数据恢复到修改前的样子
另一个作用是 MVCC ，当读取记录时，若该记录被其他事务占用或当前版本对该事务不可见，则可以通过 undo log 读取之前的版本数据，以此实现非锁定读

在 InnoDB 存储引擎中 undo log 分为两种： insert undo log 和 update undo log：

insert undo log ：指在 insert 操作中产生的 undo log。因为 insert 操作的记录只对事务本身可见，对其他事务不可见，故该 undo log 可以在事务提交后直接删除。不需要进行 purge 操作
update undo log ：update 或 delete 操作中产生的 undo log。该 undo log可能需要提供 MVCC 机制，因此不能在事务提交时就进行删除。提交时放入 undo log 链表，等待 purge线程 进行最后的删除

不同事务或者相同事务的对同一记录行的修改，会使该记录行的 undo log 成为一条链表，链首就是最新的记录，链尾就是最早的旧记录。

隐藏字段

在内部，InnoDB 存储引擎为每行数据添加了三个隐藏字段：

DB_TRX_ID（6字节）：表示最后一次插入或更新该行的事务 id。此外，delete 操作在内部被视为更新，只不过会在记录头 Record header 中的 deleted_flag 字段将其标记为已删除
DB_ROLL_PTR（7字节） 回滚指针，指向该行的 undo log 。如果该行未被更新，则为空
DB_ROW_ID（6字节）：如果没有设置主键且该表没有唯一非空索引时，InnoDB 会使用该 id 来生成聚簇索引

ReadView

class ReadView {
  /* ... */
private:
  trx_id_t m_low_limit_id;      /* 大于等于这个 ID 的事务均不可见 */
    //目前出现过的最大的事务 ID+1，即下一个将被分配的事务 ID。大于等于这个 ID 的数据版本均不可见

  trx_id_t m_up_limit_id;       /* 小于这个 ID 的事务均可见 */
    //活跃事务列表 m_ids 中最小的事务 ID，如果 m_ids 为空，则 m_up_limit_id 为 m_low_limit_id。小于这个 ID 的数据版本均可见

  trx_id_t m_creator_trx_id;    /* 创建该 Read View 的事务ID */
    //创建该 Read View 的事务 ID

  trx_id_t m_low_limit_no;      /* 事务 Number, 小于该 Number 的 Undo Logs 均可以被 Purge */

  ids_t m_ids;                  /* 创建 Read View 时的活跃事务列表 */
    //Read View 创建时其他未提交的活跃事务 ID 列表。创建 Read View时，将当前未提交事务 ID 记录下来，后续即使它们修改了记录行的值，对于当前事务也是不可见的。m_ids 不包括当前事务自己和已提交的事务（正在内存中）

  m_closed;                     /* 标记 Read View 是否 close */
}

Read View 主要是用来做可见性判断，里面保存了 “当前对本事务不可见的其他活跃事务”。事务可见范围：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Yg0k183F-1633963051313)(…/…/…/…/个人图片/笔记图片/trans_visible.jpg)]

RC和RR隔离级别下MVCC的不同

在 RC 隔离级别下的 每次select 查询前都生成一个Read View (m_ids 列表)
在 RR 隔离级别下只在事务开始后 第一次select 数据前生成一个Read View（m_ids 列表）

一致性非锁定读与锁定读

在 InnoDB 存储引擎中，多版本控制 (multi versioning) 就是对非锁定读的实现。如果读取的行正在执行 DELETE 或 UPDATE 操作，这时读取操作不会去等待行上锁的释放。相反地，InnoDB 存储引擎会去读取行的一个快照数据，对于这种读取历史数据的方式，我们叫它快照读 (snapshot read)。对于 一致性非锁定读（Consistent Nonlocking Reads） 的实现，通常做法是加一个版本号或者时间戳字段，在更新数据的同时版本号 + 1 或者更新时间戳。查询时，将当前可见的版本号与对应记录的版本号进行比对，如果记录的版本小于可见版本，则表示该记录可见。
如果执行的是下列语句，就是 锁定读（Locking Reads）
- select ... lock in share mode
- select ... for update
- insert、update、delete 操作
在锁定读下，读取的是数据的最新版本，这种读也被称为 当前读（current read）。锁定读会对读取到的记录加锁：
- select ... lock in share mode：对记录加 S 锁，其它事务也可以加S锁，如果加 x 锁则会被阻塞
- select ... for update、insert、update、delete：对记录加 X 锁，且其它事务不能加任何锁