MySQL 索引

备注：我们往 MySQL 插入的数据最终都是存在页中的。在 InnoDB 的设计中，页与页之间是通过一个双向链表连接起来。

1、为什么需要索引

一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，在生产环境中，我们遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，因此对查询语句的优化显然是重中之重。说起加速查询，就不得不提到索引了。

如果没有索引，我们在查找、修改、删除数据的时候，需要把全部的数据加载到内存中进行遍历，然后找到对应的数据进行操作，可想而知，这样效率很低，那有没有提升效率的方法呢，当然有，使用索引。

2、什么是索引

类似于字典中的目录，查找字典内容时可以根据目录查找到数据的存放位置，然后直接获取即可。

MySQL官方对索引定义：是存储引擎用于快速查找记录的一种数据结构。需要额外开辟空间和数据维护工作。

索引是物理数据页存储，在数据文件中（InnoDB，ibd文件），利用数据页(page)存储。

索引的优缺点

优点：

类似大学图书馆建书目索引，索引可以提高数据检索的效率，降低数据库的IO成本，这也是创建索引最主要的原因。
通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。
在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间，降低CPU的消耗。

缺点：

创建索引和维护索引要耗费时间，并且随着数据量的增加，所耗费的时间也会增加。
索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，存储在磁盘上，如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
虽然索引大大提高了查询速度，同时却会降低更新表的速度。当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

不同的存储引擎支持的索引类型

InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

3、Hash 索引

Hash索引底层是由 Hash 表来实现的，Hash 表是根据键值 <key,value> 存储数据的结构，。也就是说，它通过把关键码值映射到表中一个位置来访问记录，以加快查找的速度。

哈希表是 数组 + 链表 的形式，通过哈希函数计算每个节点数据中键所对应的哈希桶位置，如果出现哈希冲突，可以使用拉链法来解决。

Hash索引可以方便的提供等值查询，但是对于范围查询就需要全表扫描了。

Hash索引在 MySQL 中Hash结构主要应用在 Memory 原生的 Hash索引、InnoDB 自适应哈希索引。

Memory 原生的 Hash索引

MySQL 中的存储引擎只有 Memory 和 NDB 支持哈希索引，其中 Memory 默认使用哈希索引。

NDB存储引擎是 MYSQL 的集群存储引擎。对于这个存储引擎，MySQL服务器实际上变成了一个其他进程的集群客户端。

Memory 存储引擎使用存在于内存中的内容来创建表。每个 memory 表只实际对应一个磁盘文件，格式是 .frm 。memory 类型的表访问非常的快，因为它的数据是放在内存中的，并且默认使用HASH索引，但是一旦服务关闭，表中的数据就会丢失掉。

Memory类型的存储引擎主要用于那些内容变化不频繁的代码表，或者作为统计操作的中间结果表，便于高效地对中间结果进行分析并得到最终的统计结果。对存储引擎为memory的表进行更新操作要谨慎，因为数据并没有实际写入到磁盘中，所以一定要对下次重新启动服务后如何获得这些修改后的数据有所考虑。

①、优点

Hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像 B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引。
使用的场景：一些读操作密集的表建议使用hash索引，因为hash索引的查找速度是很快的。但是也有一些局限。

②、缺点

hash索引只包含哈希码和行指针，不能使用索引的值避免读取行，也就是要回表，不能像覆盖索引那样避免回表。
hash索引不能进行排序以及范围查找，只支持 = 、in 、<=>的比较，因为它们不会按照顺序保存行数据。
有可能产生hash碰撞，那么就必须要访问链表的每一个行指针，然后逐行进行比较得出正确数据。
因为hash算法是基于等值计算的，不支持部分键匹配，例如有个组合索引a_b，那么此时即使我们的where子句中使用到了a，也不会使用索引，like 等范围查找同理。

③、内存表与临时表区别

临时表和内存表比较像，但是这两个概念可是完全不同的。

内存表：指的是使用Memory引擎的表，建表语法是 CREATE TABLE … engine=memory。这种表的数据都保存在内存里，系统重启的时候会被清空，但是表结构还在。除了这两个特性看上去比较“奇怪”外，从其他的特征上看，它就是一个正常的表。
临时表：可以使用各种引擎类型。如果是使用InnoDB引擎或者MyISAM引擎的临时表，写数据的时候是写到磁盘上的。当然，临时表也可以使用Memory引擎。

InnoDB 自适应哈希索引

从以上可以知道，哈希表查找最优情况下是查找一次。而 InnoDB 使用的是 B+Tree，最优情况下的查找次数根据层数决定。因此为了提高查询效率，InnoDB 便允许使用 自适应哈希 来提高性能。

与其说这是一种索引不如称其为是一种 机制。自适应哈希索引的由来就是：当 InnoDB 注意到一些索引值被频繁的访问时，内部会在 B+Tree 索引的顶端为其创建哈希索引，保存在内存之中，使其具有快速哈希查找的特性，这个过程是它自动完成的。

可以通过参数 innodb_adaptive_hash_index 来决定是否开启。默认是打开的。

mysql> SHOW variables like "innodb_adaptive_hash_index";
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_adaptive_hash_index | ON    |
+----------------------------+-------+
1 row in set (0.16 sec)

存储引擎会自动对个索引页上的查询进行监控，如果能够通过使用自适应哈希索引来提高查询效率，其便会自动创建自适应哈希索引，不需要开发人员或运维人员进行任何设置操作。

自适应哈希索引是对 InnoDB 的缓冲池的 B+Tree 页进行创建，不是对整张表创建，因此速度很快。

可以通过查看 InndoDB 的 status 命令来查看自适应哈希索引的使用情况。

mysql> SHOW engine innodb status;
+--------+------+---------+
| Type   | Name | Status                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
+--------+------+---------+
| InnoDB |      |      
=====================================
2022-12-13 15:06:03 0x62f0 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 6 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 191 srv_active, 0 srv_shutdown, 708026 srv_idle
srv_master_thread log flush and writes: 708217
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 4025
OS WAIT ARRAY INFO: signal count 4057
RW-shared spins 0, rounds 40132, OS waits 3309
RW-excl spins 0, rounds 4589, OS waits 141
RW-sx spins 54, rounds 1573, OS waits 50
Spin rounds per wait: 40132.00 RW-shared, 4589.00 RW-excl, 29.13 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 3233592
Purge done for trx's n:o < 3233410 undo n:o < 0 state: running but idle
History list length 90
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 283413858068272, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (read thread)
I/O thread 4 state: wait Windows aio (read thread)
I/O thread 5 state: wait Windows aio (read thread)
I/O thread 6 state: wait Windows aio (write thread)
I/O thread 7 state: wait Windows aio (write thread)
I/O thread 8 state: wait Windows aio (write thread)
I/O thread 9 state: wait Windows aio (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
198289 OS file reads, 27374 OS file writes, 1469 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 552, seg size 554, 552 merges
merged operations:
 insert 13450, delete mark 1, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 17393, node heap has 1 buffer(s)
Hash table size 17393, node heap has 18 buffer(s)
Hash table size 17393, node heap has 1 buffer(s)
Hash table size 17393, node heap has 189 buffer(s)
Hash table size 17393, node heap has 2 buffer(s)
Hash table size 17393, node heap has 1 buffer(s)
Hash table size 17393, node heap has 0 buffer(s)
Hash table size 17393, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
LOG
Log sequence number 30110810223
Log flushed up to   30110810223
Pages flushed up to 30110810223
Last checkpoint at  30110810214
0 pending log flushes, 0 pending chkp writes
509 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 68648960
Dictionary memory allocated 685290
Buffer pool size   4096
Free buffers       1024
Database pages     2859
Old database pages 1035
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 53454, not young 2806497
0.00 youngs/s, 0.00 non-youngs/s
Pages read 197570, CREATEd 18349, written 25879
0.00 reads/s, 0.00 CREATEs/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 2859, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=1864, Main thread ID=1912, state: sleeping
Number of rows inserted 505838, updated 2, deleted 0, read 6824874
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
 |
+--------+------+---------+
1 row in set (0.17 sec)

可以看到自适应哈希索引大小，每秒的使用情况。注意从哈希表的特性来看，自适应哈希索引只能用于等值查询，范围或者大小是不允许的。

1 2	`-- 等值查询 SELECT * FROM xx WHERE name = "xxx";`

Hash 与 B+Tree 索引的优缺点

hash 类型的索引：查询单条快，范围查询慢。
B+Tree 类型的索引：层数越多，数据量指数级增长（我们就用它，因为 InnoDB 默认支持它）。

4、B+Tree MySQL 索引的实现

在MySQL中，索引是在存储引擎层实现的，不同存储引擎对索引的实现方式是不同的，下面我们探讨一下 MyISAM 和 InnoDB 两个存储引擎的索引实现方式。

MyISAM 索引实现

MyISAM 使用 B+Tree 作为索引结构，叶节点的data域存放的是数据记录的地址，MyISAM 索引的原理图如下：

这里假设表一共有三列，假设我们以 Col1 为主键，则上图是一个 MyISAM 表的主索引（Primary key）示意。可以看出 MyISAM 的索引文件仅仅保存数据记录的地址。

在 MyISAM 中，主键索引和辅助索引（Secondary key）在结构上没有任何区别，只是主键索引要求key是唯一的，而辅助索引的key可以重复。

如果我们在 Col2 上建立一个辅助索引，则此索引的结构如下图所示。同样也是一颗 B+Tree ，data域保存数据记录的地址。

MyISAM 中索引检索的算法为：首先按照 B+Tree 搜索算法搜索索引，如果指定的 Key 存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

InnoDB 索引实现

InnoDB 也使用 B+Tree 作为索引结构，但具体实现方式却与 MyISAM 不同。

第一个与 MyISAM 索引的不同是：MyISAM 索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在 InnoDB 中，表数据文件本身就是按 B+Tree 组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此 InnoDB 表数据文件本身就是主索引。

下图是 InnoD B主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引又叫做聚集索引（下面会详细讲）。

因为 InnoDB 的数据文件本身要按主键聚集，所以 InnoDB 要求表必须有主键（MyISAM 可以没有），如果没有显式指定，则 MySQL 系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则 MySQL 自动为 InnoDB 表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

第二个与 MyISAM 索引的不同是：InnoDB 的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB 的所有辅助索引都引用主键作为data域。下图为定义在 Col3 上的一个辅助索引。

这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了 InnoDB 的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在 InnoDB 中不是个好主意，因为 InnoDB 数据文件本身是一颗 B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持 B+Tree 的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

InnoDB 主键索引的 B+Tree 高度为多高呢?

我们知道 InnoDB存储引擎最小储存单元是 页，一页大小就是 16k。 B+Tree 叶子存的是数据，内部节点存的是键值+指针。索引组织表通过非叶子节点的二分查找法以及指针确定数据在哪个页中，进而再去数据页中找到需要的数据；

现在有这么一张表：主键bigint类型，长度为8Byte(int类型，一个int就是32位，4Byte)，而指针大小是固定的在InnoDB源码中设置为6Byte

那么一页所能存放的主键个数是 (8+6)B × n = 16 × 1024B，得出 n 约等于 1170。因为 B+Tree 的所有数据都存放在叶子节点上，所以叶子节点的占用内存比根节点多的，假设一行记录的数据大小为1k，那么单个叶子节点可以存 16 条记录；

如果树的高度是2，那么所存储的数据最多有：1170 × 16 = 18720
如果树的高度是3，那么所存储的数据最多有：1170 × 1170 × 16 = 21902400，2千多万
如果树的高度是4，那么所存储的数据最多有：1170 × 1170 × 16 = 25625808000，约25亿6千万

所以 B+Tree 高度一般为1-3层，已经满足千万级别的数据存储。

总结一下

MyISAM 和 InnoDB 使用的都是 B+Tree 索引，不同的是，InnoDB 的数据同时和主键索引存放在一起，而 MyISAM 的数据是存储在页中，InnoDB 其他索引的数据指向的是主键索引的值，而 MyISAM 所有索引指向的是数据的地址。

5、B+Tree 索引中的聚集索引与辅助索引

数据库中的 B+Tree 索引可以分为聚集索引（clustered index）和辅助索引（secondary index）:

MyISAM：所有的索引都是非聚集索引。
InnoDB：主键索引是聚集索引，其他索引都是非聚集索引。

聚集索引

聚集索引也叫聚簇索引。

InnoDB 存储引擎表是索引组织表，即表中数据按照主键顺序存放。聚集索引（clustered index）就是按照每张表的主键构造一棵 B+Tree ，同时叶子结点存放的即为整张表的行记录数据，也将聚集索引的叶子结点称为数据页。

聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同 B+Tree 数据结构一样，每个数据页都通过一个双向链表来进行链接。

由于实际的数据页只能按照一棵 B+Tree 进行排序，因此每张表只能拥有一个聚集索引。在多数情况下，查询优化器倾向于采用聚集索引。因为聚集索引能够在 B+Tree 索引的叶子节点上直接找到数据。此外由于定义了数据的逻辑顺序，聚集索引能够特别快地访问针对范围值的查询。

聚集索引的好处：

查询速度非常快：它对主键的排序查找和范围查找速度非常快，叶子节点的数据就是用户所要查询的数据。如用户需要查找一张表，查询最后的10位用户信息，由于 B+Tree 索引是双向链表，所以用户可以快速找到最后一个数据页，并取出10条记录。
对排序查找和范围查找优化：范围查询（range query），即如果要查找主键某一范围内的数据，通过叶子节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可。

缺点：

依赖于有序的数据：因为 B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或 UUID 这种又长又难比较的数据，插入或查找的速度肯定比较慢。
更新代价大：如果对索引列的数据被修改时，那么对应的索引也将会被修改，而且聚簇索引的叶子节点还存放着数据，修改代价肯定是较大的，所以对于主键索引来说，主键一般都是不可被修改的。

在 InnoDB 中，MySQL 是这样选择聚集索引的：

如果表中定义了 PRIMARY KEY，那么 InnoDB 就会使用它作为聚集索引；
如果没有定义 PRIMARY KEY，InnoDB 会选择第一个有 NOT NULL 约束的唯一索引作为 PRIMARY KEY，然后 InnoDB 会使用它作为聚集索引；
如果表中没有定义 PRIMARY KEY 或者合适的唯一索引。InnoDB 内部会在含有行ID值的合成列生成隐藏的聚集索引。这些行使用 InnoDB 赋予这些表的ID进行排序。行ID是6个字节的字段，且作为新行单一地自增。因此，根据行ID排序的行数据在物理上是根据插入的顺序进行排序。

回表查询

InnoDB 索引有聚集索引和辅助索引。聚集索引的叶子节点存储行记录，InnoDB 必须要有，且只有一个。辅助索引的叶子节点存储的是索引字段值和主键值，如果通过辅助索引无法直接定位行记录，通常情况下，需要扫码两遍索引树。先通过辅助索引定位主键值，然后再通过聚集索引定位行记录，这就叫做回表查询，它的性能比扫一遍索引树低。

总结：通过索引查询主键值，然后再去聚集索引查询记录信息

辅助索引

表中除了聚集索引外其他索引都是辅助索引（Secondary Index，也称为非聚集索引）。

与聚集索引的区别是：辅助索引的叶子节点不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含一个书签（bookmark）。该书签用来告诉 InnoDB 去哪里可以找到与索引相对应的行数据。

由于 InnoDB 是索引组织表，因此 InnoDB 的辅助索引的书签就是相应行数据的聚集索引键。如下图

辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引，但只能有一个聚集索引。

当通过辅助索引来寻找数据时，InnoDB 会遍历辅助索引并通过叶子级别的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录。

举例来说，如果在一棵高度为3的辅助索引树种查找数据，那需要对这个辅助索引树遍历3次找到指定主键，如果聚集索引树的高度同样为3，那么还需要对聚集索引树进行3次查找，最终找到一个完整的行数据所在的页，因此一共需要6次逻辑IO访问才能得到最终的一个数据页。

而 MyISAM 不是索引组织表，因此 MyISAM 存储引擎的辅助索引的书签就是相应行数据的实际地址。

非聚簇索引一定回表查询吗(覆盖索引)?

非聚簇索引不一定回表查询。

试想一种情况，用户准备使用 SQL 查询用户名，而用户名字段正好建立了索引。

1	`SELECT name FROM table WHERE name='guang19';`

那么这个索引的 key 本身就是 name，查到对应的 name 直接返回就行了，无需回表查询。

即使是 MYISAM 也是这样，虽然 MYISAM 的主键索引确实需要回表，因为它的主键索引的叶子节点存放的是指针。但是！如果 SQL 查的就是主键呢?

1	`SELECT id FROM table WHERE id=1;`

主键索引本身的 key 就是主键，查到返回就行了。这种情况就称之为覆盖索引了。

聚集索引和非聚集索引的联系与区别

相同点：不管是聚集索引还是辅助索引，其内部都是 B+Tree 的形式，即高度是平衡的，叶子结点存放着所有的数据。

聚集索引：

纪录的索引顺序与主键顺序相同，因此更适合 BETWEEN…AND 和 ORDER BY 操作。
叶子结点直接对应数据，从中间级的索引页的索引行直接对应数据页。
每张表只能创建一个聚集索引。

非聚集索引：

索引顺序和物理顺序无关。
叶子结点不直接指向数据页。
每张表可以有多个非聚集索引，需要更多磁盘和内容。

6、索引的常见分类

MySQL 中常见索引有：

普通索引：普通索引是最基本的索引，它没有任何限制，值可以为空。仅加速查询。
唯一索引：唯一索引与普通索引类似，不同：索引列的值必须唯一，允许有空值。如果是组合索引，则列值得组合必须唯一。加速查询 + 列值唯一（可以有null）。
主键索引：加速查询 + 列值唯一 + 表中只有一个（不可以有null）。
组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并。
全文索引：主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的 WHERE 语句的参数匹配。

索引分单列索引和组合索引：

单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。
组合索引(也叫复合索引、联合索引)，即一个索引包含多个列。

1、普通索引

普通索引仅有一个功能：加速查询

# 创建表时创建索引
CREATE TABLE 表名(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    extra text,
    index 索引名 (name)
)
# 创建索引
CREATE INDEX 索引名 ON 表名(column_name)
 
# 删除索引
DROP 索引名 ON 表名;
 
# 查看索引
SHOW INDEX FROM 表名;
 
# 注意：对于创建索引时如果是 BLOB 和 TEXT 类型，必须指定length。
CREATE INDEX 索引名 ON 表名(extra(32));

2、唯一索引

唯一索引有两个功能：加速查询和唯一约束（可含null）

# 创建表时创建索引
CREATE TABLE 表名(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    unique 索引名 (name)
)
 
# 创建索引
CREATE UNIQUE INDEX 索引名 ON 表名(列名)
 
# 删除索引
DROP UNIQUE INDEX 索引名 ON 表名

3、主键索引

主键有两个功能：加速查询和唯一约束（不可含null）

# 创建表时创建索引
CREATE TABLE 表名(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    index 索引名 (name)
)
# 或者
CREATE TABLE 表名(
    nid int not null auto_increment,
    name varchar(32) not null,
    primary key(ni1),
    index 索引名 (name)
)
 
# 创建索引
ALTER TABLE 表名 add primary key(列名);
 
# 删除索引
ALTER TABLE 表名 DROP primary key;
ALTER TABLE 表名 modify 列名 int, DROP primary key;

4、组合索引(也叫复合索引、联合索引)，即一个索引包含多个列

组合索引是将n个列组合成一个索引，其应用场景为：频繁的同时使用n列来进行查询，如：WHERE n1=lp AND n2=666。

CREATE TABLE 表名(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text
)
# 创建索引
CREATE INDEX 索引名 ON 表名(name,email);
 
# 如上创建组合索引之后，查询：
name AND email -- 使用索引
name           -- 使用索引
email          -- 不使用索引
 
# 注意：对于同时搜索n个条件时，组合索引的性能好于多个单一索引合并。

那么何时需要使用联合索引呢？在讨论这个问题之前，先来看一下联合索引内部的结果。从本质上来说，联合索引就是一棵 B+Tree，不同的是联合索引的键值得数量不是1，而是>=2。接着来讨论两个整型列组成的联合索引，假定两个键值得名称分别为a、b如图

可以看到这与我们之前看到的单个键的 B+Tree 并没有什么不同，键值都是排序的，通过叶子结点可以逻辑上顺序地读出所有数据，就上面的例子来说，即（1,1），（1,2），（2,1），（2,4），（3,1），（3,2），数据按（a,b）的顺序进行了存放。

因此，对于查询 SELECT * FROM TABLE WHERE a=xxx AND b=xxx, 显然是可以使用(a,b) 这个联合索引的，对于单个列a的查询 SELECT * FROM TABLE WHERE a=xxx，也是可以使用（a,b）这个索引的。

但对于b列的查询 SELECT * FROM TABLE WHERE b=xxx，则不可以使用（a,b）索引，其实不难发现原因，叶子节点上b的值为1、2、1、4、1、2显然不是排序的，因此对于b列的查询使用不到(a,b) 索引。

联合索引的好处是在第一个键相同的情况下，已经对第二个键进行了排序处理，例如在很多情况下应用程序都需要查询某个用户的购物情况，并按照时间进行排序，最后取出最近三次的购买记录，这时使用联合索引可以帮我们避免多一次的排序操作，因为索引本身在叶子节点已经排序了，如下

#===========准备表==============
CREATE TABLE buy_log(
    userid int unsigned not null,
    buy_date date
);
 
INSERT INTO buy_log VALUES
(1,'2009-01-01'),
(2,'2009-01-01'),
(3,'2009-01-01'),
(1,'2009-02-01'),
(3,'2009-02-01'),
(1,'2009-03-01'),
(1,'2009-04-01');
 
ALTER TABLE buy_log ADD KEY(userid);
ALTER TABLE buy_log ADD KEY(userid,buy_date);
 
#===========验证==============
mysql> SHOW CREATE TABLE buy_log;
| buy_log | CREATE TABLE `buy_log` (
  `userid` int(10) unsigned NOT NULL,
  `buy_date` date DEFAULT NULL,
  KEY `userid` (`userid`),
  KEY `userid_2` (`userid`,`buy_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
 
# 可以看到 possible_keys 在这里有两个索引可以用，分别是单个索引 userid 与联合索引 userid_2，但是优化器最终选择了使用的 key 是 userid 因为该索引的叶子节点包含单个键值，因为理论上一个页能存放的记录应该更多
mysql> EXPLAIN SELECT * FROM buy_log WHERE userid=2;
+----+-------------+---------+------+-----------------+--------+---------+-------+------+-------+
| id | select_type | table   | type | possible_keys   | key    | key_len | ref   | rows | Extra |
+----+-------------+---------+------+-----------------+--------+---------+-------+------+-------+
|  1 | SIMPLE      | buy_log | ref  | userid,userid_2 | userid | 4       | const |    1 |       |
+----+-------------+---------+------+-----------------+--------+---------+-------+------+-------+
1 row in set (0.00 sec)
 
# 接着假定要取出 userid 为1的最近3次的购买记录，用的就是联合索引 userid_2 了，因为在这个索引中，在 userid=1 的情况下，buy_date 都已经排序好了
mysql> EXPLAIN SELECT * FROM buy_log WHERE userid=1 ORDER BY buy_date DESC LIMIT 3;
+--+-----------+-------+----+---------------+--------+-------+-----+----+------------------------+
|id|select_type|table  |type|possible_keys  | key    |key_len|ref  |rows| Extra                  |
+--+-----------+-------+----+---------------+--------+-------+-----+----+------------------------+
| 1|SIMPLE     |buy_log|ref |userid,userid_2|userid_2| 4     |const|  4 |Using where; Using index|
+--+-----------+-------+----+---------------+--------+-------+-----+----+------------------------+
1 row in set (0.00 sec)
 
# ps：如果 Extra 的排序显示是Using filesort，则意味着在查出数据后需要二次排序(如下查询语句，没有先用 where userid=3 先定位范围，于是即便命中索引也没用，需要二次排序)
mysql> EXPLAIN SELECT * FROM buy_log ORDER BY buy_date DESC LIMIT 3;
+--+-----------+-------+-----+-------------+--------+-------+----+----+---------------------------+
|id|select_type| table |type |possible_keys|key     |key_len|ref |rows|Extra                      |
+--+-----------+-------+-----+-------------+--------+-------+----+----+---------------------------+
| 1|SIMPLE     |buy_log|index| NULL        |userid_2| 8     |NULL|  7 |Using index; Using filesort|
+--+-----------+-------+-----+-------------+--------+-------+----+----+---------------------------+
 
 
# 对于联合索引（a,b）,下述语句可以直接使用该索引，无需二次排序
SELECT * FROM TABLE WHERE a=xxx ORDER BY b;
 
# 假设表 tb1 有字段 (id int, a int, b int, c int, d varchar)，有联合索引(a,b,c)，主键索引id，唯一索引d，记录110条
SELECT * FROM tb1 WHERE a=xxx;                 -- 可以使用索引
SELECT * FROM tb1 WHERE a=xxx ORDER BY b;     -- 可以使用索引
SELECT * FROM tb1 WHERE a=xxx AND b=xxx ORDER BY c;   -- 可以使用索引
SELECT * FROM tb1 WHERE a=xxx AND c=xxx AND b=xxx;    -- 可以使用索引

SELECT * FROM tb1 WHERE c=xxx AND b=xxx;      -- 不能使用索引，因为索引的第一个值是 a,在使用索引树进行查找的没事，没有办法利用 a 进行比较，所以不知道找哪一个子树，所以没有办法利用索引。

SELECT * FROM tb1 WHERE a=xxx AND c=xxx;      -- 可以使用索引，但是需要回表，5.6之前，只利用索引a，把找到的符合索引a的数据，全部进行回表读取，然后再判断并返回满足条件的数据，5.6之后，直接会找到满足条件的对应的主键记录，然后再回表，用到了一个叫做索引下推的东西：直接使用组合索引判断符合查询条件的数据，然后再回表取数据
SELECT * FROM tb1 WHERE a>xxx AND b=xxx;      -- 同上，5.6 之前，只能利用索引a，范围索引会导致等值索引失效，5.6 之后，直接会找到满足条件的对应的主键记录，然后再回表，也是用了一个叫做索引下推的东西。


SELECT * FROM tb1 WHERE a=xxx ORDER BY c;     -- 可以利用索引，但是不能通过索引直接得到结果，还需要自己执行一次 filesort 操作

SELECT * FROM tb1 WHERE a>1;                  -- 不能使用索引，因为范围太大，需要回表次数太多，还不如直接把主键索引的数据加载到内存中再排序效率高。
SELECT * FROM tb1 order a,b,c;                -- 不能使用索引，因为范围太大，需要回表次数太多，还不如直接把主键索引的数据加载到内存中再排序效率高。
SELECT * FROM tb1 WHERE a>100;                -- 可以使用索引，因为符合条件的记录较少，回表次数较少，所以写 WHERE 条件的时候，尽量把条件写的更精确。
SELECT a FROM tb1 WHERE a>1;                  -- 可以使用索引，因为索引中包含a字段，不需要回表，但是查询返回的数据的结果是 按照 a字段 排序的结果，不是按照 主键 排序的结果。
SELECT a,id FROM tb1 WHERE a>1;               -- 可以使用索引，因为索引中包含a字段，而且包含id字段，不需要回表，但是查询返回的数据的结果是 按照 a字段 排序的结果，不是按照 主键 排序的结果。

# 类型不一样
SELECT * FROM tb1 WHERE a=1                   -- 可以使用索引
SELECT * FROM tb1 WHERE a='1'                 -- 可以使用索引，会先将 '1' 转化为 1
SELECT * FROM tb1 WHERE d='1'                 -- 可以使用索引
SELECT * FROM tb1 WHERE d=1                   -- 不可以使用索引

# 注：mysql 在查找时会自动进行类型转化
#   如果字段类型为数字，WHERE 条件判断类型为字符，mysql 会自动将判断的条件转化成数字，如 a='1' 会转化成 a=1，a='t' 会转化成 a=0，所以会用索引。转化规则如下：    
#       1.  字符开头的一律为 0。
#       2.  数字开头的，直接截取到第一个不是字符的位置。
#   如果字段类型为字符，WHERE 条件判断类型为数字，mysql 会自动将字段转化为数字，然后再判断，如 d='1' 会转化成 d=1，因为要转化，所以不会用索引。

7、索引的又一种分类

索引合并：使用多个单列索引组合搜索。
覆盖索引：SELECT 的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖。

上述两种索引类型不是创建的索引类型，是sql执行时的采用的类型。

1、覆盖索引（covering index，或称索引覆盖）

InnoDB 存储引擎支持覆盖索引，即从辅助索引中就可以得到查询记录，而不需要查询聚集索引中的记录。

使用覆盖索引的一个好处是：辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作。

注意：覆盖索引技术最早是在 InnoDB Plugin 中完成并实现，这意味着对于 InnoDB 版本小于1.0的，或者 MySQL 5.0 以下的，InnoDB 存储引擎不支持覆盖索引特性。

对于 InnoDB 的辅助索引而言，由于其包含了主键信息，因此其叶子节点存放的数据为（primary key1，priamey key2，…, key1，key2，…）。例如

# id字段有索引，name字段没有索引，该sql命中了索引，但未覆盖，需要去聚集索引中再查找详细信息。
SELECT age FROM s1 WHERE id=123 AND name = 'egon'; 
 
 
# 最好的情况是，索引字段覆盖了所有，那全程通过索引来加速查询以及获取结果就ok了，在辅助索引中就找到了全部信息，Using index 代表覆盖索引
EXPLAIN SELECT id FROM s1 WHERE id=1000; 
+--+-----------+-----+----------+----+-------------+------+-------+-------+------+----------+-----+
|id|select_type|table|partitions|type|possible_keys| key  |key_len| ref |rows|filtered| Extra     |
+--+-----------+-----+----------+----+--------------------+-------+-------+------+----------+-----+
| 1| SIMPLE    | s1  | NULL     | ref| idx_id      |idx_id|  4    |const| 1  | 100.00 |Using index|
+--+-----------+-----+----------+----+-------------+------+-------+-----+----+--------+-----------+
1 row in set, 1 warning (0.03 sec)

覆盖索引的另外一个好处是对某些统计问题而言的。

mysql> EXPLAIN SELECT COUNT(*) FROM buy_log;
+--+-----------+-------+-----+-------------+------+-------+----+----+-----------+
|id|select_type|table  | type|possible_keys|key   |key_len|ref |rows|Extra      |
+--+-----------+-------+-----+-------------+------+-------+----+----+-----------+
| 1| SIMPLE    |buy_log|index| NULL        |userid| 4     |NULL|  7 |Using index|
+--+-----------+-------+-----+-------------+------+-------+----+----+-----------+
1 row in set (0.00 sec)

InnoDB 并不一定选择查询聚集索引来进行统计。由于 buy_log 表有辅助索引，而辅助索引远小于聚集索引，选择辅助索引可以减少IO操作，故优化器的选择如上 key 为 userid 辅助索引。

对于（a,b）形式的联合索引，一般是不可以选择b中所谓的查询条件。但如果是统计操作，并且是覆盖索引，则优化器还是会选择使用该索引，如下

# 联合索引userid_2（userid,buy_date）,一般情况，我们按照buy_date是无法使用该索引的，但特殊情况下：查询语句是统计操作，且是覆盖索引，则按照buy_date当做查询条件时，也可以使用该联合索引
mysql> EXPLAIN SELECT COUNT(*) FROM buy_log WHERE buy_date >= '2011-01-01' AND buy_date < '2011-02-01';
+--+-----------+-------+-----+-------------+--------+-------+----+----+------------------------+
|id|select_type| table |type |possible_keys| key    |key_len|ref |rows|Extra                   |
+--+-----------+-------+-----+-------------+--------+-------+----+----+------------------------+
| 1| SIMPLE    |buy_log|index| NULL        |userid_2| 8     |NULL|  7 |Using where; Using index|
+--+-----------+-------+-----+-------------+--------+-------+----+----+------------------------+
1 row in set (0.00 sec)

2、索引合并

mysql> EXPLAIN SELECT COUNT(email) FROM index_t WHERE id=1000000 OR email='eva100000@oldboy';
+--+-----------+------+--------------+--------------------------------+---------------+--------+-----+----+-----------------------------------------+
| id | select_type| table | type        | possible_keys                  | key           | key_len| ref|rows | Extra                                                           |
+--+-----------+------+--------------+--------------------------------+---------------+--------+-----+----+-----------------------------------------+
|  1 | SIMPLE     |index_t| index_merge | PRIMARY,email,ind_id,ind_email | PRIMARY,email | 4,51   |NULL|  2  |Using union(PRIMARY,email); Using where |
+--+-----------+------+--------------+--------------------------------+---------------+--------+-----+----+-----------------------------------------+
1 row in set (0.01 sec)

8、AND/OR

AND/OR 的逻辑

条件1 AND 条件2：所有条件都成立才算成立，但凡要有一个条件不成立则最终结果不成立。
条件1 OR 条件2：只要有一个条件成立则最终结果就成立。

AND 的工作原理

条件：a = 10 AND b = 'xxx' AND c > 3 AND d =4

索引：联合索引(d,a,b,c)

工作原理：对于连续多个 AND，MySQL 会按照联合索引的顺序，即按照 d—>a->b->c 的顺序查找。

OR 的工作原理

条件：a = 10 OR b = 'xxx' OR c > 3 OR d =4

索引：联合索引(d,a,b,c)

工作原理：对于连续多个OR，MySQL 会按照条件的顺序，从左到右依次判断，即 a->b->c->d

AND 与 OR 优先级

在where中可以包含任意数目的 AND 和 OR 操作符，在没有任何其他符号的时候，例如括号，SQL 会首先执行 AND 条件，然后才执行 OR 语句，如：

SELECT * FROM TABLE FROM id=1 OR id=2 AND price>=10; 这条语句默认执行的是 id=2 并且 price>=10 的，或者是 id=1。

如果加上括号：

SELECT * FROM TABLE FROM (id=1 OR id=2) AND price>=10; 则这条语句执行的是 id=1 或 id=2 并且 price>=10。

AND 和 OR 利用索引

WHERE 语句里面如果带有 OR 条件, MyISAM 表能用到索引，InnoDB 不行。
必须所有的 OR 条件都必须是独立索引。

9、正确使用索引

由于 InnoDB 是索引组织表，所以在查找时肯定会走索引，所以主键索引不在讨论范围，本次讨论的主要是辅助索引。

另外，对于覆盖索引，查找到消息之后，不需要回表，所以如果有对应的索引，也一定会用到，所以也不在讨论范围。

表中共 80000 条数据。我们在添加索引时，必须遵循以下问题：

1、范围问题，或者说条件不明确，条件中出现这些符号或关键字：`>、>=、<、<=、!= 、BETWEEN...AND...、LIKE`

当范围很大的时候，不利用索引，当范围很小的时候，会利用索引

大于号、小于号

-- 范围很大时，全表扫描，全表扫描比利用索引还快
MariaDB [lp]> EXPLAIN SELECT * FROM t WHERE f>10000;
+------+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+------+-------------+-------+------+---------------+------+---------+------+-------+-------------+
|    1 | SIMPLE      | t     | ALL  | weiyi_f       | NULL | NULL    | NULL | 80503 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)

-- 范围不大时，会利用索引，然后回表获取数据
MariaDB [lp]> EXPLAIN SELECT * FROM t WHERE f>70000;
+------+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
| id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                 |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
|    1 | SIMPLE      | t     | range | weiyi_f       | weiyi_f | 5       | NULL | 9998 | Using index condition |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
1 row in set (0.01 sec)

不等于

-- 不等于，范围很大，全表扫描比利用索引还快
MariaDB [lp]> EXPLAIN SELECT * FROM t WHERE f!=70000;
+------+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+------+-------------+-------+------+---------------+------+---------+------+-------+-------------+
|    1 | SIMPLE      | t     | ALL  | weiyi_f       | NULL | NULL    | NULL | 80503 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+-------+-------------+

BETWEEN…AND…

-- 范围不大时，会利用索引，然后回表获取数据
MariaDB [lp]> EXPLAIN SELECT * FROM t WHERE f BETWEEN 10 AND 100;
+------+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
| id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                 |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
|    1 | SIMPLE      | t     | range | weiyi_f       | weiyi_f | 5       | NULL |   91 | Using index condition |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

-- 范围很大时，全表扫描，全表扫描比利用索引还快
MariaDB [lp]> EXPLAIN SELECT * FROM t WHERE f BETWEEN 10 AND 40000;
+------+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+------+-------------+-------+------+---------------+------+---------+------+-------+-------------+
|    1 | SIMPLE      | t     | ALL  | weiyi_f       | NULL | NULL    | NULL | 80503 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)

like

-- % 在后面可以利用索引。
MariaDB [lp]> EXPLAIN SELECT * FROM t WHERE d like 'd%';
+------+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
| id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                 |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
|    1 | SIMPLE      | t     | range | weiyi_d       | weiyi_d | 768     | NULL | 4950 | Using index condition |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

-- % 在前面不可以利用索引。
MariaDB [lp]> EXPLAIN SELECT * FROM t WHERE d like '%d';
+------+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+------+-------------+-------+------+---------------+------+---------+------+-------+-------------+
|    1 | SIMPLE      | t     | ALL  | NULL          | NULL | NULL    | NULL | 79752 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)

2 索引列不能在条件中参与计算，保持列“干净”

比如 reverse(d)='55cf62' 就不能使用到索引，原因很简单， B+Tree 中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成 d='26fc55'

MariaDB [lp]> EXPLAIN SELECT * FROM t WHERE reverse(d)='55cf62';
+------+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+------+-------------+-------+------+---------------+------+---------+------+-------+-------------+
|    1 | SIMPLE      | t     | ALL  | NULL          | NULL | NULL    | NULL | 79752 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)

MariaDB [lp]> EXPLAIN SELECT * FROM t WHERE d='26fc55';
+------+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
| id   | select_type | table | type | possible_keys | key     | key_len | ref   | rows | Extra                 |
+------+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
|    1 | SIMPLE      | t     | ref  | weiyi_d       | weiyi_d | 768     | const |    1 | Using index condition |
+------+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

3、类型不一致

列是字符串类型，传入条件是必须用引号引起来，否则不能利用索引。
列是 int 类型的可以利用索引，MySQL 会自动把字符串转化为数字。

-- d 的类型为字符串，利用不到索引。
MariaDB [lp]> EXPLAIN SELECT * FROM t WHERE d=22;
+------+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+------+-------------+-------+------+---------------+------+---------+------+-------+-------------+
|    1 | SIMPLE      | t     | ALL  | weiyi_d       | NULL | NULL    | NULL | 79752 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)

-- a 的类型为 int
MariaDB [lp]> EXPLAIN SELECT * FROM t WHERE a=22;
+------+-------------+-------+------+---------------+----------+---------+-------+------+-------+
| id   | select_type | table | type | possible_keys | key      | key_len | ref   | rows | Extra |
+------+-------------+-------+------+---------------+----------+---------+-------+------+-------+
|    1 | SIMPLE      | t     | ref  | zuhe_abc      | zuhe_abc | 5       | const |    1 |       |
+------+-------------+-------+------+---------------+----------+---------+-------+------+-------+
1 row in set (0.00 sec)

MariaDB [lp]> EXPLAIN SELECT * FROM t WHERE a='22';
+------+-------------+-------+------+---------------+----------+---------+-------+------+-------+
| id   | select_type | table | type | possible_keys | key      | key_len | ref   | rows | Extra |
+------+-------------+-------+------+---------------+----------+---------+-------+------+-------+
|    1 | SIMPLE      | t     | ref  | zuhe_abc      | zuhe_abc | 5       | const |    1 |       |
+------+-------------+-------+------+---------------+----------+---------+-------+------+-------+

4、排序条件为索引，则 SELECT 字段必须也是索引字段，否则无法命中

当根据索引排序时候，SELECT 查询的字段如果不只是索引，则速度仍然很慢，因为需要回表，不如直接在内存中排序。

MariaDB [lp]> EXPLAIN SELECT * FROM t ORDER BY a;
+------+-------------+-------+------+---------------+------+---------+------+-------+----------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra          |
+------+-------------+-------+------+---------------+------+---------+------+-------+----------------+
|    1 | SIMPLE      | t     | ALL  | NULL          | NULL | NULL    | NULL | 79752 | Using filesort |
+------+-------------+-------+------+---------------+------+---------+------+-------+----------------+
1 row in set (0.00 sec)

MariaDB [lp]> EXPLAIN SELECT a FROM t ORDER BY a;
+------+-------------+-------+-------+---------------+----------+---------+------+-------+-------------+
| id   | select_type | table | type  | possible_keys | key      | key_len | ref  | rows  | Extra       |
+------+-------------+-------+-------+---------------+----------+---------+------+-------+-------------+
|    1 | SIMPLE      | t     | index | NULL          | zuhe_abc | 15      | NULL | 79752 | Using index |
+------+-------------+-------+-------+---------------+----------+---------+------+-------+-------------+
1 row in set (0.00 sec)

5、最左前缀匹配原则，非常重要的原则

对于组合索引 MySQL 会一直向右匹配直到遇到范围查询(>、<、BETWEEN、LIKE)就停止匹配(指的是范围大了，有索引速度也慢)

比如 a=1 AND b=2 AND c>3 AND d=4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

10、其他注意事项

避免使用select *
使用 COUNT(*)
创建表时尽量使用 char 代替 varchar
表的字段顺序固定长度的字段优先
组合索引代替多个单列索引（由于mysql中每次只能使用一个索引，所以经常使用多个条件查询时更适合使用组合索引）
尽量使用短索引
使用连接（JOIN）来代替子查询(Sub-Queries)
连表时注意条件类型需一致
优先为搜索条件的字段创建索引，比如 SELECT * FROM s1 WHERE id = 333; 就需要为id加上索引。
在表中已经有大量数据的情况下，建索引会很慢，且占用硬盘空间，建完后查询速度加快，比如 CREATE INDEX idx ON s1(id); 会扫描表中所有的数据，然后以id为数据项，创建索引结构，存放于硬盘的表中。建完以后，再查询就会很快了。
InnoDB 表的索引会存放于 s1.ibd 文件中，而 MySAM 表的索引则会有单独的索引文件 table1.MYI。
MySAM 索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在 InnoDB 中，表数据文件本身就是按照 B+Tree 组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此 InnoDB 表数据文件本身就是主索引。
尽量选择区分度高的列作为索引。区分度的公式是COUNT(distinct col)/COUNT(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录
回忆 B+Tree 的结构，查询的速度与树的高度成反比，要想将树的高低控制的很低，需要保证：在某一层内数据项均是按照从左到右，从小到大的顺序依次排开，即 左1<左2<左3<...
而对于区分度低的字段，无法找到大小关系，因为值都是相等的，毫无疑问，还想要用 B+Tree 存放这些等值的数据，只能增加树的高度，字段的区分度越低，则树的高度越高。极端的情况，索引字段的值都一样，那么 B+Tree 几乎成了一根棍

Reference

https://blog.csdn.net/weixin_38754349/article/details/123650107

MySQL

#MySQL

MySQL 索引

https://flepeng.github.io/041-MySQL-41-底层原理-MySQL-索引/

作者

Lepeng

发布于

2020年8月8日

许可协议

MySQL 日志之 redo log 上一篇

MySQL 索引为什么使用B+Tree 下一篇

MySQL 索引

1、为什么需要索引

2、什么是索引

索引的优缺点

不同的存储引擎支持的索引类型

3、Hash 索引

Memory 原生的 Hash索引

InnoDB 自适应哈希索引

Hash 与 B+Tree 索引的优缺点

4、B+Tree MySQL 索引的实现

MyISAM 索引实现

InnoDB 索引实现

InnoDB 主键索引的 B+Tree 高度为多高呢?

总结一下

5、B+Tree 索引中的聚集索引与辅助索引

聚集索引

回表查询

辅助索引

非聚簇索引一定回表查询吗(覆盖索引)?

聚集索引和非聚集索引的联系与区别

6、索引的常见分类

1、普通索引

2、唯一索引

3、主键索引

4、组合索引(也叫复合索引、联合索引)，即一个索引包含多个列

7、索引的又一种分类

1、覆盖索引（covering index，或称索引覆盖）

2、索引合并

8、AND/OR

AND/OR 的逻辑

AND 的工作原理

OR 的工作原理

AND 与 OR 优先级

AND 和 OR 利用索引

9、正确使用索引

1、范围问题，或者说条件不明确，条件中出现这些符号或关键字：>、>=、<、<=、!= 、BETWEEN...AND...、LIKE

2 索引列不能在条件中参与计算，保持列“干净”

3、类型不一致

4、排序条件为索引，则 SELECT 字段必须也是索引字段，否则无法命中

5、最左前缀匹配原则，非常重要的原则

10、其他注意事项

Reference

1、范围问题，或者说条件不明确，条件中出现这些符号或关键字：`>、>=、<、<=、!= 、BETWEEN...AND...、LIKE`