MySQL基础知识点

MySQL 是最流行的关系型数据库管理系统在 WEB 应用方面 MySQL 是最好的 RDBMS(Relational Database Management System:关系数据库管理系统)应用软件之一。

关系型数据库管理系统(RDBMS)来存储和管理大数据量。所谓的关系型数据库,是建立在关系模型基础上的数据库,借助于集合代数等数学概念和方法来处理数据库中的数据。

RDBMS 术语

  • 数据库: 数据库是一些关联表的集合。
  • 数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
  • 列: 一列(数据元素) 包含了相同类型的数据, 例如邮政编码的数据。
  • 行:一行(=元组,或记录)是一组相关的数据,例如一条用户订阅的数据。
  • 冗余:存储两倍数据,冗余降低了性能,但提高了数据的安全性。
  • 主键:主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
  • 外键:外键用于关联两个表。
  • 复合键:复合键(组合键)将多个列作为一个索引键,一般用于复合索引。
  • 索引:使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
  • 参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件,目的是保证数据的一致性。
创建数据库:CREATE DATABASE 数据库名;   创建数据表:CREATE TABLE table_name (column_name column_type);

删除数据库:drop database <数据库名>;  删除数据表DROP TABLE table_name ;

MySQL支持多种类型,大致可以分为三类:数值、日期/时间和字符串(字符)类型。

插入数据表:

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );

查询数据:

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]
  • 查询语句中你可以使用一个或者多个表,表之间使用逗号(,)分割,并使用WHERE语句来设定查询条件。
  • SELECT 命令可以读取一条或者多条记录。
  • 你可以使用星号(*)来代替其他字段,SELECT语句会返回表的所有字段数据
  • 你可以使用 WHERE 语句来包含任何条件。
  • 你可以使用 LIMIT 属性来设定返回的记录数。
  • 你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。

更新数据:

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

删除指定记录:

DELETE FROM table_name [WHERE Clause]
  • 如果没有指定 WHERE 子句,MySQL 表中的所有记录将被删除。
  • 你可以在 WHERE 子句中指定任何条件
  • 您可以在单个表中一次性删除记录。

like语句:

SELECT field1, field2,...fieldN 
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

SQL LIKE 子句中使用百分号 %字符来表示任意字符,类似于UNIX或正则表达式中的星号 *。

如果没有使用百分号 %, LIKE 子句与等号 = 的效果是一样的。

排序:以下是 SQL SELECT 语句使用 ORDER BY 子句将查询数据排序后再返回数据:

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]
  • 你可以使用任何字段来作为排序的条件,从而返回排序后的查询结果。
  • 你可以设定多个字段来排序。
  • 你可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下,它是按升序排列。
  • 你可以添加 WHERE...LIKE 子句来设置条件。

GROUP BY 语法

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

连接JOIN在两个或多个表中查询数据。 按照功能大致分为如下三类:

  • INNER JOIN(内连接,或等值连接):获取两个表中字段匹配关系的记录。
  • LEFT JOIN(左连接):获取左表所有记录,即使右表没有对应匹配的记录。
  • RIGHT JOIN(右连接): 与 LEFT JOIN 相反,用于获取右表所有记录,即使左表没有对应匹配的记录。

MySQL支持的数据类型:

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC),以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。

日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR

每个时间类型有一个有效值范围和一个"零"值,当指定不合法的MySQL不能表示的值时使用"零"值。

字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET

MySQL 事务

MySQL 事务主要用于处理操作量大,复杂度高的数据。比如说,在人员管理系统中,你删除一个人员,你既需要删除人员的基本资料,也要删除和该人员相关的信息,如信箱,文章等等,这样,这些数据库操作语句就构成一个事务!

  • 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
  • 事务处理可以用来维护数据库的完整性,保证成批的 SQL 语句要么全部执行,要么全部不执行。
  • 事务用来管理 insert,update,delete 语句

