MySQL索引知识

MySQL索引学习

索引创建

• 创建表时创建(unique 代表唯一索引 fulltext代表全文索引 spatial代表空间索引)

create table student(

​ id int primary key,

​ name varchar(255),

​ sex varchar(10),

​ index(name)

)

• 使用create index创建索引(不能创建主键索引)

create index index_name on table_name(column_list)

• 使用alter table创建索引

alter table table_name add index index_name (column_list)

数据库索引结构

MySQL采用B+ tree的数据结构构建索引

• B tree
  
  B tree称为平衡多路搜索树，它基于在二叉树的基础上采用多叉树，再使用平衡二叉树的思想。

  1. 所有键值分布在整颗树中；
  2. 任何一个关键字出现且只出现在一个结点中；
  3. 搜索有可能在非叶子结点结束；
  4. 在关键字全集内做一次查找,性能逼近二分查找

• B+ tree
  
  B+树是B-树的变体，也是一种多路搜索树, 它与 B- 树的不同之处在于:

  1. 所有关键字存储在叶子节点出现,内部节点(非叶子节点并不存储真正的 data)
  2. 为所有叶子结点增加了一个链指针（对于范围查询有利）

索引分析

explain用来分析SQL语句的查询情况，它包含id、select_type、table、type、possible_keys、key、key_len、ref、rows、extra字段

索引使用策略

• 匹配全值索引查询：针对建索引的列的全值进行查询。

• 匹配最左前缀索引查询：索引不仅可以进行单列索引，也可以建立联合索引，也就是把多列建成一个联合索引匹配列。

  例如，把学生表中的姓名name、性别sex、年龄age建立成联合索引，按照最左前缀匹配原则，它可以匹配name单独一列，可以匹配name、sex两列，可以匹配name、sex、age三列，但是不能匹配sex或者age单独列，不能匹配name、age列或者sex、age列。

• 匹配列前缀索引查询：匹配列的前一部分进行查询，如查询姓名，可以按姓氏“刘”开头进行查询，使用通配%，但是通配符只能放置在右侧，不能放置在左侧。

• 匹配值的范围索引查询：对索引可以进行范围查询。

• 仅对索引进行查询：只针对索引进行查询，也叫索引覆盖，不查询数据表。例如，在年龄上建立索引后，查询的时候只查询年龄，这样它可以通过索引树上的年龄值获取年龄数据。

索引类型

• 主键索引，根据表的主键建立索引，称为主键索引。主键索引是不允许重复和不允许有空值的，它是唯一索引的一种特例。

• 普通索引，最基本的索引，没有任何限制，可以在创建表的时候创建索引，可以创建表后直接创建索引，也可以修改表结构的方式添加索引。

• 唯一索引，要求列值不允许重复，但是允许有空值，主键索引就是唯一索引的特例。也可以通过联合索引来创建唯一索引，要求列值的组合必须唯一，需要使用关键子UNIQUE来标识唯一索引。

• 聚簇索引和非聚簇索引

  聚簇索引是将数据存放在索引树的叶子节点上，找到叶子节点就可以读取这行数据。InnoDB存储引擎的索引方式就是聚簇索引。一个表只能有一个聚簇索引，一般会根据主键或者唯一索引，或者以数据库内部生成的rowid为主键，来建立聚簇索引。

  非聚簇索引是在索引树的叶子节点上存放数据的地址，找到该地址后，需要到磁盘中查询一次才能获取到数据。MyISAM存储引擎的索引方式就是非聚簇索引，只在索引树的叶子节点上存放地址。

• 索引不生效情况

  • 不等于（<>、!=），使用不等于操作符进行条件判断时时不能使用索引的

  • 前导模糊查询（%xx%），使用前导模糊查询不能使用索引，但是后导模糊查询（xx%）时可以使用索引的。

  • 比较不匹配的数据类型，比较不匹配的数据类型是指某一列在比较查询时，假如列是varchar字符串类型，但是在赋值的时候赋值int整型，这时这一列即使有索引，也不会使用到索引。

  • or连接条件，在SQL语句的条件中，用OR连接的条件表达式不能使用索引。

  • 条件表达式前使用函数，在SQL语句的条件中，在表达式前使用函数或者运算操作，不能使用索引；在表达式后使用函数或者运算操作，可以使用索引。

    select * from student where id + 1 = 1 不使用索引

    select * from student where id = 1 + 1 使用索引

索引利弊

优势：

(1) 提高查询效率，它可以通过创建唯一索引或者主键索引来标识行的唯一性，在查询的时候可以快速定位到要查询的行数据

(2) 可以加快表与表之间的连接查询

(3) 在分组和排序的时候可以极大地节省时间，使用索引可以进行快速排序，分组查询时虽然不能直接使用索引，但是分组查询要先进行排序，而在排序阶段就会减少时间，所以分组查询也能节省时间

(4) 使用索引来进行查询、排序、分组，使用优化隐藏器提高系统的性能

劣势：

(1) 创建索引和维护索引需要耗费时间，随着数据量的增大，时间也会逐渐增加

(2) 索引文件的大小会逐渐增大，索引文件需要占用物理空间，如果建立聚簇索引，文件大小会更大，会使数据库的存储逐渐变大

(3) 对表进行增加、修改、删除的时候，都需要对索引进行维护，这会影响对表和数据的操作速度，延长对表的操作时间

索引建立的原则

• 主键的字段需要建立唯一索引或者唯一索引
• 用于连接查询的字段可以建立索引
• 经常用来排序的字段可以建立索引
• 频繁使用where的条件字段可以建立索引
• 唯一性太差的字段不适合建立索引
• 更新频率远大于查询的字段不适合建立索引
• 不会出现where条件表达式的字段不适合建立索引
• 对于类型为text、blob、image、bit的字段，不适合建立索引

来源：《MySQL数据库应用实战教程（慕课版）》