Mysql中的force index和ignore index

前几天统计一个sql,是一个人提交了多少工单,顺便做了相关sql优化。数据大概2000多w。

为了实验最少受其他因素干扰,将生产库的200多w数据导出来,用测试服务器进行测试。 导出来的数据是一个堆表,没有主键,没有索引。

 1.堆表的情况 这时候就在这时候,用执行计划分析下语句。

select_type的值为SIMPLE,表示简单的select查询,不使用union或子查询。 type的值为ALL,表示要对表进行表扫描。 possible_keys 表示能使用哪个索引找到行记录。 key 表示Mysql决定使用的索引(键)。 key_len 表示Mysql决定使用索引的长度。 ref  表示使用哪个列和key一起从表中选择行。 rows 表示Mysql认为它执行查询时必须检查的行数。 extra 表示查询的详情信息,用到where,临时表,排序。   执行下该语句三次,发现执行了16.30 sec、16.34 sec、16.24 sec。  2.有索引的情况 建了四个索引,分别以custname,CreateDate建两个单列索引,另外两个是联合索引,只是最左边列不一样。

之后,用执行计划分析下sql查询语句。

从执行计划可以看出,Mysql从四个索引中选取了ix_datename这个索引,type为range表示索引范围扫描。rows的数量值是没堆表的1/3。 执行语句三次,时间是 8.64 sec、8.61sec、8.55 sec。   我建了三个索引,那么我想用下另外三个索引怎么办? 这里可以用force index(),这个指令可以指定本次查询强制使用哪个索引,因为Mysql优化器的选择并不是最优的索引。

 选用另一个联合索引 ix_namedate,这次type变为index,可以这样理解,根据索引的顺序进行全表扫描,比ALL效率要高些,rows的值和堆表的值差不多。 执行语句三次,时间是 7.84 sec、7.92 sec、7.84 sec。  

  选用另一个联合索引 ix_name,这次type是index,可以这样理解,根据索引的顺序进行全表扫描,比ALL效率要高些,rows的值和堆表的值差不多。 执行语句三次,时间是 1 min 28.17 sec、1 min 27.64 sec、1 min 27.58 sec。  

选用另一个联合索引 ix_date,这次type是range,表示索引范围扫描,rows的值是堆表的1/3多些 。 执行语句三次,时间是 9.55 sec、9.52 sec、9.39 sec。   假如我不想用索引了怎么办? 可以使用ignore index(),这个指令可以强制Mysql在查询时,不使用某索引。  

上面第一个强制不使用ix_date索引,那么就Mysql就从剩下的三个索引中,选取他认为是最优的索引。第二个时将四个索引都不使用,那么Mysql就进行全表扫描了。     总结:       1.Mysql的语句优化,没有绝对的正确,explain也只是给出个大致的方向,例如 key_len值小的,rows小的按理说,时间应该最短,效率最高。但是,实验中时间最少的却不是那个值最小的。        2. 优化还需根据实际数据情况,例如,假如我where选取的时间范围变化,或者说CustName的分布有些变化,可能跟刚才的实验,又会产生一定偏差。        3. 同样我还实验了,当给表加上主键时,整体的查询时间会缩短些。     ——————附相关index命令————--

  参考:mysql如何添加主键约束和唯一性约束,删除主键和唯一性约束 mysql强制使用索引与不使用索引 利用 force index优化sql语句性能 mysql 存在索引但不能使用索引的典型场景 mysql explain用法和结果的含义 MySQL 优化之 index merge(索引合并) MySQL单列索引和组合索引(联合索引)的区别详解 休对故人思故国 且将新火试新茶 诗酒趁年华

MySql异常:尝试读取超出流末尾的内容

