【MySQL】索引

1. 索引

索引（Index）是可以帮助MySQL高效获取数据的数据结构。索引的目的在于提高查询效率，可以将它与字典的目录类比，如果要查mysql这个单词，我们肯定要先定位到字母m，然后从上往下查找字母y，再查找剩下的sql。如果没有索引，我们可能需要扫描整部字典，这会导致查询性能下降，所以使用索引是一种提升查询性能、快速高效地获取数据的方法。
数据库除了存储业务数据之外，还维护着一个满足特定查找算法的数据结构，这个数据结构以某种方式指向数据，这样就可以在数据结构的基础上实现高级查找算法，这种数据结构就是索引。一般来说，索引本身也很大，不可能全部存储在内存中，因此往往以索引文件的形式存储在磁盘上。

2.索引的种类

• 普通索引：基本索引类型，允许在定义索引的列中插入重复值和空值。
• 唯一索引：索引列数据不允许出现重复，但是允许有空值。
• 主键索引：要求主键列中的每个值是非空、唯一的。创建一个主键系统将会自动创建主键索引。
• 复合索引：将多个列组合作为索引。
• 前缀索引：只能作用于文本内容，也就是在text、char、varchar数据类型的列上创建前缀索引，可以指定索引列的长度。

3. 索引的操作

1. 创建索引

CREATE TABLE `meituan_account` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '表自增主键',
    `bill_period` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '账期',
    `account_id` varchar(24) NOT NULL DEFAULT '' COMMENT '账户ID',
    `available_balance` varchar(24) NOT NULL DEFAULT '0' COMMENT '账户可用余额',
    `created_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间',
    `updated_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '更新时间',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uniq_1` (`bill_period`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='美团账户表';


ALTER TABLE `meituan_bill_order` ADD UNIQUE `uniq_1` ( `bill_period`, `sqt_biz_order_id` );

CREATE UNIQUE INDEX `uniq_1` ON department(`DNAME`);

CREATE TABLE `department` (
  `id` bigint NOT NULL,
  `DEPTNO` int NOT NULL,
  `DNAME` varchar(14) NOT NULL,
  `LOC` varchar(13) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_1` (`DNAME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

2. 删除索引

ALTER TABLE `meituan_bill_order` DROP INDEX `uniq_1`;

DROP INDEX `uniq_1` ON department;(普通索引和唯一索引都这么删除)

3. 查看索引

show index from sys_user;  
sys_user--表名

4.创建索引的指导原则

MySQL在以下操作中使用索引：

• 快速查找与where子句匹配的行。
• 如果在多个索引之间进行选择，MySQL通常选择使用查找最少行数的索引。
• 如果数据表具有多列索引，则优化器可以使用索引的任何最左前缀来查找数据。例如，如果在（col1, col2, col3）上有一个3列索引，则在使用(col1)、(col1, col2)和(col1, col2, col3)的查询功能时都能使用索引（最左匹配原则）。
• 如果需要进行类型转换才能比较值，则不同数据类型的列进行比较（例如，将字符串列与数字列进行比较）时可能会阻止使用索引。
• 如果查询仅使用某个索引中包含的列，则可以直接从索引树中检索所选值，以提高查询效率（索引覆盖）。
  提示： 当查询需要访问表中的大多数行时，那么按顺序读取比使用索引更快，因为顺序读取可以最大限度地减少磁盘搜索。

// TODO

附录

1.搭配第8节食用
2.面试官：MySQL为什么选择B+树作为索引结构
3.SQL优化之最左前缀原则分享
4.最左匹配原则