数据库优化之explain 的使用和常用的SQL优化或索引优化

数据库优化之explain 的使用和常用的SQL优化或索引优化

explain 的使用

id：SQL语句使用的索引

select_type：SIMPLE

table：students，当前查询的表

type：ALL：全表扫描，效率不高 system：表是系统表 const：表最多有一个匹配行

possible_keys：查询时，可能用到的索引

key：实际使用的索引

key_len：索引字段的长度

rows：扫描的行数，从几行中查出的结果

ref：关联次数

Extra：如果包含where就是using where ：包含order by

Mysql索引

索引类型

1. 普通索引：index
2. 唯一索引：unique，此列是具有唯一性的列，列中每个数据记录都是唯一的。
3. 全文索引，仅MyISAM引擎支持，主要用于外文环境。

索引的优缺点

优点

高效。

缺点

占用空间，对修改删除等操作有一定影响。

索引适合添加到哪些字段上

1. 经常被查询的字段适合用来添加索引
2. 更新频繁的字段不适合创建索引
3. 不会出现在where字句中的字段，不该创建索引。
4. 唯一性太差的字段，即字段中重复元素过多的字段，不适合创建索引。

索引的一些注意事项

1. 如果模糊查询中使用like “%aabb” 就不会使用索引，但是用like "aabb%"则会使用索引。
2. where语句条件中，如果使用or，则不会使用索引。

衡量索引有效性的指标

Handler_read_key：这个值越高越好，是索引的使用次数

Handler_read_rnd_next：这个值越低越好

常用的SQL优化

1. 大批量插入数据时的优化

如果是MyISAM引擎，我们最好在导入大批量数据之前把keys禁用，在完成导入之后，再开启索引。如果使用的是InnoDB引擎，我们可以将数据的主键排序等关闭，并且关闭自动提交事务。

另外一点需要强调的是，我们在使用MyISAM引擎时，如果将某列删除，其占用的空间不会自动删除，这就需要我们定期清理这部分空间。其指令是optimize table 表名;

2. 优化gruop by语句

我们在不需要将插入的记录进行排序时，可以在gruop by语句中使用order by null。

3. 使用join代替子查询

如标题

4. 采用or的语句优化

其中的每个条件都必须使用索引，这样才能使索引生效。