mysql 表索引的一些要点

下面是讲解mysql表索引的一些要点的完整攻略。

什么是表索引？

表索引是一种数据结构，它能够加速数据库查找特定数据行的速度。表索引是一个列值的有序列表，它包含了指向数据行的指针（或叫做引用）。这些数据行存储在表的数据页中。当你在表上运行查询时，MySQL可以利用这些指针来快速找到匹配的数据行，从而提高查询性能。

表索引的类型

主键索引

主键索引是一种特殊的唯一索引。每个表只能有一个主键索引。主键索引的值不能为Null。它可以是单列或组合列，通常对表中每个行都有唯一的标识。

你可以在创建表时使用PRIMARY KEY关键字来创建主键索引，也可以在创建表后使用ALTER TABLE语句来添加主键索引。

比如：

CREATE TABLE employee (
    id INT(11) NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age INT(11) NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB;

唯一索引

唯一索引与主键索引类似，只不过它可以有多个。每个唯一索引保证列的值是唯一的，但是可以有Null值。当你为一个列或列组创建唯一索引时，MySQL会使用B-Tree算法快速查找值。

你可以在创建表时使用UNIQUE关键字来创建唯一索引，也可以在创建表后使用ALTER TABLE语句来添加唯一索引。

比如：

CREATE TABLE employee (
    id INT(11) NOT NULL AUTO_INCREMENT,
    email VARCHAR(50) NOT NULL,
    UNIQUE (email)
) ENGINE=InnoDB;

普通索引

普通索引是最基本的索引类型。它可以是单列或多列的。与唯一索引不同，普通索引允许重复的值和Null值。

你可以在创建表时使用KEY关键字来创建普通索引，也可以在创建表后使用ALTER TABLE语句来添加普通索引。

比如：

CREATE TABLE employee (
    id INT(11) NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age INT(11) NOT NULL,
    INDEX name_age (name,age)
) ENGINE=InnoDB;

如何创建索引？

你可以使用CREATE INDEX语句来在现有表的一个或多个列上创建新索引。你也可以使用ALTER TABLE添加新索引或删除现有索引。

比如：

CREATE INDEX idx_name ON employee (name);

使用索引时需要注意的事项

索引并不是越多越好

虽然索引有助于提高查询性能，但是索引也有开销。每个索引都需要占用磁盘空间，索引数据结构也需要内存支持。索引数量增加，维护索引的代价也会增加。因此，只有在需要改善查询性能时才应该考虑添加索引。

使用最左前缀匹配

当你使用多列索引时，MySQL从最左边的列开始匹配，在找到非常不精确的匹配后就会停止搜索。因此，请确保多列索引的最左列使用得当。

比如：

SELECT *
FROM employee
WHERE name='John'
AND age=30;

如果你在(name, age)上创建了多列索引，MySQL使用这个索引来查找所有名字为John且年龄为30的记录。但是，如果你查询的只是名字为John的记录，则可以使用仅在name列上创建的单列索引。

避免使用函数

如果你在查询的列上使用函数，MySQL不能使用索引。在查询之前对列进行操作，从而避免使用函数。

比如：

SELECT *
FROM employee
WHERE YEAR(start_date)=2018;

如果你使用了YEAR函数，则MySQL无法使用索引。相反，可以在查询之前进行转换：

SELECT *
FROM employee
WHERE start_date BETWEEN '2018-01-01' AND '2018-12-31';

使用覆盖索引

覆盖索引是指仅通过索引的数据就可以满足查询的要求。当你从表中查询的列与你检索的列完全匹配时，MySQL会使用覆盖索引。因为它避免了在表中找到相应的行所需的I/O操作，所以查询速度非常快。

比如：

SELECT name
FROM employee
WHERE age=30;

如果你在age上创建了索引，则MySQL不必从表中读取所有行，只需要从索引读取name列即可。

总结

以上就是mysql表索引的一些重点要点。在创建索引时应该仔细考虑，并避免创建过多的索引。我们还介绍了主键索引、唯一索引和普通索引等常见的索引类型，并提醒读者在使用索引时需要遵循一些原则和技巧。