MySQL优化

发现问题

慢查询日志

MySQL提供了慢查询日志，可以监听一个SQL语句的执行时间，如果过长，就会记录在文件中

是否启动 ：

show variables like 'slow_query_log'

启动慢查询：

set global slow_query_log = on;

慢查询日志存放位置

show variables like 'slow_query_log_file';

记录日志的触发时间(sql执行多少秒以上才会记录)

show variables like 'long_query_time';

设置时间(一般实际项目设置为0.001)

set global long_query_time = 0.001

当一条sql执行的时间超过了100毫秒后，就会记录到日志文件中，格式是这样的：

分析问题

使用Explan可以模拟优化器执行sql语句，从而进行分析查询语句

使用：

Explain + SQL语句

id：表示select子句执行的顺序

• id相同，执行顺序右上往下
• id不同，id值越大优先级越高，越先被执行。
• id为null时表示一个结果集，不需要使用它查询，常出现在包含union等查询语句中。

select_type：表示的查询的类型，主要用于区分普通查询、子查询、复杂查询

table：这一行的数据属于哪张表

type：访问的类型

索引

什么是索引？

索引是一种特殊的文件，InnoDB引擎数据表上的索引是表空间的组成的一部分，存储的是表中数据的引用指针，索引相当于一个目录，可以通过目录快速的找到要查询的内容；索引的实现通常有B+树

索引有什么优点？

1.索引可以加快数据检索的速度，这也是创建索引的主要原因

2.可以在查询的过程中，通过优化隐藏器，提高系统性能

索引有什么缺点？

1.降低增/删/该的效率，因为对数据的增/删/改，索引也需要动态的维护，而创建索引和维护索引会浪费时间

2.因为索引是一个文件，它会占用物理空间

B树索引的特点

1.B树适用于全值匹配的查询

SELECT .. FROM 表名 WHERE 索引列 = ?
SELECT .. FROM 表名 WHERE 索引列 IN (?,?);

2.B树适用于范围内查找

SELECT .. FROM 表名 WHERE 索引列 BETWEEN ? AND ?
SELECT .. FROM 表名 WHERE 索引列 > ?
SELECT .. FROM 表名 WHERE 索引列 < ?

3.B树索引从索引的最左侧开始匹配查找列

比如现在创建一个组合索引

CREATE INDEX idx_column on  student(age,username);

那么以下哪种查询会生效呢？

-- 会生效
SELECT .. FROM student WHERE age>10;  
-- 不会生效
SELECT .. FROM student WHERE username LIKE 'z';
-- 会生效，虽然第一个条件不是最左侧的值，但是优化隐藏器会优化顺序
SELECT .. FROM student WHERE username LIKE 'z' AND count > 10;

什么时候创建索引？

1.在where子句需要筛选的条件建立索引；前提：在筛选性好的列上建立

2.包含在order by、group by、distinct中的字段

EXPLAIN
SELECT course_id,b.class_name,d.type_name,c.level_name,title,score
FROM imc_course a
JOIN imc_class b ON a.class_id =  b.class_id
JOIN imc_level c ON a.level_id = c.level_id
JOIN imc_type d ON a.type_id = d.type_id
WHERE c.level_name = '高级'
AND b.class_name  = 'MySQL';

-- 索引顺序根据筛选性排序 这里分类>方向>难度
CREATE INDEX idx_classid_typeid_levelid ON imc_course(class_id,type_id,level_id);
CREATE INDEX idx_level_name ON imc_level(level_name);

如何选择符合索引键的顺序

1.区分度最高的放在联合索引的左侧

2.使用最频繁的列放在联合索引的左侧

改写SQL