MySQL索引

MYSQL索引

索引

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

常见的索引分为：

• 主键索引(primary key)
• 唯一索引(unique)
• 普通索引(index)
• 全文索引(fulltext)

下面是一个案例：

先创建一个表，这个表插入了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);

服务器比较拉，所以创建这个表创建了40多分钟。

800W条数据全部一起查询肯定是不行的，会导致严重后果，所以我先查询前五条看看表的结构。

mysql> SELECT * FROM EMP LIMIT 5;
+--------+--------+----------+------+---------------------+---------+--------+--------+
| empno  | ename  | job      | mgr  | hiredate            | sal     | comm   | deptno |
+--------+--------+----------+------+---------------------+---------+--------+--------+
| 100002 | pcsFXY | SALESMAN | 0001 | 2023-09-25 00:00:00 | 2000.00 | 400.00 |    488 |
| 100003 | UJufru | SALESMAN | 0001 | 2023-09-25 00:00:00 | 2000.00 | 400.00 |    476 |
| 100004 | zJVCXg | SALESMAN | 0001 | 2023-09-25 00:00:00 | 2000.00 | 400.00 |    391 |
| 100005 | xXwofK | SALESMAN | 0001 | 2023-09-25 00:00:00 | 2000.00 | 400.00 |     96 |
| 100006 | NqpCQv | SALESMAN | 0001 | 2023-09-25 00:00:00 | 2000.00 | 400.00 |    332 |
+--------+--------+----------+------+---------------------+---------+--------+--------+
5 rows in set (0.01 sec)

首先，我们这张表现在是没有自己建立索引的！

接着我们查询员工号778899的员工。

mysql> SELECT * FROM EMP WHERE empno = 778899;
+--------+--------+----------+------+---------------------+---------+--------+--------+
| empno  | ename  | job      | mgr  | hiredate            | sal     | comm   | deptno |
+--------+--------+----------+------+---------------------+---------+--------+--------+
| 778899 | uYJyqi | SALESMAN | 0001 | 2023-09-25 00:00:00 | 2000.00 | 400.00 |    433 |
+--------+--------+----------+------+---------------------+---------+--------+--------+
1 row in set (12.06 sec)

mysql> SELECT * FROM EMP WHERE empno = 778899;
+--------+--------+----------+------+---------------------+---------+--------+--------+
| empno  | ename  | job      | mgr  | hiredate            | sal     | comm   | deptno |
+--------+--------+----------+------+---------------------+---------+--------+--------+
| 778899 | uYJyqi | SALESMAN | 0001 | 2023-09-25 00:00:00 | 2000.00 | 400.00 |    433 |
+--------+--------+----------+------+---------------------+---------+--------+--------+
1 row in set (14.52 sec)

mysql> SELECT * FROM EMP WHERE empno = 778899;
+--------+--------+----------+------+---------------------+---------+--------+--------+
| empno  | ename  | job      | mgr  | hiredate            | sal     | comm   | deptno |
+--------+--------+----------+------+---------------------+---------+--------+--------+
| 778899 | uYJyqi | SALESMAN | 0001 | 2023-09-25 00:00:00 | 2000.00 | 400.00 |    433 |
+--------+--------+----------+------+---------------------+---------+--------+--------+
1 row in set (14.68 sec)

我们发现每次查询时间都在10几秒，如果这种速度运用在业务上的话。首先客户肯定是等不了这么久的，其次如果是1000W条数据高并发进行查询。那么服务器直接承受不住压力就挂了。

那么我们给empno列加上索引试试呢？

ALTER TABLE 表名 add index(字段) ;添加普通索引

添加索引的过程可能会比较慢，我这边花了1分钟左右。

mysql> ALTER TABLE EMP add index(empno);
Query OK, 0 rows affected (57.26 sec)
Records: 0  Duplicates: 0  Warnings: 0

然后我们再对778899编号的员工进行查询。

mysql> SELECT * FROM EMP WHERE empno = 778899;
+--------+--------+----------+------+---------------------+---------+--------+--------+
| empno  | ename  | job      | mgr  | hiredate            | sal     | comm   | deptno |
+--------+--------+----------+------+---------------------+---------+--------+--------+
| 778899 | uYJyqi | SALESMAN | 0001 | 2023-09-25 00:00:00 | 2000.00 | 400.00 |    433 |
+--------+--------+----------+------+---------------------+---------+--------+--------+
1 row in set (0.00 sec)

