MySQL高级知识点（二）

MySQL高级知识点（二）

三、索引优化

1、什么是索引

• MySQL 官方对索引的定义为：索引（Index）是帮助 MySQL 高效获取数据的数据结构。可以得到索引的本质： 索引是数据结构。

  可以简单理解为：排好序的快速查找数据结构

• 在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。下图就是一种可能的索引方式示例：

• 左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址。为了加快 Col2 的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用 二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录
• 一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上

2、索引的优缺点

优点

• 提高数据检索的效率，降低数据库的IO成本
• 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

缺点

• 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息
• 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的

3、索引的分类

基本语法

• 创建

  CREATE [UNIQUE] INDEX [indexName] ON table_name(column);
  

• 删除

  DROP INDEX [indexName] ON table_name;
  

• 查看

  SHOW INDEX FROM table_name;
  

分类

• 单值索引

  • 定义：即一个索引只包含单个列，一个表可以有多个单列索引

  • 语法：

    --和表一起创建
    CREATE TABLE customer (
    id INT(10) UNSIGNED AUTO_INCREMENT,
    customer_no VARCHAR(200),
    customer_name VARCHAR(200), 
    PRIMARY KEY(id), 
    KEY (customer_name) --单值索引
    );
    
    --单独创建单值索引
    CREATE INDEX idx_customer_name ON customer(customer_name);
    

• 唯一索引

  • 定义：索引列的值必须唯一，但允许有空值

  • 语法：

    --和表一起创建
    CREATE TABLE customer (
    id INT(10) UNSIGNED AUTO_INCREMENT,
    customer_no VARCHAR(200),
    customer_name VARCHAR(200), 
    PRIMARY KEY(id), 
    KEY (customer_name), --单值索引
    UNIQUE (customer_no) --唯一索引
    );
    
    --单独创建唯一索引
    CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no);
    

• 主键索引

  • 定义：设定为主键后数据库会自动建立索引，innodb为聚簇索引

  • 语法：

    --和表一起创建
    CREATE TABLE customer (
    id INT(10) UNSIGNED AUTO_INCREMENT,
    customer_no VARCHAR(200),
    customer_name VARCHAR(200), 
    PRIMARY KEY(id) --主键索引
    );
    
    --单独创建主键索引
    ALTER TABLE customer ADD PRIMARY KEY customer(customer_no);
    
    --删除主键索引
    ALTER TABLE customer DROP PRIMARY KEY;
    
    --修改建主键索引
    必须先删除掉(drop)原索引，再新建(add)索引
    

• 复合索引

  • 定义：即一个索引包含多个列

  • 语法：

    --和表一起创建
    CREATE TABLE customer (
    id INT(10) UNSIGNED AUTO_INCREMENT,
    customer_no VARCHAR(200),
    customer_name VARCHAR(200), 
    PRIMARY KEY(id), 
    KEY (customer_name), --单值索引
    UNIQUE (customer_no), --唯一索引
    KEY (customer_no,customer_name) --复合索引
    );
    
    --单独创建复合索引
    CREATE INDEX idx_no_name ON customer(customer_no,customer_name);