mysql数据库优化方案

通过show status命令了解各种sql的执行效率

查看本session的sql执行效率
show status like 'Com_%';
查看全局的统计结果
SHOW GLOBAL STATUS LIKE  'Com_%'
查看服务器的状态
show global status;

结果

Com_select:执行select操作的次数，依次查询之累加1
Com_insert:执行insert操作的次数，对于批量插入的insert操作，只累加依次
Com_update:执行update操作的此时
Com_delete:执行delete的次数

上面的参数是对所有存储引擎的表进行累计，下面参数是针对InnoDB存储引擎的，累加的算法也是略有不同的

Innodb_rows_read:SELECT查询返回的行数
Innodb_rows_insered:执行inser操作插入的行数
Innodb_rows_updated:执行UPDATE操作更新的行数
Innodb_rows_deleted执行DELETE操作删除的行数

通过上述的参数可以了解当前数据库的应用是插入更新为主还是查询操作为主，以及各类的SQL的执行比例是多少。对于更新操作的计算，是对执行次数的计数，无论提交还是回滚都会进行累加对于事务形的应用，通过Com_commit和Com_rollback可以了解事务提交和回滚的情况，对于回滚操作非常频繁的数据库，可能意味着应用编写存在的问题

Connections:试图连接MySql服务器的次数
Uptime：服务器工作时间
Slow_queries:慢查询的次数

定位执行效率低的SQL语句

通过慢查询日志定位那些执行效率较低的sql语句，用--log-show-queries[=file_name]选项去启动，mysqlId写一个包含所有执行时间超过long_querty_time秒的sql语句的日志文件
慢查询日志在查询结束后才记录，所以在应用反应执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用show processlist命令查看当前Mysql在进行的线程，包括线程的状态，是否锁表等，可以实时查看sql的执行情况，同时对一些锁表进行优化

通过explain分析执行sql的执行计划

explain或者desc获取mysql如何执行select语句的信息

explain select * from user;

结果 包含，id，select_type,table.type....

每个列说明

select_type：表示SELECT的类型，常见的取值有simple（简单表，即不用表连接或者子查询），primary（主查询，即外部查询），union（union中的第二个或者后面的查询语句），subquery（子查询中的第一个select）等
table:输出结果集
type：表示Mysql在表中找到所需行的方式，或者叫访问类型，常见类型如：all,index,range,ref,eq_ref,const,system,null,从做到右，性能由差到好

mysql 4.1引入了explain extended命令，通过explain extended 加上show warnings可以查看mysql 真正被执行之前优化器所做的操作

explain select * from users;
show warnings;
可以从warning的字段中能够看到，会去除一些恒成立的条件，可以利用explain extended的结果来迅速的获取一个更清晰易读的sql语句

通过show profile 分析sql

查看mysql是否支持profile
select @@have_profiling;

默认profiling是关闭的，可以通过set语句在session级别启动profiling;
select @@profiling;

set profiling=1;

通过profile，我们能够更清楚的了解sql执行的过程。例如我们知道，MyISAM表有表元数据的缓存（例如行，即COUNT()值),对于MyISAM表的COUNT()是不需要消耗太多资源，而对于Innodb来说，就没有这种元数据，CONUT（*）执行的比较慢

select count(*) from users;
执行完毕查看 
show profiles

可以查看之前的queryid
show profile for query 2； 可以查看执行过程中线程的每个状态和消耗时间
其中 sendingdata 状态表示mysql线程开始访问数据行并把结果返回给客户端，而不仅仅是返回给客户端，由于在sending data状态下，mysql线程往往需要做大量的磁盘读取操作；所以经常是整个查询中最耗时的状态

mysql 支持进一步选择all,cpu,block io,context,switch,page faults等明细来查看mysql在使用什么资源上耗费了过高的时间，例如，选择查看cpu的耗费时间

show profile cpu for query 6;

对比MyISAM的操作，同样执行count(*)操作，检查profile，Innodb表经历了Sending data状态，而MyISAM的表完全不需要访问数据 如果对Mysql 源码感兴趣，可以通过show profile source for query查看sql解析执行过程的每个步骤对应的源码文件