问题详细描述: 2020-09-04 11:28:19,576 [DefaultQuartzScheduler_Worker-1] DEBUG MySql.Data.MySqlClient.MySqlException (0x80004005): Fatal error encountered during command execution. —> MySql.Data.MySqlClient.MySqlException (0x80004005): Fatal error encountered attempting to read the resultset. —> MySql.Data.MySqlClient.MySqlException (0x80004005): Reading from the stream has failed. —> System.IO.EndOfStreamException: 尝试读取超出流末尾的内容。 在 MySql.Data.MySqlClient.MySqlStream.ReadFully(Stream stream, Byte[] buffer, Int32 offset, Int32 count) 在 MySql.Data.MySqlClient.MySqlStream.LoadPacket() 在 MySql.Data.MySqlClient.MySqlStream.LoadPacket() 在 MySql.Data.MySqlClient.MySqlStream.ReadPacket() 在 MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId) 在 MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId) 在 MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force) 在 MySql.Data.MySqlClient.MySqlDataReader.NextResult() 在 MySql.Data.MySqlClient.MySqlDataReader.NextResult() 在 MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) 在 MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) 在 MySql.Data.MySqlClient.MySqlCommand.ExecuteDbDataReader(CommandBehavior behavior) 在 System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) 在 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) 在 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) 在 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) 原因:这个要被查询的表是空的。 解决方法:在连接串后面加入“Allow User Variables=True”就可以实现,SQL语句参数自定义化。为什么能解决我也不知道,知道的大佬可以在评论区留言。 参考链接:https://bbs.csdn.net/topics/390636133 加上这个之后,我发现无法彻底解决这个问题。后来Google了一下。找到解决办法。同样也是在数据库连接字符串加上"SslMode=none". 参考链接:http://www.z-xl.top/?p=229 再加这个:"Connection Timeout =30"设置超时时间为30秒   from:https://www.cnblogs.com/birdGe/p/13614098.html

MYSQL连接字符串参数解析(解释)

被迫转到MySQL数据库,发现读取数据库时,tinyint类型的值都被转化为boolean了,这样大于1的值都丢失,变成true了。查阅资料MySQL中无Boolean类型,都是存储为tinyint了,这也无妨,但是读回时不应该都变成boolean啊。网络检索没有解决方案,准备向官方报告bug,细想官方不应该有这样低级错误,先下载Connector/NET源码研究一下,发现其中有TreatTinyAsBoolean这个属性,检索百度无结果,google有几条英文的,看来大家都没有注意到这点,或者说很少用.Net玩 MySQL的。 这样将数据库连接字符串变为这样形式就一切正常了: Database=dbname;Data Source=192.168.1.1;Port=3306;User Id=root;Password=****;Charset=utf8;TreatTinyAsBoolean=false; 顺便把源码中涉及的所有属性都列出来: Server,host, data source, datasource, address, addr, network address: 数据库位置(以上任何关键字均可) Database,initial catalog:数据库名 Port:        socket 端口,默认 3306 ConnectionProtocol,protocol:    连接协议,默认 Sockets PipeName,pipe:        连接管道,默认 MYSQL UseCompression,compress:    连接是否压缩,默认 false AllowBatch:    是否允许一次执行多条SQL语句,默认 true Logging:    是否启用日志,默认 false SharedMemoryName:内存共享的名称,默认 MYSQL UseOldSyntax,old syntax, oldsyntax:是否兼容旧版的语法,默认 false ConnectionTimeout,connection timeout:连接超时等待时间,默认15s DefaultCommandTimeout,command timeout:MySqlCommand 超时时间,默认 30s UserID, uid, username, user name, user:数据库登录帐号 Password,pwd:    登录密码 PersistSecurityInfo:是否保持敏感信息,默认 false Encrypt:已经用 SSL 替代了,默认 false CertificateFile:证书文件(.pfx)格式 CertificatePassword:证书的密码 CertificateStoreLocation:证书的存储位置 CertificateThumbprint:证书指纹 AllowZeroDateTime:日期时间能否为零,默认 false ConvertZeroDateTime:为零的日期时间是否转化为 DateTime.MinValue,默认 false UseUsageAdvisor, usage advisor:是否启用助手,会影响数据库性能,默认 false ProcedureCacheSize,procedure cache, procedurecache:同一时间能缓存几条存储过程,0为禁止,默认 25 UsePerformanceMonitor,userperfmon, perfmon:是否启用性能监视,默认 false IgnorePrepare:    是否忽略 Prepare() 调用,默认 true UseProcedureBodies,procedure bodies:是否检查存储过程体、参数的有效性,默认 true AutoEnlist:    是否自动使用活动的连接,默认 true RespectBinaryFlags:是否响应列上元数据的二进制标志,默认 true TreatTinyAsBoolean:是否将 TINYINT(1) 列视为布尔型,默认 true AllowUserVariables:是否允许 SQL 中出现用户变量,默认 false InteractiveSession,interactive:会话是否允许交互,默认 false FunctionsReturnString:所有服务器函数是否按返回字符串处理,默认 false UseAffectedRows:是否用受影响的行数替代查找到的行数来返回数据,默认 false OldGuids:    是否将 binary(16) 列作为 Guids,默认 false Keepalive:    保持 TCP 连接的秒数,默认0,不保持。 ConnectionLifeTime:连接被销毁前在连接池中保持的最少时间(秒)。默认 0 Pooling:    是否使用线程池,默认 true MinimumPoolSize, min pool size:线程池中允许的最少线程数,默认 0 MaximumPoolSize,max pool size:线程池中允许的最多线程数,默认 100 ConnectionReset:连接过期后是否自动复位,默认 false CharacterSet, charset:向服务器请求连接所使用的字符集,默认:无 TreatBlobsAsUTF8:binary blobs 是否按 utf8 对待,默认 false BlobAsUTF8IncludePattern:列的匹配模式,一旦匹配将按 utf8 处理,默认:无 SslMode:    是否启用 SSL 连接模式,默认:MySqlSslMode.None   from:https://blog.csdn.net/caijing3210/article/details/7205423

