文章目录
MySQL索引
1、使用索引的必要性
索引：提高数据库的性能，索引是物美价廉的东西了。不用加内存，不用改程序，不用调sql，只要执行正确的
create index语句 ，查询速度就可能提高成百上千倍。但是天下没有免费的午餐，查询速度的提高是以插入、更新、删除的速度为代价的，这些写操作，增加了大量的IO。所以它的价值，在于提高一个海量数据的检索速度。常见索引分为：
主键索引(primary key)
唯一索引(unique)
普通索引(index)
全文索引(fulltext) – 解决中子文索引问题
案例：先创建一个表并插入海量数据，在查询的时候，看看没有索引时有什么问题。
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 ; -- 执行存储过程，添加8000000条记录 CALL insert_emp(100001, 8000000);到此，已经创建出了海量数据的表了。
查看有多少条数据：
mysql> select count(*) from EMP; +----------+ | count(*) | +----------+ | 8000013 | +----------+ 1 row in set (1.57 sec) mysql>
- 查询名字为smith的员工。
mysql> select * from EMP where ename='smith'; +-------+-------+-------+------+------------+--------+------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+-------+-------+------+------------+--------+------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | +-------+-------+-------+------+------------+--------+------+--------+ 1 row in set (2.84 sec)发现耗时2.84秒。这还是在本机一个人来操作，在实际项目中，如果放在公网中，假如同时有1000个人并发查询，那很可能就死机。（这里如果是查询编号很大的员工，可能查询会很快，因为员工号是主见，默认创建了主键索引，下面会讲）
- 给ename创建索引，看看查询其他人的速度。
mysql> alter table EMP add index(ename); Query OK, 0 rows affected (34.14 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from EMP where ename='king'; +-------+-------+-----------+------+------------+---------+------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+-------+-----------+------+------------+---------+------+--------+ | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | +-------+-------+-----------+------+------------+---------+------+--------+ 1 row in set (0.00 sec) mysql>发现查询速度变快了很多！
2、认识磁盘
2.1、MySQL与存储
MySQL 给用户提供存储服务，而存储的都是数据，数据在磁盘这个外设当中。磁盘是计算机中的一个机械设备，相比于计算机其他电子元件，磁盘效率是比较低的，在加上IO本身的特征，可以知道，如何提交效率，是 MySQL 的一个重要话题。
磁盘结构：我们之前在前面的博客有讲过文件系统inode，链接。
我们在前面学习MySQL的时候，比如在创建数据库、表等结构的时候，是会创建对应的文件的。
root@VM-8-12-ubuntu:/var/lib/mysql/test5# ll total 668880 drwxr-x--- 2 mysql mysql 4096 Aug 27 10:46 ./ drwxr-x--- 15 mysql mysql 4096 Aug 18 16:38 ../ -rw-r----- 1 mysql mysql 61 Jul 20 12:06 db.opt -rw-r----- 1 mysql mysql 8624 Aug 19 13:24 DEPT.frm -rw-r----- 1 mysql mysql 98304 Aug 19 13:24 DEPT.ibd -rw-r----- 1 mysql mysql 8586 Jul 25 13:16 duplicate_table.frm -rw-r----- 1 mysql mysql 98304 Jul 25 13:18 duplicate_table.ibd -rw-r----- 1 mysql mysql 8780 Aug 27 10:12 EMP.frm -rw-r----- 1 mysql mysql 683671552 Aug 27 10:13 EMP.ibd -rw-r----- 1 mysql mysql 8588 Aug 25 21:19 exam.frm -rw-r----- 1 mysql mysql 98304 Aug 25 21:19 exam.ibd -rw-r----- 1 mysql mysql 8730 Jul 20 12:53 exam_result.frm -rw-r----- 1 mysql mysql 98304 Jul 23 20:30 exam_result.ibd -rw-r----- 1 mysql mysql 8586 Jul 23 20:33 for_delete.frm -rw-r----- 1 mysql mysql 98304 Jul 23 20:35 for_delete.ibd -rw-r----- 1 mysql mysql 8586 Jul 25 13:01 for_truncate.frm -rw-r----- 1 mysql mysql 98304 Jul 25 13:06 for_truncate.ibd -rw-r----- 1 mysql mysql 8586 Jul 25 13:15 old_duplicate_table.frm -rw-r----- 1 mysql mysql 98304 Jul 25 13:15 old_duplicate_table.ibd -rw-r----- 1 mysql mysql 8626 Aug 19 13:30 SALGRADE.frm -rw-r----- 1 mysql mysql 98304 Aug 19 13:30 SALGRADE.ibd -rw-r----- 1 mysql mysql 8644 Jul 20 12:07 students.frm -rw-r----- 1 mysql mysql 114688 Jul 20 12:39 students.ibd -rw-r----- 1 mysql mysql 8586 Aug 25 21:19 stu.frm -rw-r----- 1 mysql mysql 98304 Aug 25 21:19 stu.ibd -rw-r----- 1 mysql mysql 8614 Aug 27 10:46 user.frm -rw-r----- 1 mysql mysql 98304 Aug 27 13:01 user.ibd root@VM-8-12-ubuntu:/var/lib/mysql/test5#所以，最基本的，找到一个文件的全部，本质就是在磁盘找到所有保存文件的扇区。而我们能够定位任何一个扇区，那么便能找到所有扇区，因为查找方式是一样的。
跟你上面磁盘（点链接），我们现在已经能够在硬件层面定位，任何一个基本数据块了(扇区)。那么在系统软件上，就直接按照扇区(512字节，部分4096字节)，进行IO交互吗？不是
如果操作系统直接使用硬件提供的数据大小进行交互，那么系统的IO代码，就和硬件强相关。换言之，如果硬件发生变化，系统必须跟着变化。
从目前来看，单次IO 512字节，还是太小了。IO单位小，意味着读取同样的数据内容，需要进行多次磁盘访问，会带来效率的降低。
之前学习文件系统，就是在磁盘的基本结构下建立的，文件系统读取基本单位，就不是扇区，而是数据块。
故，系统读取磁盘，是以块为单位的，基本单位是 4KB。
2.2、磁盘随机访问(Random Access)与连续访问(Sequential Access)
随机访问：本次IO所给出的扇区地址和上次IO给出扇区地址不连续，这样的话磁头在两次IO操作之间需要作比较大的移动动作才能重新开始读/写数据。因此较慢。
连续访问：如果当次IO给出的扇区地址与上次IO结束的扇区地址是连续的，那磁头就能很快的开始这次IO操作，这样的多个IO操作称为连续访问。因此较快。
因此尽管相邻的两次IO操作在同一时刻发出，但如果它们的请求的扇区地址相差很大的话也只能称为随机访问，而非连续访问。
磁盘是通过机械运动进行寻址的，随机访问不需要过多的定位，故效率比较高。
3、 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.00 sec) mysql>也就是说，磁盘这个硬件设备的基本单位是 512 字节，而 MySQL InnoDB引擎 使用 16KB 进行IO交互。即 MySQL 和磁盘进行数据交互的基本单位是 16KB 。这个基本数据单元，在 MySQL 这里叫做page（注意和系统的page区分）。
也就是创建表的时候，直接给该表分配16KB，不够再扩容。
4、预备知识
MySQL 中的数据文件，是以page为单位保存在磁盘当中的。
MySQL 的 CURD 操作，都需要通过计算，找到对应的插入位置，或者找到对应要修改或者查询的数据。
而只要涉及计算，就需要CPU参与，而为了便于CPU参与，一定要能够先将数据移动到内存当中。
所以在特定时间内，数据一定是磁盘中有，内存中也有。后续操作完内存数据之后，以特定的刷新策略，刷新到磁盘。而这时，就涉及到磁盘和内存的数据交互，也就是IO了。而此时IO的基本单位就是Page。
为了更好的进行上面的操作， MySQL 服务器在内存中运行的时候，在服务器内部，就申请了被称为 Buffer Pool 的的大内存空间，来进行各种缓存。其实就是很大的内存空间，来和磁盘数据进行IO交互。
查看buffer pool大小：
mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; +-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ | innodb_buffer_pool_size | 134217728 | +-------------------------+-----------+ 1 row in set (0.04 sec) mysql>图示：
为何更高的效率，一定要尽可能的减少系统和磁盘IO的次数。
5、索引的理解
建立测试表：
mysql> create table if not exists user ( -> id int primary key, -- 一定要添加主键哦，只有这样才会默认生成主键索引 -> age int not null, -> name varchar(16) not null -> ); Query OK, 0 rows affected (0.07 sec) 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 1 row 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.00 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) mysql>
6、为什么IO交互的大小是Page
这里就涉及计算机组成原理的知识了，涉及到局部性原理（简单来说就是现在访问的数据及其相邻的数据，在未来某个时间可能还会访问）。
为何MySQL和磁盘进行IO交互的时候，要采用Page的方案进行交互呢？用多少，加载多少不香吗？
如上面的5条记录，如果MySQL要查找id=2的记录，第一次加载id=1，第二次加载id=2，一次一条记录，那么就需要2次IO。
如果要找id=5，那么就需要5次IO。
但，如果这5条(或者更多)都被保存在一个Page中(16KB，能保存很多记录)，那么第一次IO查找id=2的时候，整个Page会被加载到MySQL的Buffer Pool中，这里完成了一次IO。但是往后如果在查找id=1，3，4，5等，完全不需要进行IO了，而是直接在内存中进行了。所以，就在单Page里面，大大减少了IO的次数。
你怎么保证，用户一定下次找的数据，就在这个Page里面？我们不能严格保证，但是有很大概率，因为有局部性原理。往往IO效率低下的最主要矛盾不是IO单次数据量的大小，而是IO的次数。
6.1、理解单个Page
6.1.1、单个Page
MySQL 中要管理很多数据表文件，而要管理好这些文件，就需要 先描述，再组织 ，我们目前可以简单理解成一个个独立文件是有一个或者多个Page构成的。
不同的 Page ，在 MySQL 中，都是 16KB ，使用 prev 和 next 构成双向链表
因为有主键的问题， MySQL 会默认按照主键给我们的数据进行排序，从上面的Page内数据记录可以看出，数据是有序且彼此关联的。
为什么数据库在插入数据时要对其进行排序呢？我们按正常顺序插入数据不是也挺好的吗？
答：插入数据时排序的目的，就是优化查询的效率（学完后面B+树就理解了）。
页内部存放数据的模块，实质上也是一个链表的结构，链表的特点也就是增删快，查询修改慢，所以优化查询的效率是必须的。
正式因为有序，在查找的时候，从头到后都是有效查找，没有任何一个查找是浪费的，而且，如果运气好，是可以提前结束查找过程。
6.1.2、引入目录
针对上面的单页Page，怎么使得查询效率更高呢？引入目录！
引入目录前，我们查询5号郭靖需要从1开始遍历（或者说效率高使用二分法）。
引入目录后，查询5号，因为5>3，因此我们先找到2[3]，然后从3开始往后找，效率更高（这里还可以使用二分法）。
那么这里就可以回答为什么通过主键MySQL要自动排序？方便使用目录结构。
6.2、理解多个Page
6.2.1、多个Page
通过上面的分析，我们知道，上面页模式中，只有一个功能，就是在查询某条数据的时候直接将一整页的数据加载到内存中，以减少硬盘IO次数，从而提高性能。但是，我们也可以看到，现在的页模式内部，实际上是采用了链表的结构，前一条数据指向后一条数据，本质上还是通过数据的逐条比较来取出特定的数据。
如果有1千万条数据，一定需要多个Page来保存1千万条数据，多个Page彼此使用双链表链接起来，而且每个Page内部的数据也是基于链表的。那么，查找特定一条记录，也一定是线性查找。这效率也太低了。
比如上面三个Page的数据都是一张表中的，那我们要找15号郭靖，还是得先从第一个Page开始找！
解决办法：继续引入多级目录（给Page也装上目录）！
6.2.2、引入多级目录
使用一个目录项来指向某一页（Page），而这个目录项存放的就是将要指向的页中存放的最小数据的键值。和页内目录不同的地方在于，这种目录管理的级别是页，而页内目录管理的级别是行。
其中，每个目录项的构成是：键值+指针。图中没有画全。
存在一个目录页来管理页目录，目录页中的数据存放的就是指向的那一页中最小的数据。有数据，就可通过比较，找到该访问那个Page，进而通过指针，找到下一个Page。
其实目录页的本质也是页，普通页中存的数据是用户数据，而目录页中存的数据是普通页的地址。
可是，我们每次检索数据的时候，该从哪里开始呢？虽然顶层的目录页少了，但是还要遍历啊？不用担心，可以在加目录页。
一般来说，三层到四层就到顶了，因为数据也就那么大。比如一个只有索引Page可以存多少索引？16KB/4B=4K条（假设索引占4B）。
假设3层，那么就是
4K*4K*16KB=256GB。
这货就是传说中的B+树啊（不过B+树的非叶子结点不会链接起来）！没错，至此，我们已经给我们的表user构建完了主键索引。
这里随便查找一个id，只需要访问3次磁盘！
7、InnoDB 在建立索引结构来管理数据的时候，其他数据结构为何不行
- 链表？线性遍历。
- 二叉搜索树？退化问题，可能退化成为线性结构。
- AVL 或者红黑树？虽然是平衡或者近似平衡，但是毕竟是二叉结构，相比较多阶B+，意味着树整体过高，大家都是自顶向下找，层高越低，意味着系统与硬盘更少的IO Page交互。虽然你很秀，但是有更秀的。
- Hash？官方的索引实现方式中， MySQL 是支持HASH的，不过 InnoDB 和 MyISAM 并不支持。Hash跟进其算法特征，决定了虽然有时候也很快(O(1))，不过，在面对范围（一个数据的附近）查找就明显不行，另外还有其他差别，有兴趣可以查一下。
存储引擎底层索引对比：
存储引擎 底层索引结构 特点 InnoDB B+树 InnoDB 是 MySQL 默认的存储引擎，使用 B+树结构的索引来存储数据。B+树结构允许高效的范围查询、顺序访问和快速查找。在 InnoDB 中，主键索引是聚簇索引，数据存储在叶子节点上。 MyISAM B+树 MyISAM 也使用 B+树结构的索引，但与 InnoDB 不同的是，MyISAM 的索引是非聚簇索引，叶子节点存储的是数据的指针而不是数据本身。因此，MyISAM 的查询速度快于 InnoDB，但不支持事务和外键。 TokuDB Fractal Tree TokuDB 使用一种叫做 Fractal Tree（分形树）的数据结构，这种结构在处理写操作（特别是批量写操作）时具有更好的性能表现。它适用于大数据集，减少了磁盘 I/O 操作，提供了压缩功能。 RocksDB LSM 树 RocksDB 是基于 LSM（Log-Structured Merge）树的存储引擎。LSM 树适合高写入场景，通过将数据分批写入磁盘来减少写入放大。RocksDB 常用于需要高写入性能的场景，如日志系统。 AriaDB B+树 Aria 是 MyISAM 的改进版，使用 B+树作为其索引结构。它可以在崩溃恢复时更快地重建索引，且支持并发查询，但写操作性能较 MyISAM 略差。 Memory 哈希表 Memory 存储引擎将数据存储在内存中，使用哈希表作为索引结构。这种结构使得查找操作非常快，但只适用于需要快速读写的小型数据集，且数据在服务器关闭时会丢失。 Cassandra SSTable + LSM树 Cassandra 使用 SSTable（Sorted String Table）与 LSM 树的组合来管理数据。LSM 树适合高写入吞吐量的场景，而 SSTable 提供了高效的读操作。Cassandra 广泛用于分布式数据库系统。
- InnoDB 为何不用B树作为底层索引?
我们下面来对比一下B+树和B树的特性。
7.1、B+树和B树比较
- B树结构：
- B+树结构：
目前这两棵树，对我们最有意义的区别是：
- B树节点，既有数据，又有Page指针。而B+树，只有叶子节点有数据，其他目录页，只有键值和Page指针
- B+叶子节点，全部相连（方便范围查找），而B没有
- 为何选择B+
- 节点不存储data，这样一个节点就可以存储更多的key。可以使得树更矮，所以IO操作次数更少。
- 叶子节点相连，更便于进行范围查找。
7.2、聚簇索引 VS 非聚簇索引
7.2.1、聚簇索引
InnoDB存储引擎是主键采用聚簇索引，也就是叶子结点直接存储数据。下图为 InnoDB 表的主索引，第一行为主键。
下面我们创建一个使用 InnoDB 存储引擎的表：
- 终端A：建数据库和建表。
mysql> create database innodb_test; Query OK, 1 row affected (0.00 sec) mysql> use innodb_test; Database changed mysql> create table itest( -> id int primary key, -> name varchar(11) not null -> )engine=InnoDB; -- 使用InnoDB 存储引擎 Query OK, 0 rows affected (0.03 sec) mysql>
- 终端B：查看建表后的文件类型（路径：
/var/lib/mysql）。root@VM-8-12-ubuntu:/var/lib/mysql/innodb_test# ll total 120 drwxr-x--- 2 mysql mysql 4096 Aug 31 10:46 ./ drwxr-x--- 17 mysql mysql 4096 Aug 31 10:44 ../ -rw-r----- 1 mysql mysql 61 Aug 31 10:44 db.opt -rw-r----- 1 mysql mysql 8586 Aug 31 10:46 itest.frm -- 表结构数据 -rw-r----- 1 mysql mysql 98304 Aug 31 10:46 itest.ibd -- 该表对应的主键索引和用户数据，虽然现在一行数据没有，但是该表并不为0，因为有主键索引数据 root@VM-8-12-ubuntu:/var/lib/mysql/innodb_test#InnoDB 在叶子结点这种用户数据与索引数据在一起索引方案，叫做聚簇索引。
7.2.2、非聚簇索引
MyISAM 引擎同样使用B+树作为索引结果，叶节点的data域存放的是数据记录的地址。下图为 MyISAM 表的主索引，第一列为主键。
其中， MyISAM 最大的特点是，将索引Page和数据Page分离，也就是叶子节点没有数据，只有对应数据的地址。相较于 InnoDB 索引， InnoDB 叶子结点是将索引和数据放在一起的。
下面我们创建一个使用 MyISAM 存储引擎的表：
- 终端A：建数据库和建表。
mysql> create database myisam_test; Query OK, 1 row affected (0.00 sec) mysql> use myisam_test; Database changed mysql> create table mtest( -> id int primary key, -> name varchar(11) not null -> )engine=MyISAM; -- 使用 MyISAM 存储引擎 Query OK, 0 rows affected (0.01 sec) mysql>
- 终端B：查看建表后的文件类型（路径：
/var/lib/mysql）。root@VM-8-12-ubuntu:/var/lib/mysql/myisam_test# ll total 28 drwxr-x--- 2 mysql mysql 4096 Aug 31 10:37 ./ drwxr-x--- 16 mysql mysql 4096 Aug 31 10:34 ../ -rw-r----- 1 mysql mysql 61 Aug 31 10:34 db.opt -rw-r----- 1 mysql mysql 8586 Aug 31 10:37 mtest.frm -- 表结构数据 -rw-r----- 1 mysql mysql 0 Aug 31 10:37 mtest.MYD -- 该表对应的数据，当前没有数据，所以是0 -rw-r----- 1 mysql mysql 1024 Aug 31 10:37 mtest.MYI -- 该表对应的主键索引数据 root@VM-8-12-ubuntu:/var/lib/mysql/myisam_test#MyISAM 在叶子结点这种用户数据与索引数据分离的索引方案，叫做非聚簇索引。
7.2.3、非主键索引
上边两种索引，只介绍了主键索引。但是它们都可以有非主键索引（普通索引）（需要自己手动设置，下面会讲）。
对于 MyISAM 的普通索引，和主键索引差不多，只是主键不能重复，而非主键可以重复。如下图：
InnoDB 除了主键索引，用户也会建立辅助（普通）索引。
可以看到， InnoDB 的非主键索引中叶子节点并没有数据，而只有对应记录的key值。所以通过辅助（普通）索引，找到目标记录，需要两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。这种过程，就叫做回表查询。为何InnoDB 针对这种辅助（普通）索引的场景，不给叶子节点也附上数据呢？原因就是太浪费空间了。
8、索引操作
8.1、创建主键索引
- 第一种方式：在建表的时候，指定主键（设置primary key）。
create table user1(id int primary key,name varchar(11) not null);或者
create table user2(id int,name varchar(11) not null,primary key(id));
- 第二种方式：建表之后再添加主键。
create table user3(id int,name varchar(11) not null); alter table user3 add primary key(id);主键索引的特点：
- 一个表中，最多有一个主键索引，当然可以是复合主键
- 主键索引的效率高（主键不可重复）
- 创建主键索引的列，它的值不能为null，且不能重复
- 主键索引的列基本上是int
8.2、创建唯一索引
和主键差不多。
- 第一种方式：在建表的时候，指定唯一键（设置unique [key]）。
create table user4(id int primary key,name varchar(11) unique);或者
create table user5(id int,name varchar(11),primary key(id),unique(name));
- 第二种方式：建表之后再添加唯一键。
create table user6(id int,name varchar(11)); alter table user6 add unique key(name);唯一索引的特点：
- 一个表中，可以有多个唯一索引
- 查询效率高
- 如果在某一列建立唯一索引，必须保证这列不能有重复数据
- 如果一个唯一索引上指定not null，接近于主键索引
8.3、创建普通索引索引
- 第一种方式：在建表的时候，在表的最后，指定某列为索引。
create table user7(id int,name varchar(11),index(name));
- 第二种方式：建表之后再添加索引。
create table user8(id int,name varchar(11)); alter table user8 add index(name);
- 第三种方式：创建一个索引，可以指定名字，不指定名字，则索引名默认为字段名。
create table user9(id int,name varchar(11)); create index name [索引名] on user9;普通索引的特点：
- 一个表中可以有多个普通索引，普通索引在实际开发中用的比较多
- 如果某列需要创建索引，但是该列有重复的值，那么我们就应该使用普通索引
8.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.01 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.00 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql>
查询全文是否有含有内容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) mysql>可以用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 -- NULL表示没有使用到索引 key_len: NULL ref: NULL rows: 6 filtered: 16.67 Extra: Using where 1 row in set, 1 warning (0.00 sec) mysql>那怎么使用全文索引呢？
使用下面语句：
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) mysql>
8.5、查询索引
使用user1表：
create table user1(id int primary key,name varchar(11) not null);
- 第一种方法：
show keys from 表名;例如：
mysql> show keys 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.00 sec)
- 第二种方法：
show index from 表名;例如：
mysql> 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 Comment: Index_comment: 1 row in set (0.00 sec)
- 第三种方法：这个方法显示的信息简略，只能看到主键（索引）。
desc 表名;
8.6、删除索引
- 删除主键索引
alter table 表名 drop primary key;
其他索引的删除：
第一种方法：
alter table 表名 drop index 索引名;第二种方法：
mysql> show keys 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 Comment: Index_comment: *************************** 2. row *************************** Table: user1 Non_unique: 1 Key_name: name -- 索引名 Seq_in_index: 1 Column_name: name Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: 2 rows in set (0.00 sec) mysql> alter table user1 drop index name; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show keys 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 Comment: Index_comment: 1 row in set (0.00 sec) mysql>索引名就是show keys from 表名中的Key_name 字段。
mysql> alter table user10 drop index idx_name;
- 第三种方法：drop index 索引名 on 表名
mysql> show keys 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 Comment: Index_comment: *************************** 2. row *************************** Table: user1 Non_unique: 1 Key_name: name Seq_in_index: 1 Column_name: name Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: 2 rows in set (0.00 sec) mysql> drop index name from user1; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from user1' at line 1 mysql> drop index name on user1; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show keys 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 Comment: Index_comment: 1 row in set (0.00 sec) mysql>
8.7、索引创建规则
- 比较频繁作为查询条件的字段应该创建索引
- 唯一性（有很多相同值，不好找）太差的字段不适合单独创建索引，即使频繁作为查询条件
- 更新非常频繁的字段不适合作创建索引（需要对B+树进行调整）
- 不会出现在where子句中的字段不该创建索引（不怎么用）
OKOK，MySQL索引就到这里，如果你对Linux和C++也感兴趣的话，可以看看我的主页哦。下面是我的github主页，里面记录了我的学习代码和leetcode的一些题的题解，有兴趣的可以看看。
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
