浅谈Mysql索引

最新推荐文章于 2024-01-04 20:21:25 发布

噜啦l

最新推荐文章于 2024-01-04 20:21:25 发布

阅读量86

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/weixin_45337294/article/details/118902933

版权

mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

Mysql索引介绍

一、索引介绍
二、索引分类
三、索引SQL
四、索引的执行过程
五、索引底层的数据结构
六、左前缀原则
七、索引的优化
八、索引总结

一、索引介绍

索引就是一中数据结构，索引是创建在表上，是对数据库表中的一列或多列的值进行排序的一种结果，索引的好处是用来提高查询效率，避免全表查询。

二、索引分类

1.普通索引：没有任何限制条件，可以给表中的任何字符按创建索引。
2.唯一索引：使用UNIQUE修饰的字段，值是不能重复的，主键索引属于唯一索引。
3.主键索引：使用Primary key修饰的字段会自动添加主键索引。
4.单列索引：值给某一个字段生成的索引
5.多列索引：又称联合索引，是指给表中某几列创建索引。
6.全文索引：使用FullText修饰的字段可以设置为全文索引，只支持varchar、text等类型的字段。

三、索引SQL

1.创建表时可以创建的索引

create table table_name(
属性名属性类型（约束条件），
…
（unique/fulltext…） index 别名属性名
）;

mysql> create table test (
    -> id1 int,
    -> id2 int,
    -> unique index idx_id(id1)
    -> );

查看表结构

mysql> show create table test;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                         |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `id1` int(11) DEFAULT NULL,
   `id2` int(11) DEFAULT NULL,
  UNIQUE KEY `idx_id` (`id1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------+

2.在已有表上添加索引

2.1通过create语法创建

create （unique/fulltext…） index 索引名 on 表名();

mysql> create index idx_2 on test (id2);
Query OK, 0 rows affected (0.86 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table test;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `id1` int(11) DEFAULT NULL,
  `id2` int(11) DEFAULT NULL,
  UNIQUE KEY `idx_id` (`id1`),
  KEY `idx_2` (`id2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

此时有了一个普通索引idx_2。

2.2通过alter语法创建

alter table 表名 add （unique/fulltext…）index idx_all(id1,id2);

mysql> alter table test add index inx_all(id1,id2);


mysql> show create table test;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                               |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `id1` int(11) DEFAULT NULL,
  `id2` int(11) DEFAULT NULL,
  UNIQUE KEY `idx_id` (`id1`),
  KEY `idx_2` (`id2`),
  KEY `inx_all` (`id1`,`id2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

联合索引inx_all。

3.删除索引

drop index 索引名 on 表名；

mysql> drop index `inx_all` on test;

mysql> show create table test;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `id1` int(11) DEFAULT NULL,
  `id2` int(11) DEFAULT NULL,
  UNIQUE KEY `idx_id` (`id1`),
  KEY `idx_2` (`id2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

四、索引的执行过程

在我的数据库表student中，通过sid来查询name为3的属性，其表如下：

+------+--------+------+------+
| sid  | sname  | sex  | sage |
+------+--------+------+------+
|    1 | 赵雷   | 男   |   20 |
|    2 | 王八蛋 | 男   |   20 |
|    3 | 孙风   | 男   |   21 |
|    4 | 吴兰   | 女   |   18 |
|    6 | 王五   | 男   |   22 |
|    7 | 李四   | 男   |   15 |
|    8 | 林主   | 男   |   15 |
+------+--------+------+------+

首先我的sid并未创建索引

mysql> show create table student;
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                    |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `sid` bigint(20) DEFAULT NULL,
  `sname` varchar(255) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `sage` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

此时分析SQL执行过程中是否使用到索引时就要使用关键字explain加查找语句。

mysql> explain select * from student where sid=3\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
        Extra: Using where
1 row in set (0.00 sec)

通过explain来分析当前的sid=3时的数据时，可以观察到possible_keys: NULL，key: NULL，说明了当前是没有命中索引的， rows: 6全表查询。
为了优化该查询操作，为sid添加索引，再次查看。

mysql> show create table student;
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                            |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `sid` bigint(20) DEFAULT NULL,
  `sname` varchar(255) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `sage` int(11) DEFAULT NULL,
  KEY `idx_id` (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

此时在进行查找sid=3的数据

mysql> explain select * from student where sid=3\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
         type: ref
possible_keys: idx_id
          key: idx_id
      key_len: 9
          ref: const
         rows: 1
        Extra: NULL
1 row in set (0.32 sec)

可以观察到possible_keys: idx_id和 key: idx_id可得使用到了所创建的索引，并且rows: 1，可知仅仅查询一次就查询到所需的数据。

五、索引底层的数据结构

索引的实现一种是基于B树的索引结构，另一种是基于哈希表的数据结构。

了解B树的特点
1.根节点至少包含两个孩子
2.树中每个结点最多含有m个还在(m>=2)
3.除了根节点和一节点外。每个节点至少含有(m/2)个孩子（向上取整）
4.所有叶子节点位于同一层
5.假设每个非终结点中包含n个关键字信息其中：
   Ki(i=1,2,....n)为关键字，且按顺序排序
   关键字的个数n必须满足（CEIL(M/2))≤n≤m-1（ceil指向上取整）
   非叶子结点的指针P[1]P[2]...P[M]，其中K[1]指向关键字小于K[1]的子树。P[M-1]指向关键字大于K[M-1]的字数，其他P[i]指向关键字属于(K[i-1],K[i]）的子树。

B+树的特点（优于B树）
1.非叶子结点的子树指针与关键字个个数相同（相对于B树来说B+树可以存放更多的关键字）
2.非叶子结点的子树指针P[i]，指向关键字值[K[i],K[i+1]]的子树，区间左闭右开。
3.非叶子节点仅仅用来索引，数据都保存在叶子节点中，B+树中所有的检索都是从根部开始，检索到叶子结点才能结束，而且非叶子结点不存储数据的话就能存储更多关键字。
4.B+树比B树矮
5.所有叶子节点均有一个链指针指向下一个叶子节点

六、左前缀原则

在联合索引中，有个左前缀原则，指的是如果查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到

select * from student where sage=20 and sis=1;

例子：
设置索引

mysql> alter table student add index inx_all(sid,sage);

当查询时的顺序不同，即sage和sid的顺序不同时，都是可以命中索引的。

mysql> explain select * from student where sid=1 and sage=20  \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
         type: ref
possible_keys: inx_all
          key: inx_all
      key_len: 14
          ref: const,const
         rows: 1
        Extra: NULL
1 row in set (0.32 sec)

mysql> explain select * from student where sage=20 and sis=1  \G
ERROR 1054 (42S22): Unknown column 'sis' in 'where clause'
mysql> explain select * from student where sage=20 and sid=1  \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
         type: ref
possible_keys: inx_all
          key: inx_all
      key_len: 14
          ref: const,const
         rows: 1
        Extra: NULL
1 row in set (0.00 sec)

当只查询其中的sid时，也是可以命中索引

mysql> explain select * from student where sid=1  \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
         type: ref
possible_keys: inx_all
          key: inx_all
      key_len: 9
          ref: const
         rows: 1
        Extra: NULL
1 row in set (0.00 sec)

当只查询sage时，不命中索引

mysql> explain select * from student where sage=20   \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
        Extra: Using where
1 row in set (0.00 sec)

所以由于该原则，在创建联合索引时，索引字段的顺序需要考虑字段值去重之后的个数，较多的放在前面。Order by也遵循此规则。

七、索引的优化

MySQL的优化方面较多，主要是索引的优化。

1.普通单表索引优化

举个简单的例子：
1.通过主键查找cid=2的信息。

mysql> explain select * from course where cid=2 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: course
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
        Extra: NULL
1 row in set (0.01 sec)

course表中虽然有多条数据，但是只影响到其中一行，是因为使用了primary 主键索引(Innodb的存储引擎)，通过id=2来查询时，在B+上使用的是主键索引来查询,通过主键索引就可以一次来查询出当前行的数据。
2.通过辅助索引cname来查询主键所对应的cid；

mysql> explain select cid from course where cname='数学' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: course
         type: ref
possible_keys: idx_c
          key: idx_c
      key_len: 768
          ref: const
         rows: 1
        Extra: Using where; Using index
1 row in set (0.05 sec)

通过explain分析执行过程，查询条件时cname=‘数学’,数据，执行过程是使用到了idx_c索引，采用的是辅助索引(INNODB的辅助︰叶子节点上存储的是关键字和主键值)，通过查询一次辅助索引就能获取到查询值，不需要到主键索引上获取信息。
3.通过cname查询tid；

mysql> explain select tid from course where cname='数学' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: course
         type: ref
possible_keys: idx_c
          key: idx_c
      key_len: 768
          ref: const
         rows: 1
        Extra: Using index condition
1 row in set (0.03 sec)

sql通过cname来查询tid值，由于cname是创建了辅助索引，要查询的tid是在主键索引的叶子节点上，执行的过程先通过cname查询辅助索引idx_c,找到主键cid，通过主键cid值来主键索引上来查询，该过程进行了两次索引查询过程。

总结：

查询过程中建议合理的使用select结果值：
如上表例子，如果经常通过cname来查询tid，建议创建(cname,tid)的联合索引，注意最左侧原则，在辅助索引上最左侧的值建立一个辅助索引的B+树，叶子节点上存在的就是关键字cname和tid以及主键值，在执行SQL时select tid from course where cname='数学';，只需要查询一次辅助索引就可以拿到结果了。

2.单表查询（普通索引+排序或者分组）

用户订单表信息，来查询用户的订单信息并倒叙排序
1.select * from orderlist where userid=1 order by data desc;

explain select * from orderlist where userid=1 order by date desc\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orderlist
         type: ALL
possible_keys: idx_id
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
        Extra: Using where; Using filesort
1 row in set (0.00 sec)

这个过程中查询了整个表，效率是比较低的，有序查询完userID=1所有的行记录后，还要按照date进行排序，所以出现了Using filesort。出现文件排序，效率比较低，一般要进行优化。
2.select * from orderlist force index (idx_id) where userid=1
通过force index (索引名):指定索引，强制索引一定执行

mysql> explain select * from orderlist force index (idx_id) where userid=1 order by date desc\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orderlist
         type: ref
possible_keys: idx_id
          key: idx_id
      key_len: 4
          ref: const
         rows: 3
        Extra: Using where; Using filesort
1 row in set (0.00 sec)

通过force 强制使用索引后，执行过程中是使用到了idx_id索引，查询表也就没有使用到全表查询。
注意：一个SQL查询一张表，一次只能使用一个索引
3.采用（userid,date)联合索引

explain select * from orderlist force index(idx_id_date) where userid=1 order by date desc\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orderlist
         type: ref
possible_keys: idx_id_date
          key: idx_id_date
      key_len: 4
          ref: const
         rows: 3
        Extra: Using where
1 row in set (0.00 sec)

通过userID和date创建的联合索引，不会出现filesort问题，根据userid=1查询辅助索引，找到的数据已经按照date排好序了，提高了查询效率。

3.多表查询

在使用多表连接查询的时候，MYSQL会首先判断那个表小。（表大小主要指的是数据行数少）
小表无论如何都是整表遍历的，是使用不到索引的，大表就需要使用索引。
所以在连接查询的时候，小表总是要整表搜索的，建索引没有用，大表创建索引是能提高查询效率的，
小表决定查询次数，大表决定查询时间
在连接查询中，大表小表的角色是不一样的，没有where子句，正常按照数据行数定大小表。如果有where子句，那么就按照条件过滤完所留下的行数来定大小表!

八、索引总结

1.MysQL以主键的值构造成一颗树，叶子节点放着该主键对应的整行数据。此为聚簇索引。
2．其他的索引为辅助索引，叶子节点存放着索引字段的值及对应的主键值。
3.一般情况下，一次查询只能使用一条索引
4.对查询where条件中区分度高的字段加索引
5．联合索引，叶子节点存储的顺序以创建时指定的顺序为准，因此区分度高的放左边，能被多个查询复用到的放左边
6.只select需要用到的字段，尽量避免select *
7.如有必要，可使用FORCE index强制索引
8．多表JOIN，先按各表的查询条件比较哪个开销小，从小表取出所有符合条件的，到大表循环查找
9.以下情况无法使用到索引，like通配符在最左，not in，!=，<>,对列做函数运算，隐式数据类型转换，OR子句
10.给查询操作较多的属性添加索引
11.给经常作为过滤条件的字段添加索引

噜啦l

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
浅谈Mysql索引

Mysql索引介绍一、索引介绍二、索引分类三、索引SQL1.创建表时可以创建的索引2.在已有表上添加索引2.1通过create语法创建2.2通过alter语法创建3.删除索引四、索引的执行过程五、索引底层的数据结构一、索引介绍索引就是一中数据结构，索引是创建在表上，是对数据库表中的一列或多列的值进行排序的一种结果，索引的好处是用来提高查询效率，避免全表查询。二、索引分类1.普通索引：没有任何限制条件，可以给表中的任何字符按创建索引。2.唯一索引：使用UNIQUE修饰的字段，值是不能重复的，主键索引
复制链接

扫一扫

专栏目录