MySQL
熟用MySQL,为高性能MySQL而努力
moxiaomomo
虚怀若谷,大爱无疆
展开
-
[mysql]python3实现并发访问水平切分表
场景说明假设有一个mysql表被水平切分,分散到多个host中,每个host拥有n个切分表。 如果需要并发去访问这些表,快速得到查询结果, 应该怎么做呢? 这里提供一种方案,利用python3的asyncio异步io库及aiomysql异步库去实现这个需求。代码演示import loggingimport randomimport asynciofrom aiomysql import c原创 2017-08-07 12:12:51 · 1779 阅读 · 0 评论 -
[MySQL]Fatalerror: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
今天在centOS7上安装mysql5.6后, 无法通过mysql -u root登录, 并且提示ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)/etc/my.cnf下的日志路径为/var/log/mysqld.log, 打开日志原创 2016-10-31 16:00:28 · 13343 阅读 · 2 评论 -
【MySQL】基础知识小结
一. char与varchar 1)char为固定空间存储, 实际数据不够会补够,超出长度则截断; varchar为不固定长度存储,使用额外的1-2个字节来存储长度信息。 2)char可能导致空间浪费, 但也可能有效减少空间碎片,上限255; varchar的情况相反,上限65535个字节(指定的长度表示的为字符,要考虑编码)二. int(x) x代表了原创 2014-04-08 00:14:15 · 3398 阅读 · 2 评论 -
【MySQL】关于MySQL错误日志信息的收集
为方便维护MySQL,写了个脚本用以提供收集错误信息的接口。这些错误信息来自与MySQL错误日志,而 通过grep mysql可以获取error-log的路径。#!/usr/bin/env python2.7#-*- encoding: utf-8 -*-"""该模块用于提取每天mysql日志中的异常或错误信息author: xiaomoemail: moxiaomomo@gmai原创 2013-12-10 20:59:17 · 3473 阅读 · 0 评论 -
【MySQL】使用Symbolic Links以支持数据分区存储
MySQL数据库及表(仅MyISAM)支持符号链接(symbolic link),即数据库或表可以存储在my.cnf中指定datadir之外的分区或目录。要支持符号链接,需要在配置中设置symbolic-links=1(较新的版本为默认开启)假设my.cnf有设置data目录: datadir=/data1/mysql/data/而在另一个分区目录下保存原创 2013-12-03 11:20:42 · 21994 阅读 · 0 评论 -
【MySQL】问题小结
最近在提交新的代码之后,MySQL偶尔会抛出异常: (2006, 'MySQL server has gone away') 这两天情况变得更糟糕,除了这个异常还有: thread.error: can't start new thread重新检查了一遍代码之后,发现了两个问题:(1)根目录/ 下的空间已经被用光了,对应的mysql错误日志会有相关提示:2013-11-原创 2013-12-02 09:44:33 · 3698 阅读 · 0 评论 -
【MySQL】修改data目录后重启失败
今天在处理数据修复时,修改了MySQL的datadir,结果导致了重启失败,提示信息为:Starting MySQL..The server quit without updating PID file (/var/lib/mysql/localhost.localdomain.pid)检查了相关文件权限,没有发现问题。想查看错误日志输出时,却发现my.cnf中定义的错误日志没原创 2013-11-28 23:44:26 · 4552 阅读 · 1 评论 -
【MySQL】批量检查表并进行repair,optimize
以下是shell的参考代码:#!/bin/bashhost_name=192.168.0.123user_name=xiaomouser_pwd=my_pwd database=my_db_nameneed_optmize_table=truetables=$(mysql -h$host_name -u$user_name -p$user_pwd $database -A -原创 2013-11-11 15:25:31 · 2811 阅读 · 1 评论 -
【mysql】MYISAM表批量压缩
关于对MYISAM表的压缩,可以使用myisampack和myisamchk完成(myisampack完之后必须进行myisamchk才能使用压缩后的表,而且是只读的), 其详细地用法可以参考官方文档: http://dev.mysql.com/doc/refman/5.1/zh/client-side-scripts.html。这两个操作需要谨慎使用,在压缩之前需要确认mysqld已关闭或者原创 2013-08-29 00:36:05 · 3189 阅读 · 0 评论 -
【MySQL】ERROR : (2006, 'MySQL server has gone away')
意思就是指client和MySQL server之间的链接断开了。造成这样的原因一般是sql操作的时间过长,或者是传送的数据太大(例如使用insert ... values的语句过长, 这种情况可以通过修改max_allowed_packed的配置参数来避免,也可以在程序中将数据分批插入)。原创 2013-08-25 19:44:34 · 5098 阅读 · 2 评论 -
【MySQL】关于Repair by keyCache
在执行optimize表时,如果出现Repair with keycache时,说明tmp空间不够了...这时MySQL性能极低,又不能冒然kill掉进程,所以耐心等待操作完成吧。而内存足够的情况下,这种过程应该是提示Repair by sorting。如何避免这种情况的发生,可以参考 http://www.penglixun.com/tech/database/slove_repair_wi原创 2013-08-11 15:49:48 · 4215 阅读 · 0 评论 -
【MySQL】关于定期优化表(optimize table)
如果一个表数据量比较大,且经常有插入或删除操作,会产生大量的空间碎片(主要由于对表进行delete操作时,服务器并不一定会释放磁盘空间, 或者数据块大小的影响)。如果不定期清理这些碎片,数据库的性能会加速下降。因此应该有个定期任务,来对表进行optimize操作;一方面可以回收大量的物理空间,另一方面可以大幅度恢复MySQL数据库原有的性能。原创 2013-08-07 09:20:58 · 4791 阅读 · 1 评论 -
【MySQL】查看MySQL配置文件路径及相关配置
(1)关于配置文件路径有时候,我发现虽然尝试修改了配置文件的一些变量,但是并没有生效。后来才发现原来是因为修改的文件并非MySQL服务器读取的配置文件。如果不清楚MySQL当前使用的配置文件路径,可以尝试这样查看:从上图可以看出, 服务器首先会读取/etc/my.cnf文件,如果发现该文件不存在,再依次尝试从后面的几个路径进行读取。(2)关于配置原创 2013-08-06 16:15:03 · 103821 阅读 · 5 评论 -
【MySQL】使用自定义变量,编写偷懒的UNION
(参考自>)假设有这样的需求:写一个UNION查询,其第一个子查询作为分支先执行,如果找到了匹配的行,则不再执行第二个分支的查询。一般来说,我们可以写出这样的UNION查询:select id from users where id=123456union allselect id from users_archived where id = 123456;原创 2013-07-28 22:02:52 · 2076 阅读 · 0 评论 -
【mysql】关联子查询的一种优化
很多时候,在mysql上实现的子查询的性能较差,这听起来实在有点难过。特别有时候,用到IN()子查询语句时,对于上了某种数量级的表来说,耗时多的难以估计。本人mysql知识所涉不深,只能慢慢摸透个中玄机了。假设有这样的一个exists查询语句:select * from table1where exists (select * from table2 where id>=30000 and table1.uuid=table2.uuid);原创 2013-07-28 15:40:20 · 6355 阅读 · 0 评论 -
【mysql】关于切分查询
对于大查询有时需要‘分而治之’,将大查询切分为小查询: 每个查询功能完全一样,但只完成原来的一小部分,每次查询只返回一小部分结果集。删除旧的数据就是一个很好地例子。定期清理旧数据时,如果一条sql涉及了大量的数据时,可能会一次性锁住多个表或行,耗费了大量的系统资源,却阻塞了其他很多小的但重要的查询。将一个大得DELETE语句切分为较小的查询时,可以尽量减少影响msql的性能,同时减少m原创 2013-07-15 23:26:03 · 2650 阅读 · 0 评论 -
【mysql】索引使用小结
(1)有一种情况下,order by可以在不满足最左前缀规则时用到索引: 当前导列为常量时。例如有索引为 KEY my_idx (a,b,c)查询语句为 select a,b,c from my_tbl where a=1 order by b,c。这样由于索引第一列被指定为常数,即使order by不满足索引的最左前缀的要求,也可以用于排序。(2)MYISAM会使用前原创 2013-07-08 22:30:09 · 1570 阅读 · 0 评论 -
【mysql】关于count(*)与count(col)
虽说count(*)与count(col)在不同的情况下,可能会表现出不同的性能。但是一般情况下,count(*)会对主键进行索引扫描,统计的是表中所有符合的记录总数;而count(col)扫描的不一定是主键,它统计的是表中所有符合的col列的记录量。对于以下两种查询:select count(*) from tbl_name;select count(col) from tb原创 2013-07-02 09:15:36 · 2351 阅读 · 2 评论 -
【mysql】关于子查询的一个例子
假设表my_tbl包含三个字段a,b,c;现在需要查询表中列a的每个不同值下的列b为最小值的记录量。比如表记录为:a b c1 3 'cd'2 3 'nhd'1 5 'bg'2 6 'cds'1 7 'kiy'3 7 'vsd'3 8 'ndf'希望得到结果为:a b c1 3 'cd'原创 2013-06-26 13:55:59 · 3321 阅读 · 1 评论 -
【mysql】关于命令load data local infile
要支持命令load data local infile,应当在/etc/mysql/my.cnf中添加这样的设置:[mysqld] #服务端配置local-infle = 1[mysql] #客户端配置local-infile = 1否则,mysql服务会提示错误:ERROR 1148 (42000): The原创 2013-05-23 17:48:10 · 8510 阅读 · 0 评论 -
【python】asq中group_by用法
A. 原生sql语句与asq语句对比假设有这样一个sql查询语句:select id,ip,ch,date from tbl group by ch,date;如要使用python的asq模块的group_by方法,则可以这样实现:(1)通过sql查询所有记录得出res_list;(2)然后使用asq功能: query(res_list).gro原创 2013-05-06 21:23:51 · 3642 阅读 · 0 评论 -
【mysql】开启慢查询日志
虽然操作简单,由于一开始没注意到mysql对文件的读写权限问题,结果发现虽然修改了配置文件my.cnf并开启了慢查询,但是实际上该日志文件总是没有创建;打开mysql的error.log,提示could not use 'slowquery.log'。 后来把权限问题改好,终于可以打印慢查询日志了。为免重蹈覆辙,以下记录开启慢查询日志的步骤: (1)修改/etc/my原创 2013-05-03 14:47:26 · 1785 阅读 · 0 评论 -
【mysql】关于字符串索引对update的影响
对某一个类型为varchar的字段添加前缀索引后,基于该子段的条件查询时间基本大幅下降;但对于update操作,所耗的时间却急剧上升,主要原因是在更新数据的同时,mysql会执行索引的更新。下面做了一个简单的试验。(1)首先对某个亿级记录的表字段所有记录执行更新:for idx in range(1, count+1): sql = "update tbl_原创 2013-04-25 10:36:46 · 2886 阅读 · 0 评论 -
【mysql】关于mysql执行时的Sleep状态
在通过python脚本执行mysql表更新操作时,想了解更新的具体情况。于是show processlist;了一下,结果发现该脚本所属的进程处于Sleep状态。再次执行show processlist;命令,进程又恢复工作状态了。如下图所示: 如此反复,说明该进程在执行mysql更新或查询操作后未及时释放资源。粗略想了下,应该mysql连接处于Sleep状态时,python脚本原创 2013-04-24 11:57:43 · 5475 阅读 · 0 评论 -
【mysql】避免重复插入记录,并获取该重复记录的id?
假设表tbl的列col1具有唯一性,即unique。为了避免插入重复col1值而报错误,可以使用ignore属性:insert ignore into ... values(...);也可以使用replace into语句,即可以插入记录,但是col1会被替换,并且自增id也会改变。另外也可以这样,当unique或主键重复时,会执行update操作:inse原创 2013-04-16 22:07:25 · 2863 阅读 · 2 评论 -
【mysql】hql查询日期用法
一开始没注意用法,在查询日期范围时直接将时间类型拼接到hql字符串里面了,结果总是报错。正确用法应该如下:String hql = "from Channel where c.create_time >= ? order by c.create_time"; // 用占位符Query query = session.createQuery(hql);query.setParameter(0原创 2013-03-07 20:30:35 · 6032 阅读 · 0 评论 -
【mysql】sql语句参数化
使用PreparedStatement支持预编译指令,在多次执行一条固定格式的sql字符串时比Statement更有效率.小结一下,它主要有以下几个优点:(1)防止sql注入;(2)自动类型转换;(3)有更好的性能(对于oracle数据库效果明显,mysql则不一定);(4)避免在拼接sql语句时过多的使用引号和字符串连接符等,代码更加简洁。通过jdbc连接数据库的具体用法原创 2013-02-17 11:04:23 · 5986 阅读 · 0 评论 -
【mysql】查询过滤器ON,WHERE,HAVING
执行顺序:首先是ON,接着是WHERE, 接着是HAVING。用法:ON : 在进行多表的连接查询时,才会用到on来过滤。ON首先会把不符合查询条件的数据给过滤掉,从而减少了查询过程中数据运算的次数。WHERE :在查询得到临时表后,在数据计算之前执行。由于数据还没分组和进行列的选取操作, 因此不能应用wher原创 2013-01-28 15:11:34 · 2639 阅读 · 2 评论 -
【mysql】修改mysql5.5默认编码
mysql数据库的默认编码并不是utf-8。安装mysql后,启动服务并登陆,使用show variables命令可查看mysql数据库的默认编码:由上图可见database和server的字符集使用了latin1编码方式,不支持中文,即存储中文时会出现乱码。以下是命令行修改为utf-8编码的过程,以支持中文。(1)关闭mysql服务service mysql原创 2013-01-24 20:19:00 · 28658 阅读 · 0 评论 -
【mysql】MyISAM表的存储格式
MyISAM支持三种不同的存储格式:固定格式,动态格式,已压缩格式(只能使用myisampack工具来创建)。静态表特征(固定格式)静态格式是MyISAM表的默认存储格式。当表不包含变量长度列(VARCHAR, BLOB, 或TEXT)时,使用这个格式。每一行用固定字节数存储。静态格式是三种存储格式中最简单和最安全的,同时也是最快的ondisk格式。基本特征: (1)原创 2013-01-23 15:30:54 · 3876 阅读 · 0 评论 -
【mysql】指定程序选项
在命令行上使用选项参数在命令行中使用选项有几个原则:(1)选项紧跟在命令名后面。(2)选项有两种参数形式:长名和短名。 shell中长名形式: mysql -uroot -p --execute="select * from user" testdb shell中短名形式: mysql -uroot -p -e "select *原创 2013-01-22 11:30:23 · 1421 阅读 · 0 评论