mysql插入数据后返回自增ID的方法 mysql在插入一条数据后，如何能获得到这个自增id的值呢？即怎么获取设置为自增主键的id（AUTO_INCREMENT）?? 方法一：是使用last_insert_id
|
1 |
mysql> SELECT LAST_INSERT_ID(); |
产生的自增ID每次连接后保存在服务器中。 这意味着函数向一个给定客户端返回的值是该客户端产生对影响AUTO_INCREMENT列的最新语句第一个 AUTO_INCREMENT值的。 这个值不能被其它客户端影响，即使它们产生它们自己的 AUTO_INCREMENT值。 这个行为保证了你能够找回自己的 ID 而不用担心其它客户端的活动，而且不需要加锁或处理。 每次mysql_query操作在mysql服务器上可以理解为一次“原子”操作, 写操作常常需要锁表的， 是mysql应用服务器锁表不是我们的应用程序锁表。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
值得注意的是，如果你一次插入了多条记录，这个函数返回的是第一个记录的ID值。 因为LAST_INSERT_ID是基于Connection的，只要每个线程都使用独立的Connection对象， LAST_INSERT_ID函数将返回该Connection对AUTO_INCREMENT列最新的insert or update*作生成的第一个record的ID。 这个值不能被其它客户端（Connection）影响，保证了你能够找回自己的 ID 而不用担心其它客户端的活动，而且不需要加锁。 使用单INSERT语句插入多条记录, LAST_INSERT_ID返回一个列表。 LAST_INSERT_ID 是与table无关的，如果向表a插入数据后，再向表b插入数据，LAST_INSERT_ID会改变。 |
方法二：是使用max(id) 使用last_insert_id是基于连接（connection）的，如果换一个窗口的时候调用则会一直返回0， 如果不是频繁的插入我们也可以使用这种方法来获取返回的id值
|
1 |
select max(id) from user; |
这个方法的缺点是不适合高并发。如果同时插入的时候返回的值可能不准确。 方法三：是创建一个存储过程，在存储过程中调用先插入再获取最大值的操作
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
DELIMITER $$ DROP PROCEDURE IF EXISTS `test` $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `test`(in name varchar(100),out oid int) BEGIN insert into user(loginname) values(name); select max(id) from user into oid; select oid; END $$ DELIMITER ; call test('gg',@id); |
方法四:使用@@identity
|
1 |
select @@IDENTITY |
@@identity是表示的是最近一次向具有identity属性(即自增列)的表插入数据时对应的自增列的值，是系统定 义的全局变量。一般系统定义的全局变量都是以@@开头，用户自定义变量以@开头。比如有个表A，它的自增列是id，当向A表插入一行数据后，如果插入数据 后自增列的值自动增加至101，则通过select @@identity得到的值就是101。使用@@identity的前提是在进行insert操作后，执行select @@identity的时候连接没有关闭，否则得到的将是NULL值。 方法五:是使用getGeneratedKeys()
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
Connection conn = ; Serializable ret = null; PreparedStatement state = .; ResultSet rs=null; try { state.executeUpdate(); rs = state.getGeneratedKeys(); if (rs.next()) { ret = (Serializable) rs.getObject(1); } } catch (SQLException e) { } return ret; |
from:https://blog.csdn.net/zhuchunyan_aijia/article/details/93620357
View Details前几天统计一个sql，是一个人提交了多少工单，顺便做了相关sql优化。数据大概2000多w。
|
1 2 3 |
select CustName,count(1) c from WorkOrder where CreateDate>'2016-5-1' and CreateDate<'2017-1-1' group by CustName having c>100 order by c desc; |
为了实验最少受其他因素干扰，将生产库的200多w数据导出来，用测试服务器进行测试。 导出来的数据是一个堆表，没有主键，没有索引。
|
1 2 3 4 5 |
mysql> show index from WorkOrder; 查询index方法1 Empty set (0.00 sec) mysql> show keys from WorkOrder; 查询index方法2 Empty set (0.00 sec) |
1.堆表的情况 这时候就在这时候，用执行计划分析下语句。
|
1 2 3 4 5 6 7 8 |
mysql> explain select CustName,count(1) c from WorkOrder where CreateDate>'2016-5-1' and CreateDate<'2017-1-1' group by CustName having c>100 order by c desc; +----+-------------+-----------+------+---------------+------+---------+------+---------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------+---------------+------+---------+------+---------+----------------------------------------------+ | 1 | SIMPLE | WorkOrder | ALL | NULL | NULL | NULL | NULL | 2528727 | Using where; Using temporary; Using filesort | +----+-------------+-----------+------+---------------+------+---------+------+---------+----------------------------------------------+ 1 row in set |
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建两个单列索引，另外两个是联合索引，只是最左边列不一样。
|
1 2 3 4 |
alter table WorkOrder add index ix_name(custname) alter table WorkOrder add index ix_date(CreateDate) alter table WorkOrder add index ix_namedate(custname,CreateDate) alter table WorkOrder add index ix_datename(CreateDate,custname) |
|
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> show keys from WorkOrder; +-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | WorkOrder | 1 | ix_name | 1 | CustName | A | 1264363 | NULL | NULL | YES | BTREE | | | | WorkOrder | 1 | ix_date | 1 | CreateDate | A | 2528727 | NULL | NULL | | BTREE | | | | WorkOrder | 1 | ix_namedate | 1 | CustName | A | 1264363 | NULL | NULL | YES | BTREE | | | | WorkOrder | 1 | ix_namedate | 2 | CreateDate | A | 2528727 | NULL | NULL | | BTREE | | | | WorkOrder | 1 | ix_datename | 1 | CreateDate | A | 2528727 | NULL | NULL | | BTREE | | | | WorkOrder | 1 | ix_datename | 2 | CustName | A | 2528727 | NULL | NULL | YES | BTREE | | | +-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 6 rows in set (0.00 sec) |
之后，用执行计划分析下sql查询语句。
|
1 2 3 4 5 6 7 8 |
mysql> explain select CustName,count(1) c from WorkOrder where CreateDate>'2016-5-1' and CreateDate<'2017-1-1' group by CustName having c>100 order by c desc; +----+-------------+-----------+-------+-----------------------------------------+-------------+---------+------+--------+-----------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+-------+-----------------------------------------+-------------+---------+------+--------+-----------------------------------------------------------+ | 1 | SIMPLE | WorkOrder | range | ix_name,ix_date,ix_namedate,ix_datename | ix_datename | 4 | NULL | 824372 | Using where; Using index; Using temporary; Using filesort | +----+-------------+-----------+-------+-----------------------------------------+-------------+---------+------+--------+-----------------------------------------------------------+ 1 row in set (0.01 sec) |
从执行计划可以看出，Mysql从四个索引中选取了ix_datename这个索引，type为range表示索引范围扫描。rows的数量值是没堆表的1/3。 执行语句三次，时间是 8.64 sec、8.61sec、8.55 sec。 我建了三个索引，那么我想用下另外三个索引怎么办？ 这里可以用force index()，这个指令可以指定本次查询强制使用哪个索引，因为Mysql优化器的选择并不是最优的索引。
|
1 2 3 4 5 6 |
mysql> explain select CustName,count(1) c from WorkOrder force index(ix_namedate) where CreateDate>'2016-5-1' and CreateDate<'2017-1-1' group by CustName having c>100 order by c desc; +----+-------------+-----------+-------+---------------------------------+-------------+---------+------+---------+-----------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+-------+---------------------------------+-------------+---------+------+---------+-----------------------------------------------------------+ | 1 | SIMPLE | WorkOrder | index | ix_name,ix_namedate,ix_datename | ix_namedate | 307 | NULL | 2528727 | Using where; Using index; Using temporary; Using filesort | +----+-------------+-----------+-------+---------------------------------+-------------+---------+------+---------+-----------------------------------------------------------+ |
选用另一个联合索引 ix_namedate，这次type变为index，可以这样理解，根据索引的顺序进行全表扫描，比ALL效率要高些，rows的值和堆表的值差不多。 执行语句三次，时间是 7.84 sec、7.92 sec、7.84 sec。
|
1 2 3 4 5 6 7 8 |
mysql> explain select CustName,count(1) c from WorkOrder force index(ix_name) where CreateDate>'2016-5-1' and CreateDate<'2017-1-1' group by CustName having c>100 order by c desc; +----+-------------+-----------+-------+---------------------------------+---------+---------+------+---------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+-------+---------------------------------+---------+---------+------+---------+----------------------------------------------+ | 1 | SIMPLE | WorkOrder | index | ix_name,ix_namedate,ix_datename | ix_name | 303 | NULL | 2528727 | Using where; Using temporary; Using filesort | +----+-------------+-----------+-------+---------------------------------+---------+---------+------+---------+----------------------------------------------+ 1 row in set |
选用另一个联合索引 ix_name，这次type是index，可以这样理解，根据索引的顺序进行全表扫描，比ALL效率要高些，rows的值和堆表的值差不多。 执行语句三次，时间是 1 min 28.17 sec、1 min 27.64 sec、1 min 27.58 sec。
|
1 2 3 4 5 6 7 |
mysql> explain select CustName,count(1) c from WorkOrder force index(ix_date) where CreateDate>'2016-5-1' and CreateDate<'2017-1-1' group by CustName having c>100 order by c desc; +----+-------------+-----------+-------+-----------------------------------------+---------+---------+------+--------+-------------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+-------+-----------------------------------------+---------+---------+------+--------+-------------------------------------------------------------------+ | 1 | SIMPLE | WorkOrder | range | ix_name,ix_date,ix_namedate,ix_datename | ix_date | 4 | NULL | 921062 | Using index condition; Using MRR; Using temporary; Using filesort | +----+-------------+-----------+-------+-----------------------------------------+---------+---------+------+--------+-------------------------------------------------------------------+ |
选用另一个联合索引 ix_date，这次type是range，表示索引范围扫描，rows的值是堆表的1/3多些 。 执行语句三次，时间是 9.55 sec、9.52 sec、9.39 sec。 假如我不想用索引了怎么办？ 可以使用ignore index()，这个指令可以强制Mysql在查询时，不使用某索引。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> explain select CustName,count(1) c from WorkOrder ignore index(ix_date) where CreateDate>'2016-5-1' and CreateDate<'2017-1-1' group by CustName having c>100 order by c desc; +----+-------------+-----------+-------+---------------------------------+-------------+---------+------+--------+-----------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+-------+---------------------------------+-------------+---------+------+--------+-----------------------------------------------------------+ | 1 | SIMPLE | WorkOrder | range | ix_name,ix_namedate,ix_datename | ix_datename | 4 | NULL | 824372 | Using where; Using index; Using temporary; Using filesort | +----+-------------+-----------+-------+---------------------------------+-------------+---------+------+--------+-----------------------------------------------------------+ mysql> explain select CustName,count(1) c from WorkOrder ignore index(ix_date,ix_name,ix_namedate,ix_datename) where CreateDate>'2016-5-1' and CreateDate<'2017-1-1' group by CustName having c>100 order by c desc; +----+-------------+-----------+------+---------------------------------+------+---------+------+---------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------+---------------------------------+------+---------+------+---------+----------------------------------------------+ | 1 | SIMPLE | WorkOrder | ALL | ix_name,ix_namedate,ix_datename | NULL | NULL | NULL | 2528727 | Using where; Using temporary; Using filesort | +----+-------------+-----------+------+---------------------------------+------+---------+------+---------+----------------------------------------------+ |
上面第一个强制不使用ix_date索引，那么就Mysql就从剩下的三个索引中，选取他认为是最优的索引。第二个时将四个索引都不使用，那么Mysql就进行全表扫描了。 总结： 1.Mysql的语句优化，没有绝对的正确，explain也只是给出个大致的方向，例如 key_len值小的，rows小的按理说，时间应该最短，效率最高。但是，实验中时间最少的却不是那个值最小的。 2. 优化还需根据实际数据情况，例如，假如我where选取的时间范围变化，或者说CustName的分布有些变化，可能跟刚才的实验，又会产生一定偏差。 3. 同样我还实验了，当给表加上主键时，整体的查询时间会缩短些。 […]
View Detailsspringboot pom.xml加入依赖
|
1 2 3 |
org.springframework.boot spring-boot-starter-data-mongodb 2.1.6.RELEASE |
application.yml加入连接信息 mapper编写
|
1 2 |
public interface BusinessLogRecordMapper extends MongoRepository<businesslogrecord,string> { } |
Application启动添加注解
|
1 |
@EnableMongoRepositories(basePackages = {"com.etc.mapper"}) |
application.yml配置，这里之前碰到一个坑，mongodb的配置没有写host和port属性，只写了database和uri，这种方式在无密码验证的情况下，可以连接mongodb。但是在mongodb设置了密码登录后，就无法连接，一直提示 Caused by: com.mongodb.MongoCommandException: Command failed with error 13: 'not authorized on wxsb_dev to execute command { insert 这里我们说下，application.yml关于mongodb的两种配置 第一种，yml方式，注意这里的host port username password database，每个属性都要配置。
|
1 2 3 4 5 6 7 8 |
spring: data: mongodb: host: 192.168.11.121 port: 27017 username: aroot password: 999999 database: udb |
第二种，uri方式
|
1 2 3 4 |
spring: data: mongodb: uri: mongodb://aroot:999999@192.168.11.121:27017/udb |
之前就是配了如下的参数，导致一直出错，还以为是mongodb的用户权限配置出错导致，原来是配置文件出错，报错信息 Caused by: com.mongodb.MongoCommandException: Command failed with error 13: 'not authorized on wxsb_dev to execute command { insert
|
1 2 3 4 5 6 7 |
spring: data: mongodb: database: udb uri: mongodb://192.168.11.111:27017 username: aroot password: 999999 |
from:https://www.it610.com/article/1283088113266081792.htm
View Details问题详细描述： 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[] […]
View Details被迫转到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 […]
View Details个人观点：etcd的红火来源于kurbernetes用etcd做服务发现，而redis的兴起则来源于memcache缓存本身的局限性。 etcd是一种分布式存储，更强调的是各个节点之间的通信，同步，确保各个节点上数据和事务的一致性， 使得服务发现工作更稳定，本身单节点的写入能力并不强。 redis更像是内存型缓存，虽然也有cluster做主从同步和读写分离， 但节点间的一致性主要强调的是数据，并不在乎事务，因此读写能力很强，qps甚至可以达到10万+ 两者都是k-v存储，但redis支持更多的存储模式，包括KEY，STRING，HMAP，SET，SORTEDSET等等， 因此redis本身就可以完成一些比如排序的简单逻辑。而etcd则支持对key的版本记录和txn操作和client对key的watch，因此适合用做服务发现。 日常使用中，etcd主要还是做一些事务管理类的，基础架构服务用的比较多，容器类的服务部署是其主流。 而redis广泛地使用在缓存服务器方面，用作mysql的缓存，通常依据请求量，甚至会做成多级缓存，当然部分情况下也用做存储型redis做持续化存储。 from:https://www.cnblogs.com/nmap/p/9398346.html
View Details一、索引基础 定义 索引，也叫做“键(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, […]
View Details要想知道每个数据库的大小的话，步骤如下： 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
View Details前言 其实数据库章节基本上的知识点我都写过一遍了，包括这篇事务和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 […]
View Details|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 |
DELIMITER // CREATE DEFINER=`root`@`localhost` FUNCTION `FN_IdCardNo15To18`( `v_card` VARCHAR(32) ) RETURNS varchar(32) CHARSET utf8mb4 NO SQL BEGIN DECLARE v_sum varchar(32) DEFAULT ''; -- 校验第一步求和 DECLARE v_mod varchar(32) DEFAULT ''; -- 校验第二步取余 DECLARE i_flag varchar(32) DEFAULT ''; -- 校验第三步计算校验位 DECLARE i_card varchar(32) DEFAULT ''; -- 身份证号 -- 去空格 SET v_card = REPLACE(v_card,' ',''); -- 为空则返回空 IF v_card='' THEN RETURN ''; END if; -- 非15位直接返回 IF LENGTH(v_card)!=15 THEN RETURN v_card; END IF; -- 补充成17位 SET v_card = CONCAT(SUBSTRING(v_card,1,6),'19',SUBSTRING(v_card,7,9)); -- 判断校验位 SET v_sum= (SUBSTRING(v_card,1,1)*7)+ (SUBSTRING(v_card,2,1)*9)+ (SUBSTRING(v_card,3,1)*10)+ (SUBSTRING(v_card,4,1)*5)+ (SUBSTRING(v_card,5,1)*8)+ (SUBSTRING(v_card,6,1)*4)+ (SUBSTRING(v_card,7,1)*2)+ (SUBSTRING(v_card,8,1)*1)+ (SUBSTRING(v_card,9,1)*6)+ (SUBSTRING(v_card,10,1)*3)+ (SUBSTRING(v_card,11,1)*7)+ (SUBSTRING(v_card,12,1)*9)+ (SUBSTRING(v_card,13,1)*10)+ (SUBSTRING(v_card,14,1)*5)+ (SUBSTRING(v_card,15,1)*8)+ (SUBSTRING(v_card,16,1)*4)+ (SUBSTRING(v_card,17,1)*2); -- 求余 SET v_mod = v_sum % 11; -- 补位 IF v_mod = 0 THEN SET i_flag = '1'; END IF; IF v_mod = 1 THEN SET i_flag = '0'; END IF; IF v_mod = 2 THEN SET i_flag = 'X'; END IF; IF v_mod = 3 THEN SET i_flag = '9'; END IF; IF v_mod = 4 THEN SET i_flag = '8'; END IF; IF v_mod = 5 THEN SET i_flag = '7'; END IF; IF v_mod = 6 THEN SET i_flag = '6'; END IF; IF v_mod = 7 THEN SET i_flag = '5'; END IF; IF v_mod = 8 THEN SET i_flag = '4'; END IF; IF v_mod = 9 THEN SET i_flag = '3'; END IF; IF v_mod = 10 THEN SET i_flag = '2'; END IF; SET i_card = CONCAT(v_card,i_flag); RETURN i_card; END// DELIMITER ; |
View Details