mysql学习笔记记录

mysql命令

登录

#安装mysql 登录
mysql -u root -p   | "mysql -u [用户名] -p"
 
mysql -h127.0.0.1 -u root -p   | "mysql -h[ip] -u [用户名] -p" 

修改密码

#方式一
set password = password("root");
#方式2
set password for username@localhost = password(newpwd);
#方式3
update mysql.user set authentication_string=password('新密码') where user='用户名' and Host ='localhost';

#刷新
FLUSH PRIVILEGES;

数据库操作

#直接创建
create database test;
#创建带着字符集
create database test1 default character set utf8;
#删除
drop database test;
#使用
use test;

表操作

#基本上可视化操作不使用
CREATE TABLE table_name (column_name column_type);
create table test (id int not null auto_increment,r_title varchar(100) not null ,primary key (id)) engine=innodb default charset=utf8;

DROP TABLE table_name ;

清空表
主键继续递增
delete from test;
主键1开始
TRUNCATE test

表数据操作

#新增
INSERT INTO table_name ( field1, field2,...fieldN ) VALUES ( value1, value2,...valueN );
#多条新增
INSERT INTO table_name  (field1, field2,...fieldN)  VALUES  (valueA1,valueA2,...valueAN),(valueB1,valueB2,...valueBN);
#根据所以或主键 去除重复数据
INSERT IGNORE INTO  重复数据不插入 一般根据索引
#修改
UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

#操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。
UNION 
#返回所有结果集，包含重复数据
UNION  all 

#正则搜索
SELECT * FROM Websites WHERE `name` REGEXP 'e|宝'

ALTER 操作

 #删除表字段
ALTER TABLE account DROP balance;
#添加表字段
ALTER TABLE account add balance INT
#添加字段在一个字段后
ALTER TABLE account add balance1 INT AFTER `name`
#修改字段类型
ALTER TABLE account MODIFY balance1 CHAR(10);
#设置字段默认值
ALTER TABLE account ALTER balance1 SET DEFAULT "s";
 #修改表引擎
ALTER TABLE account ENGINE=myisam
#重命名
ALTER TABLE account RENAME account1

简单索引

#创建索引
CREATE INDEX idx_name ON account (`name`)
#添加索引
ALTER table account ADD INDEX idx_balance (balance)
#添加唯一索引
ALTER table account ADD INDEX UNIQUE idx_balance (balance)
#删除索引
DROP INDEX idx_balance ON account; 
ALTER TABLE account DROP INDEX idx_balance;
 
#设置主键
ALTER TABLE account ADD PRIMARY KEY (i);
#设置递增开始值
ALTER TABLE t AUTO_INCREMENT = 100;
#显示索引
SHOW INDEX FROM account

简单复制表

#显示表结构
SHOW create TABLE account
#复制表
CREATE TABLE account2 LIKE account
INSERT INTO account2 SELECT * FROM account1
# 复制表数据
insert in ... select 
# 复制表数据字段
INSERT INTO account1 (id,`name`) SELECT id,`name` FROM account

sql注入

SQL注入即是指web应用程序对用户输入数据的合法性没有判断或过滤不严，攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步得到相应的数据信息

如何防止SQL注入

1. 分级管理

1. 就是权限限制，对于普通用户，禁止给予数据库及表建立、删除、修改等相关权限，只有系统管理员才具有增、删、改、查的权限

2. 参数传值

1. 书写SQL语言时，禁止将变量直接写入到SQL语句，必须通过设置相应的参数来传递相关的变量

3. 安全校验及多层验证

1. web输入对字段进行校验参数，后端服务依然要进行参数校验

4. 安全参数

1. SQL数据库为了有效抑制SQL注入攻击的影响。在进行SQLServer数据库设计时设置了专门的SQL安全参数。在程序编写时应尽量使用安全参数来杜绝注入式攻击。从而确保系统的安全性。

2. SQLServer数据库提供了Parameters集合，它在数据库中的功能是对数据进行类型检查和长度验证，当程序员在程序设计时加入了Parameters集合，系统会自动过滤掉用户输入中的执行代码，识别其为字符值。如果用户输入中含有恶意的代码，数据库在进行检查时也能够将其过滤掉。同时Parameters集合还能进行强制执行检查。一旦检查值超出范围。系统就会出现异常报错，同时将信息发送系统管理员，方便管理员做出相应的防范措施

5. 数据加密

1. 数据库保存时候有些不要直接存储进行加密处理，这样数据一般很难突破 除非秘钥暴露 （对称加密，非对称加密）

