MySql基础知识点

最新推荐文章于 2022-11-03 16:54:43 发布

写代码的Z

最新推荐文章于 2022-11-03 16:54:43 发布

阅读量287

点赞数

分类专栏：数据库关于Java开发知识点系统整理文章标签： MySql

本文链接：https://blog.csdn.net/zy1994hyq/article/details/99286704

版权

关于Java开发知识点系统整理同时被 2 个专栏收录

30 篇文章 1 订阅

订阅专栏

数据库

8 篇文章 0 订阅

订阅专栏

常用的sql语句

条件查询、关联查询、排序查询、分组查询、逻辑关键字、函数

常用的数据类型

整数型和浮点数

类型	大小	用途
TINYINT	1 字节，8位	小整数值
SMALLINT	2 字节，2*8位	大整数值
MEDIUMINT	3 字节，3*8位	大整数值
INT或INTEGER	4 字节，4*8位	大整数值
BIGINT	8 字节，8*8位	极大整数值
FLOAT	4 字节，4*8位	单精度浮点数值
DOUBLE	8 字节，8*8位	双精度浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	小数值

日期和时间类型

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

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

TIMESTAMP类型有专有的自动更新特性，将在后面描述。

类型	大小 (字节)	格式	用途
DATE	3	YYYY-MM-DD	日期值
TIME	3	HH:MM:SS	时间值或持续时间
YEAR	1	YYYY	年份值
DATETIME	8	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

类型	大小	用途
CHAR	0-255字节	定长字符串
VARCHAR	0-65535 字节	变长字符串
TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符串
BLOB	0-65 535字节	二进制形式的长文本数据
TEXT	0-65 535字节	长文本数据
MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215字节	中等长度文本数据
LONGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295字节	极大文本数据

CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中会忽略大小写。

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

MySql存储引擎

MyIASM

优点：支持B+树索引和全文检索，查询效率高
缺点：不支持事务，使用表级锁。不支持外键。不支持Hash索引。
MyISAM的表锁有读锁和写锁(两个锁都是表级别)：表共享读锁和表独占写锁。在对MyISAM表进行读操作时，不会阻塞其他用户对同一张表的读请求，但是会阻塞其他用户对表的写请求；对其进行写操作时会阻塞对同一表读操作和写操作，MyISAM存储引擎的读锁和写锁是互斥的，读写操作是串行的。那么，一个进程请求某个MyISAM表的读锁，同时另一个进程也请求同一表的写锁，MySQL如何处理呢？答案是写进程先获得锁。不仅如此，即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁请求之前！这是因为MySQL认为写请求一般比读请求要重要。这也正是MyISAM表不太适合于有大量更新操作和查询操作应用的原因，因为，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。这种情况有时可能会变得非常糟糕！
通过指定启动参数low_priority_updates，使MyISAM引擎默认给予读请求以优先的权利。

InnoDB(5.5版本之后的默认引擎)

优点：支持ACID事务，支持外键，支持表级锁和行级锁，默认采用行级锁提高了并发效率。支持B+树索引、Hash索引、全文索引。
对于Innodb表，若要使用表锁，必须先设置autocommit=0且innodb_table_locks=1(默认)，否则InnoDB无法侦测表锁
缺点，由于默认采用行级锁虽然提高的并发效率，但是发生死锁的概率也变大了。

MyISAM索引与InnoDb索引的区别

Innodb中主键索引是聚簇索引，其他索引时非聚簇索引。Innodb的主键索引会存储行数据，非主键索引会存放该索引的数据和主键值。因此Innodb查询做到覆盖索引会非常高效，就算没有做到覆盖索引，也只需要两步操作就能得到行数据（根据非主键索引找到主键索引，再根据主键索引找到数据）。
MyISAM是非聚簇索引，索引存储行数据的地址，查询时找到索引位置后，还需要进行一次寻址操作才能得到数据。
疑问：根据上面的说法按理说Innodb的查询应该比MyISAM快，但是实际情况中为什么MyISAM的查询更快？
正式由于Innodb索引结构需要存放的数据更多，因此在实际查询的时候往往需要更多的磁盘IO操作，因为虽然从索引实现上看Innodb理论应该更快，但实际因为磁盘IO的原因导致MyISAM大多数查询时会更快，因为MyISAM的索引占用空间小，一次IO可以读取更多的索引数据。

