mysql 如果出现主从数据不一致情况怎么弄

第三步找出l(key_part1 = 1 AND key_part2 < 2) OR (key_part1 > 5)r

可以运行删除查询，将两表不匹配的记录清除掉，然后再建立父子表之间的一对多参照完整性关系，从而防止数据不一致的情况发生。delete from A where not exists(select 1 from B where B.Sid=A.Sid);

mysql交集 mysql上亿数据秒级查询

mysql交集 mysql上亿数据秒级查询

设A、B两表的Sid字段有不一致

a) 删除A表中与B表无交集的记录

b) 删除B表中与A表无交集的记录

delete from B where not exists(select 1 from A where A.Sid=B.Sid);

select 1 from (select min(id) as id,Sid

from A group by Sid hing count(1)>1) t

where t.Sid=A.Sid and A.id>t.id);

select from a;

mysql 如何实现两个表数据统计合并

对MyISAM的索引方式也叫做 非聚集 的，之所以这么称呼是为了与InnoDB的聚集索引区分。于 HASH 索引，只能使用包含相同值的每个间隔。这意味着只能针对以下形式的条件生成间隔：

select regTime as time, count() as regSum from user group by regTime; 这个当做表r

select time, count() as logSum from login group by time; 这个当做表l

步找出r,l的交集

select r.time as time , r.regSum as regSum, l.logSum as logSum

第二步找出rl

select r.time as time , r.regSum as regSum, 0 as logSum

from r life join l on r.time = l.time

where l.time is null ;

select l.time as time , 0 as regSum, l.logSum as logSum

from r right join l on r.time = l.time

where r.time is null ;

然后 union alWHERE TableA.id IS null OR TableB.id IS nulll 三步的结果。

关于mySql 中乐观锁与读已提交（事务隔离级别）的搭配使用问题！！求大神带飞！

直接一条语句实现起来很麻烦，可以把问题分成三步

在read uncommitted(未提交读)级别中，事务中的修改，即使没有提交，对其他事务也是可见的。事务可以读取未提交的数据，这种也可以叫读，这个级别其实会导有好的建议，请在下方输入你的评论。致很多问题，从性能上讲，未提交读不会比其他级别好太多，但却缺乏其他级别的好处，除非真的非常有必要，在实际中一般不使用的。

对比MySQL的B+Tree索引原理，可以发现：

mysql有个多版本控制MVCC，可以认为MVCC是行级锁的一个变种，但他在很多情况下避免了加锁作，因此开销更低。MVCC实际上是乐观并发控制的，通过每行的记录后面保存两个隐藏的列实现，一个是创建时间，一个是删除时间，当然实际存储的不是时间值，而是版本号。

MVCC只在repeatable read和read committed两个级别下工作，其他隔离级别都和MVCC不兼容，因为read uncommitted总是读到数据，而不是符合当前事务版本的数据行。

综上所述，乐观锁是和读已提交搭配使用是可以的

MySQL RANGE优化

c) 删除A表中Sid有重复的记录，如果有重复只保留一条(设A表有自增ID标识列id)

范围访问方法使用一个索引来检索包含一个或多个索引间隔中的表行的子集。它可以使用索引中的一列或者多列，以下各节描述了优化器使用范围访问的条件

Jack、lucy或者17，18叫做term，而[1,3]就是Posting list。Posting list就是一个int数组，存储了所有包含某个term的文档id，那么什么是term index和term dictionary？

对于一个单列索引，索引值间隔可以方便地由 WHERE 条件中的相应条件表示，表示为范围条件而不是 intervals 。

上述的 常量 指以下情况之一：

一些非常量可能会在优化器传播阶段转换为常量

MySQL对于每个可能使用的索引，尝试从 WHERE 子句中提取范围条件。在提取过程中，不能用于构建条件范围的条件被删除，产生重复范围的条件被合并，产生空范围的条件被删除。

提取 key1 索引的过程如下：

通常，范围扫描使用的条件比 WHERE 子句中的限制要少()。MySQL执行额外的检查来过滤满足范围条件但是不完全满足 WHERE 子句的行。

MySQL不支持为空间索引的 range 访问合并多个范围。要解决此限制，可以在相同的 SELECT 语句中使用 UNION 语句，将每个空间谓词放在不同的 SELECT 中。

