为什么要使用数据库?
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
-
但通常不会删除主键,因为设计主键一定与业务逻辑无关。