20190714 Mysql系列教程笔记

数据库引擎

用于存储、处理和保护数据的核心服务。Mysql支持多种数据库引擎。如果没有选择数据库引擎,那么创建数据库的默认引擎是什么?

创建表的时候要指定数据库引擎,不同的引擎对表会有不同的影响。对两种引擎的认识。 对应数据库中的锁机制。

两种数据库引擎:Innodb引擎,MYISAM引擎。

 

数据库驱动

数据库驱动是不同数据库开发商(比如oracle mysql等)为了某一种开发语言环境(比如java)能够实现统一的数据库调用而开发的一个程序,他的作用相当于一个翻译人员,将Java语言中对数据库的调用语言通过这个翻译翻译成各个种类的数据库自己的数据库语言,当然这个翻译(数据库驱动)是由各个开发商针对统一的接口自定义开发的。

java语言和mysql之间的桥梁。 没有数据库驱动包,怎么访问数据库?

 

 

数据库

添加数据源:数据库的部分。数据源已经准备好了。

列的字符集和数据表的字符集以及排序规则,否者会出现乱码。

Mysql 的内置函数。

数据表的建立:列的字符集和新建表的字符集。

 

创建数据表的时候,没有指定字符集,导致汉字存不进去,sql执行失败。

 

-- 在mysql中,查询某字段为空时,切记不可用 = null,

-- 而是 is null,不为空则是 is not null

select * from table where column is null;

 

SELECT * FROM employee GROUP BY sex;

按照性别分组,有几种性别就显示几行数据

SELECT id AS '编号',username AS '用户名',COUNT(*) AS '员工总数',SUM(salary) AS '总薪水',MAX(s

alary) AS '最高薪水',MIN(salary) AS '最低薪水',AVG(salary) AS '平均薪水' FROM employee;

使用HAVING 对分组结果进行二次筛选。

按照性别分组,并找到分组后组中人数大于3的组。

 

在配置文件注意修改有三处

(1)数据库连接的信息:驱动类、连接地址、用户名、密码

(2)生成POJO类,Mapper 映射文件和mapper接口的包名

(3)指定当前数据库中对那几个数据表进行生成

 

模型之间的关系。

十几个查询条件的sql 语句  spring boot与spring cloud的区别是什么?

什么是分表分库?什么是定时任务?

熟悉表结构,要不然无法写mybatis这一层

 

子查询:

将子查询的结果构造成一个表 sb,并从中进行第二步的筛选。

在子查询中 SELECT 的每一列都必须有一个独一无二的名字。

 

mysql才是最重要的。

今天的主要任务是。

所以一般字符集用utf-8,排序规则用utf8_general_ci

 

如果设定数据库字段为非空,就要给定默认值(否则建表不成功)

如何把时间类型设置为当前的默认时间:给时间列设置默认值。

1、将字段类型设为  TIMESTAMP 

2、将默认值设为  CURRENT_TIMESTAMP

设置数据表的字符集,排序规则,注释。

 

在mysql中,多表连接查询是很常见的需求,在使用多表查询时,可以from多个表,也可以使用join连接连个表.这两种查询有什么区别?哪种查询的效率更高呢? 

在mysql中使用FROM查询多表和使用JOIN连接(LEFT JOIN,RIGHT JOIN除外),查询结果,查询效率是一样的(相当于内连接查询)

 

mysql配置文件

/etc/my.cnf

cat my.cnf

二进制日志 log-bin

错误日志log-error

查询日志log

frm文件存放的是表结构

myd存放表数据

myi存放表索引

 

mysql逻辑架构

