MySQK索引

最新推荐文章于 2024-05-30 14:59:09 发布

后续的诗

最新推荐文章于 2024-05-30 14:59:09 发布

阅读量942

点赞数 23

分类专栏：数据库文章标签：数据库

本文链接：https://blog.csdn.net/weixin_74310945/article/details/137124749

版权

数据库专栏收录该内容

14 篇文章 0 订阅

订阅专栏

1.认识索引

索引：提高数据库的性能，索引是物美价廉的东西了。不用加内存，不用改程序，不用调sql，只要执行正确的 create index ，查询速度就可能提高成百上千倍。但是天下没有免费的午餐，查询速度的提高是以插入、更新、删除的速度为代价的，这些写操作，增加了大量的IO。所以它的价值，在于提高一个海量数据的检索速度。

常见索引分为：

主键索引(primary key)
唯一索引(unique)
普通索引(index)
全文索引(fulltext)--解决中子文索引问题。

接下来，通过一个案例，来看看索引能提高多少检索效率。

1。我们先创建一个有着八百万条数据的表。

mysql> select COUNT(*) from EMP;
+----------+
| COUNT(*) |
+----------+
|  8000000 |
+----------+
1 row in set (3.54 sec)

2。我们要在这八百万条数据中找到指定的一条。

mysql> select * from emp where empno = 998877;
+--------+--------+----------+------+---------------------+---------+--------+--------+
| empno  | ename  | job      | mgr  | hiredate            | sal     | comm   | deptno |
+--------+--------+----------+------+---------------------+---------+--------+--------+
| 998877 | FRqhLk | SALESMAN | 0001 | 2023-08-17 00:00:00 | 2000.00 | 400.00 |    425 |
+--------+--------+----------+------+---------------------+---------+--------+--------+
1 row in set (4.52 sec)

mysql> select * from emp where empno = 998877;
+--------+--------+----------+------+---------------------+---------+--------+--------+
| empno  | ename  | job      | mgr  | hiredate            | sal     | comm   | deptno |
+--------+--------+----------+------+---------------------+---------+--------+--------+
| 998877 | FRqhLk | SALESMAN | 0001 | 2023-08-17 00:00:00 | 2000.00 | 400.00 |    425 |
+--------+--------+----------+------+---------------------+---------+--------+--------+
1 row in set (4.47 sec)

mysql> select * from emp where empno = 998877;
+--------+--------+----------+------+---------------------+---------+--------+--------+
| empno  | ename  | job      | mgr  | hiredate            | sal     | comm   | deptno |
+--------+--------+----------+------+---------------------+---------+--------+--------+
| 998877 | FRqhLk | SALESMAN | 0001 | 2023-08-17 00:00:00 | 2000.00 | 400.00 |    425 |
+--------+--------+----------+------+---------------------+---------+--------+--------+
1 row in set (4.53 sec)

可以看到查找的时间大概是4.5秒左右。