java 防止sql注入

PreparedStatement对象 预编译 使用？代替sql拼接，参数通过占位符传递，欲变以后参数会被整体赋值，无法形成sql注入

String sql = "INSERT INTO users(`id`,`name`,`password`,)\n" +
                    "VALUES (5,'aaa','123456')"
替换
Connection conn=null;
PreparedStatement st=null;
 ResultSet rs=null;
String sql = "INSERT INTO users(`id`,`name`,`password) VALUES (?,?,?)";
 st = con.prepareStatement(sql);
  //手动给参数赋值
  st.setInt(1, 5);
  st.setString(2, "aaaaaa");
  st.setString(3, "123456");
 rs=ps.executeQuery();

mysql优化

1. in查询字段包含尽量少 IN中的常量全部存储在一个数组里面，而且这个数组是排好序的。但是如果数值较多，产生的消耗也是比较大的 能用between不用in

2. select 查询结果只读取相应字段

3. 需要单条数据时候加limit 减少查找

4. 没有索引字段少排序

5. 连接join不要过多

6. 创建索引条件近可能使用索引

7. 控制索引数量 单表5个内 过多索引b+树多消耗性能与空间

8. 不要使用 select count(*) from table，这样不带任何条件的count会引起全表扫描，并且没有任何业务意义，可以用count(1)代替

9. 合理字段类型

• 能用数字类型就不用字符串，字符串处理速度比数字类型慢

• 尽量用小类型，比如：用bit存布尔值，用tinyint存枚举值等。

• 长度固定字符串用char，不固定用varchar

索引优化

不要在索引列上进行运算或使用函数 索引会失效

前导模糊查询不会使用索引 如【like '%李'】

联合索引最左前缀原则

覆盖索引是指 SQL 中 query 的所有字段，在索引 B+Tree 的叶子节点上都能找得到的那些索引，从二级索引中查询得到记录，而不需要通过聚簇索引查询获得，可以避免回表的操作

mysql几种存储引擎

存储引擎主要在表上 show engines; 显示数据库引擎

主要介绍MyISAM 和 InnoDB 的区别

Csv

应用场景

数据导入导出

表格直接转换csv

缺点

1. 数据保存在磁盘以csv格式

2. 不能所有不能NOT null,不适合大数据

3. 安全低可磁盘修改

Archive

压缩协议占用空间小 优点：数据占用磁盘少 同样数据至少比innodb少使用90%

应用场景：

1. 日志系统；

2. 大量的设备数据采集；

特点：

只支持insert 和select

只允许自增ID列建立索引

支持行级锁

不支持事务

Memory存储引擎

没用过，数据放在内存中，没有持久化，相对使用它不如用redis等菲关系性数据库

InnoDB存储引擎

mysql5.5版本默认引擎myisam， 5.7以后默认使用innodb

特点：

支持事务

支持外键（很少用外键关联操作比较复杂，一般使用关联查询）

使用聚簇索引

5.7以后支持犬瘟检索

支持行级表级锁

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

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

MyISAM存储引擎

非聚集索引 主键不是必须的；

支持全文索引；

有一个变量专门来保存整个表的行数，查询count很快(注意不能加任何 where 条件)

支持表级锁，不支持行级锁；

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

不支持事务

InnoDB与MyISAM区别

引擎	事务	外键	聚集索引	B+树	主键	行锁	表锁	全文索引	表空间
InnoDB	√	√	√	√	√	√	√	5.7以后支持	大
MyISAM	×	×	×	√	可有可无	×	√	√	小（有压缩）

InnoDB不保存表的具体行数

Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI

InnoDB辅助索引和聚集索引之间存在层级关系；聚集索引之间辅助索引和主键索引则是平级关系

innodb使用辅助索引时叶子节点存是主键，还需聚集索引再次查询（回表）

InnoDB为什么推荐使用自增ID作为主键？

自增ID可以保证每次插入时B+索引是从右边扩展的，可以避免B+树和频繁合并和分裂（对比使用UUID）。如果使用字符串主键和随机主键，会使得数据随机插入，效率比较差。

如何选择

对需要事务选InnoDB

只读数据MyISAM 有写还是innodb

对数据恢复需求先innodb myisam恢复难

5.7默认innodb 如果没有特殊需求使用innodb

用程序对查询性能要求较高，就要使用MYISAM了。MyISAM强调了快速读取操作。索引和数据是分开的，而且其索引是压缩的，可以更好地利用内存。所以它的查询性能明显优于INNODB。压缩后的索引也能节约一些磁盘空间。MYISAM拥有全文索引的功能，这可以极大地优化LIKE查询的效率。

