MySQL 数据库基础

MySQL数据库、Oracle数据库、SQL Server数据库都属于比较常见的传统的关系型数据库,MySQL的优势主要有简单易用、价格、小、支持多个平台

存储引擎

InnoDB

在MySQL5.5开始作为默认的存储引擎,支持事务,行级锁,外键约束,适合高并发场景,XA协议支持分布式事务。一般来说,如果需要事务支持,并且有较高的并发读取频率,InnoDB是不错的选择

count(0)需要扫描表

InnoD是Inno Database缩写,Innobase Oy是芬兰一家公司

MyISAM

不支持事务,不支持外键,性能优先,表级锁,不适合高并发场景

count(0)不需要扫描表,MyISAM将表的记录数单独存储,性能要优于InnoDB

MySQL Indexed Sequential Access Method (有索引的顺序访问方法)

MEMORY

为得到最快的响应时间,采用的逻辑存储介质是系统内存,当mysqld守护进程崩溃时,所有的Memory数据都会丢失。Memory同时支持散列索引和B树索引

MERGE

Merge表就是几个相同MyISAM表的聚合器;Merge表中并没有数据,对Merge类型的表可以进行查询、更新、删除操作,这些操作实际上是对内部的MyISAM表进行操作。使用场景:对于服务器日志这种信息,比如12个月份用12张表表示,要查询1年的信息可以使用MERGE把12张表聚合成一张表。

ARCHIVE

Archive是归档的意思,仅仅支持最基本的插入和查询两种功能,Archive拥有很好的压缩机制,它使用zlib压缩库,在记录被请求时会实时压缩,所以它经常被用来当做仓库使用

CSV

CSV格式存储数据,所有列必须不能为NULL,不支持索引,可以直接对数据文件编辑。

.CSV文件存储表内容,.CSM文件存储表元数据如状态、数量,.frm存储表结构信息

适合作为数据交换的中间表

BlackHole

黑洞引擎,写入的任何数据都会消失,用于记录binlog做复制的中继存储。在一主多从环境可以做传递日志用

FEDERATED

访问远程的MySQL数据库而不使用replication或cluster技术(类似于Oracle的dblink)。使用FEDERATED存储引擎的表,本地只存储表的结构信息,数据都存放在远程数据库上,查询时通过建表时指定的连接符去获取远程库的数据返回到本地

PERFORMANCE_SCHEMA

主要用于收集数据库服务器性能参数。并且库里表的存储引擎均为PERFORMANCE_SCHEMA,而用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表。MySQL5.5默认是关闭的,需要手动开启,在配置文件里添加。

查看存储引擎的SQL:

1
show engines;

各种类型的区别

char与varchar的区别

char:定长,效率高,平常用于固定长度的场景。比如:手机号码,身份证号码,电话号码等固定长度的编码

varchar:长度不固定,一般来说效率低一些

长度M: 长度限定的是输入的的个数,不是Byte数。比如varchar(2) 可以输入我我也可以输入11但是没办法输入111

varchar

  • 存储 varchar 字段是将实际内容单独存储在聚簇索引之外,内容开头用1到2个字节表示实际长度(长度超过255时需要2个字节),因此最大长度不能超过65535。
  • 编码长度 gbk每个字符最多2个字节,最大长度32766.utf8每个字符最大3个字节,最大长度不超过21845。但是row的限制是65535。所以如果这个row还有其他字段,utf-8类型字符集的数据库varchar就达不到21845
  • 行长度限制 MySQL要求一个行的定义长度不能超过65535

CHAR(M), VARCHAR(M)不同之处

char(M) 定义的列的长度为固定的,M取值可以为0~255之间,当保存CHAR值时,在它们的右边填充空格以达到指定的长度

