MySQL数据库调优之索引失效的情况

索引优化与查询优化

都有哪些维度可以进行数据库调优？简言之：
索引失效，没有充分利用到索引–索引建立
关联查询太多join(设计缺陷或不得已的需求) --SQL优化
服务器调优及各个参数设置（缓冲、线程数等） --调整my.cnf
数据过多–分库分表
虽然SQL查询优化技术有很多，但是大方向完全可以分成物理查询优化和逻辑查询优化 两大块。
物理查询优化是通过索引和表连接方式等技术来进行优化，这里重点需要掌握索引的使用。
逻辑查询优化就是通过SQL等价变换提升查询效率，直白一点就是说，换一种查询写法执行效率可能更高。
1. 数据准备

##建表
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) NULL,
PRIMARY KEY (id)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


#创建函数 函数复用之前的随机函数


#创建存储过程

#student
DELIMITER //
CREATE PROCEDURE insert_student(START 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((START+i),rand_string(6),rand_num(1,50),rand_num(1,1000));
	UNTIL i=max_num
	END REPEAT;
	COMMIT;
END //
DELIMITER ;

# 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,1000));
	UNTIL i=max_num
	END REPEAT;
	COMMIT;
END //
DELIMITER ;

#执行存储过程
CALL insert_class(10000);

CALL insert_student(100000,500000);


SELECT COUNT(*) FROM class;

SELECT COUNT(*) FROM student;

2.索引失效的案例
使用索引可以快速的定位表中的某条记录，从而提高数据库查询的速度，提高数据库的性能。如果查询时没有使用索引，查询语句就会扫描表中的所有记录。在数据量大的情况下，这样查询的速度会很慢的。
大多数情况下都(默认)采用B+树来构建索引。只是空间列类型的索引使用R-数，并且memory表还支持Hash索引。
其实，用不用索引，最终都是优化器说了算。优化器时基于什么的优化器？基于cost开销（**Cost-BaseOptimizer）,它不是基于规则**（Rule-BaseOptimizer）,也不是基于语义。怎么开销小就怎么样来。另外,SQL语句是否使用索引，跟数据库版本、数据量、数据选择度都有关系。
全值匹配我最爱

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME='abcd'



CREATE INDEX indx_age ON student(age);

CREATE INDEX indx_age_classid ON student(age,classId);

CREATE INDEX indx_age_classid_name ON student(age,classId,NAME);

最佳左前缀的规则
在Mysql建立联合索引时会遵守最佳左前缀匹配的原则，即最左优先，在检索数据时会从联合索引的最左边开始匹配。

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE  classId=4 AND NAME='abcd'

Mysql可以为多个字段创建索引，一个索引可以包含16个字段。对于多列索引，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。如果查询条件中没有使用这些字段中的第一个字段时，多列（或联合）索引不会被使用。
主键插入的顺序


这个数据页已经满了，再插进来怎么办？需要把当前页面分裂成两个页面，把页面中的一些记录移动到新创建的这个页中。页面分裂和记录移位意味着什么？
意味着：性能损耗!! 所以我们应该尽量避免这样无谓的性能损耗，最好让插入的记录的主键值依次递增，这样就不会发生这样的性能损耗了。所以：让主键具有ANTO_INCREMENT ,让存储引擎自己为表生成主键，而不是手动插入。
与业务强相关的业务表，比如订单表，后面讨论主键的设置

计算、函数、类型准换（手动或自动）导致索引失效

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

上面的left函数可以使索引失效

like模糊查询可以使用索引

# 可以使用上索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE 'abc%';

以下操作索引也会失效的

EXPLAIN SELECT SQL_NO_CACHE id,stuno,NAME FROM student WHERE stuno+1=900000;

类型转换导致索引失效

结论：设计实体类属性时，一定要与数据库字段类型相对应。否则，就会出现类型转换的情况。

范围条件右边的列索引失效

小结：
应用开发中范围查询，例如：金额查询、日期查询往往是范围查询。应将查询条件放置在where语句最后（创建的联合索引中，务必把范围涉及到的字段写在后面）。

不等于（！=或者<>）索引失效

is null 可以使用索引，is not null 无法使用索引
is null : 可以触发索引

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL;

is not null 无法触发索引

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

like以通配符%开头索引失效
在使用Like关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引就不会起作用。只有%不在第一个位置，索引才会起作用。

OR 前后存在非索引的列，索引失效
在where子句中，如果在OR前的条件列进行了索引，而在OR后的条件列没有进行索引，那么索引就会失效。
也就是说，OR前后的两个条件中的列都是索引时，查询时才使用索引
因为OR的含义就是两个只要满足一个即可。因此，只有一个条件列进行了索引是没有意义的，只要有条件列没有进行索引，就会进行全表扫描，因此索引的条件列也会失效

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

练习


总结一般性建议

链接
B站