MySQL

1. 数据库基本知识

1.1 为什么使用数据库

数据保存在内存

  • 优点:存取速度快
  • 缺点:不能持久化

数据保存在文件

  • 优点: 数据永久保存

  • 缺点:1)速度比内存操作慢,频繁的IO操作。2)查询数据不方便

数据保存在数据库

  • 数据永久保存

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

  • 管理数据方便

1.2 什么是SQL

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

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

1.3 三大范式

       第一范式:每个列都不可以再拆分

       考虑这样一个表:【联系人】(姓名,性别,电话)
如果在实际场景中,一个联系人有家庭电话和公司电话,那么这种表结构设计就没有达到 1NF。要符合 1NF 我们只需把列(电话)拆分,即:【联系人】(姓名,性别,家庭电话,公司电话)。

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

       考虑一个订单明细表:【OrderDetail】(OrderID,ProductID,UnitPrice,Discount,Quantity,ProductName)。

       因为我们知道在一个订单中可以订购多种产品,所以单单一个 OrderID 是不足以成为主键的,主键应该是(OrderID,ProductID)。

       显而易见 Discount(折扣),Quantity(数量)完全依赖(取决)于主键(OderID,ProductID),而 UnitPrice,ProductName 只依赖于 ProductID。所以 OrderDetail 表不符合 2NF。不符合 2NF 的设计容易产生冗余数据。

       第三范式:在第二范式的基础上,非主键列只依赖于主键,不依赖于其他非主键列

       考虑一个订单表【Order】(OrderID,OrderDate,CustomerID,CustomerName,CustomerAddr,CustomerCity)

       主键是(OrderID)。

       其中 OrderDate,CustomerID,CustomerName,CustomerAddr,CustomerCity 等非主键列都完全依赖于主键(OrderID)所以符合 2NF。

       不过问题是 CustomerName,CustomerAddr,CustomerCity 直接依赖的是 CustomerID(非主键列),而不是直接依赖于主键,它是通过传递才依赖于主键,所以不符合 3NF。

       通过拆分【Order】为【Order】(OrderID,OrderDate,CustomerID)和【Customer】(CustomerID,CustomerName,CustomerAddr,CustomerCity)从而达到 3NF。

2. 引擎

       常用的存储引擎有以下:

       Innodb引擎:Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。

       MyIASM引擎(原本Mysql的默认引擎):不提供事务的支持,也不支持行级锁和外键。

       MEMORY引擎:所有的数据都在内存中,数据的处理速度快,但是安全性不高。

2.1 MySQL存储引擎MyISAM与InnoDB区别

在这里插入图片描述在这里插入图片描述

2.2 MyISAM索引与InnoDB索引的区别

  • InnoDB索引是聚簇索引,MyISAM索引是非聚簇索引。
  • InnoDB的主键索引的叶子节点存储着行数据,因此主键索引非常高效。
  • MyISAM索引的叶子节点存储的是行数据地址,需要再寻址一次才能得到数据。
  • InnoDB 非主键索引的叶子节点存储的是主键,因此如果非主键索引不能满足查询要求,需要从主键索引再进行一次查询。

2.3 存储引擎选择

       如果没有特别的需求,使用默认的Innodb即可。

       MyISAM:以读为主的应用程序,比如博客系统、新闻门户网站。

       Innodb:更新(删除)操作频率也高,或者要保证数据的完整性;并发量高,支持事务和外键。比如OA自动化办公系统。

3. 索引

3.1 索引是什么

       索引是帮助 MySQL 高效获取数据的排好序数据结构

3.2 索引有哪些优缺点

索引的优点

  • 可以大大加快数据的检索速度

索引的缺点

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

3.3 索引使用场景

where

在这里插入图片描述

       上图中,根据id查询记录,因为id字段仅建立了主键索引,因此此SQL执行可选的索引只有主键索引,如果有多个,最终会选一个较优的作为检索的依据。

