文字列クエリの効率を向上させるMySQLcrc32およびcrc64関数

Mysql Crc32 Crc64 Function Improve String Query Efficiency

MySQL crc32 & crc64 function Improve string query efficiency The CRC is called the Cyclic Redundancy Check, which is also called cyclic redundancy check. CRC32 is a type of CRC algorithm that is commonly used to verify files transmitted over a network. MySQL defaults to crc32, while crc64 requires: MySQL 5.1, 5.5 and 5.6, Oracle distribution, Percona Server and MariaDB. The basic characteristics of CRC32: #1. The return value of the CRC32 function is 0-4294967296 (2 to the 32th power minus 1) #2. Compared to MD5, CRC32 function is easy to collide Scenario: We are doing a crawler. For a URL, first go to the database to find out if it exists. If it does not exist, insert it into the database. Everyone knows that this type of application table will grow very quickly, if simple mysql> SELECT * FROM urls WHERE url = 'http://wwww.shopperplus.com' Every time a full table scan is performed, the efficiency is very low. If you add an index to the url column, it will be faster, but since the url is a varchar type, the storage space of the field itself and the storage space occupied by the index are relatively large. At this point, if you add a crc32_url column, and only add an index on this column, the index space will be much smaller, and use integer to speed up the query speed to avoid string url full table scan. SELECT * FROM urls WHERE crc_url = 907060870 This is definitely not the case, because it has been mentioned that the crc32 function will collide, that is to say, the value of 907060870 is more than hello. One trick is to filter only with crc32 columns: SELECT * FROM urls WHERE crc_url = 907060870 AND url = 'hello' Crc32 shortcomings are prone to collisions, is there a better solution? The answer is yes -> crc64 Crc64() This function complements MySQL's crc32() function, which results in uneven distribution over a large number of values. The crc64() algorithm relies on MD5 as the underlying mechanism. From https://www.jianshu.com/p/af6cc7b72dac