As in a scale of 0-100 to take three random number, don't repeat (taking a cannot use random and then randomly pick up a second judge whether repeat, repeat, again take this lantern ring method)

CodePudding user response:

Now my idea is to create a temporary table 100 data, then the order by the rand () limit 3, think too complicated

CodePudding user response:

 SELECT CEIL (RAND () * (100)) from temp_table order by RAND () limit 3; 

CodePudding user response:

The order by the rand () take a random message principle
1: create a temporary table with the memory engine, there are two fields are respectively R type double fields and fields can be removed, and no index
2: with the rand () for each line to generate a random number 0 to 1, in the double field R
3: according to the R field sort: initialize sort_buffer, remove memory temporary table line R field and location information (each engine is used to uniquely identifies the data information, this rowid for a table with a primary key is a primary key id, there is no primary key is generated by the system a rowid, for the memory engine rowid is an array subscript) into sort_buffer, sort by R fields,
4: sorting is completed, remove the limit 10 rows returned to the client,
Both tmp_table_size limit the size of the memory is the temporary table, the default value is 16 m, if the temporary table size is more than the value of memory is the temporary table will be converted to disk temporary table,

Mysql 5.6 introduced a new sorting algorithm, namely the priority queue scheduling algorithm, the algorithm of the temporary file is merge sort algorithm.

Read_buffer_size and read_rnd_buffer_size: both are according to the session the exclusive, increase read_rnd_buffer_size and max_length_for_sort_data can improve the order by SQL performance,
Max_length_for_sort_data size determined what kind of (two) sorting algorithm, more than this value will use disk sort the temporary file
Max_sort_length variables can be specified when using blob or text prefix sorting, how much this prefix
Once Sort_buffer_size default is global, sorting mysql will be immediately assigned memory whether need such a big size, can query before the increase, as shown in the following
The set @ @ session. Sort_buffer_size:=409600
# statement execution
The Set @ @ session. Sort_buffer_size:=default

MySQL supports two ways of sorting filesort and index, Using the index refers to the MySQL scanning indexes sorted itself, the index of high efficiency and low efficiency of filesort,
(2) the order by meet two situations will use Using index:
A. the order by statements use the index the left front,
B. use the where clause and the order by clause condition combinations are meet the index of the left front,
(3) as far as possible on the indexed column sorted, follow the index set up, the order of the index creation principle of the best left prefix,
(4) if the order by the condition of not on the indexed column, can produce Using filesort,
(5) group by with the order by very similar, the essence of which is group after sorting first, follow the principle of best left prefix index creation order, pay attention to the where higher than that of having, can write in the where qualification don't limited by having,

There are two kinds of optimization using FileSort sorting algorithm (from a simple mysql) :
Two scanning algorithm: first remove the sort field and row pointer information according to the conditions, after sorting in sourt_buffer, if not enough sort_buffer, ranking results in the temporary table stored in the temporary table, after finish sorting, according to the row pointer back to the table to read the record, the algorithm is mysql4.1 before using the algorithm, and need to access the data twice, first take order fields and row pointer (display primary keys or automatically create implicit primary key), the second time according to the row pointer back to the table for record (may lead to a large number of random I/O operations), sort memory overhead is small when
A scan algorithm: one-time out line meet the conditions of all of the fields, and then in sourt_buffer sorting, true to output the result set, sorting time memory lock, but the efficiency is higher than two sorting algorithm,
Max_length_sort_data mysql by comparing the system size and the total size of the query statement to take out the field to determine which algorithm, if this value is bigger, use a scan sorting algorithms, otherwise sorting algorithm using two scan,
Sort buffer is customer service oriented thread allocation, if set too big may cause waste of memory, and even cause memory exchange,

CodePudding user response:

reference 1st floor jslqa67 response:

now my idea is to create a temporary table 100 data, then the order by the rand () limit 3, feel too trival

Don't need to set up a temporary table, direct use of an existing table (select count (*) & gt; You have to do is=3)

 SELECT CEIL (RAND () * (100)) from temp_table order by RAND () limit 3;