聊一聊关系型数据库的索引

在大多数应用系统的数据库中，读写比例在 10:1
左右，插入和一般的更新操作很少出现性能问题，最容易出问题的往往是复杂的查询操作，在大数据量的情况下， 查询语句的优化是非常重要的
。

在数据量和访问量不大的情况下，数据库访问是非常快的，是否加索引影响并不大。这种时候我们并不需要花费太多精力去做索引优化，因为这样的投入产出比并不理想。但是当数据和访问量激增时，数据库运行效率会明显变慢，甚至终止服务。在大数据量的情况下，SQL优化是必要的， 给数据库建立合适的索引是SQL优化的一个重要手段
。
索引存在的目的是提高查询效率。

我们可以类比一下从字典查询一个单词，加入要查询 “China” 这个单词，我们需要从目录索引先找到 c 字母开头的单词所在的第一页，再往下检索 h 字母，重复操作直到找到单词（或未找到）。假如没有首字母索引，我们可能需要将所有的单词看一遍才能找到对应单词。除了字典，生活中随处可见索引的应用，如文章目录、车站车次表等等，原理都是 通过不断缩小数据范围
来检索想要的结果，当然，这里还有一个前提，数据的排列是一个确定的顺序，即所有数据都可以通过同一个查找方式找出（或确认不存在）。
在创建索引时，我们需要考虑哪些列会用于查询，然后会这些列创建一个或多个索引。实际上，索引也是一个表，保存着主键或索引字段，以及其对应的指向实际表中数据位置的指针。数据库用户是看不到索引的，它们仅仅用于加快查询速度，数据库引擎会使用索引来快速定位数据的位置。

在建立了索引的数据表中，SELECT 语句执行速度会加快，但 INSERT 和 UPDATE 语句会变慢。因为在插入和更新时，索引值也会发生相应的变化。

索引的创建、删除

索引的类型有：

• UNIQUE

  唯一索引，不允许出现相同的值，允许为 NULL

• INDEX

  普通索引，允许出现相同值

• PRIMARY KEY

  主键索引，不允许出现相同值

• FULLTEXT INDEX

  全文索引，可针对值中的某个单词建立索引，但效率很低

• 组合索引
  又称复合索引，将多个字段建立到一个索引里，列值的组合必须唯一

《阿里巴巴 Java 开发手册》中的 SQL 规范约定，唯一索引建议命名为 uk_字段名
，普通索引命名为 idx_字段名

1. 创建索引 —— ALTER TABLE

用于给已经存在的表添加索引

ALTER TABLE 表名 ADD 索引类型 (unique,primary key,fulltext,index) [索引名] (字段名);

使用示例

// 普通索引
ALTER TABLE users ADD INDEX idx_name (name);
// 唯一索引
ALTER TABLE users ADD UNIQUE uk_name (name);
// 主键索引
ALTER TABLE users ADD PRIMARY KEY (name);

2. 创建索引 —— CREATE INDEX

CREATE INDEX 可用于建表时创建普通或唯一索引

CREATE 索引类型 索引名 ON 表名 (字段名);

使用示例

CREATE INDEX idx_name ON users (name);
CREATE UNIQUE uk_name ON users (name);

3. 删除索引

删除索引可以使用 ALTER TABLE 或 DROP INDEX 语句来实现，DROP INDEX 可用于 ALTER TABLE 语句内部

DROP INDEX 索引名 ON 表名;
ALTER TABLE 表名 DROP INDEX 索引名;
ALTER TABLE 表名 DROP PRIMARY KEY;

4. 组合索引、前缀索引

组合索引、前缀索引只是对索引使用方式的称呼，并不是索引的类型。我们建立一个 demo 表，结构如下：

create table USERS
(
  ID                        int not null auto_increment comment '主键',
  LOGIN_NAME        varchar(100) not null comment '登录名',
  PASSWORD             varchar(100) not null comment '密码',
  CITY                    varchar(30)  not null comment '城市',
  AGE                        int not null comment '年龄',
  SEX                        int not null comment '性别：0女，1男',
  primary key (ID)
)

为了进一步提升数据库查询效率，可以考虑建立组合索引，将 LOGIN_NAME, CITY, AGE 建立到一个索引中

ALTER TABLE USER_DEMO ADD INDEX name_city_age (LOGIN_NAME(16),CITY,AGE);

表结构中，LOGIN_NAME的长度为100，但建立索引时为什么使用16？这是因为大部分名字的长度都不会超过16，这样建立索引可以加快索引查询速度（因为索引数量的减少），同时减少索引文件的大小，提高INSERT、UPDATE效率。
如果分别给 LOGIN_NAME, CITY, AGE 建立单列索引，让该表有3个单列索引，查询时和组合索引的效率是大不一样的，甚至远远低于我们的组合索引。虽然此时有三个索引，但数据库只能用到其中那个它认为是最有效率的单列索引，另外两个是用不到的，也就是说还是一个全表扫描的过程。
上面建立的组合索引，相当于分别建立一下三个组合索引：

