MySQL高级

SQL性能下降原因

建索引（单值，复合）
查询更快：select * from user where name=’’ and email=’’;

单值
create index idx_user_name on user(name)
复合
create index idx_user_nameEmail on user(name，email)

七种JOIN理论

MySQL实现全连接

select * from tb1 a left join tb2 b on a.id=b.id
union
select * from tb1 a right join tb2 on a.id=b.id;

图7：
select * from tb1 a left join tb2 b on a.id=b.id where b.id is null
union
select * from tb1 a right join tb2 on a.id=b.id where a.id is null;

索引

本质：索引是数据结构，可以理解为排好序的快速查找数据结构
索引会影响到where后的查找和order by后的排序

删除其实只是逻辑上删除，给其一个标志位，物理上还是连续的，底层其实是update
索引往往以索引文件的形式存储在磁盘上

优势：

• 提高数据检索效率，降低数据库的IO成本
• 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

劣势：

索引分类

• 单值索引：一个索引只包含单个列，一个表可以有多个单值索引
• 唯一索引：索引列的值必须唯一，但允许有空值
• 复合索引：一个索引包含了多个列
• 基本语法：
  
  唯一索引加上UNIQUE
  
  索引结构
• BTree
• Hash
• full-text全文
• R-Tree

哪些情况需要建索引

复合索引，查询最好按照索引顺序，比如索引是name,age,email那么查询顺序最好为name,age,email

哪些不要建

经常增删改的表：

索引优化分析

EXPLAIN
作用：

语法：explain + sql

• id：
  
  t3最先被执行，t2最后被执行
  
  t3最先执行，衍生表是s1，其中derived2中的2表示id=2，id=2的表衍生出来的表
• select_type：
• 
  
  
• table：显示这一行的数据是关于哪张表的
• type：
  all表示全表扫描，百万级以上数据一定要优化
  
  
  
  const和system
  
  eq_ref和ref
  eq_ref表示查询结果中只有一个结果（主键和唯一键的情况下，非key显示ref）
  
  ref表示查询结果有多个也可以为一个（非主键和唯一键的情况下）
  
  range（此处age和stu都为索引）
  
  index（此处select返回的id是索引）
  
• possible_key：
• key:
  
  覆盖索引：select后查询的和索引顺序个数一致
  
  
  
• key_len：
• ref：
  
  test库的t1表的id字段
• rows：
• Extra：包含不适合在其他列中显示但十分重要的额外信息
  
  
  Using filesort
  
  排序也要按索引顺序和个数
  Using temporary
  
  分组也要按索引顺序和个数

重点id、type、key、rows和Extra

索引单表优化案例

查询category_id为1且comments大于1的情况下，views最多的article_id

type为all并且extra出现filesort，必须优化



不用带范围的做索引

两表优化

左连接给右表加索引

三表优化

索引优化

索引失效

1.全值匹配：索引是什么数量什么顺序，查询就按什么数量什么顺序

2.最佳左前缀法则：要想火车跑得快，全靠车头带，中间车厢不能断

3.不要操作索引列：
4.用不到索引中范围条件右边的列：in,like,>,<


5.尽量用覆盖索引：

6.使用不等于（!=,<>）时无法使用索引会导致全表扫描（x）：新版本优化了？

7.is null和is not null不能用索引：

8.用like语句通配符不能开头：like%加右边

对于like两边有百分号的情况，推荐用覆盖索引解决

9.字符串不加单引号会索引失效：不要在索引上类型转换

10.少用or，用or连接也会导致索引失效：新版本优化了？

总结

索引案例

都是常量所以顺序无所谓比如1+2+3=2+3+1=6，但是最好还是按索引写

不是常量顺序也没关系，优化器自己会优化，范围之后全失效，c3是倒数第二个，所以c4失效了；c4是最后一个索引，所以还是用了四个索引。

c3严格说也用到了，他作用是排序不是查找，和c4就完全没关系了

order by不按索引顺序，会产生filesort

只有c1一个字段索引，但是c2，c3用于排序，没有filesort

order by不按索引顺序，会产生filesort

c2此时已经是常量了，所以排序无所谓，直接找到const上c3

分组之前必排序，所以group by和order by几乎一致

一般性建议

查询截取分析

优化流程

优化原则：小表驱动大表

order by优化

order by默认升序，同时desc或者asc可以



总结

对于排序来说，多个相等条件也是范围查询

group by优化

慢查询日志

默认情况下MySQL不开启这个功能，如果不是调优需要建议关闭，因为会降低点性能
查询是否开启：show variables like ‘%slow_query_log%’;
开启：set global slow_query_log=1;

开启后只对当前数据库生效，重启后失效

什么样的SQL会被存入慢查询日志？

日志分析工具mysqldumpshow

常用操作

批量插入数据脚本

创建随机函数

创建插入存储过程（自动提交设为0，全部插入后提交）

用Show Profile进行SQL分析

分析步骤

2.

4.duration是用时

5.

参数列表

6.结论

全局查询日志

永远不要在生产环境开启这个功能，测试可以

锁理论

从对数据操作类型来分（读/写）：

对数据操作粒度分：表锁（偏读）、行锁（偏写）

增加锁
lock table 表名 read/write, 表名2 read/write;
查看表加的锁
show open tables;
解锁
unlock tables;

表锁（以下为MyISAM引擎）
读锁


写锁

总结



行锁（innodb引擎，默认自动提交）
就类似隔离级别

索引失效行锁变表锁

间隙锁的危害

什么是间隙锁

如何锁定某一行

锁了后别的会话一直等待

行锁总结

优化建议

主从复制

三步骤和原理：slave会从master读取binlog进行数据同步

基本原则

一主一从配置
参考：主从复制
注意：要关闭防火墙