MySQL架构总共三层:连接层,核心服务层,引擎层,存储层(把文件存储到磁盘上,并完成与存储引擎的交互,mysql的数据是存储在磁盘文件上的;为了提高性能,应该尽量减少磁盘IO。尽量避免全表扫描,把所有的数据加载到内存中,每次加载都是一次IO操作。

核心服务层:optimizer优化器

MySQL区别于其他数据库的最重要的特点就是其插件式的表存储引擎。

存储引擎是基于表的,而不是数据库(每张数据表的存储引擎可能不一样)。每种引擎具备不同的特点。不同的场景使用不同的数据表存储引擎。

数据库的工作流程:连接管理模块,线程管理模块,缓存模块,解析器,查询优化器,SQL Interface。

计算机科学中著名的局部性原理,磁盘预读。

 

mysql存储引擎

两个引擎的核心区别是什么?

是否支持事务?表锁与行锁?适用场景?myisam不适合高并发,innodb适合高并发操作。缓存:只缓存索引,不缓存真实数据;两者都缓存,对内存要求较高,而且内存大小对性能有决定性影响。

 

SQL性能下降原因

执行时间长,等待时间长

1、查询语句写的烂

2、索引失效(没建立索引):单值索引,复合索引。

3、关联查询太多join(设计缺陷或者不得已的需求),七八张表关联。

4、服务器调优及各个参数的设置(缓冲,线程数等)

create index idx_表名_字段名(给某张表的某一个字段建立索引,索引名)

create index idx_user_name on user(name) 单值索引

create index idx_user_nameEmail on user(name,email) 复合索引

drop index idx_user_name on 表名

show index from 表名

建立索引的目的是提高查询速度。

到底对数据表的哪一列建立索引?

 

SQL执行加载顺序

手写:select...from...join...on...where...group by...having...order by...limit...

机读:from...on...where...group by...having...select...distinct...order by...limit

先找到表,查询条件,,然后select。从from开始。

having子句只能分组后使用。

select以后才能去除重复

 

7种JOIN理论(7种连接查询理论)

inner join...on...

where b.key is Null 相当于是加了一个where条件。

 

索引是什么?

mysql索引分类

mysql索引结构

哪些情况需要创建索引?哪些情况不需要创建索引?

索引是帮组mysql高效获取数据的数据结构。建立索引的目的是提高查询效率,就像字段的目录。

排好序的快速查找数据结构(排好序,查找快)。

索引会影响你where后面的查找和排序。

数据库系统还维护着满足特定查找算法的数据结构。b+Tree索引.二叉查找树。建立索引其实就是建立一个二叉查找树(每个节点包含两部分内容:索引键值(索引编号)和数据所在物理地址的指针)。

对每个资源进行编号,通过编号找到资源,还可能涉及到二分法。

 

在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法。这种数据结构,就是索引。

为表设置索引要付出代价的:一是增加了数据库的存储空间,二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动)。频繁删改的字段不适合建立索引。

一般来说,索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储在磁盘上。

 

索引优势劣势

1、提高数据检索效率,降低数据库的io成本;

2、建立索引的时候会对列的数据进行排序,降低数据库的排序成本,降低CPU的消耗。

1、虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行 INSERT、UPDATE 和DELETE。因为更新表时,MySQL 不仅要保存数据,还要保存一下索引文件。

2、建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件的会膨胀很快。

 

索引分类和建立索引命名语句

单值索引:一个索引只包含单个列,一个表可以有多个单列索引。一张表最好不要建太多索引。

唯一索引:索引列的值必须唯一,但允许有空值。

复合索引:一个索引包含多个列。

 

索引结构与检索原理

BTree索引,哈希索引,full-text全文索引,RTree索引。

把磁盘文件加载到内存中,这是一次IO操作。

真实的情况是,3层的B+树可以表示上百万的数据,如果上百万的数据查找只需要三次IO,性能提高将是巨大的;如果没有索引,每个数据项都要发生一次IO,那么总共需要上百万次的IO,性能消耗非常高。

 

哪些情况适合建立索引?

1、主键会自动建立唯一索引。

2、频繁作为查询条件的字段应该创建索引。银行卡号与身份证号。一般对查询条件建立索引。建立索引可以简单理解为对某一列的数据进行排序。索引不需要显示进行调用。

3、查询中与其他表中关联的字段,外键关系建立索引。

4、频繁更新的字段不适合建立索引,因为每次更新,不仅仅是更新记录还会更新索引。

5、where条件里面,用不到的字段不需要创建索引

6、单键索引和组合索引的选择问题,who?(在高并发情况下倾向创建组合索引)

7、查询中排序的字段,排序字段若是通过索引去访问,将大大提高排序速度。

8、查询中统计或者分组的字段。

 

哪些情况不适合建立索引?

1、表记录太少,不太适合建索引,比如1万条记录(表记录比较多的时候才适合建立索引)

如何测试建立索引和不建立索引的区别?

2、经常增删改的表

3、如果某个数据列包含许多重复的内容,为它建立索引就没有太大的实际效果。数据的差异率不太高,建索引就没有意义。比如性别列。

索引的选择性:索引列中不同值得数目与表中记录数的比。

如何计算索引的选择性?一个索引的选择性越接近1,这个索引的效率就越高。

 

性能分析前提知识

性能衡量标准

1)mysql query optimizer 查询优化分析器,提供最优的执行计划。

