MySQL索引

MySQL官⽅对索引的定义为：索引（Index）是帮助MYSQL⾼效获取数据的数据结构。从⽽可以得到索引的本质：索引是数据结构。（索引⽂件）

索引的⽬的在于提⾼查询效率，可以类⽐字典。

⽐如：如果要查找“mysql”。我们肯定需要定位到m字⺟，然后在往下找到y字⺟，在找剩下的sql。

如果没有索引，那么你可能需要a---z，逐条查询，如果我们想找到java或者oracle开头的词条呢？

是不是觉得如果没有索引，这个事情根本没办法完成呢？

可以理解为：已经排好序的快速查找数据结构。

👉1、什么是索引

Mysql官方给出的索引的定义：索引（Index）是帮助mysql高效获取数据的一种数据结构

但是我们可以理解为：已经排好序的快速查找数据结构。

我们平常所说的索引，如果没有特别指明，都是指B树（BTree），（多路搜索树，并不⼀定是⼆叉树）结构组织的索引。其中聚集索引，次要索引，复合索引，前缀索引，唯⼀索引默认都是使⽤B+树索引，统称索引。当然除了B+树这种类型索引之外还有哈希索引(hash index)等。

👉2、数据本身之外，数据库还维护？

数据本身之外,数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种⽅式指向数据，这样就可以在这些数据结构的基础上实现⾼级查找算法，这种数据结构就是索引。

👉3、索引本身大不大？

索引本身就很⼤，不可能全部存储在内存中，因为索引往往以索引⽂件的形式存储在磁盘中。

📝two / 索引的优势和劣势

👉1、索引的优势

查找：类似⼤学图书馆建书⽬录索引，提⾼数据检索的效率，降低数据库的IO成本；

排序：通过索引列对数据进⾏排序，降低数据排序的成本，降低CPU的消耗。

👉2、索引的劣势

实际上索引也是⼀张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也要占⽤空间的。

虽然索引⼤⼤的提供了查询速度，同时会降低更新表的速度，如果对表进⾏增、删、改操作时,MySQL 不仅要更新数据，还要更新⼀下索引⽂件。每次更新添加了索引列的字段数据，都会调整因为更新所带来的键值变化后的索引信息。索引只是提⾼效率的⼀个因素，如果你的MySQL有⼤数据量的表，就需要花时间研究建⽴最优秀的索引。或者优化查询。（专业的DBA职责）

总结就是：占用空间，降低更新表的速度。

📝three / MySQL索引数据结构

B+TREE 索引：最常⻅的索引类型，⼤部分引擎都⽀持B+树索引

HASH索引：底层数据结构是⽤哈希表实现，只有精确匹配索引列的查询才有效，不⽀持范围查询

R-TREE索引：空间索引是 MyISAM 引擎的⼀个特殊索引类型，主要⽤于地理空间数据类型，通常使⽤较少

FULL Text全⽂索引：是⼀种通过建⽴倒排索引，快速匹配⽂档的⽅式，类似于 Lucene, Solr, ES

各存储引擎的⽀持

索引	InnoDB	MylSAM	Memory
B+Tree索引	支持	支持	支持
Hash索引	不支持	不支持	支持
R-TREE索引	不支持	不支持	不支持
Full-TEXT全文索引	5.6版本后支持	支持	不支持

👉1、⼆叉搜索树

测试数据：28、73、36、23、18、32、12、40

⼆叉搜索树缺点：顺序插⼊时，会形成⼀个链表，查询性能⼤降低。⼤数据的情况下，层级较深，检索速度慢。

👉2、红⿊树

在二叉树的基础上多了树平衡，也叫“二叉平衡树”，它不像二叉树那样极端的情况，没错，二叉树只会往一个方向发展。

红⿊树相对⼆叉排序树⽽⾔有所优化，但是同样的也存在⼤数据的情况下，层级较深，检索速度慢的问题。

👉3、B Trees多路平衡查找树树

多路平衡查找树，它是在红⿊树的基础上，每个节点可以存放多个数据。以下是最⼤度数为3（3阶）的B-TREE。

度数：⼀个节点的⼦节点个数；

最⼤度数（max-degree），⼀个节点的最多⼦节点个数；

N阶B树，最⼤度数为N，

每个节点最多可以存储N-1个key（关键字）

每个节点最多有N+1个指针

原理

左⼦树 < 根 < 右⼦树

插⼊关键字时，如果节点已满，则将其中间关键字分裂成两个结点，中间关键字被提升到该结点的⽗结点

