MySQL INNODB零碎知识点

1. MySQL repair, optimize, analyze

（1）REPAIR TABLE table_name; （修复被损坏的表，能修复MYISAM索引的表，不能直接修复INNODB、MEMORY等索引表）

修复步骤：

首先CHECK TABLE table_name;检查是否发生了表损坏

如果表损坏可以使用REPAIR TABLE table_name;直接修复MYISAM索引表。

如果是INNODB索引表可以使用一下方式：

先将数据导出一份

ALTER TABLE table_name ENGINE=INNODB;

将数据重新导入

（2）OPTIMIZE TABLE table_name; (回收闲置的数据库空间、减少数据碎片，能整理MYISAM索引的表，不能直接整理INNODB、MEMORY等索引表)

整理步骤：

如果是INNODB索引表可以使用一下方式：

先将数据导出一份

ALTER TABLE table_name ENGINE=INNODB;

将数据重新导入

或者

ALTER TABLE table_name ENGINE=InnoDB;

ANALYZE TABLE table_name ;

（3）ANALYZE TABLE table_name; （更新统计信息，其主要目的就是解决查询查询计划不稳定的情况。SHOW INDEX FROM table_name;和SHOW TABLE STATUS;也会触发统计信息的更新）

SHOW INDEX FROM table_name;中的信息取自information_schema.`STATISTICS`表。

2. MySQL如何查看优化器优化后的SQL

(1) MySQL8.0：

EXPLAIN sql语句;

SHOW WARNINGS;  -- 可以查看到优化后的sql语句

(2) MySQL8.0之前版本：

EXPLAIN EXTENDED sql语句;

SHOW WARNINGS;  -- 可以查看到优化后的sql语句

3. 优化器会将in(子查询)优化成join的形式

例如：

EXPLAIN SELECT film.`title` FROM sakila.film WHERE film_id IN (SELECT film_id FROM sakila.`film_actor` WHERE actor_id=1);

SHOW WARNINGS
           /* select#1 */ SELECT `sakila`.`film`.`title` AS `title` FROM `sakila`.`film_actor` JOIN `sakila`.`film` WHERE ((`sakila`.`film`.`film_id` = `sakila`.`film_actor`.`film_id`) AND (`sakila`.`film_actor`.`actor_id` = 1))

4. 如何用sum()和count()统计某具体一列的数量

例如：(统计first_name=‘ED’列的总数量)

SELECT COUNT(first_name='ED' OR NULL) FROM sakila.actor;  -- count在统计列时只会统计非NULL的列，并不会判定是否满足表达式

SELECT SUM(first_name='ED') FROM sakila.actor;

5. SQL语句的语法顺序和执行顺序

语法顺序：

select[distinct]，from，join(如left join)，on，where，group by，having，union，order by，limit。

执行顺序：

from，on，join，where，group by，having，select，distinct，union，order by，limit。

6. char()，binary()括号内的长度是指字符还是字节

char()：5.0以上版本的使用字符存储，5.0以下使用字节存储。

binary()：目前是以字节的形式存储。

7. utf8与utf8mb4区别

MySQL在5.5.3之后增加了这个utf8mb4的编码，mb4就是most bytes 4的意思，专门用来兼容四字节的unicode。好在utf8mb4是utf8的超集，除了将编码改为utf8mb4外不需要做其他转换。utf8能够存下大部分中文汉字,那为什么还要使用utf8mb4呢? 原来mysql支持的 utf8 编码最大字符长度为 3 字节，如果遇到 4 字节的宽字符就会插入异常了

8. char和binary区别 /* 未解决 */

（1）binary使用\0(零字节)填充，检索时不会去掉填充物。

（2）binary使用字节码按字节进行比较，区分大小写。

  char按字符比较，不区分大小写。

（3）使用binary进行比较速度比char快。

（4）mysql8中binary的使用与char大有不同，十分诡异。

9. 日期与时间戳转换的函数

将时间戳转日期的函数：FROM_UNIXTIME(unix_timestamp,format);

将日期转时间戳的函数：UNIX_TIMESTAMP(date);

10. explain 执行计划中个列的含义？

（1）select_type：

简单

simple：查询不包含子查询和union，表示不包含复杂类型的查询（join不属于复杂类型查询）

复杂

primary：查询有任何复杂的子部分，则最外层部分标记为primary，表示包含复杂类型查询最外层

subquery：不在from子句中出现的子查询（可以在select或where列表中出现）。

例如：
SELECT (SELECT actor_id FROM film_actor LIMIT 1) AS id,actor_id FROM actor;
SELECT actor_id FROM actor WHERE actor_id=(SELECT actor_id FROM film_actor LIMIT 1)

derived：在from子句中出现的子查询

例如：
SELECT actor_id FROM(SELECT actor_id FROM film_actor LIMIT 1) AS tmp

union：union中的第二个和随后的select被标记为union

例如：
SELECT actor_id FROM actor UNION ALL SELECT actor.actor_id FROM film_actor INNER JOIN actor ON actor.`actor_id`=film_actor.`actor_id`;

（2）table

显示顺序根据左侧深度优先树生成，如下：

sql：

分析：

11. MySQL查询过程

12. 如何减少count()时访问行数

EXPLAIN
SELECT COUNT(1) FROM actor WHERE actor_id>5;

EXPLAIN
SELECT (SELECT COUNT(*) FROM actor)-COUNT(*) FROM actor WHERE actor_id<5;

13. 如何在查询时不使用查询缓存，MYSQL8已经取消查询缓存

SELECT SQL_NO_CACHE * FROM customer WHERE first_name='CHRISTINA' AND last_name LIKE '%AN%';

14. 如何在一个查询中统计同一个列的不同值的数量

