一、简介

1. 为什么要使用索引

假设有一张表,表的数据有10W条数据,其中有一条数据是nickname=’css’,如果要拿这条数据的话需要些的sql是 SELECT * FROM award WHERE nickname = ‘css’
一般情况下,在没有建立索引的时候,mysql需要扫描全表及扫描10W条数据找这条数据,如果我在nickname上建立索引,那么mysql只需要扫描一行数据及为我们找到这条nickname=’css’的数据
mysql的索引分为单列索引(主键索引,唯一索引,普通索引)和组合索引

2. 使用索引的优缺点

优点：

可以通过建立唯一索引或者主键索引,保证数据库表中每一行数据的唯一性
建立索引可以大大提高检索的数据,以及减少表的检索行数
在表连接的连接条件可以加速表与表直接的相连
在分组和排序字句进行数据检索,可以减少查询时间中分组和排序时所消耗的时间（数据库的记录会重新排序）
建立索引,在查询中使用索引可以提高性能

缺点：

在创建索引和维护索引会耗费时间,随着数据量的增加而增加
索引文件会占用物理空间,除了数据表需要占用物理空间之外,每一个索引还会占用一定的物理空间
当对表的数据进行 INSERT,UPDATE,DELETE 的时候,索引也要动态的维护,这样就会降低数据的维护速度,(建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快)

二、索引入门

1. 基本操作

创建索引

create index 索引名 on 表名(字段名);

查看索引

show index from 表名

删除索引

drop index 索引名 on 表名;

2. 索引测试

创建表

-- 创建员工表
create table emp(
    id int unsigned primary key auto_increment,
    empno mediumint unsigned not null default 0,
    ename varchar(20) not null default "",
    job varchar(9) not null default "",
    mgr mediumint unsigned not null default 0,
    hiredate date not null,
    sal decimal(7,2) not null,
    comn decimal(7,2) not null,
    deptno mediumint unsigned not null default 0
);

-- 创建部门表
create table dept(
    id int unsigned primary key auto_increment,
    deptno mediumint unsigned not null default 0,
    dname varchar(20) not null default "",
    loc varchar(13) not null default ""
);
-- 经理表
create table manager(
   id int unsigned primary key auto_increment,
   mgrname varchar(50),
   mstatus int
);

insert into manager values(1,'张经理',1);
insert into manager values(2,'王经理',1);
insert into manager values(3,'李经理',1);
insert into manager values(4,'陈经理',1);

创建存储过程

create PROCEDURE insertData(IN START INT(10),IN max_num INT(10))
BEGIN
  -- 生成用户名
  DECLARE n int default 4;
  DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmlopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    DECLARE return_str VARCHAR(255) DEFAULT '';
    DECLARE i INT DEFAULT 0;
    DECLARE num INT DEFAULT 0;
  DECLARE empid INT DEFAULT 0;
  DECLARE mgrid INT DEFAULT 0;
  DECLARE money decimal(7,2) DEFAULT 1000.0;
  DECLARE j INT DEFAULT 0;
  DECLARE jobstr  VARCHAR(255) DEFAULT 'SALEMAN';
    # set autocommit =0 把autocommit设置成0，把默认提交关闭
    SET autocommit = 0;
    REPEAT

    WHILE i < n DO
    SET return_str = CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
    SET i = i+1;
    END WHILE;
  set i = 0;

    SET num = FLOOR(100+RAND()*10);
    SET jobstr =
  CASE
        when num%5= 0 then '经理'
        when num%5= 1 then '销售'
        when num%5= 2 then '开发'
        when num%5= 3 then '财务'
        when num%5= 4 then '后勤'
  END;

  SET money =
  CASE
        when num%5= 0 then 1000.0
        when num%5= 1 then 3000.0
        when num%5= 2 then 5000.0
        when num%5= 3 then 8000.0
        when num%5= 4 then 10000.0
  END;
  SET mgrid = (num%4+1);
  SET empid =(RAND()*10000000);
    SET j = j + 1;
    INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comn,deptno) VALUES (empid ,return_str,jobstr,mgrid,now(),money,400,num);
  INSERT  INTO dept( deptno,dname,loc) VALUES(num,CONCAT(jobstr,'部门') ,(START+i));
    SET return_str ='';
    UNTIL j = max_num
    END REPEAT;
    COMMIT;
END;

插入一百万行数据

call insertData(1,1000001);

三、索引常见的模型

索引的出现是为了提高查询效率，但是实现索引的方式却有很多种，所以这里也就引入了索引模型的概念。可以用于提高读写效率的数据结构很多，这里我先给你介绍三种常见、也比较简单的数据结构，它们分别是哈希表、有序数组和搜索树。

下面我主要从使用的角度，为你简单分析一下这三种模型的区别

1. 哈希表

哈希表是一种以键 - 值（key-value）存储数据的结构，我们只要输入待查找的键即 key，就可以找到其对应的值即 Value。哈希的思路很简单，把值放在数组里，用一个哈希函数把 key 换算成一个确定的位置，然后把 value 放在数组的这个位置。

不可避免地，多个 key 值经过哈希函数的换算，会出现同一个值的情况。处理这种情况的一种方法是，拉出一个链表。
假设，你现在维护着一个身份证信息和姓名的表，需要根据身份证号查找对应的名字，这时对应的哈希索引的示意图如下所示：

图中，User2 和 User4 根据身份证号算出来的值都是 N，但没关系，后面还跟了一个链表。假设，这时候你要查 ID_card_n2 对应的名字是什么，处理步骤就是：首先，将 ID_card_n2 通过哈希函数算出 N；然后，按顺序遍历，找到 User2