B Trees的特性：

关键字集合分布在整颗树中；
任何⼀个关键字出现且只出现在⼀个结点中；
搜索有可能在⾮叶⼦结点结束；
其搜索性能等价于在关键字全集内做⼀次⼆分查找；
⾃动层次控制;

B Trees的搜索，从根结点开始，对结点内的关键字（有序）序列进⾏⼆分查找，如果命中则结束，否则进⼊查询关键字所属范围的⼉⼦结点；重复，直到所对应的⼉⼦指针为空，或已经是叶⼦结点；

👉4、B+ Trees多路搜索树

B+ Trees是B Trees多路平衡树的变体，也是一种多路搜索树，与B Trees树的区别如下：

B+ Trees只会在叶⼦节点上⾯挂载数据，⽽⾮叶⼦节点不会存放数据，只存放索引列的数据；
叶⼦节点形成⼀个意向链表

B+ Threes 的特性

所有关键字都出现在叶⼦结点的链表中（稠密索引），且链表中的关键字恰好是有序的；
不可能在⾮叶⼦结点命中；
⾮叶⼦结点相当于是叶⼦结点的索引（稀疏索引），叶⼦结点相当于是存储（关键字）数据的数据层；
更适合⽂件索引系统；

与B Threes的相同之处？

B+ Trees的搜索与B Trees基本相同，区别是B+Trees只有达到叶⼦结点才命中（B Trees可以在⾮叶⼦结点命中），其性能也等价于在关键字全集做⼀次⼆分查找；

在MySQL中，是否对B+ Trees进⾏了优化

在MySQL中，对B+ Trees进⾏了优化，在原B+ Trees的基础上，增加⼀个指向相依叶⼦节点的链表指针。也就是说，叶⼦节点之间是双向指针连接，从⽽提⾼区间范围性能，范围查找。

👉5、Hash索引

哈希索引就是采⽤⼀定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。如果两个（或多个）键值，映射到⼀个相同的槽位上，他们就产⽣了hash冲突（也称为hash碰撞），可以通过链表来解决。

特点

Hash索引只能⽤于对等⽐较（=、in），不⽀持范围查询（betwwn、>、<、...）
⽆法利⽤索引完成排序操作
查询效率⾼，通常只需要⼀次检索就可以了，效率通常要⾼于 B+Tree 索引

📝four / 索引分类

👉1、单值索引

一个索引只包含单个列，一个表可以有多个单列索引（个人不建议超过5个索引）

👉2、复合索引

一个索引包含多个字段。（比如：账号+密码两个字段建立一个复合索引）

👉3、主键索引（PRIMARY KEY）

表中主键创建的索引，只能有一个，且不允许为null（创建主键索引时自动创建）

👉4、唯一索引（UNIQUE）

索引值的列必须是唯一的，可以有多个，但允许为null（创建唯一索引时自动创建）

👉5、全文索引（FULL TEXT）

全文索引查找的是文本中的关键词，而不是比较索引中的值，可以有多个

👉6、InnoDB 存储引擎

在 InnoDB 存储引擎中，根据索引的存储形式，⼜可以分为以下两种：

分类

含义

特点

聚集索引（Clustered

Index）

将数据存储与索引放⼀块，索引结构的叶⼦节点保存了⾏数据

必须有，⽽且只有⼀个

⼆级索引（Secondary

Index）

将数据与索引分开存储，索引结构的叶⼦节点关联的是对应的主键

可以存在多个

说明

⼆级索引，也叫⾮聚集索引；

为什么推荐InnoDB表必须有主键？

保证会有主键索引树的存在（因为数据存放在主键索引树上⾯
聚集索引选取规则
如果存在主键，主键索引就是聚集索引
如果不存在主键，将使⽤第⼀个唯⼀(UNIQUE)索引作为聚集索引
如果表没有主键或没有合适的唯⼀索引，则 InnoDB 会⾃动⽣成⼀个 rowid 作为隐藏的聚集索引

为什么⾮主键索引结构叶⼦节点存储的是主键值？

⼀是保证⼀致性，更新数据的时候只需要更新主键索引树
⼆是节省存储空间

为什么推荐使⽤整型的⾃增主键？

⼀是⽅便查找⽐较
⼆是新增数据的时候只需要在最后加⼊，不会⼤规模调整树结构。如果是UUID的话，⼤⼩不好⽐较，新增的时候也极有可能在中间插⼊数据，会导致树结构⼤规调整，造成插⼊数据变慢

📝five / 什么情况建⽴索引