VARCHAR(M)定义的列的长度为可变长字符串,M取值可以为0~65535之间,(VARCHAR的最大有效长度由最大行大小和使用 的字符集确定。整体最大长度是65,532字节)。VARCHAR值保存时只保存需要的字符数,另加一个字节来记录长度(如果列声明的长度超过255,则 使用两个字节)。varchar存储变长数据,但存储效率没有 CHAR高。如果一个字段可能的值是不固定长度的,我们只知道它不可能超过10个字,把它定义为VARCHAR(10)是最合算的。VARCHAR类型的实际长度是它的值的实际长度+1或者+2。为什么”+1或者+2”呢?这个字节用于保存实际使用了多大的长度

VARCHAR和TEXT、BlOB类型的区别

VARCHAR,BLOB和TEXT类型是变长类型,对于其存储需求取决于列值的实际长度(在前面的表格中用L表示),而不是取决于类型 的最大可能尺寸。例如,一个VARCHAR(10)列能保存最大长度为10个字符的一个字符串,实际的存储需要是字符串的长度 ,加上1个字节以记录字符串的长度。对于字符串‘abcd’,L是4而存储要求是5个字节。

BLOB和TEXT类型需要1、2、3或4个字节来记录列值的长度,这取决于类型的最大可能长度。VARCHAR需要定义大小,有65535字节的最大限制;TEXT则没有,如果你把一个超过列类型最大长度的值赋给一个BLOB或TEXT列,值被截断以适合它。

一个BLOB是一个能保存可变数量的数据的二进制的大对象。4个BLOB类型TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB仅仅在他们能保存值的最大长度方面有所不同。

BLOB 可以储存图片,TEXT不行,TEXT只能储存纯文本文件。4个TEXT类型TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT对应于 4个BLOB类型,并且有同样的最大长度和存储需求。在BLOB和TEXT类型之间的唯一差别是对BLOB值的排序和比较以大小写敏感方式执行,而对 TEXT值是大小写不敏感的。换句话说,一个TEXT是一个大小写不敏感的BLOB。

总结char,varchar和text的区别

字符类型若为gbk,每个字符最多占2个字节,最大长度不能超过32766
字符类型若为utf8,每个字符最多占3个字节,最大长度不能超过21845

如果数据库类型为varchar(1) 就说明可以存储一个字,可以为:1/a/我 等等

长度的区别,char范围是0~255,varchar最长是64k,但是注意这里的64k是整个row的长度,要考虑到其它的 column,还有如果存在not null的时候也会占用一位,对不同的字符集,有效长度还不一样,比如utf8的,最多21845,还要除去别的column,但是varchar在一般 情况下存储都够用了。如果遇到了大文本,考虑使用text,最大能到4G。

效率来说基本是char>varchar>text,但是如果使用的是Innodb引擎的话,推荐使用varchar代替char。

char和varchar可以有默认值,text不能指定默认值。

数据库选择合适的数据类型存储还是很有必要的,对性能有一定影响。对于int类型的,如果不需要存取负值,最好加上unsigned;对于经常出现在where语句中的字段,考虑加索引,整形的尤其适合加索引。

int tinyint

1 bytes = 8 bit ,一个字节最多可以代表的数据长度是2的8次方 11111111 在计算机中也就是-128到127

  • BIT[M] 位字段类型,M表示每个值的位数,范围从1到64,如果M被忽略,默认为1
  • TINYINT[(M)] [UNSIGNED] [ZEROFILL] M表示每个值的位数,M默认为4,很小的整数。带符号的范围是-128到127。无符号的范围是0到255。之所以默认值是4是因为最小值-128的字(字符)长度为4
  • SMALLINT[(M)] [UNSIGNED] [ZEROFILL] M表示每个值的位数,M默认为6 ,小的整数。带符号的范围是-32768到32767。无符号的范围是0到65535。
  • MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL] M表示每个值的位数,M默认为9,中等大小的整数。带符号的范围是-8388608到8388607。无符号的范围是0到16777215。
  • INT[(M)] [UNSIGNED] [ZEROFILL] M表示每个值的位数,M默认为11 普通大小的整数。带符号的范围是-2147483648到2147483647。无符号的范围是0到4294967295。
  • BIGINT[(M)] [UNSIGNED] [ZEROFILL] M表示每个值的位数,M默认为20 大整数。带符号的范围是-9223372036854775808到9223372036854775807。无符号的范围是0到18446744073709551615。

