对于MySql的一些梳理之优化篇

MySQL调优三步

---

1. 慢查询 （分析出现出问题的sql）
2. Explain （显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句）
3. Profile（查询到 SQL 会执行多少时间, 并看出 CPU/Memory 使用量, 执行过程中 Systemlock, Table lock 花多少时间等等.）

慢查询日志（定位执行效率较低的SQL语句）：

　　MySQL 提供了慢查询日志。这个日志会记录所有执行时间超过 long_query_time（默认是 10s）的 SQL 及相关的信息。

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
row in set
mysql> show variables like 'slow_query%';
+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| slow_query_log      | OFF                                  |
| slow_query_log_file | /var/log/mysql/log-slow-queries.log  |
+---------------------+--------------------------------------+
rows in set

　 slow_query_log 指的是慢查询日志是否开启。
    slow_query_log_file 指明了日志所在的位置。

开启方法：
       修改配置文件my.cnf或my.ini中的相应参数。
       或者用set在命令行设置相应的变量

在开启了 MySQL 慢查询日志一段时间之后，日志中就会把所有超过 long_query_time 的 SQL 记录下来。
另一个有用的相关 MySQL 命令是 mysqldumpslow：由于慢查询日志可能很大或者很难分析，使用它可以获得 MySQL 对慢查询日志的一个总结报告，直接获得我们想要的统计分析后的结果。

Show命令：

　　我们可以通过show命令查看MySQL状态及变量，找到系统的瓶颈：

　　Mysql> show status ——显示状态信息（扩展show status like ‘XXX’），了解各种SQL的执行频率

　　Mysql> show variables ——显示系统变量（扩展show variables like ‘XXX’）

　　Mysql> show innodb status ——显示InnoDB存储引擎的状态

　　Mysql> show processlist ——查看当前SQL执行，包括执行状态、是否锁表等

通过 Explain 分析低效 SQL 的执行计划

查询到效率低的 SQL 语句后，可以通过 EXPLAIN 获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序

mysql> explain select * from user_info where id = 2

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_info
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
row in set, 1 warning (0.00 sec)

profiling分析查询：

　　通过慢日志查询可以知道哪些SQL语句执行效率低下，通过explain我们可以得知SQL语句的具体执行情况，索引使用等，还可以结合show命令查看执行状态。
       如果觉得explain的信息不够详细，可以通过profiling命令得到更准确的SQL执行消耗系统资源的信息。

---

有没有做过数据库优化方面的事情

做过mysql数据库的优化、其他数据库类似

定位：查找、定位慢查询

优化手段：

1. 创建索引:创建合适的索引，我们就可以现在索引中查询，查询到以后直接找对应的记录。
2. 分表   ：当一张表的数据比较多或者一张表的某些字段的值比较多并且很少使用时，采用水平分表和垂直分表来优化
3. 读写分离：当一台服务器不能满足需求时，采用读写分离的方式进行集群。
4. 缓存:使用redis来进行缓存
5. 一些常用优化技巧

---

数据库优化之遵循范式

 数据库表设计时需要遵循方式

 表的范式，是首先符合1NF, 才能满足2NF , 进一步满足3NF

1NF: 即表的列的具有原子性,不可再分解，即列的信息，不能分解.只要数据库是关系型数据库(mysql/oracle/db2/sysbase/sql server)，就自动的满足1NF.关系型数据库中是不允许分割列的。

2NF:表中的记录是唯一的.通常我们设计一个主键来实现

3NF:即表中不要有冗余数据, 就是说，表的信息，如果能够被推导出来，就不应该单独的设计一个字段来存放.(外键)

反3NF :没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据。具体做法是： 在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，允许冗余。 订单和订单项、相册浏览次数和照片的浏览次数

---

选择合适的存储引擎

在开发中，我们经常使用的存储引擎 myisam / innodb/ memory

MyISAM存储引擎
如果表对事务要求不高，同时是以查询和添加为主的，我们考虑使用myisam存储引擎. 比如 bbs 中的 发帖表，回复表.

INNODB存储引擎:
对事务要求高，保存的数据都是重要数据，我们建议使用INNODB,比如订单表，账号表.

Memory 存储
我们数据变化频繁，不需要入库，同时又频繁的查询和修改，我们考虑使用memory, 速度极快.

问 MyISAM 和 INNODB的区别(主要)

1. 事务安全 myisam不支持事务而innodb支持
2. 查询和添加速度 myisam不用支持事务就不用考虑同步锁，查找和添加和添加的速度快
3. 支持全文索引 myisam支持innodb不支持
4. 锁机制 myisam支持表锁而innodb支持行锁(事务)
5. 外键 MyISAM 不支持外键， INNODB支持外键. (通常不设置外键，通常是在程序中保证数据的一致)

---

数据库优化之创建合适的索引

索引（Index）是帮助DBMS高效获取数据的数据结构。

分类：普通索引/唯一索引/主键索引/全文索引

普通索引:允许重复的值出现

