mysql的槽为什么不能是主键

mysql的槽为什么不能是主键

1. 不能当主键的原因:

MySQL写入数据时，会把数据存放到索引页中。使用UUID作为主键，新行的主键值不一定比之前的主键值大，所以innoDb无法做到总是把新行插入到索引的最后，而需要为新行寻找合适的位置来分配新的空间（因为是B+树方式存储的）。

要分配新的空间，就要知道应该分到哪个页。如果用自增主键等，直接顺序增加在后面。而UUID类，就需要排序后这儿插一个，哪儿插一个。不够的话，还要页分裂。而且，随机值载入到聚簇索引，有时候会需要做一次OPTIMEIZE TABLE来重建标并优化页的填充，这将又需要一定的时间消耗。

MYSQL 取随机数

2010年04月26日 星期一 09:48

mysql 取随机数

--对一个表取任意随机数

SELECT *

FROM TMP_XF_TEST

WHERE ID >= (SELECT FLOOR(RAND() * (SELECT MAX(ID) FROM TMP_XF_TEST)))

order by id LIMIT 1

--有条件性的取随机数

SELECT *

FROM TMP_XF_TEST

WHERE ID >= (SELECT FLOOR(RAND() *

((SELECT MAX(ID) FROM TMP_XF_TEST WHERE GID = 9) -

(SELECT MIN(ID) FROM TMP_XF_TEST WHERE GID = 9))) +

(SELECT MIN(ID) FROM TMP_XF_TEST WHERE GID = 9))

AND GID = 9

ORDER BY ID LIMIT 1

--gid上存在索引

或者

SELECT *

FROM TMP_XF_TEST AS t1 JOIN

(SELECT ROUND(RAND() * ((SELECT MAX(id) FROM TMP_XF_TEST WHERE GID = 9)-(SELECT MIN(id) FROM TMP_XF_TEST WHERE GID = 9))

+(SELECT MIN(id) FROM TMP_XF_TEST WHERE GID = 9)) AS id) AS t2

WHERE t1.id >= t2.id AND t1.GID = 9

ORDER BY t1.id LIMIT 1

#########

不要用下面的杯具写法

mysql>insert into tmp_xf_test(user_nick,gid,item_id,gmt_create,gmt_modified,memo)

->select user_nick,gid,item_id,gmt_create,gmt_modified,memo from tmp_xf_test

Query OK, 165888 rows affected (9.65 sec)

Records: 165888 Duplicates: 0 Warnings: 0

mysql>SELECT *

->FROM `tmp_xf_test`

->WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM `tmp_xf_test` )

->ORDER BY id LIMIT 1

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

| id | user_nick | gid | item_id | gmt_create | gmt_modified| memo |

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

| 467 | 玄风 | 9 | 123 | 2010-04-26 14:56:39 | 2010-04-26 14:56:39 | 玄风测试使用的数据 |

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

1 row in set (51.12 sec)

mysql>explain SELECT *

->FROM `tmp_xf_test`

->WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM `tmp_xf_test` )

->ORDER BY id LIMIT 1\G

*************************** 1. row ***************************

id: 1

select_type: PRIMARY

table: tmp_xf_test

type: index

possible_keys: NULL

key: PRIMARY

key_len: 8

ref: NULL

rows: 1

Extra: Using where

*************************** 2. row ***************************

id: 2

select_type: UNCACHEABLE SUBQUERY

table: tmp_xf_test

type: index

possible_keys: NULL

key: idx_tmp_xf_test_gid

key_len: 4

ref: NULL

rows: 331954

Extra: Using index

2 rows in set (0.01 sec)

---

mysql>SELECT * FROM `tmp_xf_test` t1 join

->(SELECT FLOOR( MAX(id) * RAND()) as id FROM `tmp_xf_test` ) as t2

->where t1.id >=t2.id

->ORDER BY t1.id LIMIT 1

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

| id| user_nick | gid | item_id | gmt_create | gmt_modified| memo | id|

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

| 40311 | 玄风 | 9 | 123 | 2010-04-28 15:47:19 | 2010-04-28 15:47:19 | 玄风测试使用的数据 | 40311 |

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

1 row in set (0.14 sec)

##############

mysql>SELECT * FROM `tmp_xf_test`

->WHERE id >= (SELECT floor(RAND() * (SELECT MAX(id) FROM `tmp_xf_test`)))

->ORDER BY id LIMIT 1

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

| id | user_nick | gid | item_id | gmt_create | gmt_modified| memo |

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

| 1352 | 玄风 | 9 | 123 | 2010-04-28 15:47:19 | 2010-04-28 15:47:19 | 玄风测试使用的数据 |

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

1 row in set (0.00 sec)

mysql>explain SELECT * FROM `tmp_xf_test`

->WHERE id >= (SELECT floor(RAND() * (SELECT MAX(id) FROM `tmp_xf_test`)))

->ORDER BY id LIMIT 1\G

*************************** 1. row ***************************

id: 1

select_type: PRIMARY

table: tmp_xf_test

type: index

possible_keys: NULL

key: PRIMARY

key_len: 8

ref: NULL

rows: 1

Extra: Using where

*************************** 2. row ***************************

id: 3

select_type: SUBQUERY

table: NULL

type: NULL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: NULL

Extra: Select tables optimized away

2 rows in set, 1 warning (0.00 sec)

对应的另外一种杯具写法是：

SELECT *

FROM TMP_XF_TEST

WHERE ID >= (SELECT FLOOR(RAND() * (MAX(ID) - MIN(ID))) + MIN(ID) MID

FROM TMP_XF_TEST

WHERE GID = 9)

AND GID = 9 LIMIT 1

1.随机读取连续多条记录。经过实践，可以随机读取多条连续的数据记录，里头取值的一般都是主键ID来进行最大值、最小值的读取：

SELECT * FROM example_table  AS t1  JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM example_table)-(SELECT MIN(id) FROM example_table))+(SELECT MIN(id) FROM example_table)) AS id) AS t2 WHERE t1.id >= t2.id ORDER BY t1.id LIMIT 5

2.随机多条数据。以下两种都是随机读取数据，查询数据速度、随机范围都基本相差不大

SELECT * FROM example_table WHERE id >= ((SELECT MAX(id) FROM example_table)-(SELECT MIN(id) FROM example_table)) * RAND() + (SELECT MIN(id) FROM example_table) LIMIT 5 SELECT * FROM example_table WHERE id>=(SELECT floor(RAND() * ((SELECT MAX(id) FROM example_table)-(SELECT MIN(id) FROM example_table)) + (SELECT MIN(id) FROM example_table))) ORDER BY id LIMIT 5

---