MySQL优化方向:
在设计上:存储引擎、字段类型、范式
在功能上:索引、缓存、分库分表
在架构上:集群、主从复制、读写分离、负载均衡
Sql语句优化
1、插入优化
● 大量数据采用批量插入形式(每提交一个SQL插入语句客户端就要网络请求到服务端,需要进行连接-传输数据-断开连接等操作)
● 事务设置手动提交,MySQL默认是自动提交,意味着每写一个SQL事务就自动提交,可能会频繁的设计事务开始和提交,所以建议手动提交。
2、order by 优化
Using filesort:通过表的索引或者全表扫描,读取满足条件的数据行,然后在排序缓冲区sort buffer 中完成排序后,所以不是通过索引直接返回的排序的结果都叫FileSort排序。
Useing index:通过有序索引顺序扫描直接返回有序数据,这种情况不需要额外的排序,效率比较高
根据多字段排序时,遵循最左侧原则。
3、group by优化
group by进行分组
在分组操作时,可以通过索引来提交效率。
分组操作时,索引的使用也要满足最左侧原则。
4、limit 优化
limit用来限制检索数据范围,一般用于分页功能。
比如:检索数据为limit 2000000,10。此时需要MySQL排序前2000010记录仅仅返回2000000~2000010间的数据,其他数据丢弃,查询排序的代价比价大。
select SID from sudent order by SID limit 2000000,10;
优化思路:一般分页查询时,通过创建覆盖索引能够起到比较好的提高性能,可以通过覆盖索引加子查询的形式进行优化。
覆盖索引:
查询列要被所有的索引覆盖到,就是select的数据列只用从索引中就能够获取到
select SID from student where Sname = '18' //覆盖索引
select Ssex from student where Sname = '18' //非覆盖索引
limit优化:
select * from student s,
(select SID from sudent order by SID limit 2000000,10 ) si
where s.SID = si.SID;
5、count优化
myisam引擎把一个表的总行数存在磁盘,因此count()直接获取结果,效率高。
innodb引擎执行count(),需要将数据一行一行从引擎中读出来,然后累计结果。
count的几种用法
用法:count(),count(主键),count(字段),count(1)
count(主键)
InnoDB引擎会遍历整张表,把每一行的主键id值都取出来,返回给服务层。服务层拿到主键后,直接按行进行累加(主键不可能为null)。
count(字段)
没有not null约束: InnoDB引擎会遍历整张表把每一行的字段值都取出来, 返回给服务层,服务层判断是否为null,不为null,计数累加。
有not null约束: InnoDB引擎会遍历整张表把每一行的字段值都取出来, 返回给服务层,直接按行进行累加。
count(1)
InnoDB引擎遍历整张表,但不取值。服务层对于返回的每一行,放一个数字"1"进去,直接按行进行累加。
count()
InnoDB引擎并不会把全部字段取出来,而是专门做了优化,不取值,服务层直接按行进行累加。
按照效率排序的话,count(字段) < count(主键id) < count(1)≈count(),所以尽量使用count()。
扩展:explain中Extra字段说明
Using index:SQL所需要的返回值所有列数据均在一颗索引树上,即无需访问实际的行记录。
Using where:Sql使用where过滤条件。
注意:使用了where条件的SQL,并不代表不需要优化,在需要进一步判断explain中type类型,type为ALL,需要扫描全表,就需要考虑优化,否则不需要考虑优化。
Using index condition:说明检索确实命中索引,但不是所有列都在索引树上,还需要访问实际的行记录 ,这个SQL语句性能也高,但不如Useing Index.。
Using filesort:得到的所有结果集,需要进行文件排序,SQL语句性能很差,需要进行优化。
索引优化
explain分析查询SQL
索引设计需要遵循的原则:
1,给区分度比较高的字段创建索引 比如:学号,身份证号。
2,经常需要排序,分组和多表联合操作的字段创建索引。
3,给经常作为查询条件的字段创建索引。
4,索引的数据不宜过多。
5,对于多例索引,优先指定最左侧的列。
6,删除不使用或者是很少使用的索引。
7,索引失效的场景: not in、like ‘%li’、< >等。
大表拆分优化
分库分表:
将以前存在一个数据库实例中的数据拆分成多个数据库实例,部署在不同的服务器上,这个是分库。
将以前存在一张表中的数据拆分成多张表,这是分表。
分库是为了解决服务器资源受单机限制,顶不住高并发的问题,把请求分发到多台服务器降低服务器的压力。
分表是为了解决单张表数据量过大,导致查询慢的问题。
分库
分库一般按照业务划分,比如划分订单库,用户库:
分库会带来的问题:
事务问题:无法保证事务完整型,解决方式:采用分布式事务。
连接Join问题:跨库无法join: 1、在业务代码上进行关联 2、适当冗余字段(当打印订单信息时也需要打印出用户的姓名,这时候需要跨库查找,所以可以将姓名字段在订单库中也写一份,便于数据的查询)
分表
分表分为两种:垂直分表和水平分表。
垂直分表如图:
垂直分表就是把一些不常用的大字段剥离出去。
水平分表,则是因为一张表内的数据太多了,上文也提到了数据越多 B+ 树就越高,访问的性能就差,所以进行水平拆分。
分表带来问题:
排序、count、分页问题:在业务层代码上将数据汇总,排序、分页处理。
路由问题:分表路由问题解决:
Hash路由:在插入时通过数据的hash值选择要插入的表,查询时可以通过hash值直接找到所对应的表,便于查询。
范围路由:数据有1000个,可以将1—500的数据存入第一个表,501—1000的数据存入第二张表,便于查询。
集群架构之主从复制
通常采用数据库集群方案来解决单机下高并发问题,也满足高可用,在多个数据库上一旦一个数据库宕机后,可以将请求分发到其他服务器,也能持续稳定提供服务。
MySQL通过主从复制来实现读写分离,负载均衡等功能。
binlog二进制日志
mysql集群中主从复制通过binlog将数据从主库同步到从库。
binlog日志默认是不开启的
通过命令查看:
show variables like ‘log_%’;
开启binlog日志,通过修改配置文件,MySQL server启动是会加载配置文件,Windows下是my.ini文件,Linux下是my.conf文件,在打开的文件中.
在[mysqld]上添加配置,保存并重启MySQL server服务器,默认就采用给定的存储引擎.
binlog_format = ROW //日志格式
配置文件写完需要重启服务器:systemctl restart mysqld.
通过show variables like ‘log_%’;查看,如果log_bin 为ON则表示开启二进制日志。
主从复制原理
mysql主从复制需要三个线程,master(binlog dump thread)、slave(I/O thread 、SQL thread)。
master
● binlog dump线程:当主库中有数据更新时,那么主库就会根据按照设置的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:MyCat是一款基于阿里开源产品Cobar而研发的,基于 Java 语言编写的开源数据库中间件。
● ShardingSphere:ShardingSphere是一套开源的分布式数据库中间件解决方案,它由ShardingJDBC、Sharding-Proxy和Sharding-Sidecar(计划中)这3款相互独立的产品组成。