存档

‘mysql’ 分类的存档

一个关于分页的面试题

2015年5月1日 5 条评论

在网上看到面试中考分页的帖子,结合自己的想法,集合一下,欢迎讨论。

分页是各式各样系统开发过程中必不可少的环节,普通web应用数据量小、访问量小,分页可以用简单的方式来实现,一般是通过startrow+pagenum来实现,甚至可以提前生成静态页面,这样数据库基本没有压力,缺点是数据变动时要重新生成所有列表页,而且不能实时显示数据的变化。

不过还好的是对于大多数的应用能够实现需求即可,不必过多考虑优化。

互联网公司的海量数据,情况就变得不一样了,更多考虑的是性能和效率,加载速度提高一点点,就意味着用户体验的提升,用户体验决定着产品的未来。

因此我们可以看到数据量变大的情况下一个高效的分页变的重要程度,分页面试能够体现面试者是否处理过大量数据,没处理过也能够体现其面试时处理问题的思考和应变能力。

不管什么方法做分页,它都离不开数据库的支持,优化原则是尽量减少扫描数据库中记录的条数。

常用的关系数据库mysql和oracle为例:mysql分页依赖于limit,oracle分页使用rownum实现。

mysql分页方法?

mysql分页的核心语句:

先看一下分页的基本原理(CSDN那个百万级数据库来测试!):

SELECT * FROM csdn ORDER BY id DESC LIMIT 100000,2000;
耗时: 0.813ms

分析:对上面的mysql语句说明:limit 100000,2000的意思扫描满足条件的102000行,扔掉前面的100000行,返回最后的2000行。

问题就在这里,如果是limit 100000,20000,需要扫描120000行,在一个高并发的应用里,每次查询需要扫描超过100000行,性能肯定大打折扣。

在《efficient pagination using mysql》中提出的clue方式。

利用clue方法,给翻页提供一些线索,比如还是SELECT * FROM csdn order by id desc,按id降序分页,每页2000条,当前是第50页,当前页条目id最大的是102000,最小的是100000。如果我们只提供上一页、下一页这样的跳转(不提供到第N页的跳转)。

那么在处理上一页的时候SQL语句可以是:
SELECT * FROM csdn WHERE id<=102000 ORDER BY id DESC LIMIT 2000; #上一页 耗时:0.015ms 处理下一页的时候SQL语句可以是: SELECT * FROM csdn WHERE id>102000 ORDER BY id ASC LIMIT 2000; #下一页
耗时:0.015ms

这样,不管翻多少页,每次查询只扫描20行。效率大大提高了!

但是,这样分页的缺点是只能提供上一页、下一页的链接形式。

oracle如何分页?

oracle分页的核心:

大量数据时oracle分页语句的优化(通过rownum和rowid来进行分页),如下:

分类: mysql, oracle 标签:

mysql-bin文件

2014年11月9日 没有评论

mysql的数据存放目录var下有很多文件
-rw-r----- 1 root root 296208 Nov 8 21:51 mysql-bin.000051
-rw-r----- 1 root root 92588461 Nov 8 21:51 mysql-bin.000050
-rw-r----- 1 root root 1331729 Nov 8 21:51 mysql-bin.000061
-rw-r----- 1 root root 4736 Nov 8 21:51 mysql-bin.000060
-rw-r----- 1 root root 5231 Nov 8 21:51 mysql-bin.000059
-rw-r----- 1 root root 125 Nov 8 21:51 mysql-bin.000058
-rw-r----- 1 root root 125 Nov 8 21:51 mysql-bin.000057
-rw-r----- 1 root root 125 Nov 8 21:51 mysql-bin.000056
-rw-r----- 1 root root 125 Nov 8 21:51 mysql-bin.000055
-rw-r----- 1 root root 125 Nov 8 21:51 mysql-bin.000054
-rw-r----- 1 root root 12670376 Nov 8 21:51 mysql-bin.000053
-rw-r----- 1 root root 9118273 Nov 8 21:51 mysql-bin.000052
导致磁盘空间很快用完,对于小硬盘的vps来说很快就耗尽了vps空间。