2)mysql常见瓶颈:CPU,IO,服务器硬件的性能瓶颈。CPU饱和一般发生在数据加载进内存或者从磁盘读取数据的时候;磁盘IO瓶颈,一般发生在装载数据远大于内存容量的时候。

3)explain

 

Explain使用简介

是什么?查看某条sql的执行计划。

能做什么?使用Explain关键字可以模拟优化器执行sql查询语句,从而知道mysql是如何帮你处理sql语句的。分析你的查询语句或者表结构的性能瓶颈。查询优化器。

 

执行计划包含的信息:各个字段的含义

1、表的读取顺序,(表的加载顺序)

2、数据读取操作的操作类型

3、哪些索引可以使用

4、哪些索引被实际使用

5、表之间的引用

6、每张表有多少行被优化器查询。

id,select_type,table,type,

 

id介绍:

select查询的序列号,包含一组数字,表示查询中执行sql子句或操作表的顺序,按照id顺序操作表。id相同,执行顺序从上到下,相当于是表的加载顺序。

select子句的执行过顺序。id越大优先级越高,越先被执行。

 

select_type:

主要是区分查询类型

MySQL将 select 查询分为简单查询和复杂查询。复杂查询分为三类:简单子查询、派生表(from语句中的子查询)、union 查询。

DERIVED:衍生查询,FROM 子查询(临时表)。

PRIMARY:复杂查询中最外层的select(主查询,最后执行的查询),

SIMPLE:简单查询,不包含子查询和联合查询。

SUBQUERY:子查询,含在 select 中的子查询(不在 from 子句中) 

 

table:显示这一行的数据是关于哪张表的。

sql的其中type:即MySQL决定如何查找表中的行。

all,index,range,ref,eq_ref,const,system,Null

从最好到最差的连接类型为system、const、eq_reg、ref、range、indexhe和ALL,一般来说,好的sql查询至少达到range级别,最好能达到ref(sql好坏的评价标准)

总共七种type类型。

all:表明你的检索是全表扫描,全表扫描有时候是避免不了的。

const:如果将主键置于where列表中,mysql就能将该查询转换为一个const(where的查询条件)常量级

eq_ref:只能找到一条记录与之匹配

ref:索引要和某个值相比较,可能会找到多个符合条件的行。

range:使用一个索引来检索给定范围的行。

index:index是从索引中读取,而ALL是从硬盘读取。相当于某一列建立了索引。

 

possible_keys:这一列表示 查询可能使用哪些索引来查找。 是否使用了索引?mysql认为索引对此查询帮助不大,选择了全表查询。 

key:这一列表示mysql实际采用哪个索引来优化对该表的访问。如何查看索引是否失效?key值为null,要么没有建立索引,要么索引失效。

覆盖索引:select后面查询字段的个数和顺序,与建立复合索引的个数和顺序一致。

key_len:表示索引中使用的字节数,可以通过该列计算查询中使用的索引的长度。在不损失精度的情况下,长度越短越好。

ref:查询条件中的常量值,查询条件引用的字段。显示索引的哪一列被使用了。

rows:根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数

Using filesort:mysql无法利用索引完成的排序操作成为“文件排序”;如果出现了文件排序,要尽快进行优化。

查询的列要被所建的索引覆盖。

sql语句中()的优先级还是很高的。

 

索引单表优化案例

如何进行单表索引优化?

如何查询views(查看做多的)article_id ? 使用排序,然后limit 1即可。

 

小表驱动大表,对大的表建立索引。

连接查询:被驱动的字段,要建立索引。

优化SQL查询语句来避免索引失效。

字符串不加单引号,会导致索引失效。索引有查找和排序两种作用。

 

在java开发中索引是如何使用的?

如何比较建立索引前后查询性能的提升?

懂原理,能写SQL,今天都不一定能够结束。

如何查询索引的内容?或者对哪一列建立了索引?

 

根据查询条件建立组合索引。索引对应的列的字段。如何避免文件排序?

什么情况下,才会导致文件排序?

如何避免文件排序功能?

left join条件用于确定如何从右表搜索行,左边一定都有,所以右表是我们的关键点,一定需要建立索引。

 

慢查询的解决方法:

1)在生产环境,看看慢sql的情况,(慢查询的开启并捕获)

2)开启慢查询日志,设置阈值,比如超过5秒就是慢sql,将它抓取出来

3)使用explain+慢sql分析

4)show profile(查询sql在mysql服务器里面的执行细节和声明周期情况)

5)运维进行sql数据库服务器的参数调优。(sql数据库服务器参数调优)

如何开启慢查询日志?

 

函数有返回值,存储过程没有返回值。

