MySQL教程 MySQL教程MySQL 连接MySQL 查看数据库MySQL 创建数据库MySQL 修改数据库MySQL 删除数据库MySQL 选择数据库MySQL 数据类型MySQL存储引擎MySQL 数据表的增删改查MySQL 表数据增删改查操作MySQL 条件子句MySQL 清空表记录MySQL 模糊查询MySQL 处理重复数据MySQL 别名MySQL 限制查询结果数量Mysql 查询结果排序MySQL 范围查询MySQL 空值查询MySQL 查询结果合拼MySQL 分组MySQL 筛选分组MySQL 表连接MySQL NULL值处理MySQL 子查询MySQL 正则表达式MySQL 视图MySQL 索引MySQL 事务MySQL 数据库备份(导出)/还原(导入)MySQL安装 MySQL Windows版下载教程MySQL windows系统安装教程MySQL windows系统简单的安装教程MySQL Linux系统安装教程MySQL Linux系统下载教程MySQL Linux系统简单的安装教程配置MySQL环境变量

MySQL 索引

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

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

130079f3a7964fe99c9ea0872105b395.png

be35547d37b54bcabe97229087792db3.png

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


创建索引

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

360截图20240320104743187.jpg

以下是命令行创建, `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_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打开结构页面即可查看

360截图20240320112506265.jpg

这将显示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效率可建立组合索引,遵循”最左前缀“原则。

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