Mysql学习笔记

慢查询

1 慢查询优化思路

当发生慢查询的时候，优化的思路为：

• 利用慢查询日志定位慢查询 SQL
• 通过 explain 分析慢查询 SQL
• 修改 SQL，尽量让 SQL 走索引

2 慢查询日志

MySQL 提供了一个功能——慢查询日志，会记录查询时间超过指定时间阈值的 SQL 到日志中，便于我们定位慢查询并且优化对应的 SQL 语句。

首先查看 MySQL 中关于慢查询相关的全局变量：

mysql> show global variables like '%quer%';
+----------------------------------------+-------------------------------+
| Variable_name                          | Value                         |
+----------------------------------------+-------------------------------+
| binlog_rows_query_log_events           | OFF                           |
| ft_query_expansion_limit               | 20                            |
| have_query_cache                       | YES                           |
| log_queries_not_using_indexes          | OFF                           |
| log_throttle_queries_not_using_indexes | 0                             |
==========================================================================
| long_query_time                        | 10.000000                     |【1】慢查询的时间阈值
==========================================================================
| query_alloc_block_size                 | 8192                          |
| query_cache_limit                      | 1048576                       |
| query_cache_min_res_unit               | 4096                          |
| query_cache_size                       | 16777216                      |
| query_cache_type                       | OFF                           |
| query_cache_wlock_invalidate           | OFF                           |
| query_prealloc_size                    | 8192                          |
==========================================================================
| slow_query_log                         | OFF                           |【2】慢查询日志是否开启
| slow_query_log_file                    | /var/lib/mysql/Linux-slow.log |【3】慢查询日志文件存储位置
==========================================================================
+----------------------------------------+-------------------------------+
15 rows in set (0.00 sec)

这里主要关注三个变量：

• long_query_time，慢查询的时间阈值，单位秒，如果一个 SQL 语句的执行时间超过这个值，那么 MySQL 就认定其为慢查询
• slow_query_log，慢查询日志功能是否开启，默认关闭，开启后记录慢查询
• slow_query_log_file，慢查询日志文件的存储位置

默认慢查询日志功能是关闭的，因此我们需要启动该功能

# 开启慢查询日志
mysql> set global slow_query_log=ON;
Query OK, 0 rows affected (0.00 sec)
# 设置慢查询时间阈值
mysql> set long_query_time=1;
Query OK, 0 rows affected (0.00 sec)

这样子设置后，MySQL 重启会丢失这些配置，需要在配置文件中修改才会永久有效。

3 explain

我们可以使用 explain 分析 SQL 语句的执行情况，例如：

mysql> explain select sum(1+2);

执行结果如下，可以看到有很多字段

• select_type 表示查询语句的查询类型，包括简单查询、子查询等等
• table 表示查询的表，不一定是存在表，可能是本次查询中得到的临时表
• type 表示检索类型，使用全表扫描、还是索引扫描等
• possible_keys表示可能使用的索引列
• keys表示查询中实际使用的索引列，由查询优化器决定

大表加字段的方案

MySQL的大表增加字段是一个比较经常遇到的问题，当我们需要在已经存在的大表中添加一个或多个字段时，这个过程可能会非常耗时，数据量越大，所需的时间也会越长。在这里，我们提供几种方案来提高这个过程的效率：

1.使用ALTER TABLE语句添加字段

ALTER TABLE table_name ADD COLUMN column_name data_type;

这是最常用的方式，但是在大表中添加字段可能需要一定的时间。特别是在一个非常大的表中添加新列时，可能会需要一些时间来进行这个过程。另外，这种方式还需要考虑锁表，容易造成生产环境的中断，因此需要谨慎操作。

2.使用pt-online-schema-change工具

pt-online-schema-change --alter "ADD COLUMN column_name data_type" D=database,t=table_name

pt-online-schema-change是一个由Percona公司开发的工具，它基于 MySQL 实现了一个在线 DDL 工具。pt-online-schema-change的主要作用是在MySQL不停机的前提下，修改表结构。通常情况下，这种方式在处理大表时方案一更加有效。

3.使用MyDumper工具导出表数据，新建表空间添加字段，再导入数据到新表

#导出数据
mydumper -h hostname -u username -p password -B dbname -q 1 -o /path/to/dumpdir
#新建表空间和添加字段
CREATE TABLE new_table (column1 type1, ..., columnN typeN, new_column_1 ..., new_column_N);
#导入数据到新表
myloader -h hostname -u username -p password -B dbname -d /path/to/dumpdir

这种方式适用于添加多个字段，或是涉及到表结构的大规模修改。将大表数据导出，新建一张表空间，并添加新的字段，然后将数据导入到新表空间中。该方案的优点是可以在导入数据的同时，进行表结构的调整。但是，在处理大规模的修改时，需要注意数据同步的问题。

数据存在则更新，不存在则插入

1.存在则更新（不影响其他字段），不存在则插入

insert into 表名(字段1,字段2) VALUES(值1,值2) ON DUPLICATE KEY UPDATE username=值3

如上语句的意思的意思是如果字段1不存在（主键，索引或者唯一条件不存在），则执行插入语句，存在则执行更新语句，该更新只更新需要的字段，不影响其他字段的值；

2.存在则更新（先删除后更新），不存在则插入

REPLACE INTO 表名(字段1,字段2) VALUES(值A,值B);

如上语句的意思的意思是如果字段1不存在（主键，索引或者唯一条件不存在），则执行插入语句，存在则执行更新语句，该更新是将该条存在的记录删除，然后再插入，所以其他的字段的值都是NULL；

3.存在则忽略，不存在插入

INSERT IGNORE INTO 表名(字段1,字段2) VALUES(值A,值B);

如上语句的意思是如果字段1不存在（主键，索引或者唯一条件不存在），则执行插入语句，如果存在，直接忽略不修改任何数据