MySQL中的索引

MySQL中，所有数据类型的列都可以被索引，常用的存储引擎InnoDB和MyISAM能支持每个表创建16个索引。InnoDB和MyISAM使用的索引其底层算法是B-tree（B树），B-tree是一种自平衡的树，类似于平衡二叉排序树，能够保持数据有序。这种数据结构能够让查找数据、顺序访问、插入数据及删除的操作都在对数时间内完成
接下来我们通过一个简单的例子来说明索引的意义，比如我们要根据学生的姓名来查找学生，这个场景在实际开发中应该经常遇到，就跟通过商品名称查找商品道理是一样的。我们可以使用MySQL的explain关键字来查看SQL的执行计划

explain select * from tb_student where stuname='林震南'\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_student
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 11
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

在上面的SQL执行计划中，有几项值得我们关注：

type：MySQL在表中找到满足条件的行的方式，也称为访问类型，包括：ALL（全表扫描）、index（索引全扫描）、range（索引范围扫描）、ref（非唯一索引扫描）、eq_ref（唯一索引扫描）、const/system、NULL。在所有的访问类型中，很显然ALL是性能最差的，它代表了全表扫描是指要扫描表中的每一行才能找到匹配的行。
possible_keys：MySQL可以选择的索引，但是有可能不会使用。
key：MySQL真正使用的索引。
rows：执行查询需要扫描的行数，这是一个预估值。

从上面的执行计划可以看出，当我们通过学生名字查询学生时实际上是进行了全表扫描，不言而喻这个查询性能肯定是非常糟糕的，尤其是在表中的行很多的时候。如果我们需要经常通过学生姓名来查询学生，那么就应该在学生姓名对应的列上创建索引，通过索引来加速查询

create index inx_student_name on tb_student(stuname);

再次查看刚才的SQL对应的执行计划

explain select * from tb_student where stuname='林震南'\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_student
   partitions: NULL
         type: ref
possible_keys: idx_student_name
          key: idx_student_name
      key_len: 62
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

可以注意到，在对学生姓名创建索引后，刚才的查询已经不是全表扫描而是基于索引的查询，而且扫描的行只有唯一的一行，这显然大大的提升了查询的性能。MySQL中还允许创建前缀索引，即对索引字段的前N个字符创建索引，这样的话可以减少索引占用的空间（但节省了空间很有可能会浪费时间，时间和空间是不可调和的矛盾），如下所示

create index idx_student_name_1 on tb_student(stuname(1));

上面的索引相当于是根据学生姓名的第一个字来创建的索引，我们再看看SQL执行计划

explain select * from tb_student where stuname='林震南'\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_student
   partitions: NULL
         type: ref
possible_keys: idx_student_name
          key: idx_student_name
      key_len: 5
          ref: const
         rows: 2
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

1.索引类型

UNIQUE(唯一索引)：不可以出现相同的值，可以有NULL值；
INDEX(普通索引)：允许出现相同的索引内容；
PROMARY KEY(主键索引)：不允许出现相同的值；
fulltext index(全文索引)：可以针对值中的某个单词，但效率确实不敢恭维；
组合索引：实质上是将多个字段建到一个索引里，列值的组合必须唯一；
2. 用 alter table 语句创建索引
应用于表创建完毕之后再添加；
- alter table 可用于创建普通索引、UNIQUE索引和PRIMARY KEY索引3种索引格式，
- table_name是要增加索引的表名，
- column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。
- 索引名index_name**可选**，缺省时，MySQL将根据第一个索引列赋一个名称。
- 另外，ALTER TABLE允许在单个语句中更改多个表，因此可以同时创建多个索引。

ALTER TABLE 表名 ADD 索引类型（unique,primary key,fulltext,index）[索引名]（字段名）

-- 普通索引
alter table table_name add index index_name (column_list) ;
-- 唯一索引
alter table table_name add unique (column_list) ;
-- 主键索引
alter table table_name add primary key (column_list) ;

3.用 create index 语句创建索引

CREATE INDEX可用于对表增加普通索引或UNIQUE索引，可用于建表时创建索引
如果是CHAR，VARCHAR类型，length可以小于字段实际长度；
如果是BLOB和TEXT类型，必须指定 length

CREATE INDEX index_name ON table_name(username(length));
--  create只能添加这两种索引;
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)

4.删除索引

删除索引可以使用ALTER TABLE或DROP INDEX语句来实现;

drop index index_name on table_name ;
alter table table_name drop index index_name ;
alter table table_name drop primary key ;

参考文章：https://www.cnblogs.com/jvjs/p/10560823.html

索引的设计原则

最适合索引的列是出现在WHERE子句和连接子句中的列。
索引列的基数越大（取值多重复值少），索引的效果就越好。
使用前缀索引可以减少索引占用的空间，内存中可以缓存更多的索引。
索引不是越多越好，虽然索引加速了读操作（查询），但是写操作（增、删、改）都会变得更慢，因为数据的变化会导致索引的更新，就如同书籍章节的增删需要更新目录一样。
使用InnoDB存储引擎时，表的普通索引都会保存主键的值，所以主键要尽可能选择较短的数据类型，这样可以有效的减少索引占用的空间，利用提升索引的缓存效果。
注意
InnoDB使用的B-tree索引，数值类型的列除了等值判断时索引会生效之外，使用>、<、>=、<=、BETWEEN…AND… 、<>时，索引仍然生效；对于字符串类型的列，如果使用不以通配符开头的模糊查询，索引也是起作用的，但是其他的情况会导致索引失效，这就意味着很有可能会做全表查询

Programming Language

【索引】创建、删除

MySQL中的索引

1.索引类型

2. 用 alter table 语句创建索引

3.用 create index 语句创建索引

4.删除索引

索引的设计原则

注意