MySQL:创建高性能的索引

一、索引基础  定义 索引,也叫做“键(Key)”,是存储引擎用于快速查找记录的一种数据结构。索引对于良好的性能非常关键,索引是对查询性能优化最有效的手段。 索引类型 1.B-Tree索引 当人们谈论索引的时候,如果没有特别指明类型,那多半说的是B-Tree索引,它使用B-Tree数据结构来存储数据。B-Tree通常意味着所有的值是按顺序存储的,并且每一个叶子节点到根的距离相同。 B-Tree索引的几个匹配原则: a.全值匹配:和索引中的所有列进行匹配。 b.匹配最左前缀:即索引的第一列。 c.匹配列前缀:即只匹配某一列的值的开头部分。 d.匹配范围值。 e.精确匹配某一列并范围匹配另外一列。 f.只访问索引的查询,即:覆盖索引。   B-Tree索引的几个限制(索引失效): a.不是按照索引的最左列开始查找,则不能使用索引。 b.不能跳过索引中的列。 c.如果查询中有某个列的范围查询,则其右边所有列都无法使用索引。   2.哈希索引 哈希索引(hash index)基于哈希表实现,只有精确匹配索引所有列的查询才有效。在MySQL中只有Memory引擎显式支持哈希索引。我们在这里只作了解。   3.R-Tree索引 R-Tree索引(空间数据索引),可以用作地理数据存储。MySQL中目前仅MyISAM引擎支持。与B-Tree不同,此类型的索引无须前缀查询。必须使用MySQL的GIS相关函数来维护数据,但遗憾的是MySQL的GIS支持并不完善。   4.全文索引 全文索引是一种特殊类型的索引,它查找的是文本中关键词,而不是直接比较索引中的值。全文索引更类似于搜索引擎做的事情,而不是简单的WHERE条件匹配。   二、索引的优点 优点: 1.索引大大减少了服务器需要扫描的数据量。 2.索引可以帮助服务器避免排序和临时表。 3.索引可以将随机I/O变为顺序I/O。   什么样的表需要使用索引?简单的说应该遵循以下3条准则: 1.非常小的表:全表扫描更高效。 2.中到大型表:索引非常有效。 3.特大型的表:创建和使用索引的代价非常高。如果表有大量插入和更新,更新索引将是很大的一个开销。对于特大表,建议使用表分区技术,分区后再使用索引。   三、高性能的索引策略 示例以【居民表:resident】为例,以下是此表的结构: 1.独立的列 独立的列是指索引列不能是表达式的一部分,也不能是函数的参数。 反例:   2.前缀索引和索引选择性 前缀索引:如果需要索引的字符列很长,这会让索引变得大且慢。通常可以只索引此列开始的部分字符。这样可以大大节约索引空间,从而提高索引效率。 索引选择性:是指不重复的索引值和数据表的记录总数的比值。此值越高,索引效率越高。例如:唯一索引和主键的索引选择性是1,性能也是最好的。 索引选择性是创建前缀索引依据。 例子:给列RESIDENT_NAME加索引,varchar(500)显然太长了,我们用前缀做索引。 先计算完整列的选择性:0.4867 再计算最接近的前缀选择性,可以看到15个字符后,20个字符也是0.4867,因此15个字符作为前缀是最合适的。 最后创建索引:   3.多列索引 很多人对多列索引的理解都不够。一个常见的错误是:为每个列都创建独立的索引;另一个是按照错误的顺序创建多列索引。 反例:独立索引对多条件查询的性能提升是很小的,一般只能匹配到一个索引,效率肯定要大打折扣的。 正例:创建一个多列索引,扫描的行数马上降了下来,快了十几倍,这还只是个简单的示例。   4.选择合适的列顺序 最让人困惑的问题莫过于索引列的顺序,正确的顺序依赖于使用该索引的查询。也要考虑到排序和分组的需要。 例子:是什么让我决定以SP_ID在前创建了上面的多列索引:SP_ID_GENDER_CODE ? 答案是:计算各列的选择性。   5.聚簇索引 聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。主键就是一个典型的聚簇索引。   6.覆盖索引 如果一个索引包含要查询的所有字段,不需要再去表里读取数据,这样的索引就叫做覆盖索引。覆盖索引能极大的提高查询性能。 例子:把查询中需要的字段改为索引中的字段时,这样一个覆盖索引就形成了。索引也只匹配到多列索引,ref也变为了常数。Extra也显示了Using index。   7.用索引做排序 可以用同一个索引既满足排序,又用于查询。这样的索引是最优的,历为我们日常工作中遇到查询一般都是要排序的。这里有个限制:如果查询涉及到多表联合,排序用的字段必须全部是第一个表的,才能使用索引做排序。 例子:   8.冗余和重复索引 重复索引:是指在相同的列上按照相同的顺序创建的相同类型的索引。避免出现这样的情况,发现要删除。 冗余索引:是指一个或多个列同步出现在多个索引中,各索引的列数、顺序不同。冗余索引也应避免。但有时查询写的不合理,可能出现单独为优化某个查询出现的冗余索引。 P.S 创建冗余索引时可能影响其他索引的匹配,从而导致以前的查询性能降低。   四、索引案例 索引 index(a, b, c) where 语句 索引是否被使用 where a=3 Y,使用到 a where a=3 and b=5 Y,使用到 a,b where a=3 and b=5 and c=4 Y,使用到 a,b,c where b=3 或者 where b=3 and c=4 或者 where c=4 N,没有 a where a=5 and c=3 Y,使用到 a,但是 c 不可以,中间 b 断了 where a=3 and b>4 and c=5 Y,使用到 a 和 b,但是 c 不可以,b 是范围 where a=3 and b like 'kk%' and c=4 Y,使用到 a,b,c where a=3 and b like '%kk' and c=4 Y,使用到 a where a=3 and b like '%kk%' and c=4 Y,使用到 a where a=3 and b like 'k%kk%' and c=4 Y,使用到 a,b,c   五、总结 1.单行访问是很慢的。 2.按顺序访问范围数据是很快的。 3.索引覆盖查询是很快的。 4.查询中如果有多个范围查询,只有一个可以使用上索引。  