需要注意的是，图中四个 ID_card_n 的值并不是递增的，这样做的好处是增加新的 User 时速度会很快，只需要往后追加。但缺点是，因为不是有序的，所以哈希索引做区间查询的速度是很慢的

你可以设想下，如果你现在要找身份证号在[ID_card_X, ID_card_Y]这个区间的所有用户，就必须全部扫描一遍了。所以，哈希表这种结构适用于只有等值查询的场景，比如 Memcached 及其他一些 NoSQL 引擎

2. 有序数组

而有序数组在等值查询和范围查询场景中的性能就都非常优秀。还是上面这个根据身份证号查名字的例子，如果我们使用有序数组来实现的话，示意图如下所示：

这里我们假设身份证号没有重复，这个数组就是按照身份证号递增的顺序保存的。这时候如果你要查 ID_card_n2 对应的名字，用二分法就可以快速得到，这个时间复杂度是 O(log(N))

同时很显然，这个索引结构支持范围查询。你要查身份证号在[ID_card_X, ID_card_Y]区间的 User，可以先用二分法找到 ID_card_X（如果不存在 ID_card_X，就找到大于 ID_card_X 的第一个 User），然后向右遍历，直到查到第一个大于 ID_card_Y 的身份证号，退出循环

如果仅仅看查询效率，有序数组就是最好的数据结构了。但是，在需要更新数据的时候就麻烦了，你往中间插入一个记录就必须得挪动后面所有的记录，成本太高。所以，有序数组索引只适用于静态存储引擎，比如你要保存的是 2017 年某个城市的所有人口信息，这类不会再修改的数据

3. 搜索树

二叉搜索树也是课本里的经典数据结构了。还是上面根据身份证号查名字的例子，如果我们用二叉搜索树来实现的话，示意图如下所示：

二叉搜索树的特点是：每个节点的左儿子小于父节点，父节点又小于右儿子。这样如果你要查 ID_card_n2 的话，按照图中的搜索顺序就是按照 UserA -> UserC -> UserF -> User2 这个路径得到。这个时间复杂度是 O(log(N))

当然为了维持 O(log(N)) 的查询复杂度，你就需要保持这棵树是平衡二叉树。为了做这个保证，更新的时间复杂度也是 O(log(N))。

树可以有二叉，也可以有多叉。多叉树就是每个节点有多个儿子，儿子之间的大小保证从左到右递增。二叉树是搜索效率最高的，但是实际上大多数的数据库存储却并不使用二叉树。其原因是，索引不止存在内存中，还要写到磁盘上。
你可以想象一下一棵 100 万节点的平衡二叉树，树高 20。一次查询可能需要访问 20 个数据块。在机械硬盘时代，从磁盘随机读一个数据块需要 10 ms 左右的寻址时间。也就是说，对于一个 100 万行的表，如果使用二叉树来存储，单独访问一个行可能需要 20 个 10 ms 的时间，这个查询可真够慢的。

为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块。那么，我们就不应该使用二叉树，而是要使用“N 叉”树。这里，“N 叉”树中的“N”取决于数据块的大小。

以 InnoDB 的一个整数字段索引为例，这个 N差不多是 1200。这棵树高是 4 的时候，就可以存 1200 的 3 次方个值，这已经 17 亿了。考虑到树根的数据块总是在内存中的，一个 10 亿行的表上一个整数字段的索引，查找一个值最多只需要访问 3 次磁盘。其实，树的第二层也有很大概率在内存中，那么访问磁盘的平均次数就更少了。N 叉树由于在读写上的性能优点，以及适配磁盘的访问模式，已经被广泛应用在数据库引擎中了。

不管是哈希还是有序数组，或者 N 叉树，它们都是不断迭代、不断优化的产物或者解决方案。数据库技术发展到今天，跳表、LSM 树等数据结构也被用于引擎设计中，这里我就不再一一展开了。

你心里要有个概念，数据库底层存储的核心就是基于这些数据模型的。每碰到一个新数据库，我们需要先关注它的数据模型，这样才能从理论上分析出这个数据库的适用场景

四、索引的分类

1. 单列索引

普通索引：这个是最基本的索引
其sql格式是 CREATE INDEX IndexName ON TableName(字段名(length)) 或者 ALTER TABLE TableName ADD INDEX IndexName(字段名(length))

第一种方式 :

CREATE INDEX account_Index ON `award`(`account`);

第二种方式

ALTER TABLE award ADD INDEX account_Index(`account`)

如果是CHAR,VARCHAR,类型,length可以小于字段的实际长度,如果是BLOB和TEXT类型就必须指定长度

唯一索引：与普通索引类似,但是不同的是唯一索引要求所有的类的值是唯一的,这一点和主键索引一样.但是他允许有空值,
其sql格式是 CREATE UNIQUE INDEX IndexName ON TableName(字段名(length)); 或者 ALTER TABLE TableName ADD UNIQUE (column_list)

CREATE UNIQUE INDEX account_UNIQUE_Index ON `award`(`account`);

主键索引：不允许有空值,(在B+TREE中的InnoDB引擎中,主键索引起到了至关重要的地位)
主键索引建立的规则是 int优于varchar,一般在建表的时候创建,最好是与表的其他字段不相关的列或者是业务不相关的列.一般会设为 int 而且是 AUTO_INCREMENT自增类型的

Java

四、MySQL索引