队列是常用的数据结构,基本特点就是先入先出,在事务处理等方面都要用到它,有的时候是带有优先级的队列。当队列存在并发访问的时候,比如多线程情况下,就需要锁机制来保证队列中的同一个元素不被多次获取
一个 MySQL 表可以看作是一个队列,每一行为一个元素。每次查询得到满足某个条件的最前面的一行,并将它从表中删除或者改变它的状态,使得下次查询不会得到它。在没有并发访问的情况下,简单地用 SELECT 得到一行,再用UPDATE(或者DELETE)语句修改之,就可以实现。代码如下:
SELECT * FROM targets WHERE status='C' LIMIT 1;
UPDATE targets SET status='D' WHERE id='id';
如果有并发访问,在SELECT和UPDATE语句之间可能会存在其他地SELECT查询,导致同一行被取出多次。为了保证在并发情况下仍然能正常工作,一种思路是使用数据库地锁来防止,就像在多线程环境下所做地一样。总之,要是的查询和修改为一个原子操作,不被其它的访问干扰。MySQL 5 支持存储过程,可以用它来实现。
单条 UPDATE 语句应该原子操作的,可以利用这个特性来保证并发访问情况下队列的正常工作。每次取元素时,先用 UPDATE 修改符合条件的第一行,然后再得到该行。可惜 UPDATE 语句没有返回值,重新用普通的SELECT的话又很难找到刚被改过的那条记录。
这里用到一个小技巧:在 UPDATE 时加上 id=LAST_INSERT_ID(id),再用 SELECT LAST_INSERT_ID() 即可得到刚修改的那条记录的id。还有一个问题,当表中不存在符合条件的记录,导致 UPDATE 失败时,LAST_INSERT_ID() 会保留原来地值不变,因而不能区分队列中是否还有元素。
ROW_COUNT() 返回上一个语句影响的行数,把它作为 SELECT 的一个条件,可以帮助解决这个问题。
最后,支持并发访问的完整解决方案为:
代码如下:
UPDATE targets SET status='D', id=LAST_INSERT_ID(id) WHERE status='C' LIMIT 1;
SELECT * FROM targets WHERE ROW_COUNT()>0 and id=LAST_INSERT_ID();
更新:在实现带优先级的队列时这种方法有问题,带有 ORDER BY ... 条件的 UPDATE 语句非常慢,例如:
代码如下: UPDATE targets SET status='D' WHERE status='C' ORDER BY schedule ASC LIMIT 1;
而单独查询和更新则是很快的:
代码如下:
SELECT id FROM targets WHERE status='C' ORDER BY schedule ASC LIMIT 1;
UPDATE targets SET status='D' WHERE id='id';
原来这是MySQL的Bug-12915,一年多以前提出来的,虽然关闭了,却只解决了部分问题,尚不支持WHERE,见MySQL 5.0.15 的 Changlog。无奈,上面这种巧妙的方法也没有实用价值了。
最后采用了一种折衷方案,如下:
代码如下:
UPDATE targets, (SELECT id FROM targets WHERE status='C' AND schedule

MySQL uses a GPL license. 1) The GPL license allows the free use, modification and distribution of MySQL, but the modified distribution must comply with GPL. 2) Commercial licenses can avoid public modifications and are suitable for commercial applications that require confidentiality.

The situations when choosing InnoDB instead of MyISAM include: 1) transaction support, 2) high concurrency environment, 3) high data consistency; conversely, the situation when choosing MyISAM includes: 1) mainly read operations, 2) no transaction support is required. InnoDB is suitable for applications that require high data consistency and transaction processing, such as e-commerce platforms, while MyISAM is suitable for read-intensive and transaction-free applications such as blog systems.

In MySQL, the function of foreign keys is to establish the relationship between tables and ensure the consistency and integrity of the data. Foreign keys maintain the effectiveness of data through reference integrity checks and cascading operations. Pay attention to performance optimization and avoid common errors when using them.

There are four main index types in MySQL: B-Tree index, hash index, full-text index and spatial index. 1.B-Tree index is suitable for range query, sorting and grouping, and is suitable for creation on the name column of the employees table. 2. Hash index is suitable for equivalent queries and is suitable for creation on the id column of the hash_table table of the MEMORY storage engine. 3. Full text index is used for text search, suitable for creation on the content column of the articles table. 4. Spatial index is used for geospatial query, suitable for creation on geom columns of locations table.

TocreateanindexinMySQL,usetheCREATEINDEXstatement.1)Forasinglecolumn,use"CREATEINDEXidx_lastnameONemployees(lastname);"2)Foracompositeindex,use"CREATEINDEXidx_nameONemployees(lastname,firstname);"3)Forauniqueindex,use"CREATEU

The main difference between MySQL and SQLite is the design concept and usage scenarios: 1. MySQL is suitable for large applications and enterprise-level solutions, supporting high performance and high concurrency; 2. SQLite is suitable for mobile applications and desktop software, lightweight and easy to embed.

Indexes in MySQL are an ordered structure of one or more columns in a database table, used to speed up data retrieval. 1) Indexes improve query speed by reducing the amount of scanned data. 2) B-Tree index uses a balanced tree structure, which is suitable for range query and sorting. 3) Use CREATEINDEX statements to create indexes, such as CREATEINDEXidx_customer_idONorders(customer_id). 4) Composite indexes can optimize multi-column queries, such as CREATEINDEXidx_customer_orderONorders(customer_id,order_date). 5) Use EXPLAIN to analyze query plans and avoid

Using transactions in MySQL ensures data consistency. 1) Start the transaction through STARTTRANSACTION, and then execute SQL operations and submit it with COMMIT or ROLLBACK. 2) Use SAVEPOINT to set a save point to allow partial rollback. 3) Performance optimization suggestions include shortening transaction time, avoiding large-scale queries and using isolation levels reasonably.


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

PhpStorm Mac version
The latest (2018.2.1) professional PHP integrated development tool

Atom editor mac version download
The most popular open source editor

WebStorm Mac version
Useful JavaScript development tools

SecLists
SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.

EditPlus Chinese cracked version
Small size, syntax highlighting, does not support code prompt function
