面试题:在日常工作中怎么做MySQL优化的?

沙海 2021年7月1日03:37:07Java评论39字数 5798阅读19分19秒阅读模式
摘要

面试题:在日常工作中怎么做MySQL优化的? 小黑格子屋

面试题:在日常工作中怎么做MySQL优化的?

小黑格子屋 文章源自JAVA秀-https://www.javaxiu.com/36341.html

以下文章来源于月伴飞鱼,作者日常加油站文章源自JAVA秀-https://www.javaxiu.com/36341.html

文章源自JAVA秀-https://www.javaxiu.com/36341.html

面试题:在日常工作中怎么做MySQL优化的?文章源自JAVA秀-https://www.javaxiu.com/36341.html

月伴飞鱼文章源自JAVA秀-https://www.javaxiu.com/36341.html

号主目前就职于美团,掘金优秀作者,日常分享计算机基础,分布式,数据库,框架源码,大数据等精品原创文章文章源自JAVA秀-https://www.javaxiu.com/36341.html

文章源自JAVA秀-https://www.javaxiu.com/36341.html

面试题:在日常工作中怎么做MySQL优化的?文章源自JAVA秀-https://www.javaxiu.com/36341.html

来源:月伴飞鱼文章源自JAVA秀-https://www.javaxiu.com/36341.html

作者:日常加油站文章源自JAVA秀-https://www.javaxiu.com/36341.html

前言

面试题来自:社招一年半面经分享(含阿里美团头条京东滴滴)文章源自JAVA秀-https://www.javaxiu.com/36341.html

MySQL常见的优化手段分为下面几个方面:文章源自JAVA秀-https://www.javaxiu.com/36341.html

SQL优化、设计优化,硬件优化等,其中每个大的方向中又包含多个小的优化点文章源自JAVA秀-https://www.javaxiu.com/36341.html

面试题:在日常工作中怎么做MySQL优化的?文章源自JAVA秀-https://www.javaxiu.com/36341.html

下面我们具体来看看文章源自JAVA秀-https://www.javaxiu.com/36341.html

文章源自JAVA秀-https://www.javaxiu.com/36341.html

SQL优化

此优化方案指的是通过优化 SQL 语句以及索引来提高 MySQL 数据库的运行效率,具体内容如下:文章源自JAVA秀-https://www.javaxiu.com/36341.html

分页优化

例如:文章源自JAVA秀-https://www.javaxiu.com/36341.html

select * from table where type = 2 and level = 9 order by id asc limit 190289,10;

优化方案:文章源自JAVA秀-https://www.javaxiu.com/36341.html

  • 延迟关联文章源自JAVA秀-https://www.javaxiu.com/36341.html

    先通过where条件提取出主键,在将该表与原数据表关联,通过主键id提取数据行,而不是通过原来的二级索引提取数据行文章源自JAVA秀-https://www.javaxiu.com/36341.html

    例如:文章源自JAVA秀-https://www.javaxiu.com/36341.html

select a.* from table a, (select id from table where type = 2 and level = 9 order by id asc limit 190289,10 ) b where a.id = b.id
  • 书签方式文章源自JAVA秀-https://www.javaxiu.com/36341.html

    书签方式说白了就是找到limit第一个参数对应的主键值,再根据这个主键值再去过滤并limit文章源自JAVA秀-https://www.javaxiu.com/36341.html

    例如:文章源自JAVA秀-https://www.javaxiu.com/36341.html

select * from table where id > (select * from table where type = 2 and level = 9 order by id asc limit 190289, 1) limit 10;

索引优化

正确使用索引文章源自JAVA秀-https://www.javaxiu.com/36341.html

假如我们没有添加索引,那么在查询时就会触发全表扫描,因此查询的数据就会很多,并且查询效率会很低,为了提高查询的性能,我们就需要给最常使用的查询字段上,添加相应的索引,这样才能提高查询的性能文章源自JAVA秀-https://www.javaxiu.com/36341.html

建立覆盖索引文章源自JAVA秀-https://www.javaxiu.com/36341.html

