让程序飞起来系列-mysql 索引基本用法

mysql 索引基本用法

本文不讨论全文索引，出现类似需求直接换“ElasticSearch”

简单了解B+树，太难不多讲，也讲不明白
关于B+树的文章

为什么

接口上生产，性能不达标有很多因素导致，最容易被当嫌疑犯的就是数据库。因为用法不过关，导致了数据库背锅。甚至有同学了为了让接口达标，还没没怎么优化数据库就过早的开始加入Redis，Memcache等缓存。

值得庆幸的是尽管开源，MySQL还是优秀到它姥姥家的一款数据库。

先从理论上手。

1. 先了解索引大致分类

1. 单列索引

   • 主键索引
   • 普通索引

2. 组合索引

2. 主键索引（聚集索引clustered index， primary key）

1. 索引顺序与物理顺序相同，意味着这个索引效率最高

2. 索引B+树叶子结点存储了物理行。意味着找到主键，就找到了行记录

3. 每个叶子结点都可以找到相邻的叶子。思考下：cursor.next在底层的实现

4. 一张表只能有一个主键

   a. 如果表定义了PK，则PK就是聚集索引；

   b. 如果表没有定义PK，则第一个not NULL unique列是聚集索引；

   c. 没有定义以上，InnoDB会创建一个隐藏的row-id作为聚集索引；

3. 普通索引（secondary index）

1. 普通索引也叫二级索引，为什么叫二级索引？？？这个要特别注意！

2. 普通索引也是B+树

3. 与主键索引的主要区别在于叶子结点只包含了主键值，还有一个标签，用来告诉存储引擎在哪里可以找到这行数据。

4. 普通索引被命中后，需要回表，用主键ID去获取行数据

5. 因为有回表的动作，所以才叫，二级索引

6. 综述：普通索引不能与主键的性能相提并论！

4. 组合索引（union index）

1. 联合几个字段一起索引

2. 创建组合索引示例

   alter table user add index login_index (user,password)
   

3. B+树体现：叶子结点存储的是第一个字段！在叶子结点额外存储了其它字段！也即是说，

   select user, password, nickname from user where user='somkelee'
   

   这种用法会造成回表,去查询nickname，虽然不影响索引效果，但会“回表”。

   select user, password, session from user where session = 'xxxxx' and user = 'xxxx'
   

   这种情况下，索引不生效！

4. 正确用法

   select user,password from user where user = 'smokelee' 
   后面加上 and password='xxx' 也生效
   

5. 总结：联合索引，务必让第一个字段位于where子句的第一个，索引才会生效！select子句字段不能包含联合索引之外的字段，否则会引发“回表”