精华内容
下载资源
问答
  • Mysql的排序问题内存临时表Mysql是什么办法来定位“一行数据”的Mysql5.6引入的新的排序算法(数据大小超过sort_buffer_size)...这个语句的意思很直白,随机排序取前三个,虽然这个SQL语句写法很简单,但执行流程很

    问题

    如果现在有一个随机显示三个单词的需求,有什么方法实现,存在什么问题以及如何改进?
    

    内存临时表

    首先,用order by rand()来实现这个逻辑:

    select word from words order by rand() limit 3;
    

    这个语句的意思很直白,随机排序取前三个,虽然这个SQL语句写法很简单,但执行流程很难

    mysql> explain select * from user order by rand() limit 3;
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
    | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
    |  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   11 |   100.00 | Using temporary; Using filesort |
    +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
    1 row in set, 1 warning (0.00 sec
    

    Extra字段显示Using temporary,表示需要临时表,Using fileSort表示的是需要执行排序操作

    • 因此这个Extra的意思就是,需要临时表,并且需要在临时表上排序。
      这就有一个问题:

    这里到底使用的是什么排序算法呢?

    对于不同引擎的表来说不同,对于InnoDB表来说,优先选择全字段排序,可以减少磁盘访问,对于MyAsim表来说,回表过程只是简单的根据数据行的位置直接访问内存得到数据,不会多访问磁盘,这时候便会使用rowid排序

    Mysql是用什么办法来定位“一行数据”的

    如果你创建的表没有主键,或者把一个表的主键删掉了,那么InnoDB会自己生成一个6字节的rowid来作为主键

    这也就是排序模式中,rowid名字的来历,实际上它表示的是:每个引擎用来唯一标识数据行的信息

    • 对于有主键的InnoDB表来说,这个rowId就是主键ID

    • 对于没有主键的InnoDB表来说,这个rowID就是由系统生成的

    • Memory引擎不是索引组织表,在这个例子里面,你可以认为它就是一个数组,因此,这个rowID就是数组的下标

    那么是不是所有的临时表都是内存表呢?

    1. 不是,参数tmp_table_size这个配置限制了内存临时表的大小,默认值是16M,如果临时表大小超过了tmp_table_size,那么内存临时表就会转成磁盘临时表
    2. 磁盘临时表默认的引擎是INNoDB,是由参数internal_tmp_disk_storage_engine来控制的
    3. 当使用磁盘临时表时,对应的就是一个没有显示索引的InnoDB表的排序过程

    Mysql5.6引入的新的排序算法(数据大小超过sort_buffer_size)

    优先队列算法
    

    我们现在的SQl语句只需要取R值最小的三个rowid,但是,如果使用归并排序算法的话,虽然最终也能得到三个值,但是这个算法结束后,已经将10000行数据都排好序了

    这就有点儿浪费了!!!

    优先队列算法,就可以精确地之得到三个最小的值,执行流程如下(就是最大堆排序):

    1. 对于这10000个准备排序的(R,rowid),先取前三行,构造成一个堆
    2. 取下一个行(R1,rowid1),跟当前堆里面的最大的R比较,如果R1小于R,把这个(R,rowid)从堆中去掉,换成(R1,rowid1)
    3. 重复第二步,直到第10000个(R10000,rowid10000)完成比较

    在OPTIMIZER_TRACE结果中:

    filesort_priority_queue_optimization这个部分的chosen=true,就表示使用了优先队列排序算法,这个过程不需要临时文件,因此对应的number_of_tmp_files是0

    这个过程结束后,我们构建的堆里就是需要的三个最小的元素

    如何正确的随机呢?

    1、 取得整个表的行数,记为C
    2、 取得Y = floor(C * rand)),floor函数在这里的作用,就是取整数部分
    3.、再用limit Y,1 取得一行

    select count(*) into @C from t;
    set @Y = floor(@C * rand());
    set @sql = concat('select * from limit ',@Y,',1');
    prepare stmt from @sql;
    execute stmt;
    deallocate prepare stmt;
    
    展开全文
  • 如何显示随机信息 mysql> select word from words order by rand() limit 3; 该方法比较简单,但是需要使用临时表,同时需要执行排序操作。 MySQL 的表是什么方法来定位“一行数据”的 如果把一个 InnoDB 表的...

    《MySQL实战45讲》第16讲

    sort_buffer

    MySQL会为每个线程分配一个内存(sort_buffer)用于排序该内存大小为sort_buffer_size。

    1. 如果排序的数据量小于sort_buffer_size,排序将会在内存中完成
    2. 如果排序数据量很大,内存中无法存下这么多数据,则会使用磁盘临时文件来辅助排序,也称外部排序
    3. 在使用外部排序时,MySQL会分成好几份单独的临时文件用来存放排序后的数据,然后再将这些文件合并成一个大文件
    全字段排序
    1. 通过索引将所需的字段全部读取到sort_buffer中
    2. 按照排序字段进行排序
    3. 将结果集返回给客户端

    缺点

    1. 造成sort_buffer中存放不下很多数据,因为除了排序字段还存放其他字段,对sort_buffer的利用效率不高
    2. 当所需排序数据量很大时,会有很多的临时文件,排序性能也会很差

    优点
    MySQL认为内存足够大时会优先选择全字段排序,因为这种方式比rowid 排序避免了一次回表操作

    rowid排序
    1. 通过控制排序的行数据的长度来让sort_buffer中尽可能多的存放数据,max_length_for_sort_data
    2. 只将需要排序的字段和主键读取到sort_buffer中,并按照排序字段进行排序
    3. 按照排序后的顺序,取id进行回表取出想要获取的数据
    4. 将结果集返回给客户端

    优点
    更好的利用内存的sort_buffer进行排序操作,尽量减少对磁盘的访问

    缺点
    回表的操作是随机IO,会造成大量的随机读,不一定就比全字段排序减少对磁盘的访问

    《MySQL实战45讲》第17讲

    如何显示随机信息
    mysql> select word from words order by rand() limit 3;
    

    该方法比较简单,但是需要使用临时表,同时需要执行排序操作。

    MySQL 的表是用什么方法来定位“一行数据”的

    如果把一个 InnoDB 表的主键删掉,是不是就没有主键,就没办法回表了?

    其实不是的。如果你创建的表没有主键,或者把一个表的主键删掉了,那么 InnoDB 会自己生成一个长度为 6 字节的 rowid 来作为主键。

    这也就是排序模式里面,rowid 名字的来历。实际上它表示的是:每个引擎用来唯一标识数据行的信息。

    • 对于有主键的 InnoDB 表来说,这个 rowid 就是主键 ID;
    • 对于没有主键的 InnoDB 表来说,这个 rowid 就是由系统生成的;
    • MEMORY 引擎不是索引组织表。在这个例子里面,你可以认为它就是一个数组。因此,这个 rowid 其实就是数组的下标。

    其实,order by rand() 使用了内存临时表,内存临时表排序的时候使用了 rowid 排序方法。

    磁盘临时表

    是不是所有的临时表都是内存表呢?其实不是的。tmp_table_size 这个配置限制了内存临时表的大小,默认值是 16M。如果临时表大小超过了 tmp_table_size,那么内存临时表就会转成磁盘临时表。

    磁盘临时表使用的引擎默认是 InnoDB,当使用磁盘临时表的时候,对应的就是一个没有显式索引的 InnoDB 表的排序过程。

    优先队列排序算法

    与归并排序算法不同,该算法没有用到临时文件。归并排序算法会将所有列排好序,但是如果我们的业务只需要取出固定数量的最大(小)值,就不必使用这么大的计算量。优先队列排序算法会维护一个最大(小)堆,堆的元素数量为我们需要的数据数量。

    随机排序方法

    不论是使用哪种类型的临时表,order by rand()这种写法都会让计算过程非常复杂,需要大量的扫描行数,因此排序过程的资源消耗也会很大。

    下面我们提供一种思路,假设要从一个表随机取3个数据,可以采用下面的算法

    1. 取得整个表的行数,记为 C;
    2. 根据随机方法得到 Y1、Y2、Y3;
    3. 再执行三个 limit Y, 1 语句得到三行数据。
    mysql> select count(*) into @C from t;
    set @Y1 = floor(@C * rand());
    set @Y2 = floor(@C * rand());
    set @Y3 = floor(@C * rand());
    select * from t limit @Y11// 在应用代码里面取 Y1、Y2、Y3 值,拼出 SQL 后执行
    select * from t limit @Y21select * from t limit @Y31

    《MySQL实战45讲》第18讲

    条件字段函数操作

    如果对字段做了函数计算,就用不上索引了,这是 MySQL 的规定.(对索引字段做函数操作,可能会破坏索引值的有序性,因此优化器就决定放弃走树搜索功能。)

    隐式类型转换

    MySQL 里的转换规则:在 MySQL 中,字符串和数字做比较的话,是将字符串转换成数字。

    例如,假设tradeid的字段类型是 varchar(32),而输入的参数却是整型,所以需要做类型转换。

    mysql> select * from tradelog where tradeid=110717;
    

    对于优化器来说,这个语句相当于:

    mysql> select * from tradelog where  CAST(tradid AS signed int) = 110717;
    

    这条语句触发了我们上面说到的规则:对索引字段做函数操作,优化器会放弃走树搜索功能。

    隐式字符编码转换

    两个表的字符集不同,做表连接查询的时候可能用不上关联字段的索引。

    当这两个类型的字符串在做比较的时候,MySQL 内部的操作是,先将一个字符集转化为另一个字符集然后再进行比较,实际上还是要用到字符集转换函数。

    实际上,字符集不同只是条件之一,连接过程中要求在被驱动表的索引字段上加函数操作,是直接导致对被驱动表做全表扫描的原因。(“按数据长度增加的方向”进行转换)

    《MySQL实战45讲》第19讲

    为什么我只查一行的语句,也执行这么慢?

    如果 MySQL 数据库本身就有很大的压力,导致数据库服务器 CPU 占用率很高或 ioutil(IO 利用率)很高,这种情况下所有语句的执行都有可能变慢,不属于我们今天的讨论范围。

    表级锁

    原因一 等 MDL 锁:现在有一个线程正在表 t 上请求或者持有 MDL 写锁,把 select 语句堵住了。解决方法:就是找到谁持有 MDL 写锁,然后把它 kill 掉。

    原因二 等 flush:有一个 flush tables 命令被别的语句堵住了,然后它又堵住了我们的 select 语句。
    在这里插入图片描述

    行锁

    由于访问 id=1 这个记录时要加读锁,如果这时候已经有一个事务在这行记录上持有一个写锁,我们的 select 语句就会被堵住。

    在这里插入图片描述

    索引

    由于没有索引,只能走全局扫描

    回滚

    在这里插入图片描述
    session B 执行完 100 万次 update 语句后,id=1 这一行处于什么状态呢?

    在这里插入图片描述

    session B 更新完 100 万次,生成了 100 万个回滚日志 (undo log)。

    带 lock in share mode 的 SQL 语句,是当前读,因此会直接读到 1000001 这个结果,所以速度很快;而 select * from t where id=1 这个语句,是一致性读,因此需要从 1000001 开始,依次执行 undo log,执行了 100 万次以后,才将 1 这个结果返回。

    《MySQL实战45讲》第20讲

    幻读

    幻读指的是一个事务在前后两次查询同一个范围的时候,后一次查询看到了前一次查询没有看到的行。

    间隙锁

    为了解决幻读问题,InnoDB 只好引入新的锁,也就是间隙锁。间隙锁,锁的就是两个值之间的空隙。

    跟间隙锁存在冲突关系的,是“往这个间隙中插入一个记录”这个操作。间隙锁之间都不存在冲突关系。

    间隙锁的问题

    间隙锁的引入,可能会导致同样的语句锁住更大的范围,这其实是影响了并发度的。

    展开全文
  • C#编程经验技巧宝典

    热门讨论 2008-06-01 08:59:33
    100 <br>0158 如何将二进制数转换为十六进制数 100 <br>0159 如何实现0~9之间随机整数 101 <br>0160 如何实现0~1之间随机数 101 <br>0161 如何返回数字的绝对值 101 <br>5.2 控件数据处理...
  • C#开发经验技巧宝典

    2008-10-14 20:21:45
    0830 如何将GridView控件数据导入Word 497 0831 如何将GridView控件数据导入Excel 497 0832 单片机如何实现在线调试 498 0833 单片机如何实现内存优化管理 498 0834 单片机实现文件系统管理 498 0835 杀...
  • delphi 开发经验技巧宝典源码

    热门讨论 2010-08-12 16:47:23
    0235 如何读取Word中的文本 156 0236 通过身份证号获取年龄 157 0237 如何实现一个应用程序只能打开一个进程 158 7.4 其他数据处理技术 159 0238 对计算结果四舍五入 159 0239 获取一个字符的ASCII值 ...
  • 0235 如何读取Word中的文本 156 0236 通过身份证号获取年龄 157 0237 如何实现一个应用程序只能打开一个进程 158 7.4 其他数据处理技术 159 0238 对计算结果四舍五入 159 0239 获取一个字符的ASCII值 ...
  • 0235 如何读取Word中的文本 156 0236 通过身份证号获取年龄 157 0237 如何实现一个应用程序只能打开一个进程 158 7.4 其他数据处理技术 159 0238 对计算结果四舍五入 159 0239 获取一个字符的ASCII值 ...
  • 0235 如何读取Word中的文本 156 0236 通过身份证号获取年龄 157 0237 如何实现一个应用程序只能打开一个进程 158 7.4 其他数据处理技术 159 0238 对计算结果四舍五入 159 0239 获取一个字符的ASCII值 ...
  • 0235 如何读取Word中的文本 156 0236 通过身份证号获取年龄 157 0237 如何实现一个应用程序只能打开一个进程 158 7.4 其他数据处理技术 159 0238 对计算结果四舍五入 159 0239 获取一个字符的ASCII值 ...
  • 0235 如何读取Word中的文本 156 0236 通过身份证号获取年龄 157 0237 如何实现一个应用程序只能打开一个进程 158 7.4 其他数据处理技术 159 0238 对计算结果四舍五入 159 0239 获取一个字符的ASCII值 ...
  •  实例019 巧位移运算符获取汉字编码值 24  实例020 使用条件运算符判断指定年份是不是闰年 25  实例021 使用流程控制语句报销业务花销 26 2.3 关键字的使用 27  实例022 使用checked关键字处理“溢出”...
  • 实例184 随机更换主界面背景 实例185 磁性窗体的设计 实例186 制作鼠标穿透窗体 实例187 窗体换肤程序 8.4 窗体动画 实例188 窗体中的滚动字幕 实例189 动画显示窗体 实例190 制作类似Office的提示精灵 实例...
  • 实例184 随机更换主界面背景 实例185 磁性窗体的设计 实例186 制作鼠标穿透窗体 实例187 窗体换肤程序 8.4 窗体动画 实例188 窗体中的滚动字幕 实例189 动画显示窗体 实例190 制作类似Office的提示精灵 实例...
  • 实例184 随机更换主界面背景 实例185 磁性窗体的设计 实例186 制作鼠标穿透窗体 实例187 窗体换肤程序 8.4 窗体动画 实例188 窗体中的滚动字幕 实例189 动画显示窗体 实例190 制作类似Office的提示精灵 实例...
  • 实例184 随机更换主界面背景 237 实例185 磁性窗体的设计 238 实例186 制作鼠标穿透窗体 246 实例187 窗体换肤程序 247 8.4 窗体动画 250 实例188 窗体中的滚动字幕 250 实例189 动画显示窗体 251 实例190 制作类似...
  • delphi生成随机数

    2012-06-18 10:38:34
    随机数在信息学奥林匹克中可用于随机化搜索、穷举等算法,以优化其性能,也可用于在快速排序中选择关键数,以使其快速排序算法的最坏情况没有固定的相应数列。如果你希望使用了随机数的程序对同一个输入数据能有恒定...
  • 停用词(stop word) 第4章 查询 4.1 访问字典的方法 访问数据结构 前端编码(Front coding) 最小完美哈希函数 完美哈希函数的设计 基于磁盘的字典存储 4.2 部分指定的查询术语 字符串暴力匹配(Brute-force string...
  • 实例071 在TListView控件中对数据排序或统计 84 实例072 在TListView组件中绘制底纹 86 实例073 在列表视图中拖动视图项 87 2.6 TTreeView控件应用典型实例 88 实例074 将数据库数据显示到树视图中 88 ...
  • 算术编码是如何工作的 实现算术编码 保存累积计数 2.5 符号模型 部分匹配预测 块排序压缩 动态马尔科夫压缩 基于单字的压缩 2.6 字典模型 自适应字典编码器的LZ77系列 LZ77的Gzip变体 自适应字典编码器的LZ78系列 LZ...
  • 实例071 在tlistview控件中对数据排序或统计 86 实例072 在tlistview组件中绘制底纹 88 实例073 在列表视图中拖动视图项 89 2.6 ttreeview控件应用典型实例 90 实例074 将数据库数据显示到树视图中 90...
  • 实例071 在tlistview控件中对数据排序或统计 86 实例072 在tlistview组件中绘制底纹 88 实例073 在列表视图中拖动视图项 89 2.6 ttreeview控件应用典型实例 90 实例074 将数据库数据显示到树视图中 90...
  • asp.net知识库

    2015-06-18 08:45:45
    在Asp.net中如何用SQLDMO来获取SQL Server中的对象信息 使用Relations建立表之间的关系并却使用PagedDataSource类对DataList进行分页 通过作业,定时同步两个数据库 SQLSERVER高级注入技巧 利用反射实现ASP.NET控件和...
  • 11.2.7 对ListView控件中的项进行排序 11.3 设计过程 11.3.1 主窗体预览 11.3.2 批量复制、剪切文件 11.3.3 批量复制、剪切文件夹 11.3.4 批量重命名文件 11.3.5 批量删除文件及文件夹 11.3.6 搜索文件及文件夹 11.3...
  • 介绍了应用Java Web进行程序开发的各个方面的知识和技巧,主要包括Java Web编程基础、文件操作管理、图形图像与多媒体技术、窗体应用技术、JSP操作Word与Excel等。全书分6篇23章,共计600个实例和600个经验技巧。每...
  • 介绍了应用Java Web进行程序开发的各个方面的知识和技巧,主要包括Java Web编程基础、文件操作管理、图形图像与多媒体技术、窗体应用技术、JSP操作Word与Excel等。全书分6篇23章,共计600个实例和600个经验技巧。每...
  • java范例开发大全(pdf&源码)

    热门讨论 2013-07-04 13:04:40
    实例138 iText生成Word文件 205 实例139 利用POI读取Word文件中的内容 208 7.3 字符流 209 实例140 按顺序创建文件 210 实例141 按顺序读取文件 211 实例142 追加文件内容 211 实例143 只显示文件中指定的字符 214...
  • 实例071 如何将字节单位B转换成GB、MB 和KB 108 实例072 身份证号从15位升到18位算法 109 第3章 面向对象编程思想 111 3.1 面向对象家族核心——类与类成员 112 实例073 面向对象编程核心——类、对象和引用 112 ...
  • 实例071 如何将字节单位B转换成GB、MB 和KB 108 实例072 身份证号从15位升到18位算法 109 第3章 面向对象编程思想 111 3.1 面向对象家族核心——类与类成员 112 实例073 面向对象编程核心——类、对象和引用 112 ...
  • 实例071 如何将字节单位B转换成GB、MB 和KB 108 实例072 身份证号从15位升到18位算法 109 第3章 面向对象编程思想 111 3.1 面向对象家族核心——类与类成员 112 实例073 面向对象编程核心——类、对象和引用 112 ...

空空如也

空空如也

1 2 3 4 5
收藏数 88
精华内容 35
关键字:

如何用word随机排序