mysql> SELECT * FROM EMP WHERE empno = 778899;
+--------+--------+----------+------+---------------------+---------+--------+--------+
| empno  | ename  | job      | mgr  | hiredate            | sal     | comm   | deptno |
+--------+--------+----------+------+---------------------+---------+--------+--------+
| 778899 | uYJyqi | SALESMAN | 0001 | 2023-09-25 00:00:00 | 2000.00 | 400.00 |    433 |
+--------+--------+----------+------+---------------------+---------+--------+--------+
1 row in set (0.00 sec)

mysql> SELECT * FROM EMP WHERE empno = 778899;
+--------+--------+----------+------+---------------------+---------+--------+--------+
| empno  | ename  | job      | mgr  | hiredate            | sal     | comm   | deptno |
+--------+--------+----------+------+---------------------+---------+--------+--------+
| 778899 | uYJyqi | SALESMAN | 0001 | 2023-09-25 00:00:00 | 2000.00 | 400.00 |    433 |
+--------+--------+----------+------+---------------------+---------+--------+--------+
1 row in set (0.00 sec)

我们惊奇的发现，每次查询比都是0.00秒就结束了！这是多么恐怖的速度！添加索引后居然有这么明显的速度提升！

接下来我们就慢慢揭开索引的面纱，探索索引的奥妙！

磁盘结构

MySql与存储

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

认识磁盘

本人在 带你深入理解文件系统 这篇文章详细介绍过磁盘的结构，所以这里就简单的带过一下。

首先我们的磁盘是有多个盘面的，这一个个盘面被合起来称为磁柱。当磁盘进行读写时，首先通过磁头定位到哪一个磁面。每一个磁面都有多个磁道，随即再定位到磁道的位置。而磁道上又有多个扇区，每一个扇区是512字节。只要我们能找到扇区，就能找到数据的存储位置，这种查找方式被称位CHS！有了CHS，我们可以很轻松的定位到任意一个扇区，找到对应的位置。

但是从操作系统的角度来看，OS磁盘抽象成了一个线性数组，通过数组的下标与每一个扇区进行映射。这种方式被称为LBA。软件上查找磁盘位置的方式是通过LBA，而硬件上则是通过CHS。

虽然每个扇区都是512字节(先进的磁盘是4kb)，但是文件系统读取的基本单位是4kb！也就是说你这个扇区哪怕只存了1个字节，操作系统读取时也必须按照4KB读取！

为什么不按照512字节读取呢？

原因一：如果按照512字节读取，那么就增加了IO的次数，众所周知，IO是非常慢的！这样就大大降低了效率！

原因二： 对磁盘进行读取是软件层面上的，如果软件层面和硬件层面不解耦。那么硬件如果升级改变了，那么操作系统是不是也要修改一下？这是极其不合理的。

原因三： 文件系统中读取的基本单位是data blocks(具体请看带你深入理解文件系统 )，每一个data blocks是一个4字节的块！也就是说文件系统读取的最低大小就是4字节！

所以，系统读取磁盘，是以块为单位的，基本都是4字节！

Mysql与磁盘交互基本单位

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

mysql>  SHOW GLOBAL STATUS LIKE 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.01 sec)

16384 / 1024 = 16。

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

实际上，MySQL是无法绕过操作系统直接与磁盘交互的，而操作系统与磁盘交互的单位是4kb，这也就意味着一个page在操作系统角度其实是进行了4次IO！但是在MySQL的角度，只进行了一次IO！

小结

• MySQL中的数据文件，都是以page为单位保存在磁盘当中的！在MySQL的角度看是page，在操作系统的角度看是4个data blocks块，在磁盘的角度看是32个扇区！
• MySQL的CURD(增改查删)操作，都需要通过计算，找到对应的插入位置，或者找到对应要修改或者查询的数据。
• 只要涉及计算，就一定需要CPU参与，一定要先将数据移动到内存当中。
• 在一段特定的时间内，数据一定在磁盘和内存中都存在！因为要对数据进行CURD就必须通过计算，而要计算就必须要CPU参与，要CPU参与就必须把数据加载到内存！在内存修改完数据后，一定又会以特定的策略刷新回磁盘！而这个过程其实就算内存和磁盘交互的过程，也就是IO，此时IO的基本单位为Page(站在数据库的角度来看)。
• 既然知道MySQL的数据是一定会被加载到内存中的，如果每次查询都加载一次，都开辟一次内存空间，是不是太慢了？所以MySQL在内存中也必须要有一个缓冲区！一次开辟一个大的缓冲区，进行各种缓存，来和磁盘数据进行长久(服务一直运行，该内存一直存在)的IO交互。这个缓冲区被称为Buffer Pool，这个缓冲区一般大小为128MB，如果不够会自动扩容。
• 因为IO是非常慢的，如果要提高效率，那么必须尽可能的减少IO次数！