一般来说,事务是必须满足4个条件(ACID)::原子性(Atomicity,或称不可分割性)、一致性(Consistency)、隔离性(Isolation,又称独立性)、持久性(Durability)。

  • 原子性:一个事务(transaction)中的所有操作,要么全部完成,要么全部不完成,不会结束在中间某个环节。事务在执行过程中发生错误,会被回滚(Rollback)到事务开始前的状态,就像这个事务从来没有执行过一样。

  • 一致性:在事务开始之前和事务结束以后,数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则,这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

  • 隔离性:数据库允许多个并发事务同时对其数据进行读写和修改的能力,隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别,包括读未提交(Read uncommitted)读提交(read committed)、可重复读(repeatable read)和串行化(Serializable)。

  • 持久性:事务处理结束后,对数据的修改就是永久的,即便系统故障也不会丢失。

在 MySQL 命令行的默认设置下,事务都是自动提交的,即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION,或者执行命令 SET AUTOCOMMIT=0,用来禁止使用当前会话的自动提交。

MYSQL 事务处理主要有两种方法:

1、用 BEGIN, ROLLBACK, COMMIT来实现

  • BEGIN 开始一个事务
  • ROLLBACK 事务回滚
  • COMMIT 事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式:

  • SET AUTOCOMMIT=0 禁止自动提交
  • SET AUTOCOMMIT=1 开启自动提交

MySQL性能优化:

1. 为查询缓存优化你的查询

大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候,这些查询结果会被放到一个缓存中,这样,后续的相同的查询就不用操作表而直接访问缓存结果了。

这里最主要的问题是,对于程序员来说,这个事情是很容易被忽略的。因为,我们某些查询语句会让MySQL不使用缓存。请看下面的示例:

1

2

3

4

5

6

// 查询缓存不开启

$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");

 

// 开启查询缓存

$today = date("Y-m-d");

$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");

上面两条SQL语句的差别就是 CURDATE() ,MySQL的查询缓存对这个函数不起作用。所以,像 NOW() 和 RAND() 或是其它的诸如此类的SQL函数都不会开启查询缓存,因为这些函数的返回是会不定的易变的。所以,你所需要的就是用一个变量来代替MySQL的函数,从而开启缓存

2、2. EXPLAIN 你的 SELECT 查询

使用 EXPLAIN 关键字可以让你知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。

EXPLAIN 的查询结果还会告诉你你的索引主键被如何利用的,你的数据表是如何被搜索和排序的……等等,等等。

挑一个你的SELECT语句(推荐挑选那个最复杂的,有多表联接的),把关键字EXPLAIN加到前面。你可以使用phpmyadmin来做这个事。然后,你会看到一张表格。下面的这个示例中,我们忘记加上了group_id索引,并且有表联接:

3、当只要一行数据时使用 LIMIT 1

4. 为搜索字段建索引

索引并不一定就是给主键或是唯一的字段。如果在你的表中,有某个字段你总要会经常用来做搜索,那么,请为其建立索引吧。

5. 在Join表的时候使用相当类型的例,并将其索引

***面试题***”

MySQL主备同步的基本原理:

MYSQL主从同步原理:
1) MYSQL主从同步是异步复制的过程,整个同步需要开启3线程,master上开启bin-log日志(记录数据库增、删除shu、修改、更新操作);
2) Slave开启I/O线程来请求master服务器,请求指定bin-log中position点之后的内容;
3) Master端收到请求,Master端I/O线程响应请求,bin-log、position之后内容返给salve;
4) Slave将收到的内容存入relay-log中继日志中,生成master.info(记录master ip、bin-log、position、用户名密码);
5) Slave端SQL实时监测relay-log日志有更新,解析更新的sql内容,解析成sql语句,再salve库中执行;
6) 执行完毕之后,Slave端跟master端数据保持一致!

MySQL存储引擎区别、索引种类、查询较慢如何分析?

MyISAM存储引擎

