Mysql索引

介绍

• 使用索引的主要目的是为了优化查询速度
• 索引是一种特殊的文件或者叫数据结构(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

索引的分类

索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引, MyISAM和InnoDB存储引擎：只支持BTREE索引， 也就是说默认使用BTREE，不能够更换 * MEMORY/HEAP存储引擎：支持HASH和BTREE索引

• 索引的分类

  • 单列索引：
    * 普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。
    * 唯一索引：索引列中的值必须是唯一的，但是允许为空值，
    * 主键索引：是一种特殊的唯一索引，不允许有空值。

  • 组合索引

    • 在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。

  • 全文索引

    • 全文索引，mysql5.6之前，只有myisam支持全文索引。到了mysql5.6，innodb开始支持全文索引。只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引。

  • 空间索引：不做介绍，一般使用不到。

索引的使用

创建索引

• 单列索引之普通索引
  CREATE INDEX index_name ON table(column(length))
  ALTER TABLE table_name ADD INDEX index_name ON (column(length))
• 单列索引之唯一索引
  CREATE UNIQUE INDEX index_name ON table(column(length))
• 单列索引之全文索引
  CREATE FULLTEXT INDEX index_name ON table(column(length))
• 组合索引
  ALTER TABLE article ADD INDEX index_name (column(50),column(10))
• 删除索引
  DROP INDEX index_name ON table

实例

• 创建表

CREATE TABLE `user` (
  `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `name` varchar(100) DEFAULT NULL,
  `age` varchar(100) DEFAULT NULL,
  `address` varchar(100) DEFAULT NULL,
  `parent_id` bigint(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

• 插入数据

INSERT INTO `user` (`id`,`name`, `age`, `address`, `parent_id`)
VALUES
    (1, 'lili', '19',china, 1);

索引查询与普通查询比较

explain显示了mysql如何使用索引来处理select语句以及连接表。

• 普通查询分析

explain select * from user where parent_id = 1;

结果

image.png

• 唯一索引

创建索引

CREATE UNIQUE INDEX parent_id ON user(parent_id);

执行查询

explain select * from user where parent_id = 1;

结果

image.png

删除parent_id索引

DROP INDEX parent_id ON user;

• 普通索引

创建索引

CREATE INDEX parent_id ON user(parent_id);

执行查询

explain select * from user where parent_id = 1;

结果

image.png

• 组合索引

组合索引其实会默认按照最左前缀原则帮我们创建多组索
组合索引按照最左前缀原则
组合索引覆盖：要查询的列，也要使用索引覆盖住

创建索引

ALTER TABLE user ADD INDEX user_add_index (name(50),age(50),address(50));

查询

explain select * from user where name = 'lili' and age = '19' and address = 'china';

结果

image.png

组合索引错误查询

explain select * from user where age = '19';
explain select * from user where age = '19' and address = 'china';

• 全文索引

和常用的模糊匹配使用 like + % 不同，全文索引有自己的语法格式，使用 match 和 against 关键字

match() 函数中指定的列必须和全文索引中指定的列完全相同，否则就会报错，无法使用全文索引，这是因为全文索引不会记录关键字来自哪一列。如果想要对某一列使用全文索引，请单独为该列创建全文索引。

创建索引

CREATE FULLTEXT INDEX address_full_index ON user(address);

查询

explain select * from user where match(address) against('china');

结果

image.png