InnoDB使用辅助索引查询数据时会回表,但是如果索引的叶节点中已经包含要查询的字段,那它没有必要再回表查询了,这就叫覆盖索引文章源自JAVA秀-https://www.javaxiu.com/36341.html

例如对于如下查询:文章源自JAVA秀-https://www.javaxiu.com/36341.html

select name from test where city='上海'

我们将被查询的字段建立到联合索引中,这样查询结果就可以直接从索引中获取文章源自JAVA秀-https://www.javaxiu.com/36341.html

alter table test add index idx_city_name (city, name);

在 MySQL 5.0 之前的版本尽量避免使用or查询文章源自JAVA秀-https://www.javaxiu.com/36341.html

在 MySQL 5.0 之前的版本要尽量避免使用 or 查询,可以使用 union 或者子查询来替代,因为早期的 MySQL 版本使用 or 查询可能会导致索引失效,在 MySQL 5.0 之后的版本中引入了索引合并文章源自JAVA秀-https://www.javaxiu.com/36341.html

索引合并简单来说就是把多条件查询,比如or或and查询对多个索引分别进行条件扫描,然后将它们各自的结果进行合并,因此就不会导致索引失效的问题了文章源自JAVA秀-https://www.javaxiu.com/36341.html

如果从Explain执行计划的type列的值是index_merge可以看出MySQL使用索引合并的方式来执行对表的查询文章源自JAVA秀-https://www.javaxiu.com/36341.html

关于Explain的使用可以参考我之前的文章:最完整的Explain总结,SQL优化不再困难文章源自JAVA秀-https://www.javaxiu.com/36341.html

避免在 where 查询条件中使用 != 或者 <> 操作符文章源自JAVA秀-https://www.javaxiu.com/36341.html

SQL中,不等于操作符会导致查询引擎放弃索引索引,引起全表扫描,即使比较的字段上有索引文章源自JAVA秀-https://www.javaxiu.com/36341.html

解决方法:通过把不等于操作符改成or,可以使用索引,避免全表扫描文章源自JAVA秀-https://www.javaxiu.com/36341.html

例如,把column<>’aaa’,改成column>’aaa’ or column<’aaa’,就可以使用索引了文章源自JAVA秀-https://www.javaxiu.com/36341.html

适当使用前缀索引文章源自JAVA秀-https://www.javaxiu.com/36341.html

MySQL 是支持前缀索引的,也就是说我们可以定义字符串的一部分来作为索引文章源自JAVA秀-https://www.javaxiu.com/36341.html

我们知道索引越长占用的磁盘空间就越大,那么在相同数据页中能放下的索引值也就越少,这就意味着搜索索引需要的查询时间也就越长,进而查询的效率就会降低,所以我们可以适当的选择使用前缀索引,以减少空间的占用和提高查询效率文章源自JAVA秀-https://www.javaxiu.com/36341.html

比如,邮箱的后缀都是固定的“@xxx.com”,那么类似这种后面几位为固定值的字段就非常适合定义为前缀索引文章源自JAVA秀-https://www.javaxiu.com/36341.html

alter table test add index index2(email(6));

使用前缀索引,定义好长度,就可以做到既节省空间,又不用额外增加太多的查询成本文章源自JAVA秀-https://www.javaxiu.com/36341.html

需要注意的是,前缀索引也存在缺点,MySQL无法利用前缀索引做order by和group by 操作,也无法作为覆盖索引文章源自JAVA秀-https://www.javaxiu.com/36341.html

查询具体的字段而非全部字段文章源自JAVA秀-https://www.javaxiu.com/36341.html

要尽量避免使用select *,而是查询需要的字段,这样可以提升速度,以及减少网络传输的带宽压力文章源自JAVA秀-https://www.javaxiu.com/36341.html

优化子查询文章源自JAVA秀-https://www.javaxiu.com/36341.html

尽量使用 Join 语句来替代子查询,因为子查询是嵌套查询,而嵌套查询会新创建一张临时表,而临时表的创建与销毁会占用一定的系统资源以及花费一定的时间,同时对于返回结果集比较大的子查询,其对查询性能的影响更大文章源自JAVA秀-https://www.javaxiu.com/36341.html

