MySQL面试常见之海量数据优化

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介: MySQL面试常见之海量数据优化

1.SQL优化


  • 避免使用OR
  • 不要使用like '%xx' %在左边时索引失效
  • 使用复合索引时没有遵循最左匹配原则
  • 不要让数据类型出现隐式转化
  • 不要在索引字段上使用not,<>,!=,一样会导致索引失效
  • 分解关联查询
  • 小表驱动大表 即小的数据集驱动大的数据集


2.事务级别调整


首先了解下事务的隔离级别,数据库共定义了四种隔离级别:

  • Serializable:可避免脏读、不可重复读、虚读情况的发生。(串行化)
  • Repeatable read:可避免脏读、不可重复读情况的发生。(可重复读)
  • Read committed:可避免脏读情况发生(读已提交)。
  • Read uncommitted:最低级别,以上情况均无法保证。(读未提交)

可以通过 set transaction isolation level 设置事务隔离级别来提高性能


3.MySql配置参数


#基础配置
datadir=/data/datafile
socket=/var/lib/mysql/mysql.sock
log-error=/data/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
character_set_server=utf8#允许任意IP访问
bind-address = 0.0.0.0#是否支持符号链接,即数据库或表可以存储在my.cnf中指定datadir之外的分区或目录,为0不开启#symbolic-links=0#支持大小写
lower_case_table_names=1#二进制配置
server-id = 1
log-bin = /data/log/mysql-bin.log
log-bin-index =/data/log/binlog.index
log_bin_trust_function_creators=1
expire_logs_days=7
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION#InnoDB存储数据字典、内部数据结构的缓冲池,16MB已经足够大了。
innodb_additional_mem_pool_size = 16M#InnoDB用于缓存数据、索引、锁、插入缓冲、数据字典等#如果是专用的DB服务器,且以InnoDB引擎为主的场景,通常可设置物理内存的60%#如果是非专用DB服务器,可以先尝试设置成内存的1/4
innodb_buffer_pool_size = 4G#InnoDB的log buffer,通常设置为 64MB 就足够了
innodb_log_buffer_size = 64M#InnoDB redo log大小,通常设置256MB 就足够了
innodb_log_file_size = 256M#InnoDB redo log文件组,通常设置为 2 就足够了
innodb_log_files_in_group = 2
innodb_file_per_table = 1#InnoDB共享表空间初始化大小,默认是 10MB,改成 1GB,并且自动扩展
innodb_data_file_path = ibdata1:1G:autoextend#设置临时表空间最大4G
innodb_temp_data_file_path=ibtmp1:500M:autoextend:max:4096M#启用InnoDB的status file,便于管理员查看以及监控
innodb_status_file = 1#当设置为0,该模式速度最快,但不太安全,mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。#当设置为1,该模式是最安全的,但也是最慢的一种方式。在mysqld 服务崩溃或者服务器主机crash的情况下,binary log 只有可能丢失最多一个语句或者一个事务。#当设置为2,该模式速度较快,也比0安全,只有在操作系统崩溃或者系统断电的情况下,上一秒钟所有事务数据才可能丢失。
innodb_flush_log_at_trx_commit = 1
max_connections=600
max_connect_errors=1000
max_user_connections=400#设置临时表最大值,这是每次连接都会分配,不宜设置过大 max_heap_table_size 和 tmp_table_size 要设置一样大
max_heap_table_size = 100M
tmp_table_size = 100M#每个连接都会分配的一些排序、连接等缓冲,一般设置为 2MB 就足够了
sort_buffer_size = 2M
join_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 2M#建议关闭query cache,有些时候对性能反而是一种损害
query_cache_size = 0
key_buffer_size = 8M
long_query_time = 120
slow_query_log=1 #开启mysql慢sql的日志
log_output=table,File #日志输出会写表,也会写日志文件,为了便于程序去统计,所以最好写表
slow_query_log_file=/data/log/slow.log
innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1#打印deadlock日志
innodb_print_all_deadlocks=1



