mysql优化方案

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款相互独立的产品组成。