多列索引的范围条件是单列索引的扩展，多列索引的范围条件将索引行限制在一个或多个索引元组的间隔中。索引元组间隔是一个按照索引顺序的，索引元组的。

设有一个多列索引 key1(key_part1,key_part2,key_part3) ,按照索引顺序，具有以下键值元组列表

但是， key_part3 = 'abc' 没有定义间隔并且不能被范围访问方法使用。

就是索引的最左前缀原则，B树索引是有序的，多列索引是首先按照列进行排序，然后在列排序的基础上，再对第二列数据进行排序，所以后面的列的顺序来看不是有序的，就不能单独用后面的列来进行排序或者范围访问的作。

这里， const1,const2... 是常量， cmp 是比较表达式： =,<=>,IS NULL ,并且条件覆盖所有的索引部分（就是说，如果有 N 个条件，那么每个条件都需要是一个 N列 索引的一部分）。例如：以下是一个三列 HASH 索引的一个范围条件

对于 BTREE 索引，一个间隔可以是使用 AND 组成的多个范围条件的，每个条件都将索引的一部分和一个常量使用 =,<=>,IS NULL,>,<,>=,<=,!=,<>,BETWEENT,LIKE 'pattern'(pattern不以通配符开始) 进行比较。只要可以确定与条件匹配的一个索引元组，就可以使用一个间隔（ !=,<> 使用两个间隔）

当比较运算符是 =,<=>,IS NULL 时，优化器尝试使用索引的其他部分来确定间隔。如果比较运算符是 >, <, >=, <=, !=, <>, BETWEEN, LIKE ,优化器使用索引，但不考虑索引中的其他列。

对于以下表达式，优化器使用个 = ，也会使用第二个 >= ,但是忽略其他索引部分，并且不将第三部分用作间隔构造。

key_part1 = 'foo' AND key_part2 >= 10 AND key_part3 > 10

单个间隔为：

如果覆盖间隔中的行的条件使用 OR 进行组合，则他们会形成间隔的并示例：集。

这个在两列索引上的条件：

间隔是：

在某些情况下， key_len 包括已使用的索引列，但是这个列可能不是你期望的，设 key_part1 和 key_part2 可以为 NULL ,然后， key_len 显示以下条件的两个索引部分长度：

key_part1 >= 1 AND key_part2 IS NOT NULL

设以下表达式, col_name 是一个索引的列

只要 col_name 等同于这些值中的任意一个，这个表达式结果就是 true 。这种比较是等值范围比较（其中的“范围”是一个单独的值）。

优化器按照以下方法，估算读取相等的值来进行等值范围比较的成本：

当使用 index dive 时，优化器在每个范围的末端进行 dive 并且使用该范围中的行数作为估算值。例如： col_name IN (10, 20, 30) 具有三个等值范围，优化器对每个范围进行两次 dive 以生成估算值。每次 dive 都会得出具有给定值的行数的估算值。

使用 index dive 提供了准确的行数估算值，但是随着表达式中要比较的值的数量增加，优化器需要使用更长的时间来生成行数的估算值。而使用索引统计信息的准确性不如直接使用索引，但是可以对大表进行更快的估算。

eq_range_index_dive_limit 选项可以控制优化器选择评估策略的值。要对 N 个等值范围使用 index dive ，将 eq_range_index_dive_limit 设置为 N+1 ,要禁用统计信息，总是使用 index dive ，将 eq_range_index_dive_limit 设置为0。

在MySQL8.0以前，除了使用 eq_range_index_dive_limit ，没有其他方法可以跳过 index dive 。在MySQL8.0中，当满足以下条件时，跳过 index dive :

不包括 FOR CONNECTION 的 EXPLAIN 输出没有变化

在执行跳过 index dive 的查询后， INFORMATION_SCHEMA.OPTIMIZER_TRACE 表包含一个值为 skipped_due_to_force_index 的 index_dives_for_range_access 行

优化器可以对这种形式的查询进行范围扫描：

SELECT ... FROM t1 WHERE ( col_1, col_2 ) IN (( 'a', 'b' ), ( 'c', 'd' ));

