MySQL优化指南-优快云博客

mysql优化、数据库优化

一、尚硅谷-----周阳

一、尚硅谷-----周阳

1、mysql的架构介绍

在这里插入图片描述

2、mysql存储引擎

数据库相关面试题参考另一篇博客：Java面试笔记自己用、第二阶段、4数据库相关

常用的两个MyISAM和InnoDB
查看mysql已提供的存储引擎命令：show engines;
查看已安装的mysql当前默认存储引擎：show variables like '%storage_engine%';
MyISAM和InnoDB对比

3、索引介绍

索引的介绍

索引不是越多越好
不要对经常变动的数据加索引
小数据量的表不加索引
索引一般加在常用来查询的字段上

# 显示所有的索引信息
show INDEX FROM sys_user 

# 添加一个全文索引（索引名）列名
ALTER TABLE test ADD FULLTEXT INDEX NAME(NAME) 

# 添加一个普通索引 命名规则--id_表名_字段名
CREATE INDEX id_test_name ON test(`NAME`)

# 添加一个组合索引
CREATE INDEX index_mytable_id_name ON mytable(id,name);

# 添加一个唯一索引
CREATE UNIQUE INDEX index_mytable_name ON mytable(name);

# 分析sql执行状况
EXPLAIN SELECT * FROM test WHERE MATCH(NAME) AGAINST('1')

对比说明： 数据量大的时候可创建索引

普通索引

这是最基本的索引类型，而且它没有唯一性之类的限制。
创建索引，例如CREATE INDEX <索引的名字> ON tablename (列的列表);
修改表，例如ALTER TABLE tablename ADD INDEX [索引的名字] (列的列表);
创建表的时候指定索引，例如CREATE TABLE tablename ( [...], INDEX [索引的名字] (列的列表) );

全文索引

MySQL从3.23.23版开始支持全文索引和全文检索。在MySQL中，全文索引的索引类型为FULLTEXT。全文索引可以在VARCHAR
或者TEXT类型的列上创建。它可以通过CREATE TABLE命令创建，也可以通过ALTER TABLE或CREATE INDEX命令创建。
对于大规模的数据集，通过ALTER TABLE（或者CREATE INDEX）命令创建全文索引要比把记录插入带有全文索引的空表更快。

复合索引

指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合
CREATE INDEX index_mytable_id_name ON mytable(id,name);

唯一索引

索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一
CREATE UNIQUE INDEX index_mytable_name ON mytable(name);

七种JOIN理论
索引是什么？有哪些？

帮助获取mysql高效的获取数据的数据结构

在这里插入图片描述

BTree索引

参考另一篇博客：Java面试笔记自己用、第二阶段、4数据库相关

4、explain结果的各字段意义说明

explain + sql语句；

type：由最差到最好排序如下

5、索引失效相关问题

常见索引失效的原因：

针对以上情况，逐个说明：
（1）全值匹配
在这里插入图片描述
（2）最佳左前缀

（8）like的%最好是在右边，如果必须两边都有%，还要索引有效，就得建立组合索引，且查询条件小于等于这个索引

（9）varchar类型加’’

小总结

6、索引面试题分析

在这里插入图片描述

7、Join调优案例：

sql调优整体步骤：
在这里插入图片描述

1. 单表sql调优

需求：
在这里插入图片描述
（1）explain此sql

发现它的type是ALL，Extra是Using filesort,这两个都需要优化
（2）对查询条件中使用到到字段建立组合索引

（3）此时type已经优化成range，但extra还是Using filesort

（4）虽然说此时三个条件都有索引，但是范围筛选之后的索引会失效，比如看一下下面的=和>对比
在这里插入图片描述
（5）所以，我们不需要对范围筛选的字段建立索引，否则会影响后面的索引
先删除之前建立的组合索引，再新建不带范围筛选字段的组合索引，此时可以看到type成了最优的ref，extra也成了Using Where

2. 双表sql调优

在这里插入图片描述
需求：优化下面sql

（1）执行结果：type有ALL

（2）book表创建索引card，再分析

（3）给class的card也加个索引，发现rows又成了20

（4）删除索引，重新建个索引

在这里插入图片描述

3. 三表sql优化

在双表的基础上再加一个phone,此时有book、class、phone三张表
在这里插入图片描述
需求：优化此sql

（1）给phone、book添加索引card，再explain一次看看

（2）结论：多表join的优化尽量达到小结果驱动大的结果集，比如此案例class类别表肯定没有book书表的数据多，就用class驱动book