MySQL数据库面试题-mysql面试必考-mysql优化必备知识

为什么官方建议使用自增主键作为索引？

• 自增主键能够维持底层数据顺序写入

• 读取可以由B+树的二分查找

• 支持范围查找，范围数据自带顺序

索引的优缺点是什么？

优点：

1. 提高数据检索的效率，降低数据库的IO成本

2. 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗

缺点：

1. 实际上索引也是一张表，该表中保存了主键与索引字段，并指向实体类的记录，所以索引列也是要占用空间的.

2. 虽然索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE，因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

索引的设计原则

索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则，便于提升索引的使用效率，更高效的使用索引。

1. 对查询频次较高，且数据量比较大的表建立索引。

2. 索引字段的选择，最佳候选列应当从where子句的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合。

3. 使用唯一索引，区分度越高，使用索引的效率越高。

4. 索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代价自然也就水涨船高。对于插入、更新、删除等DMI.操作比较频繁的表来说，索引过多，会引入相当高的维护代价，降低DML操作的双率，增加相应操作的时间消耗、另外索引过多的话，MySQL也会犯选择困难病，虽然最终仍然会找到一个可用的索引，但无疑提高了选择的代价。

5. 使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的I/O效率，也可以提升总体的访问效率，假如构成索引的字段总长度比.较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升MYSQL访问索引的I/O效率。

6. 利用最左前缀，N个列组合而成的组合索引，那么相当于是创建了N个索引，如果查询时where子句中使用了组成该索引的前几个字段，那么这条查询SOL可以利用组合索引来提升查询效率。

7. 创建复合索引:
   CREATE INDEX idx_name_email_status ON tb_user (NAME,email,STATUS);

索引失效的几种情况

1. 违反最左前缀法则

2. 范围查询右边的列不会走索引

3. 在索引列上执行运算操作，则索引失效

4. 字符串不加 单引号，mysql的查询优化器，会自动的进行数据类型转换，造成索引失效

5. 用 or 分割的条件，如果or前面的条件有索引，而后面的条件没有索引，则整个索引都会失效

6. 以%开头的Like模糊查询，索引失效，如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。可以使用覆盖索引来解决模糊匹配的索引失效问题

7. 如果MySQL评估使用索引比全表更慢，则不使用索引。

8. is NULL ，is NOT NULL 有时索引失效，，如果null值占较多数，则is notNUll 走索引，反之，如果非null值占较多数，则is null走索引。

9. in走索引，not in 则索引失效

10. 索引的使用

    1. 尽量使用覆盖索引，尽量避免使用select * ;

    EXPLAIN SELECT name,email,phone FROM user_test WHERE name = '张三'

Tips:

Extra所代表的意思：

using index: 使用覆盖索引的时候就会出现

using where: 在查找使用索引的情况下，需要回表去查询所需的数据

using index condition:查找使用了索引，但是需要回表查询数据

using index ;using where: 查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

using filesort: 通过文件系统排序，不走索引，全表扫描

2. 尽量使用复合索引，而少使用单列索引

查看全局的索引使用情况

show global status like "Handler read%';

SQL优化

批量插入优化

1. 对于 InnoDB 类型的表，有以下几种方式可以提高导入的效率:

   1)主键顺序插入

   因为InnoDB 类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率，如果InnoDB表没有主键，那么系统会自动认创建一个内部列作为主键，所以如果可以给表创建一个主键，将可以利用这点，来提高导入数据的效率。

   2)关唯一性校验

   在导入数据前执行 SET UNIQUE_CHECKS=0,关闭唯一性校验,在导入结束后执行SET UNIQUE_CHECKS=1,恢复唯一性校验，可以提高导入的效率。

   3)手动提交事务

   如果应用使用自动提交的方式，建议在导入前执行 SET AUTOCOMMIT=0,关闭自动提交，导入结束后再执行 SET AUTOCOMMIT=1,打开自动提交，也可以提高导入的效率。