索引的原理

现象

我们先建立一个测试表，看一个现象。

注意：当我们给字段添加主键时，会自动生成主键索引，添加唯一键时也会自动添加唯一索引。

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)

创建完user表后，我们添加多条记录。

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

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

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

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

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

然后我们查看user表，我们会惊人的发现，user表居然自动按id排好序了！可是插入的时候是乱序的！

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

为什么会这样？

举个例子：

假设我们这里有一本500页的字典，这个字典没有目录，并且页码是乱序的。那么你想要查东西是非常困难的，对不对？ 那么如果引入目录呢？ 那是不是就可以很轻易的定义到每一页！但是如果你的页码是乱序的呢？即使你目录标注了页码，但是因为页码也是乱序的，你还是要重新遍历一遍去找页码！ 是不是一样非常麻烦？ 效率严重降低？

所以！索引自动排序的本质，是为了引入目录！而主键的值，可以看成页码！而页码必须要有序，也是为了方便目录找到页码！

重谈page

从上面的内容，我们已经知道了page是一个16KB大小的单元。在MySQL的角度来看，page是与磁盘交互的基本单位。但page里的结构究竟是怎么样的？

在MySQL的内部，一定会存在大量的page，这也就决定了，MySQL必须将所有page管理起来！如何管理？先描述，再组织！

所以，不要简单的将page认为是一个内存块，page内部页必须写入对应的管理信息！

struct page
{
    struct page* next;
    struct page* prev;
    char buffer[NUM];
} //整个page的大小16kb，new page，将所有的page用"链表"(实际上没有这么简单)的形式管理起来，在buffer pool内部，对mysql中的page进行了一个建模！

page的结构示意图：

不同的 Page ，在 MySQL 中，都是 16KB ，使用 prev 和 next 构成双向链表。

以上谈的都是单个page，那么接下来来谈谈多个page的情况。

理解多个page

通过上面的分析，我们知道，上面的页模式中，只有一个功能，就是在查询某条数据的时候直接将一整页(page)的数据加载到内存中，以减少硬盘的IO次数，从而提高性能。但是，我们也可以看到，现在的页模式内部，实际上是采用了链表的结构，前一条数据指向后一条数据，本质上还是通过数据的逐步比较取出特定的数据。如果这时候有1000W条数据，一定要多个page来保存1千万条数据，多个page彼此都用双链表连接，而且page内部的数据也是基于链表的。那么查找特定一条记录，也一定是线性结构的。这样效率同样很低！

假设有1000W条数据，1条数据是512个字节。那么1页只能存储32条数据，那么需要30多W个page。如果仅仅是双链表的结构，那么也要遍历30多W次，还有page内部也要进行遍历，那么这效率太低了！

所以MySQL在page里面引入了目录！这样可以快速找到一个page内的数据！

引入目录后的情况是这样的：

把每一个主键(id)看成一个页码，而目录里面标注着页码信息。而目录根据标注的页码进行排序，当我们锁定到数据在某一个page内时，我们可以通过索引值锁定在哪一个目录，就比如我要找的是id为3的用户。那么我们可以根据二分查找找到目录1，因为目录2存的是id为4的数据。而3小于4，则说明数据是在目录1中。然后就算遍历一个目录内的内容(范围：后一个目录的id值 - 去当前目录的id值)。

这也就是为什么有主键时，乱序插入会自动按主键排序的原因，把主键看成页码！页码需要有序才能方便准确的定位！

可是，问题并没有解决，现在只解决了单个page内部的效率问题，如果page很多很多，我们不还是要把所有的page遍历一遍？

那么如何解决整个问题呢？

很简单！谁说page只能拿来存数据的，我page的目录也可以存其他page的地址！

假如在32位系统下，一个指针是4个字节。1个page是16KB，那么就可以存储4096的page的地址(可能会少一点)。这样我们只需要通过二分查找查找目录，就可以直接定位到下层的page了！这样是不是就大大的减少了IO效率？要记住！遍历每一个page都要将page加载进内存！而IO效率是非常慢的。上面的page不存储数据，只存储下层page的地址，这些上层的page被称为目录页 ，而最底层的page被称为普通页 。

假设一个目录页可以保存4000个普通页，也就是60MB左右，如果我们再套一层一目录页，这个目录页存的都是下一个目录页的地址，那么就是 4000 * 4000 * 16 大概就是240GB左右。而这仅仅是3层B+树，还可以继续往上套！每套一层都是一个指数级的增长。恐怖如斯！！

