mysql查询优化
查询sql的数学表达
1 SELECT A.,B. //投影
FROM A,B //笛卡尔积
WHERE A.c1=1 AND B.c1=2 //选择
UNION //并集，差集
SELECT A.,B.
FROM A,B
WHERE A.c1=1 AND B.c1=2 OR B.c1>100
mysql 逻辑架构
- 连接层，例如jdbc
- 服务层，如SQL接口，并完成缓存查询，SQL的分析和优化及部分内置函数的执行
- 引擎层，例如：MYISAM和InnoDB
- 存储层，数据存储层
MYISAM和InnoDB的主要区别
| 对比项 | MYISAM | InnoDB |
|---|---|---|
| 外键 | 不支持 | 支持 |
| 事物 | 不支持 | 支持 |
| 缓存 | 只缓存索引，不缓存数据 | 既缓存索引，也缓存数据 |
| 表空间 | 小 | 大 |
| 关注点 | 性能 | 事物 |
mysql执行解析顺序图
mysql执行解析顺序图
7种join理论
image.png
以下面两张表做演示
学生表
老师表
左外连接效果：右边为空时，以null 补全。
image.png
左外连接，只保留自己独有的数据
image.png
右外连接效果：左边为空时，以null 补全。
image.png
右外连接，只保留自己独有的数据。
image.png
内连接，取交集。
image.png
或者如下
image.png
全连接：取并集。(因为mysql不支持full join，这里我们使用union实现)
image.png
全连接：取各自独有的部分。(因为mysql不支持full join，这里我们使用union实现)
image.png
索引
索引是一种已经排好序的快速查找数据结构
索引的类型
- 单列索引：这个索引只包含一个列，一个表可以有多个单列索引
- 唯一索引：索引列的值，必须唯一，并且非空
- 复合索引 ：这个索引包含多个列
索引建立的原则
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应该建立索引
- 与其他表字段建立关联关系的字段应该建立索引
- 频繁更新的字段不适合建立索引
- where条件用不到的字段不建立索引
- 单列索引与复合索引的选择（高并发下倾向于用复合索引）
- 查询中的排序字段，该字段若通过索引去访问将大大加快效率
- 查询中统计或者分组字段
不适合建立索引的情况
- 表记录很少的情况下
- 经常增删改的表
- 数据重复且分布平均的表字段，那么建立索引也就无意义 比如性别，国籍等，因为索引的选择性太低了
使用explain来查看mysql的查询语句的执行计划，explain重要字段的解释
- id：id列数字越大越先执行，如果说数字一样大，那么就从上往下依次执行
- type：查询类型从最好到最差依次为system>const>eq_ref>ref>range>index>all
system:系统扫描，实际使用中几乎不存在
const: 常量扫描，表示通过一次索引查询就找到了，const用于比较primary或unique索引，因为只需要匹配一行数据 所以非常快，如将其置于where列中 mysql就可以将其转换为一个常量。
eq_ref：唯一键索引扫描，对于每一个索引键表中只有一条记录与之匹配，常见于主键和唯一键索引，
ref：非唯一性扫描，返回匹配某个单独值的行(可能是多条结果)，常见于使用非唯一索引
range：只检索给定范围的行，使用一个索引来选择行，一般就是在where语句中出现between, >, < ,in等查询，这种情况比全表扫描要好
index：index与all的区别是index遍历索引，这比all要快
all：全表扫描 - possible keys 可能被使用到的索引
- key：实际使用的索引，若使用了覆盖索引(查询列的顺序与个数与索引列的顺序和个数一致的情况) 则该索引仅出现在key栏目中，possible keys 中不出现。
- key_len 其值显示索引字段的最大可能长度（字节数），并非实际使用长度，长度约短越好
- ref 显示索引的哪一列被使用了，如果可能的话 是一个常数，哪些列或常量被用于查找索引列上的值。
- rows 大致扫描的行数
- Extra 其他额外很重要的信息(前三条重要 其余看看就行)。
using filesort:文件排序，这种情况 mysql会对数据使用外部的索引排序，而不是根据表内的索引排序进行读取。这种情况很差，什么时候会出现filesort？ 当我们使用order by时，如果用不到索引进行排序，那么mysql会使用文件排序。
using temporary：使用临时表保存中间结果，mysql对查询结果进行排序时使用临时表，常见于order by，group by。
using FileSort只是对数据使用外部的索引排序，但是Using temporary会将排序后的结果缓存于创建的一张临时表中，然后再删除临时表，这种情况相比于sing filesort更差
- using Index：表示相应的查询操作中使用了覆盖索引（Covering Index）来获取结果，避免查询表的数据行，效率不错，如果同时出现using Where，表明索引被用来执行索引键值的查找，如果没有同时出现Using Where，表明索引用来读取数据而非执行查找工作 也就是覆盖索引。
所以如果要使用覆盖索引，一般只取select 需要的列 而不用select *，因为不可能所有字段都一起做索引的，其次 多余字段也会消耗不必要的网络IO。
- using Where：使用where过滤条件
- using join buffer：使用了连接缓存（join太多个表，配置文件里面的JoinBuffer的值可以调大一点）
- Impossible Where：where子句的值总是false，不能获取任何数据 比如where 1 = 2
- 优化distinct操作，在找到第一个匹配的数据后即停止找同样值的动作
JOIN优化，
CREATE TABLE
class(
idint(11) NOT NULL AUTO_INCREMENT,
cardint(255) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4;
CREATE TABLE
book(
bookidint(11) NOT NULL AUTO_INCREMENT,
cardint(10) NOT NULL,
PRIMARY KEY (bookid)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4;
建立以上两张表，各随机插入20条数据，
不建立索引时：两张表均全表扫描
- 索引建在左表class时,左表使用索引index，但是两表扫描行数均为20行
ALTER TABLE book ADD INDEX Y(card);
image.png
- 索引建在右表book时,右表使用索引ref，左表扫描20行，右表扫描一行
ALTER TABLE book ADD INDEX Y(card);
image.png
我们知道ref的性能是优于index的，并且第二种情况扫描的行数更少，因此可以得出以下总结
连表(join)查询优化原则
- 小表驱动大表
- 左连接时索引应建在右表上(如果索引已经建立在左表上，那么换成右连接查询)，右连接索引应建在左表上(如果索引已经建立在右表上，那么换成左连接查询),这是由左连接特性决定的，左连接时left join用于确定搜索右表数据，左表的数据一定有 所以我们需要将索引加在右表上，右连接则相反。
- 调大join buffer设置
关于索引的一些原则
-
联合索引最左前缀原则:查询从最左前列开始并且不能跳过中间列， 如果有的中间列使用了返回查询(大于，小于 不等于)，那么后面的列将使用不到
联合索引的使用原则举例
以下补充索引对于排序时使用的情况，以KEY(a,b,c)为例子
会使用索引排序，不会产生filesort的情况
| 条件 | 备注 |
|---|---|
| order by a或者order by a,b或者order by a,b,c | 符合最左前缀原则 |
| order by a desc,b desc,c desc | 方向一致并且符合最左前缀原则 |
| where a= const order by b,c或者a=const and b=const order by c或者 a=const and b>const order by c | 最左前缀为常量，则其后面的列相当于前缀，这种情况也没问题 |
不会使用索引排序，会产生filesort的情况
| 条件 | 备注 |
|---|---|
| order by a desc,b asc,c desc | 方向不一致 |
| order by b,c或者order by a,c | 不符合最左前缀原则 |
| order by a,b,c,d | d不是索引的列 |
| where a in {...} order by b,c | 对于排序来说，多个条件相当于范围查询，这种情况也不符合最左前缀原则 |
对于group by(先排序后分组)其原则与order by几乎一致，也是最左原则什么的，注意的是：能在where里面过滤的就没必要在having里过滤
一些常用法则
- 不要在索引列上做函数计算和转换(隐式和显式都不要)，如：
EXPLAIN SELECT * FROM person t WHERE LEFT(t.name,3) = "abc"
EXPLAIN SELECT * FROM person t WHERE t.name = 12 - <>和!= 会使索引失效
- is null 和 is not null 也会导致索引失效
- or 也会使索引失效
- in 和exists,根据小表驱动大表原则，当department为小表时 in要好于exists，当employee 为小表是exists要好于in
SELECT * FROM employee WHERE r_id in( SELECT r_id FROM department)相当于 先执行 SELECT * FROM department 再执行SELECT * FROM employee WHERE employee.r_id = department.r_id
SELECT * FROM employee t1 WHERE EXISTS (SELECT 1 FROM department t2 where t1.r_id = t2.r_id ) 相当于 SELECT * FROM employee 再执行 SELECT * FROM department WHERE employee.r_id = department.r_id
锁
- 从功能上分：
- 读锁(共享锁)：多个读操作之间可以同时进行，不会互相干扰
- 写锁(排它锁)：在当前写操作没完成前，会阻断其他写锁和读锁
- 从粒度上分大致分为
- 行锁(偏写)：
- 表锁(偏读)：
对于myisam(表级锁，不支持事务)：在执行查询语句(select)前，会自动给表加读锁，在执行写操作前会自动给表加写锁
myisam 读锁
LOCK TABLE book READ;
SELECT * from book where id =1 ; -- 可以查出结果
SELECT * FROM test2 where id =1 ; -- 不能查出其他表结果
INSERT INTO book VALUES (1,"1","1"); -- 读锁不能修改数据
UNLOCK TABLES ;
对于其他session
SELECT * from book where id =1 ; -- 可以查出结果
SELECT * FROM test2 where id =1 ; -- 可以查出结果
INSERT INTO book VALUES (1,"1","1"); -- 阻塞直到上面的读锁被释放
myisam 写锁
LOCK TABLE book WRITE;
SELECT * from book where id =1 ; -- 可以读取结果
INSERT INTO book VALUES (2,"2","2"); -- 可以更改数据
SELECT * FROM test2 where id =1 ; -- 不能获取其他表的数据
UNLOCK TABLES;
对于其他session
SELECT * FROM test2 where id =1 ; -- 可以查出结果
SELECT * from book where id =1 ; -- 阻塞 读写均阻塞
对于innodb(行级锁，支持事务)
session1
SET autocommit = 0; -- 取消自动提交
update people SETname= "a" WHERE id = 1; -- 更新1号记录
COMMIT; -- 提交
session2
SET autocommit = 0; -- 取消自动提交
UPDATE people SETname= "b" WHERE id = 2; -- 更新2号记录(因为innodb使用的是行锁，因此此时不阻塞)
COMMIT; -- 提交
注意
innodb使用的是行锁，但是行锁会升级到表锁。使用行锁的前提是where后面使 用到索引，如果限制列没建立索引或者有索引但是未使用到，那么mysql的行锁将会升级到表锁。
使用for update 来锁定select的行(使用的时候要注意加限制条件，避免锁全表)
session1
SET autocommit = 0; -- 取消自动提交
SELECT * FROM people WHERE id = 1 FOR UPDATE; -- 锁定id为1的这一行 ，COMMIT;
session2
SET autocommit = 0; -- 取消自动提交
UPDATE people SETname= "xxx" WHERE id = 1; -- 此时阻塞，直到session1 提交才会暂停阻塞
COMMIT; -- 提交
