索引介绍
索引是对数据库表中一列或者多列的值进行排序的一种结构，使用索引可提高数据库中特定数据的查询速度。
索引是一个单独的、存储在磁盘上的数据库结构，它们包含着对数据表里所有记录的引用指针。使用索引用于快速找出在某个或多个列中有一特定值得行，所有MySQL列类型都可以被索引，对相关列使用索引是提高查询操作速度的最佳途径。
索引是在存储引擎中实现的，因此，每种存储引擎的索引都不一定完全相同，并且每种存储引擎也不一定支持索引类型。
根据存储引擎定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少16个索引，总索引长度至少为256字节。 MyISAM ，InnoDB支持btree索引Memory 支持 btree和hash索引
索引的优势
- 加快查询速度
- 创建唯一索引来保证数据表中数据的唯一性
- 实现数据的完整性，加速表和表之间的连接
- 减少分组和排序的时间
增加索引
增加索引也有许多不利，主要表现在如下几个方面：
- 创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
- 索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
- 当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。
索引的分类
- 唯一索引和普通索引 普通索引是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值。唯一索引，索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。主键索引是一种特殊的唯一索引，不允许有空值。
- 单列索引和组合索引 单列索引即一个索引只包含单个列，一个表可以有多个单列索引。 组合索引指在表的多个字段组合上创建的索引。只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀集合。
- 全文索引 fulltext 全文索引类型为FULLTEXT，在定义索引的列上支持值得全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建。MySQL中只有MyISAM存储引擎支持全文索引。
- 空间索引 空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4中，分别是：geometry、point、linstring和polygon 。MySQL使用SPATIAL关键字进行扩展，使得能够用于创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MyISAM的表中创建。
创建索引的规则
(1)创建索引并非是越多越好，一个表中如果有大量的索引，不仅占用磁盘空间，而且会影响insert、delete、update等语句的性能。因为当表中的数据更改时，索引也会进行调整和更新。
(2)数据量小得表最好不要创建索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要长。
(3)避免对经常更新的数据创建索引。而对经常用于查询的字段应该创建索引。
(4)在条件表达式中经常用到的不同值较多的列创建索引
(5)当唯一性是某种数据本身的特征时，我们创建唯一性索引
(6)在频繁进行排序或分组的列上建立索引，如果排序的列有多个，可以创建组合索引
创建索引
Create index 创建索引
alter table 添加索引
Create table 表名[字段名 数据类型] [unique唯一索引|fulltext全文索引|spatial空间索引] [index|key] [索引名] (col_name [length]) [asc |desc]
普通索引
普通索引是最基础的索引类型，没有唯一性的限制。作用是只加快对数据的访问速度。
先创建book表，创建索引为year_publication
1. mysql> CREATE TABLE book 2. -> ( 3. -> bookid INT NOT NULL, 4. -> bookname VARCHAR(255) NOT NULL, 5. -> authors VARCHAR(255) NOT NULL, 6. -> info VARCHAR(255) NULL, 7. -> comment VARCHAR(255) NULL, 8. -> year_publication YEAR NOT NULL, 9. -> INDEX(year_publication) 10. -> ); 11. Query OK, 0 rows affected (0.03 sec)
查看创建的索引
注：\G结尾表示纵向查看
1. mysql> show create table book\G 2. *************************** 1. row *************************** 3. Table: book 4. Create Table: CREATE TABLE `book` ( 5. `bookid` int(11) NOT NULL, 6. `bookname` varchar(255) COLLATE utf8_bin NOT NULL, 7. `authors` varchar(255) COLLATE utf8_bin NOT NULL, 8. `info` varchar(255) COLLATE utf8_bin DEFAULT NULL, 9. `comment` varchar(255) COLLATE utf8_bin DEFAULT NULL, 10. `year_publication` year(4) NOT NULL, 11. KEY `year_publication` (`year_publication`) 12. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin 13. 1 row in set (0.00 sec)
explain
使用该命令判断索引是否正在被使用
1. mysql> explain select * from book where year_publication=1999\G 2. *************************** 1. row *************************** 3. id: 1 4. select_type: SIMPLE 5. table: book 6. partitions: NULL 7. type: ref 8. possible_keys: year_publication 9. key: year_publication 10. key_len: 1 11. ref: const 12. rows: 1 13. filtered: 100.00 14. Extra: Using index condition 15. 1 row in set, 1 warning (0.00 sec)
TYPE的取值范围 System const ref eq_ref index all range
唯一索引
创建唯一索引 唯一索引主要原因是减少查询索引列操作的执行时间。尤其是对比比较庞大的数据表。与普通索引类似，不同点在于：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
创建唯一索引
1. mysql> CREATE TABLE t1 2. -> ( 3. -> id INT NOT NULL, 4. -> name CHAR(30) NOT NULL, 5. -> UNIQUE INDEX UniqIdx(id) 6. -> ); 7. Query OK, 0 rows affected (0.00 sec)
查看唯一索引
1. mysql> show create table t1\G 2. *************************** 1. row *************************** 3. Table: t1 4. Create Table: CREATE TABLE `t1` ( 5. `id` int(11) NOT NULL, 6. `name` char(30) COLLATE utf8_bin NOT NULL, 7. UNIQUE KEY `UniqIdx` (`id`) 8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin 9. 1 row in set (0.00 sec)
单列索引
单列索引：是在数据表中的某一字段上创建的索引，一个表中可以创建多个单列索引。
创建单列索引
1. mysql> CREATE TABLE t2 2. -> ( 3. -> id INT NOT NULL, 4. -> name CHAR(50) NULL, 5. -> INDEX SingleIdx(name) 6. -> ); 7. Query OK, 0 rows affected (0.01 sec)
查看单列索引
1. mysql> show create table t2\G 2. *************************** 1. row *************************** 3. Table: t2 4. Create Table: CREATE TABLE `t2` ( 5. `id` int(11) NOT NULL, 6. `name` char(50) COLLATE utf8_bin DEFAULT NULL, 7. KEY `SingleIdx` (`name`) 8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin 9. 1 row in set (0.00 sec)
组合索引
组合索引：是在多个字段上创建一个索引。遵循最左前缀原则。最左前缀 索引最左边的列来匹配行
创建组合索引
1. mysql> CREATE TABLE t3 2. -> ( 3. -> id INT NOT NULL, 4. -> name CHAR(30) NOT NULL, 5. -> age INT NOT NULL, 6. -> info VARCHAR(255), 7. -> INDEX MultiIdx(id,name,age) 8. -> ); 9. Query OK, 0 rows affected (0.02 sec)
查看组合索引
1. mysql> show create table t3\G 2. *************************** 1. row *************************** 3. Table: t3 4. Create Table: CREATE TABLE `t3` ( 5. `id` int(11) NOT NULL, 6. `name` char(30) COLLATE utf8_bin NOT NULL, 7. `age` int(11) NOT NULL, 8. `info` varchar(255) COLLATE utf8_bin DEFAULT NULL, 9. KEY `MultiIdx` (`id`,`name`,`age`) 10. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin 11. 1 row in set (0.00 sec)
组合索引可以起几个索引的作用，但是使用时并不是随意查询哪个字段都是可以使用索引。而是遵循最左前缀：利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。
全文索引
全文索引：FULLTEXT，可以用于全文搜索，5.7版本以前只有MyISAM存储引擎支持，而5.7版本以后innodb存储引擎也支持，并且只为CHAR\VARCHAR和TEXT 列。索引总是对整个列进行，不支持局部索引，适合大型数据的表
创建全文索引
1. mysql> CREATE TABLE t4 2. -> ( 3. -> id INT NOT NULL, 4. -> name CHAR(30) NOT NULL, 5. -> age INT NOT NULL, 6. -> info VARCHAR(255), 7. -> FULLTEXT INDEX FullIdx(info(100)) 8. -> )ENGINE=MyISAM; 9. Query OK, 0 rows affected (0.00 sec)
查看全文索引
1. mysql> show create table t4\G 2. *************************** 1. row *************************** 3. Table: t4 4. Create Table: CREATE TABLE `t4` ( 5. `id` int(11) NOT NULL, 6. `name` char(30) COLLATE utf8_bin NOT NULL, 7. `age` int(11) NOT NULL, 8. `info` varchar(255) COLLATE utf8_bin DEFAULT NULL, 9. FULLTEXT KEY `FullIdx` (`info`) 10. ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin 11. 1 row in set (0.00 sec)
例：创建xx34表，info列为全文索引并搜索。
1. MATCH (col1,col2,...) AGAINST (expr [search_modifier]) 2. create table xx34(id int,name varchar(20),info varchar(255),fulltext index chidx(info) with parser ngram); 3. select * from xx34 where match(info) against("主义");
空间索引
空间索引：必须在MyISAM类型的表中创建，且空间类型的字段必须为非空。
创建空间索引
1. mysql> CREATE TABLE t5 2. -> ( 3. -> g GEOMETRY NOT NULL, 4. -> SPATIAL INDEX spaIdx(g) 5. -> )ENGINE=MyISAM; 6. Query OK, 0 rows affected (0.01 sec)
查看空间索引
1. mysql> show create table t5\G 2. *************************** 1. row *************************** 3. Table: t5 4. Create Table: CREATE TABLE `t5` ( 5. `g` geometry NOT NULL, 6. SPATIAL KEY `spaIdx` (`g`) 7. ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin 8. 1 row in set (0.00 sec)
在已经存在的表上创建索引
Alter table 表名 add [unique唯一索引|fulltext全文索引|spatial空间索引] [index|key] [默认索引名] (定义索引名[length]) [asc|desc]
添加索引
在book表添加索引
1. mysql> alter table book add index BKNameIdx(bookname(30)); 2. Query OK, 0 rows affected (0.02 sec) 3. Records: 0 Duplicates: 0 Warnings: 0
查看索引
1. mysql> show index from book\G 2. *************************** 1. row *************************** 3. Table: book 4. Non_unique: 1 5. Key_name: year_publication 6. Seq_in_index: 1 7. Column_name: year_publication 8. Collation: A 9. Cardinality: 0 10. Sub_part: NULL 11. Packed: NULL 12. Null: 13. Index_type: BTREE 14. Comment: 15. Index_comment: 16. *************************** 2. row *************************** 17. Table: book 18. Non_unique: 1 19. Key_name: BKNameIdx 20. Seq_in_index: 1 21. Column_name: bookname 22. Collation: A 23. Cardinality: 0 24. Sub_part: 30 25. Packed: NULL 26. Null: 27. Index_type: BTREE 28. Comment: 29. Index_comment: 30. 2 rows in set (0.00 sec)