sql索引

索引是一种特殊的数据库结构，由数据表中的一列或多列组合而成，可以用来快速查询数据表中有某一特定值的记录。

mysql数据库主键就是一个索引（主键索引唯一的也叫做聚簇索引）主键外叫做非聚簇索引，

两种方式访问数据库表的行数据

顺序访问

中实行全表扫描，从头到尾逐行遍历，直到在无序的行数据中找到符合条件的目标数据。数据到时候比较慢

索引访问

索引访问是通过遍历索引来直接访问表中记录行的方式。

索引的优缺点

优点：主要有点提升数据查询效率

缺点：

• 创建和维护索引组要耗费时间，并且随着数据量的增加所耗费的时间也会增加。

• 索引需要占磁盘空间，除了数据表占数据空间以外，每一个索引还要占一定的物理空间。如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。

• 当对表中的数据进行增加、删除和修改的时候，索引也要动态维护，这样就降低了数据的维护速度。

聚簇索引和非聚簇索引

主键索引也是聚簇索引，非主键索引都是非聚簇索引，除格式信息外，两种索引的非叶子节点都是只存索引数据的

一张数据表一般对应一颗或多颗树的存储，树的数量与建索引的数量有关，每个索引都会有一颗单独的树。（缺点一种表现）

聚簇索引

聚簇索引叶子节点一般情况下存的是这条数据的所有字段信息非叶子节点都是只存索引数据的

非聚簇索引

叶子节点存的是这条数据所对应的主键和索引列信息，查询时候先从非聚簇索引查到主键的值，再根据主键索引去查数据内容，一般情况下要查两次（除非索引覆盖），这也称之为 回表 ，就有点类似于存了个指针

mysql B+树

索引的数据结构是B+树。 一般索引数都在3层，大于3层读取io次数会增加数据量大读取磁盘io就会多相对耗时

b树与b+树

M阶的B树，每个非叶子节点存放了M-1个关键字和M个指向子树的指针

mysql都是一页存储数据 默认每页大小16kb

b树节点存储结构

b+树节点存储结构

b树

节点保存关键词和数据 及下层节点指针

b+树

非叶子节点只存储键值信息和指向子节点页号的指针

所有叶子节点之间都有一个链指针；

数据记录都存放在叶子节点中；

树选着

b树 数据保存在节点上最优时间查找O(1)

b+树固定复杂度logN

innodb 默认页大小16kb 不存储数据 B+数阶数远大于B树，这样读取Io次数就减少提升查询效率

如果一个节点存1000个键，3层可以存1000*1000*1000=10亿 字需要读取2次io 一般第一层放在内存中 ，只需要读取1次io

B+数顺序存储子节点中的数据是通过单向链表连接的，通过这种方式可以找到表中的所有数据。 更适合范围查找 ，排序 分组查找 B树节点分散不容易

面试：张表最好不超过2000万数据？

每张表数据超过2000W 会导致致查询速度降低，影响性能？

阿里的Java开发手册上也提出：单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表

这个2000万或者500万都只是一个大概的数字，并不适用于所有场景

页信息

所以计算一页存储非叶子节点一页可用信息如下

公共占用 38（文件头）+56（页头）+26（边界）+8（文件结尾）=128

预留1/16 一页大概 可用大小

16*1/16*1024-128=15323 字节

行信息

当使用 DYNAMIC 创建表时，InnoDB 会将较长的可变长度列（比如 VARCHAR、VARBINARY、BLOB 和 TEXT 类型）的值剥离出来，存储到一个溢出页上，只在该列上保留一个 20 字节的指针指向溢出页，列是否存储在页外取决于页大小和行的总大小。当一行太长时，选择最长的列进行页外存储，直到聚集索引记录适合 B+ 树页。小于或等于 40 字节的 TEXT 和 BLOB 直接存储在行内，不会分页。

字符编码不同情况下的存储

ASCII码每个字符占一个字节，那 name 就是占用 10 个字节

对于长度不固定的字符编码（比如utf8mb4）占用为 1 ~ 4 字节

大于或等于 768 字节的 char 列会被看成是可变长度字段（就像varchar一样），可以跨页存储。例如，utf8mb4 字符集的最大字节长度为 4，则 char(255) 列将可能会超过 768 个字节，进行跨页存储。

按b+树3层计算（查找一个数据读取io3次 第一次一般在内存中读取两次）

3层b+数前2层主要是关键字索引信息

