MySQL优化方向:

在设计上:字段类型,存储引擎,范式

在功能上:索引,缓存,分库分表

在架构上:集群,主从复制,负载均衡,读写分离

SQL优化

1.插入优化

  • 大量数据采用批量插入形式
  • 事务设置手动提交,MySQL默认是自动提交,意味着每写一个SQL事务就自动提交,可能会频繁的涉及事务开始和提交,所以建议手动提交

2.order by优化

  • Using filesort:通过表的索引或者全表扫描,读取到满足条件的数据行,然后在排序缓冲区 sort buffer 中完成排序,所以返回的数据不是通过索引直接返回的,这样的排序形式就叫filesort
  • Using index:通过有序索引顺序扫描直接返回有序数据,这种情况不需要额外的排序,所以效率比较高
  • 根据多字段排序时,遵循左前缀原则

3.group by优化

group by进行分组

在分组操作时,可以通过索引来提高效率,索引使用也要满足左前缀原则

4.limit优化

limit用来限制检索数据范围,一般用于分页功能

比如:检索数据为limit 2000000,10。此时需要MySQL排序前2000010记录仅仅返回2000000-2000010间的数据,其他数据丢弃,查询排序的代价比太大

优化思路:一般分页查询时,通过创建覆盖索引能够比较好的提高性能,可以通过创建覆盖索引加子查询的形式进行优化

select * from student s,(select SID from student order by SID limit 2000000,10) s1
where s.SID=s1.SID;

覆盖索引:

查询列要被所有的索引覆盖到,就是select的数据列只用从索引(只查询一个索引树)中就能够获取到

select SID from student where Sname="18";//覆盖索引
select Ssex from student where Sname="18";//非覆盖索引

5.count优化

myisam存储引擎是把表的总行数存储在磁盘上,因此count(*)直接获取结果,效率比较高

innodb存储引擎执行count(*),需要将数据一行一行读取出来,然后累计结果,效率较低

count()的几种用法:

count(*),count(1),count(主键),count(字段)

  • count(*):innodb引擎并不会把全部字段取出来,而是专门做了优化,不取值,服务层直接按行进行累加
  • count(1):innodb引擎遍历整张表,但不取值,服务层对于返回的每一行,放一个数字“1”进去,直接按行进行累加
  • count(主键):innodb引擎遍历整张表,把每一行的主键都取出来,返回给服务层,服务层拿到主键直接按行进行累加(主键不可能为空)
  • count(字段):没有not null约束,innodb引擎遍历整张表,把每一行的字段都取出来,判断是否为null,若不为null,按行进行累加

扩展:explain 中Extra字段说明

using index:SQL所需要的返回值所有列数据均在一颗索引树上,即无需访问实际的行记录

using where:SQL使用了where过滤条件

注:使用了where条件的SQL,并不代表不需要优化,需要进一步判断explain执行计划中type的类型,如果type为all,表示进行了全表扫描,则需要优化,否则不需要优化

using index condition:说明检索确实命中索引,但不是所有的列都在索引树上,还有需要访问实际的行记录,这个SQL语句性能也很高,但不如using index

using filesort:得到的所有的结果集,需要进行文件排序,SQL语句性能很差,需要优化

索引优化

explain分析查询SQL

索引设计需要遵循的原则:

  1. 给区分度比较高的字段创建索引,比如:学号,身份证号
  2. 给经常需要排序、分组和多表联合操作的字段创建索引
  3. 给经常作为查询条件的字段创建索引
  4. 索引的数据不宜过多
  5. 对于多列索引,优先指定最左侧的列
  6. 删除不使用或者很少使用的索引
  7. 索引失效的场景:not in,like“%li”,<>等

大表拆分优化

分库分表:

分库:将以前存在于一个数据库实例中的数据拆分成多个数据库实例,部署在不同的服务器上

分表:将以前存在于一张表上的数据拆分成多个表

分库是为了解决服务器资源受单机限制,顶不住高并发的问题,把请求分发到多台服务器降低服务器的压力

分表是为了解决单张表数据量过大,查询效率慢的问题

如何分库

分库一般按照业务划分,比如订单库,用户库

 分库会带来问题:

事务问题:无法保证事务完整性,采用分布式事务

连接join问题:跨库无法join:1.在业务代码上进行关联 2.适当冗余字段

如何分表

垂直分表:把一些不常用的大字段剥离出去

水平分表:则是因为一张表内的数据太多了,上文提到数据越多B+树就越高,访问的性能越差,所以进行水平拆分

分表带来问题:

排序、count、分页问题:在业务代码上将数据进行排序、count、分页处理

路由问题:分表路由问题:hash路由、范围路由

集群架构主从复制

通常采用数据库集群方案来解决高并发问题,也满足高可用,在多个数据库上一旦一个数据库宕机后,可以将请求分发到其他服务器上,可以持续提供服务

MySQL通过主从复制来实现读写分离,负载均衡等功能

 binlog二进制日志

undo log 、redo log

MySQL集群中主从复制通过binlog将数据从主库同步到从库

binlog默认是不开启的

通过命令查看

show variables like "log_%";

 开启binlog日志,通过修改配置文件,MySQL server启动时会自动加载配置文件,Windows下my.ini文件,Linux下是my.conf文件,在打开的文件中在[mysql]上添加配置,保存并重启MySQL server服务器,默认就采用给定的日志文件

binglog_format=ROW

配置文件写完需要重启服务器:systrmctl restart mysql

通过show variables like "log_%"命令,如果log_bin为ON则表示开启二进制日志

主从复制原理

 MySQL主从复制需要三个线程,master(log dump thread),slave(I/O thread,SQL thread)

master

  • log dump thread:当主库中有数据更新时,主库就会根据按照设置的binlog格式,将此次更新的事件类型写入到主库的binlog文件中,此时主库会创建log dump线程通知slave有数据更新,当I/O线程请求日志内容时,会将此时的binlog名称和当前更新的位置同时传给slave的I/O线程

slave

  • I/O线程:该线程会连接到master,向log dump线程请求一份指定binlog文件位置的副本,并将请求回来的binlog存到本地的relay log中,relay log 和binlog日志一样是记录了数据更新的事件,他也是按照递增后缀名的方式,产生多个relay log(host_name_relay_bin.000001)文件,slave会使用一个index文件(host_name_relay_bin.index)来追踪当前正在使用的relay log文件。
  • SQL线程:该线程检测到relay log有更新后,会读取并在本地做redo操作,把发生在主库的事件在本地重新执行一遍,来保证主从数据同步

此外,如果有一个relay log文件中的全部事件都执行完毕,那么SQL线程会自动将该relay log文件删除掉

集群成熟方案

数据库中间件:常用的有MySQL Proxy、MyCat以及ShardingSphere等等

  • MySQL Proxy:是官方提供的MySQL中间件产品可以实现负载均衡,读写分离等,是一个基于服务器端的代理
  • MyCat:是一款基于阿里开源产品Cobar而研发的,基于java语言编写的开源数据库插件
  • ShardingSphere:是一套开源的分布式数据库中间件解决方案,它由ShardingJDBC、Sharding-Proxy、Sharding-Sidecar(计划中)这三款相互独立的产品组成

Logo

华为云1024程序员节送福利,参与活动赢单人4000元礼包,更有热门技术干货免费学习

更多推荐