Mysql

MySQL数据库优化

1.1.1 慢查询日志

当查询超过一定的时间没有返回结果的时候，才会记录到慢查询日志中。默认不开启。采样的时候手工开启。可以帮助我们找出执行慢的SQL 语句

查看慢 SQL 日志是否启用（on || 1 表示启用）: show variables like ‘slow_query_log’;

查看执行慢于多少秒的 SQL 会记录到日志文件中 ：show variables like ‘long_query_time’;

修改 mysql 配置参数（方法1）

my.ini（Linux 下文件名为 my.cnf），查找到[mysqld]区段，增加日志的配置。

步骤：

Win+R运行 打开服务，查找mysql

常用的参数详解：可直接赋值到my.cnf里

–是否开启慢查询日志

slow_query_log=1

–指定保存路径及文件名，默认为数据文件目录，（生成的文件路径 在my.ini 中使用ctrl+f 可查找 datadir）

slow_query_log_file=“bxg_mysql_slow.log” # --指定多少秒返回查询的结果为慢查询

long_query_time=1

–记录所有没有使用到索引的查询语句

log_queries_not_using_indexes=1

–记录那些由于查找了多于 1000 次而引发的慢查询

min_examined_row_limit=1000

–记录那些慢的 optimize table，analyze table 和 alter table 语句

log_slow_admin_statements=1

–记录由 Slave 所产生的慢查询

log_slow_slave_statements=1

修改 mysql 配置参数（方法2）

命令行修改配置方式不需要不重启即可生效，但重启之后会自动失效。 

set global slow_query_log=1;

set global slow_query_log_file=‘bxg_mysql_slow.log’;

set long_query_time=1;

set global log_queries_not_using_indexes=1;

set global min_examined_row_limit=1000;

set global log_slow_admin_statements=1;

set global log_slow_slave_statements=1;

1.14 慢日志格式

时间、主机信息、执行信息、执行时间、执行内容 

1.15 查询缓存

Query Cache 会缓存 select 查询，安装时默认是开启的，但是如果对表进行 INSERT, UPDATE, DELETE, TRUNCATE, ALTER TABLE, DROP TABLE, or DROP DATABASE 等操作 时，之前的缓存会无效并且删除。这样一定程度上也会影响我们数据库的性能。所以对一些频繁的变动表的情况开启缓存是不明智的。还有一种情况我们测试数据库性能的时候也要关闭缓存，避免缓存对我们测试数据的影响。

eg: 性能调优的时候应该关闭查询缓存，会影响多次查询时间对比

1.2.2 EXPLAN执行计划
1.2.2.1.用法

1. EXPLAIN SELECT …… 分析select语句 显示mysql server执行select语句是按照什么方式执行的
   比如按照什么循序，查哪些表，对sql语句做了哪些分解
   
   a.Using index：该值表示相应的 select 操作中使用了覆盖索引（Covering Index）。 MySQL 可以利用索引返回 select 列表中的字段，而不必根据索引再次读取数据文件包含 所有满足查询需要的数据的索引称为覆盖索引（Covering Index）。注意：如果要使用覆 盖索引，一定要注意 select 列表中只取出需要的列，不可 select *，因为如果将所有字段 一起做索引会导致索引文件过大，查询性能下降。

1.2.3 Profiling 的使用

要想优化一条 Query，就须要清楚这条 Query 的性能瓶颈到底在哪里，是消耗的 CPU 计算太多，还是需要的 IO 操作太多？要想能够清楚地了解这些信息，可以通过 Query Profiler 功能得到 

Query Profiler 是 MYSQL 自带的一种 query 诊断分析工具，通过它可以分析出一条 SQL 语句的性能瓶颈在什么地方。 

用法
（1）通过执行“set profiling”命令，可以开启关闭 QueryProfiler 功能 mysql> SET global profiling=on;

（2）查看相关变量 show VARIABLES like ‘%profiling%’;

（3）设置保存数量默认 15 条，最大值为 100 mysql> set profiling_history_size=100;

（4）在开启 Query Profiler 功能之后，MySQL 就会自动记录所有执行的 Query 的 profile 信息，下面执行 n 条 Query 作为测试 select * from person limit 10000,100;

（3）获取当前系统中保存的多个 Query 的 profile 的概要信息 mysql> show profiles;

（4）针对单个 Query 获取详细的 profile 信息。 可以根据概要信息中的 Query_ID 来获取某个 Query 在执行过程中详细的 profile 信 息。

	例如查看 cpu 和 io 的详细信息 show profile cpu,block io for query 501; 

1.2.7 数据库连接进程列表

show processlist；(在info 中可看出进程是否死锁等)

1.3.1 引擎介绍

MySQL 中的数据用各种不同的技术存储在文件（或者内存）中。这些技术中的每一 种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能 力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。

