Mysql踩坑二三事

最近对业务对mysql使用发现一些坑，今天就总结一下。

坑一

由于业务需求，最近写了一些跑数据的脚本。由于需要全量load数据表，直接全量获取的时候会导致内存占用太大无法处理，所以只能分页获取，如果直接使用数据库中的分页的话会很慢，刚开始我使用 show processlist;看到提示连接的状态一直在Sending data，刚开始我以为这个sending data是单纯的发送数据，后来查了一下，这个状态代表着收集+发送数据，参考文章实战：MySQL Sending data导致查询很慢的问题详细分析 ；之后就是通过explain来分析sql语句了，

1
2
3
4

EXPLAIN
SELECT *
FROM wp_statistics_visitor LIMIT 500
OFFSET 3000

运行结果

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	wp_statistics_visitor	NULL	ALL	NULL	NULL	NULL	NULL	34245	100.00	NULL

如果直接使用数据库的分页查询且不加限制的话，mysql会每次都全表扫描，当时我在想如何能加快搜索的速度，就想到使用一个程序分页是不是更好一些，我就做了一个测试将sql语句做了修改，通过这个id去做一个where in的查询

1
2
3
4
5

EXPLAIN
SELECT *
FROM wp_statistics_visitor
WHERE `ID` IN ("3001", "3002"..., "3014", "3015", "3016", "3017", "3018", "3019", "
3020", "3021", "3022", "3023", "3024", "3025", "302")

运行结果

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	wp_statistics_visitor	NULL	range	PRIMARY	PRIMARY	4	NULL	500	100.00	Using where

使用这样个sql语句的话我们就可以只查询指定的条数了，不需要每次都全量扫描整个表了，这样就能大大的节省查询时间，经修改后，原来需要两个多小时才能跑完数据，现在只要20分钟左右就可以跑完了，节约了很多的成本。

坑二

这个坑是昨天发现的，前几天听说mysql8发布了，这个必须要尝一下鲜啊，网上说有很多的提升，就将我自己服务器上的升级了，升级后出现两个问题。

No.1

数据库存在，但是Sequel Pro连接不上，这个问题没有，这个原因是Sequel Pro无法连接导致的只能换一个管理工具了，

No.2

用户和密码是对的，但是一直提示

1
2

Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(
/usr/local/mysql/lib/plugin/caching_sha2_password.so, 2): image not found

这个是mysql的默认加密方式变了，变成了caching_sha2_password，解决方案 修改配置，

1
2
3
4
5

vim /etc/my.cnf
#修改默认plugin
default-authentication-plugin=mysql_native_password
#重启mysql
service mysqld restart

重新修改密码就可以了 这就是最近遇到的坑，谢谢围观