java_Mysql

Mysql重点部分汇总

 

什么是Mysql

MySQL是一个关系型数据库管理系统，在Java企业级开发中非常常用，因为 MySQL 是开源免费的，并且方便扩展。

关系型数据库（Relational Database，RDBMS）是一种基于关系模型的数据库，使用表结构来存储和组织数据。关系型数据库的特点包括：

1. 结构化：数据以表格形式存储，每个表格可以包含多个行和列，表格之间的数据可以通过键和约束进行关联。
2. 易于维护：关系型数据库有严格的完整性和约束规则，可以确保数据的一致性和可靠性。
3. 查询方便：关系型数据库支持 SQL 语言，可以进行复杂的查询和操作数据。
4. 数据冗余：为了保持数据的关联性，通常需要存储重复的数据。

非关系型数据库（Not-Only-SQL，NoSQL）是一种分布式、非关系型的数据库，通常采用键值对、文档、图形等结构存储数据。非关系型数据库的特点包括：

1. 分布式：非关系型数据库可以分布在多个节点上，能够更好地应对大规模数据处理和高并发访问。
2. 灵活性：非关系型数据库没有固定的结构，可以轻松应对数据结构的变化和扩展。
3. 性能：非关系型数据库通常具有更好的读写性能，尤其是在处理大量数据和高并发请求时。
4. 可扩展性：非关系型数据库可以通过增加节点或调整配置来扩展存储容量和性能。

两者的异同点如下：

1. 数据结构：关系型数据库以表格形式存储数据，非关系型数据库采用键值对、文档或图形等结构。
2. 数据一致性：关系型数据库通常具有较高的数据一致性和可靠性，而非关系型数据库通常采用最终一致性模型。
3. 查询语言：关系型数据库使用 SQL 语言进行查询和操作数据，非关系型数据库通常使用特定领域的查询语言或 API。
4. 数据操作：关系型数据库支持丰富的数据操作，如事务处理、联合、排序、聚合等，非关系型数据库通常支持基本的读写操作和聚合查询。
5. 扩展性：非关系型数据库通常更容易扩展，可以通过增加节点或调整配置来提高性能和容量，而关系型数据库通常需要更复杂的设计和架构来满足扩展需求。
6. 数据冗余：关系型数据库通常需要存储重复的数据以维护数据的关联性，而非关系型数据库通常没有这种需求。
7. 可靠性：关系型数据库通常具有较高的可靠性，因为它们具有严格的完整性和约束规则，而非关系型数据库可能没有这些规则的限制。

在选择使用哪种类型的数据库时，需要考虑应用场景、数据规模、数据一致性、可靠性等因素。

Mysql数据类型

分类	类型名称	说明
整数类型	tinyInt	很小的整数(8位二进制)
整数类型	smallint	小的整数(16位二进制)
整数类型	mediumint	中等大小的整数(24位二进制)
整数类型	int(integer)	普通大小的整数(32位二进制)
小数类型	float	单精度浮点数
小数类型	double	双精度浮点数
小数类型	decimal(m,d)	压缩严格的定点数
日期类型	year	YYYY 1901~2155
日期类型	time	HH:MM:SS -838:59:59~838:59:59
日期类型	date	YYYY-MM-DD 1000-01-01~9999-12-3
日期类型	datetime	YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00~ 9999-12-31 23:59:59
日期类型	timestamp	YYYY-MM-DD HH:MM:SS 19700101 00:00:01 UTC~2038-01-19 03:14:07UTC
文本、二进制类型	CHAR(M)	M为0~255之间的整数
文本、二进制类型	VARCHAR(M)	M为0~65535之间的整数
文本、二进制类型	TINYBLOB	允许长度0~255字节
文本、二进制类型	BLOB	允许长度0~65535字节
文本、二进制类型	MEDIUMBLOB	允许长度0~167772150字节
文本、二进制类型	LONGBLOB	允许长度0~4294967295字节
文本、二进制类型	TINYTEXT	允许长度0~255字节
文本、二进制类型	TEXT	允许长度0~65535字节
文本、二进制类型	MEDIUMTEXT	允许长度0~167772150字节
文本、二进制类型	LONGTEXT	允许长度0~4294967295字节
文本、二进制类型	VARBINARY(M)	允许长度0~M个字节的变长字节字符串
文本、二进制类型	BINARY(M)	允许长度0~M个字节的定长字节字符串

