MySQL的个人总结

最新推荐文章于 2024-07-25 16:06:56 发布

wangchh1988

最新推荐文章于 2024-07-25 16:06:56 发布

阅读量433

点赞数

分类专栏：数据库文章标签： db mysql

本文链接：https://blog.csdn.net/wangchh1988/article/details/102825481

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

MYSQL

部分常用命令

查看数据库版本:select version(); mysql --version;
删除数据库:
- drop table table_name; 表结构和数据全部删除
- truncate table table_name; 删除表中的数据，但是索引、表结构等都保留;
- delete from table_name where column_name = ‘value’;删除具体行的行数据；
  效率而言 drop > truncate > delete，因为delete是数据库操作语言(dml),会放到rollback segement中，事物提交之后才生效；
  而drop和truncate是数据库定义语言(ddl)，操作后会立即生效，truncate实际上是drop和re-create两步；
查看数据库编码:show variables like ‘character%’;
查看数据库表中的索引：show index from table_name;
查看数据库表结构: describe table_name;

简介

官网上的一句话:mysql是世界上最流行的开源数据库管理系统(the most popular open source sql database management system),
现在是由oracle公司开发和支持。
MySQL是数据库管理系统；
MySQL数据库是关系型数据库；
MySQL软件是开源的；
MySQL服务器是快速、可靠、可扩展且易于使用；
MySQL服务是以C/S模式或者嵌入式模式运行；

SQL：Structured Query Language
MySQL: 是以一位联合创始人的女儿的名字；
MySQL的Logo是一只小海豚，它的名字是Sakila(来源于举行的一场给海豚命名的活动的获胜者)

特性

支持的数据类型有哪些？及其应用场景

参考
总的来说，MySQL支持的类型分为四种:Numeric types、Date and Time Types、String Types and Spatial Types。

Numeric Types

extract value

类型	存储(bytes)	无符号最大值	最小值	最大值
TINYINT	1	255	-128	127
SMALLINT	2	65535	-32768	32767
MEDIUMINT	3	16777215	-8388608	8388607
INT	4	4294967295	-2147483648	2147483647
BIGINT	8	2^64 - 1	-2^63	2^63 - 1

上面是整型的数据，还有两个固定小数的类型Decimal和Numeric类型

approximate value

类型	存储(bytes)
FLOAT	4
DOUBLE	8

bit value

Bit类型可以定义的长度为1-64位，如果长度不够就会左侧补零
上面所有的类型如果超出了定义的最大值都会报out-of-range的错误

Date and Time Types

类型	存储	格式	范围
datetime	8 bytes	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
timestamp	4 bytes	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:01 ~ 2038
date	3 bytes	YYYY-MM-DD	1000-01-01 ~ 9999-12-31
year	1 bytes	YYYY	1901 ~ 2155

String types

char and varChar

区别：char定义多少位在数据库中就占据多少位，varchar是根据存储的实际长度存储；char定义的最大长度是255，
而varchar可以定义的最大长度是65535；下面的例子是说明存储空间的问题:

Value	CHAR(4)	Storage Required	VARCHAR(4)	Storage Required
‘’	’ ’	4 bytes	‘’	1 byte
‘ab’	'ab ’	4 bytes	‘ab’	3 bytes
‘abcd’	‘abcd’	4 bytes	‘abcd’	5 bytes
‘abcdefgh’	‘abcd’	4 bytes	‘abcd’	5 bytes

binary and varbinary

和char与varchar类似，但是区别在于binary保存的是字节而char保存的是字符，

Blob and text

是放大版的binary和char，分别有TINYBLOB/BLOB/MEDIUMBLOB/LONGBLOB、TINYTEXT/TEXT/MEDIUMTEXT/LONGTEXT

ENUM Type

CREATE TABLE shirts (
    name VARCHAR(40),
    size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);
INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),
  ('polo shirt','small');
SELECT name, size FROM shirts WHERE size = 'medium';

set type

就是值只能从一个集合中选择零个或者多个

CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));

mysql> INSERT INTO myset (col) VALUES 
-> ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

spatial type

为了支持空间上的数据结构而引入的，包括POINT/LINESTRING/GEOMETRY/POLYGON

函数及使用方法

提供了一系列的函数
和操作符

定义及操作语法

分为数据定义语言、数据操作语言、事物和锁声明、复刻声明、预处理sql声明、合成语法、数据库管理声明和实用声明

数据定义语言(Data Definition Language-DDL)

主要包含：alter/create/drop/rename/truncate
操作的对象包括:database/event/function/procedure/table/view
语法
eg:

ALTER TABLE t2 DROP COLUMN c, DROP COLUMN d;

数据库操作语言(Data Manipulation Language-DML)

主要包含：insert/delete/update/select/call/do/handler/load/replace/subquery
语法
数据库操作语言和数据库定义语言的最大区别就是:执行流程不同，数据库操作语言是需要支持事物的，故一定是将执行结果备份，
但是数据库定义语言是不用支持事物的，一旦执行立即生效(这也就是为什么truncate比delete全表要快)；

8.0的新特性