MySql中的锁

根据锁粒度划分：

表锁：开销小，加锁快，不会出现死锁，但锁的力度比较大，发生锁冲突的概率高，因此并发访问效率低
行锁：开销大，加锁慢，有可能出现死锁，但锁的力度比较小，发生锁冲突的概率比较低，因此并发访问效率高。并发量高时如果出现查询缓慢或者很久不出结果，可以考虑是否出现死锁，排查服务器日志。

根据锁机制划分：

共享锁（读锁）：如果一个事务已经占有锁，那么其他的事务可以读但不能写。
排他锁（写锁）：如果一个事务已经占有锁，那么其他的事务不能读也不能写。

按功能划分：

读锁：读锁是一种共享锁。
写锁：写锁是一种排他锁。
读读不互斥，读写互斥，写写互斥

加锁的方式：

InnoDB默认采用行级锁。
可以使用SELECT ... LOCK IN SHARE MODE 显式加共享锁，如：SELECT * FROM tb_user WHERE user_id=1 LOCK IN SHARE MODE 显式加共享锁;
可以使用SELECT ... FOR UPDATE 显式加排他锁，如：SELECT * FROM tb_user WHERE user_id=1 FOR UPDATE 显式加排他锁
对于UPDATE、INSERT、DELETE语句InnoDB会自动给涉及的数据集加上排他锁
MyISAM默认采用表级锁。
MyISAM 在执行查询语句（SELECT）前，会自动给涉及的表加读锁
在执行更新操作UPDATE、DELETE、INSERT 等前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用 LOCK TABLES 命令给 MyISAM 表显式加锁。
在自动加锁的情况下，MyISAM 总是一次获得 SQL 语句所需要的全部锁，这也正是 MyISAM 表不会出现死锁（Deadlock Free）的原因。
可以通过LOCK TABALS ... Read [local]—可由多个会话同时获取，local是可选配置，用于MyISAM，允许并发插入。
可以通过LOCK TABLES ... [low_priority] write—阻止其他会话访问该表，通常写锁比读锁优先级要高，如果某表已经被添加读锁，此时其他会话申请写锁，则所有后续读锁请求会阻塞直至写锁释放，这样会影响读锁并发；
而low_priority则允许其他读锁优先执行，使用该选项必须谨慎，很容易导致写锁饥饿等待；
对于autocommit=0的innodb表，low_priority选项无效，视为普通写锁，会导致后续读锁请求等待

MySql中的索引

索引可以大幅增加数据库的查询性能。但是使用索引也是有代价的：

需要额外的磁盘空间保存索引。
对插入更新删除等操作，由于更新索引会增加额外的开销

因此索引比较适合用在读多写少的场景。

类型

唯一索引：保证索引列中的值是唯一的，允许出现空值
主键索引：是一种特殊的唯一索引，不允许出现空值
普通索引：与唯一索引不同，允许索引列中存在相同的值
联合索引：由多个列共同组成的索引，应用联合索引时要注意最左原则，即：WHERE查询条件中的字段必须与索引字段从左至右依次匹配，如果查询条件中没有用到联合索引的第一个字段，那么联合索引是不会生效的。出现范围查询（<、>、between、like）会体质匹配。
全文索引：只能在char，verchar，text类型上使用，底层使用倒排索引实现，要注意对于大数据量的表生成全文索引非常消耗时间也非常消耗磁盘空间。MySQL5.7.6之前自带的全文索引只能对英文进行全文检索，无法对中文进行全文检索。

索引的选择：对于唯一索引和普通索引，优先选择普通索引看这里

索引的数据结构/实现

