概述
索引的数据结构
- 二叉树
- 红黑树
- Hash表
- B-Tree
- 每个索引都可以选择不同的数据结构
使用红黑树保存索引数据,仍然会因为树的高度增加带来的查询效率的衰减,解决方案是每个节点多放几个索引数据,即B树,为了更高的查询效率,在B树的基础上衍生出B+树
B和B+树的区别
- 非叶子节点不保存数据,只保存树索引
- 叶子节点使用指针链接
B+树每个节点默认是16kb,每个索引14字节,所以一个节点可以存1170个索引元素
-
myISAM 和 InnoDB是形容表的,不是形容库的,所以每个表都能有不同的存储引擎
-
MyISAM中B+树索引的叶子节点的data元素保存的是数据库某一行的索引(在文件系统中索引和数据分两个文件存储),而InnoDB叶子节点中的data保存的就是某一行的数据(索引和文件保存在同一个文件中,即
聚集索引
) -
InnoDB表必须要有主键,并且推荐使用整型自增
每个节点中保存了很多的索引数据,如果没有使用UUID作为主键则需要先转换成为acsii进行比较。并且整型更节约空间
使用自增的原因是因为查找可能是范围查找,就可以直接遍历叶子节点
方便插入和删除(树的插入和删除复习) -
联合索引
按照联合索引顺序将多个索引统一保存在B+树的每一个节点中,查找时,如果节点中的第一个元素相同,则比较下一个,以此类推 -
每次查询的时候,文件系统最少会向内存中加载一页的数据(即一个节点16kb,类比操作系统的
局部性原理
) -
表中的每一行都会有一种行格式,比如COMPACT、Dynamic、Compress等。每一种行格式所记录的每一行的数据格式不尽相同,例如COMPACT格式记录的一行数据为:
变长字段长度列表+NULL标志位+记录头信息+列1数据+列2数据。。。- 变长字段长度列表:
按可变长字段顺序保存该字段长度,例如 name varchar(20) 是该列第3个元素,则在该变长字段长度列表的第三个位置保存实际存储的name的实际长度
注:innoDB每一行元素占用总内存不得超过65535字节,blobs类型字段除外。但是一页只有16k的数据,故超过一页大小的数据就需要分页
,不同的数据库引擎分页方式不同,例如InnoDB中,分页数据的第一页不存储数据,只存储下一页的地址,方便索引 - NULL标志位
表中的某些字段允许为空,则该字段中按照允许为空的字段顺序存储01,其中0代表该值为空,1代表不为空,例如00101
- 变长字段长度列表:
存储结构/索引
InnoDB
- InnoDB查询出来的数据会自动根据主键进行排序,原因是其主键默认也是索引。MySIAM查询出来的顺序是插入时的顺序
InnoDB数据存储格式
表中的数据以数据页为单位在磁盘中进行存储,每一页有16KB,每一页数据都会携带除用户数据以外的数据,例如下一页的数据,和该页的分组数据。例如:如果表中每一行元素只需要存很少数据,则一页数据就可以存很多行数据,所有的行数据形成一个链表,如果一页数据量过大,则查询效率会减小。
此时就会将这一页的数据分成n个组,例如(1-4)(5-9)…,每个组保存最小数据的指针,保存在页的头信息中,即分组数据
。
这样的话,如果查询某一个数据则会先去分组数据里面找,然后直接在命中的分组中进行查找。
-
目录页
一张表中的数据会存储在多个页中,那么查找数据时如何知道在第几页?
在这些页之上又抽象出一种目录页的结果(即B+树中的非叶子节点),目录页中存储了每一页的最小索引的位置 -
如果建表时既没有定义主键,也没有定义唯一列,则InnoDB会自动创建一个名为 rowID的自增字段
-
MySql在创建表时会自动创建一个空页,当向表中存数据时就会向该页存,当该页存满时,
会复制当前页到新的一页,然后再创建一个新页存储新的数据,并将第一页数据清空,升级为这两个新页的目录页
,这样做的好处是保持查找开始内存位置不变,将第一页常驻内存,加快查找速度 -
创建多个字段联合索引
会将这些字段拼接起来进行排序(建立索引的本质就是排序),并在该索引的B+树的叶子节点只保存数据行的主键
(即每创建一个索引都会创建一个B+树,但是除主键索引外,其他的都不保存行数据),然后根据该主键再去主键索引查找行数据
如果建立的索引列的数据都相似或者相同,则创建出的索引意义不大,Mysql会自动在这种索引中加上主键索引,保证索引唯一
可以使用 explain SQL 查看是否使用索引
注:
- mysql中的utf8格式其实只是真正的utf8的子集,它认为你不会存一些非常少见的字符,真正的utf8应该是utf8mb4
- 定义字段时也可以指定排序规则,比如对于字符串的比较,可以将其转换为ascii比较,也可以转换为二进制比较,使用以下方式设置
例如,以_bin结尾的就是转换为二进制比较
事务
使用begin/commit/rollback开启/提交/回滚事务,可以开启或关闭Mysql的自动提交(默认开启使用begin/commit/rollback开启/提交/回滚事务,可以开启或关闭Mysql的自动提交(默开)
-
隐式提交
使用ddl时会自动隐式提交数据 -
保存点
创建事务后,提交前,可以创建多个保存点。当需要回滚时可以指定回滚到某个保存点四种事务隔离
- 读未提交(Read Uncommitted)
事务即使没有提交,其他地方也能读到该未提交的数据
会出现脏读
- 读未提交(Read Uncommitted)
-
读已提交(Read Committed)
必须提交之后其他地方才能读到数据,但是其他地方的事务中,即使没有提交,但是表发生了变动,同一个事务中也会查出不同的结构
会出现幻读和不可重复读(两者本质相同) -
可重复读(Repeatable Read)
默认方式,同一个事务中,即时还没提交,表发生了变化,查出来的数据也是一样的 -
串行化
多个事务同时读没影响,但是如果有读写的话,读就会排队等待写完成。
实现
- 读已提交
假如两个事务A、B,及一条记录r=1,此时B创建事务修改r=2但还未提交,与此同时,A读取事务。依照隔离级别,此时A读取的数据应该是r=1,但是此时如果B提交了数据,则A在其事务内再次读取的值为r=2
每个事务都会有一个事务ID
每一行数据都会有一些除数据本身之外的其他数据,如之前说的rowID,当然也包括事务相关的transaction_id(6个字节的事务ID)及roll_pointer(7个字节的回滚指针)。transaction_id保存着最近修改该行的事务ID。版本链
表中的每一行数据都有一个版本链,每次创建事务(还没有提交)修改数据时都会在版本链头部添加一个新的版本,并将其transaction_id记录为新的事务ID,将roll_pointer设置为上个版本的地址
多版本回滚机制,关键字 undologReadView
Select语句默认会隐式地自动查询一个ReadView属性,该属性中又存在一个 m_id 的属性,m_id是一个列表,保存了当前行中所有未提交的事务ID,当查询某行数据时,从版本链中检索,如果节点的transaction_id存在于m_id中,则依照roll_pointer向下寻找
因此,当事务B提交了修改,事务A再次读取r,此时Select出来的ReadView中m_id 已经不包含事务B的事务ID了,故查出来的是新提交的数据,造成了不可重复读
多版本并发控制(MVCC)
使用了 版本链+ReadView 机制即实现了MVCC
- 可重复读
同 读已提交 ,但是在事务A中再次查询的时候,其ReadView中m_id保持不变
锁
读写锁
锁分为 读锁
(共享锁、S锁)和 写锁
(排他锁、X锁)
一个资源可以加多个读锁,只能由一个写锁。
但是Select语句会忽略锁,且不会加任何锁(区别于 select … for update)
注:
select ... for update
会将查找到的数据加上一个X锁delete
语句会在删除记录前对其加X锁insert
插入一条数据时,会先加隐式锁(可理解为X锁)来保护这条新插入的数据在提交前不被其他事务访问到
隐式锁:一个事务插入一条记录时,会向本版链中插入一个新节点,如果还没有提交,则该记录会保存本次事务id,而其他事务如果想对该记录加锁而发现事务ID不对应时,则才会产生一个X锁
update
如果修改前后没有导致存储空间的变化,则先会给记录加X锁再进行修改,如果由存储空间变化,则会先给记录加X锁,然后删除该记录,再insert一条新记录
行锁
分为:
- LOCK_REC_NOT_GAP: 单行记录上的锁
- LOCK_GAP: 间隙锁,锁定一个范围(修改的所有行,包括行的间隙,即不允许在锁住的两行记录之中插入数据),但不包括记录本身。GAP锁的目的时为了防止同一事务的两次读操作出现幻读
- LOCK_ORDINARY: 锁定一个范围,包含本身。对于行的查询,都是采用该方法,主要目的时解决幻读
- 读已提交:不管什么情况下,只会对查询出来的数据行使用行锁
- 可重复读:对行的修改操作会使得其在要修改记录行内插入间隙锁,锁住这些行和其间隙,用于解决幻读现象,此时,如果其他事务想在这个范围中修改数据,则会发生阻塞。
为什么要这样:假如在事务A中执行语句 update where age=12,假设会修改3条数据,并且age是一个索引。如果在没加间隙锁
的情况下另一个事务提交insert一条新的age=12的记录,由于age是索引,则该记录会插入在事务A查询的结果之中,则此时事务A会修改4条数据,出现了幻读
表锁
分为读写锁
如果当前表已经有行写锁了,则无法加表锁
因为加表锁需要遍历所有的行,查看是否存在行锁,开销极大,效率极低,所以在向表中加行锁时,会同时在表级别加意向锁(IS锁/IX锁),则如果需要向表加锁时,会出现锁冲突
mysql优化
-
SQL执行慢可能的原因:
- 查询语句写的不好
- 索引失效
- 关联查询太对join
- MySQL服务器调优及各个参数设置不合理
-
MySQL常见瓶颈
- CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候
- IO,磁盘IO瓶颈发生在装入数据远大于内存容量的时候
- 服务器硬件限制
Explain关键字
sql提交给MySQL之后,其会通过执行优化器进行优化,优化的过程可以使用explain进行查看
explain select * from user;
注:上图为两张表关联查询的情况,可以发现执行计划中有两个执行计划,分别代表两张表的执行过程,对执行计划的解释如下:
id
- 如果id相同,则表示sql执行时是从上往下执行的
- 如果id不同,则id值越大,越先执行
select_type
主要用于区别普通查询、联合查询、子查询等复杂查询
可取值:
SIMPLE:简单的select查询,查询中不包含子查询或UNION
PRIMARY:查询中若包含复杂的子部分,则最外层查询被标记为主查询(见上图)
SUMQUERY:在SELECT或WHERER列表中包含了子查询(见上图)
DERIVED:相当于一张查询出来的临时表(类似视图)
UNION:若第二个SELECT出现在UNION之后,则被标记为UNION。若UNION包含在FROM子句的子查询中,外层SELECT将被标记为DERIVED
UNION_RESULT:两个UNION查询之后出现的结果集被标记为该类型
type
查询方式类型
最好到最差依次为:system > const > eq_ref > ref > range > index > ALL
- system:表中只有一行记录,平时几乎用不到
- const:表示通过索引一次就找到了,const用于比较primary key或者unique索引。MySQL会将其优化为根据常量查询
- eq_ref:唯一性索引扫描,对于每个索引只有一条与之相匹配的数据。常见于主键或唯一索引
- ref:非唯一索引扫描,返回匹配某个单独值的所有行,可能找到多个符合条件的行
- range:只检索给定范围的行,使用一个索引来选择行,比如使用了 between/and in,一般需优化到该层面
- index:全索引扫描,会遍历所有的索引树
- all:全表扫描,百万级别的一般不建议用
possible_key/key
possible_key:可能用到的索引,但实际不一定用得到,比如给主键另建了一个索引后根据主键查询(主键本身已经是索引)
key:实际用到的索引,如果为NULL,则没有使用索引。如果出现了覆盖索引(select的字段和建立的索引正好匹配),则只会出现在key中而不会出现在possible_key中。
key_len
表示索引中使用的字节数,可通过该列计算查询中使用的索引长度。在不损失精确性的情况下,长度越短越好
key_len的值为索引最大可能长度,并非实际使用长度
ref
显示索引的哪一列被用到了
rows
根据表统计信息及索引选用情况,大致估算
出找到所需的记录所需要的行数
extra
除上述信息外其他非常重要的信息
- Using filesort:当使用sortby时,且被排序的列没有建立索引,则会导致其使用文件系统先进行排序,再查询,非常影响性能
- Using temporary:说明内部产生了临时表,常见用group by。极大影响性能。一般情况下,使用group by要根据其分组所用列建索引
- Using index:使用了覆盖索引,可以提高效率
- Using Where:使用了where
。。。(重要的就是前三个)
索引优化
索引的主要作用是 排序和查找
索引分析
- 单表查询
例如:某表有a、b、c、d 4列,a 为主键,根据条件where b=1 and c > 1 order by d limit 1
进行查询
情况1:没有索引
解释:由于没有任何索引,导致使用where时使用了全表扫描,并且,由于使用了sortby,导致了Using filesort
情况2:创建了(b,c,d)联合索引
解释:由于查询条件中存在索引列,所以搜索方式由全表扫描变成了范围查找。但是,由于orderby后于where c>1 执行,故没有用到索引,进而使用了Using filesort
注:联合索引的使用顺序是:先根据b排序,如果b相同,则根据c排序,如果c相同,则根据d排序。
上述查询中,由于使用了c>1的范围条件,导致其无法继续根据d进行排序,该索引失效(见下:索引失效第4条)
情况3:删除原索引,创建(b,d)联合索引
解释:去除了索引中的c,所以其执行顺序为:根据索引找到b=1的行,并筛选出当中的c>1的行,然后再根据d进行排序。故不存在Using filesort
- 两表查询
例子:A left join B on A.key=B.key
结论:应该在B表的key上建立索引
分析:因为左连接的特性是左表全都有,然后根据连接条件去右表中查询。故左表可以使用全表扫描,而右表则需要使用索引加快查询速度。右连接同理。
索引失效(应避免)
基本解决方法及注意要点:
解释:
最左前缀法则
:如果建立了多列的联合索引(a,b,c),查询时,必须有a作为条件才会使用该索引,即必须包含首个索引列
,例如a=1 and b=2 或者 a=1 and c=2 均能使用索引,但是类似 b=1 and c=2 就不会使用索引。但是 a=1 and c=2 只会使用a作为索引,跳过索引列只会使用跳过之前的列作为索引,即不能跳过索引的中间列
- 对联合索引中间列使用函数会使得索引失效,例如对联合索引(a,b,c)使用 where max(b) = 1,则b后面的索引c失效
- 对联合索引的某列做范围查询,其后续索引列失效
- 如果查询中,只select索引字段,则extra信息中会多出一个 Using index 字段,如果是select了索引以外的列,则没有该属性。即
最好只select索引的列
(覆盖索引) - 如何解决不能使用 like ‘%abc%’ 的问题?
解:使用覆盖索引,例如表中有一个 name 字段,则可以创建一个name索引,或以name开头的联合索引,select 只查询name或联合索引中的值,则会使用该索引
- 字符串查询需要加引号:例如表中数据name=’2000’,使用查询 where name=2000也可以查到,mysql底层会隐式地将2000转换为’2000’,此时就违反了索引列不能使用函数的原则
注:group by内部会需要排序,使用索引效果等同于orderby,但是其会产生中间表,极其影响性能
是否使用索引总结
sql性能分析
查询优化
- 最好使用小表驱动大表:例如 left join 左边应该是小表。小表驱动大表时,使用 exists 代替 in 效率更高
- orderby排序优化:尽量使用索引进行排序(即使用 Using index)而非MySQL内部排序(即 Using filesort)
- orderby使用多索引进行排序必须保持索引本身的顺序,不同于where。因为orderby使用不同列进行联合排序得到的结果是不同的
- 混用desc/asc会使用filesort而忽略索引
- 如果orderby的列不在索引上:其内部会使用filesort进行排序,分两种算法
- 双路排序(早期算法):进行两次磁盘扫描,第一遍扫描只读取指针和orderby列,对他们进行排序,再根据该排序顺序去磁盘一个一个取
- 单路排序:直接从磁盘读取所有的sekect的列,然后在排序buffer中对他们进行排序,避免了一次磁盘io。但是它会使用更多的空间,因为它把每一行所有列都放进内存buffer中了。而且,如果所需的行较大,buffer中放不下,则会执行多路希尔排序,将会产生比双路排序更多的磁盘io
- 为了解决单路排序的问题,可以调整MySQL的排序buffer相关的参数,sort_buffer_size:buffer容量,max_length_for_sort_data:单路排序最大排序数量,否则会使用双路排序
- groupby优化:由于内部会首先进行排序,故其优化方式同orderby。另:优先使用where而不是having进行条件查询
慢查询
查询时间超过MySQL配置的long_query_time的sql会被记录到慢查询日志。默认为10秒
其会产生一个log文件,可以直接使用打开查看,也可以使用工具查看慢查询,例如mysqldumpslow工具
例如,使用mysqldumpslow查询访问次数最多的sql:mysqldumpslow -s c -t 10 /var/…/mydb-slow.log
函数和存储过程
函数有返回值,存储过程没有
使用 create function 创建函数,使用 create procedure 创建存储过程。使用call myprocedure调用存储过程
show profile
比explain更强大的sql性能分析工具,可以用来分析当前会话中语句执行的资源详细消耗情况
-
如何使用?
- 查询最近使用的sql:
- 查询sql的每一步消耗时间(查看cpu和block io为例):
- 查询最近使用的sql:
-
如何根据上述数据判断sql是否出现问题?
- 当查询过程中出现了
converting HEAP to MyISAM
:表示查询结果太大,内存不够用了,开始往磁盘上搬了 - 出现
creating tmp table
:表示创建了临时表 - 出现
copying to tmp table on disk
:把内存中临时表复制到磁盘 - 出现
locked
- 当查询过程中出现了
MySQL锁机制
锁的分类
- 从数据操作类型分为:读写锁
- 从锁的粒度分为:行表锁
主从复制
-
复制的基本原理(类比redis使用rdb进行主从复制)
slave会从master读取binlog(即mysql的日志文件)来进行同步数据- 基本步骤
- master将改变记录到二进制日志 binlog 中。这些记录过程叫做二进制日志时间(binary log events);
- slave将master的binary log events拷贝到它的中继日志(relay log)
- slave重做中继日志中的事件,将改变应用到自己的数据库中。MySQL复制是异步的且是串行化的
- 基本步骤
-
复制的基本原则
- 每个slave只有一个master
- 每个slave只能有一个唯一的服务器ID
- 每个master可以有多个salve
-
一主一从配置
修改主从配置- 主:修改my.conf配置文件
设置server-id=1(保证主服务器id唯一即可)
启用二进制日志binlog:log-bin=/…/mysqlbin
- 主:修改my.conf配置文件
- 从:修改my.conf配置文件
设置server-id=2
启用二进制日志(可选)
在主机给从机创建账户授权复制
GRANT REPLICATION SLAVE ON *.* TO ‘username’@’slave-ip’ IDENTIFIED BY ‘12345’
使用 show master status 查看主机状态
解释:从mysqlbin.0000035 文件的第341的位置开始复制
给从机配置主机,及binlog文件和复制位置
CHANGE MASTER TO MASTER_HOST=’master-ip’, MASTER_USER=’username’, MASTER_PASSWORD=’12345′, MASTER_LOG_FILE=’mysqlbin.0000035′, MASTER_LOG_POS=341
使用 show slave status 查看是否连接成功
此时在主机进行操作,从机就会同步
分布式事务
Mysql如何保证数据被持久化?
Undolog和Redolog
undolog用于记录数据被修改前的值,redolog用于记录数据被修改后的新值
对数据的每一次修改都需要经理以下步骤:
将原数据保存在undolog —> 修改数据库的值(此时只是内存中修改) —> 将修改后的值存入redolog —> 持久化redolog(因为undolog也是记录在redolog中,所以只用持久化一次)—> 事务提交
注:事务提交就一定保证redolog已经被持久化了,即数据已经被持久化了
为什么不直接持久化数据而持久化redolog?
持久化数据需要对磁盘进行随机读写,二redolog使用文件追加的方式,更快
分布式事务:
-
分阶段提交
1.1 2PC
分两个阶段:
准备阶段:TM将事务请求发送给各个数据库执行,但不提交,数据库返回执行状态给TM
提交阶段:若TM收到的状态全部为成功,则向所有数据库发送commit命令。否则发送回滚指令。
优点:解决了分布式事务的问题,Mysql自己支持
缺点:属于阻塞式的,即多数据库向TM反馈准备阶段的结果时,所有的数据库都是阻塞加锁状态的。且单TM节点容易发生故障
注:由此衍生出3PC -
TCC(try-commit-cancel)
分两步:
2.1. TM向数据库请求预留资源(例如:要减少某个库存值,则可以在该表中添加一个字段即被冻结库存数量,try阶段就是设置冻结数量的值)
2.2. TM根据try阶段的执行结果进行让数据库执行commit或者cancel操作
与2PC的区别:
TCC的每个阶段都是独立commit的,避免了阻塞
优点:解决了2PC的阻塞问题
缺点:三个步骤都需要代码进行控制,增大了开发难度
- 可靠消息队列
A服务执行本地事务并向MQ中发送一条需要B服务执行的事务信息(例如:下单—>减库存 两个服务),B从MQ中获取到信息之后执行本地事务操作,如果失败就不断重试
优点:实现简单
缺点:延时比较高,例如转账操作。且及其依赖MQ的稳定性
-
AT模式
即Seate实现的模式
结合了2PC和TCC的优势,每次执行sql都会被Seate拦截,分析sql中修改的数据,并select出原始数据记录下来,然后执行业务sql,并记录新值,如果分布式事务执行成功,则删除记录的原值,如果失败,则先对比表中数据是否和记录的新值相同,如果相同,则修改为原值,如果不同则需要人工干预 -
saga模式