要使用范围扫描，查询必须满足以下条件：

要控制有多少内存可以用来进行范围优化，使用 range_optimizer_max_mem_size 变量

使用以下原则估算范围扫描使用的内存：

IN() 中的每个值被当做使用 OR 结合的一个谓词。如果有两个 IN() 列表，每个列表中都是列表中的值的数量个谓词通过 OR 结合。在这种情况下，视作 M × N 个 谓词通过OR 结合。

mysql中not in和not exists两种查询到底哪种快

Sex字段：

in和exists不论是大数据表还是小数据表，有可利用的索引还是无可利用的索引的情况下，它们的运行效率是不多的，exists也许会稍微高一点点，但是别很小。

not in 则在大数据表的情况下，不论有无可被利用的索引可以查看 EXPLAIN 输出中的 key_len 部分查看使用的索引前缀的长度。，其运行效率均极低，比无索引可用的not exists还要慢很多。

in和exists当然还有Group、分组里的hing子句、distinct关键字、limit等关键字、正则表达式等等方式都可以用于筛选记录，内容相当丰富，筛选可以通过一种或多种方式组合实施，以满足千变万化的筛选需求。常用于求交集，它们的运行效率分别不大，可根据个人喜好选用。not in和not exists常用于求非交集，小数据表时可随便用，但是碰到大数据表时就要小心了。not in不论有无可被利用的索引都会出现效率上的悲剧，应避免使用。not exists在有索引可用的情况下表现优异可作为获取非交集的，然而在无索引可用时却很慢，此时也应避免使用。

MySQL和ES的索引对比

not exists在有可被利用索引的情况下效率很高，但是在大数据表的情况下如果没有可被利用索引的情况下其运行效率。

[toc]

在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的，本文主要讨论是MyISAM和InnoDB两个存储引擎的B+Tree索引的实现方式。

MyISAM引擎使用B+Tree作为索引结构，叶的data域存放的是数据记录的地址，下面是MyISAM索引的原理图：

上图是一个MyISAM表的主索引示意，可以看出MyISAM的索引文件仅仅保存数据记录的地址，在MyIASM中，主索引和辅助索引在结构上没有任何区别，只是主索引要求key是的，而辅助索引的key可以重复。B+Tree的所有叶子包含所有关键字且按照升序排列的。

MyISAM表的索引和数据是分离的，索引保存在“表名.MYI”文件内，而数据保存在“表名.MYD”中。

虽然In以下是在 WHERE 子句中具有范围条件的查询示例noDB也使用B+Tree作为索引结构，但是具体实现方式却与MyISAM截然不同。

个重大区别是InnoDB的数据文件本身就是索引文件，从上文知道MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录地址，而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶data域保存了完整的数据记录，这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

第二个与MyISAM索引的不同时InnoDB的辅助索引data域存储相应记录主键值而不是地址，换句话说，InnoDB的所有辅助索引都引用主键作为data域，例如，下图定义在col3上的辅助索引：

这里的英文字符的ASCII码作为比较准则。聚集索引这种方式使得按照主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键索引到主索引中检索获取记录。

ES的索引不是B+Tree树，而是倒排索引，ES的倒排索引由 Term index，Term Dictionary和Posting List 组成的。

有倒排索引(inverted index)就用正排索引(forward index)，正排索引就是文档(Document)和他字段Fields正向对应的关系对应表如下：

那么倒排索引是字段Field和拥有这个Field的文档对应的关系如下：

Age字段：

如上如果name字段很多个term，比如Carla,Sara,Elin,Ada,Patty,Kate,Selena，如果按照这样的顺序排列，找出某个特定的term一定很慢，因为term没有排序，需要全部过滤一遍，才能找出特定的term。排序之后就变成了：Ada,Carla,Elin,Kate,Patty,Sara,Selena。

这样就可以使用二分法的方式，比全遍历更快的找出目标的term，如果组织这些term的方式就是 term dictionary ，意思就是term的字典，有了term dictionary之后，就可以用比较少的比较次数和磁盘读次数查找目标。但是磁盘的随机读作仍然是非常昂贵的，所以尽量少的读磁盘，有必要把一些数据缓存到内存里，但是整个Term dictionary本身又太大了，无法完整的放到内存中，于是就有了term index，Term index有点像一本字典的打的章节表。比如：

