MySQL索引

最新推荐文章于 2024-05-22 20:34:19 发布

加法器+

最新推荐文章于 2024-05-22 20:34:19 发布

阅读量1k

点赞数 31

分类专栏：原创 MySQL 文章标签： mysql 数据库 linux

本文链接：https://blog.csdn.net/Dirty_artist/article/details/138803544

版权

原创同时被 2 个专栏收录

175 篇文章 5 订阅

订阅专栏

MySQL

15 篇文章 0 订阅

订阅专栏

文章目录

1. 见见索引
2. 认识磁盘
3. MySQL与磁盘交互基本单位
4. 共识总结
5. 索引的理解
5.1 再谈page
5.2 单个page
5.3 多个page
5.4 页目录
5.5 单页情况
5.6 多页情况
5.7 为什么采用B+树
5.8 B树和B+树
5.9 聚簇索引和非聚簇索引

6. 索引操作
6.1 创建主键索引
6.2 创建唯一键索引
6.3 创建普通索引
6.4 查看索引
6.5 删除索引
6.6 索引创建原则
6.7 复合索引
6.8 全文索引

1. 见见索引

索引的核心工作是提高数据库性能的，提高查询效率。

mysql的服务器，本质是在内存当中，所有的curd操作，都是在内存当中进行的，索引也是。

提高算法效率的因素：

组织数据的方式（数据结构）
算法本身（各种算法）

索引是以特定更改组织数据的方式，来提高效率，所以索引的本质是一种特殊的数据结构。

导入测试表（构建800w条数据记录）：

drop database if exists `bit_index`;
create database if not exists `bit_index` default character set utf8;
use `bit_index`;

-- 构建一个8000000条记录的数据
-- 构建的海量表数据需要有差异性，所以使用存储过程来创建， 拷贝下面代码就可以了，暂时不用理解

-- 产生随机字符串
delimiter $$
create function rand_string(n INT)
returns varchar(255)
begin
declare chars_str varchar(100) default
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
declare return_str varchar(255) default '';
declare i int default 0;
while i < n do
set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
set i = i + 1;
end while;
return return_str;
end $$
delimiter ;


-- 产生随机数字
delimiter $$
create function rand_num( )
returns int(5)
begin
declare i int default 0;
set i = floor(10+rand()*500);
return i;
end $$
delimiter ;