主键⾃动建⽴唯⼀索引频繁作为查询条件的字段应该建⽴索引。最好是保存以后不再变更的字段，因为在增、删、改操作时，会造成数据的变动，同时索引⽂件也会变动。⽽在删除操作时，会带来索引⽂件的重新调整。那么我们可以这样避免：在表增加⼀个状态字段is_delete=0，如果⽤户删除数据时，把is_delete=1即可。这样可以避免变动索引⽂件，从⽽减少资源的消耗

👉1、查询中与其他表关联的字段

查询中与其他表关联的字段，即外键关系建⽴索引要控制索引的数量（不要超过5个索引），太多就性能瓶颈和维护成本，影响增删改的效率

👉2、一般在开发中建议建⽴复合索引？

id name account password email createtime 
我们会建⽴account+password的复合索引，…where account=? and password=?

查询中排序的字段，排序字段若通过索引去访问将⼤⼤的提供排序速度。

查询中统计或者分组字段（group by也和索引有关）。

📝six / 什么情况不建⽴索引

👉1、频繁更新

频繁更新的字段不适合作为索引，因为每次更新不单单是更新记录还在更新索引。

👉2、表的记录数

表的记录数太少

👉3、频繁增删改

对表的频繁的增删改

👉4、数据重复

数据重复且平均的字段。

👉5、where字段⽤不到

where字段⽤不到的字段不要建⽴索引

📝seven / 操作索引

案例如下

👉1、创建语法⼀

# 索引命名规范：idx_xxx
CREATE [UNIQUE] INDEX 索引名称 ON 表名 (字段|字段列表) ;
# 单值索引
CREATE INDEX 索引名称 ON 表名 (字段) ;
CREATE INDEX index_username ON userinfo(username) ;
# 唯⼀索引
CREATE UNIQUE INDEX 索引名称 ON 表名 (字段列表) ;

CREATE UNIQUE INDEX index_username ON userinfo(username) ;
# 复合索引,字段列表中的字段顺序是有讲究的
CREATE INDEX 索引名称 ON 表名(字段列表) ;
CREATE INDEX index_username_password ON userinfo(username,password) ;

👉2、创建语法二

ALTER TABLE 表名 ADD [UNIQUE] INDEX 索引名称(字段|字段列表)
# 单值索引
ALTER TABLE 表名 ADD INDEX 索引名称(字段)
ALTER TABLE userinfo ADD INDEX index_username(username)
# 唯⼀索引
ALTER TABLE 表名 ADD UNIQUE INDEX 索引名称(字段列表)
ALTER TABLE userinfo ADD UNIQUE INDEX index_username ON (username)
# 该语句添加⼀个主键，这意味着索引值必须是唯⼀的，并且不能为NULL
ALTER TABLE 表名 ADD INDEX 索引名称(字段列表)
ALTER TABLE userinfo ADD INDEX index_username_password ON (username,password)
# 主键索引（特殊的唯⼀索引）
ALTER TABLE 表名 ADD PRIMARY KEY(字段列表);

注意

如果某个字段设置为主键约束（primary key），那么该字段默认就是主键索引。

主键索引是特殊的唯⼀索引

相同点：该列中的数据都不能有相同值；
不同点：主键索引不能有null值，但是唯⼀索引可以有null值

👉3、全⽂检索

# 该语句指定了索引为FULLTEXT，⽤于全⽂检索
ALTER TABLE 表名 ADD FULLTEXT 索引名称(字段列表);

👉4、查看索引

#\g是缩写的grid，mysql里去掉\g
SHOW INDEX FROM 表名; \G

👉5、删除索引

DROP INDEX 索引名称 ON 表名 ;

✏️二、索引失效情况

全值匹配我最爱。
最佳左前缀法则。
不在索引列上做任何操作（计算、函数、(⾃动or⼿动)类型转换），会导致索引失效⽽转向全表扫描。
索引中范围条件右边的字段会全部失效。
尽量使⽤覆盖索引（只访问索引的查询，索引列和查询列⼀致），减少 SELECT * 。
MySQL在使⽤ != 或者 <> 的时候⽆法使⽤索引会导致全表扫描。
is null 、 is not null 也⽆法使⽤索引。
like 以通配符开头 %abc 索引失效会变成全表扫描。
字符串不加单引号索引失效。
少⽤ or ，⽤它来连接时会索引失效。

准备数据为下面的内容讲解和测试

