文章目录
MySQL如何定位慢查询
回答这个问题首先一定要结合业务场景(我们当时有一个接口测试时候响应非常慢,压测大概5秒钟)
可以使用开源工具:阿尔萨斯(Arthas)、普罗米修斯(Prometheus)、Skywalking
- SkyWalking:在这个监控中会有相应的指标的数据,可以实时查看接口的情况,并且对接口的响应情况进行了排序,访问越慢的接口会排在前面,当知道哪个接口访问的比较慢,在Skywalking中有一个追踪的功能,可以详细查看接口的执行情况,这个接口在什么阶段耗时多久可以非常清晰的呈现出来,其中也包含了SQL的执行和耗时,如果真的是某个SQL执行比较耗时就可以迅速定位哪个SQL耗时比较久,这个就是使用工具的方式定位慢查询。
- 第二种是使用MySQL自带的慢日志,慢查询日志记录了所有执行时间超过指定参数(long_quary_time,单位s,默认10)的所有SQL语句的日志,如果要开启慢日志,就要在MySQL的配置文件(/etc/my.cnf)中配置以下信息
- 一般在项目中设置执行时间2秒,如果有SQL语句的执行时间超过2秒就会记录在localhost-show.log日志文件中。
找到了这个执行慢的SQL语句,如何分析
可以使用explain或者desc来获取MySQL如何执行select语句的信息。其中可以查询到这个SQL语句执行过程中的一些字段。 主要是通过key和key_len来进行判断是否可能会命中索引。
- possible_key:这条SQL语句在执行过程中可能使用到的索引。
- key:当前SQL实际命中的索引。
- key_len:当前使用到的索引实际占用的大小。
- Extra:额外的优化建议,如果出现Using index condition这种情况,说明索引的使用是有优化的空间。
- type:SQL连接的类型,通过type可以判断这条SQL编写的好坏,性能由好到坏分为NULL、system、const、eq_ref、ref、range、index、all。如果这条SQL类型index或all那么这条SQL就需要进行优化了。
- system:查询系统中的表。
- const:根据主键查询。
- eq_ref:逐渐索引查询或唯一索引查询。
- ref:索引查询。
- range:范围查询。
- index:索引树扫描。
- all:全盘扫描。
MySQL的引擎
MySQL的默认存储引擎是InnoDB,并且所有的存储引擎中只有InnoDB是事务性存储引擎。
InnoDB
InnoDB是MySQL的默认存储引擎,支持ACID事务,支持行级锁,外键约束等特性。
MyISAM是MySQL的另一种常见的存储引擎,具有较低的存储空间和内存消耗,适用于大量读操作的场景。然而,MyISAM不支持事务、行级锁和外键约束,因此在并发写入和数据完整性方面有一定的限制。主要用户查询多,增删改较少的场景。
Memory引擎将数据存储在内存中,适用于对性能要求较高的读操作,但是在服务器重启或崩溃时数据会丢失。它不支持事务、行级锁和外键约束。
优点: InnoDB是事务型存储引擎,它支持ACID特性,支持行级锁(一个事务对某行数据操作时,只会锁定某一行数据,不锁定其他行,效率高),提供了更好的MVCC机制,支持外键约束,支持缓存,支持全文索引。
缺点: 相较于MyISAM,InnoDB的存储和管理需要更多的内存和磁盘空间,同时也对CPU的要求较高。
MyISAM
优点: MyISAM简单易于管理,支持表锁(进行dml操作时会锁定整张表),主要用户查询多,增删改较少的场景。支持全文索引,存储表的总行数。
缺点: MyISAM不支持事务和行级锁,不支持主外键
总结
- InnoDB 支持行级别的锁粒度,MyISAM 不支持,只支持表级别的锁粒度。
- MyISAM 不提供事务支持。InnoDB 提供事务支持,实现了 SQL 标准定义了四个隔离级别。
- MyISAM 不支持外键,而 InnoDB 支持。
- MyISAM 不支持 MVCC,而 InnoDB 支持。
- 虽然 MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构,但是两者的实现方式不太一样。
- MyISAM 不支持数据库异常崩溃后的安全恢复,而 InnoDB 支持。
- InnoDB 的性能比 MyISAM 更强大。
索引
什么是索引?
索引是一种用于快速查询和检索数据的数据结构,其本质可以看成是一种排序好的数据结构。它出现的作用就是为了加快查询效率。
索引的作用就相当于书的目录。打个比方: 我们在查字典的时候,如果没有目录,那我们就只能一页一页的去找我们需要查的那个字,速度很慢。如果有目录了,我们只需要先去目录里查找字的位置,然后直接翻到那一页就行了。
索引的优缺点
优点:
- 使用索引可以大大加快 数据的检索速度(大大减少检索的数据量), 这也是创建索引的最主要的原因。
- 提高数据库检索效率,降低数据库的IO成本(不需要全表扫描)。
- 通过索引列对数据进行排序,降低数据排序成本,降低了CPU的消耗。
- 通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。
缺点:
- 创建索引和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候,如果数据有索引,那么索引也需要动态的修改,会降低 SQL 执行效率。
- 索引需要使用物理文件存储,也会耗费一定空间。
大多数情况下,索引查询都是比全表扫描要快的。但是如果数据库的数据量不大,那么使用索引也不一定能够带来很大提升。
索引底层数据结构
常见的索引结构有: B 树, B+树 和 Hash。在 MySQL 中,无论是 Innodb 还是 MyIsam,都使用了 B+树作为索引结构。B+树索引按照索引列的值进行排序,并将数据分层存储在索引树的节点中,这样可以通过比较索引值,快速定位到符合条件的数据行。
- 为什么不使用红黑树呢,红黑树是平衡的二叉树,也是能够稳定查找数据。但是红黑树是二叉树,每个节点最多只能两个孩子节点,所以当某个数据库表中的数据非常大的时候,红黑树将非常的高,查找效率也会不大。B+树是多叉树,阶数更多,路径更短。
- 数据库的索引和数据都是存储在硬盘中的,我们可以把读取一个节点当作一次批判IO操作。B+树存储千万级的数据只需要3-4层高度就可以满足,着意味着千万级的表查询目标数据最多需要3-4次磁盘IO,所以相较于B树和红黑树磊说,磁盘读写代价更低,查询效率高。
- B+和B,B+树只在叶子节点存储数据,而B树非叶子节点也要存储数据,所以B+树的单个节点的数据量更小,在相同的磁盘IO次数下,就能查询更多的节点。还有B+树叶子节点采用的是双链表结构,适合MySQL中常见的基于范围的顺序查找,B树在这一点是无法做到的,因为B树的叶子节点之间没有关联,范围查找需要频繁IO,查询效率比较低。
- B+和Hash,Hash在做等职查询的时候效率非常快,搜索复杂度仅为O(1),但是Hash表不适合做范围查询,它更适合做等值的查询,这也是B+树索引要比Hash表索引有更广泛适用场景的原因。
索引的分类
底层存储方式:
聚簇索引:所查即所得,找到了索引就是找到了数据,创建原则就是有主键就默认主键。数据与索引是放在一块的,B+树的叶子节点存放了整行数据,并且有且只有一个。
非聚簇索引(二级索引):索引和数据是分离的,B+树的叶子节点存储的是对应的主键,需要根据主键,再次回表查询,在MyISAM引擎中,除了主键列,其他都是非聚簇索引。
回表查询:通过二级索引找到对应的主键值,然后通过主键去表中查找整行数据,这个过程就是回表。
判断聚簇索引和非聚簇索引的方法
按照应用维度分:
普通索引:查询加速
唯一索引:查询加速,列值唯一
主键索引:查询加速,列值唯一(不能为NULL),表中只有一个,一般用于二级索引的回表查询,如果表中没有主键索引,系统会自动创建一个虚拟的唯一索引列用于回表查询。覆盖索引: 查询使用了索引,需要查找的列在索引中全部能找见,就是使用了覆盖索引。如果查找的列中没有创建索引,就会触发回表查询,所以在平时的开发中尽量避免使用select*。
联合索引(聚集索引):多列值组成一个索引,专门用于组合搜索,其效率大于索引合并
全文索引:文本字段上的普通索引只能加快对出现在字段内容最前面的字符串(也就是字段内容开头的字符)进行检索操作。如果字段里存放的是由几个、甚至是多个单词构成 的较大段文字,普通索引就没什么作用了。添加全文索引之后MySQL将把在文本中出现的所有单词创建为一份清单,查询操作将根据这份清单去检索有关的数据记录。
MySQL添加索引的方式
新建表中添加索引
create table t_dept(
no int not null primary key,
name varchar(20) null,
sex varchar(2) null,
info varchar(20) null,
index index_no(no) // 普通索引
unique index index_no(no) // 唯一索引
fulltext index index_no(no) // 全文索引
index index_no_name(no,name) // 多列索引
)
已建表中添加索引
create index/unique/fulltext index_name // 普通/唯一/全文
on t_dept(name);
create index index_name_no // 多列索引
on t_dept(name,no)
修改表的方式添加索引
alter table t_dept
add index/unique/fulltext index_name(name); // 普通/唯一/全文
alter table t_dept
add index index_name_no(name,no); // 多列索引
MySQL超大分页怎么处理
超大分页一般是在数据量比较大的时候,我们使用limit分页查询,并且需要对数据进行排序,这个时候效率就很低,我们可以采用覆盖索引和子查询来解决。先分页查询数据的id字段,确定了id之后,再用子查询来过滤,只查询这个id列表中的数据就可以了,因为查询id的时候走的是覆盖索引,所以效率会提升很多。
什么是最左匹配原则
创建索引的原则
哪些情况创建索引
- 针对数据量大,查询比较频繁的表建立索引。(单表数据超过10万数据)
- 频繁作为查询条件的字段应该创建索引(条件where、排序order by、分组group by后面的语句)
- 尽量选择区分度高的列作为索引,尽量建立唯一索引,区分度越高,使用索引的效率也就越高。
- 尽量使用联合索引,减少单列索引,查询时,联合索引很多时候可以覆盖索引,节省存储空间,避免回表,提高查询效率。
- 要控制索引创建的数量,索引并不是越多越好,索引越多,维护索引所需的代价就约大,而且还会影响增删改的效率。
哪些情况不建议创建索引
- 表记录太少
- 经常增删改的表,提高了查询速度,同时降低了更新表的速度
- where条件里用不到的字段
- 数据重复且分布均匀的表字段
索引失效的场景
- 违反最左前缀法则,最左前缀法则就是如果索引了多个列,查询的话就要从索引的最左前列开始,并且不跳过索引中间的列,如果直接使用右边的列索引,或者跳过中间的列索引进行查询(跳之前的索引没有失效),索引就会失效。
- 范围查询右边的列索引会失效。
- 在索引列上进行运算操作。
- 字符串不加单引号(类型转换)。
- 以%开头的Like模糊查询,索引失效,单结尾不会失效。
事务
什么是事务?
事务就是逻辑上的一组操作,在同一个事务中,如果有多条sql语句执行,要么都执行,要么都不执行。
关系型数据库都有ACID的特点
A(原子性)、C(一致性)、I(隔离性)、D(持久性)
原子性:事务是一个不可分割的工作单位,事务的中间操作要么全部完成,要么全部不做,不可能停滞在中间环节。
隔离性:并发访问数据库时,一个用户的事务不被其他事务所干扰,各并发事务之间数据库是独立的
持久性:事务一旦提交,数据就持久保存在硬盘中
一致性:执行事务前后,数据保持一致,比如转账,不管成功与否,转账人与收账人总金额保持不变
只有保证了事务的持久性、原子性、隔离性之后,一致性才能得到保障。
隔离性中的不同隔离级别
查看隔离级别
SELECT @@global.transaction_isolation,@@transaction_isolation;
设置隔离级别
-- 设置左边的
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- 设置右边的
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
读未提交:一个事务可以读取到另一个事务未提交的数据,这会带来脏读(垃圾数据,因为A事务可能会回滚),幻读,不可重复读问题,将隔离级别改为读已提交,可以解决脏读问题。
读已提交:一个事务只能读取另一个事务已经提交的数据,其避免了脏读,但仍然存在不可重复读和幻读问题
不可重复度: A事务中对数据库进行了两次查询,在两次查询中,B事务修改了数据库中的数据,导致A事务中两次查询的数据不同,这就是不可重复度
可重复读:同一个事务中多次读取相同的数据返回的结果是一样的,可以避免脏读和不可重复读,但可能会导致幻读。
幻读: 可重复读隔离级别—>一个事务查询一个范围内的数据时,另一个并发事务向这个范围内添加了一个新的数据,当之间的事务再次查询这个范围的数据时,就会发现之前没有的记录,这就是幻读。
串行化:最高的隔离级别,事务串行执行,事务不存在并发执行,可以避免脏读、不可重复读和幻读,但是效率低下.
事务实现的原理
在MySQL中提供了两种概念,一个缓冲池(buffer pool),一个数据页(page)。
- 缓冲池:主内存中的一个区域,里面可以缓存磁盘中经常操作的真实数据,在执行增删改查操作时,先操作缓冲池中的数据(若缓冲池没有数据,则先从磁盘加载并缓存),以一定频率刷新到磁盘,从而减少磁盘IO,加快处理速度。
- 数据页:是InnoDB存储引擎磁盘管理的最小单位,每个页的大小默认为16KB。页中存储的是行数据。
- redolog:内存中缓冲池中存储的页还没有同步磁盘中这个页就是一个脏页,只有将这个脏页中的数据同步到磁盘中数据才能真正算是持久化,这是后如果服务器宕机,就会造成数据的丢失。
- MySQL中引入了一个日志文件就是redolog,重做日志,记录的是事务提交时数据页的物理修改,是用来实现事务的持久性的。redolog主要分为两部分,重做日志缓冲(redolog buffer)和重做日志文件(redolog file),前者在内存中,后者在磁盘中并有两份文件,当事务提交之后会把所有修改的信息都存到该日志文件中,用于刷新脏页到磁盘过程中发生错误,进行数据恢复时使用。
- 如果直接使用buffer pool也是可以的,但是会有严重的性能问题。当一个事务中有大量的update操作,在保存数据到磁盘过程中是随机的磁盘IO。而redolog在数据同步过程中是顺序的磁盘IO,日志文件都是追加。这种机制就叫做WAL(Write-Ahead Logging)。
- undolog:用来保证事务的原子性和一致性,主要提供回滚和MVCC。
- 回滚:undolog里面记录的是逻辑日志,记录的都是执行过程操作相反的操作,当事务回滚的时候能撤销所有已经执行的sql语句。
InnoDB存储引擎提供了两种事务日志,redolog(重做日志)和undolog(回滚日志),redolog用于保证事务持久性,undolog则是事务原子性和一致性实现的。
**原子性的实现:**当事务回滚时能撤销所有已执行的sql语句。这个操作依赖的时undolog日志,undolog属于逻辑日志,里面记录的都是执行过所有操作的相反操作,用于回滚后将已执行的sql语句进行撤销。
**持久性的实现:**redolog叫做重做日志,用于记录事务中所有的修改操作,包括修改前和修改后的数据,这样即使系统崩溃,这些修改也不会丢失,系统恢复后,MySQL可以读取日志,重新执行这些操作。
隔离级别的实现原理(MVCC)
多版本并发控制 Multi-Version Concurrent Control,是MySQL提高性能的一种方式,就是配合undolog使事务可以并发执行。
读未提交: 没有特殊的并发控制机制,读操作不会获取任何锁,在读取数据之前不会进行任何检查。
读已提交: 读已提交又称当前读,每次读的时候都会给版本链拍照,所以读到的数据都是最新的(已提交)。这是通过在读取数据时加上共享锁,然后在读取完成后立即释放锁来实现的。
可重复读: 快照读使用行级锁或快照隔离,当事务开始之后,第一次读取会给版本拍照,下次读取直接从版本快照中直接读取,所以一个事务中读取到的数据是一直的。
每次执行修改操作时,MySQL不会直接修改原始数据,而是创建一个新的版本。这个新版本包含了修改后的数据,以及生成这个版本的事务ID。当一个事务需要读取数据时,MySQL会根据事务的隔离级别和事务ID,从版本链中选择一个合适的版本。这样,即使在并发执行多个事务的情况下,每个事务也都能看到一个一致的数据视图。
MySQL中的锁机制
MySQL中的锁按粒度分,主要分为全局锁、表锁、行级锁
然后全局锁主要分为共享锁和排他锁,在全局锁中,是锁住所有的数据库表,放置的是备份操作中进行的操作影响最终备份的结果,共享锁可以和共享锁不排斥,共享锁和排他锁排斥,即加共享锁时,排他锁只能等候,排他锁和任何锁都互斥。
表锁主要分为表锁,元数据锁,意向锁,表锁也主要分为共享锁和排他锁,在元数据锁中,对一张表进行操作的时候,数据库会自动添加元数据锁,然后操作完成自动关闭。意向锁就是当修改一行数据时,有一个表锁,这时候要加表锁的话需要去进行全表查询,所以数据库就是用意向锁,当进行增删操作时,添加行锁,同时为这个表添加一个意向锁,当有其他表锁过来是,先去判断意向锁是不是排斥的,如果排斥就等待,这里意向锁和意向锁之间不会排斥。
行级锁分为行锁、间隙锁、临键锁,行锁分为共享锁和排他锁,间隙锁的话就是用来锁住数据与数据之间的间隙,防止事务在操作过程中突然添加数据,出现幻读,临键锁是锁住中间的间隙和两边的数据,方式出现幻读。
MySQL中的锁主要分为两种:共享锁和排他锁,共享锁允许多个事务同时获取相同资源的读取访问权限,而排他锁则只允许一个事务获取资源的写入访问权限。
SQL优化
表的设计优化
- 设置合适的数值,根据实际情况而定。
- 设置合适的字符串类型(char和varchar)。
- 尽量使用数值代替字符串类型,比如主键优先使用int类型,性别、状态,使用1,0替代。
SQL语句优化
- 避免使用select*,务必指明字段名称。
- SQL语句要避免索引失效的写法。
- 尽量使用union all代替union,union会多一次过滤,效率低。
- 避免在where子句中对字段进行表达式操作。
- 能用inner join就用inner join,如果必须使用其他的必须以小表驱动,内连接会自己对两个表进行优化,优先将小表放在外面。而left join和right join不会自动优化。
主从复制,读写分离
如果数据库场景读的操作比较多的时候,为了避免写的操作所造成的性能影响,可以采用读写分离的架构。不让数据的写入影响了读操作。
主从同步原理
MySQL主从复制的核心就是二进制日志,二进制日志(binlog)记录了所有的DDL(数据定义语言)和DML(数据操纵语言)语句,但不包括数据查询语句。
- DDL:创建表,修改表,删除表的操作语句。
- DML:增删改。
- 主从同步的过程是,当主库对数据进行操作时,将这些变化的数据写入到binlog日志中,从库有一个IOthread线程专门去主库中的binlog日志中读取数据,读取完成之后,就将数据写入到从库的中继日志(relaylog)中,然后由从库中的SQLthread线程去读取relaylog,将里面的命令重新执行。
分库分表
数据量特别大,比如一张表的数据超过500万,就可以考虑。
但是现在分库分表用的比较少了,因为现在硬件的性能非常高,一般一张MySQL表里面的数据都可以存到1亿左右,而且还不卡,因为都是通过内存淘汰链表和Free链表来更新数据。目前最多就是用用分库,分表几乎用不到了,因为10年前那会一张表一般就存储2000万数据。