优化Mysql

目录

Mysql优化就四种：定位慢查询/sql执行计划/索引/Sql优化经验... 2

1Mysql如何定位慢查询？... 2

2Sql语句执行很慢，如何分析呢？... 3

2.1那这个SQL语句执行很慢,如何分析呢?. 3

3．了解过索引吗?(什么是索引) 3

3.1什么是聚簇索引，什么是非聚簇索引？什么是回表查询？... 3

3.1.1聚集索引选取规则... 4

3.1.2回表查询... 4

4什么是覆盖索引... 5

4.1超大分页处理？... 5

5索引创建原则有哪些？... 6

6什么情况下索引会失效？... 6

7谈谈你对sql优化的经验... 7

7.1表的设计优化、避免索引失效、sql语句优化等... 7

8事务的特性... 9

9并发事务的问题，隔离级别... 9

9.1解决方案，对事务进行隔离... 9

10undo log和redo log的区别？... 11

11解释一下mvcc？... 12

12.mysql主从原理？... 13

13项目中用过分库分表吗?. 14

13.1垂直分库和分表... 14

13.2水平分库... 14

Mysql优化就四种：定位慢查询/sql执行计划/索引/Sql优化经验

1Mysql如何定位慢查询？

基本就是页面加载慢，接口的响应测试时间长。

怎么确定是mysql的问题呢，如果是sql问题，怎么找出慢的原因呢。

方案一：开源工具：监听调试。调试工具arthas。运维工具：普罗米修斯，skywalking

方案一主要就是哪些接口慢，sql时间长给监控排序罗列出来。

方案二：慢日志查询。

慢查询日志开启slow-query-log=1

慢日志时间为2long-query-time=2

Sql超过两秒则记录/一般调试的时候才开启，生产的时候不开启不然会损坏mysql性能

       1.介绍一下当时产生问题的场景(我们当时的一个接口测试的时候非常的慢，压测的结果大概5秒钟)

2.我们系统中当时采用了运维工具(Skywalking)，可以监测出哪个接口，最终因为是sql的问题

3.在mysql中开启了慢日志查询，我们设置的值就是2秒，一旦sql执行超过2秒就会记录到日志中(调试阶段)

2Sql语句执行很慢，如何分析呢？

       Explain和desc就可以分析sql语句了

字段名词解释：

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

key 当前sql实际命中的索引

key len 索引占用的大小

extra额外的优化建议

type 这条sql的连接的类型，性能由好到差为NULL、system、const、eq ref、ref、range、index、all

2.1那这个SQL语句执行很慢,如何分析呢?

可以采用MySQL自带的分析工具 EXPLAIN

通过key和key len检查晟否命中了索引(索引本身存在是否有失效的情况)

通过type字段查看sql是否有进一步的优化空间，是否存在全索引扫描或全盘扫描

通过extra建议判断，是否出现了回表的情况，如果出现了，可以尝试添加索引或修改返回字段来修复

3．了解过索引吗?(什么是索引)

索引(index)是帮助MySQI高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构(B+树)，这些数据结构以某种方式引用(指向)数据，这样就可以在这些数据结构上实现高级查找算法这种数据结构就是索引。

3.1什么是聚簇索引，什么是非聚簇索引？什么是回表查询？

个人理解，聚簇全是主键，然后拿行数据。只有一行

非聚簇就是通过某个数据字段找这个主键，可能会有多个。

聚簇索引也就是聚集索引。

二级索引也就是非聚集索引。

什么是聚集索引，什么是二级索引(非聚集索引)什么是回表?

聚集索引(Clustered Index)将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据必须有,而且只有一个，个人理解只对应一行数据

二级索引(Secondary Index)将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键可以存在多个，叶子节点存储对应的主键值。

3.1.1聚集索引选取规则

如果存在主键，主键索引就是聚集索引。

如果不存在主键，将使用第一个唯一(UNIQUE)索引作为聚集索引。

如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

3.1.2回表查询

大多数的数据都用的非聚簇索引。

回表查询就是通过二级索引找到对应的主键值，到聚集索引中查找整行数据，这个过程就是回表

4什么是覆盖索引

个人理解，不用回表查询的查询语句，比如局促和非聚簇都可以的。

4.1超大分页处理？

覆盖索引+子查询

比如分页查询需要九百万limit仅仅需要十条怎么才能快？

正常直接limit获取*你这样拿所有行数据很慢。