1、MyISAM 是MySQL (mysql 5.5版本以前) 原来的默认存储引擎.

2、MyISAM   这种存储引擎不支持事务,不支持行级锁,只支持并发插入的表锁。

3、MyISAM   类型的表支持三种不同的存储结构:静态型、动态型、压缩型。

4、MyISAM也是使用B+tree索引但是和Innodb的在具体实现上有些不同。

InnoDB存储引擎

(1)MySQL默认存储引擎(MySQL 5.5 版本后).

(2)innodb 支持事务,回滚以及系统崩溃修复能力和多版本迸发控制的事务的安全。

(3)innodb 支持自增长列(auto_increment),自增长列的值不能为空,(一个表只允许存在一个自增,并且要求自增列必须为索引)

(4)innodb 支持外键(foreign key) ,外键所在的表称为子表,而所依赖的表称为父表。

(5)innodb存储引擎支持行级锁。

(6)innodb存储引擎索引使用的是B+Tree

补充3点:

    1.大容量的数据集时趋向于选择Innodb。因为它支持事务处理和故障的恢复。Innodb可以利用数据日志来进行数据的恢复。主键的查询在Innodb也是比较快的。

    2.大批量的插入语句时(这里是INSERT语句)在MyIASM引擎中执行的比较的快,但是UPDATE语句在Innodb下执行的会比较的快,尤其是在并发量大的时候。

    3.两种引擎所使用的索引数据结构是什么?

        答案:都是B+树!

重点[面试题]:

innodb与MyISAM存储引擎的区别:
  1.innodb 是mysql5.5版本以后的默认存储引擎, 而MyISAM是5.5版本以前的默认存储引擎.
  2.innodb 支持事物,而MyISAM不支持事物
  3.innodb 支持行级锁.而MyIASM 它支持的是并发的表级锁.
  4.innodb 支持外键, 而MyIASM 不支持外键
  5.innodb与MyIASM存储引擎都采用B+TREE存储数据, 但是innodb的索引与数据存储在一个文件中,这种方式我们称之为聚合索引. 
    而MyIASM则会单独创建一个索引文件,也就是说,数据与索引是分离开的
  6.在效率方面MyISAM比innodb高,但是在性能方面innodb要好一点.

InnoDB存储引擎既支持行级锁(row-level locking),也支持表级锁,但默认情况下是采用行级锁。

MySQL这3种锁的特性可大致归纳如下。

开销、加锁速度、死锁、粒度、并发性能

l         表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。

l         行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。

l         页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。

从上述特点可见,很难笼统地说哪种锁更好,只能就具体应用的特点来说哪种锁更合适!仅从锁的角度来说:表级锁更适合于以查询为主,只有少量按索引条件更新数据的应用,如Web应用;而行级锁则更适合于有大量按索引条件并发更新少量不同数据,同时又有并发查询的应用,如一些在线事务处理(OLTP)系统。

 索引方法:

1. B+TREE 索引 

B+树是一种经典的数据结构,由平衡树二叉查找树结合产生,它是为磁盘或其它直接存取辅助设备而设计的一种平衡查找树,在B+树中,所有的记录节点都是按键值大小顺序存放在同一层的叶节点中,叶节点间用指针相连,构成双向循环链表,非叶节点(根节点、枝节点)只存放键值,不存放实际数据。

1、B+树的层级更少:相较于B树B+每个非叶子节点存储的关键字数更多,树的层级更少所以查询数据更快;

2、B+树查询速度更稳定:B+所有关键字数据地址都存在叶子节点上,所以每次查找的次数都相同所以查询速度要比B树更稳定;

3、B+树天然具备排序功能:B+树所有的叶子节点数据构成了一个有序链表,在查询大小区间的数据时候更方便,数据紧密性很高,缓存的命中率也会比B树高。

4、B+树全节点遍历更快:B+树遍历整棵树只需要遍历所有的叶子节点即可,,而不需要像B树一样需要对每一层进行遍历,这有利于数据库做全表扫描。

