数据库基础知识（索引）

为什么要使用数据库？

1.数据保存在内存

        优点：存取速度快

        缺点：数据不能永久保存

2.数据保存在文件

        优点：数据永久保存

        缺点：1.速度比内存操作慢，频繁的IO操作。2.查询数据不方便

3.数据保存在数据库

        数据永久保存。

        使用SQL语句，查询方便效率高。

        管理数据方便。

什么是SQL？

       结构化查询语言(Structured Query Language)简称SQL，是一种数据库查询语言。

  作用：用于存取数据、查询、更新和管理关系数据库系统。

什么是MySQL?

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。在Java企业级开发中非常常用，因为 MySQL 是开源免费的，并且方便扩展。

数据库三大范式是什么？

• 第一范式：每个列都不可以再拆分。

• 第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。

• 第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。

索引

什么是索引？

        索引是一种特殊的文件（InnoDB数据表上的索引是表空间的一个组成部分），它们包含着对数据表里所有记录的引用指针。

        索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。

        更通俗的说，索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录。索引是一个文件，它是要占据物理空间的。

优点：

        可以大大加快数据的检索速度，这也是创建索引的最主要的原因。

        通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

缺点：

        时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率。

        空间方面：索引需要占物理空间。

怎么创建索引的，有什么好处，有哪些分类？

1.创建索引的语法：create index depe_unique_ide on depe(dept_no)tablespace idx_

2.创建索引可以增加查询速度，唯一索引可以保证数据库列的一致性，可以确定表与表之间连接

3.索引的分类：

        逻辑分类：单列索引，复合索引，唯一索引，非唯一索引，函数索引

        物理分类：B数索引，反向键索引，位图索引

索引的作用：

通过索引可以大大的提高数据库的检索速度，改善数据库性能

索引类型具体介绍

普通索引：用表中的普通列构建的索引，没有任何限制，用于加速查询

唯一索引：用来建立索引的类的值必须是唯一的，允许空值

关键字：unique

语法：create unique 索引名 on 表名(字段名);

唯一约束的作用：列值不能重复，但是允许null；当给某个列添加唯一约束，会自动添加唯一索引

约束：限制列的动作，包括添加修改值

主键索引：即主索引，根据主键pk_clolum（length）建立索引，不允许重复，不允许空值，是一种特殊的唯一索引，一个表只能有一个主键，　　不允许有空值。一般是在建表的时候同时创建主键索引。当列添加主键约束时，自动添加主键索引。

组合索引：用多个列组合构建的索引，这多个列中的值不允许有空值

语法：create index 索引名 on 表名(字段1,字段2);

全文索引：用大文本对象的列构建的索引，主要用来查找文件中的关键字。

关键字：fulltext
语法：create fulltext index 索引名 on 表名(字段名);

索引使用场景

1.当数据多且字段值有相同的值的时候用普通索引。

2.当字段多且字段值没有重复的时候用唯一索引。

3.当有多个字段名都经常被查询的时候用复合索引。

4.普通索引不支持空值，唯一索引支持空值。

5.当这张表增删改多而查询较少的时候，就不要创建索引了，因为如果你给一列创建了索引，那么对该列进行增删改的时候，都会先访问这一列的索引；

若是增，则在这一列的索引内，以新填的这个字段名的值为名创建索引的子集；

若是改，则会把原来的删掉，再添入一个以这个字段名的新值为名创建索引的子集；

若是删，则会把索引中以这个字段为名的索引的子集删掉；

所以，会对增删改的执行减缓速度；

所以，若是这张表增删改多而查询较少的话，就不要创建索引了。

6.更新太频繁的地段不适合创建索引。

7.不会出现在where条件中的字段不该建立索引。

主键索引与唯一索引的区别：

1.主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。

2.主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。

3.唯一性索引列允许空值，而主键不允许为空值。

4.主键列在创建时，已经默认为空值++唯一索引了。

5.一个表最多只能创建一个主键，但可以创建多个唯一索引。

6.主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。

7.主键可以被其他表引用为外键，而唯一索引不能。

索引的数据结构（b树，hash）

        索引的数据结构和具体存储引擎的实现有关，在MySQL中使用较多的索引有hash索引，B+树索引等，而我们经常使用的InnoDB存储引擎的默认索引实现为：B+树索引。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择BTree索引。

1、B树索引

MySQL通过存储引擎取数据，基本上90%的人用的就是InnoDB了，按照实现方式分，InnoDB的索引类型目前只用两种：BTree（B树）索引和hash索引。B树索引是MySQL数据库中使用最频繁的索引类型，基本所有存储引擎都支持BTree索引。通常我们说的索引不出意外指的就是（B树）索引（实际是用B+树实现的，因为在查看表索引时，MySQL一律打印BTree，所以简称为B树索引）

查询方式：

        主键索引区：PI（关联保存时的数据地址）按主键查询，

        普通索引：si（关联的id的地址，然后再到达上面的地址）。所以按主键查询，速度最快

