mysql优化

1.mysql 执行记录缓存

• 需要注意的是，如果执行的是select语句并且是MySQL 8.0之前的版本的话
  则会去MySQL的查询缓存中查看之前是否有执行过这条SQL
  如果缓存中可以查到，则会直接返回查询结果，这样查询性能就会提升很高

2. sql 语句的执行流程

注意 如果是mysql 8.0 以上是没有查询缓存的步骤了.

3. SHOW ENGINES命令

使用SHOW ENGINES命令来查看MySQL数据库使用的存储引擎,如下图所示:

常用的数据库引擎有InnoDB、MyISAM、 MEMORY等

• 其中InnoDB支持事务功能，而MyISAM不支持事务，但MyISAM拥有较高的插入和查询的速度
• MEMORY是内存型的数据库引擎,它会将表中的数据存储到内存中
  因为它是内存级的数据引擎，因此具备最快速的查询效率
  缺点:重启数据库之后，所有数据都会丢失因为这些数据是存放在内存中的

4.查询缓存的利弊

●MySQL 8.0之前可以正常的使用查询缓存的功能
通过“SHOW GLOBAL VARIABLES LIKE ‘query_cache_type’ ”命令
查询数据库是否开启了查询缓存的功能

查询缓存功能的结果值:
1 OFF:关闭了查询缓存功能
2 ON:开启了查询缓存功能
3 DEMAND:在sql语句中指定sql_ cache关键字才会有查询缓存
必须使用sql cache才可以把该select语句的查询结果缓存起来
比如“select sql cache name from token where tid=1010”语句

开启和关闭查询缓存可以通过修改MySQL的配置文件my.cnf进行修改
它的配置项如下: query_cache_type = ON 更改配置后需要重启mysql才能生效

● 查询缓存的功能要根据实际的情况进行使用，建议设置为按需缓存(DEMAND) 模式
因为查询缓存的功能并不是那么好用
比如我们设置了query_cache_type= ON,当我们好不容易缓存了很多查询语句之后
任何一条对此表的更新操作都会把和这个表关联的所有查询缓存全部清空
那么在更新频率相对较高的业务中，查询缓存功能完全是一个鸡肋
所以在mysql 8.0 以后的版本完全移除了这个功能

●最常用的数据库引擎是InnoDB，它是MySQL 5.5.5之后的默认引擎
优点是支持事务，且支持4种隔离级别
●读未提交:也就是一个事务还没有提交时，它做的变更就能被其他事务看到
读已提交:指的是- -个事务只有提交了之后，其他事务才能看得到它的变更
●可重复读:此方式为默认的隔离级别，它是指一个事务在执行过程中(从开始到结束)看到的
数据都是一致的，在这个过程中未提交的变更对其他事务也是不可见的
●可串行化:是指对同一行记录的读、写都会添加读锁和写锁，后面访问的事务必须等前一个事务执行
完成之后才能继续执行，所以这种事务的执行效率很低
所以在mysql 8.0 之后就废除了这种设计理念.

事物隔离级别

●最常用的数据库引擎是InnoDB,它是MySQL 5.5.5之后的默认引擎
优点是支持事务，且支持4种隔离级别
●读未提交:也就是一个事务还没有提交时，它做的变更就能被其他事务看到
●读已提交:指的是一个事务只有提交了之后，其他事务才能看得到它的变更
●可重复读:此方式为默认的隔离级别，它是指一个事务在执行过程中(从开始到结束)看到的
数据都是一致的，在这个过程中未提交的变更对其他事务也是不可见的
●可串行化:是指对同一行记录的读、写都会添加读锁和写锁，后面访问的事务必须等前一个事务执行
完成之后才能继续执行，所以这种事务的执行效率很低

●InnoDB还支持外键、崩溃后的快速恢复、支持全文检索(需要5.6.4+版本)、集群索引
以及地理位置类型的存储和索引等功能
●MyISAM引擎是MySQL原生的引擎，但它并不支持事务功能
这也是后来被InnoDB替代为默认引擎的主要原因