关于Join语句使用,可以参考我之前的文章:写出好的Join语句,前提你得懂这些文章源自JAVA秀-https://www.javaxiu.com/36341.html

小表驱动大表文章源自JAVA秀-https://www.javaxiu.com/36341.html

我们要尽量使用小表驱动大表的方式进行查询,也就是如果 B 表的数据小于 A 表的数据,那执行的顺序就是先查 B 表再查 A 表,具体查询语句如下:文章源自JAVA秀-https://www.javaxiu.com/36341.html

select name from A where id in (select id from B);

不要在列上进行运算操作文章源自JAVA秀-https://www.javaxiu.com/36341.html

不要在列字段上进行算术运算或其他表达式运算,否则可能会导致查询引擎无法正确使用索引,从而影响了查询的效率文章源自JAVA秀-https://www.javaxiu.com/36341.html

select * from test where id + 1 = 50;select * from test where month(updateTime) = 7;

一个很容易踩的坑:隐式类型转换:文章源自JAVA秀-https://www.javaxiu.com/36341.html

select * from test where skuId=123456

skuId这个字段上有索引,但是explain的结果却显示这条语句会全表扫描文章源自JAVA秀-https://www.javaxiu.com/36341.html

原因在于skuId的字符类型是varchar(32),比较值却是整型,故需要做类型转换文章源自JAVA秀-https://www.javaxiu.com/36341.html

适当增加冗余字段文章源自JAVA秀-https://www.javaxiu.com/36341.html

增加冗余字段可以减少大量的连表查询,因为多张表的连表查询性能很低,所有可以适当的增加冗余字段,以减少多张表的关联查询,这是以空间换时间的优化策略文章源自JAVA秀-https://www.javaxiu.com/36341.html

正确使用联合索引文章源自JAVA秀-https://www.javaxiu.com/36341.html

使用了 B+ 树的 MySQL 数据库引擎,比如 InnoDB 引擎,在每次查询复合字段时是从左往右匹配数据的,因此在创建联合索引的时候需要注意索引创建的顺序文章源自JAVA秀-https://www.javaxiu.com/36341.html

例如,我们创建了一个联合索引是idx(name,age,sex),那么当我们使用,姓名+年龄+性别、姓名+年龄、姓名等这种最左前缀查询条件时,就会触发联合索引进行查询;然而如果非最左匹配的查询条件,例如,性别+姓名这种查询条件就不会触发联合索引文章源自JAVA秀-https://www.javaxiu.com/36341.html

Join优化

MySQL的join语句连接表使用的是nested-loop join算法,这个过程类似于嵌套循环,简单来说,就是遍历驱动表(外层表),每读出一行数据,取出连接字段到被驱动表(内层表)里查找满足条件的行,组成结果行文章源自JAVA秀-https://www.javaxiu.com/36341.html

要提升join语句的性能,就要尽可能减少嵌套循环的循环次数文章源自JAVA秀-https://www.javaxiu.com/36341.html

一个显著优化方式是对被驱动表的join字段建立索引,利用索引能快速匹配到对应的行,避免与内层表每一行记录做比较,极大地减少总循环次数。另一个优化点,就是连接时用小结果集驱动大结果集,在索引优化的基础上能进一步减少嵌套循环的次数文章源自JAVA秀-https://www.javaxiu.com/36341.html

如果难以判断哪个是大表,哪个是小表,可以用inner join连接,MySQL会自动选择小表去驱动大表文章源自JAVA秀-https://www.javaxiu.com/36341.html

避免使用JOIN关联太多的表文章源自JAVA秀-https://www.javaxiu.com/36341.html

对于 MySQL 来说,是存在关联缓存的,缓存的大小可以由join_buffer_size参数进行设置文章源自JAVA秀-https://www.javaxiu.com/36341.html

在 MySQL 中,对于同一个 SQL 多关联(join)一个表,就会多分配一个关联缓存,如果在一个 SQL 中关联的表越多,所占用的内存也就越大文章源自JAVA秀-https://www.javaxiu.com/36341.html