红黑树为什么他配不上MySql索引?

​ 其实从算法逻辑来讲,二叉树的查找速度和比较次数都是最小的,但是数据文件存储的位置是再磁盘,这不得不考虑一个现实的问题,磁盘IO(磁盘IO的存取次数就是评价一个数据库索引优劣的关键性指标), 利用索引查询数据的时候,逐一加载磁盘页到内存当中,每一页对应索引树的节点.如果拿红黑树作为索引会出现什么情况呢?

查询数据的时候每比较一遍,都会进行一次磁盘IO,在最坏的情况下比较的次数就是树的高度,而且节点能存放的数据又少得可怜,所以又高又瘦的红黑树并不适合作为索引.

 索引需要的是减少磁盘IO的次数以及节点存储更多的数据量.

红黑树(Red&Black Tree)除了符合BST的特性外,其的节点会分出一个存储位来标记其颜色(标颜色是为了更好地通过颜色相关的规则来检验树的平衡性),比BST多出如下的附加特性:

​ 1.节点是红色或黑色。

​ 2.根节点是黑色。

​ 3.每个叶子节点都是黑色的空节点(NIL节点)。

​ 4 每个红色节点的两个子节点都是黑色。(从每个叶子到根的所有路径上不能有两个连续的红色节点)

​ 5.从任一节点到其每个叶子的所有路径都包含相同数目的黑色节点。

什么是B树

B树(Balance Tree)是一种多路平衡查找树(多路:每个节点的子节点可以多于两个),一个m阶的B树具有如下几个特征(阶:中所有孩子结点个数的最大值称为该树的阶):

​ 1.根结点至少有两个子女。

​ 2.每个中间节点都包含k-1个元素和k个孩子,其中 m/2 <= k <= m

​ 3.每一个叶子节点都包含k-1个元素,其中 m/2 <= k <= m

​ 4.所有的叶子结点都位于同一层。

​ 5.每个节点中的元素从小到大排列,节点当中k-1个元素正好是k个孩子包含的元素的值域分划。

什么是B+树

B+树(B+ Tree)是B树的plus版本,在数据库应用中有着比B树更高的查询性能,他俩有许多共同点,但是B+树在B树基础上具备一些新的特征,一个m阶的B+树具有如下几个特征:

​ 1.有k个子树的中间节点包含有k个元素(B树中是k-1个元素),每个元素不保存数据,只用来索引,所有数据都保存在叶子节点。

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

​ 3.所有的中间节点元素都同时存在于子节点,在子节点元素中是最大(或最小)元素。

值得注意的是:根节点的最大元素就是B+树的最大元素,以后无论插入删除多少元素都要保证最大的元素在根节点当中. 叶子不仅包含了B+树的全量元素信息,并且每个叶子都带有指向下一个节点的指针,形成一个有序链表:

B+树还具有一个特点,那就是卫星数据的位置处于最下层,那什么是卫星数据呢?

卫星数据:索引元素所指向的数据记录,比如说数据库的某一行,在B树中,无论中间节点还是叶子节点都带有卫星数据.

而在B+树中只有叶子节点带有卫星数据,其余节点仅仅是索引而没有数据关联.

 在节点占用空间恒定的情况下,B+树的度更大,单个节点的元素更多。比如同样节点大小,B树的每个节点有100个元素+卫星数据,B+树的每个节点就可以装下1000个元素,数据存于最下层。这样的好处就是树的高度降低同时让节点有更多的元素。(度:每个节点的存储元素个数)

综合来看,B+树相较于B树的优势有三个:

​ 1.单一节点存储更多的元素,更少的IO次数

​ 2.所有查询都要查找到叶子节点,查询性能更稳定

​ 3.所有叶子节点形成有序链表,范围查询更简单

 2. HASH 索引

