10-1 MySQL 索引优化与查询优化

未分类 4小时前程序员胖胖胖虎阿

3 0 0

10-1 MySQL索引优化与查询优化

数据准备
索引失效案例
- 2.1 索引字段：全值匹配为优
- 2.2 索引字段：最佳左前缀准则
- 2.3 主键插入顺序
- 2.4 索引字段涉及计算、函数或类型转换致索引失效
- 2.5 索引字段类型转换引发索引失效
- 2.6 索引字段使用范围条件时右边列索引失效
- 2.7 索引字段使用不等于（!=或<>）致索引失效
- 2.8 索引字段is null可使用索引，is not null无法使用索引
- 2.9 索引字段使用以通配符%开头的like致索引失效
- 2.10 OR前后存在非索引列时索引失效
- 2.11 数据库与表字符集统一使用utf8mb4
关联查询优化
- 3.1 数据准备
- 3.2采用左外连接
- 3.3采用内连接
join语句原理
子查询优化
排序优化

1. 数据准备

创建数据库

CREATE DATABASE dbtest4;

建表

USE dbtest4;

CREATE TABLE `class` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`className` VARCHAR(30) DEFAULT NULL,
`address` VARCHAR(40) DEFAULT NULL,
`monitor` INT NULL ,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


CREATE TABLE `student` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`stuno` INT NOT NULL ,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(3) DEFAULT NULL,
`classId` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`)
#CONSTRAINT `fk_class_id` FOREIGN KEY (`classId`) REFERENCES `t_class` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

设置参数

开启允许创建函数的设置：

set global log_bin_trust_function_creators=1;    # 不加global只是当前窗口有效。

创建函数

随机产生字符串函数

DELIMITER //
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
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()*52),1));
SET i = i + 1;
END WHILE;
RETURN return_str;
END //
DELIMITER ;

#假如要删除
#drop function rand_string;

随机产生班级编号函数

DELIMITER //
CREATE FUNCTION  rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(from_num +RAND()*(to_num - from_num+1)) ;
RETURN i;
END //
DELIMITER ;

#假如要删除
#drop function rand_num;

创建存储过程

往stu表插入数据的存储过程

DELIMITER //
CREATE PROCEDURE insert_stu(startt INT ,  max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
  SET autocommit =0;#设置手动提交事务
  REPEAT #循环
  SET i = i + 1; #赋值
  INSERT INTO student (stuno, NAME ,age ,classId ) VALUES 
  ((startt+i),rand_string(6),rand_num(1,50),rand_num(1,1000));

  UNTIL i = max_num 
  END REPEAT; 
  COMMIT; #提交事务
END //
DELIMITER ;

#假如要删除
# drop PROCEDURE insert_stu;

往class表插入数据的存储过程

DELIMITER //
CREATE PROCEDURE `insert_class`(  max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
  SET autocommit = 0;
  REPEAT
  SET i = i + 1;
  INSERT INTO class ( classname,address,monitor ) VALUES (rand_string(8),rand_string(10),rand_num(1,100000));
  UNTIL i = max_num
  END REPEAT;
  COMMIT;
END //
DELIMITER ;

#假如要删除
#drop PROCEDURE insert_class;

调用存储过程

往class表添加数据

#执行存储过程，往class表添加1万条数据
CALL insert_class(10000);

往stu表添加数据

#执行存储过程，往stu表添加50万条数据
CALL insert_stu(100000,500000);

删除某表上的索引

创建存储过程

DELIMITER //
CREATE PROCEDURE `proc_drop_index`(dbname VARCHAR(200),tablename VARCHAR(200))
BEGIN
      DECLARE done INT DEFAULT 0;
      DECLARE ct INT DEFAULT 0;
      DECLARE _index VARCHAR(200) DEFAULT '';
      DECLARE _cur CURSOR FOR SELECT index_name FROM information_schema.STATISTICS WHERE table_schema = dbname AND table_name = tablename AND seq_in_index = 1 AND index_name <> 'PRIMARY';
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 2; OPEN _cur;
        FETCH _cur INTO _index;
        WHILE _index <>'' DO
              SET @str = CONCAT("drop index", _index,"on" , tablename);
              PREPARE sql_str FROM @str ;
              EXECUTE sql_str;
              DEALLOCATE PREPARE sql_str;
              SET _index='';
              FETCH _cur INTO _index;
        END WHILE;
   CLOSE _cur;
END //
DELIMITER ;

执行存储过程

CALL proc_drop_index("dbname","tablename");

2. 索引失效案例

2.1 索引字段：全值匹配最优

全值匹配最优指查询内容的过滤都契合索引，完全匹配创建的索引。

# 1. 全值匹配我最爱
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30;

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND classid = 4;

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND classid = 4 AND `name` = 'abcd';

# 添加上索引
CREATE INDEX idx_age ON student(age);
CREATE INDEX idx_age_classid ON student(age,classId);
CREATE INDEX idx_age_classid_name ON student(age,classId,`name`);

2.2 索引字段：最佳左前缀法则

Alibaba《Java开发手册》提到索引文件具B-Tree最左前缀匹配特性，左边值未确定则无法用此索引。

SHOW INDEX FROM student;
# 最佳左前缀法则
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND `name` = 'abcd';

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE classid = 1 AND `name` = 'abcd';

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE classid = 1 AND `name` = 'abcd' AND age = 30;

2.3 主键插入顺序

若插入主键值为9的记录，数据页满需页面分裂，这会损耗性能。建议让主键具AUTO_INCREMENT，如：

CREATE TABLE person_info(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
birthday DATE NOT NULL,
phone_number CHAR(11) NOT NULL,
country varchar(100) NOT NULL,
PRIMARY KEY (id),
KEY idx_name_birthday_phone_number (name(10), birthday, phone_number)
);

2.4 索引字段进行了：计算、函数、类型转换(自动或手动)导致索引失效

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';

# 计算、函数、类型转换(自动或手动)导致索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE '%abc';

# student表的字段stuno上设置有索引
CREATE INDEX idx_sno ON student(stuno);
# (索引字段)计算导致索引失效
EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno+1 = 900001;

# 索引优化：对于这种索引简单运算的，我们可以优先将运算结果计算出来，再进行查询，
EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno = 900000;

2.5 索引字段类型转换导致索引失效

# 未使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME=123;

# 使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME='123';

2.6 索引字段：使用了范围条件，右边的列索引失效

EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age = 30 AND student.classId > 20 AND student.name = 'abc';

# 将范围查询条件放置语句最后：
CREATE INDEX idx_age_classId_name ON student(age,NAME,classId);

EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age = 30 AND student.classId > 20 AND student.name = 'abc';

# 应用开发中范围查询，例如：金额查询，日期查询等等一些范围查询，在创建索引时，需将这些范围查询的字段放到(索引字段的最后面)。

2.7 索引字段：不等于(!= 或者<>)索引失效

# 不等于 (!= 或者 <>) 索引失效
CREATE INDEX idx_name ON student(`name`);

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.`name` <> 'abc';

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.`name` != 'abc';

2.8 索引字段： is null可以使用索引，is not null无法使用索引

is null 可以使用索引：

# is null 可以使用索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.`name` IS NULL;

is not null ：无法使用索引，索引失效

# is not null ：无法使用索引，索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.`name` IS NOT NULL;

结论：最好在设计数据表的时候就将字段设置为NOT NULL约束，比如可以将INT类型的字段，默认值设置为0，将字符类型的默认值设置为空字符串""。同理，在查询中使用no like也无法使用索引，导致全表扫描。

2.9 索引字段：使用了 like以通配符 % 开头索引失效

# 索引字段当中使用了 like 以通配符 '%' 开头索引失效
# 未使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.`name`LIKE '%ab';

# 索引字段当中使用了 like 以通配符 '%' 开头索引失效
# 使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.`name`LIKE 'ab%';

2.10 OR 前后存在非索引的列，索引失效

# 创建 age 的索引
CREATE INDEX idx_age ON student(age);

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.`age` = 10 OR student.`name` = 'abc';

2.11 数据库和表的字符集统一使用utf8mb4

统一使用utf8mb4( 5.5.3版本以上支持)兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。不同的字符集进行比较前需要进行转换会造成索引失效。

3. 关联查询优化

3.1 数据准备

```sql

分类

CREATE TABLE IF NOT EXISTS type (
id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
card INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (id)
);

图书

CREATE TABLE IF NOT EXISTS book (
bookid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
card INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (bookid)
);

向分类表中添加20条记录

INSERT INTO type(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO type(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO type(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO type(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO type(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO type(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO type(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO type(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO type(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO type(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO type(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO type(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO type(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO type(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO type(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO type(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO type(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO type(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO type(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO type(card) VALUES(FLOOR(1 + (RAND() * 20)));

向图书表中添加20条记录

INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20