如果程序中大量的使用了多表关联的操作,同时join_buffer_size设置的也不合理的情况下,就容易造成服务器内存溢出的情况,就会影响到服务器数据库性能的稳定性文章源自JAVA秀-https://www.javaxiu.com/36341.html

排序优化

利用索引扫描做排序文章源自JAVA秀-https://www.javaxiu.com/36341.html

MySQL有两种方式生成有序结果:其一是对结果集进行排序的操作,其二是按照索引顺序扫描得出的结果自然是有序的文章源自JAVA秀-https://www.javaxiu.com/36341.html

但是如果索引不能覆盖查询所需列,就不得不每扫描一条记录回表查询一次,这个读操作是随机IO,通常会比顺序全表扫描还慢文章源自JAVA秀-https://www.javaxiu.com/36341.html

因此,在设计索引时,尽可能使用同一个索引既满足排序又用于查找行文章源自JAVA秀-https://www.javaxiu.com/36341.html

例如:文章源自JAVA秀-https://www.javaxiu.com/36341.html

--建立索引(date,staff_id,customer_id)select staff_id, customer_id from test where date = '2010-01-01' order by staff_id,customer_id;

只有当索引的列顺序和ORDER BY子句的顺序完全一致,并且所有列的排序方向都一样时,才能够使用索引来对结果做排序文章源自JAVA秀-https://www.javaxiu.com/36341.html

UNION优化

MySQL处理union的策略是先创建临时表,然后将各个查询结果填充到临时表中最后再来做查询,很多优化策略在union查询中都会失效,因为它无法利用索引文章源自JAVA秀-https://www.javaxiu.com/36341.html

最好手工将where、limit等子句下推到union的各个子查询中,以便优化器可以充分利用这些条件进行优化文章源自JAVA秀-https://www.javaxiu.com/36341.html

此外,除非确实需要服务器去重,一定要使用union all,如果不加all关键字,MySQL会给临时表加上distinct选项,这会导致对整个临时表做唯一性检查,代价很高文章源自JAVA秀-https://www.javaxiu.com/36341.html

慢查询日志

出现慢查询通常的排查手段是先使用慢查询日志功能,查询出比较慢的 SQL 语句,然后再通过 Explain 来查询 SQL 语句的执行计划,最后分析并定位出问题的根源,再进行处理文章源自JAVA秀-https://www.javaxiu.com/36341.html

慢查询日志指的是在 MySQL 中可以通过配置来开启慢查询日志的记录功能,超过long_query_time值的 SQL 将会被记录在日志中文章源自JAVA秀-https://www.javaxiu.com/36341.html

我们可以通过设置“slow_query_log=1”来开启慢查询文章源自JAVA秀-https://www.javaxiu.com/36341.html

需要注意的是,在开启慢日志功能之后,会对 MySQL 的性能造成一定的影响,因此在生产环境中要慎用此功能文章源自JAVA秀-https://www.javaxiu.com/36341.html

设计优化

尽量避免使用NULL文章源自JAVA秀-https://www.javaxiu.com/36341.html

NULL在MySQL中不好处理,存储需要额外空间,运算也需要特殊的运算符,含有NULL的列很难进行查询优化文章源自JAVA秀-https://www.javaxiu.com/36341.html

应当指定列为not null,用0、空串或其他特殊的值代替空值,比如定义为int not null default 0文章源自JAVA秀-https://www.javaxiu.com/36341.html

最小数据长度文章源自JAVA秀-https://www.javaxiu.com/36341.html

越小的数据类型长度通常在磁盘、内存和CPU缓存中都需要更少的空间,处理起来更快文章源自JAVA秀-https://www.javaxiu.com/36341.html

使用最简单数据类型文章源自JAVA秀-https://www.javaxiu.com/36341.html

简单的数据类型操作代价更低,比如:能使用 int 类型就不要使用 varchar 类型,因为 int 类型比 varchar 类型的查询效率更高文章源自JAVA秀-https://www.javaxiu.com/36341.html

尽量少定义 text 类型文章源自JAVA秀-https://www.javaxiu.com/36341.html

