mysql 优化 历时七天，史上最强MySQL优化总结，从此优化So Easy
发布时间：2022-11-29 12:37:24 所属栏目：教程 来源：
导读： 一、概述
1. 为什么要优化
2. 如何优化
二、字段设计
1. 典型方案
①. 对精度有要求
②. 尽量使用整数表示字符串（IP）
③. 尽可能使用not null
1. 为什么要优化
2. 如何优化
二、字段设计
1. 典型方案
①. 对精度有要求
②. 尽量使用整数表示字符串（IP）
③. 尽可能使用not null
|
一、概述 1. 为什么要优化 2. 如何优化 二、字段设计 1. 典型方案 ①. 对精度有要求 ②. 尽量使用整数表示字符串（IP） ③. 尽可能使用not null ④. 定长和非定长的选择 ⑤. 字段数不要过多字段注释是必要的、字段命名见名思意、可以预留字段以备扩展 2. 范式 ①. 第一范式：段原子性(关系型数据库有列的念，默认就符合了) ②. 第二范式：消除对主键的部分依赖(因为主键可能不止一个)；使用一 个与业务无关的字段作为主键 ③. 第三范式：消除对主键的传递依赖；高内聚, 如商品表可分为商品简略信息表和商品详情表两张表 三、存储引擎的选择（MyISAM和Innodb） 1. 功能差异 Innodb支持事务、 行级锁定、外健 2. 存储差异 ①. 存储方式：MyISAM的数据和索弓 |是分开存储的(.MYI.MYD) ， 而Innodb是存在一起的(.frm) ②. 表可移动性：可以通过移动表对应的MYI和MYD能够实现表的移动，而Innodb还有 额外的关联文件 ③. 碎片空间：MyISAM删除数据时会产生碎片空间(占用表文件空间)，需要定期通过optimizetable table-name手动优化。而Innodb不会。 ④. 有序存储：Innodb插入数据时按照主键有序来插入。因此表中数据默认按主键有序(耗费写入时间，因为需要在b+ tree中查找插入点，但查找效率高) 3. 选择差异 ①. 读多写少用MyISAM：新闻、博客网站 ②. 读多写也多用Innodb： 四、索引 1. 什么是索引 从数据中提取的具有标识性的关键字，并且有到对应数据的映射关系 2. 类型 ①. 主键索引primary key：要求关键字唯一且不为null ②. 普通索引key：符合索引仅按照第一字段有序 ③. 唯一索引unique key：要求关键字唯一 ④. 全文索引fulltext key (不支持中文) 3. 索引管理语法 ①. 查看索引 ②. 建立索引 ③. 删除索引 4. 执行计划explain 分析SQL执行是否用到了索引，用到了什么索引 5. 索引使用的场景 6. 语法细节 即使建立了索引，有些场景也不一定使用 7. 索引的存储结构 mysql 数据库优化_mysql数据库优化面试_mysql 优化 五、查询缓存 1. 将select查询结果缓存起来，key为SQL语句,value为查询结果 如果SQL功能一样，但只是多个空格或略微改动都会导致key的不匹配 2. 客户端开启 query. cache. _type 3. 客户端设置缓存大小 query_ cache .size 4. 重蛋缓存 reset query cache 5. 缓存失效 日对数据表的改动会导致基 于该数据表的所有缓存失效(表层面的管理) 六、分区 1. 默认情况下一张表对应一组存储文件，但当数据量较大时(通常千万条级别)需要将数据分到多组存储文件，保证单个文件的处理效率 2. partition by分区函数(分区字段)(分区逻辑) 3. 分区管理 4. 分区字段应选择常用的检素字段，否则分区意义不大 七、水平分割和垂直分割 1. 水平 多张结构相同的表存储同一类型数据 单独一张表保证id唯一性 2. 垂直 分割字段到多张表，这些表记录是一对应关系 八、集群 1. 主从复制 ①. 首先手动将slave和master同步一下 ②. start slave查看Slave_ IO_ Running和Slave_ SQL_ _Running,必须都为YES ③. master可读可写，但slave只能读，否则主从复制会失效需要重新手动同步 ④. mysqlreplicate快速配置主从复制 2. 读写分离(基于主从复制) ①. 使用原stcConecton WriteDatabase提供写连接 ReadDatabase提供读连接 ②. 借助Sping AOP和Aspec实现数据源动态切换 3. 负载均衡 算法 4. 高可用 为单机服务提供一个冗余机 九、典型SQL 1. 线上DDL 为了避免长时间表级锁定 2. 批量导入 ①. 先禁用索引和约束,导入之后统一建立 ②. 避免逐条事务 innodb为了保证一致性，默认为每条SQL加事务(也是要耗费时间的)，批量导入前应手动建立事务，导入完毕后手动提交事务。 3. limit offset,rows 避兔较大的offset (较大页码数) offset用来跳过数据，完全可以用过滤筛选数据，而不是查出来之后再通过offset跳过 4. select * 尽量查询所需字段,减少网络传输延时(影响不大) 5. order by rand（） 会为每条数据生成一个随机数最后根据随机数排序，可以使用应用程序生成随机主键代替 6. limit 1 如果确定了仅仅检索一条数据，建议都加上limit 1 十、慢查询日志 1. 定位查询效率较低的SQL,针对性地做优化 2. 配置项 3. 慢查询日志会自己记录超过临界时间的SQL，并保存在datadir下的xxx-slow.log中 mysql 数据库优化_mysql 优化_mysql数据库优化面试 十一、Profile 1. 自动记录每条SQL的执行时间和具体某个SQL的详细步骤花费的时间 2. 配置项日 开启profiling 3. 查看日志信息show profiles 4. 查看具体SQL的详细步骤花费的时间日 show profiles for query Query_ ID 十二、典型的服务器配置 1. max_ connections, 最大客户端连接数 2. table_ open_ cache, 表文件缓存句柄数，加快表文件的读写 3. key_ buffer. _size, 索引缓存大小 4. innodb_ buffer. pool size, innodb的缓冲池大小，实现innodb各种功能的前提 5. innodb_ file_ per_ table,每个表一个ibd文件mysql 优化， 否则innodb共享 表空间 十三、压测工具MySQLSlap 1. 自动生成sq|并执行来测试性能 myqslap -a-to-generate sql -root -root 2. 并发测试 mysqlslap --auto-generate-sql --concurrency= 100 -uroot -proot,模拟100个客户端执行sql 3. 多轮测试，反应平均情况 mysqlslap --auto-generate-sql --concurrency= 100 --interations=3 -uroot -proot,模拟100个客户端执行sql.执行3轮 （编辑：草根网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐
浙公网安备 33038102330472号