order by

       当我们使用order by将查询结果按照某个字段排序时,如果该字段没有建立索引,那么执行计划会将查询出的所有数据使用外部排序(将数据从硬盘分批读取到内存使用内部排序,最后合并排序结果),这个操作是很影响性能的,因为需要将查询涉及到的所有数据从磁盘中读到内存(如果单条数据过大或者数据量过多都会降低效率),更无论读到内存之后的排序了。

       但是如果我们对该字段建立索引alter table 表名 add index(字段名),那么由于索引本身是有序的,因此直接按照索引的顺序和映射关系逐条取出数据即可。而且如果分页的,那么只用取出索引表某个范围内的索引对应的数据,而不用像上述那取出所有数据进行排序再返回某个范围内的数据。(从磁盘取数据是最影响性能的)

3.4 索引的类型

主键索引: 数据列不允许重复,不允许为NULL,一个表只能有一个主键。

唯一索引: 数据列不允许重复,允许为NULL值,一个表允许多个列创建唯一索引。

  • 可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引

  • 可以通过ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一联合索引

普通索引: 基本的索引类型,没有唯一性的限制,允许为NULL值。

  • 可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引

  • 可以通过ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);创建组合索引

全文索引: 是目前搜索引擎使用的一种关键技术。

       可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引

3.5 索引的数据结构

       数据库查询是数据库的主要功能之一,最基本的查询算法是顺序查找(linear search)时间复杂度为O(n),显然在数据量很大时效率很低。优化的查找算法如二分查找(binary search)、二叉树查找(binary tree search)等,虽然查找效率提高了。但是各自对检索的数据都有要求:二分查找要求被检索数据有序,而二叉树查找只能应用于二叉查找树上,但是数据本身的组织结构不可能完全满足各种数据结构(例如,理论上不可能同时将两列都按顺序进行组织)。所以,在数据之外,数据库系统还维护着满足特定查找算法的数据结构。这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法。这种数据结构就是索引。

       索引可以采用的数据结构有:

  • 二叉树
  • 红黑树
  • hash
  • B-Tree

       目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构,,为什么不采用其他的数据结构呢?

  • 索引为什么不用二叉搜索树?

       会出现极端情况,如 1、2、3、4、5,该情况下时间复杂度 O(n)

  • 索引为什么不用红黑树?

       数据量大的时候,深度也很大。

  • 索引为什么不用 hash?

       不能范围查询

       为什么B-Tree和B+Tree在被如此广泛用于索引?

3.5.1 B-Tree

       为了描述B-Tree,首先定义一条数据记录为一个二元组[key, data]key为记录的键值,对于不同数据记录,key是互不相同的;data为数据记录除key外的数据。那么B-Tree是满足下列条件的数据结构:

  1. d>=2,即B-Tree的度;
  2. hB-Tree的高;
  3. 每个非叶子结点由n-1keyn个指针组成,其中d<=n<=2d
  4. 每个叶子结点至少包含一个key和两个指针,最多包含2d-1key2d个指针,叶结点的指针均为NULL
  5. 所有叶结点都在同一层,深度等于树高h
  6. key和指针相互间隔,结点两端是指针;
  7. 一个结点中的key从左至右递增排列;
  8. 如果某个指针在结点node最左边且不为null,则其指向结点的所有key小于v(key1),其中v(key1)node的第一个key的值。
  9. 如果某个指针在结点node最右边且不为null,则其指向结点的所有key大于v(keym),其中v(keym)node的最后一个key的值。
  10. 如果某个指针在结点node的左右相邻key分别是keyikeyi+1且不为null,则其指向结点的所有key小于v(keyi+1)且大于v(keyi)

在这里插入图片描述

       例如一个度为dB-Tree,设其索引Nkey,则其树高h的上限为logd((N+1)/2),检索一个key,其查找结点个数的渐进复杂度为O(logdN)

3.5.2 B+Tree

       与B-Tree相比,B+Tree有以下不同点:

  • 每个结点的指针上限为2d而不是2d+1(指针个数和 key的个数相同)。
  • 非叶子结点不存储data,只存储key
  • 叶子结点不存储指针。

在这里插入图片描述

       一般来说,B+TreeB-Tree更适合实现外存储索引结构,具体原因与外存储器原理及计算机存取原理有关。

3.5.3 带有顺序访问指针的B+Tree

       一般在数据库系统或文件系统中使用的B+Tree结构都在经典B+Tree的基础上进行了优化,增加了顺序访问指针。