B+树：比较适合用作大于或小于这样的范围查询，是MySql最常使用的一种索引实现
Hash：使用散列表来对数据进行索引，Hash方式不会像B-Tree那样需要查询多次才能定位到记录，因此Hash索引的效率比B-Tree高，但是不支持范围查找、排序等功能，实际使用也较少
FullText：全文索引，是一种记录关键字与对应文档关系的一种倒排索引
R-Tree：用于处理多维数据的数据结构，可以对地理数据进行空间索引，实际业务中使用较少

索引的使用原则或者说创建索引时需要注意的地方

查询常用的列
列数据变化较大的列
经常更新的列不适合（需要更多的磁盘空间，更新等操作会维护索引，因此会有更多的IO操作）
有外键或者叫有关联查询的列
重复数据较多不适合
大内容的不适合比如text（大内容在检索的时候会进行更多的磁盘IO）
列字段尽量设置为NOT NULL 不允许空值，使用了NULL的列很难进行查询优化，允许NULL会使索引，索引统计更加复杂，允许NULL的列需要更多的存储空间，还需要MySql内部进行特殊处理，因此，具体字段建议设置一个状态值代表NULL
索引不是一定能提高性能，因为索引也需要额外的空间和维护成本。新增更新删除操作会因为维护索引而进行跟多的IO操作。
百万级别或以上数据删除时，需要先删除索引，再删除数据。因为删除操作会维护索引而进行额外的磁盘IO操作。

MySql调优

开发人员主要掌握：

可以从表结构和索引进行优化
可以从sql进行优化
从MySql参数优化（简单了解）

表结构与索引优化原则

在设计表结构时，要考虑到数据库的扩展能力和数据量和读写量的增长，规划好分库分表方案，读写分离方案
为字段选择合适的数据类型，在满足业务逻辑的条件下优先选用占用空间小的数据类型。
将字段多的表分解成多个表，必要时增加中间表进行关联
可以轻量的反范式，适当的增加冗余字段，可以避免关联查询提高查询效率

sql优化

分析慢查询日志，寻找最需要优化的语句，慢查询的相关参数：slow_query_log（1表示开启，0表示关闭）。
利用分析工具：explain、profile 查看执行计划，使用方式如：
（1）EXPLAIN SELECT * FROM tb_user
EXPLAIN出来的信息有10列，分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra
说明：id:选择标识符，select_type:表示查询的类型，table:输出结果集的表，partitions:匹配的分区，type:表示表的连接类型，possible_keys:表示查询可能使用的索引，key:表示实际使用的索引，key_len:索引字段的长度，ref:列与索引的比较，rows:扫描出的行数(估算的行数)，filtered:按表条件过滤的行百分比，Extra:执行情况的描述和说明
（2）profile查看执行计划基本流程：
1）SHOW VARIABLES LIKE '%profiling%'查看是否开启profiling功能
2）SET profiling=1; //打开profile分析
3）执行具体sql;
5）SHOW profiles; //查看刚刚执行的sql的语句分析
6）SHOW profile CPU,BLOCK IO FOR query 1; //查看CPU、IO消耗
7）SET profiling=0; //关闭profile分析
尽量在有索引的字段上进行排序操作
优化sql语句这里

存储过程与函数

MySQL 的存储过程与函数都可以避免开发人员重复编写相同的 SQL 语句，并且存储过程和函数都是在 MySQL 服务器中执行的，可以减少客户端和服务器端的数据传输。

存储过程能够实现更复杂的功能，而函数一般用来实现针对性比较强的功能，例如特殊策略求和等。存储过程可以执行包括修改表等一系列数据库操作，而用户定义函数不能用于执行修改全局数据库状态的操作。

对比

存储过程一般独立的来执行,而函数往往是作为其他SQL语句的一部分来调用
sql语句中不能使用存储过程，但可以使用函数
存储过程一般与数据库实现绑定，使用存储过程会降低程序的可移植性，应谨慎使用。存储过程说白了就是把经常使用的SQL语句或业务逻辑封装起来,预编译保存在数据库中,当需要的时候从数据库中直接调用,省去了编译的过程. 提高了运行速度。