# 准备数据
CREATE TABLE `staffs`(
`id` INT(10) PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(24) NOT NULL DEFAULT '' COMMENT '姓名',
`age` INT(10) NOT NULL DEFAULT 0 COMMENT '年龄',
 `phone` CHAR(11) NOT NULL DEFAULT '' COMMENT '联系⽅式',
`pos` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '职位',
`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '⼊职时间'
) COMMENT '员⼯记录表';
INSERT INTO `staffs`(`name`,`age`,`phone`,`pos`) VALUES
('Rose',18,'13417740001','dev'),
('Lucy',19,'13417740002','dev'),
('Lily',17,'13417740003','dev'),
('Petter',16,'13417740004','dev'),
('Preusig',15,'13417740005','dev'),
('Zielinski',13,'13417740006','dev'),
('Kalloufi',14,'13417740007','dev'),
('Peac',16,'13417740008','dev'),
('Piveteau',29,'13417740009','dev'),
('Sluis',16,'13417740010','dev'),
('Bridgland',18,'13417740011','dev'),
('Terkki',21,'13417740012','dev'),
('Genin',20,'13417740013','dev'),
('Nooteboom',12,'13417740014','dev'),
('Cappelletti',14,'13417740015','dev'),
('Bouloucos',13,'13417740016','dev'),
('Peha',14,'13417740017','dev'),
('Haddadi',15,'13417740018','dev'),
('Warwick',16,'13417740019','dev');
# 创建复合索引
CREATE INDEX idx_staffs_name_age_pos ON `staffs`(`name`,`age`,`pos`);

📝one / 最佳左前缀法则

最佳左前缀法则

如果索引是多字段的复合索引，要遵守最佳左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的字段；
如果跳过第个索引字段，则索引失败；
如果跳过某个索引字段，则索引将部分失效；

# ⽤到了idx_staffs_name_age_pos索引中的name字段
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'Ringo';
# ⽤到了idx_staffs_name_age_pos索引中的name, age字段
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'Ringo' AND `age` = 18;
# ⽤到了idx_staffs_name_age_pos索引中的name，age，pos字段，这是属于全值匹配的情况！！！
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'Ringo' AND `age` = 18 AND `pos` = 'manager';
# 同上，正常的使⽤了复合索引，条件顺序⽆关
EXPLAIN SELECT * FROM `staffs` WHERE `age` = 18 AND `pos` = 'manager' AND `name` = 'Ringo' ;
# 索引没⽤上，ALL全表扫描
EXPLAIN SELECT * FROM `staffs` WHERE `age` = 18 AND `pos` = 'manager';
# 索引没⽤上，ALL全表扫描
EXPLAIN SELECT * FROM `staffs` WHERE `pos` = 'manager';
# ⽤到了idx_staffs_name_age_pos索引中的name字段，pos字段索引失效
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'Ringo' AND `pos` = 'manager';

⼝诀：带头⼤哥不能死，中间兄弟不能断。

📝two / 索引列上不计算

# 现在要查询`name` = 'Ringo'的记录下⾯有两种⽅式来查询！
# 1、直接使⽤ 字段 = 值的⽅式来计算
mysql> SELECT * FROM `staffs` WHERE `name` = 'Ringo';
+----+-------+-----+---------+---------------------+
| id | name | age | pos | add_time |
+----+-------+-----+---------+---------------------+
| 1 | Ringo | 18 | manager | 2020-08-03 08:30:39 |
+----+-------+-----+---------+---------------------+
1 row in set (0.00 sec)
# 2、使⽤MySQL内置的函数
mysql> SELECT * FROM `staffs` WHERE LEFT(`name`, 5) = 'Ringo';
+----+-------+-----+---------+---------------------+
| id | name | age | pos | add_time |
+----+-------+-----+---------+---------------------+
| 1 | Ringo | 18 | manager | 2020-08-03 08:30:39 |
+----+-------+-----+---------+---------------------+
1 row in set (0.00 sec)

我们发现以上两条SQL的执⾏结果都是⼀样的，但是执⾏效率有没有差距呢？？？

通过分析两条SQL的执⾏计划来分析性能。

由此可⻅，在索引列上进⾏计算，会使索引失效。

⼝诀：索引列上不计算。

📝three / 范围之后全失效

# ⽤到了idx_staffs_name_age_pos索引中的name，age，pos字段 这是属于全值匹配的情况！！！
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'Ringo' AND `age` = 18 AND `pos` = 'manager';
# ⽤到了idx_staffs_name_age_pos索引中的name，age字段，pos字段索引失效
EXPLAIN SELECT * FROM `staffs` WHERE `name` = '张三' AND `age` > 18 AND `pos` = 'dev';
# 解决：在业务允许的情况下，加上“=”
EXPLAIN SELECT * FROM `staffs` WHERE `name` = '张三' AND `age` >= 18 AND `pos` = 'dev';

查看上述SQL的执⾏计划

由此可知，查询范围的字段使⽤到了索引，但是范围之后的索引字段会失效。

⼝诀：范围之后全失效。

📝four / 覆盖索引尽量⽤

在写SQL的不要使⽤ SELECT * ，⽤什么字段就查询什么字段，且这些字段应该是索引字段，如果不是索引字段，则很有可能出现回表查询。

在Extra字段中

using index condition ：查询使⽤了索引，但是需要回表查询数据
Using where;using index：查找使⽤了索引，但是需要的数据都在索引列中能找到，不需要回表查询数据
当然版本不同，出现的结果可能会不⼀样

# 聚集索引，叶⼦节点直接返回整⾏数据
EXPLAIN SELECT * FROM `staffs` WHERE id=20;
# 给name字段，创建索引
create index idx_staffs_name on staffs(name)
# 使⽤了覆盖索引 -- Extra:Using index
explain select id,name from staffs where name='Rose'
# 没有使⽤覆盖索引 -- Extra:NULL
# 其中，phone字段没有定义索引
explain select id,name,phone from staffs where name='Rose';
# 以下也没有使⽤覆盖索引，*包含了其它⾮索引字段 -- 必定会进⾏回表查询，从⽽影响性能
explain select * from staffs where name='Rose';

⼝诀：查询⼀定不⽤ *(星号) 。

📝five / like百分加右边

通过符%不能写在开始位置，否则索引失效。

# 索引失效 全表扫描
EXPLAIN SELECT * FROM `staffs` WHERE `name` LIKE '%ing%';
# 索引失效 全表扫描
EXPLAIN SELECT * FROM `staffs` WHERE `name` LIKE '%ing';
# 使⽤索引范围查询
EXPLAIN SELECT * FROM `staffs` WHERE `name` LIKE 'Rin%';

⼝诀： like 百分加右边

如果⼀定要使⽤ %like ，⽽且还要保证索引不失效，那么使⽤覆盖索引来编写SQL。

# 使⽤到了覆盖索引
EXPLAIN SELECT `id` FROM `staffs` WHERE `name` LIKE '%in%';

# 使⽤到了覆盖索引
EXPLAIN SELECT `name` FROM `staffs` WHERE `name` LIKE '%in%';
# 使⽤到了覆盖索引
EXPLAIN SELECT `age` FROM `staffs` WHERE `name` LIKE '%in%';
# 使⽤到了覆盖索引
EXPLAIN SELECT `pos` FROM `staffs` WHERE `name` LIKE '%in%';
# 使⽤到了覆盖索引
EXPLAIN SELECT `id`, `name` FROM `staffs` WHERE `name` LIKE '%in%';
# 使⽤到了覆盖索引
EXPLAIN SELECT `id`, `age` FROM `staffs` WHERE `name` LIKE '%in%';
# 使⽤到了覆盖索引
EXPLAIN SELECT `id`,`name`, `age`, `pos` FROM `staffs` WHERE `name` LIKE '%in';
# 使⽤到了覆盖索引
EXPLAIN SELECT `id`, `name` FROM `staffs` WHERE `pos` LIKE '%na';
# 索引失效 全表扫描 , 覆盖索引 不包含 add_time
EXPLAIN SELECT `name`, `age`, `pos`, `add_time` FROM `staffs` WHERE `name` LIKE '%in';

⼝诀：覆盖索引保两边。

📝six / 字符要加单引号

字段串类型的索引字段，在使⽤时必须加引号，否则索引失效

# 符合最左法则，部分使⽤了索引
EXPLAIN SELECT * FROM `staffs` WHERE `name` = '2000';
# 字符串类型字段不加引号，索引失效
# 这⾥name = 2000在MySQL中会发⽣强制类型转换，将数字转成字符串。
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 2000;
# 以下查询是否⾛索引
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'Ringo' AND `age` = 18 AND `pos` = 1000;

⼝诀：字符要加单引号。

📝seven / or连接的条件

使⽤or分割的条件中，如果or前的条件中字段有索引，⽽后⾯的字段中没有索引，那么涉及的索引都不会被⽤到。

# 其中，id字段有主键索引，add_time字段没有定义索引
# 解决：给add_time字段添加索引
EXPLAIN SELECT * FROM `staffs` WHERE id=1 or add_time='2021-03-01';
# 索引失效，全表查询，or的右边也必须遵循最左原则
EXPLAIN SELECT * FROM `staffs` WHERE id=1 or age=20;
# 索引⽣效
EXPLAIN SELECT * FROM `staffs` WHERE id=1 or name='李四';

📝eight / 数据分布影响

如果MySQL评估使⽤索引⽐值班表更慢，则不使⽤索引。

# 全表查询
explain SELECT * from staffs where phone>='13417740000';
# 给phone字段添加索引
create index idx_staffs_phone on staffs(phone)
# 再次测试，发现仍然是全表查询，并没有使⽤到索引
# 原因：当满⾜条件时，相当于查询全表，MySQL认为查询全表⽐按索引查询更快
explain SELECT * from staffs where phone>='13417740000';
# 修改条件再测试，满⾜条件只是部分数据，进索引更快
explain SELECT * from staffs where phone>='13417740009';
# 思考，执⾏以下SQL语句，是否⾛索引？
EXPLAIN SELECT * FROM `staffs` WHERE `phone` != '13417740001';
# 删除数据，再测试
DELETE from staffs where phone>='13417740004'

📝nine / SQL提示

SQL提示，是优化数据库的⼀个重要⼿段。简单的说，就是在SQL语句中加⼊⼀些⼈为的来达到优化操作的⽬的。

use index：使⽤某个索引，但MySQL不⼀定使⽤
Ignore index：忽略某个索引
force index：强制使⽤某个索引

# 语法
select 字段集合 from 表名 use index(索引名称) where 条件 ;
select 字段集合 from 表名 ignore index(索引名称) where 条件 ;
select 字段集合 from 表名 force index(索引名称) where 条件 ;
# 创建单列索引
CREATE INDEX idx_staffs_name ON `staffs`(`name`);
# 测试，可能⽤到的索引有：idx_staffs_name_age_pos、idx_staffs_name
# 最终，MySQL选择了idx_staffs_name_age_pos
# 如果我们想使⽤idx_staffs_name索引，怎么办呢？
EXPLAIN SELECT * FROM `staffs` WHERE name='lucy';
# 使⽤指定的索引
EXPLAIN SELECT * FROM `staffs` use index(idx_staffs_name) WHERE name='lucy';
# 忽略某个索引
EXPLAIN SELECT * FROM `staffs` ignore index(idx_staffs_name_age_pos) WHERE name='lucy';
# 强制使⽤某个索引
EXPLAIN SELECT * FROM `staffs` force index(idx_staffs_name) WHERE name='lucy';

📝ten / 前缀索引

当字段类型为字符串时，字符串的⻓度可能很⼤，创建索引时，如果都使⽤全部字符串来创建，则索引会很⼤，查询时，浪费⼤量的IO，从⽽影响查询效率。因此，我们可以截取字符串的⼀⼩部分作为前缀，⽤于创建索引，这样可以⼤⼤节约索引空间，从⽽提⾼索引效率。

CREATE [UNIQUE] INDEX 索引名称 ON 表名 (字段(⻓度)) ;

📝eleven / 如何设置字段前缀的⻓度

可以根据索引的选择性来决定；
选择性是指不重复的索引值（基数）和数据表的记录总数的⽐值，索引选择性越⾼则查询效率越⾼；
唯⼀索引的选择性是1，这是最好的索引选择性，性能也是最好的。

# 查看某个字段的 选择性
select count(distinct(字段)) / count(*) from 表
# 查看某个字段前缀为N的 选择性
select count(distinct(left(字段,N))) / count(*) from 表
# 查看截取前⾯两个字符的选择性
select count(distinct(left(name,2))) / count(*) from staffs;

✏️总结

基础打得好，才便于后面的学习，以及未来的发展！加油！.

半杯可可

关注

14
点赞
踩
11

收藏

觉得还不错? 一键收藏
打赏
11
评论
MySQL索引

Mysql官方给出的索引的定义：索引（Index）是帮助mysql高效获取数据的一种数据结构但是我们可以理解为：已经排好序的快速查找数据结构。我们平常所说的索引，如果没有特别指明，都是指B树（BTree），（多路搜索树，并不⼀定是⼆叉树）结构组织的索引。其中聚集索引，次要索引，复合索引，前缀索引，唯⼀索引默认都是使⽤B+树索引，统称索引。当然除了B+树这种类型索引之外还有哈希索引(hash index)等。
复制链接

扫一扫