如何查询mysql数据库大小

要想知道每个数据库的大小的话,步骤如下: 1、进入information_schema 数据库(存放了其他的数据库的信息) use information_schema; 2、查询所有数据的大小: select concat(round(sum(data_length/1024/1024),2),’MB') as data from tables; 3、查看指定数据库的大小: 比如查看数据库home的大小 select concat(round(sum(data_length/1024/1024),2),’MB') as data from tables where table_schema=’home'; 4、查看指定数据库的某个表的大小 比如查看数据库home中 members 表的大小 select concat(round(sum(data_length/1024/1024),2),’MB') as data from tables where table_schema=’home' and table_name=’members';   from:https://blog.csdn.net/weixin_34050389/article/details/89826749

MySQL事务与MVCC如何实现的隔离级别

前言 其实数据库章节基本上的知识点我都写过一遍了,包括这篇事务和MVCC的,但是国庆期间我翻阅资料的时候我发现之前写的还差点意思,例子举得也差点意思,那我就根据我自己最新的理解,加上之前的总结相当于重写了,希望你也有新的收获。   数据库事务介绍   事务的四大特性(ACID) 原子性(atomicity): 事务的最小工作单元,要么全成功,要么全失败。 一致性(consistency): 事务开始和结束后,数据库的完整性不会被破坏。 隔离性(isolation): 不同事务之间互不影响,四种隔离级别为RU(读未提交)、RC(读已提交)、RR(可重复读)、SERIALIZABLE (串行化)。 持久性(durability): 事务提交后,对数据的修改是永久性的,即使系统故障也不会丢失。   事务的隔离级别   读未提交(Read UnCommitted/RU) 又称为脏读,一个事务可以读取到另一个事务未提交的数据。这种隔离级别岁最不安全的一种,因为未提交的事务是存在回滚的情况。   读已提交(Read Committed/RC) 又称为不可重复读,一个事务因为读取到另一个事务已提交的修改数据,导致在当前事务的不同时间读取同一条数据获取的结果不一致。 举个例子,在下面的例子中就会发现SessionA在一个事务期间两次查询的数据不一样。原因就是在于当前隔离级别为 RC,SessionA的事务可以读取到SessionB提交的最新数据。 发生时间 SessionA SessionB 1 begin; 2 select * from user where id=1;(张三) 3 update user set name=’李四' where id=1;(默认隐式提交事务) 4 select * from user where id=1;(李四) 5 update user set name=’王二' where id=1;(默认隐式提交事务) 6 select * from user where id=1;(王二)   可重复读(Repeatable Read/RR) 又称为幻读,一个事物读可以读取到其他事务提交的数据,但是在RR隔离级别下,当前读取此条数据只可读取一次,在当前事务中,不论读取多少次,数据任然是第一次读取的值,不会因为在第一次读取之后,其他事务再修改提交此数据而产生改变。因此也成为幻读,因为读出来的数据并不一定就是最新的数据。 举个例子:在SessionA中第一次读取数据时,后续其他事务修改提交数据,不会再影响到SessionA读取的数据值。此为可重复读。 发生时间 SessionA SessionB 1 begin; 2 select * from user where id=1;(张三) 3 update user set name=’李四' where id=1;  (默认隐式提交事务) 4 select * from user where id=1;(张三) 5 update user set name=’王二' where id=1;(默认隐式提交事务) 6 select * from user where id=1;(张三)   串行化(Serializable) 所有的数据库的读或者写操作都为串行执行,当前隔离级别下只支持单个请求同时执行,所有的操作都需要队列执行。所以种隔离级别下所有的数据是最稳定的,但是性能也是最差的。数据库的锁实现就是这种隔离级别的更小粒度版本。 发生时间 SessionA SessionB 1 begin; 2 begin; 3 update user set name=’李四' where id=1; 4 select * from user where id=1;(等待、wait) 5 commit; 6 select * from user where id=1;(李四)   事务和MVCC原理   不同事务同时操作同一条数据产生的问题 示例: 发生时间 SessionA SessionB 1 begin; 2 begin; 3 查询余额 = 1000元 4 查询余额 = 1000元 5 存入金额 100元,修改余额为 1100元 6 取出现金100元,此时修改余额为900元 8 提交事务(余额=1100) 9 提交事务(余额=900) 发生时间 SessionA SessionB 1 begin; 2 begin; 3 查询余额 = 1000元 4 查询余额 = 1000元 5 存入金额 100元,修改余额为 1100元 6 取出现金100元,此时修改余额为900元 8 提交事务(余额=1100) 9 撤销事务(余额恢复为1000元) 上面的两种情况就是对于一条数据,多个事务同时操作可能会产生的问题,会出现某个事务的操作被覆盖而导致数据丢失。   LBCC 解决数据丢失 LBCC,基于锁的并发控制,Lock Based Concurrency Control。 使用锁的机制,在当前事务需要对数据修改时,将当前事务加上锁,同一个时间只允许一条事务修改当前数据,其他事务必须等待锁释放之后才可以操作。   MVCC 解决数据丢失 MVCC,多版本的并发控制,Multi-Version Concurrency Control。 使用版本来控制并发情况下的数据问题,在B事务开始修改账户且事务未提交时,当A事务需要读取账户余额时,此时会读取到B事务修改操作之前的账户余额的副本数据,但是如果A事务需要修改账户余额数据就必须要等待B事务提交事务。 MVCC使得数据库读不会对数据加锁,普通的SELECT请求不会加锁,提高了数据库的并发处理能力。借助MVCC,数据库可以实现READ COMMITTED,REPEATABLE READ等隔离级别,用户可以查看当前数据的前一个或者前几个历史版本,保证了ACID中的I特性(隔离性)。   InnoDB的MVCC实现逻辑   InnoDB存储引擎保存的MVCC的数据 InnoDB的MVCC是通过在每行记录后面保存两个隐藏的列来实现的。一个保存了行的事务ID(DB_TRX_ID),一个保存了行的回滚指针(DB_ROLL_PT)。每开始一个新的事务,都会自动递增产 生一个新的事务id。事务开始时刻的会把事务id放到当前事务影响的行事务id中,当查询时需要用当前事务id和每行记录的事务id进行比较。 下面看一下在REPEATABLE READ隔离级别下,MVCC具体是如何操作的。 SELECT InnoDB 会根据以下两个条件检查每行记录: InnoDB只查找版本早于当前事务版本的数据行(也就是,行的事务编号小于或等于当前事务的事务编号),这样可以确保事务读取的行,要么是在事务开始前已经存在的,要么是事务自身插入或者修改过的。 删除的行要事务ID判断,读取到事务开始之前状态的版本,只有符合上述两个条件的记录,才能返回作为查询结果。 INSERT InnoDB为新插入的每一行保存当前事务编号作为行版本号。 DELETE InnoDB为删除的每一行保存当前事务编号作为行删除标识。 UPDATE InnoDB为插入一行新记录,保存当前事务编号作为行版本号,同时保存当前事务编号到原来的行作为行删除标识。 保存这两个额外事务编号,使大多数读操作都可以不用加锁。这样设计使得读数据操作很简单,性能很好,并且也能保证只会读取到符合标准的行。不足之处是每行记录都需要额外的存储空间,需要做更多的行检查工作,以及一些额外的维护工作。 MVCC只在REPEATABLE READ和READ COMMITIED两个隔离级别下工作。其他两个隔离级别都和 MVCC不兼容 ,因为READ UNCOMMITIED总是读取最新的数据行,而不是符合当前事务版本的数据行。而SERIALIZABLE则会对所有读取的行都加锁。 MVCC 在mysql 中的实现依赖的是 undo log 与 read view 。   undo log 根据行为的不同,undo log分为两种:insert undo log 和 update undo log insert undo log: insert 操作中产生的undo log,因为insert操作记录只对当前事务本身课件,对于其他事务此记录不可见,所以 insert undo log 可以在事务提交后直接删除而不需要进行purge操作。 purge的主要任务是将数据库中已经 mark del 的数据删除,另外也会批量回收undo pages 数据库 Insert时的数据初始状态: update undo log: update 或 delete 操作中产生的 undo log。因为会对已经存在的记录产生影响,为了提供 MVCC机制,因此update undo log 不能在事务提交时就进行删除,而是将事务提交时放到入 history list 上,等待 purge 线程进行最后的删除操作。 数据第一次被修改时: 当另一个事务第二次修改当前数据: 为了保证事务并发操作时,在写各自的undo log时不产生冲突,InnoDB采用回滚段的方式来维护undo log的并发写入和持久化。回滚段实际上是一种 Undo 文件组织方式。   ReadView 对于 RU(READ UNCOMMITTED) 隔离级别下,所有事务直接读取数据库的最新值即可,和 SERIALIZABLE 隔离级别,所有请求都会加锁,同步执行。所以这对这两种情况下是不需要使用到 Read View 的版本控制。 对于 RC(READ COMMITTED) 和 RR(REPEATABLE READ) 隔离级别的实现就是通过上面的版本控制来完成。两种隔离界别下的核心处理逻辑就是判断所有版本中哪个版本是当前事务可见的处理。针对这个问题InnoDB在设计上增加了ReadView的设计,ReadView中主要包含当前系统中还有哪些活跃的读写事务,把它们的事务id放到一个列表中,我们把这个列表命名为为m_ids。 对于查询时的版本链数据是否看见的判断逻辑: 如果被访问版本的 trx_id 属性值小于 m_ids 列表中最小的事务id,表明生成该版本的事务在生成 ReadView 前已经提交,所以该版本可以被当前事务访问。 如果被访问版本的 trx_id 属性值大于 m_ids 列表中最大的事务id,表明生成该版本的事务在生成 ReadView 后才生成,所以该版本不可以被当前事务访问。 如果被访问版本的 trx_id 属性值在 m_ids 列表中最大的事务id和最小事务id之间,那就需要判断一下 trx_id 属性值是不是在 m_ids 列表中,如果在,说明创建 ReadView 时生成该版本的事务还是活跃的,该版本不可以被访问;如果不在,说明创建 ReadView 时生成该版本的事务已经被提交,该版本可以被访问。 举个例子:   READ COMMITTED 隔离级别下的ReadView 每次读取数据前都生成一个ReadView (m_ids列表) 时间 Transaction 777 Transaction 888 Trasaction 999 T1 begin; T2 begin; begin; T3 UPDATE user SET name = 'CR7' WHERE id = 1; T4 … T5 UPDATE user SET name = 'Messi' WHERE id = 1; SELECT * FROM user where id = 1; T6 commit; T7 UPDATE user SET name = 'Neymar' WHERE id = 1; T8 SELECT * FROM user where id = 1; T9 UPDATE user  SET name = 'Dybala' WHERE id = 1; T10 commit; T11 SELECT * FROM user where id = 1; 这里分析下上面的情况下的ReadView 时间点 T5 情况下的 SELECT 语句: 当前时间点的版本链: 此时 SELECT 语句执行,当前数据的版本链如上,因为当前的事务777,和事务888 都未提交,所以此时的活跃事务的ReadView的列表情况 m_ids:[777, 888]  ,因此查询语句会根据当前版本链中小于 m_ids 中的最大的版本数据,即查询到的是 Mbappe。 时间点 T8 情况下的 SELECT 语句: 当前时间的版本链情况: 此时 SELECT 语句执行,当前数据的版本链如上,因为当前的事务777已经提交,和事务888 未提交,所以此时的活跃事务的ReadView的列表情况 m_ids:[888]  ,因此查询语句会根据当前版本链中小于 m_ids 中的最大的版本数据,即查询到的是 Messi。 时间点 T11 情况下的 SELECT 语句: 当前时间点的版本链信息: 此时 SELECT 语句执行,当前数据的版本链如上,因为当前的事务777和事务888 都已经提交,所以此时的活跃事务的ReadView的列表为空 ,因此查询语句会直接查询当前数据库最新数据,即查询到的是 Dybala。 总结:使用READ COMMITTED隔离级别的事务在每次查询开始时都会生成一个独立的 ReadView。   REPEATABLE READ 隔离级别下的ReadView […]

