mysql字段uuid_Mysql 用UUID做主键可行么？一直想尝试，但没条件测试。

做主键通常会使用uuid或自增，这里从逻辑读方面对比一下两者的区别：

这也算是https://www.zhihu.com/question/43500172/answer/113356943?from=profile_answer_card 这个问题的进一步证实。

mysql> use test;

Database changed

mysql> create table song_auto_inc(id int primary key auto_increment,name varchar(10),score int);

Query OK, 0 rows affected (0.01 sec)

mysql> create table song_uuid(id varchar(36) primary key ,name varchar(10),score int);

Query OK, 0 rows affected (0.01 sec)

cat insert_auto.sh

#!/bin/bash

for i in {1..78913} ;do

name="name$i"

mysql -h10.13.53.196 -P3306 -uroot -ptest -e "insert into test.song_auto_inc (name,score) select '$name','$i' ;"

done

cat insert_uuid.sh

#!/bin/bash

for i in {1..78913} ;do

name="name$i"

mysql -h10.13.53.196 -P3306 -uroot -ptest -e "insert into test.song_uuid select uuid(),'$name','$i' ;"

done

--表记录数一样

mysql> select count(*) from song_auto_inc;

+----------+

| count(*) |

+----------+

| 78913 |

+----------+

1 row in set (0.01 sec)

mysql> select count(*) from song_uuid;

+----------+

| count(*) |

+----------+

| 78913 |

+----------+

1 row in set (0.01 sec)

--整理表碎片

mysql> optimize table song_auto_inc;

+--------------------+----------+----------+-------------------------------------------------------------------+

| Table | Op | Msg_type | Msg_text |

+--------------------+----------+----------+-------------------------------------------------------------------+

| test.song_auto_inc | optimize | note | Table does not support optimize, doing recreate + analyze instead |

| test.song_auto_inc | optimize | status | OK |

+--------------------+----------+----------+-------------------------------------------------------------------+

2 rows in set (1.59 sec)

mysql> optimize table song_uuid;

+----------------+----------+----------+-------------------------------------------------------------------+

| Table | Op | Msg_type | Msg_text |

+----------------+----------+----------+-------------------------------------------------------------------+

| test.song_uuid | optimize | note | Table does not support optimize, doing recreate + analyze instead |

| test.song_uuid | optimize | status | OK |

+----------------+----------+----------+-------------------------------------------------------------------+

2 rows in set (3.01 sec) --这个用的时间也比上面的自增用的时间长

场景1 ：全表扫描的逻辑读

mysql> show status like 'innodb_buffer_pool_read_re%';

+----------------------------------+---------+

| Variable_name | Value |

+----------------------------------+---------+

| Innodb_buffer_pool_read_requests | 6511688 |

+----------------------------------+---------+

1 row in set (0.00 sec)

mysql> select * from song_auto_inc where name='name1000';

+------+----------+-------+

| id | name | score |

+------+----------+-------+

| 1000 | name1000 | 1000 |

+------+----------+-------+

1 row in set (0.02 sec)

mysql> show status like 'innodb_buffer_pool_read_re%';

+----------------------------------+---------+

| Variable_name | Value |

+----------------------------------+---------+

| Innodb_buffer_pool_read_requests | 6521753 |

+----------------------------------+---------+

1 row in set (0.00 sec)

mysql> select * from song_uuid where name='name1000';

+--------------------------------------+----------+-------+

| id | name | score |

+--------------------------------------+----------+-------+

| d2e2213c-5400-11e6-abd2-0cc47a56f95e | name1000 | 1000 |

+--------------------------------------+----------+-------+

1 row in set (0.02 sec)

mysql> show status like 'innodb_buffer_pool_read_re%';

+----------------------------------+---------+

| Variable_name | Value |

+----------------------------------+---------+

| Innodb_buffer_pool_read_requests | 6531990 |

+----------------------------------+---------+

1 row in set (0.00 sec)

自增表全表扫读一条记录逻辑读 6521753-6511688=10065

uuid表全表扫读一条记录逻辑读 6531990-6521753=10237

场景2： 索引读一条记录的逻辑读：

--建立索引

mysql> create index idx_song_uuid_name on song_uuid(name);

