2、SQL语句的执行过程:
2.1、客户端的数据库驱动与数据库连接池:
(1)客户端与数据库进行通信前,通过数据库驱动与MySQL建立连接,建立完成之后,就发送SQL语句
(2)为了减少频繁创建和销毁连接造成系统性能的下降,通过数据库连接池维护一定数量的连接线程,当需要进行连接时,就直接从连接池中获取,使用完毕之后,再归还给连接池。常见的数据库连接池有 Druid、C3P0、DBCP
2.2、MySQL架构的Server层的执行过程:
(1)连接器:主要负责跟客户端建立连接、获取权限、维持和管理连接
(2)查询缓存:优先在缓存中进行查询,如果查到了则直接返回,如果缓存中查询不到,在去数据库中查询。
MySQL缓存是默认关闭的,也就是说不推荐使用缓存,并且在MySQL8.0 版本已经将查询缓存的整块功能删掉了。这主要是它的使用场景限制造成的:
先说下缓存中数据存储格式:key(sql语句)- value(数据值),所以如果SQL语句(key)只要存在一点不同之处就会直接进行数据库查询了;
由于表中的数据不是一成不变的,大多数是经常变化的,而当数据库中的数据变化了,那么相应的与此表相关的缓存数据就需要移除掉;
(3)解析器/分析器:分析器的工作主要是对要执行的SQL语句进行词法解析、语法解析,最终得到抽象语法树,然后再使用预处理器对抽象语法树进行语义校验,判断抽象语法树中的表是否存在,如果存在的话,在接着判断select投影列字段是否在表中存在等。
(4)优化器:主要将SQL经过词法解析、语法解析后得到的语法树,通过数据字典和统计信息的内容,再经过一系列运算 ,最终得出一个执行计划,包括选择使用哪个索引
在分析是否走索引查询时,是通过进行动态数据采样统计分析出来;只要是统计分析出来的,那就可能会存在分析错误的情况,所以在SQL执行不走索引时,也要考虑到这方面的因素
(5)执行器:根据一系列的执行计划去调用存储引擎提供的API接口去调用操作数据,完成SQL的执行。
2.3、Innodb存储引擎的执行过程:
(1)首先MySQL执行器根据 执行计划 调用存储引擎的API查询数据
(2)存储引擎先从缓存池buffer pool中查询数据,如果没有就会去磁盘中查询,如果查询到了就将其放到缓存池中
(3)在数据加载到 Buffer Pool 的同时,会将这条数据的原始记录保存到 undo 日志文件中
(4)innodb 会在 Buffer Pool 中执行更新操作
(5)更新后的数据会记录在 redo log buffer 中
(6)提交事务在提交的同时会做以下三件事
(7)(第一件事)将redo log buffer中的数据刷入到redo log文件中
(8)(第二件事)将本次操作记录写入到 bin log文件中
(9)(第三件事)将bin log文件名字和更新内容在 bin log 中的位置记录到redo log中,同时在 redo log 最后添加 commit 标记
(10)使用一个后台线程,它会在某个时机将我们Buffer Pool中的更新后的数据刷到 MySQL 数据库中,这样就将内存和数据库的数据保持统一了
3、常用的存储引擎?InnoDB与MyISAM的区别?
存储引擎是对底层物理数据执行实际操作的组件,为Server服务层提供各种操作数据的API。常用的存储引擎有InnoDB、MyISAM、Memory。这里我们主要介绍InnoDB 与 MyISAM 的区别:
(1)事务:MyISAM不支持事务,InnoDB支持事务
(2)锁级别:MyISAM只支持表级锁,InnoDB支持行级锁和表级锁,默认使用行级锁,但是行锁只有通过索引查询数据才会使用,否则将使用表锁。行级锁在每次获取锁和释放锁的操作需要消耗比表锁更多的资源。使用行锁可能会存在死锁的情况,但是表级锁不存在死锁
(3)主键和外键:MyISAM 允许没有任何索引和主键的表存在,不支持外键。InnoDB的主键不能为空且支持主键自增长,如果没有设定主键或者非空唯一索引,就会自动生成一个6字节的主键,支持外键完整性约束
(4)索引结构:MyISAM 和 InnoDB 都是使用B+树索引,MyISAM的主键索引和辅助索引的Data域都是保存行数据记录的地址。但是InnoDB的主键索引的Data域保存的不是行数据记录的地址,而是保存该行的所有数据内容,而辅助索引的Data域保存的则是主索引的值。
由于InnoDB的辅助索引保存的是主键索引的值,所以使用辅助索引需要检索两遍索引:首先检索辅助索引获得主键,然后用主键到主索引中检索获得记录。这也是为什么不建议使用过长的字段作为主键的原因:由于辅助索引包含主键列,所以,如果主键使用过长的字段,将会导致其他辅助索变得更大,所以争取尽量把主键定义得小一些。
(5)全文索引:MyISAM支持全文索引,InnoDB在5.6版本之前不支持全文索引,5.6版本及之后的版本开始支持全文索引
(6)表的具体行数:
① MyISAM:保存有表的总行数,如果使用 select count() from table 会直接取出出该值,不需要进行全表扫描。
② InnoDB:没有保存表的总行数,如果使用 select count() from table 需要会遍历整个表,消耗相当大。
(7)存储结构:
① MyISAM会在磁盘上存储成三个文件:.frm文件存储表定义,.MYD文件存储数据,.MYI文件存储索引。
② InnoDB:把数据和索引存放在表空间里面,所有的表都保存在同一个数据文件中,InnoDB表的大小只受限于操作系统文件的大小,一般为2GB。
(8)存储空间:
① MyISAM:可被压缩,存储空间较小。支持三种不同的存储格式:静态表(默认,但是注意数据末尾不能有空格,会被去掉)、动态表、压缩表。
② InnoDB:需要更多的内存和存储,它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。
(9)适用场景:
① 如果需要提供回滚、崩溃恢复能力的ACID事务能力,并要求实现行锁级别并发控制,InnoDB是一个好的选择;
② 如果数据表主要用来查询记录,读操作远远多于写操作且不需要数据库事务的支持,则MyISAM引擎能提供较高的处理效率;
备注:在mysql8.0版本中已经废弃了MyISAM存储引擎
4、事务的ACID与实现原理?
数据库的事务是并发控制的基本单位,是指逻辑上的一组操作,要么全部执行,要么全部不执行。
4.1、事务的ACID:
(1)原子性:事务是一个不可分割的工作单元,事务里的操作要么都成功,要么都失败,如果事务执行失败,则需要进行回滚。
(2)隔离性:事务的所操作的数据在提交之前,对其他事务的可见程度。
(3)持久性:一旦事务提交,它对数据库中数据的改变就是永久的。
(4)一致性:事务不能破坏数据的完整性和业务的一致性。例如在转账时,不管事务成功还是失败,双方钱的总额不变。
4.2、ACID的实现原理:
4.2.1、原子性:原子性是通过MySQL的回滚日志undo log来实现的:当事务对数据库进行修改时,InnoDB会生成对应的undo log;如果事务执行失败或调用了rollback,导致事务需要回滚,便可以利用undo log中的信息将数据回滚到修改之前的样子。
4.2.2、隔离性:
(1)事务的隔离级别:
为保证在并发环境下读取数据的完整性和一致性,数据库提供了四种事务隔离级别,隔离级别越高,越能保证数据的完整性和一致性,但对高并发性能影响也越大,执行效率越低。(四种隔离级别从上往下依次升高)
读未提交:允许事务在执行过程中,读取其他事务尚未提交的数据;
读已提交:允许事务在执行过程中读取其他事务已经提交的数据;
可重复读(默认级别):在同一个事务内,任意时刻的查询结果都是一致的;
读序列化:所有事务逐个依次执行,每次读都需要获取表级共享锁,读写会相互阻塞。
(2)事务的并发问题:
如果不考虑事务的隔离性,在事务并发的环境下,可能存在问题有:
更新丢失:两个或多个事务操作相同的数据,然后基于选定的值更新该行时,由于每个事务都不知道其他事务的存在,就会发生丢失更新问题:最后的更新覆盖了其他事务所做的更新。
脏读:指事务A正在访问数据,并且对数据进行了修改(事务未提交),这时,事务B也使用这个数据,后来事务A撤销回滚,并把修改后的数据恢复原值,B读到的数据就与数据库中的数据不一致,即B读到的数据是脏数据。
不可重复读:在一个事务内,多次读取同一个数据,但是由于另一个事务在此期间对这个数据做了修改并提交,导致前后读取到的数据不一致;
幻读:在一个事务中,先后两次进行读取相同的数据(一般是范围查询),但由于另一个事务新增或者删除了数据,导致前后两次结果不一致。
不同的事务隔离级别,在并发环境会存在不同的并发问题:
![](data/attachment/forum/202303/04/a066f1bd3104d06fb7664bc2455c3685.jpg)
(3)事务隔离性的实现原理:
Innodb事务的隔离级别是由MVVC和锁机制实现的:
① MVCC(Multi-Version Concurrency Control,多版本并发控制)是 MySQL 的 InnoDB 存储引擎实现事务隔离级别的一种具体方式,用于实现读已提交和可重复读这两种隔离级别。而读未提交隔离级别总是读取最新的数据行,无需使用 MVCC。读序列化隔离级别需要对所有读取的行都加锁,单纯使用 MVCC 无法实现。
MVCC是通过在每行记录后面保存两个隐藏的列来实现的,一个保存了行的事务ID,一个保存了行的回滚段指针。每开始一个新的事务,都会自动递增产生一个新的事务ID。事务开始时会把该事务ID放到当前事务影响的行事务ID字段中,而回滚段的指针有该行记录上的所有版本数据,在undo log回滚日志中通过链表形式组织,也就是说该值实际指向undo log中该行的历史记录链表。
在并发访问数据库时,对正在事务中的数据做MVCC多版本的管理,以避免写操作阻塞读操作,并且可以通过比较版本解决快照读方式的幻读问题,但对于当前读的幻读,MVCC并不能解决,需要通过临键锁来解决。
② 锁机制:
MySQL锁机制的基本工作原理就是:事务在修改数据库之前,需要先获得相应的锁,获得锁的事务才可以修改数据;在该事务操作期间,这部分的数据是锁定,其他事务如果需要修改数据,需要等待当前事务提交或回滚后释放锁。
排它锁解决脏读
共享锁解决不可重复读
临键锁解决幻读
4.2.3、持久性:
持久性的依靠redo log日志实现,在执行SQL时会保存已执行的SQL语句到一个redo log文件,但是为了提高效率,将数据写入到redo log之前,会先写入到内存中的redo log buffer缓存区中。写入过程如下:当向数据库写入数据时,执行过程会首先写入redo log buffer,redo log buffer中修改的数据会定期刷新到磁盘的redo log文件中,这一过程称为刷盘(即redo log buffer写日志到磁盘的redo log file中 )。
redo log buffer的使用可以大大提高了读写数据的效率,但是也带了新的问题:如果MySQL宕机,而此时redo log buffer中修改的数据在内存还没有刷新到磁盘,就会导致数据的丢失,事务的持久性无法保证。为了确保事务的持久性,在当事务提交时,会调用fsync接口对redo log进行刷盘 ,刷新频率由 innodb_flush_log_at_trx_commit变量来控制的:
0:表示不刷入磁盘;
1:事务每次提交的时候,就把缓冲池中的数据刷新到磁盘中;
2:提交事务的时候,把缓冲池中的数据写入磁盘文件对应的 os cache 缓存里去,而不是直接进入磁盘文件。可能 1 秒后才会把 os cache 里的数据写入到磁盘文件里去。
4.2.4、一致性:
一致性指的是事务不能破坏数据的完整性和业务的一致性 :
数据的完整性: 实体完整性、列完整性(如字段的类型、大小、长度要符合要求)、外键约束等
业务的一致性:例如在银行转账时,不管事务成功还是失败,双方钱的总额不变。
5、数据库中的锁机制?
当数据库中多个事务并发存取同一数据的时候,若对并发操作不加控制就可能会读取和存储不正确的数据,破坏数据库的一致性。MySQL锁机制的基本工作原理就是,事务在修改数据库之前,需要先获得相应的锁,获得锁的事务才可以修改数据;在该事务操作期间,这部分的数据是锁定,其他事务如果需要修改数据,需要等待当前事务提交或回滚后释放锁。
按照不同的分类方式,锁的种类可以分为以下几种:
按锁的粒度划分:表级锁、行级锁、页级锁;
按锁的类型划分:共享(锁S锁)、排他锁(X锁);
按锁的使用策略划分:乐观锁、悲观锁;
5.1、表级锁、行级锁、页级锁:
表级锁:最大粒度的锁级别,发生锁冲突的概率最高,并发度最低,但开销小,加锁快,不会出现死锁;
行级锁:最小粒度的所级别,发生锁冲突的概率最小,并发度最高,但开销大,加锁慢,会发生死锁;
页级锁:锁粒度界于表级锁和行级锁之间,对表级锁和行级锁的折中,并发度一般。开销和加锁时间也界于表锁和行锁之间,会出现死锁;
不同的存储引擎支持不同的锁机制:
InnoDB存储引擎支持行级锁和表级锁,默认情况下使用行级锁,但只有通过索引进行查询数据,才使用行级锁,否就使用表级锁。
MyISAM和MEMORY存储引擎采用的是表级锁;
BDB存储引擎使用的是页面锁,但也支持表级锁;
5.2、InnoDB的行锁:
InnoDB的行锁有两种类型:
共享锁(S锁、读锁):多个事务可以对同一数据行共享一把S锁,但只能进行读不能修改;
排它锁(X锁、写锁):一个事务获取排它锁之后,可以对锁定范围内的数据行执行写操作,在锁定期间,其他事务不能再获取这部分数据行的锁(共享锁、排它锁),只允许获取到排它锁的事务进行更新数据。
对于update,delete,insert 操作,InnoDB会自动给涉及的数据行加排他锁;对于普通SELECT语句,InnoDB不会加任何锁。
5.3、InnoDB的表锁与意向锁:
因为InnoDB引擎允许行锁和表锁共存,实现多粒度的锁机制,但是表锁和行锁虽然锁定范围不同,但是会相互冲突。当你要加表锁时,势必要先遍历该表的所有记录,判断是否有排他锁。这种遍历检查的方式显然是一种低效的方式,MySQL引入了意向锁,来检测表锁和行锁的冲突。
意向锁也是表级锁,分为读意向锁(IS锁)和写意向锁(IX锁)。当事务要在记录上加上行锁时,则先在表上加上对应的意向锁。之后事务如果想进行锁表,只要先判断是否有意向锁存在,存在时则可快速返回该表不能启用表锁,否则就需要等待,提高效率。
5.4、InnoDB行锁的实现与临键锁:
InnoDB的行锁是通过给索引上的索引项加锁来实现的。只有通过索引检索数据,才能使用行锁,否则将使用表锁。
在InnoDB中,为了解决幻读的现象,引入了临键锁(next-key)。根据索引,划分为一个个左开右闭的区间。当进行范围查询的时候,若命中索引且能够检索到数据,则锁住记录所在的区间和它的下一个区间。其实,临键锁(Next-Key) = 记录锁(Record Locks) + 间隙锁(Gap Locks)
间隙锁:当使用范围查询而不是精准查询进行检索数据,并请求共享或排它锁时,InnoDB会给符合范围条件的已有数据记录的索引项加锁;对于键值在条件范围内但并不存在的记录,叫做间隙(GAP)。
记录锁:当使用唯一索引,且记录存在的精准查询时,使用记录锁
5.5、利用锁机制解决并发问题:
X锁解决脏读
S锁解决不可重复读
临键锁解决幻读
6、MySQL索引的实现原理:
索引本质上就是一种通过减少查询需要遍历行数,加快查询性能的数据结构,避免数据库进行全表扫描,好比书的目录,让你更快的找到内容。(一个表最多16个索引)
6.1、索引的优缺点:
(1)索引的优点:
减少查询需要检索的行数,加快查询速度,避免进行全表扫描,这也是创建索引的最主要的原因。
如果索引的数据结构是B+树,在使用分组和排序时,可以显著减少查询中分组和排序的时间。
通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。
(2)索引的缺点:
当对表中的数据进行增加、删除和修改时,索引也要进行更新,维护的耗时随着数据量的增加而增加。
索引需要占用物理空间,如果要建立聚簇索引,那么需要的空间就会更大。
6.2、索引的使用场景:
(1)在哪些列上面创建索引:
WHERE子句中经常出现的列上面创建索引,加快条件的判断速度。
按范围存取的列或者在group by或order by中使用的列,因为索引已经排序,这样可以利用索引加快排序查询时间。
经常用于连接的列上,这些列主要是一些外键,可以加快连接的速度;
作为主键的列上,强制该列的唯一性和组织表中数据的排列结构;
(2)不在哪些列建索引?
区分度不高的列。由于这些列的取值很少,例如性别,在查询的结果中,结果集的数据行占了表中数据行的很大比例,即需要在表中搜索的数据行的比例很大。增加索引,并不能明显加快检索速度。
在查询中很少的列不应该创建索引。由于这些列很少使用到,但增加了索引,反而降低了系统的维护速度和增大了空间需求。
当添加索引造成修改成本的提高 远远大于 检索性能的提高时,不应该创建索引。当增加索引时,会提高检索性能,但是会降低修改性能。当减少索引时,会提高修改性能,降低检索性能。
定义为text, image和bit数据类型的列不应该增加索引。这些列的数据量要么相当大,要么取值很少。
6.3、 索引的分类:
(1)普通索引、唯一索引、主键索引、全文索引、组合索引。
普通索引:最基本的索引,没有任何限制
唯一索引:但索引列的值必须唯一,允许有空值,可以有多个NULL值。如果是组合索引,则列值的组合必须唯一。
主键索引:一种特殊的唯一索引,不允许有空值。
全文索引:全文索引仅可用于 MyISAM 表,并只支持从CHAR、VARCHAR或TEXT类型,用于替代效率较低的like 模糊匹配操作,而且可以通过多字段组合的全文索引一次性全模糊匹配多个字段。
组合索引:主要是为了提高mysql效率,创建组合索引时应该将最常用作限制条件的列放在最左边,依次递减。
(2)聚簇索引与非聚簇索引:
如果按数据存储的物理顺序与索引值的顺序分类,可以将索引分为聚簇索引与非聚簇索引两类:
聚簇索引:表中数据存储的物理顺序与索引值的顺序一致,一个基本表最多只能有一个聚簇索引,更新聚簇索引列上的数据时,往往导致表中记录的物理顺序的变更,代价较大,因此对于经常更新的列不宜建立聚簇索引
非聚簇索引:表中数据的物理顺序与索引值的顺序不一致的索引组织,一个基本表可以有多个聚簇索引。
6.4、索引的数据结构:
常见的索引的数据结构有:B+Tree、Hash索引。
(1)Hash索引:MySQL中只有Memory存储引擎支持hash索引,是Memory表的默认索引类型。hash索引把数据以hash值形式组织起来,因此查询效率非常高,可以一次定位。
hash索引的缺点:
Hash索引仅能满足等值的查询,不能满足范围查询、排序。因为数据在经过Hash算法后,其大小关系就可能发生变化。
当创建组合索引时,不能只使用组合索引的部分列进行查询。因为hash索引是把多个列数据合并后再计算Hash值,所以对单独列数据计算Hash值是没有意义的。
当发生Hash碰撞时,Hash索引不能避免表数据的扫描。因为仅仅比较Hash值是不够的,需要比较实际的值以判定是否符合要求。
(2)B+Tree索引:B+Tree是mysql使用最频繁的一个索引数据结构,是Innodb和Myisam存储引擎模式的索引类型。B+Tree索引在查找时需要从根节点到叶节点进行多次IO操作,在查询速度比不上Hash索引,但是更适合排序等操作。
B+Tree索引的优点:
页内节点不存储内容,每次IO可以读取更多的行,大大减少磁盘I/O读取次数
带顺序访问指针的B+Tree:B+Tree所有索引数据都存储在叶子结点上,并且增加了顺序访问指针,每个叶子节点都有指向相邻叶子节点的指针,这样做是为了提高区间查询效率。
6.5、为什么使用B+Tree作为索引:
索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储在磁盘上。这样的话,索引查找过程中就要产生磁盘I/O消耗,相对于内存存取,磁盘I/O存取的消耗要高几个数量级,所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说,索引的数据结构要尽量减少查找过程中磁盘I/O的存取次数。
(1)局部性原理与程序预读:
由于磁盘本身存取就比主存慢很多,再加上机械运动耗费,因此为了提高效率,要尽量减少磁盘I/O。为了达到这个目的,磁盘往往不是严格按需读取,而是每次都会预读,即使只需要一个字节,磁盘也会从这个位置开始,顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理:当一个数据被用到时,其附近的数据也通常会马上被使用。程序运行期间所需要的数据通常比较集中。
由于磁盘顺序读取的效率很高(不需要寻道时间,只需很少的旋转时间),因此对于具有局部性的程序来说,预读可以提高I/O效率。预读的长度一般为页的整倍数。当程序要读取的数据不在主存中时,会触发一个缺页异常,此时系统会向磁盘发出读盘信号,磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中,然后异常返回,程序继续运行。
(2)B+Tree索引的性能分析:
上文说过一般使用磁盘I/O次数评价索引结构的优劣。我们先从B树分析,B树检索一次最多需要访问h个节点,同时,数据库巧妙利用了磁盘预读原理,将一个节点的大小设为等于一个页,即每次新建节点时,直接申请一个页的空间,这样就保证一个节点在物理上也存储在一个页里,加之计算机存储分配都是按页对齐的,这样就实现了每个节点只需要一次I/O就可以完全载入。B树中一次检索最多需要h-1次I/O(根节点常驻内存),时间复杂度为O(h)=O(logdN)。一般实际应用中,出度d是非常大的数字,通常超过100,因此h非常小。综上所述,用B树作为索引结构效率是非常高的。
而红黑树这种结构,虽然时间复杂度也为O(h),但是h明显要深的多,并且由于逻辑上很近的节点,在物理上可能很远,无法利用局部性,所以IO效率明显比B树差很多。
另外,B+Tree更适合作为索引的数据结构,原因和内节点出度d有关。从上面分析可以看到,d越大索引的性能越好,而出度d的上限取决于节点内key和data的大小,由于B+Tree内节点去掉了data域,因此可以拥有更大的出度,磁盘IO的次数也就更少了。
(3)B+树索引 和 B树索引 的对比?
根据B-Tree 和 B+Tree的结构,我们可以发现B+树相比于B树,在文件系统或者数据库系统当中,更有优势,原因如下:
(1)B+树有利于对数据库的扫描:B树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题,而B+树只需要遍历叶子节点就可以解决对全部关键字信息的扫描,所以范围查询、排序等操作,B+树有着更高的性能。
(2)B+树的磁盘IO代价更低:B+树的内部结点的data域并没有存储数据,因此其内部结点相对于B树更小。如果把所有同一内部结点的关键字存放在同一盘块中,那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多,相对来说I/O读写次数也就降低了。
(3)B+树的查询效率更加稳定:由于B+树的内部结点只是叶子结点中关键字的索引,并不存储数据。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同,导致每一个数据的查询效率相当。
(4)MySQL的 InnoDB 和 MyISAM 存储引擎中B+Tree索引的实现?
MyISAM和InnoDB都是使用B+树索引,MyISAM的主键索引和辅助索引的Data域都是保存行的地址,但是InnoDB的主键索引保存的不是行的地址,而是保存该行的所有所有数据,而辅助索引的Data域保存的则是主索引的值。
索引的长度限制:
对于 Innodb 的组合索引,如果各个列中的长度超过767字节的,则会对超过767字节的列取前缀索引;对于 Innodb 的单列索引,如果列的长度超过767的,则取前缀索引(取前255字符)
对于 MyISAM 的组合索引,所创建的索引长度和不能超过1000 bytes,否则会报错,创建失败;对于 MyISAM 的单列索引,最大长度也不能超过1000,否则会报警,但是创建成功,最终创建的是前缀索引(取前333个字符)
7、SQL优化和索引优化、表结构优化:
8、数据库参数优化:
MySQL属于 IO 密集型的应用程序,主要职责就是数据的管理及存储工作。而我们知道,从内存中读取一个数据库的时间是微秒级别,而从一块普通硬盘上读取一个IO是在毫秒级别,二者相差3个数量级。所以,要优化数据库,首先第一步需要优化的就是 IO,尽可能将磁盘IO转化为内存IO。所以对于MySQL数据库的参数优化上,主要针对减少磁盘IO的参数做优化:比如使用 query_cache_size 调整查询缓存的大小,使用 innodb_buffer_pool_size 调整缓冲区的大小;
9、explain的执行计划:
执行计划是SQL语句经过查询分析器后得到的 抽象语法树 和 相关表的统计信息 作出的一个查询方案,这个方案是由查询优化器自动分析产生的。由于是动态数据采样统计分析出来的结果,所以可能会存在分析错误的情况,也就是存在执行计划并不是最优的情况。通过explain关键字知道MySQL是如何执行SQL查询语句的,分析select 语句的性能瓶颈,从而改进我们的查询,explain的结果如下:
![](data/attachment/forum/202303/04/23fdc6d5053a6afe529bd9774842e3a9.png)
重要的有id、type、key、key_len、rows、extra:
(1)id:id列可以理解为SQL执行顺序的标识,有几个select 就有几个id。
id值不同:id值越大优先级越高,越先被执行;
id值相同:从上往下依次执行;
id列为null:表示这是一个结果集,不需要使用它来进行查询。
(2)select_type:查询的类型,主要用于区分普通查询、联合查询、子查询等复杂的查询;
(3)table:表示 explain 的一行正在访问哪个表
(4)type:访问类型,即MySQL决定如何查找表中的行。依次从好到差:system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL,除了all之外,其他的 type 类型都可以使用到索引,除了 index_merge 之外,其他的type只可以用到一个索引。一般要求type为 ref 级别,范围查找需要达到 range 级别。
system:表中只有一条数据匹配(等于系统表),可以看成 const 类型的特例
const:通过索引一次就找到了,表示使用主键索引或者唯一索引
eq_ref:主键或者唯一索引中的字段被用于连接使用,只会返回一行匹配的数据
ref:普通索引扫描,可能返回多个符合查询条件的行。
fulltext:全文索引检索,全文索引的优先级很高,若全文索引和普通索引同时存在时,mysql不管代价,优先选择使用全文索引。
ref_or_null:与ref方法类似,只是增加了null值的比较。
index_merge:表示查询使用了两个以上的索引,索引合并的优化方法,最后取交集或者并集,常见and ,or的条件使用了不同的索引。
unique_subquery:用于where中的in形式子查询,子查询返回不重复值唯一值;
index_subquery:用于in形式子查询使用到了辅助索引或者in常数列表,子查询可能返回重复值,可以使用索引将子查询去重。
range:索引范围扫描,常见于使用>,<,between ,in ,like等运算符的查询中。
index:索引全表扫描,把索引树从头到尾扫描一遍;
all:遍历全表以找到匹配的行(Index与ALL虽然都是读全表,但index是从索引中读取,而ALL是从硬盘读取)
NULL:MySQL在优化过程中分解语句,执行时甚至不用访问表或索引
(5)possible_keys:查询时可能使用到的索引
(6)key:实际使用哪个索引来优化对该表的访问
(7)key_len:实际上用于优化查询的索引长度,即索引中使用的字节数。通过这个值,可以计算出一个多列索引里实际使用了索引的哪写字段。
(8)ref:显示哪个字段或者常量与key一起被使用
(9)rows:根据表统计信息及索引选用情况,大致估算此处查询需要读取的行数,不是精确值。
(10)extra:其他的一些额外信息
using index:使用覆盖索引
using index condition:查询的列未被索引覆盖,where筛选条件使用了索引
using temporary:用临时表保存中间结果,常用于 group by 和 order by 操作中,通常是因为 group by 的列上没有索引,也有可能是因为同时有group by和order by,但group by和order by的列又不一样,一般看到它说明查询需要优化了
using filesort:MySQL有两种方式对查询结果进行排序,一种是使用索引,另一种是filesort(基于快排实现的外部排序,性能比较差),当数据量很大时,这将是一个CPU密集型的过程,所以可以通过建立合适的索引来优化排序的性能
10、MySQL的主从复制:
10.1、MySQL主从复制的原理:
Slave从Master获取binlog二进制日志文件,然后再将日志文件解析成相应的SQL语句在从服务器上重新执行一遍主服务器的操作,通过这种方式来保证数据的一致性。由于主从复制的过程是异步复制的,因此Slave和Master之间的数据有可能存在延迟的现象,只能保证数据最终的一致性。在master和slave之间实现整个复制过程主要由三个线程来完成:
(1)Slave SQL thread线程:创建用于读取relay log中继日志并执行日志中包含的更新,位于slave端
(2)Slave I/O thread线程:读取 master 服务器Binlog Dump线程发送的内容并保存到slave服务器的relay log中继日志中,位于slave端:
(3)Binlog dump thread线程(也称为IO线程):将bin-log二进制日志中的内容发送到slave服务器,位于master端
注意:如果一台主服务器配两台从服务器那主服务器上就会有两个Binlog dump 线程,而每个从服务器上各自有两个线程;
10.2、主从复制流程:
(1)master服务器在执行SQL语句之后,记录在binlog二进制文件中;
(2)slave端的IO线程连接上master端,并请求从指定bin log日志文件的指定pos节点位置(或者从最开始的日志)开始复制之后的日志内容。
(3)master端在接收到来自slave端的IO线程请求后,通知负责复制进程的IO线程,根据slave端IO线程的请求信息,读取指定binlog日志指定pos节点位置之后的日志信息,然后返回给slave端的IO线程。该返回信息中除了binlog日志所包含的信息之外,还包括本次返回的信息在master端的binlog文件名以及在该binlog日志中的pos节点位置。
(4)slave端的IO线程在接收到master端IO返回的信息后,将接收到的binlog日志内容依次写入到slave端的relay log文件的最末端,并将读取到的master端的binlog文件名和pos节点位置记录到master-info文件中(该文件存slave端),以便在下一次同步的候能够告诉master从哪个位置开始进行数据同步;
(5)slave端的SQL线程在检测到relay log文件中新增内容后,就马上解析该relay log文件中的内容,然后还原成在master端真实执行的那些SQL语句,再按顺序依次执行这些SQL语句,从而到达master端和slave端的数据一致性;
10.3、主从复制的好处:
(1)读写分离,通过动态增加从服务器来提高数据库的性能,在主服务器上执行写入和更新,在从服务器上执行读功能。
(2)提高数据安全,因为数据已复制到从服务器,从服务器可以终止复制进程,所以,可以在从服务器上备份而不破坏主服务器相应数据。
(3)在主服务器上生成实时数据,而在从服务器上分析这些数据,从而提高主服务器的性能。
10.4、MySQL支持的复制类型及其优缺点:
binlog日志文件有两种格式,一种是Statement-Based(基于语句的复制),另一种是Row-Based(基于行的复制)。默认格式为Statement-Based,如果想改变其格式在开启服务的时候使用 -binlog-format 选项,其具体命令如下:
mysqld_safe –user=msyql –binlog-format=格式 &
(1)基于语句的复制(Statement-Based):在主服务器上执行的SQL语句,在从服务器上执行同样的语句。效率比较高。 一旦发现没法精确复制时,会自动选着基于行的复制。
优点:
① 因为记录的SQL语句,所以占用更少的存储空间。binlog日志包含了描述数据库操作的事件,但这些事件包含的情况只是对数据库进行改变的操作,例如 insert、update、create、delete等操作。相反对于select、desc等类似的操作并不会去记录。
② binlog日志文件记录了所有的改变数据库的语句,所以此文件可以作为数据库的审核依据。
缺点:
① 不安全,不是所有的改变数据的语句都会被记录。对于非确定性的行为不会被记录。例如:对于 delete 或者 update 语句,如果使用了 limit 但是并没有 order by ,这就属于非确定性的语句,就不会被记录。
② 对于没有索引条件的update,insert……select 语句,必须锁定更多的数据,降低了数据库的性能。
(2)基于行的复制(Row-Based):把改变的内容复制过去,而不是把命令在从服务器上执行一遍,从mysql5.0开始支持;
优点:
① 所有的改变都会被复制,这是最安全的复制方式;
② 对于 update、insert……select等语句锁定更少的行;
缺点:
① 不能通过binlog日志文件查看什么语句执行了,也无从知道在从服务器上接收到什么语句,我们只能看到什么数据改变。
② 因为记录的是数据,所以说binlog日志文件占用的存储空间要比Statement-based大。
③ 对于数据量大的操作其花费的时间有更长。
(3)混合类型的复制:默认采用基于语句的复制,一旦发现基于语句的无法精确的复制时,就会采用基于行的复制。
|