text 类型的查询效率很低,如果必须要使用 text 定义字段,可以把此字段分离成子表,需要查询此字段时使用联合查询,这样可以提高主表的查询效率文章源自JAVA秀-https://www.javaxiu.com/36341.html

适当分表、分库策略文章源自JAVA秀-https://www.javaxiu.com/36341.html

分表是指当一张表中的字段更多时,可以尝试将一张大表拆分为多张子表,把使用比较高频的主信息放入主表中,其他的放入子表,这样我们大部分查询只需要查询字段更少的主表就可以完成了,从而有效的提高了查询的效率文章源自JAVA秀-https://www.javaxiu.com/36341.html

分库是指将一个数据库分为多个数据库。比如我们把一个数据库拆分为了多个数据库,一个主数据库用于写入和修改数据,其他的用于同步主数据并提供给客户端查询,这样就把一个库的读和写的压力,分摊给了多个库,从而提高了数据库整体的运行效率文章源自JAVA秀-https://www.javaxiu.com/36341.html

常见类型选择

整数类型宽度设置文章源自JAVA秀-https://www.javaxiu.com/36341.html

MySQL可以为整数类型指定宽度,例如int(11),实际上并没有意义,它并不会限制值的范围,对于存储和计算来说,int(1)和int(20)是相同的文章源自JAVA秀-https://www.javaxiu.com/36341.html

VARCHAR和CHAR类型文章源自JAVA秀-https://www.javaxiu.com/36341.html

char类型是定长的,而varchar存储可变字符串,比定长更省空间,但是varchar需要额外1或2个字节记录字符串长度,更新时也容易产生碎片文章源自JAVA秀-https://www.javaxiu.com/36341.html

需要结合使用场景来选择:如果字符串列最大长度比平均长度大很多,或者列的更新很少,选择varchar较合适;如果要存很短的字符串,或者字符串值长度都相同,比如MD5值,或者列数据经常变更,选择使用char类型文章源自JAVA秀-https://www.javaxiu.com/36341.html

DATETIME和TIMESTAMP类型文章源自JAVA秀-https://www.javaxiu.com/36341.html

datetime的范围更大,能表示从1001到9999年,timestamp只能表示从1970年到2038年。datetime与时区无关,timestamp显示值依赖于时区。在大多数场景下,这两种类型都能良好地工作,但是建议使用timestamp,因为datetime占用8个字节,timestamp只占用了4个字节,timestamp空间效率更高文章源自JAVA秀-https://www.javaxiu.com/36341.html

BLOB和TEXT类型文章源自JAVA秀-https://www.javaxiu.com/36341.html

blob和text都是为存储很大数据而设计的字符串数据类型,分别采用二进制和字符方式存储文章源自JAVA秀-https://www.javaxiu.com/36341.html

在实际使用中,要慎用这两种类型,它们的查询效率很低,如果字段必须要使用这两种类型,可以把此字段分离成子表,需要查询此字段时使用联合查询,这样可以提高主表的查询效率文章源自JAVA秀-https://www.javaxiu.com/36341.html

范式化

当数据较好范式化时,修改的数据更少,而且范式化的表通常要小,可以有更多的数据缓存在内存中,所以执行操作会更快文章源自JAVA秀-https://www.javaxiu.com/36341.html

缺点则是查询时需要更多的关联文章源自JAVA秀-https://www.javaxiu.com/36341.html

第一范式:字段不可分割,数据库默认支持文章源自JAVA秀-https://www.javaxiu.com/36341.html

第二范式:消除对主键的部分依赖,可以在表中加上一个与业务逻辑无关的字段作为主键,比如用自增id文章源自JAVA秀-https://www.javaxiu.com/36341.html

第三范式:消除对主键的传递依赖,可以将表拆分,减少数据冗余文章源自JAVA秀-https://www.javaxiu.com/36341.html

硬件优化

MySQL 对硬件的要求主要体现在三个方面:磁盘、网络和内存文章源自JAVA秀-https://www.javaxiu.com/36341.html

磁盘文章源自JAVA秀-https://www.javaxiu.com/36341.html