这些文件是mysql的日志文件,用来实现主从复制的或者数据恢复的。
对于单机的vps不需要主从复制,我们可以关闭这个功能

1.编辑my.cnf
注释掉
#log-bin=mysql-bin
2.登陆到mysql管理界面,执行reset master删除这些文件
#mysql -u root -p
mysql> reset master;

----------------------

1:只有一个mysql服务器,那么可以简单的注释掉这个选项就行了。
vi /etc/my.cnf把里面的log-bin这一行注释掉,重启mysql服务即可。
2:如果你的环境是主从服务器,那么就需要做以下操作了。
A:在每个从属服务器上,使用SHOW SLAVE STATUS来检查它正在读取哪个日志。
B:使用SHOW MASTER LOGS获得主服务器上的一系列日志。
C:在所有的从属服务器中判定最早的日志,这个是目标日志,如果所有的从属服务器是更新的,就是清单上的最后一个日志。
D:清理所有的日志,但是不包括目标日志,因为从服务器还要跟它同步。
清理日志方法为:
PURGE MASTER LOGS TO 'mysql-bin.010';
PURGE MASTER LOGS BEFORE '2008-12-19 21:00:00';

分类: mysql 标签:

mysql多列索引和最左前缀

2014年4月12日 没有评论

数据库的索引可以加快查询速度,原因是索引使用特定的数据结构(B-Tree)对特定的列额外组织存放,加快存储引擎(索引是存储引擎实现)查找记录的速度。
索引优化是数据库优化的最重要手段。

如果查询语句使用索引(通常是where条件匹配索引)就会利用树的结构加快查找,索引会按值查找到要查找的行在表中位置,不需回表查询数据的就是聚簇索引(索引和数据存放在一起)。通常是需要回表再查数据,需要消耗额外的磁盘IO。所以有些时候(如按顺序读取数据)全表扫描会比使用索引快的原因就在于此。

查询条件只有一个字段时,在该字段建立索引即可,可优化的地方是对于text blob字段使用前缀索引。

当查询条件有多个字段时,单列索引和多列索引有很大的区别。如果使用多列索引,where条件中字段的顺序非常重要,需要满足最左前缀列。最左前缀:查询条件中的所有字段需要从左边起按顺序出现在多列索引中,查询条件的字段数要小于等于多列索引的字段数,中间字段不能存在范围查询的字段(<,like等),这样的sql可以使用该多列索引。 mysql多列索引适合的场景

  1. 全字段匹配
  2. 匹配部分最左前缀
  3. 匹配第一列
  4. 匹配第一列范围查询(可用用like a%,但不能使用like %b)
  5. 精确匹配某一列和和范围匹配另外一列

order by操作中出现的字段同样适用于按值查找的规则,where+order by中出现的字段需可以建立满足如上五种规则的多列索引。

使用多列需要按照最左索引列查找;不能跳过中间列;如果某一列是范围查询,那么其右边所有列无法使用索引。

IN什么情况下是范围查询,什么情况下是多个等值查询?如果有order by排序时,多个等于条件查询就是范围查询,没有order by排序就没有限制。

例如,建立多列索引(name, age, id),

只能使用索引的前两列。in是范围查询
... where name='nginx.cn' and age in(15,16,17) order by id

可以使用整个索引,in是按值查询
... where name='nginx.cn' and age in(15,16,17) and id ='3'

分类: mysql 标签: ,

使用另一个files的count结果update一个字段的值

2014年2月6日 没有评论

UPDATE typecho_metas tb1 set count = (SELECT COUNT(*) FROM typecho_relationships WHERE mid=tbl1.mid)

分类: mysql 标签:

linux编译安装mysql

2013年6月14日 10 条评论

紧接着上文,这次是重装mysql,正好之前有人要lnmp的安装方法,算上这篇文章就全了。
安装前准备

如果mysql用户不存在,那么添加mysql用户

mysql编译安装

到此mysql就安装到了/usr/local/mysql路径下,下面开始mysql的配置工作
------------------------------------

