MySQL > alter table auto_increment increment MySQL > alter table auto_increment MySQL > alter table auto_increment MySQL > alter table auto_increment MySQL > alter table auto_increment MySQL > alter table auto_increment MySQL > alter table auto_increment However, UUID is usually not a good choice for MySQL (especially InnoDB) because clustered indexes require physical data to be sorted by primary key, and UUID is inherently unordered, thus incurs a lot of unnecessary IO consumption. This leads us to the conclusion that ID had better be a unique value of the order.

MySQL > alter database auto_increment increment MySQL > alter database auto_increment increment The problem is that this does not satisfy high availability. Although you can increase availability by setting different auto_INCREMENT steps across multiple servers, the database itself is always the shortest plank. As for solutions, there are already plenty of similar discussions online:

  • Discuss distributed ID generation methods in detail
  • What ID generator does a business system need
  • A glance at how distributed Unique ids are generated

The most popular solution, of course, is Twitter’s snowflake, which roughly means: In order to avoid a single point of failure, run on more than one node ID generator service, each node has its own independent identity, ID is prefix with time factor, although there may be differences between the different server time, cannot guarantee the order of the absolute, but the overall trend or sequence can be thought of as, IO burden can be ignored, at the same time with a counter as suffix, So it’s unique.

Existing open source ID generators, such as Chronos, run as a service, but that was a bit too heavy for me, so I implemented a simple, unserviceable ID generator in PHP. It’s simple, but not crude, and does what Snowflake requires:

max(self::NODE_BITS);

        if (is_int($node) === false || $node > $max || $node < 0) {
            throw new \InvalidArgumentException('node');
        }

        $this->node = $node;
    }

    public function generate($time = null)
    {
        if ($time === null) {
            $time = time();
        }

        return ($this->time($time) << (self::NODE_BITS + self::COUNT_BITS)) |
               ($this->node << self::COUNT_BITS) |
               ($this->count($time));
    }

    public function restore($id)
    {
        $binary = decbin($id);

        $position = -(self::NODE_BITS + self::COUNT_BITS);

        return array(
            'time'  => bindec(substr($binary, 0, $position)) + self::EPOCH,
            'node'  => bindec(substr($binary, $position, - self::COUNT_BITS)),
            'count' => bindec(substr($binary, - self::COUNT_BITS)),
        );
    }

    private function time($time)
    {
        $key = 'seq:time';

        if (apcu_fetch($key) === false && sleep(1) === 0) {
            apcu_add($key, $time);
        }

        $time -= self::EPOCH;

        $max = $this->max(self::TIME_BITS);

        if (is_int($time) === false || $time > $max || $time < 0) {
            throw new \InvalidArgumentException('time');
        }

        return $time;
    }

    private function count($time)
    {
        $key = "seq:count:{$time}";

        while (!$count = apcu_inc($key)) {
            apcu_add($key, mt_rand(0, 9));
        }

        $max = $this->max(self::COUNT_BITS);

        if ($count > $max) {
            throw new \UnexpectedValueException('count');
        }

        return $count;
    }

    private function max($bits)
    {
        return -1 ^ (-1 << $bits);
    }
}

?>
Copy the code

The implementation in this article uses APCU to store data, but it does not need to exist as a service. Among them, we have customized a time origin, so that the number of time can be saved. In actual use, the time stamp of project approval can be used as the time origin, which is more meaningful. Taking the 30-bit time as an example, if the time origin is 1000000000, the theoretical maximum can be saved to 2035-09-18. In addition, we reserve 10 bits for nodes and 20 bits for counters, which can theoretically hold up to 1023 nodes. Each node has a maximum of 1048,575 ids per second. These thresholds are generally sufficient, and the system will probably fail before the upper limit is reached.

It should be noted that if you use the second level of time, if you restart PHP-FPM within a second, it is possible to generate non-unique values, so I added sleep(1) logic to the code to avoid this problem. In addition, because the code does not rigorously judge the server’s possible time rollback, it is possible to generate non-unique values, but only if several conditions are met: first, the server’s time rollback occurred; Second, the ID generated by the fallback happens to have been used before. Finally, the server clears the relevant cache for LRU and other reasons. It’s basically very difficult to satisfy these conditions. That said, the code in this article can be considered robust enough for most PHP projects.

In addition, it is better not to use the generated ID directly, otherwise someone can reverse solve the data, such as how many servers you have, etc. The solution is to use hashids to encode and decode the application layer, so that the database still holds the original ID (Bigint), but the user sees the HASH ID. Thus, data security is better protected.