文章目录
《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
-
将外部表中相关的列放入Join Buffer中。
-
批量的将Key(索引键值)发送到Multi-Range Read(MRR)接口。
-
Multi-Range Read(MRR)通过收到的Key,根据其对应的ROWID进行排序,然后再进行数据的读取操作。
-
返回结果集给客户端。
开启方法:
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对返回结果进行限制
。。。。。。
更新中。。。