注意:这里的M代表的并不是存储在数据库中的具体的长度,以前总是会误以为int(3)只能存储3个长度的数字,int(11)就会存储11个长度的数字,这就错了。

tinyint(1) 和 tinyint(4) 中的1和4并不表示存储长度,只有字段指定zerofill(零填充)时有用,
如tinyint(4),如果实际值是2,如果列指定了zerofill(零填充),查询结果就是0002,左边用0来填充。这个M=3我们可以简单的理解成为, 我们建立这个长度是为了告诉MYSQL数据库我们这个字段的存储的数据的宽度为3位数,当然如果你不是3位数(只要在该类型的存储范围之内)MYSQL也能正常存储。注意:windows下navicate for mysql 零填充不显示,在linux下查看,或者命令行可以正常显示

在MySQL中用来判断是否需要进行对据列类型转换的规则

  1. 在一个数据表里,如果每一个数据列的长度都是固定的,那么每一个数据行的长度也将是固定的
  2. 只要数据表里有一个数据列的长度的可变的,那么各数据行的长度都是可变的
  3. 如果某个数据表里的数据行的长度是可变的,那么,为了节约存储空间,MySQL会把这个数据表里的固定长度类型的数据列转换为相应的可变长度类型

例外:长度小于4个字符的char数据列不会被转换为varchar类型

查询分析与优化

explain

explain的使用方法很简单,只需要在select查询语句前面加上explain关键字就行

回显的数据的含义:

  • id:SELECT识别符,SELECT查询序列号,id越大的先执行
  • select_type:select类型 (simple 它表示简单的select,没有union和子查询。primary 最外面的select。union union语句的第二个或者说是后面那一个。DERIVED 派生表SELECT语句中FROM子句中的SELECT语句。UNION RESULT 一个UNION查询的结果。DEPENDENT UNION 首先需要满足UNION的条件,及UNION中第二个以及后面的SELECT语句,同时该语句依赖外部的查询。SUBQUERY 子查询中第一个SELECT语句 )
  • table:显示这一行的数据是关于哪张表的。N就是id值,指该id值对应的那一步操作的结果
  • type:这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、 index和ALL
  • key_len:使用的索引的长度。长度越短越好
  • ref:显示索引的哪一列被使用了,如果可能的话,是一个常数
  • rows:MYSQL认为必须检查的用来返回请求数据的行数
  • Extra:关于MYSQL如何解析查询的额外信息。坏的例子是Using temporary和Using filesort,意思MYSQL根本不能使用索引,结果是检索会很慢
    • Distinct:一旦MYSQL找到了与行相联合匹配的行,就不再搜索了
    • Not exists: MYSQL优化了LEFT JOIN,一旦它找到了匹配LEFT JOIN标准的行,就不再搜索了
    • Range checked for each Record:MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用
    • Using filesort: MySQL需要额外的一次传递,以找出如何按排序顺序检索行
    • Using index: 从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息
    • Using temporary 看到这个的时候,查询需要优化了。MYSQL需要创建一个临时表来存储结果
    • Using where:WHERE 子句用于限制哪一个行匹配下一个表或发送到客户
    • Using sort_union(…), Using union(…), Using intersect(…):这些函数说明如何为index_merge联接类型合并索引扫描。
    • Using index for group-by:类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查 询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。

!=是否走索引

  1. 如果是<、>、BETWEEN,那么很明显是走范围索引range。!=是不是可以理解为<和>的并集呢?
  2. 实际使用中,建了索引的字段,一般Cardinality高,意味着区分度高,才有意义。注:show index from tb_name;, Cardinality通过观察它来评估索引是否合理。
  3. 而大部分场景!=不走索引,很有可能只是因为不等于的数据占比很高,走索引不如全表扫描。从而导致普遍认为!=不走索引。
  4. 找到数据分布不均匀的字段,做实验即可验证。explain select count(1) from test where status!=1;
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    mysql -uroot -p test --test 是数据库名



    mysql> explain select count(1) from test where type!=3;
    +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
    | 1 | SIMPLE | test | NULL | index | type | type | 768 | NULL | 3 | 66.67 | Using where; Using index |
    +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
    1 row in set, 2 warnings (0.00 sec)

    mysql> explain select * from test where type!=3;
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
    | 1 | SIMPLE | test | NULL | ALL | type | NULL | NULL | NULL | 3 | 66.67 | Using where |
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
    1 row in set, 2 warnings (0.00 sec)

