mysql怎样高效率随机获取n条数据

数据库优化有很多可以讲，按照支撑的数据量来分可以分为两个阶段：单机数据库和分库分表，前者一般可以支撑500W或者10G以内的数据，超过这个值则需要考虑分库分表。另外，一般大企业面试往往会从单机数据库问起，一步一步问到分库分表，中间会穿插很多数据库优化的问题。本文试图描述单机数据库优化的一些实践，数据库基于mysql，如有不合理的地方，欢迎指正。

1、表结构优化

在开始做一个应用的时候，数据库的表结构设计往往会影响应用后期的性能，特别是用户量上来了以后的性能。因此，表结构优化是一个很重要的步骤。

11、字符集

一般来说尽量选择UTF-8，虽然在存中午的时候GBK比UTF-8使用的存储空间少，但是UTF-8兼容各国语言，其实我们不必为了这点存储空间而牺牲了扩展性。事实上，后期如果要从GBK转为UTF-8所要付出的代价是很高的，需要进行数据迁移，而存储空间完全可以用花钱扩充硬盘来解决。

12、主键

在使用mysql的innodb的时候，innodb的底层存储模型是B+树，它使用主键作为聚簇索引，使用插入的数据作为叶子节点，通过主键可以很快找到叶子节点，从而快速获取记录。因此在设计表的时候需要增加一个主键，而且最好要自增。因为自增主键可以让插入的数据按主键顺序插入到底层的B+树的叶子节点中，由于是按序的，这种插入几乎不需要去移动已有的其它数据，所以插入效率很高。如果主键不是自增的，那么每次主键的值近似随机，这时候就有可能需要移动大量数据来保证B+树的特性，增加了不必要的开销。

13、字段

131、建了索引的字段必须加上not null约束，并且设置default值

132、不建议使用float、double来存小数，防止精度损失，建议使用decimal

133、不建议使用Text/blob来保存大量数据，因为对大文本的读写会造成比较大的I/O开销，同时占用mysql的缓存，高并发下会极大的降低数据库的吞吐量，建议将大文本数据保存在专门的文件存储系统中，mysql中只保存这个文件的访问地址，比如博客文章可以保存在文件中，mysql中只保存文件的相对地址。

134、varchar类型长度建议不要超过8K。

135、时间类型建议使用Datetime，不要使用timestamp，虽然Datetime占用8个字节，而timestamp只占用4个字节，但是后者要保证非空，而且后者是对时区敏感的。

136、建议表中增加gmt_create和gmt_modified两个字段，用来记录数据创建的修改时间。这两个字段建立的原因是方便查问题。

14、索引创建

141、这个阶段由于对业务并不了解，所以尽量不要盲目加索引，只为一些一定会用到索引的字段加普通索引。

142、创建innodb单列索引的长度不要超过767bytes，如果超过会用前255bytes作为前缀索引

143、创建innodb组合索引的各列索引长度不要超过767bytes，一共加起来不要超过3072bytes

2、SQL优化

一般来说sql就那么几种：基本的增删改查，分页查询，范围查询，模糊搜索，多表连接

21、基本查询

一般查询需要走索引，如果没有索引建议修改查询，把有索引的那个字段加上，如果由于业务场景没法使用这个字段，那么需要看这个查询调用量大不大，如果大，比如每天调用10W+，这就需要新增索引，如果不大，比如每天调用100+，则可以考虑保持原样。另外，select 尽量少用，用到什么字段就在sql语句中加什么，不必要的字段就别查了，浪费I/O和内存空间。

22、高效分页

limit m,n其实质就是先执行limit m+n，然后从第m行取n行，这样当limit翻页越往后翻m越大，性能越低。比如

select from A limit 100000,10，这种sql语句的性能是很差的，建议改成下面的版本:

selec id,name,age from A where id >=(select id from A limit 100000,1) limit 10

23、范围查询

范围查询包括between、大于、小于以及in。Mysql中的in查询的条件有数量的限制，若数量较小可以走索引查询，若数量较大，就成了全表扫描了。而between、大于、小于等，这些查询不会走索引，所以尽量放在走索引的查询条件之后。

24、模糊查询like

使用 like %name%这样的语句是不会走索引的，相当于全表扫描，数据量小的时候不会有太大的问题，数据量大了以后性能会下降的很厉害，建议数据量大了以后使用搜索引擎来代替这种模糊搜索，实在不行也要在模糊查询前加个能走索引的条件。

25、多表连接

