mysql 索引

最新推荐文章于 2024-09-25 14:34:16 发布

last_summer_x

最新推荐文章于 2024-09-25 14:34:16 发布

阅读量101

点赞数

分类专栏：索引文章标签： mysql 索引

本文链接：https://blog.csdn.net/last_summer_x/article/details/79897673

版权

索引专栏收录该内容

1 篇文章 0 订阅

订阅专栏

一、索引是什么

索引是一种数据结构，他能快速的查找表中的数据。

举个栗子：如果把数据表比作一本书，那么索引可以理解为是书的目录。

二、索引有哪些

1. 非聚簇索引

①、单列索引

②、多列索引

③、全文索引（此文不做讲述）

2、聚簇索引

聚簇索引也叫簇类索引，是一种对磁盘上实际数据重新组织以按指定的一个或多个列的值排序。由于聚簇索引的索引页面指针指向数据页面，所以使用聚簇索引查找数据几乎总是比使用非聚簇索引快。每张表只能建一个聚簇索引，并且建聚簇索引需要至少相当该表120%的附加空间，以存放该表的副本和索引中间页。 ——摘自百度百科

因为聚簇索引有排序的效果，所以可以在频繁order by的某几列上建立聚簇索引

三、索引的优缺点

先看一下表结构：

mysql> show create table have_index;
| have_index | CREATE TABLE `have_index` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `qtime` time DEFAULT NULL,
  `uid` varchar(100) DEFAULT NULL,
  `qword` char(100) DEFAULT NULL,
  `url` varchar(150) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `i_t` (`qword`),
  KEY `uid_word` (`uid`,`url`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8 |

mysql> select count(*) from have_index;
+----------+
| count(*) |
+----------+
|   973032 |
+----------+
1 row in set (4.42 sec)

可以看到表中有97万条记录，而且全表搜索时间为4.42s

mysql> select * from have_index where qword = '[谷歌卫星地图]';
49 rows in set (0.50 sec)

select * from no_index where qword = '[谷歌卫星地图]';
49 rows in set (3.06 sec)

很容易对比出有索引的时候查询比没有的时候快了很多

1.优点

索引能提升查询的速度

2.缺点

但是索引是有一个专门的文件（索引文件）来维护，当索引多时，索引文件可能比数据表本身还要大。

而且当每次对数据表进行写操作是，索引文件也会更新。所以索引是以牺牲写的性能来提高读的性能

一张表中索引最好不要超过5,6个

在数据量小时，因为需要额外查询索引，所以不建立索引查询速度反而更快

四、索引的注意事项

1.索引所在列的重复字段不能很多

select * from have_index where qword = '[哄抢救灾物资]';
34893 rows in set (4.04 sec)

select * from no_index where qword = '[哄抢救灾物资]';
34893 rows in set (3.01 sec)

可以明显的看出，执行该sql的时间和全表搜索时间近似，甚至比没有索引的慢

在来用expalin来分析一下这条慢sql

mysql> explain select * from have_index where qword = '[哄抢救灾物资]' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: have_index
   partitions: NULL
         type: ref
possible_keys: i_t
          key: i_t
      key_len: 301
          ref: const
         rows: 64340
     filtered: 100.00
        Extra: NULL

但是可以看到 key哪一行是有值的（走了索引）

由此可以看出，在重复很多时，就算有索引也会变的非常慢。因为需要频繁的向索引（数据结构）里取数据。

2.多列索引或模糊匹配必须"前缀"匹配

①多列索引

这是表中的多列索引KEY `uid_word` (`uid`,`url`)

mysql> explain select * from have_index where uid = '900755558064074'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: have_index
   partitions: NULL
         type: ref
possible_keys: uid_word
          key: uid_word
      key_len: 303
          ref: const
         rows: 284
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

可以看到用uid作为条件是走了索引的

mysql> explain select * from have_index where uid = '900755558064074' and url = 'askldj aslk '\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: have_index
   partitions: NULL
         type: ref
possible_keys: uid_word
          key: uid_word
      key_len: 756
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

拿uid和url一起作为条件也是可以走索引的

mysql> explain select * from have_index where url = 'askldj aslk '\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: have_index
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 963699
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

这回只拿url就不行了，而且连可能会走的索引都没有

由上面的实验不难发现，复合索引当前面的列出现在匹配条件中才会走索引

假如有这样一个索引 in_mul(c1, c2, c3)

那么当匹配条件中有c1 或者 c1 & c2 或者 c1 & c2 & c3 时索引才会起作用

②模糊匹配

KEY `i_t` (`qword`)

mysql> explain select * from have_index where qword like '[哄抢%' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: have_index
   partitions: NULL
         type: range
possible_keys: i_t
          key: i_t
      key_len: 301
          ref: NULL
         rows: 69574
     filtered: 100.00
        Extra: Using index condition; Using MRR

当模糊匹配的%在后面时可以看到走了索引

而当%在中间时，由于%前面有具体的词，所以可以认为和%在后面一样（[哄抢%物资] 和 [哄抢% 差不多）

mysql> explain select * from have_index where qword like '%救灾物资]' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: have_index
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 963699
     filtered: 11.11
        Extra: Using where

可是当%出现在前面时索引就失效了

尽管如此，还是要少用模糊匹配，因为他可能会查出大量的数据

mysql> select * from have_index where qword like '[哄抢%' ;
35536 rows in set (5.23 sec)
mysql> select * from no_index where qword like '[哄抢%' ;
35536 rows in set (3.05 sec)

上面看到了，这条模糊匹配的sql是使用了索引的，但是他的执行时间比没有索引的时候还慢

3覆盖索引

说是说覆盖索引，其实就是多列索引，先来看结果

KEY `uid_word` (`uid`,`url`)

拿着两条sql对比一下（为了更好比较，删了几行）

mysql> explain select url from have_index where uid = '900755558064074' \G
*************************** 1. row ***************************
possible_keys: uid_word
          key: uid_word
      key_len: 303
          ref: const
         rows: 284
     filtered: 100.00
        Extra: Using index
--------------------------我是分隔线-------------------------------------
mysql> explain select qtime from have_index where uid = '900755558064074' \G
*************************** 1. row ***************************
possible_keys: uid_word
          key: uid_word
      key_len: 303
          ref: const
         rows: 284
     filtered: 100.00
        Extra: NULL

根据多列索引前缀匹配的原则，这两条sql都是使用了索引的，而且也可以看到他们都使用了索引

再比较一下效率

mysql> select url from have_index where uid = '900755558064074';
284 rows in set (0.00 sec)
mysql> select qtime from have_index where uid = '900755558064074';
284 rows in set (0.40 sec)

可以明显的看到上面的快了很多

这是因为他直接在索引里面拿数据，而不需要另外表中拿数据。而要取表中数据就要经过io，有io就减慢了速度

再来解释一下什么是覆盖索引

当需要 select a from table_name where b = 'XXX' 时

就可以建立多列索引 (b, a)

这样查询的速度就能像上面那样明显了

声明：以上表中数据取自搜狗实验室