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