磁盘应该尽量使用有高性能读写能力的磁盘,比如固态硬盘,这样就可以减少 I/O 运行的时间,从而提高了 MySQL 整体的运行效率文章源自JAVA秀-https://www.javaxiu.com/36341.html

磁盘也可以尽量使用多个小磁盘而不是一个大磁盘,因为磁盘的转速是固定的,有多个小磁盘就相当于拥有多个并行运行的磁盘一样文章源自JAVA秀-https://www.javaxiu.com/36341.html

网络文章源自JAVA秀-https://www.javaxiu.com/36341.html

保证网络带宽的通畅(低延迟)以及够大的网络带宽是 MySQL 正常运行的基本条件,如果条件允许的话也可以设置多个网卡,以提高网络高峰期 MySQL 服务器的运行效率文章源自JAVA秀-https://www.javaxiu.com/36341.html

内存文章源自JAVA秀-https://www.javaxiu.com/36341.html

MySQL 服务器的内存越大,那么存储和缓存的信息也就越多,而内存的性能是非常高的,从而提高了整个 MySQL 的运行效率文章源自JAVA秀-https://www.javaxiu.com/36341.html

最后

觉得有收获,希望帮忙点赞,转发下哈,谢谢,谢谢文章源自JAVA秀-https://www.javaxiu.com/36341.html

参考资料:文章源自JAVA秀-https://www.javaxiu.com/36341.html

  • 《高性能MySQL》文章源自JAVA秀-https://www.javaxiu.com/36341.html

  • 《MySQL技术内幕:InnodDB存储引擎》文章源自JAVA秀-https://www.javaxiu.com/36341.html

文章源自JAVA秀-https://www.javaxiu.com/36341.html

-End-

文章源自JAVA秀-https://www.javaxiu.com/36341.html

面试题:在日常工作中怎么做MySQL优化的?文章源自JAVA秀-https://www.javaxiu.com/36341.html

文章源自JAVA秀-https://www.javaxiu.com/36341.html

面试题:在日常工作中怎么做MySQL优化的?文章源自JAVA秀-https://www.javaxiu.com/36341.html

老大让我优化数据库,我上来就分库分表,他过来就是一jio。。。文章源自JAVA秀-https://www.javaxiu.com/36341.html

文章源自JAVA秀-https://www.javaxiu.com/36341.html

文章源自JAVA秀-https://www.javaxiu.com/36341.html

面试题:在日常工作中怎么做MySQL优化的?文章源自JAVA秀-https://www.javaxiu.com/36341.html

Redis:我是如何与客户端进行通信的文章源自JAVA秀-https://www.javaxiu.com/36341.html

文章源自JAVA秀-https://www.javaxiu.com/36341.html

面试题:在日常工作中怎么做MySQL优化的?文章源自JAVA秀-https://www.javaxiu.com/36341.html

想不到吧,这些人竟然也是程序员!文章源自JAVA秀-https://www.javaxiu.com/36341.html

面试题:在日常工作中怎么做MySQL优化的? 可乐记得加冰,爱我就要置顶 面试题:在日常工作中怎么做MySQL优化的?文章源自JAVA秀-https://www.javaxiu.com/36341.html

面试题:在日常工作中怎么做MySQL优化的?文章源自JAVA秀-https://www.javaxiu.com/36341.html

面试题:在日常工作中怎么做MySQL优化的?文章源自JAVA秀-https://www.javaxiu.com/36341.html

面试题:在日常工作中怎么做MySQL优化的?素质三连biubiubiu~面试题:在日常工作中怎么做MySQL优化的?文章源自JAVA秀-https://www.javaxiu.com/36341.html

继续阅读
速蛙云 - 极致体验,强烈推荐!!!购买套餐就免费送各大视频网站会员!快速稳定、独家福利社、流媒体稳定解锁!速度快,全球上网、视频、游戏加速、独立IP均支持!基础套餐性价比很高!这里不多说,我一直正在使用,推荐购买:https://www.javaxiu.com/59919.html
weinxin
资源分享QQ群
本站是JAVA秀团队的技术分享社区, 会经常分享资源和教程; 分享的时代, 请别再沉默!
沙海
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定