简述mysql性能优化策略

 

2：简述主从同步的延迟的原因

一个服务器开放Ｎ个链接给客户端来连接的，这样有会有大并发的更新操作, 但是从服 务器的里面读取binlog 的线程仅有一个，当某个SQL在从服务器上执行的时间稍长 或者由于某个SQL要进行锁表就会导致，主服务器的SQL大量积压，未被同步到从服务器里。这就导致了主从不一致， 也就是主从延迟。

3：简述死锁是如何产生的以及如何避免死锁

死锁的发生与否，并不在于事务中有多少条SQL语句，死锁的关键在于：两个(或以上)的Session【加锁的顺序】不一致。

避免死锁： 1、注意程序的逻辑

2、保持事务的轻量

3、提高运行的速度

4、尽量快提交事务，减少持有锁的时间

4：简述性能分析的思路

 

快照读，读取的是记录的可见版本 (有可能是历史版本)，不用加锁。

当前读，读取的是记录的最新版本，并且当前读返回的记录，都会加上锁，保证其他事务不会再并发修 改这条记录。

6：简述原子性，持久性和一致性的实现机制

原子性，持久性和一致性主要是通过redo log、undo log和Force Log at Commit机制机制来完成 的。redo log用于在崩溃时恢复数据，undo log用于对事务的影响进行撤销，也可以用于多版本控 制。而Force Log at Commit机制保证事务提交后redo log日志都已经持久化。

7：系统表空间和用户表空间存储哪些数据？

1.系统表空间是一个共享的表空间，因为它是被多个表共享的。 InnoDB系统表空间包含InnoDB数据字典(元数据以及相关对象)、double write buffer、change buffer、undo logs的存储区域。 系统表空间也默认包含任何用户在系统表空间创建的表数据和索引数据。

2.用户表空间只存储该表的数据、索引和插入缓冲BITMAP等信息，其余信息还是存放在默认的系统 表空间中。

8：简述MySQL锁的分类

1.按照锁的粒度来说，MySQL主要包含三种类型（级别）的锁定机制：

 

3）不要在索引上做计算

4）范围条件右边的列失效 ，不能继续使用索引中范围条件（bettween、<、>、in等）右边的列

5）尽量使用覆盖索引 ，尽量使用覆盖索引（只查询索引的列），也就是索引列和查询列一致，减少

select *

6）索引字段上不要使用不等 索引字段上不要使用不等 ，索引字段上使用（！= 或者 < >）判断时，会

导致索引失效而转向全表扫描

7）索引字段上不要判断null ，索引字段上使用 is null / is not null 判断时，会导致索引失效而转向全表 扫描

8）索引字段使用like不以通配符开头 ，索引字段使用like以通配符开头（‘%字符串’）时，会导致索引失 效而转向全表扫描，like以通配符结束相当于范围查找，索引不会失效。与范围条件（bettween、<、 >、in 等）不同的是：不会导致右边的索引失效。

9）索引字段字符串要加单引号 ，索引字段是字符串，但查询时不加单引号，会导致索引失效而转向全 表扫描 .

10）索引字段不要使用or ，索引字段使用 or 时，会导致索引失效而转向全表扫描

10、EXPLAIN 查看执行计划，各列的含义

各列的含义如下: id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符. select_type: SELECT 查询的类型. table: 查询的是哪个表 partitions: 匹配的分区 type: join 类型 possible_keys: 此次查询中可能选用的索引 key: 此次查询中确切使用到的索引. ref: 哪个字段或常数与 key 一起被使用 rows: 显示此查询一共扫描了多少行. 这个是一个估计值. filtered: 表示此查询条件所过滤的数据的百分比 extra: 额外的信息

11、简述索引使用场景有哪些

1、频繁作为查询条件的字段应该创建索引 where 2、多表关联查询中，关联字段应该创建索引on两边都要创建索引 select * from user left join order on user.id = order.userid 3、查询中排序的字段，应该创建索引B + tree 有顺序"

12：MyIsam和InnoDB的区别并说明使用场景

InnoDB：支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比 较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。如果需要频繁的 更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚 （rollback）。

MyISAM：插入数据快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择 MyISAM能实现处理高效率。如果应用的完整性、并发性要求比 较低，也可以使用。

13：Mysql的各组件及各组件的功能

连接器（Connectors） 系统管理和控制工具（Management Serveices & Utilities） 连接池（Connection Pool） SQL接口（SQL Interface） 解析器（Parser） 查询优化器（Optimizer） 查询缓存（Cache和Buffer）

 

1. 1：简述mysql性能优化策略

2. 分析步骤：

   1. 在发现系统存在性能问题的时候（不只是数据库存在问题的时候），只在该时间段内开启慢查询日 志，收集问题SQL。

   2. 针对收集到的问题SQL语句，使用explain命令进行查询计划的分析。 select_type、 type extra

   3. 使用profile进行磁盘硬件或者CPU相关的性能分析

3. 服务器硬件优化

4. 提升硬件设备，例如选择尽量高频率的内存（频率不能高于主板的支持）、提升网络带宽、使用SSD高速磁盘、提升CPU性能等。

5. 参数优化

6. 内核相关参数(/etc/sysctl.conf)

