4、索引

一、MySQL中的索引

1、索引

• 索引是帮助MySQL高效获取数据的排好序的数据结构，使用索引可以加快查询速度，索引的数据结构如下
  • 二叉树
  • 红黑树
  • HASH表
  • B-TREE
• key-value：key是索引，value是所在行的指针
• 在MySQL中，如果不使用索引，则查询时MySQL是通过遍历全表来查询的，如果建立了索引，则可以通过索引查找到数据（索引相当于指针）
• 使用B+Tree（BTree的变种，B+Tree非叶子节点不存储数据，只存储索引），索引是存在磁盘上的，当查找到需要的元素所在的叶节点后，再把该叶节点（MySQL中设置一个叶节点默认大小为16KB）加载到内存中，再在内存中
• 关于索引的本质讲解，请看视频：B站：MySQL索引底层原理

2、索引的类型

（1）普通索引和唯一索引

• 普通索引允许插入重复值和空值
• 唯一索引不允许重复值，但允许空值
• 主键索引是特殊的唯一索引，不允许重复值和空值

（2）单列索引和组合索引

• 单列索引是指在单个列上建立的索引
• 组合索引是在多个列上建立的索引，使用组合索引时，遵循最左前缀集合（见后面的例子）

（3）全文索引

略（待补充）

（4）空间索引

略（待补充）

3、MySQL中的索引

• 索引是在存储引擎中实现的
• MySQL中索引的类型有BTREE和HASH
• MyISAM和InnoDB存储引擎只支持BTREE索引；MEMORY和HEAP存储引擎支持BTREE和HASH

4、查看索引

mysql> SHOW INDEX FROM table_name;		# salaries为表名

mysql> SHOW CREATE TABLE salaries\G;
*************************** 1. row ***************************
       Table: salaries
Create Table: CREATE TABLE `salaries` (
  `emp_no` int(11) NOT NULL,
  `salary` int(11) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`,`from_date`),
  KEY `emp_no` (`emp_no`),								# 可以看到emp_no为索引（KEY、INDEX表示索引）
  CONSTRAINT `salaries_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

二、创建索引

1、定义表的时候创建

基本语法如下

CREATE TABLE <表名> [UNIQUE|FULLTEXT|SPATIAL] 
                    <INDEX|KEY> 
                    <索引名(字段名)[索引长度]>;

• UNIQUE表示创建唯一索引；FULLTEXT表示创建全文索引；SPATIAL表示创建空间索引
• INDEX/KEY表示创建索引
• 索引长度可不写
• 创建全文索引和空间索引时，需要把表的存储引擎设置为MyISAM

mysql> CREATE TABLE tb1 (
  		 id INT, 
  		 name VARCHAR(20), 
  		 age INT, 
  		 UNIQUE KEY idIdx(id));         # 创建索引idIdx，索引的字段为id
Query OK, 0 rows affected (0.04 sec)

mysql> SHOW CREATE TABLE tb1\G;
*************************** 1. row ***************************
       Table: tb1
Create Table: CREATE TABLE `tb1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  UNIQUE KEY `idIdx` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> CREATE TABLE tb2 (
  		 id INT, 
  		 name VARCHAR(20), 
  		 age INT, 
  		 KEY MultIdIdx(id, name, age));				# 创建组合索引MultIdIdx，索引的字段为id、name、age
Query OK, 0 rows affected (0.04 sec)

mysql> SHOW CREATE TABLE tb2\G;
*************************** 1. row ***************************
       Table: tb2
Create Table: CREATE TABLE `tb2` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  KEY `MultIdIdx` (`id`,`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

• 使用组合索引时，查询字段遵循最左前缀
• 最左前缀：利用最左边的列集来匹配行
• 上例中，只能使用索引查询这些字段：(id, name, age)，(id, name)，(id)

2、在已存在的表上创建索引

使用ALTER TABLE语句创建索引

ALTER TABLE <表名>
ADD
<UNIQUE|FULLTEXT|SPATAIL> <INDEX|KEY> <索引名(字段名)[字段长度]> <ASC|DESC>;

或者使用CREATE INDEX创建索引（实际上MySQL中，CREATE TABLE语句被映射到ALTER TABLE语句上）

CREATE INDEX <UNIQUE|FULLTEXT|SPATAIL> INDEX <索引名>
ON <表名> <字段名[索引长度]> <ASC|DESC>;

mysql> CREATE TABLE tb003 (a INT, b INT, c INT);
Query OK, 0 rows affected (0.08 sec)

mysql> ALTER TABLE tb003 
    -> ADD
    -> UNIQUE INDEX aIdx(a);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> CREATE INDEX bIdx
    -> ON tb003(b);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE tb003\G;
*************************** 1. row ***************************
       Table: tb003
Create Table: CREATE TABLE `tb003` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  UNIQUE KEY `aIdx` (`a`),
  KEY `bIdx` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

三、删除索引

• 添加AUTO_INCREMENT约束字段的唯一索引不能被删除
• 当索引所查询的列被删除时，该索引也会被删除
• 删除索引可以使用ALTER TABLE语句或者DROP INDEX语句

ALTER TABLE <表名>
DROP
<索引名>;

DROP INDEX <索引名>
ON <表名>;

转载：https://blog.csdn.net/qq_40650558/article/details/104434008