mysql索引 - w1234

1..什么是索引？

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

2.为什么要有索引呢？

索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能
非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。
索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。
索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。

3.索引类型

在MySQL中，通常我们所指的索引类型，有以下几种：

常规索引

常规索引，也叫普通索引（index或key），它可以常规地提高查询效率。一张数据表中可以有多个常规索引。常规索引是使用最普遍的索引类型，如果没有明确指明索引的类型，我们所说的索引都是指常规索引。
主键索引

主键索引（Primary Key），也简称主键。它可以提高查询效率，并提供唯一性约束。一张表中只能有一个主键。被标志为自动增长的字段一定是主键，但主键不一定是自动增长。一般把主键定义在无意义的字段上（如：编号），主键的数据类型最好是数值。
唯一索引

唯一索引（Unique Key），可以提高查询效率，并提供唯一性约束。一张表中可以有多个唯一索引。
全文索引

全文索引（Full Text），可以提高全文搜索的查询效率，一般使用Sphinx替代。但Sphinx不支持中文检索，Coreseek是支持中文的全文检索引擎，也称作具有中文分词功能的Sphinx。实际项目中，我们用到的是Coreseek。
外键索引

外键索引（Foreign Key），简称外键，它可以提高查询效率，外键会自动和对应的其他表的主键关联。外键的主要作用是保证记录的一致性和完整性。

注意：只有InnoDB存储引擎的表才支持外键。外键字段如果没有指定索引名称，会自动生成。如果要删除父表（如分类表）中的记录，必须先删除子表（带外键的表，如文章表）中的相应记录，否则会出错。创建表的时候，可以给字段设置外键，如 foreign key(cate_id) references cms_cate(id)，由于外键的效率并不是很好，因此并不推荐使用外键，但我们要使用外键的思想来保证数据的一致性和完整性。

4.创建与删除索引

创建索引
    -在创建表时就创建（需要注意的几点）
    create table s1(
    id int ,#可以在这加primary key
    #id int index #不可以这样加索引，因为index只是索引，没有约束一说，
    #不能像主键，还有唯一约束一样，在定义字段的时候加索引
    name char(20),
    age int,
    email varchar(30)
    #primary key(id) #也可以在这加
    index(id) #可以这样加
    );
    -在创建表后在创建
    create index name on s1(name); #添加普通索引
    create unique age on s1(age);添加唯一索引
    alter table s1 add primary key(id); #添加住建索引，也就是给id字段增加一个主键约束
    create index name on s1(id,name); #添加普通联合索引
删除索引
    drop index id on s1;
    drop index name on s1; #删除普通索引
    drop index age on s1; #删除唯一索引，就和普通索引一样，不用在index前加unique来删，直接就可以删了
    alter table s1 drop primary key; #删除主键(因为它添加的时候是按照alter来增加的，那么我们也用alter来删)

5.索引的选择

常见的数据结构有：

https://www.cs.usfca.edu/~galles/visualization/Algorithms.html 这个网页可以帮助理解各种树

二叉树
- 如果次数有一万个数为（1,2,3....)，那么二叉树会有一万层，更像一个链表结构。这种结构很显然是不便于快速定位数据的。因为如果要查询10000那么需要10000次io才能检索到索引；
红黑树
- 红黑树是一种平衡二叉树，较于二叉树会减少层次数量，但是随着数据量增加，红黑树的层级也会增加；
Hash表
- Hash表只能针对于单数据查询，如select * from user where id=888；这种查询会先hash(888)算出索引地址，然后拿到数据的地址。可以极快的查询到数据；
- 但是如果是范围查询，那么hash便无法支持；
以上，所以MySQL索引最好保证树的高度（1-4层），以此减少io的次数（1-4次），来达到快速查找的目的；
B树
- 比较矮胖，即缩小高度，将每层级的数据增多。
- 每个节点都有data，这样查询的时候一次性获取一个层级的节点放到内存，在内存中可以快速查找；
- 因为获取一个层级节点到内存是有大小限制的，即如果节点过大，那么获取的数量会减少；
- innodb_page_size的默认值是16kb，即一个层级的节点最好16kb.

MySQL最终选择的是B+树

B+树（B树的变种）
- 较于B树，B+树的非叶子节点不存储data。这样一次io可以获取更多的节点； - 较于B树，B+树非叶子节点会冗余索引，即每层会有重复的，这样做事为了查询更快； - 较于B树，B+树叶子节点中有指针连接，这样是为了范围查询的的时候可以直接往下查询，而不用从第一层索引开始重头开始；

假如一个节点是16KB，且主键是Bigint（占用内存8B) ，主键间存储的内存地址假设占用内存B，那么一层可以存储1170个节点；依次类推，这样的高度为3的B+树大概可以存储2000W数据；

MySQL 索引匹配原则

在我们了解到MySQL底层数据结构B+树后，这些规则其实很好理解；

索引匹配原则如下：

等值匹配
最左侧列匹配
最左前缀匹配
范围查找
等值匹配+范围查找

假设现在有一张表student_score，表中有5个字段，id，class, name，course，score，其中id为主键，有四条数据

id：1 class：1班 name：张三 course：数学 score 90
id：2 class：1班 name：张三 course：语文 score 50
id：3 class：3班 name：李四 course：数学 score 50
id：4 class：3班 name：李四 course：语文 score 20

并且我们以class、name、course建立了联合索引；

等值匹配

假设现在有一条sql如下

select * from student_score where class = '1班' and name = '张三' and course = '数学'

这条语句中就会用到所有的索引字段，首先找class，然后查找name，最后查找course

最左侧列匹配

假设有一条sql如下

select * from student_score where course = '数学'

该SQL语句就不会用到索引，因为B+树中，索引的使用是从左到右的，不能跳过左边的，直接查找右边的，我们可以用explain来看一下

确定这里是没有用到索引的，这条SQL可以改成

select * from student_score where class = '1班' and name = '张三'

这样就可以用到索引了

因此在建立索引的时候，我们需要考虑表中的字段，到底哪些字段是最常被用于查找的

最左前缀匹配

这条原则适用于模糊匹配的时候，也就是需要用到like的时候，假设有一条SQL如下

select * from student_score where class like '1%'

这里是可以用到索引的，印在B+索引树中，数据都是按照字段来排序的，比如这里有联合索引 key(class、name、course) ，那么数据会按照class排序

但是如果你把SQL写成了下面这样，就用不到索引了，因为匹配不到最左前缀

select * from student_score where class like '%班'

范围查找

我们可以通过> 、 <这种范围比较来查找数据

select * from student_score where class > '1班' and class < '3班'

通过explain，可以看到，这条sql是用到了索引的但是如果你把sql改成下面这样的，就不会用到索引了

select * from student_score where name > '张三'

因为该联合索引的B+树中只能根据class字段来进行范围查找，也就是联合索引中最左侧的字段

等值匹配+范围查找

假设有一条SQL如下

select * from student_score where class = '1班' and name > '张三' and course > '数学'

通过explain分析后，可以看到，这里也会用到索引

posted on 2021-12-05 13:20 w1234 阅读(72) 评论(0) 收藏举报