但你如果先拿到id，再通过id来查询就非常快，省去了九百万的时间代价。

5索引创建原则有哪些？

先陈述自己在工作中用到的，主键索引，唯一索引，根据业务创建的复合索引

单表超过十万条数据，可以建立索引。

3尽量不要好几条数据都是北京市，这种字段不适合索引

4尽量字段短一些，太长的可以截取一部分，建立前缀索引。

5尽量使用联合索引或者叫做复合索引。

总结重点：数据量大；查询频繁；排序，分组，查询条件的字段；尽量联合索引，避免回表；控制数量。

6什么情况下索引会失效？

命中索引就是查询的时候使用到了索引。

1.复合索引。不能跳过某一列去查询，可能会失效。违反最左前缀法则，索引失效。

2.范围查询，>1后面的索引是失效的。这叫范围查询最右边的列，不能使用索引。

3.不要在索引列上运算，不然会失效

4.类型转换，比如明明是字符串，您不加单引号，那也索引失效。

5.%开头的模糊查询也会失效，放在末尾%不会影响索引

7谈谈你对sql优化的经验

7.1表的设计优化、避免索引失效、sql语句优化等

       参考阿里开发手册嵩山版本

1. 类型选择，tinyint或者char是固定类型但性能好。
2. 索引创建原则，查询的时候避免索引失效

 

8事务的特性

ACID分别就是原子性，隔离性，一致性，隔离性，持久性。

9并发事务的问题，隔离级别

1. 脏读就是，读的是错误的修改后还没提交的数据。
2. 不能读两次，又是事务前，又是事务后。

9.1解决方案，对事务进行隔离

打叉就是解决的问题。

 

10undo log和redo log的区别？

缓冲池和数据页。Sql操作肯定先去内存的缓冲池找数据操作，如果找不到就再去磁盘。然而操作缓冲池以后，要将数据同步到磁盘中，没有同步过去的称为脏页。一旦服务器宕机，内存中的数据会丢失。于是就引进了redo log

关于mysql的事务提交，增删改数据，为了提高性能。引入了两块区域，一个是内存结构，一个是磁盘结构。

磁盘的结构主要是存储的数据页，比如说某一个表的ibd文件里边包含了很多数据页。每个页中存储的就是sql一行行的数据。

增删改首先会操作内存。内存的概念就是缓冲池。数据库先操作内存，如果没有数据才考虑磁盘，操作完内存会将数据同步到磁盘中。

主要是用来实现事务的持久性的。

Redo log 由两部分组成，redo log buffer（内存）和redo log file （磁盘）

内存中有buffer pool和redo log buffer，当增删改buffer pool的时候redo log buffer就记录数据的变化。一旦发生变化，redo log buffer记录数据页的变化，就会把这些数据记录到磁盘文件中，也就是redo log file日志文件中。

所以一旦从内存同步数据到磁盘失败的话，就会从redo log file日志文件中恢复数据

这个过程遵循WAL机制，write-ahead-logging就是先写日志redo log，所以当脏页正常写到磁盘中的时候，日志就没用了。这个日志文件在磁盘中是两份，循环写。

Undo log记录相反日志，可以实现事务的一致性和原子性。

11解释一下mvcc？

个人理解大概实现了隔离性。

12.mysql主从原理？

主库会把ddl（create-drop）和dml(增删改)写进binlog二进制文件，

从库会有一个io thread线程来读取这个二进制日志文件，

然后写进从库的一个中继文件relay log中，

再由中继文件通过sql thread线程同步到从库的数据库中。

 

13项目中用过分库分表吗?

单表数据量达到一千万，或者20G以后。就要分库分表了

13.1垂直分库和分表

个人理解：垂直分库，就是根据业务不同的表放在不同的库中。

用户微服务，商品微服务，订单微服务。

比如基本信息，详细信息，只有感兴趣，点进去才会展示。

对比垂直分表，个人理解，不同的字段拆成不同的表中。

热数据冷数据。

特点:

1，冷热数据分离2，减少IO过渡争抢，两表互不影响

13.2水平分库

每个库存储的数据是不一样的，但是类型是一样的。所有库的数据加起来才是这个业务的所有数据。

但是，你想要拿数据，该怎么选择库呢，就对id进行一个分库，可以路由规则

 

水平分库用的更多一些，海量数据一般都水平分库。

高并发提高磁盘io性能一般垂直分库

垂直分表，冷热数据分离/