luzhensmart的专栏

一、MySQL索引原理详解1、背景使用mysql最多的就是查询，我们迫切的希望mysql能查询的更快一些，我们经常用到的查询有：按照id查询唯一一条记录按照某些个字段查询对应的记录查找某个范围的所有记录（between and）对查询出来的结果排序mysql的索引的目的是使上面的各种查询能够更快。2:什么是索引？索引的本质：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来

随着用户量的不断累积，Mysql从单实例单节点发展到多实例多节点的主备，再到一个机房几十台Myql实例，但是现在，一个机房也开始慢慢无法承受庞大的数据吞吐，多机房的理念开始构建。 假设现有两个机房，需要做到数据同步。 以下是架构图（实际架构图根据现有机房架构和实际会比下图复杂，但整体思路不变）： Mycat、Canal、Otter是关键的三项技术： Mycat：数据库分库分表中间件，可以管理一个mysql集群，屏蔽了mysql集群，...

主从复制原理如下。slave（从服务器）master（主服务器）mysql主从是异步复制过程master开启bin-log功能，日志文件用于记录数据库的读写增删主从同步过程中主服务器master有一个工作线程I/O dump thread，从服务器slave有两个工作线程I/O thread和SQL thread。Slave 通过IO线程连接master，并且请求某个bin-log，position之后的内容。MASTER服务器收到slave IO线程发来的日志请求信息，io线程去将

1.redo 重做日志　　作用：确保日志的持久性，防止在发生故障，脏页未写入磁盘。重启数据库会进行redo log执行重做，达到事务一致性2.undo 回滚日志　 作用：保证数据的原子性，记录事务发生之前的一个版本，用于回滚，innodb事务可重复读和读取已提交 隔离级别就是通过mvcc+undo实现3.errlog 错误日志　　作用：Mysql本身启动，停止，运行期间发生的错误信息4.slow query log 慢查询日志　　作用：记录执行时间过长的sql...