唯一索引:除了不能有重复的记录外，其它和普通索引一样(用户名、用户身份证、email,tel)

主键索引：是随着设定主键而创建的，也就是把某个列设为主键的时候，数据库就会給改列创建索引。这就是主键索引.唯一且没有null值

全文索引:用来对表中的文本域(char，varchar，text)进行索引， 全文索引针对MyIsam

explain select * from articles where match(title,body) against(‘database’);【会使用全文索引】

---

索引使用小技巧

索引弊端
1.占用磁盘空间。
2.对dml(插入、修改、删除)操作有影响，变慢。

使用场景：
a: 肯定在where条件经常使用,如果不做查询就没有意义
b: 该字段的内容不是唯一的几个值(sex)
c: 字段内容不是频繁变化.

具体技巧：

对于创建的多列索引（复合索引），不是使用的第一部分就不会使用索引。
alter table dept add index my_ind (dname,loc); // dname 左边的列,loc就是右边的列
explain select * from dept where dname='aaa'\G 会使用到索引
explain select * from dept where loc='aaa'\G 就不会使用到索引

对于使用like的查询，查询如果是’%aaa’不会使用到索引而‘aaa%’会使用到索引。
explain select * from dept where dname like '%aaa'\G不能使用索引
explain select * from dept where dname like 'aaa%'\G使用索引.
所以在like查询时，‘关键字’的最前面不能使用 % 或者 _这样的字符.，如果一定要前面有变化的值，则考虑使用 全文索引->sphinx.

如果条件中有or，有条件没有使用索引,即使其中有条件带索引也不会使用。换言之，就是要求使用的所有字段,都必须单独使用时能使用索引.

如果列类型是字符串，那一定要在条件中将数据使用引号引用起来。否则不使用索引。
expain select * from dept where dname=’111’;
expain select * from dept where dname=111;（数值自动转字符串）
expain select * from dept where dname=qqq;报错
也就是，如果列是字符串类型，无论是不是字符串数字就一定要用 ‘’ 把它包括起来.

如果mysql估计使用全表扫描要比使用索引快，则不使用索引。
表里面只有一条记录

---

数据库优化之分表

分表分为水平(按行)分表和垂直(按列)分表

根据经验，Mysql表数据一般达到百万级别，查询效率会很低，容易造成表锁，甚至堆积很多连接，直接挂掉；水平分表能够很大程度较少这些压力。
按行数据进行分表。

如果一张表中某个字段值非常多(长文本、二进制等)，而且只有在很少的情况下会查询。这时候就可以把字段多个单独放到一个表，通过外键关联起来。
考试详情，一般我们只关注分数，不关注详情。
水平分表策略：

1.按时间分表
这种分表方式有一定的局限性，当数据有较强的实效性，如微博发送记录、微信消息记录等，这种数据很少有用户会查询几个月前的数据，如就可以按月分表。

2.按区间范围分表
一般在有严格的自增id需求上，如按照user_id水平分表：
table_1  user_id从1~100w
table_2  user_id从101~200w
table_3  user_id从201~300w

3.hash分表*****
通过一个原始目标的ID或者名称通过一定的hash算法计算出数据存储表的表名，然后访问相应的表。

---

数据库优化之读写分离

一台数据库支持的最大并发连接数是有限的，如果用户并发访问太多。一台服务器满足不要要求是就可以集群处理。Mysql的集群处理技术最常用的就是读写分离。

主从同步
数据库最终会把数据持久化到磁盘，如果集群必须确保每个数据库服务器的数据是一直的。能改变数据库数据的操作都往主数据库去写，而其他的数据库从主数据库上同步数据。

读写分离
使用负载均衡来实现写的操作都往主数据去，而读的操作往从服务器去。

---

数据库优化之缓存

在持久层(dao)和数据库(db)之间添加一个缓存层，如果用户访问的数据已经缓存起来时，在用户访问时直接从缓存中获取，不用访问数据库。而缓存是在操作内存级，访问速度快。
作用：减少数据库服务器压力，减少访问时间。

Java中常用的缓存有，
1、hibernate的二级缓存。该缓存不能完成分布式缓存。
2、可以使用redis(memcahe等)来作为*缓存。对缓存的数据进行集中处理

---

语句优化小技巧

DDL优化:
1 、通过禁用索引来提供导入数据性能 。 这个操作主要针对有数据库的表，追加数据
//去除键
alter table test3 DISABLE keys;
//批量插入数据
insert into test3 select * from test;
//恢复键
alter table test3 ENABLE keys;

2、 关闭唯一校验
set unique_checks=0  关闭
set unique_checks=1  开启

3、修改事务提交方式(导入)（变多次提交为一次）
set autocommit=0   关闭
//批量插入
set autocommit=1   开启

DML优化（变多次提交为一次）       
insert into test values(1,2);
insert into test values(1,3);
insert into test values(1,4);
//合并多条为一条
insert into test values(1,2),(1,3),(1,4)

 

 

 

 

参考：

https://www.cnblogs.com/mengchunchen/p/9229731.html