《Mysql性能优化和高可用架构实践：宋立桓》阅读笔记

《Mysql性能优化和高可用架构实践：宋立桓》阅读笔记

InnoDB存储引擎体系结构

缓冲池

​ InnoDB是基于磁盘存储的，但是为了解决CPU和磁盘之间速度的差距，引入了缓冲池技术提升整体性能。读取数据时，将读取到的页存放在缓冲池中，下次读相同的数据直接从缓存池中读取，数据的更新操作，首先在缓冲池中操作，然后定期写入磁盘。InnoDB缓存机制和MyISAM的缓存机制的区别是：InnoDB不仅缓存索引还缓存实际的数据。

​ 缓冲池的大小一般设置为内存总大小的50%-80%，设置方法：

set global innodb_biffer_pool_size=1073741824;
show variables like 'innodb_pool_size%';
--5.7之前需要在my.conf中设置，重启生效
--MYSQL5.7之后可以直接设置，不用重启mysql，但是设置期间会阻塞用户请求

​ InnoDB缓存池默认大小为16k。缓存数据页的换进换出使用了LRU（最近最少使用）算法。为了防止用户查询整张表等大量数据的查询操作。InnoDb加入了midpoint位置标记（innodb_old_blocks_pct），新加入的页并不是放在缓冲的首部，而是放在midpoint之后，等待innoDB_old_blocks_times时间之后才会加入热端。innodb_old_blocks_pct默认值为37（后37%），innoDB_old_blocks_times默认为1000。

二级索引知识点

​ mysql中每个表都有一个聚簇索引（clustered index ），除此之外的表上的每个非聚簇索引都是二级索引，又叫辅助索引（secondary indexes）。

​ 以InnoDB来说，每个InnoDB表具有一个特殊的索引称为聚集索引。如果您的表上定义有主键，该主键索引是聚集索引。如果你不定义为您的表的主键时，MySQL取第一个唯一索引（unique）而且只含非空列（NOT NULL）作为主键，InnoDB使用它作为聚集索引。如果没有这样的列，InnoDB就自己产生一个这样的ID值，它有六个字节，而且是隐藏的，使其作为聚簇索引。

后面半章有点难，代下次阅读再做笔记。。。。🤕

Mysql事务和锁

事务

​ 在mysql中，InnoDB存储引擎支持事务，并且符合ACID（原子性、一致性、隔离性、持久性）的特性。

Mysql事务的隔离级别

问题

​ 数据库在进行高并发操作的时候容易出现下面的问题：

• 脏读：当前事务读取到了其他事务还没有提交的数据。如：事务A读取到事务B修改还未提交的数据，然后事务B回滚数据，出现了脏读的现象。
• 不可重复读：在一个事务中，不同的时刻，读取同一个数据，但是读到的结果不同。主要针对的是更新操作，在第一次读取后其他的事务对数据进行了更新操作，导致第二次读取数据不一致。
• 幻读：主要是针对数据的插入来说的。一个事务读取到了其他事务新增的数据。

隔离级别

​ 为了解决上面的情况，数据库提出了四种隔离级别，由低到高依次为：

• 读未提交（READ UNCOMMITTED）：并发性能最强，但是安全性和隔离性是最差的

• 读提交 （READ COMMITTED）：读取事务允许其他事务访问该数据，但是写事务禁止其他事务在提交之前访问改行数据（该级别无法避免不可重复的问题，如：事务A读取了一条数据，紧接着事务B对该行数据进行了修改并提交，事务A第二次读取数据变化了）

• 可重复读 （REPEATABLE READ）：读取数据的事务不允许写数据的事务，只允许其他读事务，写数据的事务不允许所有事务

• 序列化（SERIALIZABLE）：事务只能一个接着一个执行，不允许并发。

隔离级别	脏读	不可重复读	幻读
读未提交	✅	✅	✅
读提交	❌	✅	✅
可重复读	❌	❌	✅
序列化	❌	❌	❌

⚠️Mysql默认的隔离级别是可重复读 （REPEATABLE READ）

设置隔离级别

