![](https://img-blog.csdnimg.cn/20201014180756926.png?x-oss-process=image/resize,m_fixed,h_64,w_64)
MySQL
文章平均质量分 52
mysql数据库
每一个不曾起舞的日子,都是对生命的辜负
这个作者很懒,什么都没留下…
展开
-
数据库服务器优化步骤
步骤1:观察数据库服务器状态是否有周期性波动如果有,可能是某一时刻出现大量查询或缓存失效,尝试加缓存或更改缓存失效策略 如果没有,进入步骤二步骤2:可能是出现了慢查询开启慢查询日志,定位到慢查询的sql语句 通过explain和show profiling命令来查看慢查询详情,根据慢查询详情进行判断步骤3:是不是sql等待时间长那就调节服务器参数,线程数等步骤4:是不是sql执行时间长那就查询加索引,优化索引 看看是不是多表join过多,优化多表联查 优化表结构步骤5:还是没原创 2022-03-26 00:00:21 · 947 阅读 · 0 评论 -
mysql常用语法
1. 常用操作数据库的命令show databases; 查看所有的数据库create database test; 创建一个叫test的数据库drop database test;删除一个叫test的数据库use test;选中库 ,在建表之前必须要选择数据库show tables; 在选中的数据库之中查看所有的表create table 表名 (字段1 类型, 字段2 类型);desc 表名;查看所在的表的字段drop table 表名; 删除表show create database原创 2021-12-22 00:11:59 · 524 阅读 · 0 评论 -
主从复制所带来的的问题
1. 写后读问题一般而言,写操作会发生在主库,读操作发生在从库。主从复制采用异步复制。所以可能会导致用户在主库写入后立即从从库读取最新的更改过程中,因为异步复制的延迟无法读取的最新的更新数据。1.1 如何解决从主库读取用户自己的档案,在从库中去取其他用户的档案 自己的档案自己可编辑,而其他人无法编辑 这种方式适合可编辑内容只有少数人能写而大多数人只能读的情况 客户端记住最近一次写入的时间戳,系统需要确保从库为该用户提供任何查询时,该时间戳前的变更已经传播到本从库中。否则就从主库读 时原创 2021-10-29 16:06:18 · 249 阅读 · 0 评论 -
mysql一次数据更新的全过程
更新语句经过解析与优化,生成执行计划,交由执行器调用存储引擎接口。 执行器会多次调用存储引擎接口,并不是一次完成 查询旧值,如果这个值不在内存缓冲区则需要查找旧值,从磁盘数据页加载到内存缓冲区 先将旧值写入undo log日志文件中,用于回滚数据 更新内存中的数据 向更新操作写入内存中redo log buffer中 redo log buffer里的日志每隔一秒会自动刷新到磁盘 将redo log buffer中跟本事务相关的redo log日志刷新到磁盘,并向磁盘redo log文原创 2021-10-16 14:48:58 · 533 阅读 · 0 评论 -
InnoDB之redo log与undo log的物理结构
1. redo log1.1 redo log含义它是物理日志。本质上记录的是对某个表空间的某个数据页的某个偏移量的地方修改了几个字节的值。 记录的内容:表空间号+数据页号+偏移量+修改几个字节的值+具体的值1.2 redo log类型类型 含义 MLOG_1BYTE 表示这条日志只修改了了1字节的值 MLOG_2BYTE 表示这条日志只修改了了2字节的值 MLOG_4BYTE 表示这条日志只修改了了4字节的值 MLOG_8BYTE 表示这条日.原创 2021-10-16 13:45:55 · 440 阅读 · 0 评论 -
InnoDB存储引擎中的锁
1. InnoDB存储引擎支持的锁1.1 S行级共享锁select * from l where a=8 lock in share mode;默认查询语句是不加任何锁 通过上述查询语句手动加行级共享锁。 行级锁都是加在索引上的。所以如果查询条件不走索引则只能加表级锁。1.2 X行级排它锁select * from l where a=8 for update;删除和更改语句自动加行级排它锁 查询语句可以通过上述方式加排它锁 行级锁都是加在索引上的。所以如果查询条件不走..原创 2021-10-06 17:02:10 · 322 阅读 · 0 评论 -
InnoDB之redo日志
1. redu log日志1.1什么是redo log它是InnoDB存储引擎的日志,利用redo log能够实现事务持久化。 它是物理逻辑日志,记录了数据页的物理修改,而不是记录某个sql逻辑语句。 内存中有一个redo log buffer,用于作为redo log日志的缓冲池。大小设置8M就够了。1.2 执行过程 先将数据页读入内存缓冲池 修改内存数据页 将redo log日志写入内存的redo log buffer中 将内存的redo log .原创 2021-10-05 21:32:54 · 262 阅读 · 0 评论 -
mysql之存储引擎InnoDB
1. 一些概念1.1 表空间(数据表存储的空间)表空间有多个段对象组成(段对用户来说是透明,逻辑的概念) 每个段由区组成。区是申请磁盘空间的最小单位,区在物理磁盘上是连续的。区的大小固定为1M 每个区由页组成。页是调入内存的最小单位。页的默认大小是16k,可以通过参数调整。 每个页里保存着数据,保存的是记录(里面还包括了一个row offset array。用于给每个页里面的记录行排序的)。1.2 innodb的记录行的隐藏列rowid: 如果我们自己设置了主键,则rowid就是.原创 2021-10-05 11:17:06 · 192 阅读 · 0 评论 -
mysql之join buffer的作用
1. 一些要知道的概念1.1 小表驱动大表在表连接过程中。一般选择小表作为驱动表,大表作为被驱动表。 驱动表(小表)的连接字段无论建立没建立索引都需要全表扫描的。被驱动表(大表)如果在连接字段建立了索引,则可以走索引。如果没有建立索引则也需要全表扫描。1.2 两张表连接的情况被驱动表的连接字段有索引:主键索引 对于驱动表中的每一条数据,到被驱动表的聚簇索引上寻找其对于的数据。 被驱动表的连接字段有索引:二级索引 对于驱动表上的每一条数据,到被驱动表的二次索引上寻找其对于的数据id原创 2021-10-04 18:12:58 · 1461 阅读 · 5 评论 -
mysql之关键字in与exists比较
SELECT * FROM employees WHERE emp_no IN ( SELECT emp_no FROM dept_emp WHERE dept_no = 'd005' ) LIMIT 10;SELECT * FROM employees e WHERE EXISTS ( SELECT * FROM dept_emp de WHERE dept_no = 'd005' AND e.emp_no = de.emp_no ) LIMIT 10;上述两个查询...原创 2021-10-03 21:54:20 · 173 阅读 · 0 评论 -
mysql之limit使用细节
1. 使用方法1.1 一个参数select * from user limit 10;表示取出前十条数据1.2 两个参数select * from user limit 0, 10;limit offset size 表示偏移量从0开始,取10个显示出来。(mysql中好像就只有这里是偏移量从0开始,其他都是从1开始)2. limit的一些缺点select * from user limit 100000,10;上述查询语句,其实会查询100010条数据,然后前10原创 2021-10-03 17:47:44 · 666 阅读 · 1 评论 -
mysql数据类型
1.int类型占四个字节 推荐不要使用unsigned 因为范围行本质没有大的改变 unigned可能会有溢出现象的发生 自增int类型的主键建议使用bigint (8个字节) 因为互联网数据量大。四个字节不一定够用。到时候用完了就麻烦了 2. 数字类型类型 占用空间 精度 精确性 float 4 单精度 低 double 8 双精度 低 decimal 变长 高精度 非常高 数字类型直接用.原创 2021-10-03 13:34:29 · 62 阅读 · 0 评论 -
MySQL之慢查询日志
1. 相关配置# 开启慢查询日志slow_query_log=on# 慢查询日志名称slow_query_log_file=slow.log# 多少秒以上就算慢查询long_query_time=2# 至少检测多少行才会被记录到慢查询日志min_examined_row_limit=100# 将没有使用索引的查询语句也记录到慢查询日志中log-queries-not-using-indexes = on# 限制每分钟记录没有使用索引sql语句的次数log_throttl原创 2021-10-02 22:14:40 · 65 阅读 · 0 评论 -
mysql一些基本操作
1. 权限管理1.1 创建一个用户create user 'ljs'@'%' identified by '123';用户名:ljs %代表任何IP地址都可以访问 123:访问密码create user 'ljs'@'127.0.0.%' identified by '123';在某个网段才能使用该账户密码访问。1.2 删除一个用户drop user 'ljs'@'%';1.3 查看用户权限查看当前用户权限show grants;查看其他用户权限show原创 2021-10-02 21:35:34 · 114 阅读 · 0 评论 -
哪些字段适合建立索引
表的主键,外键 一般对重复元素少的字段进行建立索引 索引应该建立在选择性高的字段上 经常与其他表进行连表查询,在连接字段上可以建立索引 经常出现在where子句中的字段,应该建立索引 索引应该建立在小字段上,对于大的文本字段不要建立索引...原创 2021-09-16 00:25:02 · 5995 阅读 · 0 评论 -
mysql日志操作
1. binlog日志记录了所有增删改sql语句的日志1.1 开启binlog日志没默认不开启,可以通过配置文件my.cnf开启# 配置开启binlog日志,日志文件的前缀为mysqlbinlog_bin=mysqlbin# 启二进制文件的时候,需要设置这个参数,用于主从复制区分主库与从库server-id=1# 配置二进制日志的格式, statment,row,mixed 三个可选项。默认MIXEDbinlog_format=STATMENT1.2 日志格式ST原创 2021-08-29 21:59:22 · 135 阅读 · 0 评论 -
05_mysql常用工具
1. mysql客户端工具// -e: 执行sql语句并退出mysql -uroot -proot db01 -e "select * from tb_book";2. mysqladminmysql数据库管理工具// 创建库mysqladmin -uroot -proot create 'test01'// 删除库mysqladmin -uroot -proot drop 'test01'3. mysqlbinlog用于查看二进制日志文件的管理工具4. mys原创 2021-08-29 16:58:28 · 58 阅读 · 0 评论 -
小表驱动大表
1. 什么是驱动表和被驱动表驱动表是表连接中的基础表,也就是通过驱动表的全表数据集作为循环基础数据,然后将这个全表数据集里的一条一条的数据作为过滤条件到被驱动表中查询数据,最后合并。2. 如何区分驱动表和被驱动表左连接中,左表是驱动表 右连接中,右表是驱动表 内连接中,mysql会自动选择数据量较小的表作为驱动表。3. 什么是小表驱动大表将小表作为驱动表,大表作为被驱动表 对于驱动表来说,需要全表扫描,所以建不建索引都无法优化查询速度 对于被驱动表来说,可以将与驱动表连接的字段建立索原创 2021-08-28 17:56:37 · 4517 阅读 · 1 评论 -
MySQL常用函数
1. 字符函数1. length// 获取参数值的字节个数select length('join');2. concat// 拼接字符串select concat(last_name,'_',first_name) from employees;3. upper、lower// 大小写转换select upper('john');// 将姓变大写,名变小写,然后拼接select concat(upper(last_name),lower(first_name));原创 2021-08-27 00:29:10 · 111 阅读 · 0 评论 -
mysql 练习题
表与题均来源自网络1. 建表语句--建表--学生表CREATE TABLE `Student`(`s_id` VARCHAR(20),`s_name` VARCHAR(20) NOT NULL DEFAULT '',`s_birth` VARCHAR(20) NOT NULL DEFAULT '',`s_sex` VARCHAR(10) NOT NULL DEFAULT '',PRIMARY KEY(`s_id`));--课程表CREATE TABLE `Course`(`c原创 2021-08-23 23:30:37 · 79 阅读 · 0 评论 -
MySQL脏页刷盘流程
1. 什么是脏页InnoDB更新语句,是先查询到指定记录到内存缓冲区,然后更新内存缓冲区数据,再写redo log。并不会立即将数据页刷新到磁盘上。这样就会导致内存数据页和磁盘数据页的数据不一致的情况。这种数据不一致的数据页成为脏页。当脏页写入到磁盘后(flush),数据一致性后称为干净页2. 什么时候会flush脏页redo log写满:redo log大小固定,写完后会循环覆盖写入,写满后当有新内容要写入时,系统必须停止所有的更新操作,将checkpoint向前推进到新的位置,但是在推进原创 2021-07-29 20:15:40 · 1506 阅读 · 0 评论 -
MySQL主从复制
Master将数据更新记录到二进制日志中 Slave上面的IO进程连接上Master,并请求从指定日志文件的指定位置(或者从最开始的日志)之后的日志内容 Master接受到来自Slave的IO进程的请求后,负责复制的IO进程会根据请求信息读取日志指定位置之后的日志信息,返回给Slave的IO进程。返回的信息中除了日志锁包含的信息之外,还包括本次返回的信息已经到Master端的binlog文件的名称以及binlog的位置。 Slave的IO进程接受到信息后,将接收到的日志内容一次添加的Slave端的re原创 2021-07-29 19:57:59 · 71 阅读 · 0 评论 -
MySQL日志分类
1. redo log(重做日志)2. undo log(归滚日志)3. binlog(二进制日志)4. errorlog(错误日志)5. slow query log(慢查询日志)6. general log(一般查询日志)7. relay log(中继日志 )原创 2021-07-29 19:48:48 · 96 阅读 · 0 评论 -
InnoDB中B+树有几层
一般使1~3层,可以存储大约2千万行数据InnoDB存储引擎默认最小存储单元是页,默认一个页的存储大小是16K(可以修改) InnoDB的所有数据文件(后缀为ibd的文件)它的大小始终是16K的整数倍 页可以存储数据也可以存储键值+指针,在B+树叶子节点存放数据,非叶子节点存放键值+指针 假设主键id为bigint类型,长度为8个字节,指针在InnoDB中是6个字节。这样一共14个字节,则一页能能存储多少个这样的单元,就代表一页能存储多少指针,即大约16384/14=1170个指针 假设一行记录原创 2021-07-28 23:32:21 · 1601 阅读 · 0 评论 -
MySQL如何避免死锁
MyISAM是一次性获得所需要的全部锁,InnoDB的锁是逐步获得。 发生死锁后InnoDB一般可以检测到,并使一个事务释放锁回退,另一个则可以获取锁完成事务。 避免死锁的方式有 通过表锁来减少死锁产生的概率(但效率低) 多个程序尽量约定以相同的顺序访问表 同一个事务尽可能做到一次锁定所需要的所有资源 ...原创 2021-07-28 22:15:05 · 639 阅读 · 0 评论 -
InnoDB行锁的实现和分类
InnoDB行锁是通过给索引上的索引项加锁来实现的,所以只有通过索引进行条件检索数据InnoDB才会使用行级锁,否则InnoDB将使用表锁。InnoDB中的行级锁有以下几种:Record Lock:对索引项加锁,锁定符合条件的行。其他事务不能修改和删除加锁项 Gap Lock: 对符合条件范围的间隙加锁,锁定记录的范围,不包含索引项本身。其他事务不能在锁范围内插入数据,“间隙(gap)”是指 键在条件范围内但并不存在的记录 Next-key Lock:锁定索引项本身和间隙。Record Lock原创 2021-07-28 20:35:45 · 137 阅读 · 0 评论 -
MySQL的四个隔离级别是如何实现的
1. 读未提交所有的读不加锁,读到的数据都是最新的数据,性能最好 所有的写加行级锁,写完就立即释放,不等事务结束2. 读已提交使用的是MVCC技术 写操作:加行级锁,事务开始后,会添加一条undo记录。数据行的隐藏列有指向undo记录的指针 读操作:不加锁,在读取时,如果该行被其他事务锁定,则顺着隐藏列上undo指针,找到上一个有效的历史记录。(有效记录:该记录对当前事务可见,且DELETE_BIT=0)3. 可重复读也是使用MVCC技术,读写操作跟上面几乎一样 区别是读已提交每次读原创 2021-07-28 20:17:41 · 115 阅读 · 0 评论 -
数据库的分库分表
分库分表是为了解决由于数据量过大导致数据库性能降低的问题,将原来独立的数据库拆分为若干数据库组成,将原来数据大表拆分为若干数据表组成,使得单一数据库、单一数据表的数据量变小,从而达到数据库性能提升的目的一般来说,数据量与访问量不是很大的情况下,先考虑缓存、读写分离、索引技术等方案。若数据量极大,且持续增长,再考虑分库分表等方案1. 水平分库把同一个表的数据按照一定规则拆分到不同的数据库中去,然后将不同的库放到不同的服务器上去。 比如,将id为单数的与id为双数的数据分别存放到两个库中去。 优原创 2021-07-27 11:21:24 · 92 阅读 · 0 评论 -
事务的特性
原子性:事务是最小的执行单位,不允许分割,要么全部做,要么完全不起作用 一致性:执行事务后,数据库要从一个一致性状态转换为另一个一致性状态 隔离性:并发访问数据库时,一个用户的事务不被其他事务所干扰,各个并发事务之间数据库是隔离的 持久性:一个事务被提交之后,他对数据库中的数据的改变是永久的,即数据库发生了故障也不应该对其有任何影响。...原创 2021-07-27 10:33:17 · 43 阅读 · 0 评论 -
MySQL全局锁、表锁、行锁、意向锁
1. 全局锁对整个数据库进行加锁 MySQL加全局读锁的命令是 Flush tables with read lock 使用场景是全库逻辑备份。就是把整个库每个表都select出来存成文本 让整个库只读,会出现以下问题 在主库备份,则备份期间不能执行更新,业务基本要停止 若在从库备份,则备份期间从库不能执行主库同步过来的binlog,会导致主从延迟 2. 表级锁语法是 lock tables ... read/write。 可以用unlock tables主动释放锁 开销小,加锁快原创 2021-07-27 10:01:54 · 902 阅读 · 0 评论 -
事务的隔离级别
READ_UNCOMMITTED(读未提交):最低的隔离级别,允许读取尚未提交的数据变更,会导致脏读,幻读,不可重复读 READ_COMMITTED(读已提交):允许读取并发事务已经提交的数据,可以阻止脏读,但会导致不可重复读和幻读 REPEATABLE_READ(可重复读):对同一字段的多次读取结果是一致的,除非数据被自己本身所修改,可以组织脏读和不可重复读。但幻读仍可能发生;MySQL默认隔离级别 SERIALIZABLE(串行化):事务串行执行,但是严重影响性能。可以解决脏读、不可重复读、幻读。原创 2021-07-27 08:17:42 · 67 阅读 · 0 评论 -
脏写、脏读、不可重复读、幻读
1. 脏写两个事务,事务A和事务B同时更新一条数据,事务A先把它更新为a值,事务B紧接着把它更新为B值,但接着事务A利用undo log进行了回滚。结果事务B就发现自己的更新丢失了 脏写发生的本质是事务B去修改事务A修改过的值,而且此时事务A还未提交,所以事务A随时会回滚,导致事务B修改的值也没了。2. 脏读假设事务A更新了一行数据的值为a,此时事务B去查询这一行数据的值,看到了a;但接着事务A突然回滚了事务,此时事务B再去读发现刚才的a值没了。 脏读的本质就是事务B去查询了事务A修改过的数据原创 2021-07-27 08:10:50 · 91 阅读 · 0 评论 -
MVCC(多版本并发控制)
首先需要了解一些基本概念当前读读取的是记录的最新版本,需要保证其他并发事务不能修改当前记录,所以会对读取的记录加锁快照读不加锁的select操作就是快照读,即不加锁的非阻塞读;不能保证读取的是最新的结果。MVCC为了实现读写冲突不加锁,用的就是这个快照读MVCC的好处并发读写数据库时,读操作时不阻塞写操作,写操作也不阻塞读操作。 可以解决脏读,幻读,不可重复读,但不能解决更新丢失问题两种组合MVCC+悲观锁:MVCC解决读写冲突,悲观锁解决写写冲突 MVCC+乐观锁:MVCC解原创 2021-07-27 07:41:35 · 455 阅读 · 4 评论 -
MySQL的存储引擎对比
MyISAM表锁,写并发性差,不支持事务,不支持外键 会在磁盘上产生三个文件,.frm(表的定义) .MYD(存储数据) .MYI(存储索引) 清空整个表时,时删除重建 关于count(), 它会直接存储总行数。而InnoDB不是,需要按行扫描 支持全文索引InnoDB基于B+树索引组织表,每一张表都需要一个聚簇索引 建议主键自增,这样写入顺序能和B+树索引的叶子节点顺序一致,这时候存取效率最高 行锁,并发性好 支持日志持久化 支持事务,默认隔离级别是可重复读 清空整个表时,是一原创 2021-07-26 17:48:37 · 91 阅读 · 0 评论 -
MySQL查询性能优化方法
减少请求的数据量只返回必要的列,最好不要使用select *语句 只返回必要的行,使用limit语句限制返回的数据 缓存重复查询的数据。减少服务器端扫描的行数使用索引覆盖查询。语法优化不要再索引列上进行运算 尽量使表的列有默认值,因为is null 或is not null 会使索引失效 用union代替or 尽量用小表驱动大表,外表大而内表小用in,外表小而内表大用exists not in和not exists 中推荐使用not exists 连接表时尽量在两个表的连接字段原创 2021-07-26 14:52:28 · 46 阅读 · 0 评论 -
索引失效的情况
索引列参数与表达式运算 索引列参与了函数运算 模糊查询("码农%" 走索引;"%码农" 不走索引) 字符串与数字比较不走索引('a'='1'走索引;但'a'=1 不走索引) 查询条件有or 正则表达式也不会使用索引 MySQL内部优化,觉得使用索引还不如全表扫描,则也不会使用索引...原创 2021-07-26 14:13:17 · 72 阅读 · 0 评论 -
MySQL explain详解
通过使用explain命令可以获得mysql优化器执行计划,下面对explain字段进行详解1. idselect标识符,是查询语句的查询序号 id相同,执行顺序由上至下 id不同,先执行id值越大的(比如,子查询)2. select_type显示每个select子句的类型SIMPLE:表明这是一个简单查询语句,不适用union和子查询 PRIMARY: 表明这是一个包含子查询语句的最外层查询语句 UNION:这是union中的第二个或者后面的查询语句(可能union多次) SU原创 2021-07-26 13:41:40 · 131 阅读 · 0 评论 -
索引的最左前缀原则
原理mysql建立多列索引有最左前缀原则,即最左优先如: 如果建立了一个索引(col1,col2,col3)则对(col1),(col1,col2),(col1,col2,col3)上建立了索引范围查询范围列可以用到索引(必须是最左前缀),但是范围列之后无法用到索引,同时,索引最多用于一个范围列,一次如果查询条件中有两个范围列则无法全用到索引 mysql会一直向右匹配知道遇到范围查询(>,<,between,like)就停止匹配。比如a=1 and b=2 and c >3原创 2021-07-26 10:43:19 · 198 阅读 · 0 评论 -
Hash索引
InnoDB采用除法散列函数,冲突采用链接法 但是Hash索引只适合等值比较查询如"=","IN"和"<=>"。不支持任何范围查询,因为Hash索引比较的是进行Hash运算后的hash值,这个Hash值没有大小关系。 Hash索引不能利用部分索引键查询:对于组合索引建立的hash索引,hash计算是将组合索引键合并后在计算hash值,所以不能利用部分索引键查询 Hash索引可能会遇到大量Hash值相等的情况。 Hash索引对精确查找速度非常快,由于自己失去了有序性,所以不支持部分查找和范原创 2021-07-26 09:50:00 · 252 阅读 · 0 评论 -
聚簇索引、覆盖索引
1.聚簇索引将数据和索引存储在一起,即聚簇索引的叶子结点存储的是数据本身。而非聚簇索引叶子结点仍然是索引节点。它们要查询到对应数据块可能需要二次查询。 一张表只允许存在一个聚簇索引,默认根据主键创建聚簇索引2. 覆盖索引在InnoDB中,非聚簇索引查询到的结果只是这条数据的id,然后我们还需要根据这个id进行查询聚餐索引才能找到要查询的数据,所以需要两遍索引查询 覆盖索引就是将频繁的查询条件字段和查询结果字段组成一个索引,这样这个索引项相对数据项存储空间也比较小,但是其中已经包括了所需要的结果原创 2021-07-26 09:34:36 · 431 阅读 · 0 评论