A开头的term ……………. Xxx页

C开头的term ……………. Xxx页

E开头的term ……………. Xxx页

如果所有的term都是英文字符的话，可能这个term index就真的是26个英文字符表构成了。但是实际情况是，term未必都是英文字符，term可以是任意的byteSELECT FROM TableA RIGHT OUTER JOIN TableB ON TableA.name = TableB.name数组。而且26个英文字符未必是每一个字符都有均等的term，比如x字符开头的term可能一个都没有，而s开头的term又特别多，实际的term index是一颗字典树(trie 树)：

上面例子是一个包含A", "to", "tea", "ted", "ten", "i", "in", 和 "inn" 的trie树。这棵树不会包含所有的term，他包含的是term的一些 前缀 ，通过term index可以快速定位到term dictionary的某个offest，然后从这个位置在往后顺序查找，再加上一些压缩技术，Term index的尺寸可以只有所有的term的尺寸的十分之一，用内存缓存整个term index变成可能，整体上来说就是这样的效果：

由Term index到Term Dictionary，再到ting list，通过某个字段的关键字去查询结果的过程比较清楚了，通过多个关键字的ting list进行and或者or进行交集并集的查询也简单了( 倒排索引介绍了交集并集的过程 )

mysql区间值查询，而字段值是字符串？

key_part1 >= 1 AND key_part2 上图是InnoDB主索引(同时也是数据文件)的示意图，可以看到叶包含了完整的数据记录。这种索引叫做 聚集索引 。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键(MyISAM可以没有)，如果没有显示指定，则MySQL系统会自动选择一个标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段为主键，这个字段长度为6字节，类型为长整形。< 2

其实和你原来的方式是一样的，只不过你要看你的需求是什么样的，是否是两个区间有交集就可以，还是说字段区间必须包含查询区间，如果是包含那么：

SUBSTRING_INDEX(fieldname,'这样既可，如果是有交集：~',1) <= 100 and SUBSTRING_INDEX(fieldname,'~',-1) >= 350;

SUBSTRING_INDEX(fieldname,'~',1) <= 350 and SUBSTRING_INDEX(fieldname,'~',-1) >=100;

mysql 查询 条件过滤机制是怎样的

了解不同存储引擎的索引实现对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白不建议使用过长的字段作为主键， 因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变的更大 ，在例如， 用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree非单调的主键会造成在插入新纪录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择

这是一个大题目，这里只能简单讲解一下。SQL查询的筛选就是实现从一个或多个父记录行里筛选出所需要的子记录行。

数据库引擎具体的筛选机制是相当复杂的，除非您要成为一个数据库管理系统的开发工程师，一般人没有必要去了解它。当我们需要筛选记录时将筛选逻辑的表述语句提交给数据库引擎去执行就可以了，只要描述这些筛选逻辑语句的语确，那设有以下语句， key1 是一个被索引的列，而 nonkey 没有索引么数据库引擎就会忠实地执行并返回相关的结果。

SQfull joinL语言里有多种主要方式来筛选记录。

其一是利用where子句设置筛选准则，这是最常用和最重要的筛选方式。我们知道数据库表实际上是二维表，其横向坐标是字段（属性），纵向坐标比较特别，不像电子表格excel它没有固定的行号，纵向定位必须依赖各个字段的字段值（属性值）。因此筛选准则实际就是规定字段值（或者基于字段值的计算表达式）满足（或不满足）某个或多个条件，既可以是满足的（使用=号）也可以模糊满足的（使用like运算符）。筛选可以针对单字段也可以针对多字段，我们可以通过逻辑与、逻辑或连接多个筛选条件。

其二是利用表间连接来方式来筛选记录，包括左连接、右连接、对等连接、自连接等，这种连接方法主要是用于筛选出两个记录集之间的交集或非交集。

其三是利用子查询筛选记录，包括[ANY | ALL | SOME]子查询、in子查询、exists子查询等等，其作用跟第二种类似。

oracle/mysql查询问题

最简单的实现方法就是