--查询数据库当前的隔离级别
show variables like 'tx_isolation'
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
--设置隔离级别
--set [作用域session/global] transaction isolation level [事务隔离级别]
set global transaction isolation level read uncommitted;

InnoDB的锁机制

锁的种类

• 行级锁：共享（S）锁又称为读锁、排他（X）锁又称为写锁

• 表级锁：意向锁，目的是为了防止DDL（数据库定义语言）和DML（数据库操作语言insert、update、delete）的并发问题

  • 意向共享锁（IS LOCK）
  • 意向排他锁（IX LOCK）

• 根据上锁的范围不同，行级锁又可以分为：

  • 单行记录的记录锁（Record Locks）
  • 区间锁（Gap Locks，间隙锁）：锁定一个范围，锁定的是索引记录的区间
  • Next-key Lock：上面两种的结合

⚠️InnoDB上的行级锁是通过给索引上的索引项加锁来实现的，所以只有通过索引查询的操作才会使用行级锁，如果查询的条件不是索引，就会使用表级锁。

如何加锁

​ 意向锁是InnoDB自动加的，不需要用户干预。

​ 对于增加删除修改操作，InnoDB会自动给涉及的数据加X Lock；对于一般的查询语句，InnoDB不会加任何锁，事务可以通过以下语句给显示加共享锁或排他锁。

--共享锁
select * from table_name where .....lock in share mode
--排他锁：
select * from table_name where .....for update

SQL语句性能优化

InnoDB的索引结构

InnoDB的索引使用的是B+Tree的结构。

• B+Tree和BTree的区别：B+Tree是所有的数据都在叶子节点，非叶子节点不存储真正的数据。所有的叶子节点都是由指针连接，可以顺序访问。

  数据结构测试：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

聚集索引和辅助索引

​ 一个表的主键是聚集索引，如果没有主键，InnoDB会取表的唯一索引，而且只包含非空列作为主键，使用它作为聚集索引。如果没有这样的列，InnoDB会自己维护一个隐藏的ID（6字节）作为聚集索引；

​ 除了聚集索引之外其他的所有索引都是辅助索引。

​ 辅助索引和聚集索引的区别就是，辅助索引的所有叶子节点存储的是主键的值，而聚集索引的叶子节点存储的是表的一行全部数据，如果查询使用的是辅助索引，会先通过辅助索引查到主键的值，再通过聚集索引查询出主键所在行的所有字段。

Index Condition Pushdown

​ ICP是通过InnoDB对与查询的数据进行过滤，减少从基表中读取数据的数量，从而降低IO所消耗的时间，提升性能。

开启方法：

SET optimizer_switch="index_condition_pushdown=on/off"

开启ICP之后，Extra列为：using index condition,不开启为：using where

案例：

--People表有一个二级索引INDEX（pcode,lastName,firstName）
--需要执行的语句
select * from people where pcode=100 and lastName like '%a%' and firstName like '%b%';

​ 如果没有开启ICP会通过二级索引中的pcode值去找出基表中所有pcode=100的数据，然后service参对数据进行其他两个条件的过滤。

其他调试用到的语句：

--为了不让缓存影响ICP对查询效率的提升
--关闭Query Cache
set query_cache_type=0;
set global query_cache_size=0;
--启用profiling
set profiling=1
show profiles;

--要查看执行计划，在sql语句之前加上explain

Multi-Range Read Optimization(MRR)

​ MRR是优化器将随机IO转化为顺序IO，减少了磁盘的随机访问。

​ 通过二级索引查询数据时，需要根据二级索引返回的主键id在聚集索引树里进行会表，从而得到数据。MRR的优化就是，取到二级索引的主键后，不直接访问聚集索引，而是把主键id缓存起来，对其进行排序后再去访问聚集索引查询记录，实现了IO从随机到顺序的转化。

开启方法：

set optimizer_switch='mrr=on,mrr_cost_based=off';
----参数解释
--mrr=on ：开启mrr
--mrr_cost_based=on ：表示cost base方式开启mrr，当发现优化后的代价过高时就不会使用该项优化，off为始终开启mrr 

适用场景：

​ 通过二级索引查询范围内的数据。

