mysql索引学习_key `msgidkey` (`msgid`) using btree-CSDN博客

本文链接：https://blog.csdn.net/zajiayouzai/article/details/80930439

一、索引类型

1、唯一索引（主键）

建表语句：

CREATE TABLE `person` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

2、普通索引

建表语句：

CREATE TABLE `person` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY (`NAME`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

添加索引：

CREATE INDEX `NAME` ON person(NAME)

3、全文索引

建表语句：

CREATE TABLE `person` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(20) DEFAULT NULL,
  `INTRODUCE` varchar(100),
  PRIMARY KEY (`ID`),
  KEY (`NAME`),
  FULLTEXT KEY (`INTRODUCE`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

添加索引：

CREATE FULLTEXT INDEX `INTRODUCE` ON person(INTRODUCE);

4、组合索引

建表语句：

CREATE TABLE `person` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(20) DEFAULT NULL,
  `INTRODUCE` varchar(100) DEFAULT NULL,
  `AGE` int(2) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `NAME` (`NAME`) USING BTREE,
  KEY `NAME_AGE` (`NAME`,`AGE`) USING BTREE,
  FULLTEXT KEY `INTRODUCE` (`INTRODUCE`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

添加索引：

CREATE INDEX `NAME_AGE` ON person(NAME,AGE)

二、索引原理

1、数据结构

学习索引原理之前，先了解一些概念：BTREE、B-TREE、B+TREE、B*TREE：

1）BTREE

BTREE即为二叉搜索树（二叉查询树等），基本结构：
这里写图片描述

所有非叶子结点至多拥有两个儿子（Left和Right）；
所有结点存储一个关键字；
非叶子结点的左指针指向小于其关键字的子树，右指针指向大于其关键字的子树；

2）B-TREE

B-TREE是一种多路搜索树（并不是二叉的），基本结构（D=3）：
这里写图片描述

D为大于1的一个正整数，称为B-Tree的度；
H为一个正整数，称为B-Tree的高度；
根结点的key数为[2, D]；
每个非叶子结点最多只有D个key；且D>2；
每个叶子节点最少包含一个key，最多包含2D-1个key，叶节点的指针均为null
指针数等于key数+1

它的特性是关键字集合分布在整颗树中，任何一个关键字出现且只出现在一个结点中，这样搜索有可能在非叶子结点结束。

3）B+TREE

B+树是B-树的变体，也是一种多路搜索树，基本结构：
这里写图片描述
其定义基本与B-TREE相同，除了非叶子结点的子树指针与关键字个数相同；

它的特性是所有关键字都出现在叶子结点的链表中（稠密索引），且链表中的关键字恰好是有序的，索引不可能在非叶子结点命中。

4）B*TREE
这里写图片描述
是B+树的变体，在B+树的非根和非叶子结点再增加指向兄弟的指针。

2、索引原理

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。也就是说：索引是一种数据结构。

1）MyISAM的索引

MyISAM引擎使用B+Tree作为索引结构，叶结点的data域存放的是数据记录的地址，所以说MyISAM的索引文件仅仅保存数据记录的地址，它的数据和索引文件是分开的。在MyISAM中，主索引和辅助索引在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。

因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，取出地址值，读取相应数据记录，而地址值不是按照索引顺序分布的，MyISAM的索引方式也叫做非聚集索引。

2）InnoDB索引

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。第一个重大区别是InnoDB的数据文件本身就是索引文件，这棵树的叶结点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。

InnoDB的这种索引叫做聚集索引，因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键。

3）非聚集索引和聚集索引

从上面了解，聚集索引就像字典中的拼音搜索页，拼音的顺序与页码的顺序一直；非聚集索引就像字典中的偏旁部首搜索页，偏旁部首的顺序并不与页码顺序对应。所以这也就应对了MyISAM和InnoDB存储引擎的优缺点，InnoDB可以直接根据索引迅速找到硬盘上存储位置进行INSERT或UPDATE，所以说InnoDB更适用大量的存储和更新操作。

三、执行计划

执行计划就是SQL在数据库中执行时的表现情况，通常用于SQL的性能分析、优化等场景，Mysql通过explain命令输出执行计划，对要执行的查询进行分析。

1、Mysql逻辑架构
在这里插入图片描述

客户端
如连接处理、授权认证、安全等功能。
核心服务
mysql大多数核心服务均在这一层，包括查询解析、分析、优化、缓存、内置函数（时间、数学、加密等），所有的跨存储引擎的功能也在这一层，如存储过程、触发器、视图等。
存储引擎
负责mysql中的数据存储和读取，中间的服务通过api与存储引擎通信，这些api屏蔽了不同存储引擎间的差异。

2、查询过程
在这里插入图片描述

查询过程如下：

客户端将查询发送到mysql服务器
服务器先检查查询缓存，如果命中，立即返回缓存中的结果；否则进入下一阶段
服务器对SQL进行解析、预处理，再由优化器生成对象的执行计划
MySQL根据优化器生成的执行计划，调用存储引擎API来执行查询
服务器将结果返回给客户端，同时缓存查询结果

四、EXPLAIN

输出字段解释：

1、id

select查询序列号，id相同，执行顺序由上至下；id不同，id值越大优先级越高，越先被执行。

2、select_type

查询数据的操作类型：

simple
简单查询，不包含子查询或union
primary
包含复杂的自查询，最外层查询标记为该值
subquery
在select或where里面包含子查询，被标记为该值
derived
在from列表中包含的子查询被标记为该值，mysql会递归执行这些子查询，把结果放入临时表中
union
若第二个select出现union之后，则被标记为该值；若union包含在from的子查询中，外层select被标记为derived
union result
从union表获取结果的select

3、table

显示该杭数据是关于哪张表

4、partitions

匹配的分区

5、type

表的连接类型，其值、性能由高到低排序，system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL：

system
表只有一行记录，相当于系统表
const
通过索引一次就找到，只匹配一行数据
eq_ref
唯一性索引扫描，对于每个索引，表中只有一条记录与之匹配
ref
非唯一性索引扫描，返回匹配某个单独值的所有行
range
只检索给定范围的行，使用一个索引来选择行
index
只遍历索引树
ALl
全表扫描，性能最差

6、possible_keys

指出 MySQL 使用哪个索引在该表找到行记录。如果该值为 NULL，说明没有使用索引，可以建立索引提高性能

7、key

显示 MySQL 实际使用的索引。如果为 NULL，则没有使用索引查询

8、key_len

显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。使用的索引的长度。在不损失精确性的情况下，长度越短越好

9、ref

显示该表的索引字段关联了哪张表的哪个字段

10、rows

根据表统计信息及选用情况，大致估算出找到所需的记录或所需读取的行数，数值越小越好

11、filtered

返回结果的行数占读取行数的百分比，值越大越好

12、extra

包含不适合在其他列中显示但十分重要的额外信息。常见的值如下

using
mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，若出现该值，应该优化sql语句
using temporary
使用临时表保存中间结果，比如mysql在对查询结果进行排序时使用临时表，常见于order by和group by，若出现该值，应该优化sql语句
using index
表示select操作使用了覆盖索引，避免了访问表的数据行，效率不错
using where
where子句用于限制哪一行
using join buffer
使用连接缓存
distinct
发现第一匹配后，停止为当前的行组合搜索更多的行

五、索引的使用及注意事项

1、不走索引的sql

1）涉及到函数运算的，包括字符串操作：

/*不使用索引*/
EXPLAIN SELECT * FROM student WHERE CONCAT(nickname,'ss') = '小明ss';
EXPLAIN SELECT * FROM student WHERE age + 3 = 26
/*使用索引*/
EXPLAIN SELECT * FROM student WHERE nickname = '小明';
EXPLAIN SELECT * FROM student WHERE age = 23;

2）like的使用

/*不使用索引*/
EXPLAIN SELECT * FROM student WHERE nickname LIKE '%小明%';
/*使用索引*/
EXPLAIN SELECT * FROM student WHERE nickname LIKE '小明%';
EXPLAIN SELECT * FROM student WHERE nickname LIKE '小明';

3）数字向字符串的隐式转换

/*不使用索引*/
EXPLAIN SELECT * FROM student WHERE grade = 027021;
/*使用索引*/
EXPLAIN SELECT * FROM student WHERE grade = '027021';
EXPLAIN SELECT * FROM student WHERE age = '23';
EXPLAIN SELECT * FROM student WHERE age = 23;

这里grade字段是数字的字符串类型，age是数字类型，当027021数字类型向字符串类型进行隐式转换的时候，没有走索引，而‘23’转为数字时候是可以走索引的。

4）条件判断

EXPLAIN SELECT * FROM student WHERE nickname='小明' AND age = 23;
EXPLAIN SELECT * FROM student WHERE nickname='小明' OR age = 20;

and和or的分析：

使用and时，只要其中有条件带索引，都会使用到索引来查找；而or的使用会比较复杂，当前后条件中只有一个带有索引的话，是不会使用索引的；当前后条件都带有索引的话，需要针对查询的结果进行分析，当查询出的结果较多的时候不会使用到索引，而查询出的结果较少时候，会使用到索引。

EXPLAIN SELECT s.*,t.* FROM student s,teacher t WHERE t.name = s.nickname AND s.nickname = '小明';
EXPLAIN SELECT s.*,t.* FROM student s,teacher t WHERE t.name = s.nickname

当多表联查使用where and作为条件判断的时候，如果条件中都带有索引，并且有一个精准的定位（s.nickname = ‘小明’）会使用到索引；但是如果没有精准定位的话会比较复杂，需要针对sql进行判断，包括查询出来的是哪几列。

EXPLAIN SELECT * FROM teacher t LEFT JOIN student s ON t.name = s.nickname;

当多表联查使用左（又）连接作为条件判断的时候，只会使用到副表的索引，如例子中的student表如果nickname字段是索引，就会使用到索引。但是主表还是不会使用到索引，因为它需要查询所有字段。

5）!=、=、IN、NOT IN、BETWEEN

/*使用索引*/
EXPLAIN SELECT * FROM student WHERE GRADE = '27000';
EXPLAIN SELECT * FROM student WHERE GRADE IN ('27001','27002');
EXPLAIN SELECT * FROM student WHERE GRADE BETWEEN '27001' AND '27002';
/*不使用索引*/
EXPLAIN SELECT * FROM student WHERE GRADE != '27000';
EXPLAIN SELECT * FROM student WHERE GRADE NOT IN ('27001','27002');
EXPLAIN SELECT * FROM student WHERE GRADE NOT BETWEEN '27001' AND '27002';

IN，EXISTS

EXPLAIN SELECT * FROM student WHERE NICKNAME IN (SELECT NAME FROM sys_user);
EXPLAIN SELECT * FROM student WHERE EXISTS (SELECT NAME FROM sys_user WHERE NAME = student.NICKNAME);

比较一下两种方法：

IN的结果显示，虽然都用到了连个表的索引列，但是student的效率更高，sys_user的效率非常低；EXISTS的结果显示，只要用到了sys_user的索引列，而且效率很高，没有使用student的索引列。所以到底使用IN还是EXISTS要结合表的大小来定，两者不一定谁的效率会更高，如果子查询（sys_user）的表更大，使用EXISTS，相反使用IN。

2、联合索引

当搜索条件使用到联合索引的时候，最高的效率是多个条件都用到了联合索引，其次是只用联合索引中的第一个索引，而使用联合索引第二位及其后面的索引作为条件（没有使用联合索引的第一个索引），就不会走索引了。

六、其他sql优化

1、使用LIMIT 1

当确定只需要一条数据的时候，使用LIMIT 1，这样在查询到一条数据的时候可以直接返回，不再向下继续搜索。

**2、不使用SELECT ***

从数据库里读出越多的数据，那么查询就会变得越慢，而且增加数据传输的负担。

3、NULL的使用

建表时候字符串类型不要选择默认NULL，因为NULL也会占用内存，增加开销，可以设置为空串，因为空串不会占用内存。

4、固定长度的表会更快

固定长度的表会提高性能，因为MySQL搜寻得会更快一些，因为这些固定的长度是很容易计算下一个数据的偏移量的，所以读取的自然也会很快。而如果字段不是定长的，那么，每一次要找下一条的话，需要程序找到主键。并且，固定长度的表也更容易被缓存和重建。不过，唯一的副作用是，固定长度的字段会浪费一些空间，因为定长的字段无论你用不用，他都是要分配那么多的空间。