MySQL-学习

MySQL-学习

MySQL学习笔记

1、事务隔离

隔离级别

1. 读未提交：一个事务还未提交时，做的变更就可被别的事务看到

2. 读提交：一个事务提交后，才可以被其他事务看到

3. 可重复读：一个事务执行过程中看到的数据，总跟这个事务在启动时看到的是一致的

4. 串行化：对于同一行记录进行加锁，访问串行化

Innodb事务的视图理解

在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准

配置方法：启动参数transaction-isolation

1. 串行化：直接加锁

2. 可重复读：事务启动时创建，整个事务存在期间都用这个视图

3. 读已提交：在sql执行时创建

4. 读未提交：无视图概念

事务的实现方式

可重复读：

事实上，在事务中，每条记录在更新时会同时记录一条回滚操作；通过回滚操作，每条记录可以得到上一个状态的值。

同一条记录在系统中可以存在有多个版本，即数据库的多版本并发控制。

回滚日志会在没有事务需要用到的时候被删除，所以不建议使用长事务，会一直存在，导致回滚日志过多。

避免长事务

客户端：

1. 在启动连接成功后，很多连接框架会默认先执行一个set autocommit=0的命令，这样就会把自动提交命令关掉，导致接下来的查询都在事务中，直到执行commit或rollback或断开连接。如果是长连接，就会导致意外的长事务。建议使用set autocommit=1的显示命令来启动事务，只有在需要时才启动事务。

2. 确认是否有只读事务，有的话尽量避免使用事务。

3. 通过set max_execution_time来控制每个语句的最大执行时间。

服务端：

监控长事务阈值，超过则报警或者kill。

事务plus之读规则：

InnoDB的行数据有多个版本，每个版本都有自己的row trx_id，通过之前的undo log（记录每一行的变化）来实现版本的回滚。每个事务或者语句都有自己的一致性视图，普通的查询是一致性读，是根据row trx_id的版本来确定查到的对应的行数据。版本在查询之前的则可见，在之后的则不可见。

1. 可重复读：核心是一致性读，查询只承认在事务启动之前就已经提交完成的事务。

2. 读提交：查询只承认在语句启动前就已经提交的事务。

3. 当前读：总是读取已经提交的最新版本。

事务更新操作时，只能是当前读（确保更新的为最新数据），如果当前记录的行锁被其他事务占用的话，就需要进入等待。（MySQL加行级锁的写法即由此而来）

2、索引

主键索引与非主键索引的区别

1. 主键索引是表的根本B+树，主键索引的叶子节点存的是整行的数据，在innodb中，主键索引也被成为聚簇索引。

2. 非主键索引也对应一个B+树，叶子节点存的是主键的值，在innodb中也被称为二级索引。

eg：

1. select * from T where ID=500，即主键查询方式，只需要搜索主键索引ID这棵B+树。

2. select * from T where k=5，即非主键查询，需要先搜索K这棵B+树，得到ID值，再到ID这棵B+树搜索一次，这个过程又叫做回表。

自增主键的使用

1. 主键索引的值应该尽量小，用自增索引比较合适。只有当只有一个索引且是唯一索引时，即k-v场景时，用业务字段做主键索引才比较合适。

2. 自增主键是插入式的数据模式，可以避免数据结构的调整，简化维护的过程

联合索引的技巧

eg：select * from T where k between 3 and 5;

这条语句会先查一个k的索引树，拿到k=3对应的ID，再查ID的索引树拿结果；

...

直到查到k=5；

期间需要多次回表，我们可以通过设计联合索引来避免多次回表的过程。

1. 覆盖索引：如果直接是select ID T where k between 3 and 5;这时由于ID本来就在k的索引树上，所以此时的查询不需要回表；

2. 联合索引：可以将需要的联合查询字段建立所以，并且满足最左前缀原则。即考虑到业务要求，将查找频率较高的字段进行靠左建立索引；