InnoDb的加强
数据类型的加强，比如JSON类型
去掉了utf8mb3编码，默认的数据库编码类型由latin改为utf8mb4;

数据引擎

mysql支持的数据引擎有InnoDB、MyISAM、MEMORY、CAS、ARCHIVE等10多种存储引擎，当时最常用的是InnoDB和MyISAM，
其中InnoDB是5.6之后版本的默认存储引擎。各引擎的差距主要在支持查询、数据存储格式及数据压缩和支持事物等上的差距。

这是mysql的基本架构图，可以分为三个部分：在这里插入图片描述

其中引擎(InnoDB)的结构图如下
在这里插入图片描述
主要分为两个部分:内存结构和磁盘结构，这部分是跟MySQL的性能优化有很大关系的，我们这次重点在应用层面上(也就是sql的编写上)

先看一下如果MySQL要执行一条查询语句，它的整个过程是怎样的：

在MySQL的服务端进行语句解析优化等操作生成执行命令；
根据执行命令调用存储引擎的API；
引擎根据调用的api进行数据的查找和读取；
将数据集合返回给客户端；

如果要优化语句的执行时间，那么可以根据步骤分为server服务端的优化(添加优化器等)和存储引擎的优化(数据查找和读取)；
其中存储引擎的优化:数据查找的优化、数据读取优化

数据查找的优化:
1. 添加数据的缓存；
2. 增加数据的索引；
数据读取的优化:
1. 采用B+树的存储结构减少IO的次数；
2. 采用预读取等减少机械消耗(局部性原理:当一条数据被用到时，其周围的数据也会被马上用到)；

那为什么采用B+树的存储结构就可以减少IO的次数呢，这其中涉及到磁盘的读取原理、B+树的数据结构、

磁盘读取存取原理

在这里插入图片描述
上面是磁盘逻辑图，是由盘片和磁头组成，系统要读取某位置上的数据时会向磁盘发出数据逻辑地址，磁盘按照寻址逻辑将逻辑地址转换为物理地址，
即确定数据在哪个扇区、哪个磁道，为了读取到扇区里的数据，磁头需要移动到对应的磁道，这个过程叫做寻道，所消耗的时间叫寻道时间；
盘片需要需要将对应的扇区旋转到磁头，这个过程消耗时间叫做旋转时间。
由于存储介质的特性，磁盘存取本身就比内存读取要慢的多，再加上机械运动消耗，磁盘读取的速度一般为内存读取的几百分之一，因此为了提高效率，
应该尽量减少磁盘IO，

B+树的数据结构

在这里插入图片描述
上面是B树的数据结构，
使用阶来定义 B 树，一棵 m 阶的 B 树，需要满足下列条件：

每个节点最多拥有m个子节点且m>=2，空树除外
除根节点外每个节点的关键字数量大于等于ceil(m/2)-1，小于等于m-1
所有叶子节点均在同一层、叶子节点除了包含了关键字和关键字记录的指针外也有指向其子节点的指针只不过其指针地址都为null对应下图最后一层节点的空格子
如果一个非叶节点有n个子节点，则该节点的关键字数等于n-1
所有节点关键字是按递增次序排列，并遵循左小右大原则

注：

m阶代表一个树节点最多有多少个查找路径，m阶=m路,当m=2则是2叉树，m=3则是3叉。
ceil()是个朝正无穷方向取整的函数，如ceil(1.1)结果为2，即向上取整。

在这里插入图片描述
与B树相比，B+树有以下区别:

m阶B+树表示了内部结点最多有m-1个关键字，阶数m同时限制了叶子结点最多存储m-1个记录。
B+Tree叶子节点保存了父节点的所有关键字和关键字记录的指针，每个叶子节点的关键字从小到大链接;
内节点不存储data，只存储key；叶子节点不存储指针。因此所有数据地址必须要到叶子节点才能获取到，所以每次数据查询的次数都一样;

那综合上述，数据库查询优化即为:

B树的查找复杂度为树的深度h，?(ℎ)=?(log??)。其中d是每个节点存储的数据量；（一般使用磁盘I/O次数评价索引结构的优劣。先从B-Tree分析，根据B-Tree的定义，可知检索一次最多需要访问h个节点。
数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。
为了达到这个目的，在实际实现B-Tree还需要使用如下技巧：每次新建节点时，直接申请一个页的空间，
这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。
B-Tree中一次检索最多需要h-1次I/O（根节点常驻内存），渐进复杂度为?(ℎ)=?(log??)。
一般实际应用中，出度d是非常大的数字，通常超过100，因此h非常小（通常不超过3）。综上所述，用B-Tree作为索引结构效率是非常高的）
由于B+Tree内节点去掉了data域，因此可以拥有更大的出度，容纳更多的节点，能够有效减少磁盘IO次数。
每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的B+Tree。(
做这个优化的目的是为了提高区间访问的性能，例如图4中如果要查询key为从18到49的所有数据记录，当找到18后，
只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率。)

参考引用

应用场景

场景的特点

MySQL使用的场景；
使用MySQL的限制，比如单条数据的大小

如果采用其他数据库的效果