Order By优化

mysql排序方式分为两种：

• 第一种是通过对返回数据进行排序，也就是通常说的 filesort 排序，所有不是通过索引直接返排序结果的排序都叫 FileSort 排序。

• 第二种通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高，要求查询字段为索引字段

• 多字段排序。要么全部升序，要么全部降序，并且，要求排序的字段顺序要与索引字段的顺序一致，否则索引将失效，依旧为FileSort 排序

  FileSort 优化

通过创建合适的索引，能够减少Filesort 的出现，但是在某些情况下，条件限制不能让Filesort 消失，那就需要加快 Filesort的排序操作。对于Filesort ，MySQL 有两种排序算法:

1)两次扫描算法: MYSQL4.1 之前，使用该方式排序。首先根据条件取出排序字段和行指针信息，然后在排序区 sort burfer 中排序，如果sort buifter不够，则在占时表 temporary table 中存储排序结果。完成非序之后，再根据行指针回表读取记录，该操作可能会导致大量随机I/O操作。

2)一次扫描算法:一次性取出满足条件的所有字段，然后在排序区 sort buffer 中排序后直接输出结果集。排序时内存开销较大，但是排序效率比两次扫描算法要高。

MySOL通过比较系统变量 max_length_for_sort_data 的大小和Query语句取出的字段总大小，来判定使用那种排序算法，如果max_length_for_sort_data 更大，那么使用第二种优化之后的算法:否则使用第一种

可以适当提高 sort_buffer_size 和 max_length_for_sort_data 系统变量，来增大排序区的大小，提高排序的效率

SHOW VARIABLES LIKE 'sort_buffer_size'

SHOW VARIABLES LIKE 'max_length_for_sort_data'

group By 优化

由于GROUP BY 实际上也同样会进行排序操作，而且与ORDER BY 相比，GROUP BY 主要只是多了排序之后的分组操作，当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算，所以，在GROUP BY 的实现过程中，与 ORDER BY 一样也可以利用到索引

如果查询包含 group by 但是用户想要避免排序结果的消耗，则可以执行order by null 禁上排序。如下图

联合查询优化

使用多表联合查询替换子查询

OR条件优化

对于包含OR的查询子句，如果要利用索引，则OR之间的每个条件列都必须用到索引，而且不能使用到复合索引，如果没有索引，则应该考虑增加索引。建议使用union替换OR

如下图：const 执行效率要比 range 更高

我们来比较下重要指标，发现主要差别是 type 和 ref 这两项type 显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是:

system > const > eq_ref > ref > fulltext > ref_or-null > index_merge > unique_subquery > index_subquery > range > index > ALL

UNION 语句的 type 值为 ref，OR 语句的 type 值为 range，可以看到这是一个很明显的差距

UNION 语句的 ref值为 const，OR 语句的 type 值为 null，const 表示是常量值引用，非常快

这两项的差距就说明了 UNION 要优于 OR。

分页查询 - LIMIT优化

一般分页查询时，通过创建覆盖索能够比较好地提高性能。一个常见又非常头疼的问题就是 limit 2000000,10 ，此时需要MYSQL排序前2000010 记录，仅仅返回2000000 - 2000010 的记录，其他记录丢弃，查询排序的代价非常大。

优化思路一

在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容

优化思路二

该方案适用于主键自增的表，可以把Limit 查询转换成某个位置的查询，但是数据中间不能出现断层，否则查询数据不精确

select * from tb_item where id > 1000000 limit 10;

使用SQL提示

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

USE INDEX: 在查询语句中表名的后面，添加 use index 来提供希望MYSQL去参考的索引列表，就可以让MYSQL不考虑其他可用的索引

IGNORE INDEX

如果用户只是单纯的想让MySQL忽略一个或者多个索引，则可以使用 ignore index 作为 hint.

