MySQL深度剖析-SQL调优实战

一、排序优化

数据准备

-- 部门表
CREATE TABLE department (
  id INT(11) PRIMARY KEY,
  deptName VARCHAR(30) ,
  address VARCHAR(40) 
) ;

-- 部门表测试数据
INSERT INTO `department` VALUES (1, '研发部', '1层');
INSERT INTO `department` VALUES (2, '人事部', '3层');
INSERT INTO `department` VALUES (3, '市场部', '4层');
INSERT INTO `department` VALUES (5, '财务部', '2层');

-- 员工表
CREATE TABLE employee (
  id INT(11) PRIMARY KEY,
  NAME VARCHAR(20) ,
  dep_id INT(11) ,
  age INT(11) ,
  salary DECIMAL(10, 2)
);

-- 员工表测试数据
INSERT INTO `employee` VALUES (1, '鲁班', 1, 15, 1000.00);
INSERT INTO `employee` VALUES (2, '后裔', 1, 22, 2000.00)
INSERT INTO `employee` VALUES (4, '阿凯', 2, 20, 3000.00);
INSERT INTO `employee` VALUES (5, '露娜', 2, 30, 3500.00);
INSERT INTO `employee` VALUES (6, '李白', 3, 25, 5000.00);
INSERT INTO `employee` VALUES (7, '韩信', 3, 50, 5000.00);
INSERT INTO `employee` VALUES (8, '蔡文姬', 3, 35, 4000.00);
INSERT INTO `employee` VALUES (3, '孙尚香', 4, 20, 2500.00);

 为 employee 表 创建索引

-- 联合索引
ALTER TABLE employee ADD INDEX idx_name_age(NAME,age);

-- 为薪资字段添加索引
ALTER TABLE employee ADD INDEX idx_salary(salary);

场景1: 只查询用于排序的 索引字段, 可以利用索引进行排序,最左原则

• 查询 name, age 两个字段, 并使用 name 与 age 行排序

EXPLAIN SELECT e.name, e.age FROM employee e ORDER BY e.name,e.age;

场景2: 排序字段在多个索引中,无法使用索引排序

• 查询 name , salary 字段, 并使用 name 与 salary 排序

EXPLAIN SELECT e.name, e.salary FROM employee e ORDER BY e.name,e.salary;

场景3: 只查询用于排序的索引字段和主键, 可以利用索引进行排序

• 查询 id , name , 使用 name 排序

EXPLAIN SELECT e.id, e.name FROM employee e ORDER BY e.name;

场景4: 查询主键之外的没有添加索引的字段，不会利用索引排序

• 查询 dep_id ,使用 name 进行排序

EXPLAIN SELECT e.dep_id FROM employee e ORDER BY e.name;

场景5: 排序字段顺序与索引列顺序不一致,无法利用索引排序

• 使用联合索引时, ORDER BY子句也要求, 排序字段顺序和联合索引列顺序匹配。

EXPLAIN SELECT e.name, e.age FROM employee e ORDER BY e.age,e.name;

场景6: where 条件是 范围查询时, 会使order by 索引 失效

• 比如 添加一个条件 : age > 18 ,然后再根据 age 排序.

EXPLAIN SELECT e.name, e.age FROM employee e WHERE e.age > 10 ORDER BY e.age;

注意: ORDERBY子句不要求必须索引中第一列,没有仍然可以利用索引排序。但是有个前提条件，只有在等值过滤时才可以，范围查询时不可以

EXPLAIN SELECT e.name, e.age FROM employee e WHERE e.age = 18 ORDER BY e.age;