select id,name where name='shenjian'select id,name,sex where name='shenjian'*多查询了一个属性，为何检索过程完全不同？什么是回表查询？什么是索引覆盖？如何实现索引覆盖？哪些场景，可以利用索引覆盖来优化SQL？这些，这是今天要分享的内容。画外音：本文试验基于MySQL5.6-InnoDB。一、什么是回表查询？这先要从InnoDB的索引实现说起，InnoDB有两大类索引： 聚集索引(clu.

线程栈信息使用内存(thread_stack)主要用来存放每一个线程自身的标识信息，如线程id，线程运行时基本信息等等，我们可以通过 thread_stack 参数来设置为每一个线程栈分配多大的内存。排序使用内存(sort_buffer_size)MySQL 用此内存区域进行排序操作（filesort），完成客户端的排序请求。当我们设置的排序区缓存大小无法满足排序实际所需内存的时候，MySQL 会将数据写入磁盘文件来完成排序。由于磁盘和内存的读写性能完全不在一个数量级，所以sort_bu.

前言：在使用order by时，经常出现Using filesort，因此对于此类sql语句需尽力优化，使其尽量使用Using index。#1.创建test表。drop table if exists test;create table test(id int primary key auto_increment,c1 varchar(10),c2 varchar(10),c3 varchar(10),c4 varchar(10),c5 varchar(10)) ENGINE=I

近日遇到一个比较奇怪的deadlock错误, 错误详情:Deadlock found when trying to get lock; try restarting transaction; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException...跟踪代码后最终定位到一段业务逻辑:delete from A where no = $no;insert into A(no, value) values(

innodb lockinggap lockGap locking is not needed for statements that lock rows using a unique index to search for a unique row. (This does not include the case that the search condition includes only some columns of a multiple-column unique index; in t

总体来说，就是MySQL innoDB引擎要在RR隔离级别之下解决幻读的问题，所以引入了间隙锁。在进行当前读的情况下，对读出的数据的附近的一整个范围（“间隙”）进行加锁，保证满足查询条件的记录不能被插入。1、幻读与innoDB的隔离级别（为什么会出现间隙锁这个概念）根据 ISO/ANSI SQL92 所定义的标准，四级隔离级别中，只有在可串行化的级别之下，才可以防止幻读的出现。隔离级别所谓幻读，指的是事务A执行过程中，由于事务B并发插入了一条新数据，事务A两次读数据的内容不一样，出现

红黑树等数据结构也可以用来实现索引，但是文件系统及数据库系统普遍采用B-/+Tree作为索引结构，这一节将结合计算机组成原理相关知识讨论B-/+Tree作为索引的理论基础。一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最...

SQL，一些有意思的TIPS，希望大家有收获。一、一些常见的SQL实践（1）负向条件查询不能使用索引。select * from order where status!=0 and stauts!=1not in/not exists都不是好习惯。可以优化为in查询：select * from order where statusin(2,3)（2）...

MVCC(Multi Version Concurrency Control的简称)，代表多版本并发控制。与MVCC相对的，是基于锁的并发控制，Lock-Based Concurrency Control)。MVCC最大的优势：读不加锁，读写不冲突。在读多写少的OLTP应用中，读写不冲突是非常重要的，极大的增加了系统的并发性能了解MVCC前，我们先学习下Mysql架构和数据库事务隔离级别...

MYSQL中NOW、CURRENT_TIMESTAMP、SYSDATE的区别mysql> select NOW(),CURRENT_TIMESTAMP(),SYSDATE();1执行上边的SQL，结果如下：+---------------------+---------------------+---------------------+| NOW() ...

可能错误的原因有千千万，本人出错的原因是:数据库用的是Mysql8版本，但工程里面mysql驱动包却是5.1.37版本。只需修改驱动包为8.0.11版本即可。<!-- mysql --><dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-jav...

快照读历史数据－mvccinnodb的默认事务隔离级别是rr（可重复读）。它的实现技术是mvcc。基于版本的控制协议。该技术不仅可以保证innodb的可重复读，而且可以防止幻读。（这也就是是此前以rr隔离级别实践时，不仅可以防止可重复读，也防止了幻读）但是它防止的是快照读，也就是读取的数据虽然是一致的，但是数据是历史数据。（http://blog.sina.com.cn/s/blog_4997...

InnoDB默认的隔离级别是RR（可重复读），可以解决脏读和不可重复读，只解决了快照读情况下的幻读问题,当前读情况下解决幻读问题得靠next-key锁。mysql如何实现避免幻读:在快照读读情况下，mysql通过mvcc来避免幻读。 在当前读读情况下，mysql通过next-key来避免幻读- 快照读, 读取专门的快照 (对于RC，快照（ReadView）会在每个语句中创建。对于RR...

我们知道，mysql的innodb采用的是行锁，而且采用了多版本并发控制来提高读操作的性能。什么是多版本并发控制呢 ？其实就是在每一行记录的后面增加两个隐藏列，记录创建版本号和删除版本号，而每一个事务在启动的时候，都有一个唯一的递增的版本号。1、在插入操作时 ： 记录的创建版本号就是事务版本号。比如我插入一条记录, 事务id 假设是1 ，那么记录如下：也就是说，创建版本...

导读在MySQL里常用的索引数据结构有B+树索引和哈希索引两种，我们来看下这两种索引数据结构的区别及其不同的应用建议。二者区别备注：先说下，在MySQL文档里，实际上是把B+树索引写成了BTREE，例如像下面这样的写法：CREATE TABLE t(aid int unsigned not null auto_increment,userid int unsigned n...

在MySQL的InnoDB中，预设的Tansaction isolation level 为REPEATABLE READ（可重读）在SELECT 的读取锁定主要分为两种方式：　　SELECT ... LOCK IN SHARE MODE　　　SELECT ... FOR UPDATE　　这两种方式在事务(Transaction) 进行当中SELECT 到同一个数据表时，都必须等待...

MySQL的多表查询(笛卡尔积原理)先确定数据要用到哪些表。 将多个表先通过笛卡尔积变成一个表。 然后去除不符合逻辑的数据（根据两个表的关系去掉）。 最后当做是一个虚拟表一样来加上条件即可。注意：列名最好使用表别名来区别。笛卡尔积Demo:左，右连接，内，外连接l内连接：要点：返回的是所有匹配的记录。...

以前使用sql server， mysql时候，本能以为update操作返回的行数能判断对记录的操作结果，除了数据库异常，一般确实能反应操作的真实情况，但是，为何却出现update数据更新成功，反而返回影响行数为0的情况呢？以为可以判断返回值是否大于0来判断更新是否成功，但是，下面的两种情况均返回0：1、没有找到需要更新的数据比如，我们进行update...

作为一名开发人员，当你在使用mysql来做为你的存储数据库，肯定都会考虑用最合适的字段类型来创建数据库表吧。  www.2cto.com  可是假如你其中一个语句这么写 ip varchar(15) not null default '' COMMENT '用户IP地址',你有没考虑过，其实，有更省空间的方式来存储用户的IP地址。（这个虽然是变长的，但对于大部分中国IP来说，基本都在10字...

InnoDB有三种行锁的算法：1，Record Lock：单个行记录上的锁。2，Gap Lock：间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。3，Next-Key Lock：1+2，锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。测试一：默认RR隔离级别root@localhost...

一、事务的基本要素（ACID）　　1、原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。　　 2、一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏 。比如A...

在sql查询中为了提高查询效率，我们常常会采取一些措施对查询语句进行sql优化，下面总结的一些方法，有需要的可以参考参考。1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。 2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如： select id from t whe...

聚簇索引是对磁盘上实际数据重新组织以按指定的一个或多个列的值排序的算法。特点是存储数据的顺序和索引顺序一致。一般情况下主键会默认创建聚簇索引，且一张表只允许存在一个聚簇索引。在《数据库原理》一书中是这么解释聚簇索引和非聚簇索引的区别的：聚簇索引的叶子节点就是数据节点，而非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针。因此，MYSQL中不同的数据存储引擎对聚簇索引的支持...

索引的重要性数据库性能优化中索引绝对是一个重量级的因素，可以说，索引使用不当，其它优化措施将毫无意义。聚簇索引(Clustered Index)和非聚簇索引 (Non- Clustered Index)最通俗的解释是:聚簇索引的顺序就是数据的物理存储顺序，而对非聚簇索引的索引顺序与数据物理排列顺序无关。举例来说，你翻到新华字典的汉字“爬”那一页就是P开头的部分，这就是物理存储顺序（聚簇索引）；...

安装 登录MySQL网站用dmg的方式安装。Download MySQL Community Server或者常规方式，打开官网 : http://www.mysql.com/downloads/ 进入, 点击下方的DOWNLOADS : MySQL Community Server   选择dmg文件下载   选择选择不登录,直接下载接着, 会...

定义：悲观锁（Pessimistic Lock）： 每次获取数据的时候，都会担心数据被修改，所以每次获取数据的时候都会进行加锁，确保在自己使用的过程中数据不会被别人修改，使用完成后进行数据解锁。由于数据进行加锁，期间对该数据进行读写的其他线程都会进行等待。乐观锁（Optimistic Lock）： 每次获取数据的时候，都不会担心数据被修改，所以每次获取数据的时候都不会进行加锁，但是在更...

当然,   从总的结果来看,   似乎两者都表现为两次读取的结果不一致.但如果你从控制的角度来看,   两者的区别就比较大对于前者,   只需要锁住满足条件的记录对于后者,   要锁住满足条件及其相近的记录-----------------------------------------------------------我这么理解是否可以？避免不可重复读需要锁行就行避免幻影读则...

1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据　　2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。　　3、幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现...

1、读未提交：　　　　（1）打开一个客户端A，并设置当前事务模式为read uncommitted（未提交读），查询表account的初始值： 　　　　（2）在客户端A的事务提交之前，打开另一个客户端B，更新表account：  　　　　（3）这时，虽然客户端B的事务还没提交，但是客户端A就可以查询到B已经更新的数据： 　　　　（4）一旦客户端B的事务因为某种原因回...

（1）打开一个客户端A，并设置当前事务模式为read committed（未提交读），查询表account的所有记录： 　　　　（2）在客户端A的事务提交之前，打开另一个客户端B，更新表account： 　　　　（3）这时，客户端B的事务还没提交，客户端A不能查询到B已经更新的数据，解决了脏读问题： 　　　　（4）客户端B的事务提交　　　　（5）客户端A执行与上一步...

1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num is null可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id...

详解 MySql InnoDB 中意向锁的作用前言InnoDB 支持多粒度锁（multiple granularity locking），它允许行级锁与表级锁共存，而意向锁就是其中的一种表锁。意向锁（Intention Locks）需要强调一下，意向锁是一种不与行级锁冲突表级锁，这一点非常重要。意向锁分为两种：意向共享锁（intention shared lock, IS）：事...

先来看看官方的说明The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value gener...

 背景： 为了提高数据库效率，建索引是家常便饭；那么当查询条件为2个及以上时，我们是创建多个单列索引还是创建一个联合索引好呢？他们之间的区别是什么？哪个效率高呢？我在这里详细测试分析下。一、联合索引测试注：Mysql版本为 5.7.20创建测试表(表记录数为63188)： CREATE TABLE `t_mobilesms_11` ( `id` bigint(20) N...

****什么是共享锁，什么是排他锁？********共享锁****也叫读锁，简称S锁，原理：一个事务获取了一个数据行的共享锁，其他事务能获得该行对应的共享锁，但不能获得排他锁，即一个事务在读取一个数据行的时候，其他事务也可以读，但不能对该数据行进行增删改。****排他锁****也叫写锁，简称x锁，原理：一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁（排他锁或者共享锁），...

乐观锁乐观锁不是数据库自带的，需要我们自己去实现。乐观锁是指操作数据库时(更新操作)，想法很乐观，认为这次的操作不会导致冲突，在操作数据时，并不进行任何其他的特殊处理（也就是不加锁），而在进行更新后，再去判断是否有冲突了。通常实现是这样的：在表中的数据进行操作时(更新)，先给数据表加一个版本(version)字段，每操作一次，将那条记录的版本号加1。也就是先查询出那条记录，获取出versio...