explain select * from tb_seller ignore index(idx_seller_name) where name = '小米科技'

FORCE INDEX

强制MySQL使用一个特定的索引，可在查询中使用 force index 作为hint

应用层优化

1. 使用数据库连接池

2 .减少对mysql的访问

2.1 避免对数据进行重复检索

在编写应用代码时，需要能够理清对数据库的访问逻辑。能够一次连接就获取到结果的，就不用两次连接，这样可以大大减少对数据库无重复请求

比如，需要获取书籍的id 和name字段 ，则查询如下

select id , name from tb_book;

之后，在业务逻辑中有需要获取到书籍状态信息 ，则查询如下 :

select id , status from tb_book;

这样，就需要向数据库提交两次请求，数据库就要做两次查询操作。其实完全可以用一条SOL语句得到想要的结果

select id, name , status from tb_book;

2.2 增加cache层

在应用中，我们可以在应用中增加 缓存 层来达到减轻数据库负担的目的。缓存层有很多种，也有很多实现方式，只要能大到降低教据库的负担又能满足应用需求就可以。

因此可以部分数据以数据库中抽取出来放到应用端以文本方式存储，或者使用框架(Mybatis,Hibernate 提供的一级缓存/二级缓存，或者使用redis数据库来缓存数据。

3. 负载均衡

负载均衡是应用中使用非常普遍的一种优化方法，它的机制就是利用某种均衡算法，将固定的负载量分布到不同的服务器上，以此来降低单台服务器的负载，达到优化的效果

3.1 利用MySQL复制分流查询

通过MySQL的主从复制，实现读写分离，使增删改操作走主节点，查询操作走从节点，从而可以降低单台服务器的读写压力。

3.2 采用分布式数据库架构

分布式数据库架构适合大数据量，负载高的情况，它有良好的拓展性和高可用性。通过在多台服务器之间分布数据，可以实现在多台服务器之间的负载均衡，提高访问效率

Mysql中查询缓存优化（8.0废弃）

概述：查询缓存功能在 MySQL 8.0 中已经被删除，如果使用8.0以上数据库，这项无需了解

开启Mysql的查询缓存，当执行完全相同的SQL语句的时候，服务器就会直接从缓存中读取结果，当数据被修改，之前的缓存会失效，修改比较频繁的表不适合做查询缓存。

查看当前的MySQL数据库是否支持查询缓存

SHOW VARIABLES LIKE 'have_query_cache'

查看当前MySQL是否开启了查询缓存

开启查询缓存方法：修改my.cnf 配置query_cache_type参数值为1或ON。默认为0或OFF

SHOW VARIABLES LIKE 'query_cache_type'

查看查询缓存的占用大小

SHOW VARIABLES LIKE 'query_cache_size'

InnoDB 内存优化

innodb用一块内存区做IO缓存池，该缓存池不仅用来缓存innodb的索引块，而且也用来缓存innodb的数据块.

innodb_buffer_pool_size

该变量决定了 innodb 存储引擎表数据和索引数据的最大缓存区大小，在保证操作系统及其他程序有足够内存可用的情况下，innodb_buffer_pool_size的值越大，缓存命中率越高，访问InnoDB表需要的磁盘I/O 就越少，性能也就越高。

innodb_buffer_pool_size=512M

innodb_log_buffer_size

决定了innodb重做日志缓存的大小，对于可能产生大量更新记录的大事务，增加innodb_log_buffer_size的大小，可以避免innodb在事务提交前就执行不必要的日志写入磁盘操作

innodb_log_buffer_size=10M

Mysql并发参数调整

从实现上来说，MYSQL Server 是多线程结构，包括后台线程和客户服务线程，多线程可以有效利用服务器资源，提高数据库的并发性能。在Mysq中，控制并发连接和线程的主要参数包括 max_connections、back_log、thread_cache_size、 table_open_cahce。

1. max_connections

采用max_connections控制允许连接到MYSQL数据库的最大数量。默认值是151，如果状态变量 connection_errors_max_connetions 不为零，并且一直增长，则说明不断有连接请求因数据库连接数已达到允许最大值而失败，这是可以考虑增大max_connections 的值.

Mysql 最大可支持的连接数，取决于很多因素，包括给定操作系统平台的线程库的质量、内存大小、每个连接的负荷、CPU的处理速度，期望的响应时间等。在Linux 平台下，性能好的服务器，支持500-1000 个连接不是难事，需要根据服务器性能进行评估设定。

2. back_log

back_log 参数控制MySQL监听TCP端口时设置的积压请求栈大小，如果MySql的连接数达到max_connetions时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源，将会报错。5.6.6版本之前默认值为 50，之后的版本默认为 50 + (max_connections/5)，但最大不超过900.

如果需要数据库在较短的时间内处理大量连接请求，可以考虑适当增大back_log 的值

3. table_open_cahce

该参数用来控制所有SQL语句执行线程可打开表缓存的数量，而在执行SQL语句时，每一个SQL执行线程至少要打开 1 个表缓存。该参数的值应该根据设置的最大连接数 max_connections 以及每个连接执行关联查询中涉及的表的最大数量来设定:

max_connections x N :

4. thread_cache_size

为了加快连接数据库的速度，MySQL 会缓存一定数量的客户服务线程以备重用，通过参数 thread_cache_size可控制 MySQL 缓存客户服务线程的数量。

5. innodb_lock_wait_timeout

该参数是用来设置InnoDB 事务等待行锁的时间，默认值是50ms ，可以根据需要进行动态设置，对于需要快速反馈的业务系统来说，可以将行锁的等待时间调小，以避免事务长时间挂起，对于后台运行的批量处理程序来说，可以将行锁的等待时间调大，以避免发生大的回滚操作。

MYSQL 锁问题

MYSQL锁的分类

从对数据操作的粒度分 :

1)表锁 : 操作时，会锁定整个表,偏向MYISAM存储引擎

