轻松上手,快乐学习!

MySQL 教程

MySQL 首页MySQL 介绍MySQL 安装MySQL 实例库下载MySQL 实例库导入MySQL SELECTMySQL DISTINCTMySQL ORDER BYMySQL WHEREMySQL ANDMySQL ORMySQL INMySQL BETWEENMySQL LIKEMySQL LIMITMySQL IS NULLMySQL 别名MySQL JOINSMySQL INNER JOINMySQL LEFT JOINMySQL RIGHT JOINMySQL CROSS JOINMySQL SELF JOINMySQL GROUP BYMySQL HAVINGMySQL ROLLUPMySQL 子查询MySQL 派生表MySQL EXISTSMySQL CTEMySQL 递归 CTEMySQL UNIONMySQL INTERSECTMySQL MINUSMySQL INSERTMySQL INSERT INTO SELECTMySQL INSERT IGNOREMySQL UPDATEMySQL UPDATE JOINMySQL DELETEMySQL ON DELETE CASCADEMySQL DELETE JOINMySQL REPLACEMySQL PREPAREMySQL 事务MySQL 表锁定MySQL USEMySQL 数据库管理MySQL CREATE DATABASEMySQL DROP DATABASEMySQL 存储引擎MySQL CREATE TABLEMySQL 序列MySQL ALTER TABLEMySQL RENAME TABLEMySQL DROP COLUMNMySQL ADD COLUMNMySQL DROP TABLEMySQL 临时表MySQL TRUNCATE TABLEMySQL 数据类型MySQL NOT NULLMySQL Primary KeyMySQL Foreign KeyMySQL UNIQUEMySQL CHECKMySQL 字符集MySQL 排序规则MySQL 导入 CSVMySQL 导出 CSVMySQL 自然排序MySQL 基础

MySQL 存储过程

MySQL 存储过程介绍MySQL 存储过程实例MySQL 存储过程变量MySQL 存储过程参数MySQL 存储过程返回多值MySQL IF 语句MySQL CASE 语句MySQL IF CASE 选择MySQL 存储过程循环MySQL 存储过程游标MySQL 存储过程列表MySQL 存储过程异常处理MySQL SIGNAL 和 RESIGNALMySQL 存储函数

MySQL 视图

MySQL 视图教程SQL 视图介绍MySQL 视图介绍MySQL 创建视图MySQL 可更新视图WITH CHECK OPTIONLOCAL&CASCADEDMySQL 视图管理

MySQL 触发器

MySQL 触发器SQL 触发器MySQL 触发器介绍MySQL 触发器的创建MySQL 创建多个触发器MySQL 触发器管理MySQL 计划事件MySQL事件修改

MySQL 索引

MySQL 索引MySQL 索引创建MySQL 索引删除MySQL 显示索引MySQL 唯一索引MySQL 前缀索引MySQL 隐形索引MySQL 降序索引MySQL 复合索引MySQL 聚集索引MySQL 索引基数MySQL USE INDEXMySQL 强制索引

MySQL 管理

MySQL 管理MySQL访问控制系统MySQL 用户创建MySQL 用户密码MySQL 权限授予MySQL 权限撤销MySQL 角色MySQL 删除用户MySQL 表维护mysqldump 备份工具MySQL 数据库列表MySQL 表列表MySQL 表字段列表MySQL 用户列表MySQL 进程列表MySQL 列生成比较MySQL中同一表中的连续行

MySQL 全文搜索

MySQL 全文搜索MySQL 全文搜索介绍FULLTEXT索引MySQL 自然语言全文搜索MySQL 布尔全文搜索MySQL查询扩展MySQL ngram

MySQL 高级

MySQL 函数MySQL 窗口函数

MySQL 技巧

MySQL 查找重复数据MySQL 删除重复数据MySQL UUIDMySQL 表的复制MySQL 复制库MySQL 变量MySQL SELECT INTO 变量MySQL 表的存储引擎MySQL 使用正则查询MySQL 添加序号MySQL 随机查询MySQL 查询第 N 高记录MySQL 重置自增值MySQL VS MariaDBMySQL 间隔值MySQL 获取当天日期MySQL NULL 映射MySQL 注释理解MySQL EXPLAINMySQL 技巧介绍MySQL COUNTMySQL 数据分层MySQL 两表比较Mysql Like 优化

MySQL 窗口函数

MySQL CUME_DIST() 函数MySQL DENSE_RANK() 函数MySQL FIRST_VALUE 函数MySQL LAG() 函数MySQL LAST_VALUE() 函数MySQL LEAD 函数MySQL NTH_VALUE 函数MySQL NTILE 函数MySQL PERCENT_RANK 函数MySQL RANK 函数MySQL ROW_NUMBER 函数

MySQL 应用

MySQL应用程序编程接口PHP MySQL教程PHP 数据库连接

MySQL 优化

mysqld 内存持续变高


MySQL 索引创建


简介:在本教程中,您将了解索引以及如何使用MySQL CREATE INDEX语句向表中添加索引。


电话簿比喻

假设您有一本电话簿,其中包含城市中所有人的姓名和电话号码。假设你想找到Bob Cat的电话号码。知道这些名称是按字母顺序排列的,首先要查找姓氏为Cat的页面,然后查找Bob和他的电话号码。

