七种连贯

查问表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 NULLUNIONSELECT * 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树

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

B+树

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

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

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

哪些状况须要创立索引

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

哪些状况不须要创立索引

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

Explain

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

执行打算蕴含的信息

各字段解释

  1. id

    查问的序列号,蕴含一组数字，示意查问中执行select子句或操作表的程序

    • id雷同，执行程序由上至下

    • id不同，如果是子查问，id的序号会递增，id值越大优先级越高，越先被执行

    • id雷同和不同的同时存在：id如果雷同，能够认为是一组，从上往下程序执行；在所有组中，id值越大，优先级越高，越先执行

  1. select_type

    • SIMPLE：简略的 select 查问,查问中不蕴含子查问或者UNION

    • PRIMARY：查问中若蕴含任何简单的子局部，最外层查问则被标记为Primary

    • DERIVED：在FROM列表中蕴含的子查问被标记为DERIVED(衍生)

    • SUBQUERY：在SELECT或WHERE列表中蕴含了子查问

    • UNION：若第二个SELECT呈现在UNION之后，则被标记为UNION；

    • UNION RESULT：从UNION表获取后果的SELECT

  2. 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：将遍历全表以找到匹配的行
  3. possible_keys

    显示可能利用在这张表中的索引，一个或多个。查问波及到的字段上若存在索引，则该索引将被列出，但不肯定被查问理论应用

  4. key

    理论应用的索引。如果为NULL，则没有应用索引

  5. key_len

    示意索引中应用的字节数，可通过该列计算查问中应用的索引的长度。

  6. ref

    显示索引的哪一列被应用了，也可能是一个常数。哪些列或常量被用于查找索引列上的值

  7. 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';
  • 最佳左前缀法令：如果索引了多列，要恪守最左前缀法令。指的是查问从索引的最左前列开始并且不跳过索引中的列。
  • 不在索引列上做任何操作（计算、函数、(主动or手动)类型转换），会导致索引生效而转向全表扫描

  • 存储引擎不能应用索引中范畴条件左边的列

咱们以name ， age ，pos 的程序建设索引，因为应用了age>11这个条件，pos索引将不能用到

  • 尽量应用笼罩索引(只拜访索引的查问(索引列和查问列统一))，缩小select *
  • mysql 在应用不等于(!= 或者<>)的时候无奈应用索引会导致全表扫描
  • is not null 也无奈应用索引,然而is null是能够应用索引的
  • like以通配符结尾('%abc...')mysql索引生效会变成全表扫描的操作
  • 字符串不加单引号索引生效： 底层进行转换使索引生效，应用了函数造成索引生效，相当于在列上进行了操作导致索引生效。