Mysql必知必会：索引的使用

索引

索引用来排序数据以加快搜索和排序操作的速度。主键数据总是排序的， 因此，按主键检索特定行总是一种快速有效的操作。但是，搜索其他列中的值通常效率不高。这时候我们可以使用索引，在一个或多个列上定义索引，使 DBMS 保存其内容的一个排过序的列表。在定义了索引后，DBMS 以使用书的索引类似的方法使用它。DBMS 搜索排过序的索引，找出匹配的位置，然后检索这些行。

索引特点：

• 索引提高检索的性能，但降低了数据增删改的性能。 在执行这些操作时，DBMS 必须动态地更新索引。
• 索引数据可能要占用大量的存储空间。
• 并非所有数据都适合做索引。取值不多的数据（如地区）不如具有更多可能值的数据（如姓名），能够更加体现索引的价值。
• 索引用于数据过滤和数据排序。如果你经常以某种特定的顺序排序数据，则该数据可能适合做索引。
• 可以在索引中定义多个列（例如，国家 + 城市）。

普通索引

在创建表的时创建普通索引。

DROP TABLE IF EXISTS student;
CREATE TABLE student
(    
    id INT(11),
  stu_name VARCHAR(10)
)
复制代码

直接创建。

CREATE INDEX stu_id ON student(id);
复制代码

修改表时创建。

ALTER TABLE student ADD INDEX stu_id(id);
复制代码

删除索引。

DROP INDEX stu_id ON student;
复制代码

唯一索引

唯一索引列值必须唯一，但允许有空值（注意和主键不同）。如果是组合索引，则列值的组合必须唯一。事实上，在许多场合，创建唯一索引的目的往往不是提高访问速度，而是为了避免数据出现重复。

CREATE UNIQUE INDEX stu_id ON student(id);
复制代码

全局索引

全文索引只能作用在 CHAR、VARCHAR、TEXT、类型的字段上。创建全文索引需要使用 FULLTEXT 参数进行约束。

CREATE FULLTEXT INDEX s_name ON student(stu_name);
复制代码

多列索引

多列索引，即在数据表的多个字段上创建索引。

CREATE TABLE student
(    
  id INT(11),
  stu_name VARCHAR(10),
  email VARCHAR(20),
  INDEX info(stu_name, email)
);
复制代码

在多列索引中，只有查询条件中使用了这些字段中的第一个字段(即上面示例中的 stu_name 字段)，索引才会被使用（最左前缀’原则）。如果没有用到第一字段，则索引不起任何作用。

-- 使用索引
SELECT * FROM student WHERE stu_name = '张三';
SELECT * FROM student WHERE stu_name = '李四' AND email = '11111@qq.com';
-- 未使用索引
SELECT * FROM student WHERE email = '11111@qq.com';
复制代码