这些不同的技术以及配套的相关功能在 MySQL 中被称作存储引擎(也称作表类型)。 MySQL 默认配置了许多不同的存储引擎，可以预先设置或者在 MySQL 服务器中启用。你 可以选择适用于服务器、数据库和表格的存储引擎，以便在选择如何存储你的信息、如何 检索这些信息以及你需要你的数据结合什么性能和功能的时候为你提供最大的灵活性。

1.3.1.2. 引擎作用

1）设计并创建数据库以保存系统所需的关系

2）实现系统以访问和更改数据库中存储的数据。

3）提供日常管理支持以优化数据库的性能。

1.3.7. Innodb 对比 myIsam

1.3.7.1. 事务

MyISAM 类型不支持事务处理等高级处理，而 InnoDB 类型支持，提供事务支持已经 外部键等高级数据库功能。

InnoDB 表的行锁也不是绝对的，假如在执行一个 SQL 语句时 MySQL 不能确定要扫 描的范围，InnoDB 表同样会锁全表，例如 update table set num=1 where name like “a%”， 就是说在不确定的范围时，InnoDB 还是会锁表的。

1.3.7.2. 性能

以前版本中 MyISAM 类型的表强调的是性能，其执行数度比 InnoDB 类型更快。但现 在 InnoDB 在多方面的性能已经赶上活超过了 MyIsam。

1.3.7.3. 行数保存

InnoDB 中不保存表的具体行数，也就是说，执行 select count() from table 时，InnoDB 要扫描一遍整个表来计算有多少行，但是 MyISAM 只要简单的读出保存好的行数即可。注 意的是，当 count()语句包含 where 条件时，两种表的操作是一样的。

1.3.7.6. 锁的支持

MyISAM 只支持表锁。InnoDB 支持表锁、行锁 行锁大幅度提高了多用户并发操作的 新能。但是 InnoDB 的行锁，只是在 WHERE 的主键是有效的，非主键的 WHERE 都会锁 全表的。

1.3.6. Blackhole（黑洞引擎）

任何写入到此引擎的数据均会被丢弃掉， 不做实际存储；Select 语句的内容永远是 空。他会丢弃所有的插入的数据，服务器会记录下 Blackhole 表的日志，所以可以用于复制数据到备份数据库。

使用场景
可与用于 dump file 语法的正确性

1.4.4. 大数据量的分页优化

使用 limit 进行分页，翻到 10000 多页后效率低。原因在于 limit offset 会逐行查找， 是先查询再跳过。

使用 limit 进行分页，翻到 10000 多页后效率低。原因在于 limit offset 会逐行查找， 是先查询再跳过。

1.4.4.2. 技术优化方法一
select * from person where id>999900 limit 100;

但这样用有前提，id 是连续的，中间的数据不能删，否则 id 为 999900 的并不是第 999900 个记录。

1.4.4.3. 技术优化方法二

如果必须用 limit offset 查询，就用延迟关联

select id from person limit 999900 ,100;

这样只查询 id 列，实现了索引覆盖，就会很快

select p.* from person p inner join (select id from person limit 999900 ,100) as tmp on p.id=tmp.id;

1.5. 索引优化

B+Tree 中的 B 是指 balance，意为平衡。需要注意的是，B+树索引并不能找到一个 给定键值的具体行，它找到的只是被查找数据行所在的页，接着数据库会把页读入到内存， 再在内存中进行查找，最后得到要查找的数据。 

sql 语句优化

1.4.1.1. 避免 SELECT *

从数据库里读出越多的数据，那么查询就会变得越慢，这还会增加网络传输的负载。

1.4.1.2. 避免在 where 子句中使用!=或<>操作符

应尽量避免在 where 子句中使用!=或<>操作符，否则引擎放弃使用索引而进行全表 扫描。

1.4.1.3. 尽量避免全表扫描

对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的 列上建立索引。

1.4.1.4. 用 UNION 来代替 OR

采用 OR 语句：

select * from person where fname =‘LVc1oJjd’ or fname=‘bjRdlVo’;

采用 UNION 语句，返回的结果同上面的一样，但是速度要快些：

select * from person where fname ='LVc1oJjd‘

Union

select * from person where fname=‘bjRdlVo’;

1.4.1.5. like 语句避免前置百分号
前置百分号会导致索引失效

1.4.1.7. 避免在 where 子句中对字段进行表达式操作

应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而 进行全表扫描。如：

select id from t where num/2=100

应改为:

select id from t where num=100*2

**1.4.1.10. 用 exists 代替 in

1.4.1.11. 尽量使用数字型字段

尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询 和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会 逐个比较字符 串中每一个字符，而对于数字型而言只需要比较一次就够了。

1.4.1.12. 使用 varchar/nvarchar 代替 char/nchar

尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小， 可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

1.4.1.16. 避免向客户端返回大数据量

尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

1.4.1.17. 避免在 where 子句中对字段进行 null

应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索 引而进行全表扫描。如：

select id from t where num is null

可以在 num 上设置默认值 0，确保表中 num 列没有 null 值，然后这样查询：

select id from t where num=0

在 Mysql5.7 版本中该条建议已经不用考虑了，因为 null 判断也能使用索引了。