MYSQL优化原理和执行计划分析(一)

索引基础

性能下降SQL慢执行时间长等待时间长

1.  查询数据过多           (能不能拆，条件过滤尽量少)
2. 关联了太多的表，太多join  （join 原理。用  A 表的每一条数据 扫描 B表的所有数据。所以尽量先过滤，IO数量大）
3. 没有用到索引（索引针对 列 建索引。但并不可能每一列都建索引
   索引并非越多越好。当数据更新了，索引会进行调整。也会很消耗性能。
   且 mysql 并不会把所有索引都用上，只会根据其算法挑一个索引用。所以建的准很重要。）
4. 服务器调优及各个参数设置（缓冲,线程数配置   DBA工作）

常见通用的join查询

 

1. SQL执行顺序  手写
2.  
3. SQL执行顺序   优化器 编译
4. join图
5. 建表语句
6. CREATE TABLE `t_dept` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `deptName` VARCHAR(30) DEFAULT NULL,
    `address` VARCHAR(40) DEFAULT NULL,
    PRIMARY KEY (`id`)
   ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
7.  
8. CREATE TABLE `t_emp` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(20) DEFAULT NULL,
     `age` INT(3) DEFAULT NULL,
    `deptId` INT(11) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `fk_dept_id` (`deptId`)
    #CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
   ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
9. 七中join

10.  
    1 A、B两表共有
     select * from t_emp a inner join t_dept b on a.deptId = b.id;
     
    2 A、B两表共有+A的独有
     select * from t_emp a left join t_dept b on a.deptId = b.id;
     
    3 A、B两表共有+B的独有
     select * from t_emp a right join t_dept b on a.deptId = b.id;
     
    4 A的独有 
    select * from t_emp a left join t_dept b on a.deptId = b.id where b.id is null; 
     
    5 B的独有
     select * from t_emp a right join t_dept b on a.deptId = b.id where a.deptId is null;  
     
    6 AB全有
    #MySQL Full Join的实现 因为MySQL不支持FULL JOIN,下面是替代方法
     #left join + union(可去除重复数据)+ right join
    SELECT * FROM t_emp A LEFT JOIN t_dept B ON A.deptId = B.id
    UNION
    SELECT * FROM t_emp A RIGHT JOIN t_dept B ON A.deptId = B.id
     这里因为要联合的缘故，不能考虑到小表驱动大表的情况。只能用right join。要保证查询出来的数字要一致。
    7 A的独有+B的独有
            * FROM t_emp A LEFT JOIN t_dept B ON A.deptId = B.id WHERE B.`id` IS NULL
    UNION
    SELECT * FROM t_emp A RIGHT JOIN t_dept B ON A.deptId = B.id WHERE A.`deptId` IS NULL;

 

索引简介

是什么？ 索引是数据结构

A:索引的目的在于提高查询效率，可以类比字典，
 
如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。
 
如果没有索引，那么你可能需要a----z，如果我想找到Java开头的单词呢？或者Oracle开头的单词呢？
 
是不是觉得如果没有索引，这个事情根本无法完成？

 

B:详解

 

 在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，
这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。下图就是一种可能的索引方式示例：

左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址
 为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指