各个数据库的使用场景；
- hbase：
  1. 读写比例低的场景，hbase比较适合于写密集型的应用；
  2. 不需要复杂的查询条件，由于只支持rowKey上添加索引，所以单条或者小范围查找是可以的，
    但由于存储是分布式的原因，大范围查找就比较受影响了；
  3. 数据量巨大(百亿行+百万列)，对性能和可靠性非常高的场景(几十万qps)；
- MySQL：
  1. 读写比例高的场景；
  2. 需要支持事物的场景；
  3. 有多表联查的场景；
  4. 用户可以自定义查询语句sql;
  5. 同时支持值查询和范围查询；
- MongoDB:
  1. 数据模型无法确定的场景-文档型json数据格式存储；
  2. 单条数据量大的场景(最大16M，相比mysql单条6635bytes);
  3. 数据结构复杂，会有数组或者嵌套等场景；

采用数据库一般从开发、性能、运维三个角度去综合衡量去解决应该采用哪个数据库；
当开发一个web网站中使用频率最高就是mysql，所以也就会让开发效率提高，运维主要考虑当数据量变大时所采用的数据库是否支持扩展，
性能主要是看业务场景是读多还是写多，数据量的级别对是否有性能的影响
那根据我们的场景，我们这选择了mysql作为基本信息的存储数据库，将文件解析的文档内容存储在mongoDB中；

mongoDB的存储结构

HBase的存储结构

优化使用

根据职责(developer or DBA)不同，优化可以分为sql语句优化和数据库优化

sql语句优化

索引的使用

索引的类型：

覆盖索引(covering index)：如果select中的字段和where字段都在一个索引中，那么mysql可以不去查整条row数据来得到结果

使用索引的情况

todo

不使用索引的情况,那应该修改为怎么写才合适

todo

需要平常注意的情况

必须使用UTF8字符集
和DBA负责人确认后，纠正为“新库默认使用utf8mb4字符集”。
默认使用这个字符集的原因是：“标准，万国码，无需转码，无乱码风险”，并不“节省空间”。
数据表、数据字段必须加入中文注释
禁止使用存储过程、视图、触发器、Event
禁止使用外键，如果有外键完整性约束，需要应用程序控制
禁止大表使用JOIN查询，禁止大表使用子查询
只允许使用内网域名，而不是ip连接数据库
缓存（memcache、redis）的连接，服务（service）的连接都必须使用内网域名，机器迁移/平滑升级/运维管理…太多太多的好处;
禁止使用小数存储国币(见上面浮点型可能出现的问题)
使用“分”作为单位，这样数据库里就是整数了。
禁止使用属性隐式转换。
案例：SELECT uid FROM t_user WHERE phone=13812345678 会导致全表扫描，而不能命中phone索引
SELECT uid FROM t_user WHERE phone=’13812345678’
禁止使用负向查询NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等，会导致全表扫描。
必须使用InnoDB存储引擎。
禁止存储大文件或者大照片。
为何要让数据库做它不擅长的事情？大文件和照片存储在文件系统，数据库里存URI多好。
库名、表名、字段名：小写，下划线风格，不超过32个字符，必须见名知意，禁止拼音英文混用。
表名t_xxx，非唯一索引名idx_xxx，唯一索引名uniq_xxx。
单实例表数目必须小于500。
单表列数目必须小于30。
表必须有主键，例如自增主键。
a）主键递增，数据行写入可以提高插入性能，可以避免page分裂，减少表碎片提升空间和内存的使用
b）主键要选择较短的数据类型， Innodb引擎普通索引都会保存主键的值，较短的数据类型可以有效的减少索引的磁盘空间，提高索引的缓存效率
c）无主键的表删除，在row模式的主从架构，会导致备库夯住
禁止使用外键，如果有外键完整性约束，需要应用程序控制。
外键会导致表与表之间耦合，update与delete操作都会涉及相关联的表，十分影响sql 的性能，甚至会造成死锁。高并发情况下容易造成数据库性能，大数据高并发业务场景数据库使用以性能优先
单表索引建议控制在5个以内
单索引字段数不允许超过5个
字段超过5个时，实际已经起不到有效过滤数据的作用了
禁止在更新十分频繁、区分度不高的属性上建立索引
更新会变更B+树，更新频繁的字段建立索引会大大降低数据库性能
“性别”这种区分度不大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似
建立组合索引，必须把区分度高的字段放在前面
能够更加有效的过滤数据
禁止在WHERE条件的属性上使用函数或者表达式
SELECT uid FROM t_user WHERE from_unixtime(day)>=‘2017-02-15’ 会导致全表扫描
正确的写法是：SELECT uid FROM t_user WHERE day>= unix_timestamp(‘2017-02-15 00:00:00’)

wangchh1988

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
MySQL的个人总结

MYSQL部分常用命令查看数据库版本:select version(); mysql --version;删除数据库:drop table table_name; 表结构和数据全部删除truncate table table_name; 删除表中的数据，但是索引、表结构等都保留;delete from table_name where column_name = ‘value’;删...
复制链接

扫一扫

专栏目录