sql server 定位 string sql定位查询

转载

幸福的地图 2024-01-28 05:18:29

文章标签 数据库 sql mysql sql优化慢查询 文章分类 SQL Server 数据库

文章目录

一、什么是慢日志
二、优化大致思路
三、例子详解

explain工具分析sql
测试count(id)会走主键还是普通索引

一、什么是慢日志

慢日志：用来记录执行比较慢的一些sql

二、优化大致思路

根据慢日志定位慢查询sql
使用explain等工具分析sql
修改sql或者尽量让sql走索引

三、例子详解

环境：Navicat for MySQL，MySQL8.0+，win10
博主这里为了方便测试，提前插入了50万条数据（花了挺久的，选错引擎了，用的InnoDb，貌似用myisam会很快）下边给出脚本内容

/*
Navicat MySQL Data Transfer

Source Server         : test
Source Server Version : 80014
Source Host           : localhost:3306
Source Database       : mysql_study

Target Server Type    : MYSQL
Target Server Version : 80014
File Encoding         : 65001

Date: 2020-04-21 11:50:48
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for person_info_large
-- ----------------------------
DROP TABLE IF EXISTS `person_info_large`;
CREATE TABLE `person_info_large` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`,`age`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=501946 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Procedure structure for add_vote_memory
-- ----------------------------
DROP PROCEDURE IF EXISTS `add_vote_memory`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `add_vote_memory`(IN `n` int)
BEGIN
	DECLARE i INT DEFAULT 1;  

    WHILE (i <= n ) DO  

      INSERT into person_info_large  (name,age) VALUEs (rand_string(20),FLOOR(RAND() * 100));  

            set i=i+1;  

    END WHILE;  

END
;;
DELIMITER ;

-- ----------------------------
-- Function structure for rand_string
-- ----------------------------
DROP FUNCTION IF EXISTS `rand_string`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `rand_string`(`n` int) RETURNS varchar(255) CHARSET utf8
    NO SQL
BEGIN
	DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';   
  DECLARE return_str varchar(255) DEFAULT '' ;  
  DECLARE i INT DEFAULT 0;   

  WHILE i < n DO
     SET return_str = concat(return_str,substring(chars_str , FLOOR(1 + RAND()*62 ),1));   
     SET i = i +1;   
  END WHILE;   

RETURN return_str; 

END
;;
DELIMITER ;

里面有一个函数和一个存储过程，用来插入大量数据的，觉得慢可以百度另一种引擎方法。
在插入数据之前，最好去my.ini里面加上一句max_heap_table_size = 1024M，避免无法存那么多。找不到my.ini的可以百度一下。
调用存储过程直接：CALL add_vote_memory(500000);,时间比较久，可以耐心等待一下

我们先查询一些变量：SHOW VARIABLES LIKE "%quer%";，我的是已经修改好的

sql server 定位 string sql定位查询_sql优化

设置语句用SET GLOBAL name = 设置long_query_time后不会立马生效，需要关闭当前连接，重新进去，我们再次查询，看是否改变，不行的话，就直接改my.ini文件进行修改这三项，修改完后记得重新启动mysql服务。

我们写一个查询：SELECT name from person_info_large ORDER BY name DESC;

sql server 定位 string sql定位查询_sql优化_02

可以看到，查询时间超过了1秒，我们可以查询一下慢查询sql：show status like '%slow_queries%';

sql server 定位 string sql定位查询_数据库_03

已经记录了一条，我们可以去慢查询日志看一下

sql server 定位 string sql定位查询_慢查询_04

里面有慢查询sql，以上就是定位的方法。

explain工具分析sql

只需要在sql前面加一个explain即可，具体的explain语法可以网上查询一下，这里不再细说

执行没走索引的查询加排序：explain SELECT name from person_info_large ORDER BY name DESC;，结果如下

sql server 定位 string sql定位查询_慢查询_05

可以看到出现了ALL和Using filesort字样，一般我们都是要改sql，让其走索引的，目前的需求只是查询name，于是我们可以加一个普通索引：alter table person_info_large add index idx_name(name);（注：添加索引是DDL语言，不会算成慢查询日志里）执行之后，走索引查询一次：SELECT name from person_info_large ORDER BY name DESC;

sql server 定位 string sql定位查询_慢查询_06