MySQL 索引

数据库的索引就像一本书的目录，能够加快数据库的查询速度。索引是一种数据库结构，用于加速数据库查询的执行。索引是根据表中的一个或多个列创建的，它存储了列值的排序顺序以及对应行的存储位置信息。索引的主要目的是提高查询性能，特别是在大型表中进行范围查询、排序、连接等操作时。按照功能划分，索引分为单列索引（包括普通索引NORMAL、唯一索引UNIQUE、主键索引PRIMARY）、组合索引（多个单列索引组合）、全文索引FULLTEXT、空间索引SPATIAL。

索引可以理解为一种数据结构。索引的实现方式（也称算法），主要有Hash索引和BTREE索引。在 MySQL 中，索引通常以BTREE的形式实现，BTREE是一种平衡搜索树，它具有快速的插入、删除和查找操作。

索引虽然可以提高查询性能，但也会带来一些额外的开销。在插入、更新和删除数据时，索引需要维护其排序顺序和存储位置信息，这可能会导致一定的性能下降。因此，在创建索引时需要权衡性能和维护成本，并根据具体的查询需求进行合理的设计。

创建索引

创建索引可以在新增表或者修改的时候创建,建议直接使用数据库工具创建，比如phpMyAdmin。在表结构选中其列后再选择索引类型即可创建。

以下是命令行创建， `table_name`是表名，`column` 是的列名，index_name是索引名

PRIMARY KEY（主键索引） 

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

UNIQUE(唯一索引) 

ALTER TABLE `table_name` ADD UNIQUE (`column`)

INDEX(普通索引)  

ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

FULLTEXT(全文索引) 

ALTER TABLE `table_name` ADD FULLTEXT ( `column` )

组合索引（包含多列的索引，与单行索引相对）

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

为了进一步榨取MySQL的效率，就要考虑建立组合索引。比如将vc_Name,vc_City,i_Age建到一个索引里：

ALTER TABLE myIndex ADD INDEX name_city_age (vc_Name(10),vc_City,i_Age);

--注意了，建表时，vc_Name长度为50，这里为什么用10呢？因为一般情况下名字的长度不会超过10，这样会加速索引查询速度，还会减少索引文件的大小，提高INSERT的更新速度。

    执行T-SQL时，MySQL无须扫描任何记录就到找到唯一的记录！！

    肯定有人要问了，如果分别在vc_Name,vc_City,i_Age上建立单列索引，让该表有3个单列索引，查询时和上述的组合索引效率一样吧？嘿嘿，大不一样，远远低于我们的组合索引~~虽然此时有了三个索引，但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。

    建立这样的组合索引，其实是相当于分别建立了

• vc_Name,vc_City,i_Age

• vc_Name,vc_City

• vc_Name

    这样的三个组合索引！为什么没有vc_City,i_Age等这样的组合索引呢？这是因为mysql组合索引"最左前缀"的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引，下面的几个T-SQL会用到：

    SELECT * FROM myIndex WHREE vc_Name="erquan" AND vc_City="郑州"
    SELECT * FROM myIndex WHREE vc_Name="erquan"

而下面几个则不会用到：

    SELECT * FROM myIndex WHREE i_Age=20 AND vc_City="郑州"
    SELECT * FROM myIndex WHREE vc_City="郑州"

空间索引

MySQL是一种流行的关系型数据库管理系统，提供了多种索引类型来优化查询性能。SPATIAL索引是一种特殊的索引类型，用于处理空间数据。本文将介绍MySQL的SPATIAL索引类型，并通过代码示例演示其用法和优势。空间数据是指地理或几何实体的数据，如点、线、面等。常见的应用包括地图、位置服务和地理信息系统（GIS）。MySQL支持处理空间数据，并提供了几何类型来存储和操作这些数据。

ALTER TABLE `table_name` ADD SPATIAL index_name (`column1`);

案例代码

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int(11) NOT NULL,
  `name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `sex` varchar(11) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '张三', '男');
INSERT INTO `student` VALUES ('2', '李四浩', '男');
INSERT INTO `student` VALUES ('3', '李四', '女');

原始数据

mysql> select * from student;
+----+-----------+------+
| id | name      | sex  |
+----+-----------+------+
|  1 | 张三      | 男   |
|  2 | 李四浩    | 男   |
|  3 | 李四      | 女   |
+----+-----------+------+

创建普通索引

mysql> ALTER TABLE `student` ADD INDEX( `name`);
Query OK, 3 rows affected (0.07 sec)

查询索引

可以使用SHOW INDEX语句来查看表上的索引信息。

例如，要查看student表上的索引信息，可以执行以下语句：

mysql> SHOW INDEX from student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          1 | name     |            1 | name        | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

或者用数据库工具phpmyadmin打开结构页面即可查看

这将显示student表上的所有索引信息，包括索引名称、索引所在的列、索引类型、索引是否唯一、索引的排序顺序等。

修改索引

使用ALTER TABLE语句将索引名称修改为'name2'。在 MySQL 中，不能直接修改索引的定义。如果需要修改索引的列、排序顺序或其他属性，需要先删除现有的索引，然后创建一个新的索引。

ALTER TABLE `test`.`student` DROP INDEX `name`, ADD INDEX `name2` (`name`) USING BTREE;

mysql> SHOW INDEX from student;

+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          1 | name2    |            1 | name        | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

也可以使用数据库工具phpmyadmin进行修改。

删除索引

如果不再需要索引，可以使用DROP INDEX语句将其删除。

例如，要删除student表上的name索引，可以执行以下语句：

mysql> ALTER TABLE student DROP INDEX name2;
Query OK, 3 rows affected (0.06 sec)

mysql> SHOW INDEX from student;
Empty set (0.00 sec)

索引的触发

不是建立索引后所有的查询都会触发的，WHERE 和 JOIN子句中的 < , <= , = , > , >= ,BETWEEN , IN , 非 % 或 _ 开头的LIKE 都会触发索引。

查看是否使用索引

要在MySQL中查看是否正在使用索引，可以通过EXPLAIN语句来获取有关查询的信息。

mysql> EXPLAIN SELECT * from student;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+

这将返回一个结果集，其中包含有关该查询如何执行的信息。如果"possible_keys"列显示为NULL或空字符串，则表示没有使用任何索引；如果"key"列显示为非空值（比如'index_name'），则表示已经使用了相应的索引。

MySQL各种索引区别

普通索引(INDEX)：最基本的索引，没有任何限制

唯一索引(UNIQUE)：与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。

主键索引(PRIMARY)：它 是一种特殊的唯一索引，不允许有空值。 创建主键时候自动创建索引

全文索引(FULLTEXT )：仅可用于 MyISAM 表， 用于在一篇文章中，检索文本信息的, 针对较大的数据，生成全文索引很耗时好空间。

组合索引：为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则。

空间索引：主要用于搜索空间数据。