都有哪些维度可以进行数据库调优？简言之：

  • 索引失效、没有充分利用到索引—―索引建立
  • 关联查询太多JOIN（设计缺陷或不得已的需求）——SQL优化
  • 服务器调优及各个参数设置（缓冲、线程数等）――调整my.cnf
  • 数据过多——分库分表

关于数据库调优的知识点非常分散。不同的DBMS，不同的公司，不同的职位，不同的项目遇到的问题都不尽相同。这里我们分为三个章节进行细致讲解。
虽然SQL查询优化的技术有很多，但是大方向上完全可以分成物理查询优化和逻辑查询优化两大块

  • 物理查询优化是通过索引和表连接方式等技术来进行优化，这里重点需要掌握索引的使用。
  • 逻辑查询优化就是通过SQL等价变换提升查询效率，直白一点就是说，换一种查询写法执行效率可能更高。

    1、数据准备

    学员表插50万条，班级表插1万条。
    步骤1：建表 ``sql CREATE TABLEclass(idINT(11) NOT NULL AUTO_INCREMENT,classNameVARCHAR(30) DEFAULT NULL,addressVARCHAR(40) DEFAULT NULL,monitorINT 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;

  1. **步骤2：设置参数**
  3. - 命令开启：允许创建函数设置
  4. ```sql
  5. set global log_bin_trust_function_creators=1; #不加global只是当前窗口有效。

步骤3：创建函数
保证每条数据都不同

  1. #函数1：产生随机字符串
  2. DELIMITER //
  3. CREATE FUNCTION rand_string(n INT)
  4.                 RETURNS VARCHAR(255) #该函数会返回一个字符串
  5. BEGIN
  6.             DECLARE chars_str VARCHAR (100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
  7.             DECLARE return_str VARCHAR(255) DEFAULT '';
  8.             DECLARE i INT DEFAULT 0;
  9.             WHILE i < n DO
  10.             SET return_str = CONCAT(return_str, SUBSTRING(chars_str, FLOOR(1+RAND()*52),1));
  11.             SET i = i + 1;
  12. END WHILE;
  13. RETURN return_str;
  14. END //
  15. DELIMITER;

随机产生班级编号

  1. #用于随机产生多少到多少的编号
  2. DELIMITER //
  3. CREATE FUNCTION rand_num (from_num INT, to_num INT) RETURNS INT(11)
  4. BEGIN
  5. DECLARE i INT DEFAULT 0;
  6. SET i = FLOOR(from_num + RAND() * (to_num - from_num + 1)); 
  7. RETURN i;
  8. END //
  9. DELIMITER ;

步骤4：创建存储过程

  1. #创建往stu表中插入数据的存储过程
  2. DELIMITER //
  3. CREATE PROCEDURE insert_stu(START INT, max_num INT)
  4. BEGIN
  5. DECLARE i INT DEFAULT 0;
  6. SET autocommit = 0; #设置手动提交事务
  7. REPEAT #循环
  8. SET i = i + 1; #赋值
  9. INSERT INTO student (stuno, name, age, classId) VALUES ((START + i), rand_string(6) , rand_num(1, 50), rand_num(1, 1000));
  10. UNTIL i = max_num
  11. END REPEAT;
  12. COMMIT; #提交事务
  13. END //
  14. DELIMITER ;
  16. #class表插入数据存储过程
  17. DELIMITER //
  18. CREATE PROCEDURE insert_class(max_num INT)
  19. BEGIN
  20. DECLARE i INT DEFAULT 0;
  21. SET autocommit = 0 ;
  22. REPEAT
  23. SET i = i + 1;
  24. INSERT INTO class (classname, address, monitor) VALUES(rand_string(8), rand_string(10) , rand_num(1 , 100000));
  25. UNTIL i = max_num
  26. END REPEAT;
  27. COMMIT;
  28. END //
  29. DELIMITER ;

步骤5：调用存储过程

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

步骤6：创建删除索引的存储过程
方便后续的删除索引操作

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=1来控制游标的结束
DECLARE CONTINUE HANDLER FOR NOT FOUND set done=2;
#若没有数据返回,程序继续,并将变量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 ;

2、索引失效案例

MySQL中提高性能的一个最有效的方式是对数据表 设计合理的索引。索引提供了高效访问数据的方法，并且加快查询的速度，因此索引对查询的速度有着至关重要的影响。

  • 使用索引可以快速地定位表中的某条记录，从而提高数据库查询的速度，提高数据库的性能。
  • 如果查询时 没有使用索引，查询语句就会 扫描表中的所有记录。在数据量大的情况下，这样查询的速度会很慢。

大多数情况下都（默认）采用B+树来构建索引。只是空间列类型的索引使用R-树，并且MEMORY表还支持 Hash索引。
其实，用不用索引，最终都是优化器说了算。优化器是基于什么的优化器？基于cost开销(CostBaseOptimizer)，它不是基于规则(Rule-BasedOptimizer)，也不是基于语义。怎么样开销小就怎么来。另外，SQL语句是否使用索引，跟数据库版本、数据量、数据选择度都有关系。

2.1、全值匹配我最爱

系统经常会出现的SQL语句如下

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 = 'abc';

建立索引前执行：

mysql> SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND classId = 4 AND name = 'abcde';
Empty set, 1 warning (0.10 sec)

建立索引：

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);

建立索引后执行：

mysql> SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND classId = 4 AND name = 'abcdefg';
Empty set, 1 warning (0.00 sec)

mysql>

可以看到，创建索引前的查询时间是0.10秒，创建索引后的查询时间是0.00秒，索引帮助我们极大的提高了查询效率。

2.2、最佳左前缀法则

在MySQL建立联合索引时会遵守最佳左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。
先删除其他索引，留下idx_age_classid_name

DROP INDEX idx_age ON student;
DROP INDEX idx_age_classid ON student;

举例1：

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.name = 'abcd';

image.png
虽然索引idx_age_classid_name可以正常使用，但是只有部分被使用到了。

举例2：

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.classid=30 AND student.name = 'abcd';

image.png
完全没有使用上索引。

举例3：

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

image.png
虽然顺序不对，但索引idx_age_classid_name被完全使用到了。

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

拓展：Alibaba《Java开发手册》 索引文件具有B-Tree的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。

2.3、主键插入顺序

对于一个使用InnoDB存储引擎的表来说，在我们没有显式的创建索引时，表中的数据实际上都是存储在聚簇索引的叶子节点的。而记录又是存储在数据页中的，数据页和记录又是按照记录主键值从小到大的顺序进行排序，所以如果我们插入的记录的主键值是依次增大的话，那我们每插满一个数据页就换到下一个数据页继续插，而如果我们插入的主键值忽大忽小的话，就比较麻烦了，假设某个数据页存储的记录已经满了，它存储的主键值在1~100之间：
image.png
如果此时插入一条主键值为9的记录，那它插入的位置就如下图：
image.png
可这个数据页已经满了，再插进来咋办呢？我们需要把当前页面分裂成两个页面，把本页中的一些记录移动到新创建的这个页中。页面分裂和记录移位意味着什么？意味着：性能损耗！所以如果我们想尽量避免这样无谓的性能损耗，最好让插入的记录的主键值依次递增，这样就不会发生这样的性能损耗了。所以我们建议：让主键具有AUTO_INCRENENT，让存储引擎自己为表生成主键，而不是我们手动插入。

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

ICP（索引条件下推）：主要作用是减少回表的次数，从而提升查询的性能
ICP仅作用于二级索引，在根据索引查出对应的用户记录集合ListA，接着根据失效的索引条件对ListA再进行二次过滤，得到ListB，然后在根据ListB进行回表操作，减少回表次数。

如：表tb有一个联合索引 idx_ab(a, b)，有以下查询语句 select * from tb where a = ‘123’ and b like ‘%123%’

1、由于b like '%123%' 导致索引idx_ab只能利用a部分去搜索，因此在搜索索引树时只能根据a = '123'
去获取对应的用户记录，假设这里加载了10页数据。
2、如果没有ICP，InnoDB会遍历这10页数据，一个个进行回表操作，将符合条件的数据页全部加载
3、利用ICP后，InnoDB会根据b like '%123%' 对步骤1中10数据进行筛选，假设筛选后只剩1页数据，
那么需要进行会表操作的数据就会大大减少，从而提高查询效率