●MyISAM有独立的索引文件，因此在读取数据方面的性能很高
它也支持全文索引、地理位置存储和索引等功能，但不支持外键

mysql 面试题

在一个自增表里面一共有5条数据，id从1到5，删除了最后两条数据
也就是id为4和5的数据，之后重启的MySQL服务器，又新增了一条数据
请问新增的数据id为几?
答案:如果表为MyISAM引擎，那么id就是6,如果是InnoDB那么id 就是4
●但是这个情况在高版本的InnoDB中，也就是MySQL 8.0之后就不准确
它的id就不是4了，而是6了
●因为在MySQL 8.0之后InnoDB会把索引持久化到日志中
重启服务之后自增索引是不会丢失的，因此答案是6

mysql优化方案有那些 ?

MySQL数据库常见的优化手段的三个层面:

• SQL和索引优化

• 数据库结构优化

• 系统硬件优化

• SQL和索引优化
  tip: 我们应该尽可能的使用主键查询，而非其他索引查询，因为主键查询
  不会触发回表查询，因此节省了一部分时间，变相的提高了查询的性能.

  • 在MySQL 5.0之前的版本要尽量避免使用or查询，可以使用union或者子查询来替代因为早期的MySQL版本使用or查询可能会导致索引失效
  • 在MySQL 5.0之后的版本中引入了索引合并，简单来说就是把多条件查询
    比如or或and查询的结果集进行合并交集或并集的功能，因此就不会导致索引失效的问题了
  • 避免在where查询条件中使用!=或者<>操作符因为这些操作符会导致查询引擎放弃索引而进行全表扫描
  • 适当使用前缀索引，MySQL是支持前缀索引的也就是说我们可以定义字符串的一部分来作为索引
  • 我们知道索引越长占用的磁盘空间就越大，那么在相同数据页中能放下的索引值也就越少这就意味着搜索索引需要的查询时间也就越长，进而查询的效率就会降低
    所以我们可以适当的选择使用前缀索引，以减少空间的占用和提高查询效率

  -- 比如现在有一个表User , slat 的值是 6位以上的无序的随机数
  -- 添加索引前 耗费时间 0.34 秒
  select * from `User` where slat ='58958954';
  
  -- 使用以下命令 添加前缀索引
  alter table `User` add index (slat(5));
  -- 再次执行sql 查询slat为其他值的数据 效率为 0.01 秒
  select * from `User` where slat ='60424075';
  

比如在实际开发的业务场景中用户的邮箱号 就可以设置前缀索引 比如统一规定邮箱都为qq邮箱的话,那么后缀都是@qq.com 那么设置邮箱字段的前5-6位或者更多位数的话,就能更快的查询到数据了.

• 尽量避免使用select *,而是查询需要的字段 这样可以提升速度，以及减少网络传输的带宽压力
• 尽量使用Join语句来替代子查询，因为子查询是嵌套查询，而嵌套查询会新创建一张临时表而临时表的创建与销毁会占用一定的系统资源以及花费一定的时间
  但Join语句并不会创建临时表,因此性能会更高
• 注意查询结果集 我们要尽量使用小表驱动大表的方式进行查询 也就是如果B表的数据小于A表的数据，那执行的顺序就是先查B表再查A表

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

• 不要在列字段.上进行算术运算或其他表达式运算 否则可能会导致查询引擎无法正确使用索引，从而影响了查询的效率
• 适当增加冗余字段增加冗余字段,可以减少大量的连表查询
  因为多张表的连表查询性能很低，所有可以适当的增加冗余字段
  以减少多张表的关联查询，这是以空间换时间的优化策略