只有id主键表

CREATE TABLE `course_schedule` (
  `id` int NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

bigint 8位+6索引指针+5行头=19

上面可用页大小 15323/19=801条数据

页数据页目录每个槽4-8条数据 按6条

801/6=134 268字节最后一层可保存（15323-268）/19=787条数据

int 约993条

前2层

bigint ：787^2=619369 叶子节点

int :993^2=986049 叶子节点

最大数据一条数据为页大小一般8k 一页2条数据

可以存数据

bigint: 2*619369=1238738 一百二十万

简单int表

CREATE TABLE `course_schedule` (
  `id` int NOT NULL,
  `teacher_id` int NOT NULL,
  `course_id` int NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

4+4+4+6+7（回滚指针）+5=30

15232/30=507条

507（没有去槽）*619369 约5亿

复杂表

CREATE TABLE `blog` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '博客id',
  `author_id` bigint unsigned NOT NULL COMMENT '作者id',
  `title` varchar(50) CHARACTER SET utf8mb4 NOT NULL COMMENT '标题',
  `description` varchar(255) CHARACTER SET utf8mb4 NOT NULL COMMENT '描述',
  `school_code` bigint unsigned DEFAULT NULL COMMENT '院校代码',
  `cover_image` char(32) DEFAULT NULL COMMENT '封面图',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `release_time` datetime DEFAULT NULL COMMENT '首次发表时间',
  `modified_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `status` tinyint unsigned NOT NULL COMMENT '发表状态',
  `is_delete` tinyint unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `author_id` (`author_id`),
  KEY `school_code` (`school_code`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_general_mysql500_ci ROW_FORMAT=DYNAMIC;

1. 行记录头信息：肯定得有，占用5字节。

2. 可变长度字段列表：表中 title占用1字节，description占用2字节，共3字节。

3. null值列表：表中仅school_code、cover_image、release_time3个字段可为null，故仅占用1字节。

4. 事务ID和指针字段：两个都得有，占用13字节。

5. 字段内容信息：

1. id、author_id、school_code 均为bigint型，各占用8字节，共24字节。

2. create_time、release_time、modified_time 均为datetime类型，各占8字节，共24字节。

3. status、is_delete 为tinyint类型，各占用1字节，共2字节。

4. cover_image 为char(32)，字符编码为表默认值utf8，由于该字段实际存的内容仅为英文字母（存url的），结合前面讲的字符编码不同情况下的存储 ，故仅占用32字节。

5. title、description 分别为varchar(50)、varchar(250)，这两个应该都不会产生溢出页（不太确定），字符编码均为utf8mb4，实际生产中70%以上都是存的中文(3字节)，25%为英文(1字节)，还有5%为4字节的表情😁，则存满的情况下将占用 (50+250)×(0.7×3+0.25×1+0.05×4)=765(50 + 250) \times (0.7 \times 3 + 0.25 \times 1 + 0.05 \times 4 ) = 765(50+250)×(0.7×3+0.25×1+0.05×4)=765 字节。

统计上面的所有分析，共占用 869 字节，则每个叶子节点可以存放 15232÷869≈1715232 \div 869 \approx 1715232÷869≈17 条，算上页目录，仍然能放 17 条。

则三层B+树可以存放的最大数据量就是 17×619369=10,529,27317 \times 619369 = 10,529,27317×619369=10,529,273，约一千万条数据

引用：

链接：https://juejin.cn/post/7165689453124517896 （这个文章）

mysql5.7 行介绍

所以所并不是每张表都2000w性能就好 要看存储结构

sql事务

事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

• 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

• 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

• 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

• 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

查询数据库事务隔离级别 及自动提交

select @@transaction_isolation;
select @@autocommit;

事务隔离级别

READ UNCOMMITTED : 读取未提交

READ COMMITTED：读取已提交

REPEATABLE READ：可重复读（默认）

SERIALIZABLE：可串行化 性能很差

并发事务带来的问题

脏读：未提交事务被读取到

不可重复读：一个事务读前后取数据不同

幻读：一个事务前后读取条数不同

不可重复读针对的是更新和删除，幻读针对的是插入。

MVCC(Mutil-Version Concurrency Control)，就是 多版本并发控制。MVCC 是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问。
在 快照读读情况下，mysql通过mvcc来避免幻读。
在 当前读读情况下，mysql通过next-key来避免幻读。锁住某个条件下的数据不能更改。

设置事务隔离级别

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

Spring事务（待补充）

参考：

https://juejin.cn/post/7165689453124517896