MySQL

啊江呀
86 阅读12分钟

MySQL

优化

定位慢查询

  • 聚合查询

  • 多表查询

  • 表数据量过大查询

  • 深度分页查询

表现现象:页面加载过慢, 接口压测响应时间过长(超过1秒)

方案一: 开源工具, 通过工具可以实时的查看接口方法的响应速度

  • 调试工具: Arthas

  • 运维工具:Prometheus, Skywalking

方案二:  MySQL自带慢日志(调试阶段才会开启,生产环境不会开启的,会损耗一些mysql的性能)

慢查询日志记录了所有执行时间超过指定参数(long_query_time,单位.秒 默认 10s)的所有SQL语句的日志

开启慢查询日志,需要在mysql的配置文件/ etc/my.cnf中配置

#开启mysql慢日志查询开关
slow_query_log=1 
#设置慢日志的时间为2秒, SQL语句执行时间如果超过2秒,就会被视为慢查询,就将记录到慢日志查询中
long_query_time=2

如何定位慢查询?

我们项目当时做压测的时候有的接口非常慢,接口的响应时长时间超过了2秒以上,然后我们就会在mysql中开启慢日志查询,我们记得当时项目配置的是2秒,只要sql执行的时间超过了2秒就会记录到日志文件中,我们就可以在日志文件找到执行比较慢的sql了

一个SQL语句执行很慢,如何分析?

possible_key 当前sql可能会使用到的索引

key 当前sql命中的索引

key-len 索引占用的大小

Extra 额外的优化建议

Using Where; Using Index 查找使用到了索引,需要的数据都在索引列中能找到,不需要回表查询数据

Using Index condition 查找使用到了索引, 但是需要回表查询数据

type 这条sql的连接类型, 性能又好到差: Null--->system----->const---->eq_ref---->ref---->range----->index----->all

如果一条sql执行很慢的话,我们通常会使用mysql自带的explain来去查看这条sql的执行情况,比如:可以通过key和key_len检查是否命中了索引,如果本身已经添加了索引,也可以判断索引是否失效的情况, 第二个是可以通过type字段查看sql是否有进一步的优化空间,是否存在全盘扫描,第三个可以通过Extra建议来判断,是否出现了回表,如果是,可以尝试添加索引或修改返回字段来修复

什么是索引?

索引是帮助mysql高效获取数据的数据结构(有序),主要是用来提高数据检索的效率,降低数据库的IO成本,同时通过索引列对数据进行排序,降低数据排序的成本,也能降低CPU的消耗

索引的底层数据结构了解过吗?

​ mysql默认使用的是InnoDB存储引擎,采用的是B+树的数据结构来存储索引的, 选择B+树的主要原因是,阶数更多,路径更短, 第二个是磁盘读写代价B+树更低,非叶子节点只存储指针,叶子节点存储数据, 第三是B+树便于扫库和区间查询,叶子节点是一个双向链表,不需要每次都从根节点查询,他会一次把叶子节点范围内的都查询出来

B树和B+树的区别是什么?

在B树中,非叶子节点和叶子节点都会存放数据, 而B+树的所有的数据都会出现在叶子节点上, 在查询的时候,B+树查询效率更加稳定

在进行范围查找的时候,B+树效率更高, 因为B+树都在叶子节点存储数据, 并且叶子节点是一个双向链表

什么是聚簇索引和非聚簇索引?

聚簇索引主要是指数据和索引以ibd文件的形式存放到一块,B+数的叶子节点保存了整行数据,有且只有一个,主键作为聚簇索引

聚簇索引的选取规则:

如果存在主键,主键索引就是聚簇索引; 如果不存在主键,将使用第一个唯一索引作为聚簇索引; 如果还是没有主键,或没有合适的唯一索引,则InnoDB会自动生成一个rowid作为隐藏的聚簇索引

非聚簇索引指的是索引和数据分开存储,B+树的叶子节点保存对应的主键,可以有多个,一般我们自定义的索引就是非聚簇索引

什么是回表查询?

