MySQL索引
---恢复内容开始---
一、索引
1.1 索引介绍
索引用于快速找出在某个列中有一特定值的行，不使用索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花费的时间就越多。如果表中查询的列有一个索引，MySQL能够快速到达一个位置去搜索数据文件，而不必查看所有数据，那么将会节省很大一部分时间。其中MySQL中的索引的存储类型有两种：BTREE、HASH。 也就是用树或者Hash值来存储该字段，要知道其中详细是如何查找的，就需要会算法的知识了。我们现在只需要知道索引的作用，功能是什么就行。
1.2 索引的优缺点和使用原则
1.2.1 优点
1、所有的MySql列类型(字段类型)都可以被索引，也就是可以给任意字段设置索引。
2、大大加快数据的查询速度。
1.2.2 缺点
1、创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
2、索引也需要占空间，我们知道数据表中的数据也会有最大上线设置的，如果我们有大量的索引，索引文件可能会比数据文件更快达到上线值。
3、当对表中的数据进行增加、删除、修改时，索引也需要动态的维护，降低了数据的维护速度。
1.2.3 使用原则
通过上面说的优点和缺点，我们应该可以知道，并不是每个字段设置索引就好，也不是索引越多越好，而是需要自己合理的使用。
1、对经常更新的表就避免对其进行过多的索引，对经常用于查询的字段应该创建索引，
2、数据量小的表最好不要使用索引，因为由于数据较少，可能查询全部数据花费的时间比遍历索引的时间还要短，索引就可能不会产生优化效果。
3、在一同值少的列上(字段上)不要建立索引，比如在学生表的"性别"字段上只有男、女两个不同值。相反的，在一个字段上不同值较多可是建立索引。
4、索引一定要创建在where后的条件列上，而不是select的选择数据的列上。
二、索引分类
索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引。
MyISAM和InnoDB存储引擎：只支持BTREE索引， 也就是说默认使用BTREE，不能够更换。MEMORY/HEAP存储引擎：支持HASH和BTREE索引。
索引我们分为四类来讲，单列索引(普通索引，唯一索引，主键索引)、组合索引、全文索引、空间索引。
2.1 单列索引
一个索引只包含单个列，但一个表中可以有多个单列索引。
1、普通索引
MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。
2、唯一索引
索引列中的值必须是唯一的，但是允许为空值，
3、主键索引
主键索引是一种特殊的唯一索引，不允许有空值。创建表时指定设置主键，默认就建立了该字段的索引。
2.2 组合索引
在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。例如index(a,b,c),仅a,ab,abc三个查询条件可以索引，b,bc,ac等无法使用索引，这就是联合索引的前缀生效特性。
2.3 全文索引
全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引。全文索引就是在一堆文字中，通过其中的某个关键字等，就可能找到该字段所属的记录行。关键词：match（filed）aginst(key)
2.4 空间索引
空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种，GEOMETRY、POINT、LINESTRING、POLYGON。在创建空间索引时，使用SPATIAL关键字。要求引擎为MyISAM，创建空间索引的列，必须将其声明为NOT NULL。
三、索引操作
3.1 创建索引
3.1.1 建表时建索引
格式：CREATE TABLE 表名[字段名 数据类型] [UNIQUE|FULLTEXT|SPATIAL|...] [INDEX|KEY] [索引名字] (字段名[length]) [ASC|DESC]
1、创建普通索引
mysql> create table test1(
-> id int not null,
-> name char(20),
-> age int not null,
-> desc varchar(50),
-> index(name)
-> );
mysql> show create table test1;
+-------+--------------------------------+
|Table|Create Table
+-------+--------------------------------+
| test1 | CREATE TABLE `test1` (
`id` int(11) NOT NULL,
`name` char(20) DEFAULT NULL,
`age` int(11) NOT NULL,
`comment` varchar(50) DEFAULT NULL,
KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------+
1 row in set (0.00 sec)
虽然表中没数据，但是有EXPLAIN关键字，用来查看索引是否正在被使用，并且输出其使用的索引的信息。
mysql> explain select * from test1 where name='qiujiajia'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test1
type: ref
possible_keys: name
key: name
key_len: 61
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)
ERROR:
No query specified
id: SELECT识别符。这是SELECT的查询序列号,也就是一条语句中，该select是第几次出现。在此语句中，select就只有一个，所以值为1
select_type: 所使用的SELECT查询类型，SIMPLE表示为简单的SELECT，不使用UNION或子查询，仅为简单的SELECT。其他取值，PRIMARY：最外面的SELECT,在拥有子查询时，就会出现两个以上的SELECT。UNION：union(两张表连接)中的第二个或后面的select语句 SUBQUERY：在子查询中，第二SELECT。
table：数据表的名字。他们按被读取的先后顺序排列，这里因为只查询一张表，所以只显示test1。
type：指定本数据表和其他数据表之间的关联关系，该表中所有符合检索值的记录都会被取出来和从上一个表中取出来的记录作联合。ref用于连接程序使用键的最左前缀或者是该键不是 primary key 或 unique索引（可能的取值有 system、const、eq_ref、index和All
possible_keys：MySQL在搜索数据记录时可以选用的各个索引，该表中就只有一个索引name
key：实际选用的索引
key_len：显示了mysql使用索引的长度，当 key 字段的值为 null时，索引的长度就是 null。
ref: 给出关联关系中另一个数据表中数据列的名字。常量（const），这里使用的是qiujiajia，就是常量。
rows：MySQL在执行这个查询时预计会从这个数据表里读出的数据行的个数。
extra：提供了与关联操作有关的信息，没有则什么都不写。
我们主要的是看possible_keys和key 这两个属性，上面显示了key为name，说明使用了索引。
3.1.2 创建唯一索引
mysql> create table test2(
-> id int not null,
-> name char(20),
-> age int,
-> sex char(5),
-> unique index uniq_inx(name));
Query OK, 0 rows affected (0.01 sec)
mysql> show create table test2\G;
*************************** 1. row ***************************
Table: test2
Create Table: CREATE TABLE `test2` (
`id` int(11) NOT NULL,
`name` char(20) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`sex` char(5) DEFAULT NULL,
UNIQUE KEY `uniq_inx` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified
要查看其中查询时使用的索引，必须先往表中插入数据，然后在查询数据，不然查找一个没有的name值，是不会使用索引的。
mysql> insert into test2(id,name,age,sex)
-> values(1,'chenhong',18,'woman');
Query OK, 1 row affected (0.00 sec)
mysql> explain select * from test2 where name='chenhong'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test2
type: const
possible_keys: uniq_inx
key: uniq_inx
key_len: 61
ref: const
rows: 1
Extra:
1 row in set (0.00 sec)
ERROR:
No query specified
可以看到，通过name查询时，会使用唯一索引。查询一个没有的name值，则不会使用索引，可能所有的name应该会存储到一个const tables中，若其中并没有该name值，那么就没有查找的必要了。
3.1.3 创建主键索引
mysql> create table cs1(
-> id int not null,
-> name char(20),
-> primary key(id)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> explain select * from cs1 where id=1\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Impossible WHERE noticed after reading const tables
1 row in set (0.00 sec)
ERROR:
No query specified
插入数据后查询：
mysql> insert into table cs1(id,name)
-> values(1,'qiujiajia')
-> );
mysql> explain select * from cs1 where id=1\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: cs1
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
Extra:
1 row in set (0.00 sec)
ERROR:
No query specified
我们声明的主键，就是一个主键索引。
3.2 分类索引
3.2.1 创建单列索引
上面创建的索引都属于单列索引。
3.2.2 创建组合索引
组合索引就是在多个字段上创建一个索引，生成一个表test,建立组合索引id、name、age三个索引。
mysql> create table test(
-> id int not null,
-> name char(20) not null,
-> age int not null,
-> index multindex(id,name,age)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test(id,name,age)
-> values(1,'qiujia',24),(2,'chenming',27);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
组合索引就是遵从了最左前缀，利用索引中最左边的列集来匹配行，这样的列集称为最左前缀。这里由id、name和age3个字段构成的索引，索引行中就按id/name/age的顺序存放，索引可以索引下面字段组合(id，name，age)、(id，name)或者(id)。如果要查询的字段不构成索引最左面的前缀，那么就不会是用索引，比如，age或者（name，age）组合就不会使用索引查询
mysql> explain select * from test where id=1 and name='qiujia'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test
type: ref
possible_keys: multindex
key: multindex
key_len: 64
ref: const,const
rows: 1
Extra: Using where; Using index
1 row in set (0.00 sec)
ERROR:
No query specified
这样可以查询索引。如果根据字段name和age，则查询不到索引。如下：
mysql> explain select * from test where name='qiujia' and age=24\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test
type: index
possible_keys: NULL
key: multindex
key_len: 68
ref: NULL
rows: 2
Extra: Using where; Using index
1 row in set (0.01 sec)
ERROR:
No query specified
3.2.3 创建全文索引
全文索引可以用于全文搜索，但只有MyISAM存储引擎支持FULLTEXT索引，并且只为CHAR、VARCHAR和TEXT列服务。索引总是对整个列进行，不支持前缀索引。
mysql> create table test(
-> id int not null,
-> name char(20) not null,
-> age int not null,
-> info varchar(255),
-> fulltext fullindex(info)
-> )engine=myisam;
mysql> show create table test\G;
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`id` int(11) NOT NULL,
`name` char(20) NOT NULL,
`age` int(11) NOT NULL,
`info` varchar(255) DEFAULT NULL,
FULLTEXT KEY `fullindex` (`info`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.01 sec)
ERROR:
No query specified
使用一下什么叫做全文搜索,就是在很多文字中，通过关键字就能够找到该记录。
mysql> insert into test(id,name,age,info)
-> values(1,'qiujia',28,'i am a oldboy, my name is qiujia');
Query OK, 1 row affected (0.00 sec)mysql> insert into test(id,name,age,info)
-> values(1,'qiujia',28,'i am a oldboy, my name is qiujia');
Query OK, 1 row affected (0.00 sec)
检查：
mysql> select * from test where match(info) against('oldboy');
+----+--------+-----+----------------------------------+
| id | name | age | info |
+----+--------+-----+----------------------------------+
| 1 | qiujia | 28 | i am a oldboy, my name is qiujia |
+----+--------+-----+----------------------------------+
1 row in set (0.00 sec)
mysql> explain select * from test where match(info) against('oldboy')\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test
type: fulltext
possible_keys: fullindex
key: fullindex
key_len: 0
ref:
rows: 1
Extra: Using where
1 row in set (0.00 sec)
ERROR:
No query specified
3.2.3 创建空间索引
空间索引也必须使用MyISAM引擎， 并且空间类型的字段必须为非空。 这个空间索引具体能干嘛我也不知道。
mysql> create table test1(
-> id geometry not null,
-> name char(20),
-> spatial index spaindex(id)
-> engine=myisam);
Query OK, 0 rows affected (0.00 sec)
注意点：
1）geometry；定义要创建空间索引的列。
2）spatial index：创建空间索引。
3）对应的引擎为myisam。
mysql> show create table test1\G;
*************************** 1. row ***************************
Table: test1
Create Table: CREATE TABLE `test1` (
`id` geometry NOT NULL,
`name` char(20) DEFAULT NULL,
SPATIAL KEY `spaindex` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified
3.3 已存表上创建索引
3.3.1 查看索引
命令：SHOW INDEX FROM 表名\G
mysql> show index from test\G;
*************************** 1. row ***************************
Table: test
Non_unique: 1
Key_name: fullindex
Seq_in_index: 1
Column_name: info
Collation: NULL
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: FULLTEXT
Comment:
Index_comment:
1 row in set (0.00 sec)
ERROR:
No query specified
Table: 创建索引的表
Non_unique：表示索引非唯一，1代表非唯一索引，0代表唯一索引。意思就是该索引是不是唯一索引。
Key_name：索引名称
Seq_in_index：表示该字段在索引中的位置，单列索引的话该值为1，组合索引为每个字段在索引定义中的顺序。
Column_name：表示定义索引的列字段
Sub_part：表示索引的长度
Null：表示该字段是否能为空值
Index_type：表示索引类型
3.3.2 为表添加索引
1、格式一：ALTER TABLE 表名 ADD[UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [索引名] (索引字段名)[ASC|DESC]
将test表name列加索引，索引名称为aa_index
mysql> alter table test add index aa_index(name(3));
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> show index from test\G;
*************************** 1. row ***************************
Table: test
Non_unique: 1
Key_name: aa_index
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: NULL
Sub_part: 3
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
2、格式二：CREATE [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] 索引名称 ON 表名(创建索引的字段名[length])[ASC|DESC]
mysql> create index aa_index on info(name(5));
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from info\G;
*************************** 1. row ***************************
Table: info
Non_unique: 1
Key_name: aa_index
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 5
Sub_part: 5
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.00 sec)
和第一种方法类似，上面在info表中对name字段长度为5创建索引，且索引名称为aa_index。建议学习熟练使用格式一。
3.3.3 删除索引
前面讲了对一张表中索引的添加，查询的方法。
添加的两种方式
现在来说说如何给表删除索引的两种操作。
1、格式一：ALTER TABLE 表名 DROP INDEX 索引名。
mysql> alter table info drop index aa_index;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from info;
Empty set (0.00 sec)
2、格式二：DROP INDEX 索引名 ON 表名；
mysql> show index from test1\G;
*************************** 1. row ***************************
Table: test1
Non_unique: 1
Key_name: spaindex
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: NULL
Sub_part: 32
Packed: NULL
Null:
Index_type: SPATIAL
Comment:
Index_comment:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> drop index spaindex on test1;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from test1\G;
Empty set (0.00 sec)
ERROR:
No query specified
四、总结
1、索引的使用原则：
经常使用的表字段建立索引以便于查询，索引要创建在where后的条件列上，而不是select的选择数据的列上。
2、索引分类：单列索引、组合索引、全文索引、空间索引。
3、创建索引：可以在创建表时就创建索引，或在已存在的表中增家列索引。
4、删除索引：删除索引关键词drop index。
文章整理自：https://www.cnblogs.com/whgk/p/6179612.html
---恢复内容结束---
