MySQL 学习笔记 | 老李的笔记

SQL查询的基本执行过程

应用通过MySQL API把查询命令发送给MySQL服务器，然后被解析
检查权限、MySQL optimizer进行优化，经过解析和优化后的查询命令被编译为CPU可运行的二进制形式的查询计划（query plan），并可以被缓存
如果存在索引，那么先扫描索引，如果数据被索引覆盖，那么不需要额外的查找，如果不是，根据索引查找和读取对应的记录
如果有关联查询，查询次序是扫描第一张表找到满足条件的记录，按照第一张表和第二张表的关联键值，扫描第二张表查找满足条件的记录，按此顺序循环
输出查询结果，并记录binary logs

导出数据:

1	mysqldump -uroot -p --default-character-set=utf8 crawler > e:/crawler.sql

导入数据:

导入时出现ERROR at line 206: Unknown command '\''.的错误提示，然后中断。是字符集的问题，加上 --default-character-set=utf8。

1	mysql -uroot -p --default-character-set=utf8 crawler < e:/crawler.sql

修改客户端连接编码:

1	SET NAMES 'utf8';

查看最大连接数

1	show variables like '%max_connections%'

查看已使用最大连接数

1	show global status like 'Max_used_connections';

使用慢查询记录查询慢的 SQL

1
2
3

slow_query_log              -- 慢查询开启状态
slow_query_log_file         -- 慢查询日志存放的位置(一般设置为MySQL的数据存放目录)
long_query_time             -- 查询超过多少秒才记录

查看慢查询是否开启

1	show variables like 'slow_query%'; -- slow_query_log 为 ON 表示开启了， OFF 表示未开启

开启慢查询（重启服务生效）
方法一：

1
2
3

set global slow_query_log='ON';
set global slow_query_log_file='/usr/local/mysql/data/slow.log';
set global long_query_time=1;

方法二：修改配置文件，在 [mysqld] 下加入

1
2
3

slow_query_log = ON
slow_query_log_file = /usr/local/mysql/data/slow.log
long_query_time = 1

其中 query_time 表示 query 语句的执行时间，但是为秒， lock time 是锁定的时间， rows_sent 是 query 语句执行返回的记录数，而 rows_examined 则是优化器估算的扫描行数

使用 Query Profiler 分析sql 性能

Query Profiler 是 MYSQL 自带的一种 query 诊断分析工具，通过它可以分析出一条 SQL 语句的性能瓶颈在什么地方。Query Profiler 是 5.0.37 之后添加的，要想使用此功能，要确保版本在 5.0.37 之后。

查看数据库版本

1	select version();

查看 profiles 功能是否开启

方法一：

1	show profiles; -- 返回 Empty set, 1 warning (0.00 sec) ，说明没有开启。

方法二：

1	select @@profiling; -- 结果为 1 说明已开启

方法三：

1	show variables like "profiling%"; -- 结果为 On 说明已开启

开启当前 session 会话 Query Profiler 功能

1	set profiling=1;

根据 query_id 查看某个查询的详细时间耗费

1	show profile for query query_id;

查看占用cpu、 io等信息

1	show profile block io, cpu for query query_id; -- 另外还可以看到 memory,swaps,context switches,source 等信息

使用 EXPLAIN 查看 SQL 的执行计划

1	mysql> explain select * from htmlentity;

用 EXISTS 替换 DISTINCT：当提交一个包含一对多表信息（比如部门表和雇员表）的查询时，避免在 SELECT 子句中使用 DISTINCT ，一般可以考虑用 EXIST 替换， EXISTS 使查询更为迅速，因为RDBMS核心模块将在子查询的条件一旦满足后，立刻返回结果。

EXPLAIN 参数说明

SELECT_TYPE

SIMPLE : 简单 SELECT (不使用UNION或子查询等)
PRIMARY : 最外层的 SELECT
UNION : UNION 中的第二个或后面的 SELECT 语句
DEPENDENT UNION : UNION 中的第二个或后面的SELECT语句，取决于外面的查询
UNION RESULT : UNION 的结果(此时 ID 为 NULL)
SUBQUERY : 子查询中的第一个 SELECT
DEPENDENT SUBQUERY : 子查询中的第一个 SELECT ，取决于外面的查询
DERIVED : 派生表的 SELECT ( FROM 子句的子查询)

TYPE

SYSTEM : CONST的特例，当表上只有一条元组匹配
CONST : WHERE条件筛选后表上至多有一条元组匹配时，比如WHERE ID = 2 （ID是主键，值为2的要么有一条要么没有）
EQ_REF : 参与连接运算的表是内表（在代码实现的算法中，两表连接时作为循环中的内循环遍历的对象，这样的表称为内表）。基于索引（连接字段上存在唯一索引或者主键索引，且操作符必须是“=”，索引值不能为NULL）做扫描，使得对外表的一条元组，内表只有唯一一条元组与之对应。
REF : 可以用于单表扫描或者连接(参与连接运算的表是内表)，基于索引（连接字段上的索引是非唯一索引，操作符必须是“=”，连接字段值不可为NULL）做扫描，使得对外表的一条元组，内表可有若干条元组与之对应。
FULLTEXT : FULL TEXT，全文检索
REF_OR_NULL : 类似REF，只是搜索条件包括：连接字段的值可以为NULL的情况，比如 where col = 2 or col is null
INDEX_MERGE : 多重范围扫描。两表连接的每个表的连接字段上均有索引存在且索引有序，结果合并在一起
UNIQUE_SUBQUERY : 在子查询中，基于唯一索引进行扫描，类似于EQ_REF
INDEX_SUBQUERY : 在子查询中，基于除唯一索引之外的索引进行扫描
RANGE : 范围扫描，基于索引做范围扫描，为诸如BETWEEN，IN，>=，LIKE类操作提供支持
INDEX : 索引做扫描，是基于索引在索引的叶子节点上找满足条件的数据（不需要访问数据文件）
ALL : 全表扫描或者范围扫描：不使用索引，顺序扫描，直接读取表上的数据（访问数据文件）

KEY_LEN

一般地，key_len 等于索引列类型字节长度，例如int类型为 4-bytes ， bigint 为 8-bytes
如果是字符串类型，还需要同时考虑字符集因素，例如：CHAR(30) UTF8则key_len至少是 90-bytes
若该列类型定义时允许 NULL ，其 key_len 还需要再加 1-bytes
若该列类型为变长类型，例如 VARCHAR（ TEXT\BLOB 不允许整列创建索引，如果创建部分索引，也被视为动态列类型），其 key_len 还需要再加 2-bytes

EXTRA

USING WHERE : 使用了WHERE子句来过滤元组
USING TEMPORARY : 用临时表保存中间结果，常用于 GROUP BY 和 ORDER BY 操作中
USING FILESORT : 使用文件完成排序操作，这是可能是 ORDERY BY ， GROUP BY 语句的结果
USING INDEX : 不需要读取数据文件，从索引树（索引文件）中即可获得信息
DISTINCT : 优化 DISTINCT 操作，在找到第一匹配的元组后即停止找同样值的动作

修改自增值从 1 开始

1	ALTER TABLE table_name AUTO_INCREMENT = 1;

查询库占用大小

1	SELECT (sum(DATA_LENGTH)+sum(INDEX_LENGTH))/1048576 FROM information_schema.TABLES where TABLE_SCHEMA='knowledge';