它首先会通过二级索引(非聚簇索引)找到对应的主键值,然后再通过主键值找到聚簇索引中所对应的整行数据,这个过程就是回表

什么是覆盖索引?

覆盖索引是指查询使用了索引,在返回的列中,必须在索引中全部能够找到,如果我们使用id查询,它会直接走聚集索引查询,一次索引扫描,直接返回数据,性能高

如果按照二级索引查询数据的时候, 返回的列中没有创建索引,有可能会触发回表查询,尽量避免使用select* ,尽量在返回的列中都包含添加索引的字段

MySQL超大分页怎么处理?

使用覆盖索引+子查询来解决:先分页查询数据的id字段,确定了id之后, 再用子查询来过滤,只查询id列表中的数据就可以了, 因为查询id的时候,走的是覆盖索引, 所以效率可以提升很多

select * from table1 t1,(select id from table1 order by id limit 80000000,10 )t2 where t1.id=t2.id

索引创建原则有哪些?

  • 针对数据量较大,且查询比较频繁的表建立索引. 单表超过10万数据(增加用户体验)

  • 针对于常作为查询条件(where), 排序(order by), 分组(group by) 操作的字段建立索引

  • 尽量选择区分度高的列作为索引, 尽量建立唯一索引, 区分度越高,使用索引的效率越高

  • 如果是字符串类型的字段, 字段的长度较长, 可以针对于字段的特点, 建立前缀索引

  • 尽量使用联合索引, 减少单列索引,查询时, 联合索引很多时候可以 覆盖索引,节省存储空间,避免回表,提高查询效率

  • 要控制索引的数据,索引并不是多多益善, 索引越多, 维护的成本就越大,会影响增删改的效率

  • 如果索引列不能存储NULL值, 在创建时使用NOT NULL约束它

这个情况有很多,不过都有一个大的前提,就是表中的数据超过10万以上, 我们才会创建索引,并且添加的索引的字段是查询比较频繁的字段, 一般像作为查询条件,排序或者分组,可以添加索引,提高效率

还有就是,我们会使用联合索引(使用表中的多个字段创建索引, 也叫组合索引和复合索引)来创建

但是并不是所有的字段都要添加索引,如果添加的索引过多,维护的成本也就会越高,也会导致增删改的速度变慢

联合索引查询的时候使用的是最左匹配原则

什么情况下索引会失效?

  • 违反最左前缀法则

  • 范围查询(>,<)右边的列会索引失效

  • 不要在索引列上进行运算操作, 索引会失效

  • 字符串不加单引号, 会造成索引失效(类型转换)

  • 以%开头的like模糊查询,索引失效

sql的优化经验

优化sql,我们会从建表的时候., 使用索引,sql语句的编写,主从复制, 读写分离,还有就是数据量比较大的时候,分库分表

创建表的时候,是怎么优化的? 我一般都是参考阿里出的那个开发手册(嵩山版), 在字段定义的时候根据字段的内容来选择合适的类型

平时对sql语句做了哪些优化? 就是select语句要指明字段名称, 不要直接使用select * ,还有就是要注意sql语句避免造成索引失效的写法; 如果是表关联,尽量使用inner join, 如果使用left join,一定要以小表驱动, 关联查询最好不要超过3个

事务的特性是什么?

ACID,分别指原子性, 一致性, 隔离性和持久性,

  • 原子性:事务是不可分割的最小执行单元, 要么全部完成,要么全部失败

  • 一致性:执行事务前后,数据要保持一致

  • 隔离性: 并发访问数据时, 一个用户的事务不被其他事务所干扰,各个事务之间相互独立

  • 持久性: 一个事务提交之后, 对数据的修改就是永久的, 即使数据库发生故障也不会丢失

InnoDB引擎通过什么技术来保证事务的这四个特性呢?

原子性是通过undo log(回滚日志)来保证的

一致性是通过持久性+原子性+隔离性来保证的

隔离性是通过 是通过MVCC(多版本并发控制)或锁机制来保证的

持久性是通过redo log(重做日志)来保证的

并发事务带来的那些问题? 怎么解决?