什么是三范式

• 第一范式：每个列都不可以再拆分。

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

• 第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。

Mysql常用函数

• count(*/column)：返回行数

• sum(column)： 返回指定列中唯一值的和

• max(column)：返回指定列或表达式中的数值最大值

• min(column)：返回指定列或表达式中的数值最小值

• avg(column)：返回指定列或表达式中的数值平均值

• date（Expression）: 返回指定表达式代表的日期值

Mysql的引擎

常用的引擎：

• Innodb引擎：Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。
• MyIASM引擎(原本Mysql的默认引擎)：不提供事务的支持，也不支持行级锁和外键。
• MEMORY引擎：所有的数据都在内存中，数据的处理速度快，但是安全性不高。

MyISAM与InnoDB区别

比较	MyISAM	Innodb
存储结构	每张表被存放在三个文件：frm-表格定义、MYD(MYData)-数据文件、MYI(MYIndex)-索引文件	所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB
存储空间	MyISAM可被压缩，存储空间较小	InnoDB的表需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引
可移植性、备份及恢复	由于MyISAM的数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作	免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了
文件格式	数据和索引是分别存储的，数据.MYD，索引.MYI	数据和索引是集中存储的，.ibd
记录存储顺序	按记录插入顺序保存	按主键大小有序插入
外键	不支持	支持
事务	不支持	支持
锁支持（锁是避免资源争用的一个机制，MySQL锁对用户几乎是透明的）	表级锁定	行级锁定、表级锁定，锁定力度小并发能力高
SELECT	MyISAM更优	--
INSERT、UPDATE、DELETE	--	InnoDB更优
select count(*)	myisam更快，因为myisam内部维护了一个计数器，可以直接调取。
索引的实现方式	B+树索引，myisam 是堆表	B+树索引，Innodb 是索引组织表
哈希索引	不支持	支持
全文索引	支持	不支持

MyISAM索引与InnoDB索引的区别？

• InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。
• InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效。
• MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。
• InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效。

InnoDB引擎的4大特性

• 插入缓冲（insert buffer)

• 二次写(double write)

• 自适应哈希索引(ahi)

• 预读(read ahead)

存储引擎选择

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

• MyISAM：以读写插入为主的应用程序，比如博客系统、新闻门户网站。

• Innodb：更新（删除）操作频率也高，或者要保证数据的完整性；并发量高，支持事务和外键。比如OA自动化办公系统。

为什么是B+树

• 索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。

• 索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。

 

事务

• 事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。事务是逻辑上的一组操作，要么都执行，要么都不执行。

• 原子性： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
• 一致性： 执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；
• 隔离性： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
• 持久性： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

什么是脏读？幻读？不可重复读？

• 脏读(Drity Read)：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。
• 不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。
• 幻读(Phantom Read):在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

SQL 标准定义了四个隔离级别：

• READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
• READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
• REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
• SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

注意：

• 这里需要注意的是：Mysql 默认采用的 REPEATABLE_READ隔离级别 Oracle 默认采用的 READ_COMMITTED隔离级别

• 事务隔离机制的实现基于锁机制和并发调度。其中并发调度使用的是MVVC（多版本并发控制），通过保存修改的旧版本信息来支持并发一致性读和回滚等特性。

• 因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是READ-COMMITTED(读取提交内容):，但是你要知道的是InnoDB 存储引擎默认使用 **REPEATABLE-READ（可重读）**并不会有任何性能损失。

• InnoDB 存储引擎在 分布式事务 的情况下一般会用到**SERIALIZABLE(可串行化)**隔离级别。