相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
打赏
0
0
0
0
9
分享
相关文章
深入解析MySQL的EXPLAIN:指标详解与索引优化
MySQL 中的 `EXPLAIN` 语句用于分析和优化 SQL 查询,帮助你了解查询优化器的执行计划。本文详细介绍了 `EXPLAIN` 输出的各项指标,如 `id`、`select_type`、`table`、`type`、`key` 等,并提供了如何利用这些指标优化索引结构和 SQL 语句的具体方法。通过实战案例,展示了如何通过创建合适索引和调整查询语句来提升查询性能。
202 9
MySQL进阶突击系列(03) MySQL架构原理solo九魂17环连问 | 给大厂面试官的一封信
本文介绍了MySQL架构原理、存储引擎和索引的相关知识点,涵盖查询和更新SQL的执行过程、MySQL各组件的作用、存储引擎的类型及特性、索引的建立和使用原则,以及二叉树、平衡二叉树和B树的区别。通过这些内容,帮助读者深入了解MySQL的工作机制,提高数据库管理和优化能力。
MySQL派生表合并优化的原理和实现
通过本文的详细介绍,希望能帮助您理解和实现MySQL中派生表合并优化,提高数据库查询性能。
55 16
MySQL 面试题
MySQL 的一些基础面试题
MySQL派生表合并优化的原理和实现
通过本文的详细介绍,希望能帮助您理解和实现MySQL中派生表合并优化,提高数据库查询性能。
33 7
【深入了解MySQL】优化查询性能与数据库设计的深度总结
本文详细介绍了MySQL查询优化和数据库设计技巧,涵盖基础优化、高级技巧及性能监控。
43 0
面试必问的多线程优化技巧与实战
多线程编程是现代软件开发中不可或缺的一部分,特别是在处理高并发场景和优化程序性能时。作为Java开发者,掌握多线程优化技巧不仅能够提升程序的执行效率,还能在面试中脱颖而出。本文将从多线程基础、线程与进程的区别、多线程的优势出发,深入探讨如何避免死锁与竞态条件、线程间的通信机制、线程池的使用优势、线程优化算法与数据结构的选择,以及硬件加速技术。通过多个Java示例,我们将揭示这些技术的底层原理与实现方法。
84 3
MySQL 索引优化与慢查询优化:原理与实践
通过本文的介绍,希望您能够深入理解MySQL索引优化与慢查询优化的原理和实践方法,并在实际项目中灵活运用这些技术,提升数据库的整体性能。
106 5
数据库传奇:MySQL创世之父的两千金My、Maria
《数据库传奇:MySQL创世之父的两千金My、Maria》介绍了MySQL的发展历程及其分支MariaDB。MySQL由Michael Widenius等人于1994年创建,现归Oracle所有,广泛应用于阿里巴巴、腾讯等企业。2009年,Widenius因担心Oracle收购影响MySQL的开源性,创建了MariaDB,提供额外功能和改进。维基百科、Google等已逐步替换为MariaDB,以确保更好的性能和社区支持。掌握MariaDB作为备用方案,对未来发展至关重要。
60 3
MySQL崩溃保险箱:探秘Redo/Undo日志确保数据库安全无忧!
《MySQL崩溃保险箱:探秘Redo/Undo日志确保数据库安全无忧!》介绍了MySQL中的三种关键日志:二进制日志(Binary Log)、重做日志(Redo Log)和撤销日志(Undo Log)。这些日志确保了数据库的ACID特性,即原子性、一致性、隔离性和持久性。Redo Log记录数据页的物理修改,保证事务持久性;Undo Log记录事务的逆操作,支持回滚和多版本并发控制(MVCC)。文章还详细对比了InnoDB和MyISAM存储引擎在事务支持、锁定机制、并发性等方面的差异,强调了InnoDB在高并发和事务处理中的优势。通过这些机制,MySQL能够在事务执行、崩溃和恢复过程中保持
76 3

热门文章

最新文章

AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等