关于mysql的查询优化和一些常见的坑

普遍的sql优化防范

首先，如何查看sql效率

explain select * from auth_code where code='1234';

在要执行的sql前加入explain，查看sql执行计划，接下来分析其中的字段
对explain对此不做详解，引用此篇文章

MYSQL explain详解

对表建立索引

在建表时添加索引

CREATE TABLE `auth_code`
(
    id         varchar(32)  NOT NULL,
    email      VARCHAR(100) NOT NULL comment '用户id',
    code       varchar(4) DEFAULT NULL comment '验证码',
    updateTime datetime   DEFAULT NULL comment '创建时间',
    PRIMARY KEY (`id`),
    index index_email (email)
) ENGINE = INNODB
  DEFAULT CHARSET = utf8 comment ='验证码信息表';

此处，PRIMARY KEY (id)，指定id为主键， index email (email)指定一个索引名称叫index_email,指定索引字段为email

对已经建好的表添加索引

ALTER TABLE `auth_code` ADD INDEX index_email (email);

是否增加索引的字段作为where条件的表现差异

首先使用未增加索引的字段code作为where条件

explain select * from auth_code where code='1234';

可以看到执行后的type等级为ALL，全盘扫描。

接下来使用作为索引条件的email为where条件

explain select * from auth_code where email='1234@1234.1234';

可以看到执行后的type等级为ref,提升到了不止一个层级。

但是！sql优化是否增加的索引越多越好？，错！

我们来看一下这个搜索案例
首先对我们之前的表增加索引字段为code，然后执行

alter table auth_code add index code(code)

explain select * from auth_code where code='123'and email='123'

用EXPLAIN分析后发现，此时使用的索引是email，而不是code,命中率为0，相当于进行了全表扫描。
此时，我们就需要指定code为索引字段

explain select * from auth_code use index(code)  where code='123'and email='123'

通过添加use index(code)，指定使用索引为code,此时，我们的命中率提高，查询效率自然就提高了

同时，增加的索引字段越多，表占用磁盘的体积就越大，并且每次发生数据变更之后，都会导致数据库底层大量的重新计算的操作，反而会占用跟多的cpu和内存资源。因此，建立索引一定要多方面考虑，同时，一张表的字段过多的时候，开发人员写sql的时候就会发生索引选择不准确的情况，需要维护人员检测到高占用后对sql进行二次优化，严重的时候可能导致服务器的宕机。
文章来自我的博客：深的空间