show profile source for query 6

通过trace分析优化器如何 MySql 5.6提供对sql的跟踪trace，通过trace文件能够进一步了解为什么优化器选择A执行计划而不选择B执行计划，帮助我们更好地了解优化器的行为

使用方式
首先打开trace，设置格式为json，设置trace最大能够使用的内存，避免解析过程中因为默认内存小而不能完整显示
set optimizer_trace="enabled=on",end_markers_in_json=on;
set optimizer_trace_max_mem_size=1000000;

接下来执行trace的sql语句，
select * from ....where....
最后检查information_schema.optimizer_trace就可以知道Mysql如何执行sql
select * from information_schema.optimizer_trace

索引问题

mysql提供四种索引

MySql目前不支持函数索引，但是能对列的前面某一部分进行索引，例如标题title字段，可以只取title的前10个字符索引，这样的特性大大缩小了索引文件的大小，但前缀索引也有缺点，在排序order by和分组group by操作的时候无法使用

常用的索引就是B-tree索引和hash索引，资只有memory引擎支持HASH索引，hash索引适用于key-value查询，通过hash索引比B-tree索引查询更加迅速，但是hash索引不支持范围查找例如<><==,>==等操作，如果使用memory引擎并且where不使用=进行索引列，就不会用的索引。Memory只有在"="的条件下才会使用索引。

简单的优化方法

本语句可以用于分析和存储表的关键字分布，分析的结果可以使得系统得到准确的统计信息使得sql，能够生成正确的执行计划。如果用户感觉实际执行计划并不预期的执行计划，执行一次分析表可能会解决问题

analyze table payments;

检查表：检查表：检查表的作用是检查一个表或多个表是否有错误,也可以检查视图是否错误

check table payment;

优化表:如果删除了表的一大部分，或者如果已经对可变长度的行表（含varchar、blob、text列）的表进行改动，则使用optimize 进行表优化，这个命令可以使表中的空间碎片进行合并、并且可以消除由于删除或者更新造成的空间浪费

optimize table payment;

对于innodb引擎的表，可以通过设置innodb_file_per_taable参数，设置InnoDb为独立表空间模式，这样每个数据库的每个表都会生成一个独立的idb文件，用于存储表的数据和索引，可以一定程度减少Innodb表的空间回收问题,另外，在删除大量数据后，Innodb表可以通过alter table但是不锈钢引擎方式来回收不用的空间

alter table payment enigine=innodb;

ANALYZE,CHECK,OPTIMIZE,ALTER TABLE执行期间都是对表进行锁定，因此要在数据库不频繁的时候执行相关的操作

面试回答数据库优化问题从以下几个层面入手

（1）、根据服务层面：配置mysql性能优化参数；（2）、从系统层面增强mysql的性能：优化数据表结构、字段类型、字段索引、分表，分库、读写分离等等。（3）、从数据库层面增强性能：优化SQL语句，合理使用字段索引。（4）、从代码层面增强性能：使用缓存和NoSQL数据库方式存储，如MongoDB/Memcached/Redis来缓解高并发下数据库查询的压力。（5）、减少数据库操作次数，尽量使用数据库访问驱动的批处理方法。（6）、不常使用的数据迁移备份，避免每次都在海量数据中去检索。（7）、提升数据库服务器硬件配置，或者搭建数据库集群。（8）、编程手段防止SQL注入：使用JDBC PreparedStatement按位插入或查询；正则表达式过滤（非法字符串过滤）；

原文：https://cloud.tencent.com/developer/article/1125408

MySQL优化三大方向

① 优化MySQL所在服务器内核(此优化一般由运维人员完成)。

② 对MySQL配置参数进行优化（my.cnf）此优化需要进行压力测试来进行参数调整。

③ 对SQL语句以及表优化。

MySQL参数优化
1:MySQL 默认的最大连接数为 100，可以在 mysql 客户端使用以下命令查看
mysql> show variables like 'max_connections';
2:查看当前访问Mysql的线程
mysql> show processlist;
3:设置最大连接数
mysql>set globle max_connections = 5000;
最大可设置16384,超过没用
4:查看当前被使用的connections
mysql>show globle status like 'max_user_connections'

