2_Mysql_Join以及索引概念

1. MYSQL逻辑架构

1.连接层
最上层是一些客户端和连接服务，包含本地Sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在改层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层是吸纳基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
2.服务层
第二层架构主要完成大多的核心功能，如SQL接口，并完成缓存的查询，SQL的分析和优化及部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化，如确定查询的顺序，是否利用索引等，最后生成相应的执行操作。如果是select，服务器还会查询内部的缓存。如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。
3.引擎层
储存引擎层，储存引擎真正的负责了MYSQL中数据的储存和提取，服务器通过API于存储引擎进行通信。不同的存储引擎具有的功能不疼痛，这样我们可以根据自己的实际需要进行选取。

2. MYSQL存储引擎

2.1 查看引擎

	show engines;

2.2 查看默认引擎

	show variables like '%storage_engine%';

2.3 MyISAM 和 InnoDB

对比项	MyISAM	InnoDB
主外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作	行锁，操作时只锁某一行，不对其他行有影响
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据，对内存的要求较高，而且内存大小对性能有决定性组作用
表空间	小	大
关注点	性能	事务
默认安装	Y	Y

3 索引优化分析

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

1. 查询语句写的烂
2. 索引失效(单值，复合)
3. 关联查询太多join(设计缺陷或不得已的需求)
4. 服务器调优及各个参数设置(缓冲、线程数等)

4 常用的Join

4.1 mysql执行顺序

4.2 七种Join

4.2.0 P13 SQL

CREATE TABLE `tbl_emp` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`deptId` int(11) DEFAULT NULL,
PRIMARY KEY (`id`) ,
KEY `fk_dept_id`(`deptId`)
)ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8;

CREATE TABLE `tbl_dept` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`deptName` varchar(30) DEFAULT NULL,
`locAdd` varchar(40) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8;

4.2.1 A 全部

select * from tbl_dept a left join tbl_emp b on a.id  = b.deptId;

4.2.2 A 独有

select * from tbl_dept a left join tbl_emp b on a.id  = b.deptId where b.id is null

4.2.3 B 全部

select * from tbl_dept a right join tbl_emp b on a.id  = b.deptId;

4.2.4 B 独有

select * from tbl_dept a right join tbl_emp b on a.id  = b.deptId where a.id is null

4.2.5 A 、B 交集

select * from tbl_dept a inner join tbl_emp b on a.id  = b.deptId 

4.2.6 A 、B 全有

select * from tbl_dept a left  join tbl_emp b on a.id  = b.deptId
union 
select * from tbl_dept a right  join tbl_emp b on a.id  = b.deptId

4.2.7 A 、B 独有

select * from tbl_dept a left  join tbl_emp b on a.id  = b.deptId where b.id is null
union 
select * from tbl_dept a right  join tbl_emp b on a.id  = b.deptId where a.id is null

案例使用的SQL: https://blog.csdn.net/Pzzzz_wwy/article/details/106600571

5 索引

5.1 索引是什么

索引是帮助mysql高速获取数据的结构
索引本身也很大，不可能全部存储在内存中，因此索引往往以文件的形式存储在磁盘
所说的索引没有特别说明，都是指B树(多路搜索树，并不一定是二叉树)结构组织的索引。其中聚集索引，次要索引，覆盖索引。复合索引，前缀索引，唯一索引默认是使用B+索引，统称索引。

5.2 优势

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

5.3 劣势

• 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引也是占空间的
• 降低更新表的速度，对表进行insert、update、delete时，mysql不仅要保存数据，还会保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的的键值变化后的索引信息
• 索引只是提高效率的一个因素，需要花费大量的时间研究建立最优秀的索引

5.4 mysql索引分类

5.4.1 单值索引

即一个索引包含单个列，一个表可以有多个单列索引

5.4.2 唯一索引

索引列的值必须唯一，但允许有空值

5.4.3 复合索引

即一个索引包含多个列

5.4.4 基本语法

5.4.4.1 创建

create [unique] index indexName on table (columnname(length))
alter mytable add [unique] index indexName on (columnname(length))

5.4.4.2 删除

drop index [indexName] on myTable;

5.4.4.3 查看

show index from table_name\G

5.4.4.4 使用alter命令

alter TABLE tbl_name ADD PRIMART KEY (column_list); 该语句添加一个主键，这意味着所以只必须是唯一的，不能为null
alter TABLE tbl_name ADD unique index_name(column_list); 索引值必须是唯一的(除了NULL，NULL可能出现多次)
alter TABLE tbl_name ADD INDEX index_name (column_list); 添加普通索引，索引值可能出现多次
alter TABLE tbl_name ADD FULLTEXT  index_name (column_list); 指定索引值为 FULLTEXT 用于全文检索

5.4.5 建议

	一张表不要超过5个索引

5.5 索引结构

• BTree 索引
  类似二叉树
• Hash索引
• full-text全文索引
• R-Tree索引

5.6 适合建索引