创建存储过程;调用存储过程。

 

-- 子查询不能为列设置别名,不然就会找不到数据

-- from 后面接两张表是什么意思?

-- 子连接查询,把一张表当做两张表来用。

-- 对表起别名,那么所有的字段前都要添加别名。哪怕是聚合函数。

-- 如何进行去重?

-- 如何判断数据是否为空?

 

四种隔离级别

索引

Eureka.client.service-url.default-zone:

操作系统原理

算法和数据结构

 

从一个表中选出最后几个元素

Limit关键字的使用?

 

Group By

“Group By”从字面意义上理解就是根据“By”指定的规则对数据进行分组,所谓的分组就是将一个“数据集”划分成若干个“小区域”,然后针对若干个“小区域”进行数据处理。

注:group by语句中select指定的字段必须是“分组依据字段”,其他字段若想出现在select中则必须包含在聚合函数中。

 

having

having子句可以让我们筛选成组后的各种数据,where子句在聚合前先筛选记录,也就是说作用在group by和having子句前。而 having子句在聚合后对组记录进行筛选。

 

insert into dept(dname,db_source) values('技术部',DATABASE())

 

select * from dept

 

-- 创建部门表

CREATE TABLE IF NOT EXISTS department(

id TINYINT UNSIGNED AUTO_INCREMENT KEY,

depName VARCHAR(20) NOT NULL UNIQUE

)

-- 添加部门

INSERT department(depName) VALUES('开发部');

INSERT department(depName) VALUES('视频部');

INSERT department(depName) VALUES('教学部');

INSERT department(depName) VALUES('运营部');

-- 创建员工表

CREATE TABLE IF NOT EXISTS employee(

id Int UNSIGNED AUTO_INCREMENT KEY,

username VARCHAR(20) NOT NULL,

age TINYINT UNSIGNED DEFAULT 18,

addr VARCHAR(50) NOT NULL DEFAULT '北京',

salary FLOAT(6,2) NOT NULL DEFAULT 0,

sex ENUM('男','女','保密'),

depId TINYINT UNSIGNED

);

 

INSERT employee(username,age,addr,salary,sex,depId) VALUES('张三','21','山东','5432.12','男',1);

INSERT employee(username,age,addr,salary,sex,depId) VALUES('李四','32','河北','6432.00','男',2);

INSERT employee(username,age,addr,salary,sex,depId) VALUES('王五','26','北京','5932.92','女',3);

INSERT employee(username,age,addr,salary,sex,depId) VALUES('赵六','32','上海','6232.14','男',4);

INSERT employee(username,age,addr,salary,sex,depId) VALUES('Mr Adword','55','美国','9432.99','男',4);

INSERT employee(username,age,addr,salary,sex,depId) VALUES('田七','19','北京','4932.92','保密',1);

INSERT employee(username,age,addr,salary,sex,depId) VALUES('孙八','62','上海','9932.14','男',2);

INSERT employee(username,age,addr,salary,sex,depId) VALUES('Mr lili','45','美国','9132.99','女',1);

 

select * from department

select * from employee

insert into department(depName) values('行政部')

SELECT * from employee GROUP BY sex

-- 按照性别进行分组并且查询每个分组中的人数

select * ,COUNT(*) as '每组人数' from employee GROUP BY depid

-- 按照性别进行分组,并且查询每个分组中用户的姓名

SELECT *,GROUP_CONCAT(username)from employee GROUP BY sex

-- 查询用户以及所属部门(关联查询:两张表必须要有关联才行)

-- 把两张表的所有数据都查出来了,不符合要求啊?

select username as '用户名' ,depName as '所属部门' from employee e LEFT JOIN department dep on e.depId=dep.id

select * from employee e LEFT JOIN department dep on e.depId=dep.id

-- 分组配合聚合函数使用

-- 统计员工表中员工数目,以及薪水的总和、最大值、最小值、平均值

select COUNT(*) as '员工数目',SUM(salary) as '薪水总和',max(salary) as '最大值',min(salary) as '最小值',AVG(salary) as '平均值' from employee

-- 按照性别分组,查询每个分组中的...

select sex, COUNT(*) as '员工数目',SUM(salary) as '薪水总和',max(salary) as '最大值',min(salary) as '最小值',AVG(salary) as '平均值' from employee GROUP BY sex

-- 使用HAVING 对分组结果进行二次筛选

-- 按照性别分组,并找到分组后组中人数大于3的组

select * from employee GROUP BY sex HAVING COUNT(*)> 3

 

-- 连接查询