B+tree性质：

        1.n棵子tree的节点包含n个关键字，不用来保存数据而是保存数据的索引。

        2.所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。

        3.所有的非终端结点可以看成是索引部分，结点中仅含其子树中的最大（或最小）关键字。

        4.B+树中，数据对象的插入和删除仅在叶节点上进行。

        5.B+树有2个头指针，一个是树的根节点，一个是最小关键码的叶节点。

2、哈希索引

        简要说，类似于数据结构中简单实现的hash表（散列表）一样，当我们在MySQL中用哈希索引时，主要就是通过Hash算法（常见的Hash算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法），将数据库字段数据转换成定长的Hash值，与这条数据的行指针一并存入Hash表的对应位置；如果发生Hash碰撞（两个不同关键字的Hash值相同），则在对应Hash键下以链表形式存储。

索引的基本原理

        索引用来快速地寻找那些具有特定值的记录。如果没有索引，一般来说执行查询时遍历整张表。

        索引的原理很简单，就是把无序的数据变成有序的查询。

        1.把创建了索引的列内容进行排序；

        2.对排序结果生成倒排表；

        3.在倒排表内容上拼上数据的地址链；

        4.在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据。

索引算法有哪些？

        索引算法有 BTree算法和Hash算法

1、BTree算法

BTree是最常用的MySQL数据库索引算法，也是MySQL默认的算法。因为它不仅可以被用在=,>,>=,<,<= 和between这些比较操作符上，而且还可以用于like操作符，只要它的查询条件是一个不以通配符开头的常量，例如：

-- 只要它的查询条件是一个不以通配符开头的常量
select * from user where name like 'jack%'; 
-- 如果一通配符开头，或者没有使用常量，则不会使用索引，例如： 
select * from user where name like '%jack'; 

2、Hash算法

        Hash索引只能用于对等比较，例如=,<=>（相当于=）操作符。由于是一次定位数据，不像BTree索引需要从根节点到枝节点，最后才能访问到页节点这样多次IO访问，所以检索效率远高于BTree索引。

索引设计的原则？

1.适合索引的列是出现在where子句中的列，或者连接子句中指定的列

2.基数较小的类，索引效果较差，没有必要在此建立索引

3.使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间

4.不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。

创建索引的原则

1.最左前缀匹配原则，组合索引非常重要的原则，MySQL会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2.较频繁作为查询条件的字段才去创建索引

3.更新频繁字段不合适创建索引

4.若是不能有效区分数据的列不适合做索引列（如性别，男女未知，最多也就三种，区分度实在太低）

5.尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加（a,b）的索引，那么只需要修改原来的索引即可。

6.定义有外键的数据列一定要建立索引。

7.对于那些查询中很少涉及的列，重复值较多的列不要建立索引。

8.对于定义为text、image和bit的数据类型的列不要建立索引。

创建索引的三种方式

第一种方式：在执行CREATE TABLE是创建索引

CREATE TABLE user_index2 (
id INT auto_increment PRIMARY KEY,
first_name VARCHAR (16),
last_name VARCHAR (16),
id_card VARCHAR (18),
information text,
KEY name (first_name, last_name),
FULLTEXT KEY (information),
UNIQUE KEY (id_card)
);

第二种方式：使用ALTER TABLE命令去增加索引

ALTER TABLE table_name ADD INDEX index_name (column_list);

1.ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

2.table_name ：表名；column_list：要进行索引的列，多列时各列之间用逗号分隔。

3.索引名index_name可自己命名，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。

第三种方式：使用CREATE INDEX命令创建

CREATE INDEX index_name ON table_name (column_list);

CREATE INDEX可以对表增加普通索引或UNIQUE索引。（但不能创建PRIMARY KEY索引）。

创建索引时需要注意什么？

1.非空字段：应该指定列为NOT NULL,除非你想存储NULL。在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值；

2.取值离散大的字段：（变量各个取值之间差异程度）的列放到联合索引的前面，可以通过count（）函数查看字段的差异值，返回值越大说明字段的唯一值越多，字段的离散程度高；

3.索引字段越小越好：数据库的数据存储以页为单位一页存储的数据越多，一次IO操作获取的数据越大效率越高。

如何删除索引：

据索引名删除普通索引、唯一索引、全文索引：alter table 表名 drop KEY 索引名

alter table user_index drop KEY name;
alter table user_index drop KEY id_card;
alter table user_index drop KEY information;

• 删除主键索引：alter table 表名 drop primary key（因为主键只有一个）。

• 这里值得注意的是，如果主键自增长，那么不能直接执行此操作（自增长依赖于主键索引）：

        需要取消自增长再行删除：

alter table user_index
-- 重新定义字段
MODIFY id int,
drop PRIMARY KEY

• 但通常不会删除主键，因为设计主键一定与业务逻辑无关。