Query OK, 0 rows affected (3.50 sec) ---uuid的用时较长

Records: 0 Duplicates: 0 Warnings: 0

mysql> create index idx_song_auto_inc_name on song_auto_inc(name);

Query OK, 0 rows affected (1.30 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> show status like 'innodb_buffer_pool_read_re%';

+----------------------------------+---------+

| Variable_name | Value |

+----------------------------------+---------+

| Innodb_buffer_pool_read_requests | 6856001 |

+----------------------------------+---------+

1 row in set (0.00 sec)

mysql> select * from song_auto_inc where name='name1000';

+------+----------+-------+

| id | name | score |

+------+----------+-------+

| 1000 | name1000 | 1000 |

+------+----------+-------+

1 row in set (0.00 sec)

mysql> show status like 'innodb_buffer_pool_read_re%';

+----------------------------------+---------+

| Variable_name | Value |

+----------------------------------+---------+

| Innodb_buffer_pool_read_requests | 6856012 |

+----------------------------------+---------+

1 row in set (0.01 sec)

mysql> select * from song_uuid where name='name1000';

+--------------------------------------+----------+-------+

| id | name | score |

+--------------------------------------+----------+-------+

| d2e2213c-5400-11e6-abd2-0cc47a56f95e | name1000 | 1000 |

+--------------------------------------+----------+-------+

1 row in set (0.00 sec)

mysql> show status like 'innodb_buffer_pool_read_re%';

+----------------------------------+---------+

| Variable_name | Value |

+----------------------------------+---------+

| Innodb_buffer_pool_read_requests | 6856023 |

+----------------------------------+---------+

1 row in set (0.00 sec)

自增表索引读一条记录逻辑读 6856012-6856001=11

uuid表索引读一条记录逻辑读 6856023-6856012=11

场景3：索引读多条记录的逻辑读：

mysql> show status like 'innodb_buffer_pool_read_re%';

+----------------------------------+---------+

| Variable_name | Value |

+----------------------------------+---------+

| Innodb_buffer_pool_read_requests | 6889962 |

+----------------------------------+---------+

1 row in set (0.00 sec)

mysql> select * from song_auto_inc where name

+-------+-----------+-------+

| id | name | score |

+-------+-----------+-------+

| 1 | name1 | 1 |

| 10 | name10 | 10 |

| 100 | name100 | 100 |

| 1000 | name1000 | 1000 |

+-------+-----------+-------+

4 rows in set (0.00 sec)

mysql> show status like 'innodb_buffer_pool_read_re%';

+----------------------------------+---------+

| Variable_name | Value |

+----------------------------------+---------+

| Innodb_buffer_pool_read_requests | 6890003 |

+----------------------------------+---------+

1 row in set (0.00 sec)

mysql> select * from song_uuid where name

+--------------------------------------+----------+-------+

| id | name | score |

+--------------------------------------+----------+-------+

| d05052ae-5400-11e6-abd2-0cc47a56f95e | name1 | 1 |

| d055fde2-5400-11e6-abd2-0cc47a56f95e | name10 | 10 |

| d0911d6b-5400-11e6-abd2-0cc47a56f95e | name100 | 100 |

| d2e2213c-5400-11e6-abd2-0cc47a56f95e | name1000 | 1000 |

+--------------------------------------+----------+-------+

4 rows in set (0.00 sec)

mysql> show status like 'innodb_buffer_pool_read_re%';

+----------------------------------+---------+

| Variable_name | Value |

+----------------------------------+---------+

| Innodb_buffer_pool_read_requests | 6890056 |

+----------------------------------+---------+

1 row in set (0.00 sec)

自增表索引读多条记录逻辑读6890003-6889962=41

uuid表索引读多条记录逻辑读6890056-6890003=53

上面的测试是在ucloud(UCloud – 专业云计算服务商)的mysql5.6的普通udb上完成的，如果对比插入时的性能不满意，可以使用ssd的udb，ssd的性能非常高。在网上看到https://linux.cn/article-7596-1.html这个文章，里面用ucloud ssd mysql和别的云服务商进行对比，可以参考下。

QQ 273002188 欢迎一起学习

QQ 群 236941212

oracle,mysql,PG 相互交流