3。我们建立索引之后再次查找。(因为数据较多，所以建立索引也需要花一些时间）

mysql> alter table emp add index(empno);
Query OK, 0 rows affected (25.24 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from emp where empno = 998877;
+--------+--------+----------+------+---------------------+---------+--------+--------+
| empno  | ename  | job      | mgr  | hiredate            | sal     | comm   | deptno |
+--------+--------+----------+------+---------------------+---------+--------+--------+
| 998877 | FRqhLk | SALESMAN | 0001 | 2023-08-17 00:00:00 | 2000.00 | 400.00 |    425 |
+--------+--------+----------+------+---------------------+---------+--------+--------+
1 row in set (0.01 sec)

mysql> select * from emp where empno = 998877;
+--------+--------+----------+------+---------------------+---------+--------+--------+
| empno  | ename  | job      | mgr  | hiredate            | sal     | comm   | deptno |
+--------+--------+----------+------+---------------------+---------+--------+--------+
| 998877 | FRqhLk | SALESMAN | 0001 | 2023-08-17 00:00:00 | 2000.00 | 400.00 |    425 |
+--------+--------+----------+------+---------------------+---------+--------+--------+
1 row in set (0.00 sec)

可以看到几乎检测不到查询时耗费的时间。
根本原因就是，给员工工号创建索引后再根据员工工号来查询数据，这时就能够直接通过底层建立的数据结构来快速定位到目标数据，从而提高数据的检索速度，这就是索引的价值。

2.认识磁盘

2.1基础结构

MySQL 给用户提供存储服务，而存储的都是数据，数据在磁盘这个外设当中。
磁盘是计算机中的一个机械设备，相比于计算机其他电子元件，磁盘效率是比较低的，在加上IO本身的特征，可以知道，如何提交效率，是 MySQL 的一个重要话题。

先来研究一下磁盘：

一个磁盘由多个盘片叠加而成

每一面都会有一个读写磁头，在盘片中有很多微小单元，这些微小单元就是一个小磁铁，而磁铁是有南北极的。我们可以使用南极表示1，北极表示0，当我们需要修改数据时，只需让磁头找到对应单元，然后将对应单元的磁铁的磁性改变就行。

而一个磁盘对应那么多盘片，一个盘片中那么多个微小单元，磁头是如何精准的找到对应单元呢。

每个盘片被分为很多个同心圆，每一个圆都是一个磁道。每个磁道都有一个编号
每一个磁道会分为若干个扇区。每个扇区的存储容量都是512字节，不论离圆心斤或是远。
文件系统访问磁盘的基本单位是4kb。

2.2CHS寻址方式

CHS寻址模式将硬盘划分为磁头（Heads）、柱面(Cylinder)、扇区(Sector)。

磁头(Heads)：每张磁片的正反两面各有一个磁头，一个磁头对应一张磁片的一个面。因此，用第几磁头就可以表示数据在哪个磁面。
柱面(Cylinder)：所有磁片中半径相同的同心磁道构成“柱面"，意思是这一系列的磁道垂直叠在一起，就形成一个柱面的形状。简单地理解，柱面数=磁道数。
扇区(Sector)：将磁道划分为若干个小的区段，就是扇区。虽然很小，但实际是一个扇子的形状，故称为扇区。每个扇区的容量为512字节。

所以当我们需要修改一个数据的时候，实际上是在磁盘当中先找到这个数据在哪个盘片（磁头），再找到对应的磁道（柱面），最后找到对应的扇区。

2.3操作系统与磁盘交互的基本单位

文件系统访问磁盘的基本单位是4kb。也就是说，即使你只要改1字节的数据，也要将4kb的数据全部加载到内存。

1.实际的物理内存是被划分成一个个以4kb为大小的页框，磁盘以4kb为大小和操作系统交互，可以让数据更好的保存与加载。

2.前面说到磁盘的效率很低，如果IO的单位太小，意味着我们读取同样的内容需要访问更多次的磁盘，那样IO的效率就低了。

3如果操作系统直接使用硬件提供的数据大小进行交互，那么系统的IO代码，就和硬件强相关，换言之，如果硬件发生变化，系统必须跟着变化

总结：文件系统访问磁盘的基本单位是4kb，一方面可以增大效率，一方面实现了硬件和系统的解耦。

2.4磁盘的随机访问与连续访问

随机访问：本次IO所给出的扇区地址和上次IO给出扇区地址不连续，这样的话磁头在两次IO操作之间需要作比较大的移动动作才能重新开始读/写数据。

连续访问：如果当次IO给出的扇区地址与上次IO结束的扇区地址是连续的，那磁头就能很快的开始这次IO操作，这样的多个IO操作称为连续访问。

若是随机访问，两次IO时的地址相差比较大，磁头也需要移动很大的位置才能找到。

若是连续访问，因为访问的扇区地址连续，所以磁头也不需要太大的移动就能定位目标地址。

总结：连续访问的效率要高于随机访问。

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

而 MySQL 作为一款应用软件，可以想象成一种特殊的文件系统。它有着更高的IO场景，所以，为了提高基本的IO效率， MySQL 进行IO的基本单位是 16KB 。

mysql> show global status like 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.02 sec)

可以看到在InnoDB存储引擎下，交互的基本单位是16kb

也就是说，磁盘这个硬件设备的基本单位是 512 字节，而 MySQL InnoDB引擎使用 16KB 进行IO交互。即， MySQL 和磁盘进行数据交互的基本单位是 16KB 。这个基本数据单元，在 MySQL 这里叫做page（注意和系统的page区分）

4.MySQL交互模型

MySQL 中的数据文件，是以page为单位保存在磁盘当中的。
MySQL 的 CURD 操作，都需要通过计算，找到对应的插入位置，或者找到对应要修改或者查询的数据。
而只要涉及计算，就需要CPU参与，而为了便于CPU参与，一定要能够先将数据移动到内存当中。所以在特定时间内，数据一定是磁盘中有，内存中也有。后续操作完内存数据之后，以特定的刷新策略，刷新到磁盘。而这时，就涉及到磁盘和内存的数据交互，也就是IO了。而此时IO的基本单位就是Page。
为了更好的进行上面的操作， MySQL 服务器在内存中运行的时候，在服务器内部，就申请了被称为 Buffer Pool 的的大内存空间，来进行各种缓存。其实就是很大的内存空间，来和磁盘数据进行IO交互。
为何更高的效率，一定要尽可能的减少系统和磁盘IO的次数

4.1MySQL从磁盘读数据。

1。MySQL启动时，会向内存申请一块空间（Buffer Pool）

2。每次查询，都会从磁盘读取以4KB为单位的数据到内核缓冲区，再从内核缓冲区以16KB为大小读取到Buffer Pool当中。

3。当需要增删改的时候，如果Buffer Pool中有对应的数据，就直接修改。如果没有就从磁盘中读取。

4.2MySQL将数据刷新到磁盘

所有的增删改查都在内存中进行，每次执行sql的时候都会在缓存池中先把数据调整好，然后再刷新到内核缓冲区。内核有自己刷新策略，会定期把数据从内核缓冲区刷新到磁盘。

5. 索引的理解

5.1索引的默认排序

create table if not exists user (
id int primary key, --一定要添加主键哦，只有这样才会默认生成主键索引
age int not null,
name varchar(16) not null
);

mysql> show create table user \G
*************************** 1. row ***************************
Table: user
Create Table: CREATE TABLE `user` (
`id` int(11) NOT NULL,
`age` int(11) NOT NULL,
`name` varchar(16) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 --默认就是InnoDB存储引擎
1 row in set (0.00 sec)

mysql> desc user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| age   | int(11)     | NO   |     | NULL    |       |
| name  | varchar(16) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

此时，我们再插入一些数据。

mysql> insert into user (id, age, name) values(3, 18, '杨过');
Query OK, 1 row affected (0.01 sec)

mysql> insert into user (id, age, name) values(4, 16, '小龙女');
Query OK, 1 row affected (0.00 sec)

mysql> insert into user (id, age, name) values(2, 26, '黄蓉');
Query OK, 1 row affected (0.01 sec)

mysql> insert into user (id, age, name) values(5, 36, '郭靖');
Query OK, 1 row affected (0.00 sec)

mysql> insert into user (id, age, name) values(1, 56, '欧阳锋');
Query OK, 1 row affected (0.00 sec)

mysql> select * from user;
+----+-----+-----------+
| id | age | name      |
+----+-----+-----------+
|  1 |  56 | 欧阳锋    |
|  2 |  26 | 黄蓉      |
|  3 |  18 | 杨过      |
|  4 |  16 | 小龙女    |
|  5 |  36 | 郭靖      |
+----+-----+-----------+
5 rows in set (0.00 sec)

注意我们插入的时候，并没有让id（主键）有序插入，但是在查询的时候，id竟然时有序的。

所以我们可以得出一个结论：MySQL底层会自动按照主键对插入的数据进行排序。

5.2为何IO交互要是 Page

为何MySQL和磁盘进行IO交互的时候，要采用Page（交互的基本单位为16KB）的方案进行交互呢?用多少，加载多少不香吗?

如果使用用多少加载多少的方式：上面的5条记录，如果MySQL要查找id=2的记录，第一次加载id=1，第二次加载id=2，一次一条记录，那么就需要2次IO。如果要找id=5，那么就需要5次IO。
如果使用Page方案进行交互：如果这5条(或者更多)都被保存在一个Page中(16KB，能保存很多记录),那么第一次IO查找id=2的时候，整个Page会被加载到MySQL的Buffer Pool中，这里完成了一次IO。但是往后如果在查找id=1,3,4,5等，完全不需要进行IO了，而是直接在内存中进行了。所以，就在单Page里面，大大减少了IO的次数。

你怎么保证，用户一定下次找的数据，就在这个Page里面？我们不能严格保证，但是有很大概率，因为有局部性原理。

往往IO效率低下的最主要矛盾不是IO单次数据量的大小，而是IO的次数

5.3理解单个Page

MySQL 中要管理很多数据表文件，而要管理好这些文件，就需要先描述，在组织 ,我们目前可以简单理解成一个个独立文件是有一个或者多个Page构成的。

不同的 Page ，在 MySQL 中，都是 16KB ，使用 prev 和 next 构成双向链表
因为有主键的问题， MySQL 会默认按照主键给我们的数据进行排序，从上面的Page内数据记录可以看出，数据是有序且彼此关联的。

插入数据时排序的目的，就是优化查询的效率。

页内部存放数据的模块，实质上也是一个链表的结构，链表的特点也就是增删快，查询修改慢，所以优化查询的效率是必须的。

正式因为有序，在查找的时候，从头到后都是有效查找，没有任何一个查找是浪费的，而且，如果运气好，是可以提前结束查找过程的

5.4理解多个Page

通过上面的分析，我们知道，上面页模式中，只有一个功能，就是在查询某条数据的时候直接将一整页的数据加载到内存中，以减少硬盘IO次数，从而提高性能。但是，我们也可以看到，现在的页模式内部，实际上是采用了链表的结构，前一条数据指向后一条数据，本质上还是通过数据的逐条比较来取出特定的数据。

如果有1千万条数据，一定需要多个Page来保存1千万条数据，多个Page彼此使用双链表链接起来，而且每个Page内部的数据也是基于链表的。那么，查找特定一条记录，也一定是线性查找。这效率也太低了。

5.5页目录

当我们在读书的时候，如果我们想找到特定内容在数的那里，如果使用一页一页翻（线性查找）的方式，那效率就太低了，通常的做法是我们会在书前面的目录中查找，有了目录我们就可以快速定位，这里也是一样的，我们可以在Page中增加一个目录来提高查找效率。

在一个Page内部，我们引入了目录。比如，我们要查找id=4记录，之前必须线性遍历4次，才能拿到结果。现在直接通过目录2[3]，直接进行定位新的起始位置，提高了效率。

5.6多页情况

MySQL 中每一页的大小只有 16KB ，单个Page大小固定，所以随着数据量不断增大， 16KB 不可能存下所有的数据，那么必定会有多个页来存储数据。

在单表数据不断被插入的情况下， MySQL 会在容量不足的时候，自动开辟新的Page来保存新的数据，然后通过指针的方式，将所有的Page组织起来。

这样，我们就可以通过多个Page遍历，Page内部通过目录来快速定位数据。可是，貌似这样也有效率问题，在Page之间，也是需要 MySQL 遍历的，遍历意味着依旧需要进行大量的IO，将下一个Page加载到内存，进行线性检测。这样就显得我们之前的Page内部的目录，有点杯水车薪了。

5.7Page页目录

使用一个目录项来指向某一页，而这个目录项存放的就是将要指向的页中存放的最小数据的键值。
和页内目录不同的地方在于，这种目录管理的级别是页，而页内目录管理的级别是行。
其中，每个目录项的构成是：键值+指针。

存在一个目录页来管理页目录，目录页中的数据存放的就是指向的那一页中最小的数据。有数据，就可通过比较，找到该访问那个Page，进而通过指针，找到下一个Page。

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

可是，我们每次检索数据的时候，该从哪里开始呢？虽然顶层的目录页少了，但是还要遍历

不用担心，可以在加目录页

熟悉数据结构的同学可能会发现这就是一颗B+树，在InnoDB存储引擎下，就是使用B+树来存储数据的。
创建主键时，会在底层将数据以B+树的形式保存在Buffer Pool中，当我们查找数据时，使用B+树就可以大大提高查询效率。
Page分为目录页和数据页。目录页只放各个下级Page的最小键值。
查找的时候，自定向下找，只需要加载部分目录页到内存，即可完成算法的整个查找过程，大大减少了IO次数

5.8 InnoDB 建立索引结构来管理数据为何使用B+树

链表：线性遍历，效率低
二叉搜索树：退化问题，可能退化成为线性结构
AVL和红黑树：虽然是平衡或者近似平衡，但是毕竟是二叉结构，相比较多阶B+，意味着树整体过高，大家都是自顶向下找，层高越低，意味着系统与硬盘更少的IO Page交互。
Hash：官方的索引实现方式中， MySQL 是支持HASH的，不过 InnoDB 和 MyISAM 并不支持.Hash跟进其算法特征，决定了虽然有时候也很快(O(1))，不过，在面对范围查找就明显不行。

下面几个是常见的存储引擎所支持的索引结构

5.9B树vsB+树

通过上面的分析我们知道了B树的效率最优，而B树又有一种变形结构B+树，在实际存储中我们最后为什么选择了B+树而不是B树呢。

先来看看B树和B+树有什么区别：

1.查询效率：

B+ 树的非叶子节点不存放实际的记录数据，仅存放索引，因此数据量相同的情况下，相比存储即存索引又存记录的 B 树，B+树的非叶子节点可以存放更多的索引，因此 B+ 树可以比 B 树更「矮胖」，查询底层节点的磁盘 I/O次数会更少。

2.增删效率：

B+ 树的插入和删除存在冗余节点，插入可能存在节点的分裂（如果节点饱和），但是最多只涉及树的一条路径。而且 B+ 树会自动平衡，不需要像更多复杂的算法，类似红黑树的旋转操作等。因此，B+ 树的插入和删除效率更高。

3.范围查询：

B树叶子节点间没有进行连接，B+ 树所有叶子节点间还有一个链表进行连接，这种设计对范围查找非常有帮助。B 树只能通过树的遍历来完成范围查询（每一次都要从头开始），这会涉及多个节点的磁盘 I/O 操作，B+树进行范围查找时，先找到一个数据，然后依次向后遍历即可（数据是连续的）。B+树在范围查找上的效率也更高。

5.10聚簇索引 VS 非聚簇索引

前面我们学习了InnoDB存储引擎是采用B+树的，而MyISAM存储引擎采用的也是B+树，但是与InnoDB不同的是，将索引Page和数据Page分离，也就是叶子节点没有数据，只有对应数据的地址。InnoDB 是将索引和数据放在一起的。

InnoDB 这种用户数据和索引数据放在一起的方案，叫做聚簇索引

MyISAM 这种用户数据与索引数据分离的索引方案，叫做非聚簇索引

mysql> create table InnoDB_test(
    -> id int primary key,
    -> name varchar(11) not null
    -> )engine=InnoDB; 
Query OK, 0 rows affected (0.02 sec)

如果我们存储引擎采用InnoDB的形式，那在对应目录下就会生成两个文件。

mysql> create table MyISAM_test(
    -> id int primary key,
    -> name varchar(11) not null
    -> )engine=MyISAM; 
Query OK, 0 rows affected (0.00 sec)

而MyISAM会生成三个文件，那么这三个文件分别存放的是什么呢？

InnoDB(聚簇索引）

xxx.frm表相关信息。

xxx.ibd索引和相关数据

MyISAM（非聚簇索引）

xxx.frm表相关信息。

xxx.MYD数据相关信息。

xxx.MYI索引相关信息。

MySQL 除了默认会建立主键索引外，我们用户也有可能建立按照其他列信息建立的索引，一般这种索引可以叫做辅助（普通）索引。

对于 MyISAM ,建立辅助（普通）索引和主键索引没有差别，无非就是主键不能重复，而非主键可重复。下图就是基于 MyISAM 的 Col2 建立的索引，和主键索引没有差别

同样， InnoDB 除了主键索引，用户也会建立辅助（普通）索引，我们以上表中的 Col3 建立对应的辅助索引如下图：

6.索引操作

索引的常见分类：

主键索引(primary key)
唯一索引(unique)
普通索引(index)
全文索引(fulltext)--解决中子文索引问题。

6.1创建主键索引

方法一：在创建表的时候，直接在字段名后指定 primary key

create table user1(id int primary key, name varchar(30));

方法二：在创建表的最后，指定某列或某几列为主键索引

create table user2(id int, name varchar(30), primary key(id));

方法三：创建表以后再添加主键

create table user3(id int, name varchar(30));

alter table user3 add primary key(id);

mysql> desc user1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

主键索引的特点：

一个表中，最多有一个主键索引，当然可以使符合主键
主键索引的效率高（主键不可重复）
创建主键索引的列，它的值不能为null，且不能重复
主键索引的列基本上是int

6.2创建唯一索引

方法一：在表定义时，在某列后直接指定unique唯一属性。

create table user4(id int primary key, name varchar(30) unique);

方法二：创建表时，在表的后面指定某列或某几列为unique

create table user5(id int primary key, name varchar(30), unique(name));

方法三：创建表以后再添加唯一键

create table user6(id int primary key, name varchar(30);
alter table user6 add unique(name);

mysql> desc user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

唯一索引的特点：

一个表中，可以有多个唯一索引
查询效率高
如果在某一列建立唯一索引，必须保证这列不能有重复数据
如果一个唯一索引上指定not null，等价于主键索引

6.3创建普通索引

方法一：在表的定义最后，指定某列为索引

create table user8(
    id int primary key,
    name varchar(20),
    email varchar(30),
    index(name)
);

方法二：创建完表以后指定某列为普通索引

create table user9(
    id int primary key, 
    name varchar(20), 
    email varchar(30)
);

alter table user9 add index(name);

方法三：创建一个索引名为 idx_name 的索引

create table user10(
    id int primary key, 
    name varchar(20), 
    email varchar(30)
);

create index idx_name on user10(name);

普通索引的特点：

一个表中可以有多个普通索引，普通索引在实际开发中用的比较多
如果某列需要创建索引，但是该列有重复的值，那么我们就应该使用普通索引

6.4创建全文索引

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

mysql> create table articles(
    -> id int unsigned auto_increment not null primary key,
    -> title varchar(200),
    -> body text,
    -> fulltext(title, body)
    -> )engine=MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> desc articles;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| title | varchar(200)     | YES  | MUL | NULL    |                |
| body  | text             | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> INSERT INTO articles (title,body) VALUES
    -> ('MySQL Tutorial','DBMS stands for DataBase ...'),
    -> ('How To Use MySQL Well','After you went through a ...'),
    -> ('Optimizing MySQL','In this tutorial we will show ...'),
    -> ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
    -> ('MySQL vs. YourSQL','In the following database comparison ...'),
    -> ('MySQL Security','When configured properly, MySQL ...');
Query OK, 6 rows affected (0.03 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from articles;
+----+-----------------------+------------------------------------------+
| id | title                 | body                                     |
+----+-----------------------+------------------------------------------+
|  1 | MySQL Tutorial        | DBMS stands for DataBase ...             |
|  2 | How To Use MySQL Well | After you went through a ...             |
|  3 | Optimizing MySQL      | In this tutorial we will show ...        |
|  4 | 1001 MySQL Tricks     | 1. Never run mysqld as root. 2. ...      |
|  5 | MySQL vs. YourSQL     | In the following database comparison ... |
|  6 | MySQL Security        | When configured properly, MySQL ...      |
+----+-----------------------+------------------------------------------+
6 rows in set (0.00 sec)

当我们在文章中查找database关键词时

mysql> select * from articles where body like '%database%';
+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
+----+-------------------+------------------------------------------+
2 rows in set (0.00 sec)

但是这种方法并没有使用到全文索引。可以用explain工具看一下，是否使用到索引

mysql> explain select * from articles where body like '%database%' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: articles
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
     filtered: 16.67
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

key那一行为空，说明没有使用。

如何使用全文索引呢？

使用match against进行搜索。

mysql> select * from articles where match(title, body) against('database');
+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
+----+-------------------+------------------------------------------+
2 rows in set (0.00 sec)

mysql> explain select * from articles where match(title, body) against('database') \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: articles
   partitions: NULL
         type: fulltext
possible_keys: title
          key: title
      key_len: 0
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

可以看到key那一行由NULL变成了title。

6.5查询索引

第一种方法： show keys from 表名

mysql> show keys from articles \G;
*************************** 1. row ***************************
        Table: articles
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 6
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: articles
   Non_unique: 1
     Key_name: title
 Seq_in_index: 1
  Column_name: title
    Collation: NULL
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: FULLTEXT
      Comment: 
Index_comment: 
*************************** 3. row ***************************
        Table: articles
   Non_unique: 1
     Key_name: title
 Seq_in_index: 2
  Column_name: body
    Collation: NULL
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: FULLTEXT
      Comment: 
Index_comment: 
3 rows in set (0.00 sec)

ERROR: 
No query specified

Table：表示创建索引的表的名称。
Non_unique：表示该索引是否是唯一索引，如果是则为0，如果不是则为1。
Key_name：表示索引的名称。
Column_name：表示定义索引的列字段。
Collation：表示列以何种顺序存储在索引中，“A”表示升序，NULL表示无分类。
Index_type：显示索引使用的类型和方法（BTREE、FULLTEXT、HASH、RTREE）。

第二种方法: show index from 表名;

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

ERROR: 
No query specified

第三种方法（信息比较简略）： desc 表名；

mysql> desc articles;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| title | varchar(200)     | YES  | MUL | NULL    |                |
| body  | text             | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

6.6删除索引

第一种方法-删除主键索引： alter table 表名 drop primary key;

mysql> desc user2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

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

mysql> desc user2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

第二种方法-其他索引的删除： alter table 表名 drop index 索引名；

索引名就是show keys from 表名中的 Key_name 字段

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

mysql> desc user2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

第三种方法方法： drop index 索引名 on 表名

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

mysql> desc user2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

6.6索引创建原则

比较频繁作为查询条件的字段应该创建索引
唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
更新非常频繁的字段不适合作创建索引
不会出现在where子句中的字段不该创建索引

后续的诗

关注

23
点赞
踩
30

收藏

觉得还不错? 一键收藏
0
评论
MySQK索引

索引：提高数据库的性能，索引是物美价廉的东西了。不用加内存，不用改程序，不用调sql，只要执行正确的 create index ，查询速度就可能提高成百上千倍。但是天下没有免费的午餐，查询速度的提高是以插入、更新、删除的速度为代价的，这些写操作，增加了大量的IO。所以它的价值，在于。接下来，通过一个案例，来看看索引能提高多少检索效率。1。我们先创建一个有着八百万条数据的表。2。我们要在这八百万条数据中找到指定的一条。可以看到查找的时间大概是4.5秒左右。3。我们建立索引之后再次查找。
复制链接

扫一扫