子查询和join都可以实现在多张表之间取数据，但是子查询性能较差，建议将子查询改成join。对于mysql的join，它用的是Nested Loop Join算法，也就是通过前一个表查询的结果集去后一个表中查询，比如前一个表的结果集是100条数据，后一个表有10W数据，那么就需要在10010W的数据集合中去过滤得到最终的结果集。因此，尽量用小结果集的表去和大表做join，同时在join的字段上建立索引，如果建不了索引，就需要设置足够大的join buffer size。如果以上的技巧都无法解决join所带来的性能下降的问题，那干脆就别用join了，将一次join查询拆分成两次简单查询。另外，多表连接尽量不要超过三张表，超过三张表一般来说性能会很差，建议拆分sql。

3、数据库连接池优化

数据库连接池本质上是一种缓存，它是一种抗高并发的手段。数据库连接池优化主要是对参数进行优化，一般我们使用DBCP连接池，它的具体参数如下：

31 initialSize

初始连接数，这里的初始指的是第一次getConnection的时候，而不是应用启动的时候。初始值可以设置为并发量的历史平均值

32、minIdle

最小保留的空闲连接数。DBCP会在后台开启一个回收空闲连接的线程，当该线程进行空闲连接回收的时候，会保留minIdle个连接数。一般设置为5，并发量实在很小可以设置为1

33、maxIdle

最大保留的空闲连接数，按照业务并发高峰设置。比如并发高峰为20，那么当高峰过去后，这些连接不会马上被回收，如果过一小段时间又来一个高峰，那么连接池就可以复用这些空闲连接而不需要频繁创建和关闭连接。

34、maxActive

最大活跃连接数，按照可以接受的并发极值设置。比如单机并发量可接受的极值是100，那么这个maxActive设置成100后，就只能同时为100个请求服务，多余的请求会在最大等待时间之后被抛弃。这个值必须设置，可以防止恶意的并发攻击，保护数据库。

35、maxWait

获取连接的最大等待时间，建议设置的短一点，比如3s，这样可以让请求快速失败，因为一个请求在等待获取连接的时候，线程是不可以被释放的，而单机的线程并发量是有限的，如果这个时间设置的过长，比如网上建议的60s，那么这个线程在这60s内是无法被释放的，只要这种请求一多，应用的可用线程就少了，服务就变得不可用了。

36、minEvictableIdleTimeMillis

连接保持空闲而不被回收的时间，默认30分钟。

37、validationQuery

用于检测连接是否有效的sql语句，一般是一条简单的sql，建议设置

38、testOnBorrow

申请连接的时候对连接进行检测，不建议开启，严重影响性能

39、testOnReturn

归还连接的时候对连接进行检测，不建议开启，严重影响性能

310、testWhileIdle

开启了以后，后台清理连接的线程会没隔一段时间对空闲连接进行validateObject，如果连接失效则会进行清除，不影响性能，建议开启

311、numTestsPerEvictionRun

代表每次检查链接的数量，建议设置和maxActive一样大，这样每次可以有效检查所有的链接。

312、预热连接池

对于连接池，建议在启动应用的时候进行预热，在还未对外提供访问之前进行简单的sql查询，让连接池充满必要的连接数。

4、索引优化

当数据量增加到一定程度后，靠sql优化已经无法提升性能了，这时候就需要祭出大招：索引。索引有三级，一般来说掌握这三级就足够了，另外，对于建立索引的字段，需要考虑其选择性。

41、一级索引

在where后面的条件上建立索引，单列可以建立普通索引，多列则建立组合索引。组合索引需要注意最左前缀原则。

42、二级索引

如果有被order by或者group by用到的字段，则可以考虑在这个字段上建索引，这样一来，由于索引天然有序，可以避免order by以及group by所带来的排序，从而提高性能。

43、三级索引

如果上面两招还不行，那么就把所查询的字段也加上索引，这时候就形成了所谓的索引覆盖，这样做可以减少一次I/O *** 作，因为mysql在查询数据的时候，是先查主键索引，然后根据主键索引去查普通索引，然后根据普通索引去查相对应的记录。如果我们所需要的记录在普通索引里都有，那就不需要第三步了。当然，这种建索引的方式比较极端，不适合一般场景。

44、索引的选择性

在建立索引的时候，尽量在选择性高的字段上建立。什么是选择性高呢？所谓选择性高就是通过这个字段查出来的数据量少，比如按照名字查一个人的信息，查出来的数据量一般会很少，而按照性别查则可能会把数据库一半的数据都查出来，所以，名字是一个选择性高的字段，而性别是个选择性低的字段。

5、历史数据归档