案例：查询近一月常上网人员名单（头像、姓名、上网次数、常去网吧）（desc）：统计近30天(近一月)内的上网数据(取前15条数据)

初始思路：

查询出近一个月前15条上网人员信息

在通过上网人员ID查询出近一个月他常去网吧

优化思路：

查询出近一个月上网人员到网吧上网次数（人员+网吧）

对上网次数求和

sql:

select v.visitor_id AS visitorId,v.visit_face_url AS picUrl, m.name AS name, v.netCount AS totalInternet,o.org_name  AS oftenOrg from 
    (select a.visitor_id, a.org_id ,a.visit_face_url,max(a.netbarCount) as maxNetCount, SUM(a.netbarCount) as netCount from 
         (select r.visitor_id, r.org_id,r.visit_face_url,count(1) as netbarCount 
         from t_record_visitor r 
         WHERE r.visit_time between date_add(now(),interval -1 month) and now() 
         GROUP BY r.visitor_id, r.org_id) a 
     GROUP BY a.visitor_id 
     ORDER BY netCount DESC
     LIMIT 15) v
LEFT JOIN t_m_visitor m ON v.visitor_id = m.visitor_id
LEFT JOIN t_m_org o ON v.org_id = o.org_id

2，mysql数据库主从延时长，如何优化？

select for update引发死锁分析

1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描。
3.应尽量避免在 where 子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。
4.应尽量避免在 where 子句中使用or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描
5.in 和 not in 也要慎用，否则会导致全表扫描，如：select id from t where num in(1,2,3) 对于连续的数值，
    能用 between 就不要用 in 了：select id from t where num between 1 and 3
6.下面的查询也将导致全表扫描：select id from t where name like ‘%李%’若要提高效率，可以考虑全文检索。
7. 如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；
它必须在编译时进行选择。然 而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。
如下面语句将进行全表扫描：select id from t where num=@num可以改为强制查询使用索引：select id from t with(index(索引名)) where num=@num
8.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：select id from t where num/2=100应改为:select id from t where num=100*2
9.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：select id from t where substring(name,1,3)=’abc’ ，name以abc开头的id应改为:
select id from t where name like ‘abc%’

10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
11.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
12.不要写一些没有意义的查询，如需要生成一个空表结构：select col1,col2 into #t from t where 1=0
这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样： 
create table #t(…)

13.很多时候用 exists 代替 in 是一个好的选择：select num from a where num in(select num from b)
用下面的语句替换： 
select num from a where exists(select 1 from b where num=a.num)

14.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。
15. 索引并不是越多越好，索引固然可 以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有 必要。
16. 应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。
17.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
18.尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
19.任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。
20.尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。
21.避免频繁创建和删除临时表，以减少系统表资源的消耗。
22.临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。
23.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。
24.如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。
25.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。
26.使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。
27. 与临时表一样，游标并不是不可使 用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时 间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。
28.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送DONE_IN_PROC 消息。
29.尽量避免大事务操作，提高系统并发能力。
30.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

mysql JOIN 语句的优化原则-- mysql JOIN 语句的优化原则
-- 1.小表驱动大表（EXPLAIN的第一行是驱动表）,WHERE 条件驱动表的筛选j出尽量少的数
-- 2.where里有筛选条件，而且可以使用索引,并对驱动表晒选出尽量少的行数
-- 3.非驱动表连接join字段最好是主键索引，无法建立索引的时候，设置足够的Join Buffer Size
-- 4.尽量避免联表数量，避免尽量少返回字段，避免返回字段有计算，越多，嵌套循环算法越慢，
-- 5.join连接表的语句不能再用子查询，COUNT(1) 分页统计返回字段要尽量少。
-- 6.扫描行数必须控制在百万级别，返回行数控制在千级别，且要分页处理.
-- 7.必须遵循以上原则，否则不用join，改单表查询在拼接，或者用es查询在拼接

MySQL分页查询优化

1，使用子查询优化

这种方式先定位偏移位置的 id，然后往后查询，这种方式适用于 id 递增的情况。