LOGIN_NAME, CITY, AGE
LOGIN_NAME, CITY
LOGIN_NAME

为什么没有 CITY, AGE
这样的组合索引呢？这是因为数据库组合索引 “ 最左前缀
” 原则的结果，简单的理解就是，只从最左边的索引开始组合，加入查询的条件顺序是 SEX, LOGIN_NAME, CITY ，由于没有 SEX 索引，即使有右边的两个索引，数据库也不会执行索引查询。

索引使用的注意事项

EXPLAIN
语句可以帮助开发人员分析 SQL 语句的问题，它会将数据库如何使用索引处理 SELECT 语句以及连接表的过程显示出来，从而帮助开发人员分析优化索引和查询语句。
直接在 SELECT 语句前加上 EXPLAIN 即可

EXPLAIN SELECT * from user where id = 1;

编写 SQL 查询语句时，我们需要注意一些事项：

• 尽量避免对索引列进行计算
  查询语句涉及对索引列进行计算时，数据库检索时不会使用索引。

  // 因涉及索引列计算，检索时不会使用 age 索引
  SELECT name, age from users where age + 10 = 30;

• 尽量避免对索引列进行函数运算
  原理与上一条相同
• 尽量避免使用使用前模糊查询

  SELECT * FROM users WHERE name LIKE '%quincy'; // 不使用索引
  SELECT * FROM users WHERE name LIKE 'quincy%'; // 使用索引

• 尽量避免字符串与数字做比较
  当索引列类型为字符串，查询与数字比较（或反过来）时，数据库检索时不使用索引

  // 假设age是varchar类型
  SELECT * FROM users WHERE age='24'; // 走索引
  SELECT * FROM users WHERE age=24;        // 不走索引

• 尽量不使用 or 语句，除非所有条件列都存在索引

  // 假设 name, age 存在索引，city无索引
  SELECT * FROM users WHERE name = 'quincychen' or age = 24 // 走索引
  SELECT * FROM users WHERE name = 'quincychen' or city = 'shenzhen' // 不走索引

索引的好处有很多，但与此同时，也存在一些缺点：

• 虽然索引大大的提高了查询速度，但同时也会降低表的更新效率。

  对表进行 INSERT, UPDATE, DELETE 时，更新数据的同时要更新索引文件

• 建立索引会占用磁盘空间。

  一般而言，由于磁盘成本较低，这个问题不太重要，但是如果给一个数据量极大的表添加过多的索引时，索引文件的膨胀速度是惊人的。

使用索引的一些技巧

1. 尽量使用短索引

   短索引可以提高查询效率，同时减小索引文件的体积。例如，用户名最长为 255，但大部分用户名不超过30，则对用户名列建立长度为30的索引即可。

2. 索引列排序问题

   一张表查询只能用到一个索引。当 where 子句已经使用了索引时， order by 语句中的列不会使用索引。因此，当数据库默认排序符合要求时不要使用排序操作，尽量不要包含多个列的排序，必要时可以给这些列建立复合索引。如果 where 中的列和 order 中的列是分开建立的索引，则 where 语句会使用索引，但 order 语句不使用索引。

3. 正确使用 like 语句

   尽量减少 like 语句的使用，必要时，注意使用方式，避免使用前模糊查询 %test

4. 尽量不对索引列进行运算

   包括算数计算、函数运算，查询中对索引列进行运算时数据库检索将不使用索引

5. 正确使用操作符

   尽量减少不使用的索引的操作符，如：NOT IN, , !=，用走索引的操作符代替，如：<, =, BETWEEN, IN

6. 对经常用于 SELECT 操作的列建立索引

   如果列很少被用到，那么有无索引并不会明显改善查询速度，相反，还会增加数据库的更新速度和磁盘占用

7. 对常出现在 where 和 join 语句中的列建立索引

8. 尽量将索引建立在值比较唯一的列

9. 尽量不对 text, bit, boolean, blob 等数据类型的列建立索引

   这些数据类型要么数据量非常大，要么取值范围小，建立索引的回报率太低

10. 不同表的关联字段尽量采用相同类型和长度

    在执行 join 语句时，只有主键和外键类型一致时，数据库才会使用索引

11. 以下情况时不适合建立索引

    • 表数据量很少
    • 表更新非常频繁
    • 数据重复度很高且分布比较均匀的列

12. 尽量避免索引列值为null，建议设置默认值

13. 可以通过扩展已有索引满足时，不新建索引