当数据量到了一年增加500W条的时候，索引也无能为力，这时候一般的思路都是考虑分库分表。如果业务没有爆发式增长，但是数据的确在缓慢增加，则可以不考虑分库分表这种复杂的技术手段，而是进行历史数据归档。我们针对生命周期已经完结的历史数据，比如6个月之前的数据，进行归档。我们可以使用quartz的调度任务在凌晨定时将6个月之前的数据查出来，然后存入远程的hbase服务器。当然，我们也需要提供历史数据的查询接口，以备不时之需。

以上就是对mysql 单机数据库的优化资料整理，后续继续补充相关资料，谢谢大家对本站的支持！

这种定时任务一般都是写shell脚本来解决，通过定时执行shell脚本来实现定时任务。

可以在shell脚本中，指定需要批量删除的数据库，将它们设置为变量，然后写清空表的语句，就可以实现定义在变量中的数据库批量删除。

你可以先写一个脚本进行测试，可以读取到一个数据库就行，然后将多个数据库放到数组中，循环遍历即可。

最后测试没问题的话，就可以将shell脚本添加到crontab定时任务中，就可以实现每2天定时清空数据表。

O'Reilly Learning PHP & MySQL 第二版

>

个人建议楼主一方面脚踏实地多写程序，另一方面戒骄戒躁，好好学习java技术。

根据你的问题，和你与其他回答者的交流来看，确实如3楼所说，你的思路错了，不过也不奇怪，可能你的水平目前正处于较低的位置，这也是我在开始处提那点建议的原因。

说到具体的问题，如果你执意要延续当前的想法，用JSP来传递数据，虽然别扭，但也是可以的。此处假设你所说的“java界面”是指java Swing图形界面，你的队友开发的是桌面应用，那么你的队友可以封转一个数据获取与转换模块，来专门从你提供的URL所对应的JSP页面中获取并解析、封装数据，然后在其他地方使用。该模块的核心是，通过URLConnection对象获取URL对应页面源码（如果是JSON格式或其他约定的格式最好），然后根据数据格式反向解析数据，并封转成对象，很简单的思路；

如果你的队友也是web应用，那么最好，通过RMI或WebService服务获取更加简单(其实无论是web应用还是桌面应用，这两种技术都是可以用来达到你想要的效果)；

不过我很赞同3楼的看法，建议你还是将你的mysql配置成可以远程连接的服务器模式，让你的队友直接连接你的数据库，然后他那边的程序写个定时器，定时抽取数据库中的数据，你这边的工作基本上就全省掉了，这是最简单最快捷的方式；

方法/步骤

1 在根目录下新建定时备份存储文件夹mkdir /mysql_backup

2新建备份的脚本vim /root/mysql_backupsh

3在mysql_backupsh中输入内容：

backupdir=/mysqlbackup time=` date +%Y%m%d%H`mysqldump -uroot -proot abc | gzip > $backupdir/abc$timesqlgzfind $backupdir -name “abcsqlgz" -type f -mtime +5 -exec rm {} \; > /dev/null 2>&1

mysql要实现定时执行sql语句就要用到Event

具体 *** 作如下：

先看看看event 事件是否开启

show variables like '%sche%';

如没开启，则开启。需要数据库超级权限

set global event_scheduler =1;

创建存储过程 update_a (注：就是你要执行的sql语句)

mysql> create procedure update_a() update a set ay_avg=(select avg(byouhao) from b where aa_id=ba_id);

创建一个定时任务：event e_updateA

mysql> create event if not exists e_updateA

-> on schedule every 60 second ---设置60秒执行一次

-> on schedule at date_add(now(),interval 1 minute) ---在一分钟后执行

-> on completion preserve

-> do call update_a(); ---执行update_a()存储过程

创建Event之后，sql语句就定时执行一次。

关闭事件任务

mysql> alter event e_updateA ON

-> COMPLETION PRESERVE DISABLE;

开启事件任务

mysql> alter event e_updateA ON

-> COMPLETION PRESERVE ENABLE;

不知道你想做什么。但是如果是用计时器做什么数据处理的话，还是建议你在其他地方做连接，计时器里做处理。最后退出的时候关闭连接。因为连接数据库是比较慢的。当然，如果你就是需要这么玩，那么在用完之后就需要关闭了。

以上就是关于mysql怎样高效率随机获取n条数据全部的内容，包括:mysql怎样高效率随机获取n条数据、如何在mysql中定时清除所有库中指定的表中的数据，而不用每个库都写一遍定时任务，求大神指点！！！、如何在php随机抽取mysql数据库里的一定数量的记录并进行分页呢等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！