文章目录

  • 一.最大连接数优化
  • 二.启用查询缓存
  • 三.引擎优化
  • 四.索引
  • 五.SQL语句优化
  • 常有的优化方式

mysql数据库调优面试 mysql配置调优面试题_查询缓存

一.最大连接数优化

  • 1.查询最大连接数
  • 方法一：SQL指令查询
show variables like '%max_connections%';
  • 方法二：也可通过DBMS查看（navicat，workbeanch）
  • 2.修改最大连接数
  • 方法一：SQL指令修改
//此修改方式重启数据库之后会重置
set global max_connections=300
  • 方法二：修改my.ini文件(永久修改)
  • 在自己的盘符找到配置文件然后修改

mysql数据库调优面试 mysql配置调优面试题_查询缓存_02

二.启用查询缓存

特别注意：查询缓存从MySQL 5.7.20开始已被弃用，并在MySQL 8.0中被删除。

  • 简单描述MySQL5.0中的操作方法

mysql数据库调优面试 mysql配置调优面试题_存储引擎_03

为什么MySQL8.0直接把查询缓存的功能删除了呢？

  • 一种说法是不建议使用查询缓存，因为查询缓存往往弊大于利。查询缓存的失效非常频繁，只要有对一个表的更新，这个表上的所有的查询缓存都会被清空。因此很可能你费劲地把结果存起来，还没使用呢，就被一个更新全清空了。对于更新压力大的数据库来说，查询缓存的命中率会非常低。除非你的业务有一张静态表，很长时间更新一次，比如系统配置表，那么这张表的查询才适合做查询缓存。

三.引擎优化

  • 执行以下sql即可查询到mysql中的存储引擎
SHOW ENGINES

mysql数据库调优面试 mysql配置调优面试题_存储引擎_04

  • InnoDB存储引擎
  • InnoDB是事务型数据库的首选引擎，InnoDB是目前MYSQL的默认事务型引擎，是目前最重要、使用最广泛的存储引擎。支持事务安全表（ACID），支持行锁定和外键。
  • InnoDB主要特性有：
  • ①InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事物安全（ACID兼容）存储引擎。
  • ②InnoDB是为处理巨大数据量的最大性能设计。它的CPU效率可能是任何其他基于磁盘的关系型数据库引擎锁不能匹敌的
  • ③InnoDB存储引擎完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB将它的表和索引在一个逻辑表空间中，表空间可以包含数个文件（或原始磁盘文件）。这与MyISAM表不同，比如在MyISAM表中每个表被存放在分离的文件中。InnoDB表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上
  • ④InnoDB支持外键完整性约束，存储表中的数据时，每张表的存储都按主键顺序存放，如果没有显示在表定义时指定主键，InnoDB会为每一行生成一个6字节的ROWID，并以此作为主键
  • ⑤InnoDB被用在众多需要高性能的大型数据库站点上
  • 应用场景：由于其支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）。
  • MyISAM存储引擎
  • MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事物和外键。
  • MyISAM主要特性有：
  • 1、支持大文件（达到63位文件长度）
  • 2、当把删除和更新及插入操作混合使用的时候，动态尺寸的行产生更少碎片。
  • 3、每个MyISAM表最大索引数是64，每个索引最大的列数是16
  • 4、最大的键长度是1000字节，这也可以通过编译来改变，对于键长度超过250字节的 情况，一个超过1024字节的键将被用上
  • 5、BLOB和TEXT列可以被索引
  • 6、NULL被允许在索引的列中，这个值占每个键的0~1个字节
  • 7、所有数字键值以高字节优先被存储以允许一个更高的索引压缩
  • 8、MyISAM类型表的AUTO_INCREMENT列更新比InnoDB类型的AUTO_INCREMENT更快
  • 9、可以把数据文件和索引文件放在不同目录
  • 10、每个字符列可以有不同的字符集
  • 11、有VARCHAR的表可以固定或动态记录长度
  • 12、VARCHAR和CHAR列可以多达64KB
  • 场景：如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。
  • 引擎的查看与修改
  • ①查看引擎
show create table tablename；
SHOW TABLE STATUS FROM sites WHERE NAME='site';  
SHOW TABLE STATUS FROM db_name WHERE NAME='table_name';
  • ②修改引擎表
alter table table_name engine=innodb;  
alter table table_name engine=myisam;

补充知识点

  • 行锁
  • ①读锁\乐观锁：允许其他事务查询数据，但不允许修改数据
select column from table where conditions lock in share mode；
  • ②写锁\悲观锁：不允许其他事务查询及修改数据
select math from zje where math >60 for update；
  • 小结：
  • a. 行锁只有InnoDB有
  • b. 提交事务就是释放锁
  • c. 锁需要在事务内开启

引擎优化小结

  • ①一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求，使用合适的存储引擎，将会提高整个数据库的性能
  • ②InnoDB提供提交、回滚、崩溃恢复能力及并发控制能力，适用于对数据更新操作频率高的数据表
  • ③MyISAM引擎能提供较高的查询效率，适用于对数据进行频繁查询操作的数据表

四.索引

  • 这里是便于查询可以设置索引，让查询效率变高

索引分类

语法

普通索引

create index name on s1(name);

唯一索引

create unique index age on s1(age);

聚合索引（多字段）

create index name on s1(id,name);

全文索引（MyISAM）

ALTER TABLE article ADD FULLTEXT INDEX 索引名称(title,content)

  • 聚合索引按照从左到右的匹配原则。也就是必须先匹配ID才能匹配name查询。
  • 全文检索的查询方式：
  • SELECT * FROM article WHERE MATCH(title, content) AGAINST('查询字符串')
  • 全文索引只适合MyISAM引擎的数据表。并且只能对英文进行检索

五.SQL语句优化

  • SQL优化的重心是查询优化，查询优化的重心是建立索引。所以查询优化主要是避免出现导致索引失效的查询。
  • ①避免在索引列上出现null。
  • ②不要在索引列上进行算术运算。：select age+1 from user
  • ③避免实现!=或者<>、is null或者is not null、in等可能导致全表遍历的操作。
  • ④模糊查询只能使用右边%。
  • ⑤where语句后尽可能少用小括号、或者不要出现小括号嵌套小括号。
  • 小结：
    ① 表不能太多（超过200张）
    ②单表的列数不能太多（超过40列）
    ③32位系统单表最大4G，64位没有限制。

Whatever is worth doing is worth doing well.

mysql数据库调优面试 mysql配置调优面试题_存储引擎_05


2020.03.02