关于mysql:mysql总结

5次阅读

共计 3536 个字符，预计需要花费 9 分钟才能阅读完成。

查问表 A 和表 B 共有

` select * from t_emp a inner join t_dept b on a.deptId = b.id;`

A、B 两表共有 + A 的独有

 select * from t_emp a left join t_dept b on a.deptId = b.id;

A、B 两表共有 + B 的独有

 select * from t_emp a right join t_dept b on a.deptId = b.id;

A 的独有

select * from t_emp a left join t_dept b on a.deptId = b.id where b.id is null;

B 的独有

 select * from t_emp a right join t_dept b on a.deptId = b.id where a.deptId is null;

A 独有 + B 独有

SELECT * 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;

索引（Index）是帮忙 MySQL 高效获取数据的数据结构。能够失去索引的实质：索引是数据结构。

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

右边是数据表，一共有两列七条记录，最右边的是数据记录的物理地址。为了放慢 Col2 的查找，能够保护一个左边所示的二叉查找树，每个节点别离蕴含索引键值和一个指向对应数据记录物理地址的指针，这样就能够使用二叉查找在肯定的复杂度内获取到相应数据，从而疾速的检索出符合条件的记录。

索引的毛病

实际上索引也是一张表，该表保留了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。
尽管索引大大提高了查问速度，同时却会升高更新表的速度，如对表进行 INSERT、UPDATE 和 DELETE。
因为更新表时，MySQL 不仅要保留数据，还要保留一下索引文件每次更新增加了索引列的字段，
都会调整因为更新所带来的键值变动后的索引信息

索引的分类

主键索引：设定为主键后数据库会主动建设索引，innodb 为聚簇索引
单值索引：即一个索引只蕴含单个列，一个表能够有多个单列索引
惟一索引：索引列的值必须惟一，但容许有空值
合乎索引：即一个索引蕴含多个列

创立索引

CREATE INDEX 索引名称
ON 表名称 (列名称)

删除索引

DROP INDEX 索引名称 ON 表名称;

查看索引

SHOW INDEX FROM 表名称

一颗 b 树，浅蓝色的块咱们称之为一个磁盘块，能够看到每个磁盘块蕴含几个数据项（深蓝色所示）和指针（黄色所示）
如磁盘块 1 蕴含数据项 17 和 35，蕴含指针 P1、P2、P3，P1 示意小于 17 的磁盘块，P2 示意在 17 和 35 之间的磁盘块，P3 示意大于 35 的磁盘块。
B 树绝对于均衡二叉树，每个节点存储了更多的键值（key）和数据（data），并且每个节点领有更多的子节点，子节点的个数个别称为阶，上述图中的 B 树为 3 阶 B 树。

所有 data 信息都挪动叶子节点中，而叶子节点和子节点之间会有个指针指向，这个也是 B + 树的外围点，这样能够大大晋升范畴查问效率，也不便遍历整个树。

在 B + 树中，所有数据记录节点都是依照键值大小程序寄存在同一层的叶子节点上，而非叶子节点上只存储 key 值息，这样能够大大加大每个节点存储的 key 值数量，升高 B + 树的高度。

非叶子节点不再存储数据，数据只存储在同一层的叶子节点上;
叶子节点之间，减少了链表，获取所有节点;

哪些状况须要创立索引

主键主动建设惟一索引
频繁作为查问条件的字段应该创立索引(where 前面的语句)
查问中与其它表关联的字段，外键关系建设索引
查问中排序的字段，排序字段若通过索引去拜访将大大提高排序速度
查问中统计或者分组字段

哪些状况不须要创立索引

表记录太少
常常增删改的表
Where 条件里用不到的字段不创立索引
数据反复且散布均匀的表字段

应用 EXPLAIN 关键字能够模仿优化器执行 SQL 查问语句，从而晓得 MySQL 是如何解决你的 SQL 语句的。剖析你的查问语句或是表构造的性能瓶颈

执行打算蕴含的信息

各字段解释

id

查问的序列号, 蕴含一组数字，示意查问中执行 select 子句或操作表的程序
- id 雷同，执行程序由上至下
- id 不同，如果是子查问，id 的序号会递增，id 值越大优先级越高，越先被执行
- id 雷同和不同的同时存在：id 如果雷同，能够认为是一组，从上往下程序执行；在所有组中，id 值越大，优先级越高，越先执行

select_type
- SIMPLE：简略的 select 查问, 查问中不蕴含子查问或者 UNION
- PRIMARY：查问中若蕴含任何简单的子局部，最外层查问则被标记为 Primary
- DERIVED：在 FROM 列表中蕴含的子查问被标记为 DERIVED(衍生)
- SUBQUERY：在 SELECT 或 WHERE 列表中蕴含了子查问
- UNION：若第二个 SELECT 呈现在 UNION 之后，则被标记为 UNION；
- UNION RESULT：从 UNION 表获取后果的 SELECT
type

type 显示的是拜访类型，是较为重要的一个指标，后果值从最好到最坏顺次是：

system>const>eq_ref>ref>range>index>ALL
- system：表只有一行记录（等于零碎表），这是 const 类型的特列，平时不会呈现，这个也能够忽略不计
- const：示意通过索引一次就找到了,const 用于 primary key 或者 unique 索引。因为只匹配一行数据，所以很快
- eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或惟一索引扫描
- ref：非唯一性索引扫描，返回匹配某个独自值的所有行。实质上也是一种索引拜访，它返回所有匹配某个独自值的行。
- range：只检索给定范畴的行, 应用一个索引来抉择行
- index：index 与 ALL 区别为 index 类型只遍历索引树。这通常比 ALL 快，因为索引文件通常比数据文件小。
- all：将遍历全表以找到匹配的行
possible_keys
显示可能利用在这张表中的索引，一个或多个。查问波及到的字段上若存在索引，则该索引将被列出，但不肯定被查问理论应用
key
理论应用的索引。如果为 NULL，则没有应用索引
key_len
示意索引中应用的字节数，可通过该列计算查问中应用的索引的长度。
ref
显示索引的哪一列被应用了，也可能是一个常数。哪些列或常量被用于查找索引列上的值
Extra

蕴含不适宜在其余列中显示但非常重要的额定信息
- Using filesort：阐明 mysql 会对数据应用一个内部的索引排序，而不是依照表内的索引程序进行读取。
- Using temporary：使了用长期表保留两头后果,MySQL 在对查问后果排序时应用长期表。常见于排序 order by 和分组查问 group by。
- USING index：示意相应的 select 操作中应用了笼罩索引(Covering Index)。
- Using where：表明应用了 where 过滤

建设索引的程序和查问的程序一样

索引 idx_staffs_nameAgePos 建设索引时 以 name，age，pos 的程序建设的。查问时依照建设的程序
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July';
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25;
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25 AND pos = 'dev';