Mysql踩坑二三事

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

坑一

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

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的查询

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 |
+----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

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

坑二

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

No.1

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

No.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,解决方案
修改配置,

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

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

发表评论

电子邮件地址不会被公开。