MySQL自定义函数:身份证号15位转18位

 

【mysql】 1292. Truncated incorrect INTEGER value: "

错误分析 一般来讲,找到对应的insert字段,然后看一下是否是由于字段类型不匹配导致的。 例如,表中声明的是bigInt类型,你传值传了个字符串进入。 另外,如果你是通过insert into select的方式,将查询结果导入到新的表中,可能你单独执行select中的内容,是可以查询到相应的结果,但是当你执行insert into语句时,会产生如下错误

在MySQL的论坛上找到一个哥们儿说的内容,也就是说这个1292的错误,有可能并不是错误,而是警告提示。可以通过ignore关键字进行警告屏蔽 所以,我把自己的代码前缀,改成如下格式,即可正常执行导入操作

  ———————————————— 版权声明:本文为CSDN博主「小魏的马仔」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。 原文链接:https://blog.csdn.net/xuexiiphone/article/details/103426404

MySQL处理JSON数组,数组转字符串

GROUP_CONCAT:group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc  ] [separator '分隔符'] ) SUBSTRING_INDEX:substring_index(str,delim,count) str:要处理的字符串 delim:分隔符 count:计数 SUBSTRING:SUBSTRING(string,position) JSON_EXTRACT:取json字符串字段下的某个键的值

必须知道的SQL语句不走索引时的排查利器