并发事务问题:脏读, 不可重复读, 幻读

  • 脏读: 一个事物读到另一个事务还没有提交的数据

  • 不可重复读:在一个事务内多次读同一条数据,如果出现前后两次读到的数据不同,就是不可重复读

  • 幻读: 是指在一个事务中,前后两次查询同一个范围的数据时,发现有新的行数据插入或者已有的行数据被删除,导致查询出现了之前不存在的行数据或者缺失了一些行数据,从而产生了"幻觉"

解决:对事务进行隔离

  • 未提交读: 都解决不了

  • 读已提交: 可解决脏读

  • 可重复读: 可解决脏读和不可重复读 默认的隔离级别

  • 串行化: 都可解决,但是它是串行化的,不能并发执行, 性能较差

undo log 和redo log的区别

redo log(重做日志): 记录的是数据页的物理变化,服务宕机可用来同步数据

undo log(回滚日志): 记录的是逻辑 日志,当事务回滚时,通过逆操作恢复原来的数据

redo log 保证了事务的持久性, undo log 保证了事务的原子性和一致性

redo log是为了系统崩溃之后恢复数据用的,让数据库照着日志,把没做好的事情重做一遍,有了redo log,就可以保证即使数据库发生崩溃重启后,之前提交的记录都不会丢失(crash-safe 崩溃恢复)

undo log 是为了回滚用的,在事务提交之前就开始写数据,万一事务到最后不打算提交了,要回滚,或者系统崩溃了,这些提前写入的数据就变成了脏数据,这时候就必须用 undo log恢复了

这种在写磁盘之前先写日志的方式就叫做:WAL技术

事务中的隔离性事如何保证的呢?

锁:排他锁(如一个事务获取了一个数据行的排他锁,其他事务就不能再获取该行的其他锁)

MVCC:多版本并发控制,维护一个数据的多个版本,使读写操作没有冲突,它的底层实现主要分为了三个部分:隐藏字段, undo log日志, readView读视图;

  • 隐藏字段: 是指在mysql中给每个表都设置隐藏字段,有一个是trx_id(事务id),记录每一次事务操作的id,是自增的, 另一个字段是roll_pointer(回滚指针),指向上一个版本的事务记录地址

  • undo log主要作用记录回滚日志,存储老版本数据,在内部会形成一个版本链,链首是最新的旧数据,尾部是最早的旧纪录

  • readView解决的是一个事务查询选择版本的问题,在内部定义了一些匹配规则和当前的一些事务id判断该访问哪个版本的数据, 不同的隔离级别快照读是不一样的,最终访问的结果也不一样。如果是rc隔离级别,每一次执行快照读时生成ReadView, 如果是rr(可重复读)隔离级别,仅在事务中第一次执行快照读时生成ReadView,后续复用

MySQL主从复制的原理

MySQL主从复制的核心就是二进制日志binlog, 二进制日志记录了所有的DDL和DML语句

流程: 首先master服务器会将变化的数据写到二进制binlog日志文件中, 然后slave服务器会开启一个IO Thread去主库的binlog日志中读取数据,读取完成之后,就把数据写入到从库的中继日志文件中, 然后从库在开启一个SQL Thread去读取中继日志中的内容,解析成具体操作并执行, 最终保证主从数据的一致性

分库分表的使用

业务介绍:根据自己项目,想一个数据量较大的业务员(请求数多或业务累积大);

达到了什么样的量级(单表1000万或超过20G)

具体拆分策略:

  • 垂直分库: 根据业务进行拆分, 不同的业务使用不同的数据库, 进而将一个数据库的压力分担到多个数据库, 高并发下提高磁盘IO和网络连接数

  • 垂直分表: 是对数据表列的拆分,把一张列比较多的表拆分为多张表,冷热数据分离,多表互不影响

  • 水平分库:将一个库的数据拆分到多个库中,解决海量数据存储和高并发问题

  • 水平分表: 对数据表行的拆分,把一张行比较多的表拆分为多张表, 解决单表存储数据量过大的问题

avatar
文章
阅读
粉丝