Mysql（二）索引、查询

一、索引

概念： 索引时帮助MySQL高效获取数据的数据结构
本质： 数据结构
实现： 在存储引擎层面实现的，而不是server层面。不是所有的存储引擎都支持所有的索引类型。即使多个存储引擎支持某一索引类型，它们的实现和行为也可能有所差别。
目的： 提高查询效率
存储形式： 索引本身也很大，不可能全部存储在内存中，一般以索引文件的形式存储在磁盘上。
默认： B+树。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引
优势： 提高数据检索效率，降低数据库IO成本。降低数据排序成本，降低CPU的消耗。
劣势： 占用内存，提高了查询效率，降低了更新表的效率。

索引分类

数据结构角度

• B+数索引
• Hash树索引
• Full-Text全文索引
• R-Tree索引

物理存储角度

• 聚集索引
• 非聚集索引（辅助索引）
  PS：都是B+树索引

逻辑角度

• 主键索引：特殊的唯一索引，不允许有空值
• 普通索引（单列索引）：值包含单个列，一个表可以有多个单列索引
• 多列（复合）索引：复合索引指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用复合索引时遵循最左前缀集合。
• 唯一索引或非唯一索引
• 空间索引：空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。
  MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建

MySQL索引结构

B+Tree：MyISAM 和 InnoDB 存储引擎，都使用 B+Tree的数据结构，它相对与 B-Tree结构，所有的数据都存放在叶子节点上，且把叶子节点通过指针连接到一起，形成了一条数据链表，以加快相邻数据的检索效率。

B+和B-树的区别

B-Tree：为磁盘等外存储设备设计的一种平衡查找树。
B+Tree：B+Tree 是在 B-Tree 基础上的一种优化，使其更适合实现外存储索引结构，InnoDB 存储引擎就是用 B+Tree 实现其索引结构。

语法

创建

create [unique] index indexName on mytable(username(length));
如果是CHAR，VARCHAR类型，length可以小于字段实际长度；
如果是BLOB和TEXT类型，必须指定 length。

修改

alter table tableName ADD [unique] index indexName(columnName)

删除

drop index [indexName] on mytable

查看

show index from tableName\G
\G格式化输出信息

二、MySQL查询

count(*) 和 count(1)和count(列名)区别

执行效果上：

• count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL
• count(1)包括了所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL
• count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计。

执行效率上

• 列名为主键，count(列名)会比count(1)快
• 列名不为主键，count(1)会比count(列名)快
• 如果表多个列并且没有主键，则 count(1) 的执行效率优于 count(*)
• 如果有主键，则 select count（主键）的执行效率是最优的
• 如果表只有一个字段，则 select count(*) 最优。

MySQL中 in和 exists 的区别？

如果查询的两个表大小相当，那么用in和exists差别不大。
如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in：

• exists：exists对外表用loop逐条查询，每次查询都会查看exists的条件语句，当exists里的条件语句能够返回记录行时（无论记录行是的多少，只要能返回），条件就为真，返回当前loop到的这条记录；反之，如果exists里的条件语句不能返回记录行，则当前loop到的这条记录被丢弃，exists的条件就像一个bool条件，当能返回结果集则为true，不能返回结果集则为false
• in：in查询相当于多个or条件的叠加

UNION和UNION ALL的区别?

• UNION在进行表连接后会筛选掉重复的数据记录（效率较低），而UNION ALL则不会去掉重复的数据记录；
• UNION会按照字段的顺序进行排序，而UNION ALL只是简单的将两个结果合并就返回；

SQL执行顺序

内连接、外连接，交叉连接

内连接：返回两个表的交集（阴影）部分
左连接：左表的记录将会全部表示出来，而右表只会显示符合搜索条件的记录。右表记录不足的地方均为NULL
右连接：右表的记录将会全部表示出来，而左表只会显示符合搜索条件的记录。左表记录不足的地方均为NULL

全连接：MySQL目前不支持此种方式，可以用其他方式替代解决