前言: 在索引优化时,经常会看到的一句话:如果索引字段出现隐式字符集转换的话,那么索引将失效,进而转为全表扫描,查询效率将大大降低,要避免出现隐式字符集转换; 在此我想问问同学们: 大家知道为什么隐式字符集转换会导致索引失效吗? 实际场景中有没有遇到过隐式字符集转换导致索引失效的场景,具体排查的过程; 本文主线: 由上面的两个问题牵引出了本文的主线; 简单描述下隐式字符集转换导致索引失效的原因 然后模拟实际场景排查隐式字符集转换导致索引失效的过程 隐式字符集转换导致索引失效的原因 MySQL索引的数据结构是 B+Tree,想要走索引查询必须要满足其 最左前缀原则 ,否则无法通过索引树进行查找,只能进行全表扫描; 例如:下面的这个SQL由于在 索引字段 上使用函数进行运算,导致索引失效

  上面的这个SQL怎么改造才能使索引生效呢?如下所示:

  通过上面的小例子可以知道,如果在索引字段上使用函数运算,则会导致索引失效,而索引字段的 隐式字符集转换 由于MySQL会自动的在索引字段上加上 转换函数 ,进而会导致索引失效; 那接下来我们就通过模拟的实际场景来具体看看是不是由于MySQL自动给加上了转换函数而导致索引失效的; 模拟场景 + 问题排查 由于导致索引失效的原因有很多,如果自己写的SQL怎么看都没问题,但是通过查看执行计划发现就是没有走索引查询,此时就会让很多人陷入困境,这到底是怎么导致的呢? 此时本文重点将要讲述的工具就要闪亮登场啦: explain extended + show warnings ; 使用这个工具可以将执行的SQL语句的一些扩展信息展示出来,这些扩展信息就包括:MySQL优化时可能会添加上字符集转换函数,使得字符集不匹配的SQL可以正确执行下去; 下面就来具体聊聊 explain extended + show warnings 的使用; 模拟隐式字符集转换的场景: 首先创建两个字符集不一样的表:

  然后使用存储过程构造数据:

  注意:在构造数据时,记得将 t_employees 表中的 de_no 字段值构造的 离散些 ,因为如果索引字段值的 区分度很低 的话,那么MyQSL优化器通过采样统计分析时,发现索引查询和全表扫描性能差不多,就会直接进行全表扫描了; 索引失效的查询SQL语句: 将表和数据构造完后,我们使用SQL语句进行查询下,然后再看看其执行计划;

  其执行计划如下: 发现 t_employees 表中的 de_no 字段有索引,但是没有走索引查询,type=ALL 走的全表扫描,但是通过查看SQL语句发现其没有问题呀,表面看上去都是满足走索引查询的条件呀,排查到这发现遇到了困境,苦恼啊! 还好,通过在网络世界上遨游,最终发现了 explain extended + show warnings 利器,利用它快速发现了索引失效的根本原因,然后快速找到了解决方案; 下面就来聊聊这个利器的具体使用,开森! 使用利器快速排查问题: 注意:explain 后面跟的关键字 EXTENDED(扩展信息) 在MySQL5.7及之后的版本中废弃了,但是该语法仍被识别为向后兼容,所以在5.7版本及后续版本中,可以不用在 explain 后面添加 EXTENDED 了; EXTENDED关键字的具体查阅资料:https://dev.mysql.com/doc/refman/5.7/en/explain-extended.html 具体使用方法如下: ①、首先在MySQL的可视化工具中打开一个 命令列介面 :工具 --> 命令列介面 ②、然后输入下面的SQL并按回车:

  ③、然后紧接着输入命令 show warnings; 并回车,会出现如下图所示内容: 通过展示出的执行SQL扩展信息,发现MySQL在字符集不一致时自动添加上字符集转换函数,因为是在 索引字段 de_no 上添加的转换函数,所以就导致了索引失效; 而如果我们没看扩展信息的话,那么可能直到我们查看表结构的时候才会发现是由于字符集不一致导致的,这样就会花费很多的时间; 扩展:隐式类型转换 咱们聊完上面的隐式字符集转换导致索引失效的情况,再来简单聊聊另一种 隐式类型转换 导致索引失效的情况; 隐式类型转换:简单的说就是字段的类型与其赋值的类型不一致时会进行隐式的转换; 小例如下:

  上面的SQL中 em_name 为索引字段,字段类型是 varchar,为其赋 int 类型的值时,会发现索引失效,这里也可以通过 explain extended + show warnings 查看,会发现如下图所示内容: 至此本文进入结尾,在此再说明下,上文中测试时使用的MySQL版本都是 5.7 ;   from:https://my.oschina.net/u/4216693/blog/5122830