hash就是一种(key=>value)形式的键值对,允许多个key对应相同的value,但不允许一个key对应多个value,为某一列或几列建立hash索引,就会利用这一列或几列的值通过一定的算法计算出一个hash值,对应一行或几行数据.   hash索引可以一次定位,不需要像树形索引那样逐层查找,因此具有极高的效率.

MySQL中常见索引有:

  • 普通索引:加速查询
  • 唯一索引:加速查询 和 唯一约束(可含一个null 值)
  • 主键索引:加速查询 和 唯一约束(不可含null)
  • 组合索引:组合索引是将n个列组合成一个索引  。其应用场景为:频繁的同时使用n列来进行查询,如:where n1 = 'alex' and n2 = 666。

慢查询优化

将mysql服务器中影响数据库性能的相关SQL语句记录到日志文件,通过对这些特殊的SQL语句分析,改进以达到提高数据库性能的目的。

慢查询日志参数:

long_query_time     :  设定慢查询的阀值,超出设定值的SQL即被记录到慢查询日志,缺省值为10s
slow_query_log      :  指定是否开启慢查询日志
log_slow_queries    :  指定是否开启慢查询日志(该参数已经被slow_query_log取代,做兼容性保留)
slow_query_log_file :  指定慢日志文件存放位置,可以为空,系统会给一个缺省的文件host_name-slow.log
log_queries_not_using_indexes: 如果值设置为ON,则会记录所有没有利用索引的查询.

#.查询慢日志配置信息 :
show variables like '%query%';
#.修改配置信息
set global slow_query_log  = on;

# 显示参数  
show variables like '%log_queries_not_using_indexes';
# 开启状态
set global log_queries_not_using_indexes  = on;

#查看慢日志记录的方式
show variables like '%log_output%';

#设置慢日志在文件和表中同时记录
set global log_output='FILE,TABLE';
#查看表中的日志
select * from mysql.slow_log;

已标记关键词 清除标记
相关推荐
课程简介: 历经半个多月的时间,Debug亲自撸的 “企业员工角色权限管理平台” 终于完成了。正如字面意思,本课程讲解的是一个真正意义上的、企业级的项目实战,主要介绍了企业级应用系统中后端应用权限的管理,其中主要涵盖了六大核心业务模块、十几张数据库表。 其中的核心业务模块主要包括用户模块、部门模块、岗位模块、角色模块、菜单模块和系统日志模块;与此同时,Debug还亲自撸了额外的附属模块,包括字典管理模块、商品分类模块以及考勤管理模块等等,主要是为了更好地巩固相应的技术栈以及企业应用系统业务模块的开发流程! 核心技术栈列表: 值得介绍的是,本课程在技术栈层面涵盖了前端和后端的大部分常用技术,包括Spring Boot、Spring MVC、Mybatis、Mybatis-Plus、Shiro(身份认证与资源授权跟会话等等)、Spring AOP、防止XSS攻击、防止SQL注入攻击、过滤器Filter、验证码Kaptcha、热部署插件Devtools、POI、Vue、LayUI、ElementUI、JQuery、HTML、Bootstrap、Freemarker、一键打包部署运行工具Wagon等等,如下图所示: 课程内容与收益: 总的来说,本课程是一门具有很强实践性质的“项目实战”课程,即“企业应用员工角色权限管理平台”,主要介绍了当前企业级应用系统中员工、部门、岗位、角色、权限、菜单以及其他实体模块的管理;其中,还重点讲解了如何基于Shiro的资源授权实现员工-角色-操作权限、员工-角色-数据权限的管理;在课程的最后,还介绍了如何实现一键打包上传部署运行项目等等。如下图所示为本权限管理平台的数据库设计图: 以下为项目整体的运行效果截图: 值得一提的是,在本课程中,Debug也向各位小伙伴介绍了如何在企业级应用系统业务模块的开发中,前端到后端再到数据库,最后再到服务器的上线部署运行等流程,如下图所示:
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页