Mysql索引详解

454 阅读2分钟

一、数据索引是干什么用的呢?

数据库索引其实就是为了使查询数据效率快。

二、数据库索引有哪些呢?

  1. 聚集索引(主键索引):在数据库里面,所有行数都会按照主键索引进行排序。

  2. 非聚集索引:就是给普通字段加上索引。

  3. 联合索引:就是好几个字段组成的索引,称为联合索引。

    联合索引遵从最左前缀原则

三、索引相关操作

  1. 创建表时创建索引
CREATE TABLE t(
   c1 INT PRIMARY KEY,
   c2 INT NOT NULL,
   c3 INT NOT NULL,
   c4 VARCHAR(10),
   INDEX (c2,c3) 
);
  1. 创建表之后添加索引
CREATE INDEX index_name ON table_name (column_list)
  1. 查看表中的索引
SHOW INDEXES FROM employees;

截屏2021-09-11 10.51.51.png

四、有哪些列子不走索引呢?

表student中两个字段age,name加了索引

key 'idx_age' ('age'),
key 'idx_name' ('name')
  1. Like这种就是%在前面的不走索引,在后面的走索引
A: select * from student where 'name' like '王%'
B: select * from student where 'name' like '%小'
  A走索引,B不走索引

2. 用索引列进行计算的,不走索引

A:select * from student where age = 10+8
B:select * from student where age + 8 = 18
 A走索引,B不走索引

3. 对索引列用函数了,不走索引

A:select * from student where  concat('name','哈') ='王哈哈';
B:select * from student where name = concat('王哈','哈');
 A不走索引,B走索引

 4. 索引列用了!= 不走索引,如下:

select * from student where age != 18

对于不走索引的这些情况要使用index merge(索引合并),具体用法后续章节会讲到

什么是索引合并?

根据官方文档中的说明,我们可以了解到:

  1. 索引合并是把几个索引的范围扫描合并成一个索引。
  2. 索引合并的时候,会对索引进行并集,交集或者先交集再并集操作,以便合并成一个索引。
  3. 这些需要合并的索引只能是一个表的。不能对多表进行索引合并。

怎么确定使用了索引合并?

在使用explain对sql语句进行操作时,如果使用了索引合并,那么在输出内容的type列会显示 index_merge,key列会显示出所有使用的索引

mysql> explain select * from test where (key1_part1=4 and key1_part2=4) or key2_part1=4\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: index_merge
possible_keys: key1,key2
          key: key1,key2
      key_len: 8,4
          ref: NULL
         rows: 3
        Extra: Using sort_union(key1,key2); Using where
1 row in set (0.00 sec)

为什么索引用B+树?

一页存储16kb,页目录也是16kb,页目录不存储数据,只有叶子节点存储数据,这就使以页为单位的索引中可以存放更多的节点。减少更多的I/O支出,因此,B+树成为了数据库比较优秀的数据结构,mysql将树根常驻内存,减少了一次I/O