 一、MySql安装、使用相关问题 ① 远程客户端报错：client does not support authentication protocol requested by server； 原因：mysql5.8密码加密规则跟客户端不一致； 解决方法： 1、linux登录mysql，use mysql库 2、找到user表； 3、执行命令:alter user 'root'@'%' identified with mysql_native_password by 'root'; 注意：password:自己密码 ② mysql服务器命令 service mysql start 命令执行失败 原因：目录没有权限，需要添加可写权限 解决方法： 将mysql目录下 support-files中的mysql.server 复制到/etc/init.d/ 目录下，并重命名为mysql赋予文件权限：chmod -R 777 mysql ③ Group by 查询报错：this is incompatible with sql_mode=only_full_group_by 原因：语法不严谨，或者放宽限制 方法1：重启后依然需要设置model SET @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZE RO,NO_ENGINE_SUBSTITUTION'; SET @@session.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZE RO,NO_ENGINE_SUBSTITUTION'; 方法2:修改etc/my.inf（mysql5.8） sql-mode="NO_ENGINE_SUBSTITUTION" ④ Linux系统环境MySql自动备份 1、备份数据库sh脚本 #!/bin/bash mysqldump -uroot -pdbpass database > backpath/bak_$(date +%Y%m%d_%H%M%S).sql dbpass:数据库连接密码 database:备份数据库名称 backpath:备份文件存放路径 2、添加定时任务 添加命令:crontab -e #每天晚上23:50执行脚本 50 23 * * * /path/.sh 查看定时任务命令 crontab -l 二、MySql查询优化方法 ① 开启慢查询日志 -- 修改慢查询时间,只能当前会话有效; set long_query_time=1; -- 启用慢查询 ,加上global，不然会报错的; set global slow_query_log='ON'; -- 是否开启慢查询; show variables like "%slow%"; -- 查询慢查询SQL状况; show status like "%slow%"; -- 慢查询时间（默认情况下MySQL认位10秒以上才是慢查询）show variables like "long_query_time"; 修改my.ini配置文件-- 开启日志; slow_query_log = on -- 记录日志的log文件（注意:window上必须写绝对路径） slow_query_log_file = D:/mysql5.5.16/data/showslow.log -- 最长查询的秒数; long_query_time = 2 ② 混合排序 mysql不能利用索引混合排序。但在某些场景下，可以使用特殊方法优化。 SELECT *FROM my_order o INNER JOIN my_appraise a ON a.orderid = o.idORDER BY a.is_reply ASC, a.appraise_time DESCLIMIT 0, 20由于is_replay 只有0、1状态，目前查询会全表扫描/**优化后 */SELECT *FROM ((SELECT * FROM my_order o INNER JOIN my_appraise a ON a.orderid = o.id AND is_reply = 0 ORDER BY appraise_time DESC LIMIT 0, 20) UNION ALL (SELECT * FROM my_order o INNER JOIN my_appraise a ON a.orderid = o.id AND is_reply = 1 ORDER BY appraise_time DESC LIMIT 0, 20)) tORDER BY is_reply ASC, appraisetime DESCLIMIT 20; ③ Exists语句 SELECT *FROM my_neighbor n LEFT JOIN my_neighbor_apply sra ON n.id = sra.neighbor_id AND sra.user_id = 'xxx'WHERE n.topic_status < 4 AND EXISTS(SELECT 1 FROM message_info m WHERE n.id = m.neighbor_id AND m.inuser = 'xxx') AND n.topic_type <> 5 /**优化后 */SELECT *FROM my_neighbor n INNER JOIN message_info m ON n.id = m.neighbor_id AND m.inuser = 'xxx' LEFT JOIN my_neighbor_apply sra ON n.id = sra.neighbor_id AND sra.user_id = 'xxx'WHERE n.topic_status < 4 AND n.topic_type <> 5 ④ 提前缩小范围 SELECT * FROM my_order o LEFT JOIN my_userinfo u ON o.uid = u.uid LEFT JOIN my_productinfo p ON o.pid = p.pidWHERE ( o.display = 0 ) AND ( o.ostaus = 1 ) ORDER BY o.selltime DESC LIMIT 0, 15 优化sql语句：先左连接，然后取15条记录，如果my_order记录比较多，执行比较耗时。/**优化后 */ SELECT *FROM ( SELECT * FROM my_order o WHERE ( o.display = 0 ) AND ( o.ostaus = 1 ) ORDER BY o.selltime DESC LIMIT 0, 15 ) o LEFT JOIN my_userinfo u ON o.uid = u.uid LEFT JOIN my_productinfo p ON o.pid = p.pid ORDER BY o.selltime DESC limit 0, 15 ⑤ 中间结果下推 SELECT a.*, c.allocatedFROM ( SELECT resourceid FROM my_distribute d WHERE isdelete = 0 AND cusmanagercode = '1234567' ORDER BY salecode limit 20) aLEFT JOIN ( SELECT resourcesid， sum(ifnull(allocation, 0) * 12345) allocated FROM my_resources GROUP BY resourcesid) cON a.resourceid = c.resourcesid 不难看出子查询 c 是全表聚合查询，在表数量特别大的情况下会导致整个语句的性能下降。/**优化后 */WITH a AS ( SELECT resourceid FROM my_distribute d WHERE isdelete = 0 AND cusmanagercode = '1234567' ORDER BY salecode limit 20)SELECT a.*, c.allocatedFROM aLEFT JOIN ( SELECT resourcesid， sum(ifnull(allocation, 0) * 12345) allocated FROM my_resources r, a WHERE r.resourcesid = a.resourcesid GROUP BY resourcesid) cON a.resourceid = c.resourcesid ⑥ 优化关联查询 1、确保on和using子句列有索引。 2、创建索引的时候，考虑到关联顺序，当a表和b表用到c关联时候， 优化器关联顺序是b、a表，不需要在b表对应列创建索引。 ⑦ 当我们知道查询结果一条记录时，使用limit select * from user where email ='' email没有索引，但是又是唯一，这时可以加limit 1 提高查询效率 select * from user where email ='' limit 1 ⑧ Count(*)统计执行效率最高 count(1)、count(id)需要全表扫描，并且判断值是否为空，count(*)是mysql 做了优化处理 ⑨ Explain执行计划 三、MySql特殊语句整理 ① 查询日期字段追加10天的记录 DATE_FORMAT(DATE_ADD(column,INTERVAL 10 DAY),'%Y-%m-%d %H:%i:%S') '采集时间 ② 查询字段逗号分隔 select group_concat(column) from table |
|
|