• 最小数据长度 一般说来数据库的表越小，那么它的查询速度就越快 因此为了提高表的效率，应该将表的字段设置的尽可能小 比如身份证号，可以设置为char(18)就不要设置为varchar(18)
• 使用最简单数据类型 能使用int类型就不要使用varchar类型 因为int类型比varchar类型的查询效率更高
• 尽量少定义text类型 text类型的查询效率很低，如果必须要使用text定义字段
  可以把此字段分离成子表，需要查询此字段时使用联合查询
  这样可以提高主表的查询效率
• 适当分表、分库策略 分表和分库方案也是我们经常说的垂直分隔(分表)和水平分隔(分库) 分表是指当一张表中的字段更多时，可以尝试将一张大表拆分为多张子表 把使用比较高频的主信息放入主表中，其他的放入子表 这样我们大部分查询只需要查询字段更少的主表就可以完成了从而有效的提高了查询的效率
  分库是指将一个数据库分为多个数据库，比如我们把一个数据库拆分为了多个数据库
  一个主数据库用于写入和修改数据，其他的用于同步主数据并提供给客户端查询
  这样就把一个库的读和写的压力,分摊给了多个库从而提高了数据库整体的运行效率

硬件优化

• 磁盘应该尽量使用有高性能读写能力的磁盘，比如固态硬盘 这样就可以减少I/0运行的时间，从而提高了MySQL整体的运行效率
• 磁盘 也可以尽量使用多个小磁盘而不是一个大磁盘, 因为磁盘的转速是固定的 有多个小磁盘就相当于拥有多个并行运行的磁盘一样
• 网络 保证网络带宽的通畅(低延迟)以及够大的网络带宽是MySQL正常运行的基本条件
  如果条件允许的话也可以设置多个网卡，以提高网络高峰期MySQL服务器的运行效率
• 内存 MySQL服务器的内存越大,那么存储和缓存的信息也就越多而内存的性能是非常高的，从而提高了整个MySQL的运行效率.

正确使用联合索引

联合索引是遵循从左往右的匹配原则 . 如果左边的第一个索引对不上,那么就无法使用联合索引.比如有联合索引 (name,age) 那么就不用再添加name字段的索引.

慢查询的优化

• 慢查询通常的排查手段是先使用慢查询日志功能，查询出比较慢的SQL语句
  然后再通过explain来查询SQL语句的执行计划，最后分析并定位出问题的根源.
• 慢查询日志指的是在MySQL中可以通过配置来开启慢查询日志的记录功能 超过long_query_time值的SQL将会被记录在日志中
  
  设置慢查询开启

通过设置“slow_ query_log=1” 来开启慢查询,它的开启方式有两种:
●通过MySQL命令行的模式进行开启，只需要执行“set global slow_ query log=1”即可
然而这种配置模式再重启MySQL服务之后就会失效
●通过修改MySQL配置文件的方式进行开启，我们需要配置my.cnf中的
“slow_query_log=1"即可，并且可以通过设置“slow_query_log_file=/tmp/mysql slow.log” 来配置慢查询日志的存储目录，但这种方式配置完成之后需要重启MySQL服务器才可生效
注意 开启慢sql 会在生产环境中有一定的影响效率.

explain 关键字分析sql

比如执行一个sql 分析结果如下

每个列的值对应含义如下

结果中最重要的是type字段 结果值如下所示

当type为all时，则表示全表扫描，因此效率会比较低
此时需要查看一下为什么 会造成此种原因，是没有创建索引还是索引创建的有问题?
以此来优化整个MySQL运行的速度

记录根据where 条件进行update 一次问题

比如现在代码逻辑中最终生成一条sql

udpate `user` set email = 'xxx@qq.com' where name ='%xxx%';

假设现在的逻辑是没有根据主键修改数据 此时,如果是另外一个接口访问到类似的业务逻辑要进行数据修改. sql如下

udpate `user` set email = 'xxx@mail.com' where name ='%xxx%';

此时是会等待第一个sql执行完成才会执行第二个sql,因为mysql数据库在根据列进行 update 且查询的类没有索引的时候,会进行锁表. 想要接近这个问题,只需要在name列上添加索引,问题就解决了.