总结:MySQL会根据具体情况分析是否走索引。

性能测试

插入数据测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
CREATE TABLE `t` (
`x` int(11) ,
`y` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`z` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
PRIMARY KEY (`x`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
ROW_FORMAT=DYNAMIC

-- MySQL5.7 普通i5 8G内存台式机运行18秒执行完。大概每秒钟500条。
DELIMITER $$
DROP PROCEDURE IF EXISTS `proc_auto_insert`$$
CREATE PROCEDURE `proc_auto_insert`()
BEGIN
DECLARE i INTEGER DEFAULT 1;
WHILE i <= 10000 DO
INSERT INTO t VALUES(i,CONCAT('--', i), i);
SET i = i + 1;
END WHILE;
END$$
DELIMITER ;

CALL proc_auto_insert();

select * from t;

-- 存储引擎改成MyISAM插入速度提高,100万条100秒就够
-- 存储引擎改成MEMORY,插入2万条1.2秒

常用运维命令

  1. 登录mysql -h127.0.0.1 -uabcd -p123456
  2. 导入.sql文件,好像只能root建库后授权普通用户使用

    1
    2
    3
    4
    5
    6
    7
    8
    9
    mysql -u root -p # root 登录
    create database test;
    grant all privileges on test_db.* to abcd@localhost identified by '123456'; # 授权
    flush privileges;
    mysql -u root -p # abcd 登录
    use test
    set names utf8;
    source /home/user1/20201010.sql
    show tables;
  3. 允许外部连接

    1
    2
    3
    4
    5
    mysql -u root -p # root 登录
    use mysql;
    update user set host = '%' where user = 'abcd';
    flush privileges;
    select host,user from user;

安装配置Redhat6.5

  1. 官方网站下载
    服务端 rpm
    客户端 rpm
  2. 安装
    rpm -ivh xxx.rpm
  3. 设置密码
    /usr/bin/mysqladmin -u root password ‘xxx’;
    mysql -u root -p
  4. 查看字符集:
    show variables like ‘collation_%’
  5. 修改字符集
    打开/etc/my.cnf在[client]和[mysqld]下面均加上default-character-set=utf8
  6. 卸载
    rpm -qa|grep -i mysql
    rpm -e xxx.rpm
  7. 删除残余
    rm -f /etc/my.cnf
    rm -f /var/lib/mysql
  8. 复制数据库
    mysqldump source_db -uroot -pxxx –opt | mysql target_db -uroot -pxxx -h target_ip
  9. 开启关闭
    service mysqld start
    service mysqld stop

安装配置Redhat6.5 Mysql5.7

  1. wget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
  2. yum -y install mysql57-community-release-el7-10.noarch.rpm
  3. yum -y install mysql-community-server
  4. systemctl start mysqld.service
  5. systemctl status mysqld.service
  6. grep "password" /var/log/mysqld.log 获取初始密码
  7. mysql -u root -p 登录
  8. ALTER USER 'root'@'localhost' IDENTIFIED BY 'new password'; 修改密码
  9. show variables like '%password%';查看密码策略,修改密码策略:

    1
    2
    validate_password_policy=0
    validate_password = off
  10. 开启远程访问

    1
    2
    3
    grant all privileges on *.* to 'root'@'192.168.1.1' identified by 'password' with grant option;
    flush privileges;
    exit;
  11. 打开端口

    1
    2
    firewall-cmd --zone=public --add-port=3306/tcp --permanent
    firewall-cmd --reload
  12. 查看修改编码 show variables like '%character%';

    1
    2
    3
    4
    # vi /etc/my.cnf
    [mysqld]
    character_set_server=utf8
    init_connect='SET NAMES utf8'

文件目录

数据库目录:/var/lib/mysql/
配置文件:/usr/share/mysql(mysql.server命令及配置文件)
相关命令:/usr/bin(mysqladmin、mysqldump等命令)(*mysql的一种安全启动方式:/usr/bin/mysqld_safe –user=root &)
启动脚本:/etc/rc.d/init.d/

配置模板:/usr/share/mysql/my-default.cnf
配置路径:/etc/my.cnf

应用开发

中间表是否加主键

  1. 第一种策略:统一都加主键id或者表名_id,id的优点是简单,表名_id优点是便于前端识别/可读性高
  2. 第二种策略:如果中间表只有外键没有其他附加字段不加id,如果有附加字段加主键id
  3. 第三种策略:中间表不加主键
  4. 根据具体情况加,比如需要对中间表中的一个字段进行更新操作建议加id,可以通过id快速定位,不然就得根据两个关联表外键进行组合查询,涉及根据外键条件更新建议外键加上索引。根据具体情况分析,比如中间表加了id做中间表更新操作的时候一般涉及只涉及新增删除
  5. 表因为使用UUId作为主键,使数据存储稀疏,这就会出现聚簇索引有可能有比全表扫面更慢,所以建议使用int的auto_increment作为主键
  6. 中间表的索引:(1)InnoDB通常根据主键值(primary key)进行聚簇。(2)如果没有创建主键,则会用一个唯一且不为空的索引列做为主键,成为此表的聚簇索引,(3)上面二个条件都不满足,InnoDB会自己创建一个虚拟的聚集索引

中间表实际应用:
任务人员中间表内容:id,task_id,person_id
有中间表id的情况:附件 更新提交 不存在前端进行左右移动导致id丢失问题,通过是否提交ID进行判断,如果有ID说明在数据库中存在,如果没有ID说明是新增的。数据库操作时先把不存在的ID删除(delete from xxx where id not in()),把新增的项添加进去
没有中间表id的情况:任务人员 更新提交 集合A(前端)和B(数据库)操作,A-B为新增,B-A为删除,A∩B为不变
最暴力的方案(不推荐):任务人员 删除所有旧数据,重新插入新数据
注意幂等性:界面可以重复提交添加,不导致报错。比如转移功能,把旧的关联解绑,绑定新的关联的时候不是直接插入就行而要判断数据库中是否已经存在关联,存在的话就跳过插入操作。类似场景表单重复提交(随机码等解决方案)

对于加不加主键各有各的优缺点,加了主键删除比较快捷,但是显示的列表就必须有主键。

对地理空间、地图应用

geometry 可以存储点、线、集合。支持一些地理空间操作函数。insert语句使用ST_GeomFromText('POINT(121.111 31.222)')

SQL语句

order by

1
select * from test order by create_at desc,realname desc

设置自增id

自增ID只能设置成比当前数据库最大自增ID值,更大的值。小于当前值将不生效

1
alter table user AUTO_INCREMENT=100;

truncate

1
2
3
4
5
truncate table user; -- 会把自增id清除从1开始,并且不会记录日志。建议使用delete
-- 有外键约束无法truncate的时候,需要加两句,关闭启用约束检查
SET FOREIGN_KEY_CHECKS = 0; -- 先归0
TRUNCATE table user; -- 再清除数据
SET FOREIGN_KEY_CHECKS = 1; -- 然后设置1

工具结合使用

设计表-选项:表选项中可以设置引擎(InnoDB),字符集,排序规则,自增当前值,行格式(DYNAMIC/COMPACT)等。

可能出现问题

  • ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
    • 可能root密码设置了多个,外网访问与localhost使用不同密码
    • 如果在后台操作可以跳过密码,重新设置一个密码
    • 查看mysql.user表有匿名用户,可能匿名用户导致匹配虽然使用的是%的时候优先匹配到localhost,mysql会优先匹配主机名再匹配用户名的记录

参考