锁

从锁的类别上来讲，有共享锁和排他锁。

• 共享锁: 又叫做读锁。 当用户要进行数据的读取时，对数据加上共享锁。共享锁就是让多个线程同时获取一个锁。

• 排他锁: 又叫做写锁。 当用户要进行数据的写入时，对数据加上排他锁。排它锁也称作独占锁，一个锁在某一时刻只能被一个线程占有，其它线程必须等待锁被释放之后才可能获取到锁。排他锁只可以加一个，他和其他的排他锁，共享锁都相斥。

隔离级别与锁的关系

• 在Read Uncommitted级别下，读取数据不需要加共享锁，这样就不会跟被修改的数据上的排他锁冲突

• 在Read Committed级别下，读操作需要加共享锁，但是在语句执行完以后释放共享锁；

• 在Repeatable Read级别下，读操作需要加共享锁，但是在事务提交之前并不释放共享锁，也就是必须等待事务执行完毕以后才释放共享锁。

• SERIALIZABLE 是限制性最强的隔离级别，因为该级别锁定整个范围的键，并一直持有锁，直到事务完成。

按照锁的粒度分数据库锁有哪些？锁机制与InnoDB锁算法

• 在关系型数据库中，可以按照锁的粒度把数据库锁分为行级锁(INNODB引擎)、表级锁(MYISAM引擎)和页级锁(BDB引擎 )。

什么是死锁？怎么解决？

• 死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。

• 常见的解决死锁的方法

  • 1、如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。
  • 2、在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
  • 3、对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；

如果业务不好处理,可以用分布式事务锁或者使用乐观锁

乐观锁适用于写比较少的情况下（多读场景） 

一般多写的场景下用悲观锁就比较合适

视图

触发器

• 触发器是用户定义在关系表上的一类由事件驱动的特殊的存储过程。触发器是指一段代码，当触发某个事件时，自动执行这些代码。

使用场景

• 可以通过数据库中的相关表实现级联更改。
• 实时监控某张表中的某个字段的更改而需要做出相应的处理。
• 例如可以生成某些业务的编号。
• 注意不要滥用，否则会造成数据库及应用程序的维护困难。
• 大家需要牢记以上基础知识点，重点是理解数据类型CHAR和VARCHAR的差异，表存储引擎InnoDB和MyISAM的区别。

在MySQL数据库中有如下六种触发器：

• Before Insert
• After Insert
• Before Update
• After Update
• Before Delete
• After Delete

存储过程

存储过程是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需要创建一次，以后在该程序中就可以调用多次。

优点

1. 存储过程是预编译过的，执行效率高。
2. 存储过程的代码直接存放于数据库中，通过存储过程名直接调用，减少网络通讯。
3. 安全性高，执行存储过程需要有一定权限的用户。
4. 存储过程可以重复使用，减少数据库开发人员的工作量。

缺点

1. 调试麻烦，但是用 PL/SQL Developer 调试很方便！弥补这个缺点。
2. 移植问题，数据库端代码当然是与数据库相关的。但是如果是做工程型项目，基本不存在移植问题。
3. 重新编译问题，因为后端代码是运行前编译的，如果带有引用关系的对象发生改变时，受影响的存储过程、包将需要重新编译（不过也可以设置成运行时刻自动编译）。
4. 如果在一个程序系统中大量的使用存储过程，到程序交付使用的时候随着用户需求的增加会导致数据结构的变化，接着就是系统的相关问题了，最后如果用户想维护该系统可以说是很难很难、而且代价是空前的，维护起来更麻烦。

SQL优化

如何知道需要优化：

1. 查看SQL的执行时间
2. 使用explain关键字可以模拟优化器执行SQL查询语句，从而知道MYSQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。

3. 开启慢查询日志。开启慢sql日志；配置慢sql时间；导出慢sql文件。

 优化：

