IT_10的博客

假如现在有这样的两张表：CREATE TABLE `employees` (`emp_no` int(11) NOT NULL,`birth_date` date NOT NULL,`first_name` varchar(14) NOT NULL,`last_name` varchar(16) NOT NULL,`gender` char(1) NOT NULL,`hire_date` date NOT NULL,PRIMARY KEY (`emp_no`));CREATE TABLE

前言表结构如下，现在从select city,name,age from t where city='西安' order by name limit 1000 ;这条语句分析下order by的原理。CREATE TABLE `t` ( `id` INT (11) NOT NULL, `city` VARCHAR (16) NOT NULL, `name` VARCHAR (16) NO...

CREATE TABLE `t` ( `id` INT (11) NOT NULL AUTO_INCREMENT, `a` INT (11) DEFAULT NULL, `b` INT (11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `a` (`a`)) ENGINE = INNODB;自增值的存储存储自增值的变量名为：AUTO...

mysql版本：5.6操作系统：windwos 10表结构：id为主键，a为非唯一索引，索引名index_a数据量：查询1：查询2：对比上面的两个sql语句，查询目的一样，但是效率相差较大，现在就来从limit原理分析两条sql的差别。explain查看sql1的执行计划：通过type为all以及key为null可以看出虽然sql中有where a=1在索引上的条件...

mysql5.6不能使用MySQL中的运算符如=，<，<>等和NULL做比较，因为任何和NULL做比较的运算都会得到NULL值结果，即无法通过运算比较符和NULL做比较得到有意义的结果。如下：mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;+----------+---------...

binlog详解二进制日志（binary log）记录了对MySQL数据库执行更改的所有操作，但是不包括SELECT和SHOW这类操作。binlog配置log-bin默认情况下，binlog是不开启的，通过配置参数log-bin=XXX来开启，XXX为产生的二进制日志文件名，后缀默认是二进制日志的序列号，所在路径为数据库所在目录。查看数据库所在目录：show variables like...

假设有这样一个场景：数据库主机内存有128G，需要扫描的数据库大表有200G，会不会数据库主机把内存用光？现在来分析这个扫描流程：对于InnoDB引擎，表中的所有数据字段都保存在主键索引上，因此直接扫描表的主键索引，将符合条件的每一行数据放入结果集，然后返回给客户端。具体步骤如下：获取一行，写到 net_buffer 中。这块内存的大小是由参数 net_buffer_length 定义的，...

前言redologredolog即重做日志，数据库安装目录下的data/ib_logfile指的就是redolog文件。redolog作为一种对数据的恢复操作，恢复的是提交事务修改的页操作。如当我们执行下面的语句：start transaction;select balance from bank where name="zhangsan";// 生成 重做日志 balance=600...

前言首先我们知道，MyISAM引擎直接把一个表的总行数存在了磁盘上，因此只需count(* )的时候直接从磁盘上取值返回即可。但是InnoDB引擎由于支持事务，引出了隔离级别以及MVCC等概念，因此即使是同一个时刻的多个查询，在不同的事务隔离级别以及同一种隔离级别但是不同事务下读取到的count(* )值也不同。举例：一个表的最开始的总行数是10000[外链图片转存失败,源站可能有防盗链...

如果有这样一个场景，某个字段保存的是邮箱，然后需要给这个字段建立索引，有如下几种解决方案：全字段索引alter table user add index index1(email);不建议用这种方式，因为索引树需要存储字段的全部值造成不必要的空间浪费使用前缀索引alter table user add index index1(email(6));这种方式可以节约索引的存储空间，但...

count(*)和count(1)：计算表一共有多少列，包含字段为NULL的列。count(字段)：统计该字段在表中出现的次数，不包含NULL值。对于count(主键id)来说，InnoDB引擎会遍历整张表，把每一行的id值都取出来，返回给server层。server层拿到id后，就按行累加。对于count(1)来说，InnoDB引擎遍历整张表，但不取值。server层对于返回的每一行，放一...

前言《MySQL事务详解》一文中详细讲解了事务的概念，包括ACID特性，事务并发引起的问题，事务的四种隔离级别。在事务的四种特性中，原子性、一致性、持久性通过数据库的redo log和undo log来完成，redo log称为重做日志，用来保证事务的原子性和持久性，undo log称为回滚日志，用来保证事务的一致性。事务的隔离性通过锁机制来实现。这篇文章主要讲解redo log和undo...

悲观锁和乐观锁**乐观锁**可以认为是一种在最后提交的时候检测冲突的手段，而悲观锁则是一种避免冲突的手段。 可以通过为记录添加版本或时间戳字段来实现乐观锁，一旦发现出现冲突了，修改失败就要通过事务进行回滚操作。可以用session.Lock()锁定对象来实现悲观锁（本质上就是执行了SELECT * FROM t FOR UPDATE语句），避免冲突的发生。悲观锁含义认为数据随时会修改，所以...

慢日志记录MySQL中的慢SQL，以及没有用到索引的SQL。慢日志阈值通过参数long_query_time设置，超过该值则计入慢日志文件或者慢日志表中。查看和重置慢日志阈值：show variables like 'long%';set long_query_time=5; //仅对当次服务器开启有效修改my.cnf（Linux下）或者my.ini（Windows下）配置文件，则永久开...

InnoDB体系架构下图是一个简易的InnoDB存储引擎体系架构InnoDB存储引擎有多个内存块，这些内存块组成了一个大的内存池，主要负责：维护所有进程/线程需要访问的多个内部数据结构（MySQL是单进程多线程模式）缓存磁盘上的数据，方便快速读取，同时在对磁盘文件的数据修改之前在内存池中缓存重做日志（redo log）缓冲…后台线程的主要作用是负责刷新内存池中的数据，保证缓冲池...

定义将具有特定功能的SQL语句集提前编写好放在数据库中，用户可以通过定义好的名称调用执行。优点有很强的灵活性，可以完成复杂的判断和较复杂的运算可保证数据的安全性和完整性通过存储过程可以使没有权限的用户在控制之下间接地存取数据库，从而保证数据的安全通过存储过程可以使相关的动作在一起发生，从而可以维护数据库的完整性极大地改善SQL语句的性能存储过程是编译好放在数据库中的，所以...

之前在《数据库事务与锁详解》中讲解了事务的概念、事务引发的问题以及通过隔离性避免这些问题、锁的概念。这里就来讲讲InnoDB引擎中隔离性是怎么实现的。READ COMMITED 和 REPEATABLE READ 的隔离性实现：MVCCMVCC即多版本控制系统，为了实现更好的并发，可以使得大部分的读操作不用加锁， 但是insert，delete，update是需要加锁的。MVCC 只在 R...

关于文章提到的事务、锁、索引、BTree/B+Tree等请看：MySQL索引详解（结合了其他各大佬的文章）数据库事务与锁详解InnoDB支持事务，MyISAM不支持事务。对于InnoDB每一条SQL语句都默认封装成了一个事务，自动提交，这样会影响执行速度，所以有些情况下可以把多条SQL语言放在begin和commit之间组成一个事务，当然，事务并不是为了避免这种速度影响而出现的。I...

数值类型数值类型大致可分为两类：整数、浮点数（小数）MySQL 允许我们指定数值字段中的值是否有正负之分(UNSIGNED)或者用零填补(ZEROFILL)。MySQL中有一个可选的宽度指示器，如INT（6），6即是宽度指示器。该宽度指示器并不会影响int列存储字段的大小,也就是说,超过6位它不会自动截取,依然会存储,只有超过它本身的存储范围才会截取;此处宽度指示器的作用在于该字段是否有z...

参考：【MySQL优化】——看懂explainexplain的作用explain关键字可以模拟优化器执行SQL，从而知道mysql是如何处理sql语句，分析查询语句或者表结构的性能瓶颈。MySQL5.6版本后，select、update、delete、insert都可以使用explain查看执行计划。表的读取顺序数据读取操作的操作类型表中有那些索引一条SQL中，那些索引被实际使用了（...

前言针对不同的存储引擎，数据库锁的实现是有差异的，而现代绝大部分都采用的是InnoDB作为存储引擎，因此这里只讨论InnoDB数据库锁机制。InnoDB数据库锁依靠于InnoDB完整的事物特性的支持，但是，事物也加大了处理成本，带来了并发问题。事务（Transaction）事务是指作为单个逻辑工作单元执行的一系列操作，要么都执行成功，要么都执行失败。事务处理可以确保除只有本事务单元内的所有...

基础嵌套循环算法（Nested Loop Join，简称NLJ）MySQL实现join的时候，采取了一种称为嵌套循环算法的机制。指定循环外层是驱动表，循坏内层是被驱动表，驱动表会驱动被驱动表进行连接操作。首先驱动表找到第一条记录，然后从头扫描被驱动表，逐一查找与驱动表第一条记录匹配的记录然后连接起来形成结果表中的一条记。被驱动表查找完后，再从驱动表中取出第二个记录，然后从头扫描被驱动表，逐一查...

概念MySQL外键约束（FOREIGN KEY）用来在两个表的数据之间建立链接，它可以是一列或者多列。一个表可以有一个或多个外键。外键对应的是参照完整性，一个表的外键可以为空值，若不为空值，则每一个外键的值必须等于另一个表中主键的某个值。外键是表的一个字段，不是本表的主键，但对应另一个表的主键。定义外键后，不允许删除另一个表中具有关联关系的行。两个具有关联关系的表中，主键所在的表叫做主表...

概念索引是 MySQL 数据库中的重要对象之一，它是数据库性能调优技术的基础，常用于实现数据的快速检索。索引就是根据表中的一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表，实质上是一张描述索引列的列值与原表中记录行之间一一对应关系的有序表。MySQL数据访问方式在 MySQL 中，通常有以下两种方式访问数据库表的行数据：顺序访问顺序访问是在表中实行全表扫描，从头到尾逐行...

参考：MySQL索引原理及BTree（B-/+Tree）结构详解BTree有很多种分类，也可以说是基于BTree实现了其他类似BTree是结构，MySQL的默认索引方式为B+Tree，这里就从B+Tree的查找效率和计算机组成原理来讨论为什么BTree作为数据库索引效率很高，红黑树等数据结构也可以用来实现索引，为什么不选择这个结构？一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往...

不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以 获得特定的功能。同一个数据库中，不同的表可以有不同的引擎。（1）InnoDB引擎MySQL5.5及其之后的默认引擎支持事务（提交、回滚、崩溃恢复），表锁，行锁，外键每张表的数据存储按照主键顺序存放，若没有指定主键，InnoDB会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MyS...

从《数据库事务和锁（悲观锁 乐观锁 共享锁 排它锁 行锁 表锁）详解》文章中可以了解到事务并发会引发的四个问题：更新丢失、脏读、不可重复度、幻读，可以通过设置隔离级别来决定事物之间的影响程度从而在一定程度上避免这四个问题。隔离级别的实现大多采用数据库的锁机制：共享锁（S锁）、排它锁（X锁）。在运用X锁和S锁对数据对象加锁时，还需要约定一些规则 ，例如何时申请X锁或S锁、持锁时间、何时释放等。称这...

参考 https://segmentfault.com/a/1190000012513286定义两段锁协议是指每个事务的执行分为两个阶段：生长阶段（加锁阶段）和衰退阶段（解锁阶段）。加锁阶段：在该阶段可以进行加锁操作。在对任何数据进行读操作之前要申请并获得S锁，在进行写操作之前要申请并获得X锁。加锁不成功，则事务进入等待状态，直到加锁成功才继续执行。解锁阶段：当事务释放了一个封锁以后，事...

数据库语言分类SQL语言共分为四大类：数据查询语言DQL，数据操纵语言DML，数据定义语言DDL，数据控制语言DCL。DQL（Data Query Language）数据库查询语言，关键字为SELECTSELECT select_list[ INTO new_table ]FROM table_source[ WHERE search_condition ][ GROUP BY g...

对于一些数据量较大的系统，数据库面临的问题除了查询效率低下，还有就是数据入库时间长。特别像报表系统，每天花费在数据导入上的时间可能会长达几个小时或十几个小时之久。因此，优化数据库插入性能是很有意义的。经过对MySQL innodb的一些性能测试，发现一些可以提高insert效率的方法，供大家参考参考。1、一条SQL语句插入多条数据常用的插入语句如：INSERT INTO `insert_t...

概念死锁是指两个或两个以上事务在执行过程中因争抢锁资源而造成的互相等待的现象。产生死锁的四个必要条件：（1） 互斥条件：一个资源每次只能被一个进程使用。（2） 请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放。（3） 不剥夺条件：进程已获得的资源，在末使用完之前，不能强行剥夺。（4） 循环等待条件：若干进程之间形成一种头尾相接的循环等待资源关系。死锁举例事务1 （...