在这里插入图片描述

       在B+Tree的每个叶子结点增加一个指向相邻叶子结点的指针,就形成了带有顺序访问指针的B+Tree。做这个优化的目的是为了提高区间访问的性能,例如图4中如果要查询key为从1849的所有数据记录,当找到18后,只需顺着结点和指针顺序遍历就可以一次性访问到所有数据结点,极大提到了区间查询效率。

3.5.4 为什么使用B-Tree(B+Tree)

       一般来说,索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储的磁盘上。这样的话,索引查找过程中就要产生磁盘I/O消耗,相对于内存存取,I/O存取的消耗要高几个数量级,所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说,索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。

  • 预读:磁盘一般会顺序向后读取一-定长度的数据(页的整数倍)放入内存

  • 局部性原理:当一个数据被用到时,其附近的数据也通常会马上被使用

       B-Tree(B+Tree)点的大小设为等于一个页,每次新建节点直接申请一个页的空间,这样就保证一个节点物理上也存储在一个页里,就实现了一个节点的载入只需一次 I/0

       B+Tree相比于B-Tree的优点是其非叶子节点不存储Data,这样就导致每个非叶子能存储的索引更多,从而高度变得很低(3~5)。如此IO的次数便降低了很多,红黑树也正是因为这个原因没有选择。

3.6 聚簇索引与非聚簇索引

  • 聚簇索引:将数据存储与索引放到了一块,找到索引也就找到了数据

  • 非聚簇索引:将数据存储于索引分开结构,索引结构的叶子节点指向了数据的对应行,如果需要完整的数据,则还需要通过叶子节点再进行一次查询。

3.7 联合索引是什么

       MySQL可以使用多个字段同时建立一个索引,叫做联合索引。在联合索引中,如果想要命中索引,需要按照建立索引时的字段顺序挨个使用,否则无法命中索引。

       联合索引的使用需要最左前缀原理。

3.7.1 最左前缀原理

       假设现在建立了"name,age,school"的联合索引,那么索引的排序为: 先按照name排序,如果name相同,则按照age排序,如果age的值也相等,则按照school进行排序。

       当查询条件精确匹配索引的左边连续一个或几个列时,如或<name, school>,索引可以被用到,但是只能用到一部分,即条件所组成的最左前缀。但是如果是这样的使用,<age, school>,则无法使用到被创建的联合索引。

4. SQL 优化

CREATE TABLE employees (
		id int(11) NOT NULL AUTO_INCREMENT,
		name varchar(24) NOT NULL DEFAULT "" COMMENT '姓名',
		age int(11) NOT NULL DEFAULT 0 COMMENT '年龄',
		position varchar(20) NOT NULL DEFAULT "" COMMENT '职位',
		hire_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
		PRIMARY KEY (id),
		KEY idx_name_age_position (name, age, position) USING BTREE
) ENGINE= InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET =utf8 COMMENT='员工记录表';

INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager' ,NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('HanMeimei', 23,'dev' ,NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev' ,NOW());

4.1 全值匹配

在这里插入图片描述

4.2 最佳左前缀法则

       如果索引了多列,要遵守最左前缀法则。

       指的是查询从索引的最左前列开始并且不跳过索引中的列

在这里插入图片描述

4.3 不在索引上做任何操作

       (计算、函数、(自动or手动)类型转换),会导致索引失效而导致全表扫描。

在这里插入图片描述

4.4 存储引擎不能使用范围条件右边的列

在这里插入图片描述

4.5 尽量使用覆盖索引

       (只访问索引的查询(索引列包含查询列)),减少 select * 语句。

在这里插入图片描述

4.6 不使用不等于

       MySQL 使用不等于(!= 或者 <>)的时候无法使用索引会导致全表扫描。

在这里插入图片描述

4.7 不使用 is null、is not null

       is nullis not null无法使用索引。

在这里插入图片描述

4.8 Like 通配符不放到最左边

在这里插入图片描述

4.9 字符串不加单引号索引失效

在这里插入图片描述

4.10 少用 or 或 in

       用它查询时,非主键字段的索引会失效,主键索引有时生效,有时不生效,跟数据量有关。

在这里插入图片描述

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值