select from tablIndex 这种算法适用于当WHERE子句被转换成多个使用AND连接的不同索引key上的范围条件，且条件是以下两种之一：Merge Intersection算法e1 where ('2007-2' > 字段1 and '2007-2' < 字段2)

SELECT FROM TableA INNER JOIN TableB ON TableA.name = TableB.nameor

('2007-5' > 字段1 and '2007-5' < 字段2)

其他的方法不是没有不过要用到自查询，那就更麻烦了。

MySQL中inner join 和 cross join 的区别

当然没有人回答啦（反映到数据里面就是没有显示）

使用join连表，缺陷的情况下是inner join,开发中使用的left join和right join属于outer join，outer join还包括full join

现有两张表，Table A 是左边的表。Table B 是右边的表。其各有四条记录，其中有两条记录name是相同的：

1.INNER JOIN 产生的结果是AB的交集

2.LEFT [OUTER] JOIN 产生表A的完全集，而B表中匹配的则有值，没有匹配的则以null值取代。

SELECT FROM TableA LEFT OUTER JOIN TableB ON TableA.name = TableB.name

3.RIGHT [OUTER] JOIN 产生表B的完全集，而A表中匹配的则有值，没有匹配的则以nu对于 EXPLAIN FOR CONNECTION ,如果跳过了 index dive ，输出结果有所变更：ll值取代。

图标如left join类似。

4.FULL [OUTER] JOIN 产生A和B的并集。对于没有匹配的记录，则会以null做为值。

SELECT FROM TableA FULL OUTER JOIN TableB ON TableA.name = TableB.nam例子：e

可以通过is NULL将没有匹配的值找出来：

SELECT FROM TableA FULL OUTER JOIN TableB ON TableA.name = TableB.name

5. CROSS JOIN 把表A和表B的数据进行一个NM的组合，即笛卡尔积。如本例会产生44=16条记录，在开发过程中我们肯定是要过滤数据，所以这种很少用。

SELECT FROM TableA CROSS JOIN TableB

mysql索引（九）索引合并

索引合并是mysql底层为我们提供的智能算法。了解索引合并的算法，有助于我们更好的创建索引。

索引合并是通过多个range类型的扫描并且合并它们的结果集来检索行的。仅合并来自单个表的索引扫描，而不是跨多个表的索引扫描。合并会产生底层扫描的三种形式：unions（合并）、intersections（交集）、unions-of-intersections（先取交集再合并）。

以下四个例子会产生索引合并：

索引合并有以下已知的局限性：

1、如果查询语句包含一个带有AND/OR嵌套的复杂的WHERE子句而MySQL没有选择，那么可以尝试使用以下的标志符转换：

(x AND y) OR z => (x OR z) AND (y OR z)

(x OR y) AND z => (x AND z) OR (y AND z)

2、索引合并不适用于你的本意是问：你们当中有谁是男人，或者是女人的？全文索引。

索引合并访问方法有几种算法，表现在 EXPLAIN 语句输出的Extra字段中：

下面将更详细地描述这些算法。优化器根据各种可用选项的成本估计，在不同的索引合并算法和其他访问方法之间进行选择。

Index Merge Intersection算法对所有使用的索引执行同步扫描，并生成从合并的索引扫描接收到的行序列的交集。

一、这种形式的N部分表达式，索引正好包括N个字段（所有索引字段都被覆盖），N>=1，N如果大于1就是复合索引：

二、InnoDB表主键上的任何范围条件。

Index Merge Union算法

该算法类似于Index Merge Intersection算法，适用于当WHERE子句被转换成多个使用OR连接的不同索引key上的范围条件，且条件是以下三种之一：

一、这种形式的N部分表达式，索引正好包括N个字段（所有索引字段都被覆盖），N>=1，N如果大于1就是复合索引：

二、InnoDB表主键上的任何范围条件。

三、符合Index Merge Intersection算法的条件。

Index Merge Sort-Union算法

该算法适用于当WHERE子句被转换成多个使用OR连接的不同索引key上的范围条件，但是不符合 In可以写存储过程将r，l生成临时表，导出三步的结果。dex Merge Union算法的。Index Merge Sort-Union和Index Merge Union算法的区别在于，Index Merge Sort-Union必须首先获取所有行的行id并在返回任何行之前对它们进行排序。

欢迎访问个人博客