MySQL优化查询

mysq查询l优化

　　指标：执行时间 检查的行数 返回的行数

1. count的优化

　　比如：计算id大于5的城市 a. select count(*) from world.city where id > 5; b. select (select count(*) from world.city) – count(*) from world.city where id <= 5; a语句当行数超过11行的时候需要扫描的行数比b语句要多， b语句扫描了6行，此种情况下，b语句比a语句更有效率。当没有where语句的时候直接select count(*) from world.city这样会更快，因为mysql总是知道表的行数。

案例：

# 建库
CREATE DATABASE IF NOT EXISTS db3 DEFAULT CHARSET=utf8;
USE db3;
# 建表
CREATE TABLE IF NOT EXISTS cnt(id INT , NAME VARCHAR(10),age INT ,tel VARCHAR(10));
# 创建存储过程 procedrue  
DELIMITER $
CREATE PROCEDURE cnt()
BEGIN
#定义变量 declare
DECLARE i INT DEFAULT 0;
WHILE(i<100000) DO
    BEGIN
        SELECT i;
        SET i=i+1;
        INSERT INTO cnt(id,NAME)VALUES(i,'zs');
    END;
END WHILE;
END $
DELIMITER ;
#调用存储过程
CALL cnt();

    
SELECT COUNT(*)FROM cnt;
#执行耗时   : 0 sec 传送时间   : 0.051 sec 总耗时      : 0.051 sec
#数据库知道表内有多少条数据    

SELECT COUNT(*) FROM cnt WHERE id > 5;  
# 执行耗时   : 0.130 sec 传送时间   : 0 sec 总耗时      : 0.130 sec
# 使用where 扫描了全表 id > 5的所有数据 总查询99995条

SELECT (SELECT COUNT(*) FROM cnt) - COUNT(*) FROM cnt WHERE id <=5 ;
# 执行耗时   : 0.080 sec 传送时间   : 0 sec  总耗时      : 0.081 sec
# 使用where 扫描全表 id <= 5 的五条数据  加上 (SELECT COUNT(*) FROM cnt) 一条数据 总查询 6 条

2、索引

强制使用索引

# 10.必要时强制查询优化器使用索引
#建表
create table if not exists T1(processid int, nextprocess int);
#建索引
create index index_processID on T1(processid);
# 10.1：不使用索引
select * from T1 where nextprocess = 1 and processid in (8,32,45);
# 10.2: 强制使用索引
select * from T1 froce index(index_processID)
 where nextprocess = 1 and process = 1 and processid in (8,32,45);