Mysql优化

优化由谁参与?

在进行数据库优化时，应由数据库管理员、业务部门代表、应用程序架构师、应用程序设计人员、应用 程序开发人员、硬件及系统管理员、存储管理员等，业务相关人员共同参与。

优化的思路

• 优化的方向
  在数据库优化上有两个主要方向：即安全与性能。
  安全 ---> 数据安全性
  性能 ---> 数据的高性能访问

mysql优化方向:

我们把数据库优化分为四个纬度：硬件，系统配置，数据库表结构，SQL及索引,成本由高到低,

• 硬件： CPU、内存、存储、网络设备等
• 系统配置： 服务器系统、数据库服务参数等
• 数据库表结构： 高可用、分库分表、读写分离、存储引擎、表设计等
• Sql及索引： sql语句、索引使用等
• 从优化成本进行考虑：硬件>系统配置>数据库表结构>SQL及索引 北
• 从优化效果进行考虑：硬件<系统配置<数据库表结构<SQL及索引

1.3.3 优化的工具

msyqladmin #mysql客户端，可进行管理操作 mysqlshow #功能强大的查看shell命令 show [SESSION | GLOBAL] variables #查看数据库参数信息 SHOW [SESSION | GLOBAL] STATUS #查看数据库的状态信息 SHOW ENGINE INNODB STATUS Innodb #引擎的所有状态 information_schema #获取元数据的方法 SHOW PROCESSLIST #查看当前所有连接session状态 explain #获取查询语句的执行计划 how index #查看表的索引信息 slow-log #记录慢查询语句 mysqldumpslow #分析slowlog文件的
不常用但好用的工具
zabbix #监控主机、系统、数据库（部署zabbix监控平台） mysqlslap #分析慢日志 sysbench #压力测试工具 workbench #管理、备份、监控、分析、优化工具（比较费资源） pt-query-digest #分析慢日志 mysql profiling #统计数据库整体状态工具 Performance Schema mysql #性能状态统计的数据

数据库调优方案:

• 应急调优的思路：
  针对突然的业务办理卡顿，无法进行正常的业务处理！需要立马解决的场景！

1. show processlist（查看链接session状态）
2. explain(分析查询计划)，show index from table（分析索引）
3. 通过执行计划判断，索引问题（有没有、合不合理）或者语句本身问题
4. show status like '%lock%'; # 查询锁状态
5. SESSION_ID; # 杀掉有问题的session

• 常规调优的思路：
  针对业务周期性的卡顿，例如在每天10-11点业务特别慢，但是还能够使用，过了这段时间就好了。

1. 查看slowlog，分析slowlog，分析出查询慢的语句。
2. 按照一定优先级，进行一个一个的排查所有慢语句。
3. 分析top sql，进行explain调试，查看语句执行时间。
4. 调整索引或语句本身。

1.2SQL语句优化

• 尽量少用数据库自带的函数
• 连续条件用Between ..and 不用in
• update语句 如果只改1,2个字段不要update全部字段消耗性能
• 尽量使用数字类型,若只含数值信息字段不要涉及为字符型
• 不建议经常用select * from ,不要返回用不到的字段
• 不带条件的count会全表扫描

2 查询优化

2.1 MySQL查询流程

数据库查询的流程:
① 客户端将查询发送到服务器；
② 服务器检查查询缓存，如果找到了，就从缓存中返回结果，否则进行下一步。
③ 服务器解析，预处理。
④ 查询优化器优化查询
⑤ 生成执行计划，执行引擎调用存储引擎API执行查询
⑥服务器将结果发送回客户端。

mysql.png

查询缓存 在解析一个查询语句之前，如果查询缓存是打开的，那么MySQL会优先检查这个查询是否命 中查询缓存中的数据，如果命中缓存直接从缓存中拿到结果并返回给客户端。这种情况下，查询不会被 解析，不用生成执行计划，不会被执行。
语法解析和预处理器 MySQL通过关键字将SQL语句进行解析，并生成一棵对应的“解析树”。MySQL解 析器将使用MySQL语法规则验证和解析查询。
查询优化器 语法书被校验合法后由优化器转成查询计划，一条语句可以有很多种执行方式，后返回相 同的结果。优化器的作用就是找到这其中好的执行计划。
查询执行引擎 在解析和优化阶段，MySQL将生成查询对应的执行计划，MySQL的查询执行引擎则根据 这个执行计划来完成整个查询。常使用的也是比较多的引擎是MyISAM引擎和InnoDB引擎。 mysql5.5开始的默认存储引擎已经变更为innodb了

2.2 查询优化

• EXPLAIN 分析查询，通过定位分析性能的瓶颈，才能更好的优化数 据库系统的性能。