优点：开销小，枷锁快，不会出现死锁，缺点：锁粒度较大，发生锁冲突概率较高，并发度低

2) 行锁: 操作时，会锁定当前操作行，偏向InnoDB存储引擎

优点：锁粒度较小，发生锁冲突概率较低，并发度比较高。缺点：开销大，加锁慢，会出现死锁。

 

从对数据操作的类型分 :

1)读锁(共享锁):针对同一份数据，多个读操作可以同时进行而不会相影响.

2)写锁(排它所 ): 当前操作没有完成之前，它会阻断其他写锁和读锁

某个表有数千万数据，查询比较慢，如何优化? 说一下思路

1.前端优化 减少查询

1.1 合并请求:多个请求需要的数据尽量一条sql拿出来

1.2 会话保存: 和用户会话相关的数据尽量一次取出重复使用

1.3 避免无效刷新,即避免短时间内重复请求

2.多级缓存不要触及到数据库

1.应用层热点数据高速查询缓存(低一致性缓存) --例如：排行榜，热点新闻。

2.高频查询大数据量镜像缓存(双写高一致性缓存)

3.入口层缓存(几乎不变的系统常量) --例如：菜单，标签，分类名称等。。

3.使用合适的字段类型及长度，比如varchar换成char一定要高效使用索引。

1.使用explain 深入观察索引使用情况

2.检查select 字段最好满足索引覆盖

3.复合索注意观察key_len索引使用情况

4.有分组，排序，注意file sort，合理配置相应的buffer大小

5.检查查询是否可以分段查询，避免一次拿出过多无效数据

6.多表关联查询是否可以设置冗余字段，是否可以简化多表查询或分批查询

7.分而治之:把服务拆分成更小力度的微服务

8.冷热数据分库存储

9.读写分离，主被集群

本文链接：https://www.518wz.top/post/21.html 转载需授权！