SELECT SUM(color='red') AS recCnt,SUM(color='blue') AS blueCnt FROM table_name;

15. MySQL导入导出表数据方法

例如：

导出：SELECT * FROM actor INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/test2.sql';

若提示：The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

可通过SHOW VARIABLES LIKE 'secure_file_priv'查看导出数据安全存放路径

导入：LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/test2.sql' INTO TABLE actor;

16. MySQL计算字符串中字节长度函数，和字符串中字符多少函数

例如：

SELECT LENGTH('汉1');    -- 5 

SELECT LENGTH('?1');    -- 5

SELECT CHAR_LENGTH('汉1');    -- 2
SELECT CHAR_LENGTH('?1');    -- 2

17. MySQL8 查询设置隔离级别

SHOW VARIABLES LIKE 'transaction_isolation';

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

18. INNODB表导入效率优化

（1）导入数据按照主键顺序排序

（2）SET unique_checks=0  -- 关闭唯一性校验

（3）SET autocommit=0  -- 关闭自动提交

19. 可以使用show profile for query，查看是否使用查询缓存，但仅限于mysql8以前，切记勿忘开查询缓存。

20. MYISAM不会死锁而InnoDB会死锁的原因？

MyISAM 表 锁 是 deadlock free 的， 这是 因为 MyISAM 总是 一次 获得 所需 的 全部 锁， 要么 全部 满足， 要么 等待， 因此 不会 出现 死锁。 但在 InnoDB 中， 除 单个 SQL 组成 的 事务 外， 锁 是 逐步 获得 的， 这就 决定了 在 InnoDB 中 发生 死锁 是 可能 的。

21. MYISAM锁表时的并发操作

LOCK TABLES时加了‘local’选项，其作用就是在满足MyISAM表并发插入条件的情况下，允许其他用户在表尾插入记录。

例如：LOCK tables orders read local,order_detail read local;

22.排查死锁问题

SHOW ENGINE INNODB STATUS; -- 查看死锁语句

23. mysqldumpslow.pl 使用

24.设置服务器和客户端通信字符集

SET NAMES gbk; -- 或者SET CHARACTER SET utf8;

25.修改表的校对规则和字符集

ALTER TABLE tmp4 COLLATE utf8_bin;

ALTER TABLE tmp4 CHARACTER SET utf8;

26.修改字段的字符集和校对规则

ALTER TABLE tmp4 MODIFY COLUMN first_name VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_bin;

27.全文索引（fullIndex，基于5.7.6的ngram parser插件）

<1> 自然语言的全文索引：

如果想针对特定长度（ngram_token_size）进行切割（适合中文，不适合英文，英文按照空格切割），例如：松下电子，切割为：松下 下电 电子

可以在my.ini中设置ngram_token_size=2，创建表时添加相应注释即可

若不按照特定长度（不设置ngram_token_size）切割则默认根据空格切割，使用自然语言全文索引需注意ngram_token_size的大小。

<2> 布尔全文索引：

根据空格切割（不论中文、英文）。

<3> 注意：

（1）修改ngram_token_size，需要重启服务并OPTIMIZE TABLE xx;

（2）不确定切割结果时可以使用如下查看。

  SET GLOBAL innodb_ft_aux_table="sakila/film_text2";

  SELECT * FROM information_schema.INNODB_FT_INDEX_TABLE;
  SELECT * FROM information_schema.INNODB_FT_INDEX_CACHE;

28.查看当前session的线程id：

SELECT @@pseudo_thread_id

29.RC隔离级别下不支持间隙锁。

30.事务开启时间是在第一个sql语句执行时，而不是begin执行时。

31. 在5.7版本中可以查看sys.innodb_lock_waits表来确定是哪个线程占用锁未释放。

32. Innodb RR和RC隔离级别下，由于对非索引行查找需要进行全表扫描，所以对非索引行加锁会导致全表锁住。

33. Gap Lock是为了解决幻读问题，所以只有在RR隔离级别下才存在，由于对非索引查找需要进行全表扫描，所以对非索引字段加锁会造成全表性的间隙锁，如果对非主键索引查找，只会对其左右两侧的间隙上锁，如果对主键索引查找，由于主键的自增特性所以不会加间隙锁。

34. MySQL脏页刷新问题

（1）可以通过下面语句查看脏页比例：
SELECT VARIABLE_VALUE INTO @a FROM `performance_schema`.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty';
SELECT VARIABLE_VALUE INTO @b FROM `performance_schema`.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_total';
SELECT @a/@b;

（2）如果脏页比例接近75%，就要检查innodb_io_capacity设置的是否合理，innodb_io_capacity建议设置为磁盘的IOPS。

（3）IOPS获取方式，可以通过fio工具，命令如下：

fio -filename=/tmp/test_randrw -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=2G -numjobs=10 -runtime=60 -group_reporting -name=mytest

35. 如何使用optimizer_trace去分析order by语句。
/* 打开optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on'; 

/* @a保存Innodb_rows_read的初始值 */
select VARIABLE_VALUE into @a from  performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 执行语句 */
select city, name,age from t where city='杭州' order by name limit 1000; 

/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G

/* @b保存Innodb_rows_read的当前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 计算Innodb_rows_read差值 */
select @b-@a;

optimizer_trace结果图：

36. 如何查看mysql binlog？

可以使用mysqlbing工具查看mysql binlog

mysqlbinlog工具存放在mysql bin目录下，例如：C:\Program Files\MySQL\MySQL Server 8.0\bin

mysql binlog日志存放在Data目录下，例如：C:\ProgramData\MySQL\MySQL Server 8.0\Data

使用mysqlbing分析时可以将需分析的binlog放到D盘根目录，然后使用如下命令分析：

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqlbinlog -vv D:\binlog.000104 --start-position=8092