select * from orders_history where type=8 and id>=(select id from orders_history where type=8 limit 100000,1) limit 100;

2，使用 id 限定优化

select * from orders_history where id >= 1000001 limit 100;

3,使用临时表优化

MySQL分库分表

主从复制只能减轻读压力，但无法解决容量问题。

影响：

无法执行 DDL，比如添加一列，或者增加索引，都会直接影响线上业务，导致长时间的数据库无响应。
无法备份，与上面类似，备份会自动先 lock 数据库的所有表，然后导出数据，量大了就没法执行了
影响性能与稳定性，系统越来越慢，随时可能会出现主库延迟高，主从延迟很高，且不可控，对业务系统有极大的破坏性影响。

分表

把一个表的数据放到多个表中，然后查询时，就查一个表。

分库

单库一般达到2000并发，亟需扩容，合适的单库并发值推荐在1000/s。可将一个库的数据拆分到多个库，访问时就访问一个库。

分库分表中间件

sharding-jdbc（shardingsphere）

github: https://github.com/apache/incubator-shardingsphere

最初由当当开源，client层方案。
SQL语法支持较多，支持分库分表、读写分离、分布式id生成、柔性事务（最大努力送达型事务、TCC事务）。被大量公司使用，我司也在用。现在已经升级为Apache组织的项目。
这种client层方案的优点：
不用部署，运维成本低，无需代理层的二次转发请求，性能很高
但遇到升级啥的需要各个系统都重新升级版本再发布，各个系统都需要耦合sharding-jdbc的依赖。

Sharding可简单定义为将大数据库分布到多个物理节点上的一个分区方案。每一个分区包含数据库的某一部分，称为一个shard，分区方式可以是任意的，并不局限于传统的水平分区和垂直分区。
一个shard可以包含多个表的内容甚至可以包含多个数据库实例中的内容。每个shard被放置在一个数据库服务器上。一个数据库服务器可以处理一个或多个shard的数据。系统中需要有服务器进行查询路由转发，负责将查询转发到包含该查询所访问数据的shard或shards节点上去执行。

mycat

github地址：https://github.com/MyCATApache/Mycat-Server

基于cobar改造，proxy层方案，支持的功能非常完善，社区活跃。但相比sharding jdbc年轻一些。

proxy层方案的缺点：
需要部署，自己及运维一套中间件，运维成本高，但是好处在于对于各个项目是透明的，如果遇到升级之类的都是自己中间件那里搞就行了。

数据库拆分

1 水平拆分

把一个表的数据给弄到多个库的多个表里，但每个库的表结构都一样，只不过每个库中表放的数据不同，所有库表的数据加起来就是全部数据。关注点在数据的特点。

水平拆分的意义

将数据均匀放更多的库，然后用多个库抗更高并发
多个库的存储进行扩容

2 垂直拆分（拆库）
解决问题
1)服务不能复用
2)连接数不够
将一个数据库，拆分成多个提供不同业务数据处理能力的数据库，关注点在于业务相关性。

例如拆分所有订单的数据和产品的数据，变成两个独立的库，数据结构发生了变化，SQL 和关联关系也必随之改变。
原来一个复杂 SQL 直接把一批订单和相关的产品都查了出来，现在得改写 SQL 和程序。

先查询订单库数据，拿到这批订单对应的所有产品 id
再根据产品 id list去产品库查询所有产品信息
最后再业务代码里进行组装把一个有很多字段的表给拆分成多个表或库
每个库表的结构都不一样，每个库表都包含部分字段。

一般将较少的访问频率很高的字段放到一个表，然后将较多的访问频率很低的字段放到另外一个表。
因为数据库有缓存，访问频率高的行字段越少，可在缓存里缓存更多行，性能就越好。这个一般在表这个层面做的较多。

注：现有中间件都可实现分库分表后，根据你指定的某个字段值，比如userid，自动路由到对应库，然后再自动路由到对应表。

分库分表的方式

1）按range分
就是每个库一段连续的数据，一般按比如时间范围来的，但是这种一般较少用，因为很容易产生热点问题，大量的流量都打在最新的数据上了