-- 创建存储过程，向雇员表添加海量数据
delimiter $$
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0;
set autocommit = 0;
repeat
set i = i + 1;
insert into EMP values ((start+i)
,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
until i = max_num
end repeat;
commit;
end $$
delimiter ;

-- 雇员表
CREATE TABLE `EMP` (
  `empno` int(6) unsigned zerofill NOT NULL COMMENT '雇员编号',
  `ename` varchar(10) DEFAULT NULL COMMENT '雇员姓名',
  `job` varchar(9) DEFAULT NULL COMMENT '雇员职位',
  `mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '雇员领导编号',
  `hiredate` datetime DEFAULT NULL COMMENT '雇佣时间',
  `sal` decimal(7,2) DEFAULT NULL COMMENT '工资月薪',
  `comm` decimal(7,2) DEFAULT NULL COMMENT '奖金',
  `deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT '部门编号'
);


-- 执行存储过程，添加8000000条记录
call insert_emp(100001, 8000000);

查看前5条数据：

查找工号为778899的员工：

在未构建索引之前，查询时间是4秒多（不同配置机器，性能不同）

构建索引：

alter table EMP add index(empno);

再次进行查找：

构建索引之后，查询效率十分高效，基本上是秒查。

2. 认识磁盘

关于磁盘的内容可看此篇文章：Linux文件系统之inode

3. MySQL与磁盘交互基本单位

mysql是应用层软件，所以mysql之下并不是硬件，而是系统。

系统与磁盘进行交互时，基本单位是4kb，而mysql比一般的文件系统有着更高的IO场景，为了提高mysql的IO效率，mysql的基本单位是16kb，这个基本单元在mysql这里叫page。

但mysql是应用层，不直接和硬件接触，所以这个16kb是站在mysql角度向操作系统提出的

mysql innoDB引擎是16kb，这里说的也是mysql innoDB引擎

mysql> show global status like 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_page_size | 16384 |
+------------------+-------+

4. 共识总结

mysql数据文件，以page为单位（16kb）存在磁盘当中
mysql的curd操作，都需要通过计算，找到对应的位置
设计到计算，需要CPU参与，为了便于CPU参与，一定要先将数据移动到内存当中
在特定的时间内，数据在磁盘中有，在内存当中也有，内存数据操作完毕之后，再刷新到磁盘当中（IO交互）
为了进行更好的进行上面的操作，mysql服务器在内存当中运行的时候，在服务器内部申请了buffer pool的大内存空间，来进行各种缓存
高效的原因：每次IO的数据更多，减少了系统和磁盘IO的次数

5. 索引的理解

建立测试表：

mysql> create table if not exists user (
    -> id int primary key,
    -> age int not null,
    -> name varchar(16) not null
    -> );

插入数据：

mysql> insert into user (id, age, name) values (3, 18, '瑞兹');
Query OK, 1 row affected (0.00 sec)

mysql> insert into user (id, age, name) values (4, 19, '瑞雯');
Query OK, 1 row affected (0.00 sec)

mysql> insert into user (id, age, name) values (2, 29, '奥拉夫');
Query OK, 1 row affected (0.00 sec)

mysql> insert into user (id, age, name) values (1, 21, '崔斯特');
Query OK, 1 row affected (0.00 sec)

mysql> insert into user (id, age, name) values (5, 20, '厄加特');
Query OK, 1 row affected (0.00 sec)

查了插入数据是无序插入的，但是查询的时候，发现这个有主健的表格当中，数据会自动排序

5.1 再谈page

一个page是16kb，而buffer poor默认是128mb，所以意味着mysql启动时，一定需要并且存在大量的page，这就决定了mysql必须要将多个同时存在的page管理起来，即先描述，再组织。

page内部必须要写入管理信息：

struct page
{
    struct page* next;
    struct page* prev;
    char buffer[NUM];
};

这样就将page链式管理起来，在buffer pool内部对mysql中的page进行了建模

IO交互为什么是page

如果只有1kb数据，多出来的15kb会进行预加载，能够有效减小IO的次数。

计算机局部性原理，在访问某些代码或者数据的时候，下次可能还是这块位置。

IO效率低下主要原因并不是单次IO数据量的大小，而是IO的次数。

5.2 单个page

不同的page在mysql当中都是16kb，用prev和next构成双向链表

如果有主键，mysql会默认按照主键对数据进行排序，如果没有数据就按照插入的顺序

为什么要对数据进行排序：

因为是链式结构，链表增删快，查询慢。对数据进行排序，可以优化查询效率

5.3 多个page

查询数据的时候，是直接将一整页的数据加载到内存当中，这样可以减少IO的次数，从而提高性能。
而页的内部采用是链式结构，如果数据量十分大，那么查找某条特定记录的时候，一定是线性查找的，那这样效率就很低。

5.4 页目录

书籍会花费几页纸张作为目录，目录可以快速定位到书的某部分内容；

目录的本质就是以空间换时间

5.5 单页情况

在page中引入目录：

目录也是数据，也需要占空间，也就意味着page内部保存数据的条数会减少，但是目录一般都不会很大

这也就能解答，为什么有主健的时候，mysql会将数据变为有序，这是因为只有数据有效了，才方便引入页内目录

5.6 多页情况

同理，以上只解决了单个page查找的效率，但是page和page之间，还是线性遍历的，数据量大之后，必定会有很多页来存储数据。

解决page之间的遍历问题，也可以引入目录

一个page是16kb，也就是16*1024 = 16384byte，假设目录采用整型int，里面再包含一个指针(64位)，即4+8 = 12byte，忽略page和page之间的指针，16384 / 12 = 1365，即一个page可以管理1635个子page，1365 * 16kb / 1024 = 21mb，差不多就一个page可以管理21mb的数据

目录页的本质也是页，普通页的数据是用户数据，目录页的数据是普通页的地址。

检索的时候，还是要遍历，那就再加一层：

此时管理的数据量就十分大了，一个二级页表管理21mb数据，一个三级页表可以管理1365个二级页表，即1635*21/1024，约为28gb

这个结构就是B+树，mysql对索引的管理采用的就是B+树（并不是所有的索引都是采用B+树结构，但是B+树是主流。），这整个结构叫做mysql innode db下的索引结构。

一般建表插入数据的时候，就是在该结构下进行curd操作

实际真实的B+树，中间节点是没有链接的，只有叶子节点是链接起来的

B+树叶子节点是链接起来的，进行范围查找的时候，更加方便
只有叶子节点保存数据，非叶子节点没有数据，只有目录项

非叶子节点不存数据，就可以存更多的目录项，可以更多管理叶子page，这就意味着这棵树是“矮胖树”，途径路上节点更少，找到目标数据所需的page就更少，IO此时就更少，在IO层面提高了效率；而每个节点都有目录项，这样就在算法角度，提高了搜索的效率。

5.7 为什么采用B+树

链表：线性遍历，查找效率较低
二叉搜索树：二次搜索树是瘦高状的，从上往下遍历的时候，可能会遇到很多节点，就需要进行多次IO，而且二次搜索树在极端情况下，会退化为线性结构
AVL树和红黑树：虽然这两颗树近似平衡，但是层高越低，IO次数越少，B+树更加优秀
Hash：哈希搜索效率虽然十分快，但是范围查找的时候，每个元素都要找一遍

存储引擎	索引类型	数据结构
MyISAM	B-tree	B树
InnoDB	B-tree	B+树
MEMORY	Hash/B-tree	哈希表/ B树
NDB	Hash	哈希表
ARCHIVE	None	无索引
CSV	None	无索引
TokuDB	Fractal Tree	分形树
Aria	B-tree	B+树

5.8 B树和B+树

B树的每个节点都会包含数据，而且叶子节点没有链接起来
B+树只有叶子节点包含数据，其他节点只有键值和page指针

非叶子节点不存数据可以保证存储更多的key值，是这颗树更矮，减少IO的次数

算法成本和IO成本相比，IO层面成本更高

叶子节点链接起来进行范围查找更加方便

5.9 聚簇索引和非聚簇索引

MyISAM引擎也是采用的B+树作为索引结构，但是它的叶子节点上面数据域存放的是数据的地址

图片来源：《MySQL》系列-小胖要的 MySQL 索引详解（附 20 张图解）_慕课手记 (imooc.com)，侵权删

这种数据和索引分离的方案叫做非聚簇索引。

mysql> create table test1(
    -> id int primary key,
    -> name varchar(20) not null
    -> )engine=innodb;
    
mysql> create table test2(
    -> id int primary key,
    -> name varchar(20) not null
    -> )engine=myisam;

mysql除了默认给主键建立索引，用户也可以给其他需要索引的列信息建立索引，这种叫做辅助索引。

而对于MyISAM，建立的辅助索引和主键索引没有区别，无非就是主键不能重复，非主键可以重复。

对于InnoDB建立辅助索引，依旧是按照指定列来构建B+树，但是非主键索引中的叶子节点，并没有数据，只有对应的key值。

因为数据没必要存2份，所以InnoDB除了主键索引之外其他的普通索引叶子节点上存的都是对应要构建索引键值的主键值

通过辅助索引，找到目标记录，需要两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。这种过程，就叫做回表查询。

为什么InnoDB针对辅助索引的场景，不给叶子节点附带数据？

原因很简单——太浪费空间了。

6. 索引操作

6.1 创建主键索引

方法1：

# 在创建表的时候，直接在字段名后指定 primary key
create table user1(id int primary key, name varchar(30));

方法2：

# 在创建表的最后，指定某列或某几列为主键索引
create table user2(id int, name varchar(30), primary key(id));

方法3：

create table user3(id int, name varchar(30));
# 创建表以后再添加主键, 主键索引只能有一个
alter table user3 add primary key(id);

主键索引的特点：

一个表当中，最多有一个主键索引
主键索引效率高（不重复）
列值不为null
列基本为int

6.2 创建唯一键索引

方法1：

# 在创建表的时候，直接在字段名后指定 primary key
create table user1(id int primary key, name varchar(30));

方法2：

# 在创建表的最后，指定某列或某几列为主键索引
create table user2(id int, name varchar(30), primary key(id));

方法3：

create table user3(id int, name varchar(30));
# 创建表以后再添加主键
alter table user3 add primary key(id);

唯一键索引的特点：

一个表可以有多个唯一键索引
查询效率高
如果在唯一键索引指定not null，等同于主键索引

6.3 创建普通索引

方法1：

create table user8(id int primary key,
name varchar(20),
email varchar(30),
index(name) # 在表的定义最后，指定某列为索引
);

方法2：

create table user9(id int primary key, name varchar(20), email varchar(30));
alter table user9 add index(name); # 创建完表以后指定某列为普通索引

方法3：

create table user10(id int primary key, name varchar(20), email varchar(30));
# 创建一个索引名为 idx_name 的索引
create index idx_name on user10(name);

普通索引的特定：

一个表可以有多个索引
某列需要创建索引且有重复的值，采用普通索引

6.4 查看索引

show index/keys table_name或者desc table_name

主键索引：

show index from user1\G
*************************** 1. row ***************************
        Table: user1
   Non_unique: 0
     Key_name: PRIMARY	#索引名称
 Seq_in_index: 1
  Column_name: id	#索引在哪列
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE	#索引类型, 这里BTREE就是B+树
      Comment: 
Index_comment: 
1 row in set (0.09 sec)

唯一键索引：

mysql> show index from user4\G
*************************** 1. row ***************************
        Table: user4
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: user4
   Non_unique: 0
     Key_name: name
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
2 rows in set (0.00 sec)

普通索引：

mysql> show index from user7\G
*************************** 1. row ***************************
        Table: user7
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: user7
   Non_unique: 1
     Key_name: name
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
2 rows in set (0.00 sec)

6.5 删除索引

删除主键索引：

alter table table_name drop primary key;

mysql> alter table user1 drop primary key;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from user1\G
Empty set (0.00 sec)

删除其他索引：

alter table table_name drop index index_name;	#这个index_name就是show index里面的Key_name字段

mysql> alter table user4 drop index name;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from user4\G
*************************** 1. row ***************************
        Table: user4
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
1 row in set (0.00 sec)

mysql> alter table user7 drop index name;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from user7\G
*************************** 1. row ***************************
        Table: user7
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
1 row in set (0.00 sec)

这里唯一键索引和普通索引其实没什么区别，只是唯一键有字段不冲突而已

6.6 索引创建原则

比较频繁作为查询条件的字段应该创建为索引
唯一性太差的字段不适合单独创建索引（例如性别）
频繁更新的字段不适合创建索引
不会出现在where子句的字段不能创建索引

6.7 复合索引

如果想用多列一起创建索引，可以采用复合索引的方式：

mysql> create table user10(
    -> id int primary key,
    -> name varchar(20),
    -> email varchar(20)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> create index my_index on user10(name, email);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from user10\G
*************************** 1. row ***************************
        Table: user10
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: user10
   Non_unique: 1
     Key_name: my_index
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 3. row ***************************
        Table: user10
   Non_unique: 1
     Key_name: my_index
 Seq_in_index: 2
  Column_name: email
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
3 rows in set (0.00 sec)

看着有3个索引，其实是2个，从左向右匹配（最左匹配原则），只有同时满足才命中该索引

6.8 全文索引

当对文章字段或有大量文字的字段进行检索时，会使用到全文索引。MySQL提供全文索引机制，但是有要求，要求表的存储引擎必须是MyISAM，而且默认的全文索引支持英文，不支持中文。如果对中文进行全文检索，可以使用sphinx的中文版coreseek。

mysql> create table songs(
    -> id int unsigned auto_increment primary key,
    -> name varchar(50),
    -> lyric text,
    -> fulltext(name,lyric)
    -> )engine=MyISAM;

mysql> show index from songs\G
*************************** 1. row ***************************
        Table: songs
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 4
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: songs
   Non_unique: 1
     Key_name: name
 Seq_in_index: 1
  Column_name: name
    Collation: NULL
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: FULLTEXT
      Comment: 
Index_comment: 
*************************** 3. row ***************************
        Table: songs
   Non_unique: 1
     Key_name: name
 Seq_in_index: 2
  Column_name: lyric
    Collation: NULL
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: FULLTEXT
      Comment: 
Index_comment: 

mysql> insert into songs(name,lyric) values 
    -> ('Love Story', 'We were both young when I first saw you'),
    -> ('Fortnight', 'I was supposed to be sent away...'),
    -> ('Cruel Summer', 'Fever dream high in the quiet of the night...'),
    -> ('Blank Space', 'Nice to meet you where you been...');

mysql> select * from songs;
+----+--------------+-----------------------------------------------+
| id | name         | lyric                                         |
+----+--------------+-----------------------------------------------+
|  1 | Love Story   | We were both young when I first saw you       |
|  2 | Fortnight    | I was supposed to be sent away...             |
|  3 | Cruel Summer | Fever dream high in the quiet of the night... |
|  4 | Blank Space  | Nice to meet you where you been...            |
+----+--------------+-----------------------------------------------+

查询数据：

普通查询:

mysql> select * from songs where lyric like '%you%';
+----+-------------+-----------------------------------------+
| id | name        | lyric                                   |
+----+-------------+-----------------------------------------+
|  1 | Love Story  | We were both young when I first saw you |
|  4 | Blank Space | Nice to meet you where you been...      |
+----+-------------+-----------------------------------------+

采用explain工具看一下是否用到索引：

mysql> explain select * from songs where lyric like '%you%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: songs
   partitions: NULL
         type: ALL	# 全部遍历
possible_keys: NULL
          key: NULL	# 表明未用到索引
      key_len: NULL
          ref: NULL
         rows: 4
     filtered: 25.00
        Extra: Using where

使用全文索引：

mysql> select * from songs where match(name,lyric) against ('supposed');
+----+-----------+-----------------------------------+
| id | name      | lyric                             |
+----+-----------+-----------------------------------+
|  2 | Fortnight | I was supposed to be sent away... |
+----+-----------+-----------------------------------+

采用explain工具看一下是否用到索引：

mysql> explain select * from songs where match(name,lyric) against ('supposed')\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: songs
   partitions: NULL
         type: fulltext
possible_keys: name
          key: name	# 用的名为name的索引
      key_len: 0
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where