现在,如果电话簿中的姓名没有按字母顺序排序,则需要浏览所有页面,阅读其中的每个名称,直到找到Bob Cat。这称为顺序搜索。您将查看所有条目,直到找到具有您要查找的电话号码的人员为止。

将电话簿与数据库表相关联,如果您有电话簿表并且必须找到Bob Cat的电话号码,则可以执行以下查询:

SELECT
    phone_number
FROM 
    phone_book
WHERE 
    first_name = 'Bob' AND 
    last_name = 'Cat';

这很容易。虽然查询速度很快,但数据库必须扫描表的所有行,直到找到行为止。如果表有数百万行而没有索引,则数据检索将花费大量时间来返回结果。


索引简介

索引是一种数据结构,例如B-Tree,它以增加额外写入和存储为代价来提高表上数据检索的速度。

查询优化器可以使用索引来快速定位数据,而无需扫描表中给定查询的每一行。

当您使用主键唯一键创建表时,MySQL会自动创建一个名为PRIMARY的特殊索引。索引称为聚簇索引

PRIMARY索引是特殊的,因为索引本身与数据一起存储在同一个表中。 聚簇索引强制执行表中的行顺序。

PRIMARY索引之外的其他索引称为二级索引或非聚簇索引。


MySQL CREATE INDEX语句

通常,您在创建时为表创建索引。例如,以下语句创建一个新表,其索引由两列c2和c3组成。

CREATE TABLE t(
   c1 INT PRIMARY KEY,
   c2 INT NOT NULL,
   c3 INT NOT NULL,
   c4 VARCHAR(10),
   INDEX (c2,c3) 
);

要为列或一组列添加索引,请使用以下CREATE INDEX语句:

CREATE INDEX index_name ON table_name (column_list)

要为列或列列表创建索引,请指定索引名称,索引所属的表以及列列表。

例如,要为列c4添加新索引,请使用以下语句:

CREATE INDEX idx_c4 ON t(c4);

默认情况下,如果未指定索引类型,MySQL将创建B-Tree索引。以下显示了基于表的存储引擎的允许索引类型:

存储引擎 允许的索引类型
InnoDB BTREE
MyISAM BTREE
MEMORY / HEAP HASH,BTREE

请注意,CREATE INDEX上面的CREATE INDEX语句是MySQL引入的语句的简化版本。我们将在后续教程中介绍更多选项。


MySQL CREATE INDEX示例

以下声明查找职位名称为的员工Sales Rep

SELECT 
    employeeNumber, 
    lastName, 
    firstName
FROM
    employees
WHERE
    jobTitle = 'Sales Rep';

这是输出:

+----------------+-----------+-----------+
| employeeNumber | lastName  | firstName |
+----------------+-----------+-----------+
|           1165 | Jennings  | Leslie    |
|           1166 | Thompson  | Leslie    |
|           1188 | Firrelli  | Julie     |
|           1216 | Patterson | Steve     |
|           1286 | Tseng     | Foon Yue  |
|           1323 | Vanauf    | George    |
|           1337 | Bondur    | Loui      |
|           1370 | Hernandez | Gerard    |
|           1401 | Castillo  | Pamela    |
|           1501 | Bott      | Larry     |
|           1504 | Jones     | Barry     |
|           1611 | Fixter    | Andy      |
|           1612 | Marsh     | Peter     |
|           1619 | King      | Tom       |
|           1621 | Nishi     | Mami      |
|           1625 | Kato      | Yoshimi   |
|           1702 | Gerard    | Martin    |
+----------------+-----------+-----------+
17 rows in set (0.01 sec)

我们有17行,表明17名员工的职位是销售代表。

要查看MySQL如何在内部执行此查询,请在SELECT语句的开头添加EXPLAIN子句,如下所示:

+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   23 |    10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

正如您所看到的,MySQL必须扫描包含23行的整个表,以查找具有Sales Rep职位的员工。

现在,让我们jobTitle使用以下CREATE INDEX语句为列创建索引   :

CREATE INDEX jobTitle ON employees(jobTitle);

并再次执行上述语句:

EXPLAIN SELECT 
    employeeNumber, 
    lastName, 
    firstName
FROM
    employees
WHERE
    jobTitle = 'Sales Rep';

输出是:

+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | jobTitle      | NULL | NULL    | NULL |   23 |    73.91 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

正如您所看到的,MySQL只需要从jobTitle键列中找到17行,   而不是扫描整个表。

要显示表的索引,请使用SHOW INDEXES语句,例如:

SHOW INDEXES FROM employees;

这是输出:

+-----------+------------+------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name   | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| employees |          0 | PRIMARY    |            1 | employeeNumber | A         |          23 |     NULL | NULL   |      | BTREE      |         |               |
| employees |          1 | reportsTo  |            1 | reportsTo      | A         |           7 |     NULL | NULL   | YES  | BTREE      |         |               |
| employees |          1 | officeCode |            1 | officeCode     | A         |           7 |     NULL | NULL   |      | BTREE      |         |               |
| employees |          1 | jobTitle   |            1 | jobTitle       | A         |           9 |     NULL | NULL   |      | BTREE      |         |               |
+-----------+------------+------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

在本教程中,您已经了解了MySQL索引以及如何为表中的列添加索引。