3. 索引下推：like 'hello%' and age>10;MySQL5.6以前需要每条like 'hello%'的记录都回表去判断age>10；而MySQL5.6以后，会在查找前先进行一次age>10的过滤，再去查找like 'hello%'，可以减少回表率，提升检索的速度。

3、全局锁、表锁和行锁

全局锁：

• Flush tables with read lock（FTWRL）;

• 典型使用场景为做全库的逻辑备份，如果是InnoDB引擎，建议使用事务的single-transaction参数，对应用会更加友好。

• MyISAM不支持事务，不支持可重复读，则需要全局锁使其数据一致。

表级锁：

1、显示：

• lock tables t1 read, t2 write;

• 在unlock tables 前，其他线程对t1的读和t2的写都会被锁住，而该线程也只能执行读t1和写t2操作，不能执行其他的操作，影响面较大。

2、隐示：MDL锁

• 在语句执行时加上，在整个事务都提交以后才会再释放，可以保证读写的正确性。所有增删改查操作会加读锁，对表结构做变更操作时会加写锁。

• 读锁之间不互斥，但读写锁、写锁之间是互斥的，用来保证变更表结构的操作是安全的。

• 如果某个表的查询频繁，而且超时后会再起一个新的session再请求的话，对其做更新表结构的操作一定要小心，很容易使库的线程爆满。

3、如何安全的给表加字段：

• 首先要解决长事务，如果事务不提交，就会一直占据着MDL锁。如果做DDL变更时恰好有一个长事务在执行，要考虑先暂停DDL或者kill长事务。

• 如果是一个热点表，kill事务不一定管用，可能很快又有新的请求进来。比较理想的方法是alter table里面设置等待时间，在这个时间能拿到MDL写锁则执行，拿不到则先放弃，不阻塞业务请求，然后不断手动重试。

行锁：

MyISAM引擎不支持行锁，InnoDB支持行锁

1、两阶段锁：

• 在InnoDB事务中，行锁是在需要的时候才会加上，但是却不是在不需要时就释放，而是等到事务结束时才释放。

• 如果事务中需要锁多个行，则要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。eg：购买电影票时，需要三个操作：A、从顾客账户扣钱；B、扣除电影院票余额；C、记录交易日志。此时需要将B操作后置，否则会影响到其他顾客的买票操作。

2、死锁和死锁检测：

• 当并发系统中出现线程之间资源循环依赖，涉及的线程在等待别的线程释放资源时，就会导致这几个线程进入无限等待状态，成为死锁。

• 出现死锁的策略有两种：A、直接进入等待，直到超时，innodb默认时间为50S；B、发起死锁检测，发现死锁后主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。正常情况下都是采用策略B，innodb默认开启。但是这样容易造成额外的负担，是一个复杂度为O(n)的操作，会造成大量的性能消耗。

• 其他优化的思路：C、如果可以确保不会出现死锁，则可以将死锁检测临时关掉，但是这样会有一定的风险；D、控制并发度，在服务端通过数据库中间件来控制访问的并发度，可以控制死锁检测的量级。

• 最后还有一种方式是考虑将一行数据改为逻辑上的多行来减少锁冲突，但是在代码上还需要做特殊的逻辑处理。

4、应用

1、唯一索引和普通索引：

1. 区别：

• 查询过程：普通索引找到满足条件的记录后，还需要继续向下找到第一个不满足条件的数据为止；唯一索引只需要找到满足条件的数据即停止。因为数据库都是分页查询，效率差异微乎其微。

• change buffer：更新操作时，如果数据页在内存中，则直接更新；如果数据页不在内存中，则维持一个change buffer的缓存区，缓存更新操作，之后后台定期merge或者在查询时merge。

• 更新过程：普通索引可以使用change buffer的形式更新；而唯一索引因为需要判断数据的唯一性，所以不能使用change buffer更新，当数据页不在内存中时，需要从磁盘中读取，所以IO消耗较大，效率相对普通索引更低。在业务保证的前提下，优先用普通索引。

