MySQL索引

SQL优化是我们在面试的时候经常遇到的问题，而SQL优化主要在索引方面。下面这篇文章就来介绍一下MySQL的索引。

• 索引的作用
• 索引的优缺点
• 索引的分类
• 索引的设计原则
• 创建索引
• 查看索引
• 删除索引
• 面试题

索引的作用

• 索引在数据库中的作用是快速找出某个列中一个特定值的行，不使用索引的话，MySQL必须从第一条记录遍历到相关行，表越大，花费的时间越多，但是如果有索引，就能快速的到达某个位置去搜索数据文件，索引对于优化数据库查询速度有着不可替代的作用。

索引的优缺点

优点

1. 可以大大提高查询速度
2. 可以加速表与表的连接
3. 可以显著的减少查询中分组和排序的时间
4. 唯一索引还能确保数据库中每一行数据的唯一性

缺点

1. 创建索引和维护索引需要时间，而且表数据量越大，时间越长
2. 创建索引需要占据磁盘的空间
3. 当对表中的数据进行增、删、改的时候，索引也需要进行维护，降低了数据的维护速度

索引的分类

普通索引

• 基本的索引类型，允许在定义索引的列中插入空值或者重复值

唯一索引

• 索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。主键索引是一种特殊的唯一索引，不允许有空值

单列索引

• 只包含一个列的索引，一个表中可以有多个

组合索引

• 包含多个列的索引，查询条件包含这些列的最左边的字段的时候，索引就会被引用，遵循最左缀原则

全文索引

• 在定义的值中支持全文查找，允许空值和重复值，可以在CHAR，VARCHAR或者TEXT字段类型上创建，仅支持MyISAM存储引擎

空间索引

• 针对空间数据做的索引，支持的数据类型有4种，分别是GEOMETRY,POINT,LINESTRING和POLYGON。创建空间索引的列必须声明为非空值（NOT NULL），仅支持MyISAM存储引擎

索引的设计原则

• 1.不是越多越好
• 2.常更新的表越少越好
• 3.数据量小的表最好不要建立索引
• 4.不同的值比较多的列才需要建立索引
• 5.某种数据本身具备唯一性的时候，建立唯一性索引，可以保证定义的列的数据完整性，以提高查询熟度
• 6.频繁进行排序或分组的列(group by或者是order by)可以建立索引，提高搜索速度
• 7.经常用于查询条件的字段应该建立索引

创建索引

• 主键索引

ALTER TABLE students ADD PRIMARY KEY (sid)

• 创建普通索引

CREATE INDEX index_name ON table_name (column_list)

• 创建唯一索引

CREATE UNIQUE INDEX index_name ON table_name (column_list)

查看索引

• show index from tblname

删除索引

• DROP INDEX index_name ON talbe_name
• ALTER TABLE table_name DROP INDEX index_name
• ALTER TABLE table_name DROP PRIMARY KEY

其中，前两条语句是等价的，删除掉table_name中的索引index_name。

第3条语句只在删除PRIMARY KEY索引时使用，因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引。

如果从表中删除了某列，则索引会受到影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。

面试题

有ABCD，建立了index(A,C,D)
查A = xx and D = xx可以吗
查C = xx and D = xx可以吗
查C = xx and A = xx可以吗

• 建表语句

  CREATE TABLE t_test_index(
  id INT PRIMARY KEY AUTO_INCREMENT,
  A VARCHAR(256) NOT NULL,
  B VARCHAR(256) NOT NULL,
  C VARCHAR(256) NOT NULL,
  D VARCHAR(256) NOT NULL
  );
  ALTER TABLE t_test_index ADD INDEX index_name (A, C , D);

可以通过查看执行计划来看是否使用索引
EXPLAIN SELECT * FROM t_test_index WHERE A=‘1’ AND D=‘1’;
EXPLAIN SELECT * FROM t_test_index WHERE C=‘1’ AND D=‘1’;
EXPLAIN SELECT * FROM t_test_index WHERE C=‘1’ AND A=‘1’;

可以看到

   查A = xx and D = xx可以吗   -- 可以
   查C = xx and D = xx可以吗  -- 不可以
   查C = xx and A = xx可以吗   -- 可以