大批量更新数据mysql批量更新的四种方法

mysql 批量更新如果一条条去更新效率是相当的慢, 循环一条一条的更新记录,一条记录update一次,这样性能很差,也很容易造成阻塞。 mysql 批量更新共有以下四种办法 1、.replace into 批量更新

  2、insert into …on duplicate key update批量更新

  3.创建临时表,先更新临时表,然后从临时表中update

  注意:这种方法需要用户有temporary 表的create 权限。 4、使用mysql 自带的语句构建批量更新 mysql 实现批量 可以用点小技巧来实现:

  这句sql 的意思是,更新orderId 字段,如果id=1 则orderId 的值为3,如果id=2 则orderId 的值为4…… where部分不影响代码的执行,但是会提高sql执行的效率。确保sql语句仅执行需要修改的行数,这里只有3条数据进行更新,而where子句确保只有3行数据执行。 如果更新多个值的话,只需要稍加修改:

  到这里,已经完成一条mysql语句更新多条记录了。 php中用数组形式赋值批量更新的代码:

  这个例子,有8条记录进行更新。代码也很容易理解,你学会了吗 更新 100000条数据的性能就测试结果来看,测试当时使用replace into性能较好。 replace into 和 insert into on duplicate key update的不同在于: replace into 操作本质是对重复的记录先delete 后insert,如果更新的字段不全会将缺失的字段置为缺省值,用这个要悠着点否则不小心清空大量数据可不是闹着玩的。 insert into 则是只update重复记录,不会改变其它字段。   from:https://www.cnblogs.com/mslagee/p/6509682.html

MySQLHelper/Util类