-- 关联查询sql编写的思路,1先确定所连接的表,2再确定所要查询的字段,3确定连接条件以及连接方式

-- 如何理解内连接(两个条件都需要满足,只查询在连接的表中能够有对应的记录)

select e.username as '用户姓名',dept.depName as '所在部门' from employee e INNER JOIN department dept on e.depId=dept.id

 

-- 左外连接查询

-- 指以左边的表的数据为基准,去匹配右边的表的数据,如果匹配到就显示,匹配不到就显示为null。

-- 左表,也就是基准表,用基准表的数据去匹配右表的数据;所以左表的记录是全部会查询出来的,如果右表没有记录对应的话就显示null

select * from employee emp LEFT JOIN department dep on emp.depId=dep.id

 

-- 右连接查询(连接查询可能存在一对多的情况,一个部门对应多个员工)

select * from department dep LEFT JOIN employee emp on dep.id=emp.depId

 

-- 自连接查询

-- 自连接查询就是当前表与自身的连接查询,关键点在于虚拟化出一张表给一个别名。

-- 例如:查询员工以及他的上司的名称,由于上司也是员工,所以这里虚拟化出一张上司表

-- left join ...ON...

select emp.username as '员工名', boss.username as '上司名',emp.* from employee emp left join employee boss on emp.bossId=boss.id

 

数据库总结:

1、为什么查询数据库的时候一直爆语法错误啊?  同时执行了太多行的查询。每次只能执行一个查询。

  1. 2、什么是分表分库?如何在spring中进行配置。一个数据库切分成多个部分放到不同的数据库(server)上,从而缓解单一数据库的性能问题。不太严格的讲,对于海量数据的数据库,如果是因为表多而数据多,这时候适合使用垂直切分,即把关系紧密(比如同一模块)的表切分出来放在一个server上。如果表并不多,但每张表的数据非常多,这时候适合水平切分,即把表的数据按某种规则(比如按ID散列)切分到多个数据库(server)上。

垂直切分(分成多个数据库)和水平切分(分成多张设计表)。

3、返回受影响的行数。

 

Mysql报错:The driver has not received any packets from the server.

数据库服务挂掉了,导致连接超时。

 

右键单击数据表----à转存sql文件---à仅结构。执行即可(导出开发环境的建表语句,在正式的linux环境中运行即可)。

 

事务的特性

A    Atomicity         原子性,事务中的操作将会被视为一个整体不可分割的部分,要么都成功要么都失败.

C    Consistency           一致性,事务执行前和执行后 数据的一致性应得以保证.例如tom账户减去100,相应的jerry账户就多出100;

I     Isolation          隔离性,当同时存在多个事务的时候,事务之间应该不收干扰.

D   Durability         持久性,事务一旦提交,数据会持久化到保存介质上.

 

锁是计算机协调多个进程或纯线程并发访问某一资源的机制。

在数据库中,除传统的计算资源(CPU、RAM、I/O)的争用以外,数据也是一种供许多用户共享的资源。

如何保证数据并发访问的一致性、有效性是所在有数据库必须解决的一个问题,锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说,锁对数据库而言显得尤其重要,也更加复杂。

 

使用锁可以对有限的资源进行保护,解决隔离和并发问题。

 

相对其他数据库而言,MySQL的锁机制比较简单,其最显著的特点是不同的存储引擎支持不同的锁机制。

MySQL大致可归纳为以下3种锁:

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

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

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

分类标准:开销,加锁速度,死锁,粒度,并发性能。

 

锁的分类

1、从对数据库操作的类型分,分为读锁和写锁 

读锁(共享锁):针对同一份数据,多个读操作可以同时进行而不会互相影响 

写锁(排它锁):当前写操作没有完成前,它会阻断其他写锁和读锁

2、从对数据操作的粒度分,分为表锁和行锁

 

场景:表锁偏读,行锁偏写

 

查看表上加过的锁

show open tables 查看数据库中表的加锁情况,1:表被锁定了。

手动增加表锁

lock table employee read,depart write;

删除表锁

unlock tables

读锁:这把锁是谁加的?

总结:谁加的锁,谁负责。当前用户添加读锁,会阻塞其他用户写操作,当前表只能读不能写操作。

写锁:会阻塞其他用户对表的任何操作。当前用户只能对表进行增删改操作,不能进行查询操作。

Mysql的读写锁有什么区别?

如何分析表锁定?

如何关掉数据库引擎的事务,如何开启 set autocommit=1?

事务并发问题?

索引失效,行锁变表锁。

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值