• 有外键约束的话会影响增删改的性能，如果应用程序可以保证数据库的完整性那就去除外键
• Sql语句全部大写，特别是列名大写，因为数据库的机制是这样的，sql语句发送到数据库服务器，数据库首先就会把sql编译成大写在执行，如果一开始就编译成大写就不需要了把sql编译成大写这个步骤了
• 如果应用程序可以保证数据库的完整性，可以不需要按照三大范式来设计数据库
• 其实可以不必要创建很多索引，索引可以加快查询速度，但是索引会消耗磁盘空间
• 如果是jdbc的话，使用PreparedStatement不使用Statement，来创建SQl，PreparedStatement的性能比Statement的速度要快，使用PreparedStatement对象SQL语句会预编译在此对象中，PreparedStatement对象可以多次高效的执行

 

• 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引
• 用索引可以提高查询
• SELECT子句中避免使用*号，尽量全部大写SQL
• 应尽量避免在 where 子句中对字段进行 is null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，使用 IS NOT NULL
• where 子句中使用 or 来连接条件，也会导致引擎放弃使用索引而进行全表扫描
• in 和 not in 也要慎用，否则会导致全表扫描
• 最左匹配原则。
• 尽可能多的走索引查询

大表数据查询，怎么优化

1. 优化shema、sql语句+索引；
2. 第二加缓存，memcached, redis；
3. 主从复制，读写分离；
4. 垂直拆分，根据你模块的耦合度，将一个大的系统分为多个小的系统，也就是分布式系统；
5. 水平切分，针对数据量大的表，这一步最麻烦，最能考验技术水平，要选择一个合理的sharding key, 为了有好的查询效率，表结构也要改动，做一定的冗余，应用也要改，sql中尽量带sharding key，将数据定位到限定的表上去查，而不是扫描全部的表；

优化查询过程中的数据访问 

优化长难的查询语句

优化特定类型的查询语句

优化关联查询

优化子查询

优化LIMIT分页

优化UNION查询

优化WHERE子句

数据库优化

数据库结构优化：

1. 将字段很多的表分解成多个表
2. 增加中间表
3. 增加冗余字段

 分库分表：

垂直分区

根据数据库里面数据表的相关性进行拆分；把一张列比较多的表拆分为多张表。

• 垂直拆分的优点： 可以使得行数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。

• 垂直拆分的缺点： 主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决。此外，垂直分区会让事务变得更加复杂；

垂直分表

• 把主键和一些列放在一个表，然后把主键和另外的列放在另一个表中。

 缺点：

• 有些分表的策略基于应用层的逻辑算法，一旦逻辑算法改变，整个分表逻辑都会改变，扩展性较差
• 对于应用层来说，逻辑算法增加开发成本
• 管理冗余列，查询所有数据需要join操作

水平分区

保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。 水平拆分可以支撑非常大的数据量。

• 水平拆分能够 支持非常大的数据量存储，应用端改造也少，但 分片事务难以解决 ，跨界点Join性能较差，逻辑复杂。

水平分表

• 表很大，分割后可以降低在查询时需要读的数据和索引的页数，同时也降低了索引的层数，提高查询次数

水平切分的缺点

• 1、给应用增加复杂度，通常查询时需要多个表名，查询所有数据都需UNION操作
• 2、在许多数据库应用中，这种复杂度会超过它带来的优点，查询时会增加读一个索引层的磁盘次数

 

数据库分片的两种常见方案：

• 客户端代理： 分片逻辑在应用端，封装在jar包中，通过修改或者封装JDBC层来实现。 当当网的 Sharding-JDBC 、阿里的TDDL是两种比较常用的实现。
• 中间件代理： 在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。 我们现在谈的 Mycat 、360的Atlas、网易的DDB等等都是这种架构的实现。

 读写分离，主从复制

方案一

• 使用mysql-proxy代理

• 优点：直接实现读写分离和负载均衡，不用修改代码，master和slave用一样的帐号，mysql官方不建议实际生产中使用

• 缺点：降低性能， 不支持事务