我的SQL优化之路

我的SQL优化之路

大家好，我是Andy，那个永远积极向上且自恋的Andy

---

此博客记录我所理解与学习的一些SQL优化方面的知识（以下例子都在mysql 环境下测试）：

1.索引的使用

1.1 索引作用

      无索引：没有索引的表查数据时，会检索整个表，比如：表中数据有值1-100的数据，我要找到50这条数据，这时候会通过一条条的找，直到找到50 这条数据为止（当数据量大的时候，查询速度可想而知）。
      有索引：索引就像一本书，把整个表数据根据页数来分类，使用索引查询数据时，直接找到这条数据所在页码，再去搜索你需要的数据。比如：表中数据有值1-100的数据，我要找到50这条数据，索引把1-100分为了1-10页，这时候查找数据时会先找到第5页，然后再找到50这条数据。

1.2 索引类型

• 主键索引
• 普通索引
• 聚合索引（多列索引）
• 唯一索引
• 全文索引

唯一索引和全文索引使用较少，我们主要关注主键索引、普通索引和聚合索引。
1）主键索引：主键是加在主键上的索引，设置主键（primary key）时，mysql 会自动创建主键索引
2）普通索引：创建在非主键列上的索引
3）聚合索引：创建在多列上的索引

1.3 索引语法

• 查看某张表的索引：show index from 表名
• 创建普通索引：alter 表名 add index 索引名(加索引的列)
• 创建聚合索引：alter 表名 add index 索引名(加索引的列1,加索引的列2)
• 删除表中某个索引：drop index 索引名 on 表名

1.4 索引的实际测试

1）创建测试表：

CREATE TABLE `andy_user`(
    `id` BIGINT(20)PRIMARY KEY NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(20)DEFAULT NULL,
    `password` VARCHAR(50)DEFAULT NULL
) ENGINE = MyISAM DEFAULT CHARSET = utf8;

存储引擎使用的MyISAM ，此引擎没有事物等数据库高级应用，方便我们快速插入测试数据，待插入完数据把存储引擎改为InnoDB

2）测试数据插入（存储过程插入，此处仅插入10W 数据，因耗时太长，可自行增加数据测试）

CREATE PROCEDURE andy_user_addData_proc()
BEGIN
    DECLARE num INT;
    SET num = 1;
    WHILE num <= 100000 DO
        INSERT INTO andy_user(username, password) VALUES (num, PASSWORD(num));
        SET num = num + 1;
    END WHILE;
END;
call andy_user_addData_proc();

更改存储引擎

alter table andy_user engine=InnoDB;

3）SQL测试（本测试数据为10W，数据量越大差距越明显）
①主键索引 id列

select id,username,password from andy_user where id=99999

耗时0.014s

②普通索引列 username列
未加索引执行sql

select id,username,password from andy_user where username='99999'

耗时0.076s
下面我们给username 加上普通索引

ALTER TABLE `andy_user` ADD INDEX index_name(username);

再次执行
耗时0.014s

③聚合索引列 username + password
先删除username 的索引

drop index index_name on andy_user

执行没有索引的sql

select id,username,password from andy_user where username='99999' and password = '*54D8397040D8186C83AAA36B50EB087538E6E1DD'

耗时0.071s
下面我们给username +password 加上聚合索引

ALTER TABLE `andy_user` ADD INDEX index_union_name_password(username,password)

再次执行
耗时0.014s

学习SQL优化最主要学会查看执行计划，用desc 或 explain ，例如：

desc select id,username,password from andy_user where id=99999;
explain select id,username,password from andy_user where username='99999' and password = '*54D8397040D8186C83AAA36B50EB087538E6E1DD';

通过执行计划可以看到该语句查哪些表，各个表是全表扫描还是其他。所使用到的有哪些索引。检索了多少行。等等…

后续会补充其他所学习到的SQL优化，例如：造成索引没有被使用到的关键字、一些要注意的地方等