小结

• 使用一个目录项来指向某一页，而这个目录项存放的就是将要指向的页中存放的最小数据的键值。

• 和页内目录不同的地方在于，这种目录管理的级别是页，而页内目录管理的级别是行。

• 其中，每个目录项的构成是：键值+指针。图中没有画全。

InnoDB 在建立索引结构来管理数据的时候，其他数据结构为何不行？

**链表为什么不行？ **

因为链表遍历是线性结构！O(N)的复杂度，当有1000W条数据时需要进行1000W次IO，恐怖如斯，效率低下，所以不行！

二叉搜索树为什么不行？

二叉搜索树有个致命的缺点，就是会退化成链表。退化成链表就和上面的情况一下，恐怖如斯了。

AVL&&红黑树为什么不行？

我承认，AVL和红黑树的查询效率十分优秀。但是这次情况不同，这次情况是需要内存和磁盘进行IO。假设有10亿条数据，用AVL树可能需要进行IO30次，用红黑树可能需要IO60次。查询次数就是IO次数，即使很优秀，但在这方面，B+树更胜一筹。

HASH为什么不行？

哈希表也很优秀，但别忘了，哈希表有个致命缺点！那就是不有序！不有序的话，如果要查找一段区间的值呢？那是不是很麻烦！所以哈希表也不行。

B+树为什么行？

因为B+树是一颗矮胖树，底层的兄弟节点及其之多，但是高度却特别特别矮。3层B+树就差不多能存储200多GB的数据，红黑树和AVL树纵然很强，但是太高了，一层进行一次IO，红黑树和AVL树要进行的IO次数肯定是比B+多的。

B+树 VS B树

B树：

B+树：

B+树和B树最明显的区别我认为有两个。

一：B树的节点是既存数据，又存page指针，而B+树，只要叶子节点存数据，其他节点全部存page指针

二：B+叶子节点全部用双链表连接，而B树没有

InnoDB为什么选择B+?

• 节点不存储数据值，就意味着可以存储更多的page地址，可以使树变得更矮，IO操作更少。
• 叶子节点相连，方便找区间

聚簇索引 VS 非聚簇索引

MyISAM 存储引擎-主键索引

MyISAM 引擎同样使用B+树作为索引结果，叶节点的data域存放的是数据记录的地址。下图为 MyISAM 表的主索引， Col1 为主键。

其中， MyISAM 最大的特点是，将索引Page和数据Page分离，也就是叶子节点没有数据，只有对应数据 的地址。

相较于 InnoDB 索引， InnoDB 是将索引和数据放在一起的。

InnoDB 这种用户数据与索引数据在一起索引方案，叫做聚簇索引 当然， MySQL 除了默认会建立主键索引外，我们用户也有可能建立按照其他列信息建立的索引，一般这 种索引可以叫做辅助（普通）索引。 对于 MyISAM ,建立辅助（普通）索引和主键索引没有差别，无非就是主键不能重复，而非主键可重复。 下图就是基于 MyISAM 的 Col2 建立的索引，和主键索引没有差别

InnoDB除了主键索引，也可以创建普通索引，如图：

我们可以看到，InnoDB的非主键索引中的叶子节点并没有数据，而只有对应记录的主键(key)值。

所以InoodDB如果通过普通索引去找的话，会先找一遍找到key值，也就是主键，再通过这个key值回去再找一次获得记录。这个过程，也叫做回表查询。

为何 InnoDB 针对这种辅助（普通）索引的场景，不给叶子节点也附上数据呢？

如果给普通索引也放上数据，那么意味着什么？意味一个数据在多个索引里面，意味一份数据被多次存储，这是一种很浪费空间的行为！因为一份资源存了多份！而存key值再通过key值去找到对应的记录，可以保证数据自始至终只被存储一份。

索引的使用

创建主键索引

就是创建主键，没什么好说的，创建主键就自动创建了主键索引。

添加主键的方式有三种：

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

-- 在创建表的最后，指定某列或某几列为主键索引
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);

主键索引的特点

1. 1个表中最多只有1个主键索引

2. 主键索引效率高 (主键不可重复)

3. 创建主键索引的列，它的值不能为null，也不能重复。

4. 主键索引的列基本上是int

唯一索引的创建

和添加主键差不多，只要加上唯一键约束，就有唯一索引了。

添加唯一索引的三种方式：

-- 1.在表定义时，在某列后直接指定unique唯一属性。
create table user4(id int primary key, name varchar(30) unique);