EXPLAIN SELECT * FROM t_table WHERE score = 60

2.2.1 慢查询

• 慢查询日志开启
  在配置文件my.cnf或my.ini中在[mysqld]一行下面加入两个配置参数

log-slow-queries=/data/mysqldata/slow-query.log 
long_query_time=5

log-slow-queries参数为慢查询日志存放的位置，一般这个目录要有mysql的运行帐号的可写权限，一 般都将这个目录设置为mysql的数据存放目录；
long_query_time=5中的5表示查询超过五秒才记录；
还可以在my.cnf或者my.ini中添加log-queries-not-using-indexes参数，表示记录下没有使用索引的查 询。
慢查询分析
我们可以通过打开log文件查看得知哪些SQL执行效率低下 ，从日志中，可以发现查询时间超过5 秒的 SQL，而小于5秒的没有出现在此日志中。
如果慢查询日志中记录内容很多，可以使用mysqldumpslow工具（MySQL客户端安装自带）来对慢查 询日志进行分类汇总。mysqldumpslow对日志文件进行了分类汇总，显示汇总后摘要结果。
2.2.2 EXPLAIN

• EXPLAIN可以帮助开发人员分析SQL问题，EXPLAIN显示了MySQL如何使用使用SQL执行计划，可以帮 助开发人员写出更优化的查询语句。使用方法，在select语句前加上Explain就可以了：
  EXPLAIN SELECT * FROM products

3.0索引优化

CREATE TABLE `tb_table` (  `id` int(11) NOT NULL 
AUTO_INCREMENT COMMENT '主键',  `name` varchar(20) 
DEFAULT NULL COMMENT '姓名',  `number` int(11) DEFAULT 
NULL COMMENT '编号',  PRIMARY KEY (`id`), 
 KEY `number` (`number`) ) //这里就是给number添加了索引
 ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT 
CHARSET=utf8;


drop procedure if exists tb_insert; CREATE PROCEDURE 
tb_insert() BEGIN DECLARE i INT; SET i = 0; START 
TRANSACTION; WHILE i < 10000 DO -- 然后再添加数据库的数据，插入100万条，再次测试有索引和没有索引的查询语句。    
INSERT INTO tb_table (`name`,`number`) VALUES (concat("张
三",i),i);    SET i = i+1; END WHILE; COMMIT; END;
call tb_insert();

SELECT * FROM tb_table WHERE number = 500000
通过上面的对比测试可以看出，索引是快速搜索的关键。MySQL索引的建立对于MySQL的高效运行是 很重要的。对于少量的数据，没有合适的索引影响不是很大，但是，当随着数据量的增加，性能会急剧 下降。

3.2 索引的类型

• 1. 主键索引 PRIMARY KEY
     它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引。
     PRIMARY KEY (id)
• 2. 唯一索引 UNIQUE 唯一索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。可以在创建 表的时候指定，也可以修改表结构。
     UNIQUE KEY num (number) USING BTREE
• 3. 普通索引 INDEX 这是基本的索引，它没有任何限制。可以在创建表的时候指定，也可以修改表结构
     KEY num (number) USING BTREE
• 4. 组合索引 INDEX 索引分单列索引和组合索引(联合索引)。单列索引，即一个索引只包含单个列，一个表可以有多个 单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。
     KEY num (number,name) USING BTREE
     注意，组合索引前面索引必须要先使用，后面的索引才能使用。要么就一起用
• 5. 全文索引 FULLTEXT
     SELECT * FROM tb_table WHERE number = 500000
     PRIMARY KEY (id)
     UNIQUE KEY num (number) USING BTREE
     KEY num (number) USING BTREE
     KEY num (number,name) USING BTREE
     全文索引（也称全文检索）是目前搜索引擎使用的一种关键技术。它能够利用分词技术等多种算法 智能分析出文本文字中关键字词的频率及重要性，然后按照一定的算法规则智能地筛选出我们想要 的搜索结果。

3.3 失效索引

• 不要在where字句中对字段null进行null值判断,否则会引起引擎放弃使用索引,全表扫描
• 避免在where字句中使用!= 或<>操作符,否则会引起引擎放弃使用索引,全表扫描
• 避免在where字句中用or来连接条件,如果一个字段没有索引,将导致放弃使用索引,全表扫描
• 不做列运算
• 查询like,如果%aaa 不会走索引

mysql的分库分表

当一个表数据太过庞大,可以进行分库分表

• 垂直切分和水平切分
  垂直切分: 将表按功能模块,关系密切程度划分,部署到不同的库
  水平 切分

使用缓存

redis和ehche
场景: 短时间内查询多次且数据更新不频繁,这个时候可以优先从缓存查询,查询不到在从数据库中查询,存入缓存.适用于单机缓存