1. change buffer：

• 当写多读少，且写完后很小概率会马上读到这行记录时，比较适合change buffer的形式：如财务报表，日志记录等；如果需要写入后马上查询，这时反而会成为负担。

• 与redo log的比较：change buffer是当数据页不在内存中时会记录下更新操作，是为了节省随机读磁盘的消耗，是当数据页不在内存时的更新操作；redo log会记录下所有的更新操作（包括change buffer的相关），是为了节省随机写磁盘的IO消耗（转为顺序写）。

2、MySQL索引选择：

一个表中可能存在多个索引，具体一个查询中选择哪一个索引，是由MySQL优化器来决定的。优化器会根据扫描行数、是否使用临时表和是否排序等因素进行综合判断。

1. 索引统计：

• MySQL在开始执行语句之前并不能精确知道满足这个条件的语句有多少条，所以只能根据统计数据来估算记录数。

• 这个统计数据就是索引的区分度，当一个索引上不同的值越多，这个索引的区分度就越好。统计的过程不是完全精确的，而是会选取随机的N个数据页的不同值进行统计，再乘上页面数得到索引的基数。当数据变更超过1/M后，就会自动触发再做一次索引统计。（N和M根据参数确定）

1. 索引回表：

• 如果是查询非主键索引，每从索引拿到一个值，还需要回到主键索引上去查整行数据，这个代价优化器也会算进去。

1. 修正统计：

• 通过analyze table t 命令可以强制重新统计索引信息，来修正索引统计。

1. 索引异常处理：

• eg：select * from t where a between 1 and 100 and b between 5000 and 10000 order by b limit 1; 本来应该选择索引a进行查询会比较快，但此时优化器会选择索引b。

• 采用force index语句，强行选择一个索引。如果force index(a)中的a索引在候选列表中，执行时就会强行使用a索引。这种方式，变更的及时性不强，修改起来麻烦。

• 考虑修改语句，引导MySQL使用我们期望的索引，比如语句中的order by b limit 1可以改成order by b, a limit 1。之前之所以选择b，是因为优化器认为选择b可以避免排序。而此时为按照b, a排序，都需要排序，就会使用到索引a。但这种方式也不具有通用性，只是这个场景恰巧合适。

• 新建索引来提供新的选择，或者删掉误用的索引。

3、字符串索引：

• 直接创建完整索引，这样可能比较占用空间。

• 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引。

• 倒序存储，再创建前缀索引，用于绕过字符串本身前缀区分度不够的问题。

• 创建hash字段索引，查询性能稳定，有额外的存储和计算消耗，跟倒序索引一样，都不支持范围索引。

4、WAL场景带来的抖动：

• WAL机制通过redo log的方式，即所有更新操作都只先记录该内存数据页的redo log，而不直接同步到磁盘数据页，只在空闲和必要时同步磁盘。这种方式将随机写转换成为顺序写，大大提升了数据库的性能。

• 但同时会带来数据脏页的问题，脏页会被后台线程自动flush（空闲和关机前），也会由于数据页被淘汰（可以是redo log写满了，也可以是内存数据页不够用后，刷新数据页）而触发，flush脏页会带来更新和查询性能的下降。

5、表的存储：

• 表的存储：共享表空间、单独.ibd后缀文件（现在的主流）；

• 表的删除：对于索引来说，delete命令只是标记删除，会对空间结构留下很多空洞；

• 解决办法：新建一个同样结构的表，再按id一条条插入，即不会有标记删除的空洞。或者 alter table A engine = InnoDB可以自动重建表；

• 重建表的过程：5.56后引入了online DDL，会在执行重建时将对表所有的操作都记录在一个row log中，执行完后再将操作应用到新表上。这个DDL执行时，alter语句会自动获取MDL写锁，但在执行过程会退化为读锁，并进行记录，所以只会阻塞其他DDL，而不会阻塞增删改查。