Batched Key Access

1. 将外部表中相关的列放入Join Buffer中。

2. 批量的将Key（索引键值）发送到Multi-Range Read（MRR）接口。

3. Multi-Range Read（MRR）通过收到的Key，根据其对应的ROWID进行排序，然后再进行数据的读取操作。

4. 返回结果集给客户端。

开启方法：

set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

慢SQL优化思路

慢sql概念了解

​ 慢sql不仅指查询语句，指DML语句执行时间超过了指定时间。

查询慢sql相关的参数：

SHOW VARIABLES LIKE '%QUER%'

Variable_name	Value
log_queries_not_using_indexes	OFF	--sql没有使用索引是否也记录到慢查询日志中
log_throttle_queries_not_using_indexes	0	--每分钟记录上面情况慢sql的次数
long_query_time	10.000000	--超过10秒被认定为慢sql
query_prealloc_size	8192
slow_query_log	OFF	--开启慢查询日志
slow_query_log_file	/var/lib/mysql/iz91j7hvad15xbz-slow.log	--慢查询日志存放位置

分析慢sql日志：

• 使用mysql自带的mysqldumpslow工具分析

mysqldumpslow -t 10 /var/lib/mysql/xxxx-slow.log

--参数解释
-- -s按照哪种方式排序：c（记录次数）、t（时间）、l（查询时间）、r（返回的记录数），前面加a表示倒序
-- -t返回top多少条数据
-- -g正则匹配

explain对sql语句分析

在select前面加explain可以展示sql语句在执行时候的每一步信息

id	Select_type	table	partitions	type	Possible_keys	key	Key_len	ref	rows	filtered	Extra
1	SIMPLE	s	NULL	ALL	PRIMARY	NULL	NULL	NULL	46	10.00	Using wher

1. id

id列的编号是 select 的序列号，有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的。MySQL将 select 查询分为简单查询和复杂查询。复杂查询分为三类：简单子查询、派生表（from语句中的子查询）、union 查询。

2. select_type

select_type 表示对应行是是简单还是复杂的查询，如果是复杂的查询，又是上述三种复杂查询中的哪一种。

​ 1）simple：简单查询。查询不包含子查询和union

​ 2）primary：复杂查询中最外层的 select

​ 3）subquery：包含在 select 中的子查询（不在 from 子句中）

​ 4）derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表（derived的英文含义）

​ 5）union：在 union 中的第二个和随后的 select

​ 6）union result：从 union 临时表检索结果的 select

3. table

​ 这一列表示 explain 的一行正在访问哪个表。

​ 当 from 子句中有子查询时，table列是 格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。当有 union 时，UNION RESULT 的 table 列的值为 <union1,2>，1和2表示参与 union 的 select 行id。

4. type列

这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行。

依次从好到差分别为：NULL>system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

​ 1）NULL：mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表

​ 2）const, system：mysql能对查询的某部分进行优化并将其转化成一个常量（可以看show warnings 的结果）。用于 primary key 或 unique key 的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快。

​ 3）eq_ref：primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。这可能是在 const 之外最好的联接类型了，简单的 select 查询不会出现这种 type。

​ 4）ref：相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。

​ 5）ref_or_null：类似ref，但是可以搜索值为NULL的行。

​ 6）index_merge：表示使用了索引合并的优化方法。 例如下表：id是主键，tenant_id是普通索引。or 的时候没有用 primary key，而是使用了 primary key(id) 和 tenant_id 索引

​ 7）range：范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。

​ 8）index：和ALL一样，不同就是mysql只需扫描索引树，这通常比ALL快一些。

​ 9）**ALL**全表扫描，意味着mysql需要从头到尾去查找所需要的行。通常情况下这需要增加索引来进行优化了

5. possible_keys列

​ 使用到了那些索引

6. key列

这一列显示mysql实际采用哪个索引来优化对该表的访问。

如果没有使用索引，则该列是 NULL。如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 force index、ignore index。

7. key_len列

这一列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。

8. ref列

这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），func，NULL，字段名（例：film.id）

9. rows列

