2_1 索引优化(补)

对【2.Explain详解与索引优化 -  CSDN App】https://blog.csdn.net/Csdn_xzh/article/details/142603868?type=blog&rId=142603868&refer=APP&source=Csdn_xzh 这篇文章的常用优化错误改正和补充

这里以表employess为例

CREATE TABLE 'employees'(

    'id' int(11) NOT NULL AUTO INCREMENT,

    'name' varchar(24) NOT NULL DEFAULT '' COMMENT'姓名',

    'age' int(11) NOT NULL DEFAULT 'O' COMMENT '年龄',

    'position' varchar(28) NOT NULL DEFAULT '' COMMENT'职位',

    'hire_time' timeStamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'时间',

    PRIMARY KEY('id'),

    KEY 'idx_name_age_position'('name','age','position')USING BTREE )ENGINE=InnODB_AUTO_INCREMENT=4 DEFAULT CHARSET=Utf8 COMMENT='员工记录表';

INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NoW());

INSERT INTO employees(name,age,position,hire_time) VALUES( 'HanMeimei', 23, 'dev' ,NOW());

INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23, 'dev',NOW());

• 1. 全值匹配

EXPLAIN SELECT * FROM employees WHERE name ='LiLei'; -- 一个字段简单全值查询，并且使用了索引，精确定位一条数据

• 2. 最左侧原则

-- 如果是联合索引，无论多少个字段，只要遵循查询的字段能够正确从左到右区匹配索引，并且不跳过索引中的列。 EXPLAIN SELECT * FROM employees WHERE name = 'Bill' and age = 31; -- 可以走索引 EXPLAIN SELECT * FROM employees WHERE age = 30 AND position = 'dev'; -- 不可以走索引 跳过了name列 EXPLAIN SELECT * FROM employees WHERE position = 'manager'; -- 不可以走索引

• 3. like 尽量不使用%开头，会导致索引失效进行全表扫秒。可以使用%结尾（当成常量），若使%开头，尽量使用覆盖索引

EXPLAIN SELECT * FROM employees WHERE name like '%Lei'

EXPLAIN SELECT * FROM employees WHERE name like 'Lei%'

• 4. 尽量少使用 or 和 in ，mysql内部会对sql进行一个优化，根据检索的比例、表大小、数据量大小选择是否使用索引

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' or name = 'HanMeimei'; -- 这里只查出3条数据，mysql优化器认为全盘扫描聚簇索引更快

• 5. 不在索引上做计算、函数、类型转换等操作，导致索引失效进行全表扫秒

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei'; 
EXPLAIN SELECT * FROM employees WHERE left(name,3) = 'LiLei'; -- 导致索引失效

• 6. 条件字段尽量少使用索引中的范围查询，若数据量过大，可能会造成索引失效

-- 例如给 age建议索引 
ALTER TABLE employees ADD INDEX 'idx_age'('age') USING BTREE ; 
explain select * from employees where age >=1 and age <=2000;

-- 优化方式，可以将age查询范围缩小
explain select * from employees where age >=1 and age <=1000; 
explain select * from employees where age >=1001 and age <=2000

• 7. 尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少 select * 语句

EXPLAIN SELECT name,age FROM employees WHERE name= 'LiLei' AND age = 23 AND position ='manager';

• 8. 尽量不适用 not in、!=、not exists、>=等这些查询条件,会导致索引失效全盘扫秒，mysql会根据检索比例，数据量大小等衡量是否使用索引

EXPLAIN SELECT * FROM employees WHERE name != 'LiLei';

• 9. is null 、is not null 一般情况下也无法使用索引

EXPLAIN SELECT * FROM employees WHERE name is null;

• 10. 字符串不加单引号索引会失效

EXPLAIN SELECT * FROM employees WHERE name = '1000'; 
EXPLAIN SELECT * FROM employees WHERE name = 1000 -- 字符串不加引导可能会进行隐式转换，导致索引失效

• 11. 存储引擎不能使用索引中范围条件右边的列

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age > 22 AND position ='manager'；
-- age 使用了范围查找，导致后面position索引失效