-- 2.创建表时，在表的后面指定某列或某几列为unique
create table user5(id int primary key, name varchar(30), unique(name))


--  3.创建表之后添加唯一索引
create table user6(id int primary key, name varchar(30)）；
alter table user6 add unique(name);

唯一索引的特点

1. 一个表中，可以有多个

2. 查询效率高

3. 如果在某一列建立唯一索引，必须保证这列不能有重复数据

普通索引的创建

第一种方式：

create table user8(id int primary key,
name varchar(20),
email varchar(30),
index(name) 
);
Query OK, 0 rows affected (0.19 sec)

然后我们show index from 表名; 或者 show keys from 表名; 来查看表内部的索引。

mysql> show index FROM user8\G;
*************************** 1. row ***************************
        Table: user8
   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: user8
   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)

我们可以看到user8表中有一个PRIMARY索引，这是主键索引，还有一个name索引，这是我们添加的普通索引。注意！索引是key_name,Column_name是加了索引的列名，和key_name是两个完全不同的概念！不指定索引名是会和列名一样，但不代表意义相同！

第二种方式：

创建完表之后再添加索引，如果是主键或者唯一索引，确保列中没有重复数据！

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

第三种方式：

一样是先创建表后添加索引，格式为create index 索引名(key_name) on 表名(列名);

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

普通索引的特点

1.一个表中可以有多个普通索引，普通索引在实际开发中用的比较多

2. 如果某列需要创建索引，但是列里有重复的值，可以用普通索引。

全文索引的创建

当文章字段或有大量文字的字段进行检索时，比如一个文章库全是文章，你想查找某某内容。就会用到全文索引。MYSQL提供全文索引的机制，但是有要求，要求表的存储引擎必须是MyISAM，而且默认的全文索引不支持中文。如果对中文进行全文检索，可以使用sphinx的中文版(coreseek)

创建一个表，设置存储引擎为MyISAM

CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body)
)engine=MyISAM;

插入多条评论

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 ...');

使用全文索引

语法： WHERE MATH(字段名,字段名) 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)

感觉看不出全文索引啊？我们可以用explain来查看。

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)

我们把MATH去掉，不用全文索引，单纯的来查找呢？

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.01 sec)

我们会发现它的索引为null。

查询索引

第一种方法

mysql> show keys from goods\G
*********** 1. row ***********
Table: goods <= 表名
Non_unique: 0 <= 0表示唯一索引
Key_name: PRIMARY <= 主键索引
Seq_in_index: 1
Column_name: goods_id <= 索引在哪列
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE <= 以二叉树形式的索引
Comment:
1 row in set (0.00 sec)

第二种方法

show index from 表名;

第三种方法

desc 表名;

删除索引

方法一：

我们删除主键约束，就会自动删除主键索引。语法：alter table 表名 drop primary key;

mysql> ALTER TABLE user8 drop primary key;
Query OK, 0 rows affected (0.89 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from user8\G;
*************************** 1. row ***************************
        Table: user8
   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: 
1 row in set (0.00 sec)

方法二：

删除其他索引： alter table 表名 drop index 索引名; 索引名就是show keys from 表名中的 Key_name 字段

mysql> ALTER TABLE user8 DROP index name;
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

方法三：

drop index 索引名 on 表名

总结

本篇文章的重点还是MySQL的索引原理。MySQL的存储也是类似文件系统的那样的东西，只不过文件系统的data blocks是以4kb为单位，而MySQL则是16kb的page为单位。在InooDB下，每个叶子节点的page页存储的都是数据，非叶子节点的页也被称为目录页，目录页只存储其他页的地址，不存储数据。而在学习的过程中我也遇到了一些疑问，将在下面分享给大家。

疑问一：每次查询都要加载一次B+树吗？

答案是否定的，因为每次查询都加载一次，就疑问IO效率很低，如果B+树已经构建好了的话，则不需要。

疑问二：每次加载是加载B+树整棵树，还是一部分？

毫无疑问，肯定是一部分，如果加载整棵树，在数据量庞大的情况下，效率肯定是有影响的。因为IO效率低，每次查询，会根据主键找到对应的页地址。如果这个页还没有被加载，那么会先去磁盘加载这个页，加载完之后再把这个页的地址填上(加载实际上就是从磁盘加载进内存)。至于磁盘怎么加载这个页或者说怎么准确的加载到这个页，那肯定有数据库的一套规则，就像文件系统的data bolcks块能清除的定义到磁盘的某个扇区一样。数据库也一定有知道自己对应的存储的页在哪块磁盘空间的能力。