好处
后面扩容的时候，就很容易，因为你只要预备好，给每个月都准备一个库就可以了，到了一个新的月份的时候，自然而然，就会写新的库了

缺点
但是大部分的请求，都是访问最新的数据。实际生产用range，要看场景，你的用户不是仅仅访问最新的数据，而是均匀的访问现在的数据以及历史的数据

2）按某字段hash

均匀分散，最为常用。

好处
可以平均分配没给库的数据量和请求压力
坏处
扩容起来比较麻烦，会有一个数据迁移的过程

分库分表后的难题
1 全局唯一ID生成方案
1）数据库自增ID
使用

auto_increment_increment
auto_increment_offset
系统变量让MySQL以期望的值和偏移量来增加auto_increment列的值。

优点
最简单，不依赖于某节点，较普遍采用，但需要非常仔细的配置服务器哦！
缺点
单点风险、单机性能瓶颈。不适用于一个节点包含多个分区表的场景。

2）数据库集群并设置相应步长（Flickr方案）

在一个全局数据库节点中创建一个包含auto_increment列的表，应用通过该表生成唯一数字。

优点
高可用、ID较简洁。
缺点
需要单独的数据库集群。

3）Redis缓存

避免了MySQL性能低的问题。

4）Snowflake（雪花算法）

优点
高性能高可用、易拓展
缺点
需要独立的集群以及ZK

5)各种GUID、Random算法

优点
简单
缺点
生成ID较长，且有重复几率

5)业务字段

为减少运营成本并减少额外风险，排除所有需要独立集群的方案，采用了带有业务属性的方案：时间戳+用户标识码+随机数

优点
方便、成本低
基本无重复的可能
自带分库规则，这里的用户标识码即为userID的后四位，在查询场景，只需订单号即可匹配到相应库表而无需用户ID，只取四位是希望订单号尽可能短，评估后四位已足。
可排序，因为时间戳在最前
缺点
长度稍长，性能要比int/bigint的稍差。

2,事务
分库分表后，由于数据存到了不同库，数据库事务管理出现困难。如果依赖数据库本身的分布式事务管理功能去执行事务，将付出高昂的性能代价；如果由应用程序去协助控制，形成程序逻辑上的事务，又会造成编程方面的负担。

解决方案
比如美团，是将整个订单领域聚合体切分，维度一致，所以对聚合体的事务是支持的。
3,数据库特性

多表的 join 在单库时可通过一个 SQL 完成，但拆分到多个数据库后就无法跨库执行 SQL，好在 join 语法一般都被禁止使用，都是把两个表的数据取出后在业务代码里做筛选。
分库分表后，难免会将原本逻辑关联性很强的数据划分到不同的表、不同的库上，这时，表的关联操作将受到限制，我们无法join位于不同分库的表，也无法join分表粒度不同的表，结果原本一次询能够完成的业务，可能需要多次查询才能完成。

解决方案
垂直切分后，就跟join说拜拜了；水平切分后，查询的条件一定要在切分的维度内。
比如查询具体某个用户下的订单等；
禁止不带切分的维度的查询，即使中间件可以支持这种查询，可以在内存中组装，但是这种需求往往不应该在在线库查询，或者可以通过其他方法转换到切分的维度来实现。
在未分库分表前，查询数据总数时只需 SQL 执行 count()，现在数据被分散到多个库表，就要考虑其他方案，比方说将计数的数据单独存储在一张表或记录在 Redis。
4,额外的数据管理和运算压力

额外的数据管理负担，最显而易见的就是数据的定位问题和数据的增删改查的重复执行问题，这些都可以通过应用程序解决，但必然引起额外的逻辑运算。
例如，对于一个记录用户成绩的用户数据表userTable，业务要求查出成绩最好的100位，在进行分表前，只需一个order by即可。但分表后，将需要n个order by语句，分别查出每一个分表前100名用户数据，然后再对这些数据进行合并计算，才能得出结果。

posted on 2018-11-27 16:48 让代码飞阅读(138) 评论(0) 收藏举报

刷新页面返回顶部