7. MySQL配置参数相关设置

   1. 足够大的 innodb_buffer_pool_size ，将数据读取到内存中。降低磁盘写入次数；

   2. innodb_buffer_pool_size 表示缓冲池字节大小。 推荐值为物理内存的50%~80%。 innodb_flush_log_at_trx_commit=1 如果该机器不是作为写操作的机器（读写分离的情况），该参数设置为0；

   3. sync_binlog每提交1次事务同步写到磁盘中，可以设置为n。 如果该机器不是作为写操作的机器（读写分离的情况），该参数设置为0；

   4. 脏页占innodb_buffer_pool_size的比例时，触发刷脏页到磁盘。 推荐值为25%~50%。

   5. innodb_io_capacity默认200，后台进程最大IO性能指标。如果SSD，调整为5000~20000；

   6. long_qurey_time慢查询日志的阈值设置，单位秒。慢查询日志的阈值是多少，都是根据需求方的非功能性需求来定的（RT）；

   7. 调高该参数则应降低interactive_timeout、wait_timeout的值。默认是200，一般建议生产调整为2000左右。 最大连接数的提高，其实也就是提高了数据库的并发能力。但是并发能力的提高不能只是考 虑连接数的调大，因为连接数大了，使用的内存也就多了；

   8. innodb_log_file_size 过大，实例恢复时间长；过小，造成频繁脏页落盘。 redo log文件大小。

   9. 全量日志建议关闭。 默认关闭。 不只是该日志，还有错误日志、慢查询日志等都要进行关闭。 general_log

8. SQL设计层面优化

   1. 设计中间表，一般针对于统计分析功能，或者实时性不高的需求（OLTP、OLAP）

   2. 为减少关联查询，创建合理的冗余字段（考虑数据库的三范式和查询性能的取舍，创建冗余字段 还需要注意数据一致性问题）

   3. 对于字段太多的大表，考虑拆表（比如一个表有100多个字段）

   4. 对于表中经常不被使用的字段或者存储数据比较多的字段，考虑拆表（比如商品表中会存储商品介绍，此时可以将商品介绍字段单独拆解到另一个表中，使用商品ID关联）

   5. 每张表建议都要有一个主键（主键索引），而且主键类型最好是int类型，建议自增主键（不考虑分布式系统的情况下）。

9. SQL语句优化

   1. 索引优化 为搜索字段（where中的条件）、排序字段、select查询列，创建合适的索引，不过要考虑数据 的业务场景：查询多还是增删多？

   2. 尽量建立组合索引并注意组合索引的创建顺序，按照顺序组织查询条件、尽量将筛选粒度大的查 询条件放到最左边。

   3. 尽量使用覆盖索引，SELECT语句中尽量不要使用*。

   4. order by、group by语句要尽量使用到索引 ,order by的索引生效，order by排序应该遵循最佳左前缀查询，如果是使用多个索引字段进行排 序，那么排序的规则必须相同（同是升序或者降序），否则索引同样会失效。

   5. 索引长度尽量短，短索引可以节省索引空间，使查找的速度得到提升，同时内存中也可以装载更多的索引键值。太长的列，可以选择建立前缀索引

   6. 索引更新不能频繁，更新非常频繁的数据不适宜建索引，因为维护索引的成本。

10. LIMIT优化 如果预计SELECT语句的查询结果是一条，最好使用 LIMIT 1，可以停止全表扫描。

    处理分页会使用到 LIMIT ，当翻页到非常靠后的页面的时候，偏移量会非常大，这时LIMIT的效率

    会非常差。 LIMIT OFFSET , SIZE；

    LIMIT的优化问题，其实是 OFFSET 的问题，它会导致MySql扫描大量不需要的行然后再抛弃掉。

    解决方案1：使用order by 和索引覆盖

    解决方案2：使用子查询

    解决方案3：单表分页时，使用自增主键排序之后，先使用where条件 id > offset值，limit后

    面只写rows

11. 其他查询优化 小表驱动大表，建议使用left join时，以小表关联大表，因为使用join的话，第一张表是必须 全扫描的，以少关联多就可以减少这个扫描次数。

12. 避免全表扫描，mysql在使用不等于(!=或者<>)的时候无法使用导致全表扫描。在查询的时候， 如果对索引使用不等于的操作将会导致索引失效，进行全表扫描 避免mysql放弃索引查询，如果mysql估计使用全表扫描要比使用索引快，则不使用索引。（最典 型的场景就是数据量少的时候）

13. 尽量不使用count(*)、尽量使用count（主键）

14. JOIN两张表的关联字段最好都建立索引，而且最好字段类型是一样的。

15. WHERE条件中尽量不要使用1=1、not in语句（建议使用not exists）、

16. 不用 MYSQL 内置的函数，因为内置函数不会建立查询缓存。

17. 合理利用慢查询日志、explain执行计划查询、show profile查看SQL执行时的资源使用情况。

18. 在互联网项目中，一般都不建议使用外键。

19. 首先需要使用【慢查询日志】功能，去获取所有查询时间比较长的SQL语句

20. 其次【查看执行计划】查看有问题的SQL的执行计划 explain

21. 后可以使用【show profile[s]】 查看有问题的SQL的性能使用情况

    5：简述什么是当前读与快照读？

22. 全局锁：锁的是整个database。由MySQL的SQL layer层实现的

23. 表级锁：锁的是某个table。由MySQL的SQL layer层实现的

24. 行级锁：锁的是某行数据，也可能锁定行之间的间隙。由某些存储引擎实现，比如InnoDB。 2.按照锁的功能来说分为：共享读锁和排他写锁。 3.按照锁的实现方式分为：悲观锁和乐观锁（使用某一版本列或者唯一列进行逻辑控制）