• 主键自动创建唯一索引
• 频繁作为查询条件的字段
• 查询中与其他表关联的字段，外键关系建立索引
• 频繁更新的字段不适合创建索引(不但会更新数据还会更新索引)
• where条件里用不大的字段不创建索引
• 单键/组合索引的选择问题(高并发下倾向于组合索引)
• 查询中排序的字段，排序字段若通过索引访问大大提高排序速度
• 查询中统计或者分组字段

5.7 不适合建索引

• 表记录太少
• 经常增删改的表
• 数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据建立索引。注意：如果魔偶个数据列包含许多重复的内容，为他建立索引就没有太大的效果。

6 explain

6.1是什么

	使用explain关键字可以模拟优化器执行SQL查询语句，从而知道MYSQL是如何处理SQL，分析是查询语句或是表结构的性能瓶颈

6.2 能干嘛

• 表的读取顺序
• 数据读取操作的操作类型
• 那些索引可是使用
• 那些索引被实际使用
• 表之间的引用
• 每张表有多少行被优化器查询

6.3 怎么玩

	explain + SQL

6.4 字段解释

6.4.1 ID

• id相同，执行顺序由上而下
• 如果是子查询，id的序号会递增，id值越大有限级别越高，越先被执行
• id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

6.4.2 select_type

6.4.2.1 select_type 类型

 1. simple  简单的select查询，查询中不包括子查询或者union
 2. primary 查询中若包含任何复杂的子部分，最外层查询则被标记为
 3. subquery 在select或者where中包含子查询
 4. derived 在form 列表中包含的子查询被标记为derived(衍生) mysql会递归执行这些子查询，把结果放到临时表
 5. union 若第二个人delectable出现在union 之后，则被标记为union，若union包含在from子句的子查询中，外层select将被标记为derived
 6. union result 从union 表获取结果的select
	查询的类型主要用于区别
	普通索引、联合查询、子查询等的复杂查询

6.4.3 table

那张表

6.4.4 type

从最好到最差依次是
system>const>eq_ref>ref>range>index>all
一般来说，得保证查询至少达到range级别，最好能到达ref
 1. system  表只有一行记录(等于系统表)，这是const类型的特立，平时不会出现
 2. const 	  表示索引一次就找到了，const用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快。如将主键至于where列表中，mysql就能将该查询转换为一个常量
 3. eq_ref	唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
 4. ref			非唯一索引扫描，返回匹配某个单独值得所有行；本质上也是一种索引访问，他返回所有匹配某个单独值得行，然而，他可能会找到多个复合条件的行，所以他应该属于查找和扫描的混合体
 5. range	只检索给定范围的行，使用一个索引来选择行。key列显示使用了那个索引，一般就是在爱where语句中出现了between、<、>/in等的查询。这种范围扫描索引比全表扫描摇号，因为他只需要开始于索引的某一点，未结束另一点，不用扫描全部索引
 6. index	Full Index Scan ，index与All区别为index类型只遍历索引树。这通常比all块，因为索引文件通常比数据文件小。也就是说all和index嗾使读全表，但是index是从索引中读取的，而all是从硬盘中读取的
 7. all full table scan ，将遍历全表找到匹配的行

6.4.4 possible_keys

	显示可能应用在这张表中的索引，一个或多个
	查询涉及到的字段上若存在索引，**则索引被列出，但不一定被查询实际使用**

6.4.5 key

	实际用到的索引。如果为null，则没有使用索引
	查询中若使用了覆盖索引，则该索引仅出现在key列表中
	（索引的列与select后面的个数顺序一样就是覆盖）

6.4.6 key_len

	表示索引中适应的字节数，可通过该列计算查询中使用的索引长度。在不损失精确性的条件下，越短越好
	key_len显示的值为索引字段最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出来的

6.4.7 ref

	显示索引的那一列被使用了，如果可能的话，是一个常数。那些类或常量被用于查找索引列上的值
	（查询中与其他表关联的字段，外键关系建立索引）

6.4.8 rows

	柑橘表统计信息即索引选用情况，大致估算出找到所需的记录所需要读取的行数

6.4.9 Extra

	包含不适合在其他列中显示但是十分重要的额外信息
	
 1. Using filesort 说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引进行读取。mysql无法利用索引完成的排序操作称为“”“文件排序”。（如果索引是1,2，3，但是order by 1 则会显示 ，除非排序后面的字段要和索引一致）
 2. Using tempporary  使用临时表保存中间结果，mysql在对查询结果排序时使用临时表。常见于排序order by 和分组查询group by。
 3. Using index 表示相应的select操作中使用了覆盖索引，避免访问表的数据行。如果同时出现using where，表明索引被用来执行索引键值的查找，如果没有同时出现using where，表明索引用来读取数据，而非执行查找动作（覆盖索引：就是select的数据列只用从索引中就能取得，不必读取数据行，mysql可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖）
 4. Using where 使用了where
 5. Using join buffer 使用了连接索引 
 6. impossible where     where的值总是FALSE，不能用来获取元素
 7. select tables optimized away   在没有 group by子句的情况下，基于索引雨花MIN/MAX操作或者对于MyISAMYSQL存储引擎优化count(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化
 8. distinct 		优化distinct操作，再找到第一匹配的元祖后即停止找同样值得作用