关于MySQL索引

文章目录

• • MySQL 索引是什么？
  • 索引的优势
  • 索引的劣势
  • 什么时候适合建立索引？
  • 什么时候不适合建立索引？
  • 一般性建议
  • MySQL索引分类
  • 如何避免索引失效？

MySQL 索引是什么？

官方定义：索引（INDEX）是帮助mysql高效获取数据的数据结构。

可以得到索引的本质：索引是数据结构。 拥有排序和查找两大功能，用于解决where和order by后面字段是否执行快。

看个图吧：

有的时候当某条数据不需要时，会将该数据active状态改成非激活状态。只是逻辑上进行删除，并未真正从物理上删除。

我们平常所说的索引，如果没有特别指明，都是指B+树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。

索引的优势

1、提高数据检索的效率，降低数据库的IO成本 2、降低数据排序的成本，降低了CPU的消耗

索引的劣势

1、实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的 2、虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。 因为更新表时，MYSQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

综上可知：

什么时候适合建立索引？

1、主键自动建立唯一索引 2、频繁作为查询条件的字段应该创建索引 3、查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度 4、查询中统计或者分组字段

什么时候不适合建立索引？

1、Where条件里用不到的字段不创建索引 2、表记录太少 3、经常增删改的表（提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，mysql不仅要保存数据，还要保存一下索引文件，加重了IO负担） 4、数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。（注意如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果）

一般性建议

1、对于单键索引，尽量选择针对当前query过滤性更好的索引 2、在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠左越好。 3、在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引 4、尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

MySQL索引分类

主键索引：它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引

普通索引：这是最基本的索引，它没有任何限制，单列允许重复

create index idx_name on user(name(20));

唯一索引：与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值（注意和主键不同）。如果是组合索引，则列值的组合必须唯一，创建方法和普通索引类似

CREATE UNIQUE INDEX idx_email ON user(email);

全文索引： MySQL支持全文索引和搜索功能。MySQL中的全文索引类型为FULLTEXT的索引。 FULLTEXT 索引仅可用于 MyISAM表:

CREATE TABLE articles (
   id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
   title VARCHAR(200),
   body TEXT,
   FULLTEXT (title,body)
);
mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database');

复合索引：即一个索引包含多个列:

CREATE TABLE test (
    id INT NOT NULL,
    last_name CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (id),
    INDEX name (last_name,first_name)
);

不过我比较习惯用 alter 对表设计进行修改：

如何避免索引失效？