安装mysql选项文件

mysql设置开机自启动

配置权限表

启动mysql

这一步可能会报错
/etc/init.d/mysqld: line 260: my_print_defaults: command not found
/etc/init.d/mysqld: line 263: my_print_defaults: command not found
/etc/init.d/mysqld: line 270: @HOSTNAME@: command not found
/etc/init.d/mysqld: line 279: @HOSTNAME@: command not found
/etc/init.d/mysqld: line 292: cd: @prefix@: No such file or directory
Starting MySQLCouldn't find MySQL manager (@bindir@/mysqlmanager) or server (@bindir@/mysqld_safe) [FAILED]

因为路径没有设置,请重启确认上面配置工作是否都完成,一般是configure后没有make和make install。

mysql初始化配置:

mysql如何导入旧的数据:
直接拷贝数据库文件夹到数据目录下然后执行

==================================================================
mysql安装过程中问题

1.
make[2]: Entering directory /down/webinstall/mysql-5.1.57/mysql-test
make[3]: Entering directory
/down/webinstall/mysql-5.1.57/mysql-test
make[3]: Nothing to be done for install-exec-am.
make INSTALL_TO_DIR="/usr/local/mysql/mysql-test" install_test_files
make[4]: Entering directory
/down/webinstall/mysql-5.1.57/mysql-test
时间较长
MYSQL安装到这里卡了很久,可能以为出错了,实际上MYSQL已经安装成功了,它这步过段时间就会好了,是mysql在自我编译测试造成的。

2.checking for termcap functions library... configure: error: No curses/termcap library found
yum install ncurses ncurses-devel

3.Starting MySQL.Manager of pid-file quit without updating fi[FAILED]

启动数据库之前,需要先配置权限表,其中/usr/local/mysql为mysql的安装目录
chown -R mysql:mysql /usr/local/mysql
/usr/local/mysql/bin/mysql_install_db --user=mysql

分类: mysql 标签: ,

mysql启动不了

2013年4月28日 1 条评论

早上访问www.nginx.cn,页面提示

“建立数据库连接时出错”

机器环境跑的是nginx+php-fpm+mysql、出问题之前也没做过停止mysql进程的操作。

突然间数据库连接出错,

开始以为php的问题,那么我在命令行直接访问mysql

出现错误提示

阅读全文...

分类: mysql 标签: , , ,

mysql Table 'xx' is read only

2013年4月9日 没有评论

更换服务器环境的时候如果数据库是直接拷贝数据库文件夹过来,需要做两件事情。

其中/usr/local/lnmp/mysql/是mysql的安装路径:

重启mysql,数据库就可以使用。

因为数据库的权限是启动时加载的需要重新启动才行。

分类: mysql 标签: , ,

ERROR 2003 (HY000): Can't connect to MySQL server on 'www.neihanji.com' (111)

2012年11月12日 没有评论

从一台服务器去连接另一台服务器的mysql提示如下错误

ERROR 2003 (HY000): Can't connect to MySQL server on 'www.nginx.cn' (111)

注释掉/etc/mysql/my.conf中以下两条

#skip-networking
#bind-address = 127.0.0.1

重启mysql服务
service mysql restart

分类: mysql 标签: ,

mysql插入年月日格式日期

2012年8月21日 没有评论

mysql中插入如下数据

“2012年1月1日”到 date类型字段

 

数据库表结构 huangli(day date);

sql语句格式为

insert into huangli values(STR_TO_DATE('2012年1月1日', '%Y年%m月%d日'))

 

 

分类: mysql 标签: ,

mysql insert忽略1062错误

2012年8月6日 没有评论

批量insert语句主键冲突时,会停止执行,报如下错误,

ERROR 1062 (23000) at line 5412: Duplicate entry '1577987' for key 'PRIMARY'

mysql -uroot -p123456 -f database < filename.sql

-u后跟用户名,无空格

-p后跟密码,无空格

-f后跟数据库名,有空格表示强制执行,忽略错误

分类: mysql 标签: