mysql - 索引种类及深入理解

1.聚集索引

在MySQL中一般用id作为主键索引，并且利用整行数据来构造B+树，在mysql中一个TABLE只能一个聚集索引

如果在table中没有建键，会使用unique index来建聚集索引，如果unique index也没有则会用rowID来建聚集索引

2.辅助索引

在我们实际业务场景中主键不能满足实际的数据查询，会以其他列作为查询条件，所以也建索引，我们称之为辅助索引

但是mysql在在建辅助索引和聚集索引最大的差别不会用整行数据保存在索引中，只会用指定列数据加上主键列保存在索引中，并生成B+树

所以mysql在我们利用辅助索引查询数据先从索引中找到满足条件的数据，在数据中找到主键，利用主键从聚集索引中定位到table中具体记录，我们称之为回表

从回表过程中我们可以看出如果利辅助索引我们用到两个B+树。

另外还有索引覆盖，就是避开了回表，比如以下sql

select userid,name,age from user where userid='l0001'

假设user表我们建了一个联合索引(userid,name,age)，根据上面描述mysql会利用userid，name，age来生成B+树，我们根据userid在B+树里查找时就能得到userid,name,age数据，无需定位到表中具体数据行，但是如果用以下sql查询就需要回表，

select * from user where userid='l0001'

因为我们查数据时需要查出表中所有列，在联合索中没有。

从以上可以看出我们在sql优化时利用索引覆盖有利查询速度优化

3.自动哈希索引

是mysql对热数据的一种处理机制，主要适合等于的条件之下，我们人为无法干涉，我们可以通过show engine innodb status 命令来查看，可以通过innodb_adaptive_hash_index这个参数来开启或关闭。

=====================================

2022-10-24 17:41:11 0x4b60 INNODB MONITOR OUTPUT

=====================================

Per second averages calculated from the last 54 seconds

-----------------

BACKGROUND THREAD

-----------------

srv_master_thread loops: 3 srv_active, 0 srv_shutdown, 19107 srv_idle

srv_master_thread log flush and writes: 0

----------

SEMAPHORES

----------

OS WAIT ARRAY INFO: reservation count 0

OS WAIT ARRAY INFO: signal count 0

RW-shared spins 0, rounds 0, OS waits 0

RW-excl spins 0, rounds 0, OS waits 0

RW-sx spins 0, rounds 0, OS waits 0

Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx

------------

TRANSACTIONS

------------

Trx id counter 338952

Purge done for trx's n:o < 338950 undo n:o < 0 state: running but idle

History list length 3

LIST OF TRANSACTIONS FOR EACH SESSION:

---TRANSACTION 283552691751576, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 283552691750704, 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

956 OS file reads, 205 OS file writes, 33 OS fsyncs

0.11 reads/s, 16384 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s

-------------------------------------

INSERT BUFFER AND ADAPTIVE HASH INDEX

-------------------------------------

Ibuf: size 1, free list len 0, seg size 2, 0 merges

merged operations:

insert 0, delete mark 0, delete 0

discarded operations:

insert 0, delete mark 0, delete 0

Hash table size 2267, node heap has 0 buffer(s)

Hash table size 2267, node heap has 0 buffer(s)

Hash table size 2267, node heap has 0 buffer(s)

Hash table size 2267, node heap has 0 buffer(s)

Hash table size 2267, node heap has 0 buffer(s)

Hash table size 2267, node heap has 0 buffer(s)

Hash table size 2267, node heap has 1 buffer(s)

Hash table size 2267, node heap has 3 buffer(s)

4.52 hash searches/s, 10.20 non-hash searches/s

---

LOG

---

Log sequence number 49288380

Log buffer assigned up to 49288380

Log buffer completed up to 49288380

Log written up to 49288380

Log flushed up to 49288380

Added dirty pages up to 49288380

Pages flushed up to 49288380

Last checkpoint at 49288380

18 log i/o's done, 0.00 log i/o's/second

----------------------

BUFFER POOL AND MEMORY

----------------------

Total large memory allocated 8585216

Dictionary memory allocated 390982

Buffer pool size 512

Free buffers 246

Database pages 262

Old database pages 0

Modified db pages 0

Pending reads 0

Pending writes: LRU 0, flush list 0, single page 0

Pages made young 0, not young 0

0.00 youngs/s, 0.00 non-youngs/s

Pages read 933, created 142, written 154

0.11 reads/s, 0.00 creates/s, 0.00 writes/s

Buffer pool hit rate 997 / 1000, young-making rate 0 / 1000 not 0 / 1000

Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s

LRU len: 262, unzip_LRU len: 0

I/O sum[6]: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=7660, Main thread ID=0000000000003908 , state=sleeping

Number of rows inserted 0, updated 314, deleted 0, read 5040

0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 11.00 reads/s

----------------------------

END OF INNODB MONITOR OUTPUT

============================

4.全文检索

有点类似于ELK全文检索功能，但是在mysql中比较鸡肋不建议使用，因为mysql毕竟关系数据库，全文检索不是他的强项。

在mysql中只有聚集索引是密集型索引，其他们都稀疏型索引