【mysql]】要点总结1

目录

1.SQL语言

2.事务的四个特性及含义

3.drop,delete与truncate的区别

4.Mysql存储引擎

5.关系型和非关系型数据库

6.sql优化

---

1.SQL语言

数据定义(DDL)：数据库、表、视图、索引、存储过程、触发器等的创建和删除。

数据操纵(DML)：数据的查询和存取等操作。如： Select、Insert、Update、Delete

数据控制(DCL)：事务管理、存取权限等。

数据查询（DQL）

2.事务的四个特性及含义

数据库事务transanction：ACID

原子性(Atomicity)、一致性(Correspondence)、隔离性(Isolation)、持久性(Durability)。

原子性: 要么全部完成，要么全部不完成。

一致性: 在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。

隔离性: 两个事务，运行在相同的时间内，执行 相同的功能，每一事务在系统中认为只有该事务在使用系统。

持久性: 事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中。

3.drop,delete与truncate的区别

同：都会删除表内的数据

异：

drop	truncate	delete
删除表库，释放占用空间	清除表数据并重置id	删除表数据
删除表结构、数据	删除表数据	删除表数据
DDL,执行后会自动提交	DDL,执行后会自动提交	DML,不会自动提交

4.Mysql存储引擎

• MySQL 支持哪些存储引擎，‘show engines;’
• MySQL的底层结构是B+树，最后一层是叶子节点的单链表结构，可以快速查询数据。
• MySQL使用的是硬盘来进行数据存储，存储位置查找：‘show variables like ‘datadir’’。

• 存储引擎的物理存储的方式：

            转载：一文带你看懂 MySQL 存储引擎_mysql存储引擎-CSDN博客

InnoDB采用聚簇索引来存储数据，包含.frm和.ibd文件，其中索引和数据都保存在ibd文件当中，表的数据行存放在对应的叶子节点上，查询速度比非聚簇索引要快。(5.5 及以后版本后，MySQL 选择使用 InnoDB为默认存储引擎。)

特点：  叶子节点包含了完整的记录行

聚集索引（聚簇索引）；

不支持全文检索；

表级锁、行级锁（默认）；

必须有主键（如果我们没有明确去指定创建主键索引。它会帮我们隐藏的生成一个 6 byte 的 int 型的索引作为主键索引）；

硬盘存储方式为.frm文件存储表结构，ibd文件存储数据内容；

InnoDB 支持事务操作；（每一条SQL都默认封装成事务，自动提交，会影响速度）
InnoDB 支持外键；；
InnoDB 不保存表的总条数。

MyISAM采用非聚簇索引，包含.frm和.MYD.MYI文件,其中索引和数据是分开存储的，表的数据行对应的叶子节点存放的是数据块的索引指针，最后再根据索引获取数据。

特点： 非聚簇索引的叶子节点只有索引字段和主键ID。

非聚集索引；

支持全文索引；

表级锁，不支持行级锁；

主键不是必须的；

硬盘存储方式为.frm文件存储表结构，.MYD文件存储数据内容，.MYI文件存储索引文件。

MyISAM 有一个变量专门来保存整个表的行数，不能加 where 条件
MyISAM 压缩后可进行查询操作，节省空间容量；

5.关系型和非关系型数据库

关系型数据库主要是通过表格模型方式存储，

好处：数据库事务使用易维护、表格和sql条件的查询易使用；

缺点：网站并发量高时，出现每秒上万次的请求，对磁盘i/o读写压力大。

非关系型数据库主要是通过键值key-value的形式存储，

好处：支持高并发和读写，通过key实现快速查询，数据库可扩展性（数据之间没有耦合性），不受数据库ACID约束，每一条数据都是原子性。

6.sql优化

 1.创建时优化

创建表时的数据类型优化：使用对用的数据类型，整型尽量使用TinyInt，浮点使用Decimal，字符串使用char和varchar定长，时间DateTime等。

查询时优化：查询相关的行和列，提升查询时间，例如

2.减少使用select *

3.尽量使用union all 代替union

union获取的排重后的数据，需要经历遍历、排序、比较等步骤才能得到结果，比较废cpu

4.in和exist使用场景

in：会先执行右侧子查询的语句，然后查出来来作为条件再执行左侧的表，推荐是左大右小

exist:会先执行左侧的sql作为条件，再去执行右侧表，推荐左小右大

4.减少数据库的读写次数

        对于批量插入或者修改使用batch的方法

5.limit 

--  offset：从第几行开始，注意：初始行为0。
--  rows：返回具体行数。
select * from table_A limit [offset，] rows

-- 查询前10行记录
select * from table_A limit 10;

-- 从第6行开始，查询10行记录
select * from table_A limit 5 ，10;

---

备注union

union 去重相同数据

union all 多表查询的全部数据