数据库优化之 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 的语句优化

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