Mysql优化

查询慢的原因

• 用户访问量激增导致查询慢，解决方法就是MySQL集群
• 如果查询慢跟表中数据量有关，可以考虑查询语句优化

判断查询语句是否需要优化

• 慢查询记录
• explain执行计划
• 相关日志文件

七个查询命令中，哪些命令执行效率慢

• 执行速度最慢的是group by，group by先排序，然后分组
• order by，原因是order by还是要排序，将排序好的数据变成一个全新的临时表
• where，需要对表中所有数据行进行遍历，如果临时表中数据行过多就会慢
• limit，如果指定的起始行数过大，也会导致变慢
• 多表查询时，如果没有指定文件的加载顺序，也会导查询过慢

查询命令执行慢的原因

• 执行时需要对所有的数据进行排序
• 对所有的数据行进行遍历
• I/O流加载文件次数

索引的语句

//查看文件中已经存在的索引
show index from 表;

//创建索引
create index 索引名 on 表名(字段);

//删除索引
drop index 索引名 on 表名;

索引的分类

• 聚簇索引与非聚簇索引
• 主键索引，唯一性索引
• 单个索引，复合索引

聚簇索引和非聚簇索引

聚簇索引：

• 只能来自于innoDB存储引擎表的数据
• MySQL自动将采用了innoDB存储引擎表中主键建立索引，这个索引就是聚簇索引
• 如果当前表中没有主键，MySQL将会选择一个添加唯一性约束的字段作为聚簇索引
• 如果当前表中既没有主键字段，也没有唯一性约束的字段，MySQL会随机选取一个字段作为聚簇索引
• 在采用INNODB存储引擎的表文件中，必然会存在一个聚簇索引
• 在表文件其它字段上建立的索引都是非聚簇索引

非聚簇索引：

• 由开发人员自行创建
• 对于采用了INNODB存储引擎表，除了一个聚簇索引之外，其它字段上创建的索引都是非聚簇索引
• 在采用MyISAM存储引擎的表中，创建的所有索引都是非聚簇索引

聚簇索引和非聚簇索引的区别：

• 聚簇索引：数据节点存储的是当前数据所在行数以及当前数据所在行内容
• 非聚簇索引：数据节点存储的是当前数据所在行数
• 聚簇索引不需要I/O流到硬盘文件上进行读取，因此执行效率较快

主键索引与唯一性索引效率

执行速度：
主键索引效率高于唯一性索引，唯一性索引效率高于普通索引

单字段索引和复合索引

//单字段索引创建
create index 索引名 on 表名(字段名);

//复合索引创建
create index 索引名 on 表明(字段名1，字段名2，。。。)

BTREE算法

HASH算法

explain执行计划

通过explain查看语句执行的效率，优化sql，通过explain可以查看一下信息：

• 查看表的加载顺序
• 查看sql的查询类型
• 哪些索引可能被使用，哪些索引实际使用了
• 表之间的引用关系
• 一个表中有多少行被优化器查询
• 其它额外的辅助信息

explain中的id属性：

作用：可以通过id来判断查询语句中多表的加载顺序

explain中的select_type

对当前查询语句中的查询类型进行判断
simple：表示当前查询语句是一个简单查询语句，不包含子查询，不包含联合查询，不包含连接查询

primary：如果执行的是一个包含子查询的查询，或者是已给联合查询，primary指向的是外部查询语句或联合查询的第一个子查询语句

dependent subquery：表示当前查询语句是一个子查询，并且执行条件依赖于外部查询提供的条件

subquery：表示当前是一个子查询，并且这个子查询不依赖于外部查询

explain中的type

type表示MySQL对本次查询的评价

• all：全表查询，最差
• index：定位数据行没有用索引，但是在已经定位的数据行读取字段时使用了索引
• range：定位数据行，使用到索引，使用区间定位的方式，where age <= 23
• ref：定位到数据行，使用到索引，使用值等方式，where name = “”
• const：定位数据行，使用到索引，采用主键值等方式，主键是聚簇索引，直接保存了数据值，所以效率更高
• system：操作表只有一行数据，并且数据内容是固定不变的

索引使用原则

//如果索引字段使用了函数，导致索引失效，下面语句因使用了upper函数，导致没有索引
explain select * from emp where upper(ename) = "SMITH"

//如果索引字段上使用运算，导致索引失效
explain select * from emp where empno+1=110

//如果索引字段上产生了隐式类型转换，导致索引失效，ename为varchar，而20为int
explain select * from emp where ename=20

//如果索引字段上进行了模糊查询，只支持前置条件模糊查询
explain select * from emp where ename like "a%"//使用了索引
explain select * from emp where ename like "%a"//未使用索引
explain select * from emp where ename like "%a%"//未使用索引