6、计算表的行数：

1. MySQL自带计数：

• MyISAM表：count(*)语句时会直接记录一个总行数，但是不支持事务。

• InnoDB表：由于支持MVCC，count(*)语句需要将数据一行一行的读出来，优化器会找到最小的索引树来进行遍历，在保证逻辑正确的前提下，尽量减少扫描的数据量。

• show table status：返回虽然很快，但是不准确。

1. 优化思路：

• Redis服务存储：优点：易操作且返回速度快。缺点：数据容易丢失，且数据不支持并发控制。原因在于两个不同存储结构组成的系统，不支持分布式事务，无法拿到精确的一致性视图。

• 单独表存储：解决了数据容易丢失的问题，且支持多版本并发控制。

1. count(字段)：和count(*)不同的地方在于，其只返回满足条件下，该字段不为null时的总个数。

7、表的排序：

1. select city, name, age from t where city = '杭州' order by name limit 1000;执行顺序：

• 1、初始化线程的sort_buffer，确定放入name、city、age；

• 2、从索引city找到第一个满足city='杭州'条件的主键id；

• 3、到主键id索引取出整行，取name、city、age三个字段的值放入sort_buffer；

• 4、从索引city中取下一个记录的主键id，重复2和3直到不满足条件；

• 5、对sort_buffer中的数据按照字段name做快速排序；

• 6、按照排序结果取前1000行返回。

1. 特殊情况

• 如果sort_buffer_size满足排序的数据量（单行*总行数），则在内存中排序，否则要利用磁盘临时文件，即为外部排序，一般采用归并排序，效率会降低；

• 如果MySQL认为排序单行长度太大（单行），会先只查主键id，然后在排序时只取id和name两个字段，在排序完成后再遍历一次取得name、city、age的结果；

• 核心思想是如果内存够，就多利用内存，尽量减少磁盘访问。

1. 排序优化

• 并不是所有的order by都是需要排序操作的，如果数据本身在存储时就是有序的，则不需要再进行一次排序了；

• 根据city_name创建联合索引，由于是先where city，再order name，如果city_name本身有序，则在查询过程中不需要创建临时表，也不需要再排序；

• 再进一步优化，则是创建city_name_age的联合索引，这样就不需要再回表了，即为覆盖索引。

1. 随机排序 select word from words order by rand() limit 3;

• 会创建一个内存临时表，存有word和一个double类型，然后遍历全表，对每个word生成一个随机0-1的double值，再初始化sort_buffer，从临时表中取出rand值和位置信息，存入sort_buffer中的两个字段，进行排序，最后取出最前面三个进行展示。

• 其中内存临时表有大小限制，超出了则会转为磁盘临时表，会使用优先队列排序算法，而不是归并排序，因为只需要获得最前的3行，相当于维持一个小根堆，不需要全部排序。

• 可以借助业务代码实现简单随机，即在外面根据总行数随机一个行数，再指定去select 时用limit指定第几行，则可以取到一个随机的行数。

8、索引字段做函数操作时，索引不生效

1. 对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器可能会放弃走树搜索功能。

where t_modified='2018-7-1'时会用索引，而where month(t_modified)=7时用不上索引

1. 隐式类型转换

比如当字符串与数字类型做比较时，默认会将字符串类型转为数字类型。这时会触发函数操作，优化器会放弃走树搜索功能。

1. 关联查询时，相关联的俩字段，字符集不同时，也会放弃走树搜索

• select d.* from tradelog l, trade_detail d where d.tradeid=l.id and l,id=2;(d为utf8,l为utf8mb4)

• utf8与utf8mb4是两个字符集，如果关联查询，会强转关联的字段，导致放弃走树搜索功能。

• select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2;

• 此时主动将l.tradeid转为了utf8，避免了被驱动表上的字符编码转换，可以使索引生效。