这一列是mysql估算要找到结果要读取的行数

10. Extra列

这一列展示的是额外信息。常见的重要值如下：

​ 1）distinct: 一旦mysql找到了与行相联合匹配的行，就不再搜索了

​ 2）Using index：这发生在对表的请求列都是同一索引的部分的时候，返回的列数据只使用了索引中的信息，而没有再去访问表中的行记录。是性能高的表现。

​ 3）Using where：mysql服务器将在存储引擎检索行后再进行过滤。就是先读取整行数据，再按 where 条件进行检查，符合就留下，不符合就丢弃。

​ 4）Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。

​ 5）Using filesort：mysql 会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。此时mysql会根据联接类型浏览所有符合条件的记录，并保存排序关键字和行指针，然后排序关键字并按顺序检索行信息。这种情况下一般也是要考虑使用索引来优化的。

索引的优化

1.联合索引最左前缀原则

联合索引的建立，如果在一个表建立（a、b、c）的索引，会自动创建三个索引a，ab，abc。

• 如果只查询bc不会使用索引
• 对于联合索引，MySQL 会一直向右匹配直到遇到范围查询（> ， < ，between，like）就停止匹配。比如 a = 3 and b = 4 and c > 5 and d = 6，如果建立的是（a,b,c,d）这种顺序的索引，那么 d 是用不到索引的，但是如果建立的是 （a,b,d,c）这种顺序的索引的话，那么就没问题，而且 a，b，d 的顺序可以随意调换。（把非等值条件放到索引最后）
• = 和 in 可以乱序，比如 a = 3 and b = 4 and c = 5 建立 （a，b，c）索引可以任意顺序。

何时会使用联合索引

​ 假设联合索引为abc

• 只是通过and连接的条件只要用到了最左侧a列，和顺序无关 都会使用 索引

a = 1 AND b = 2 AND c = 3 ; --使用索引(abc)
c = 1 AND b = 2 AND a = 3 ; --使用索引(abc)
a = 1 AND b = 2 ; --使用索引(ab)
a = 1 AND c = 3 ; --使用索引(a)
c = 1 AND a = 2 ; --使用索引(a)

• 不包含最左侧的 a 的不使用索引

c = 3 ; --不使用索引
b = 2 ; --不使用索引
b = 2 AND c = 3 ; --不使用索引
c = 1 AND b = 2 ; --不使用索引

• OR 不使用索引（只要有or就不使用索引）

a = 1 AND b = 2 OR c = 3 --不使用索引
a = 1 OR b = 2 AND c = 3 --不使用索引
a = 1 OR b = 2 OR c = 3 --不使用索引

• 最左侧的‘a’列 被大于，小于，不等于比较的 ，不使用索引

a > 1 AND b = 2 AND c = 3  未使用索引
a < 1 AND b =  2 AND c = 3  未使用索引
a > 1 ; 未使用索引
a <> 1 AND b = 2 AND c = 3 未使用索引

• 最左侧a=某某，后面列大于小于无所谓，都使用索引（但后面必须 and and ）

a = 1 AND b < 2 AND c = 3 --使用索引(ab)
a = 1 AND c = 2 AND b < 3 --使用索引(abc)????
a = 1 AND b < 2 --使用索引
a = 1 AND b <> 2 AND c = 3 --使用索引
a = 1 AND b < 2 OR c = 2 --未使用索引

2.like语句的前导模糊不会使用索引

select * from xxx where a like '%XX'；   --不使用索引 
select * from xxx where a like 'XX%'；   --使用索引 

3.union、in、or 都能够命中索引，建议使用 in

4.负条件查询不会使用索引

• 负向条件有：!=、<>、not in、not exists、not like 等

5.不在索引列上面做任何操作(计算、函数)，否则会导致索引失效而转向全表扫描

6.强制类型转换会全表扫描

7.更新频繁的咧不宜创建索引

8.尽量使用索引一次查询结果，避免使用select * 会有回表操作

9.索引不会包含有NULL值的列

• 复合索引中如果某一列的值包含null，则此列索引失效

10.使用limit对返回结果进行限制

。。。。。。

更新中。。。