SQL优化笔记一：索引和explain
数据库方面，我会使用MySQL来讲解
为什么需要优化SQL
性能低，执行时间长，SQL语句写的垃圾(特别是嵌套查询)，索引失效，服务器参数不合理(缓存，线程数)
SQL优化的重点
SQL优化上面说了几个原因，其中最重要的就是索引的优化，索引就是汉语词典里面的目录，有目录，我可以很容易的找到想查的字，没有目录，只能一页一页的翻查，这样效率就很低了
索引
索引的结构
索引是数据结构，有B树，二叉树，Hash树等等，MySQL用的是B树里的B+树
B树的原理就是：小的放左边，大的放右边
例如下图，索引建立在age字段上，B树结构如右
上图很直观的看出，不加索引，查age=33需要5次，加了索引，查索引，只需要3次。这还是小数据量，假如是百万级别的数据量，那效率会非常高
索引的优缺点总结：
索引的优势：
- 提高查询效率（降低IO的使用率）
- 降低CPU的使用率（例如order by age desc，没有索引需要排序，有索引，B树本身就已经排好序了）
索引的弊端：
- 索引本身很大，存放在内存/硬盘之中(一般存放在硬盘)
- 少量数据时不适合使用索引，比如就几个数据，没必要
- 频繁更新的字段，不适合使用索引。比如上面的age字段，频繁更新的话索引也要跟着频繁更新，不适合
- 很少使用的字段不适合使用索引，比如上面的age字段，我查询的时候几乎不使用这个，那我在这个字段上建立索引干嘛
- 索引会提高查询的效率，但是会降低增删改的效率，每次的增删改都要对索引进行更新，不适合
索引的分类
- 单值索引：单列加索引，比如age字段加索引
- 唯一索引：也是单列加索引，但是这一列不能重复，一般是id列
- 复合索引：多个列构成的索引，比如 name+address，先根据名字查人，名字一样的话，再根据地址查人。
索引操作
以我的Message表为例，创建索引的模板如下：
方式一：create 索引类型 索引名 on 表(字段)
#创建单值索引
CREATE INDEX command_index on message(COMMAND)
#创建唯一索引
CREATE UNIQUE INDEX id_index on message(ID)
#创建复合索引
CREATE INDEX COMMAND_CONTENT_index on message(COMMAND,CONTENT)
方式二：alter table 表名 索引类型 索引名(字段)
#创建单值索引
ALTER TABLE message add INDEX command_index(COMMAND)
#创建唯一索引
ALTER TABLE message add UNIQUE INDEX id_index(ID)
#创建复合索引
ALTER TABLE message add INDEX COMMAND_CONTENT_index(COMMAND,CONTENT)
这两种创建索引的方式，使用哪个都可以
| 注意：如果一个字段是primary key，那么默认就是主键索引 |
主键索引和唯一索引差不多，唯一的区别就是主键索引不能为null
查询索引
show index from 表名
删除索引
drop index 索引名 on 表名
B树
实战
我们先来创建三个表
create table teacherCard(
tcid int,
tcdesc nvarchar(10)
)
create table teacher(
tid int,
tname nvarchar(10),
tcid int
)
create table course(
cid int,
cname nvarchar(10),
tid int
)
里面的数据自己填，这是我的
INSERT INTO test.course (cid, cname, tid) VALUES (1, '编曲', 1);
INSERT INTO test.course (cid, cname, tid) VALUES (2, '编程', 2);
INSERT INTO test.course (cid, cname, tid) VALUES (3, '作曲', 3);
INSERT INTO test.course (cid, cname, tid) VALUES (4, '遛狗', 1);
INSERT INTO test.teacher (tid, tname, tcid) VALUES (1, '许嵩', 1);
INSERT INTO test.teacher (tid, tname, tcid) VALUES (2, '蜀云泉', 2);
INSERT INTO test.teacher (tid, tname, tcid) VALUES (3, '林俊杰', 3);
INSERT INTO test.teachercard (tcid, tcdesc) VALUES (1, '许嵩最佳歌手');
INSERT INTO test.teachercard (tcid, tcdesc) VALUES (2, '蜀云泉只会编程');
INSERT INTO test.teachercard (tcid, tcdesc) VALUES (3, '林俊杰作曲');
问题
查询出课程编号为2或者教师证编号为3的老师的信息？（不要看答案，自己写写试试）
我的答案：
select t.* from course c inner join teachercard tc
on c.tid=tc.tcid and (c.cid=2 or tc.tcid=3) inner join teacher t on t.tcid=tc.tcid
这就是我第一反应的SQL水平.......
我们加一个执行计划explain来，试试结果。explain就是看一条SQL语句的执行计划的
explain select t.* from course c inner join teachercard tc
on c.tid=tc.tcid and (c.cid=2 or tc.tcid=3) inner join teacher t on t.tcid=tc.tcid
结果如下
把上述的SQL语句换成子查询
explain select tc.* from teachercard tc where tc.tcid=(
select t.tcid from teacher t where t.tid=(
select c.tid from course c where cname like '%编曲%'
)
)
结果如下
待续。。。
