良玉的博客 点点滴滴,积水成河_良玉的博客_页游、手游linux运维工程师之路

几百万的数据,mysql快速高效创建索引

  有一个问题,一张表有3百万条记录,随着时间的增加,记录量会更多,此时查询速度很慢。在创建此表前没有未相应字段添加索引,所以此时需要为表添加索引。但是因为数据量大的原因,索引添加不成功,想了很多办法,终于在短时间内解决了。

  办法如下:

  1、进入mysql界面。mysql -uroot -hlocalhost -plovelive gm;

  2、导出相应表的数据。select * from tab into outfile 'tab.txt'; 此处tab.txt文件在mysql的data目录里


阅读全文...

mysql日志通过general_log(普通日志)显示操作连接数据库的IP

mysql日志要记录操作连接数据库的IP就得开启general_log(普通日志)


General log:
Geleral log记录了服务器接收到的每一个查询或是命令,无论这些查询或是命令是否正确甚至是否包含语法错误,general log 都会将其记录下来 ,记录的格式为 {Time ,Id ,Command,Argument }。也正因为mysql服务器需要不断地记录日志,开启General log会产生不小的系统开销。 因此,Mysql默认是把General log关闭的。 我们可以通过修改Mysql全局变量来开启General log功能或是更改日志存放路径。 
注意:mysql5.0版本,如果要开启slow log、general log,需要重启,从MySQL5.1.6版开始,general query log和slow query log开始支持写到文件或者数据库表两种方式,并且日志的开启,输出方式的修改,都可以在Global级别动态修改。


阅读全文...

mysql之 Genral log(普通日志)与 Slow log(慢速日志)

对Mysql进行深入的分析对于发现mysql性能瓶颈和寻找优化策略是十分必要的。 我们可以从不同的粒度上对Mysql进行分析:可以整体分析服务器,或者检查单个查询或批查询。
通过分析,我们得到的如下信息:
1、Mysql访问得最多的数据
2、Mysql执行得最多的查询的种类
3、Mysql停留时间最长的状态
4、Mysql用来执行查询的使用得最频繁的子系统
5、Mysql查询过程中访问的数据种类
6、Mysql执行了多少种不同类型的活动,比如索引扫描。

阅读全文...

Mysql 重做日志及与二进制日志的区别

Mysql默认情况下会有两个文件:ib_logfile0ib_logfile1,这两个文件就是重做日志文件,或者事务日志。

重做日志的目的:万一实例或者介质失败,重做日志文件就能派上用场。

每个InnoDB存储引擎至少有一个重做日志文件组,每个文件组下至少有2个重做日志文件,如默认的ib_logfile0ib_logfile1InnoDB存储引擎先写重做日志文件1,当达到文件的最后时,会切换至重做日志文件2,当重做日志文件2也被写满时,会再被切换到重做日志文件1中。


阅读全文...

MySQL之错误日志(Error Log)

同大多数关系型数据库一样,日志文件是MySQL数据库的重要组成部分。MySQL有几种不同的日志文件,通常包括错误日志文件,二进制日志,通用日志,慢查询日志,等等。这些日志可以帮助我们定义mysqld内部发生的事情,数据库性能故障,记录数据的变更历史,用户恢复数据库等等。本文主要描述错误日志文件。

1、MySQL日志文件系统的组成
  a、错误日志:记录启动、运行或停止mysqld时出现的问题。
  b、通用日志:记录建立的客户端连接和执行的语句。
  c、更新日志:记录更改数据的语句。该日志在MySQL 5.1中已不再使用。
  d、二进制日志:记录所有更改数据的语句。还用于复制。
  e、慢查询日志:记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询。
  f、Innodb日志:innodb redo log


阅读全文...

Mysql中primary key主键冲突的可能性与解决方案

        在mysql中,数据的存储其实相当于Oracle的IOT表。主键和其他列的值以b+tree的形式组织在一起,在其叶子节点上不仅包含了主键,还包含了其他列的值。所以,我们在访问以主键作为where条件的查询时,极大的提高了效率。当然,这也存在一个缺点,主键和其他列存在一起,导致主键作为index比其他二级index需要的空间开销更大,所以当我们做recover index scan时,就会需要scan更多的block。除此之外,我们还需要数据按照主键升序排列,所以我们的主键就可能要设置为自增的,以免随机的数字作为主键,导致数据块频繁分裂,而减缓dml的速度。所以,一般情况下,我们会默认给每张表都加上一个自增的id作为主键,这个id的类型依据实际存放的数据量的范围来定。

阅读全文...

mysql优化-选择合适的innodb_log_file_size

Choosing proper innodb_log_file_size


选择合适的innodb_log_file_size


If you’re doing significant amount of writes to Innodb tables decent size of innodb_log_file_size is important for MySQL Performance. However setting it too large will increase recovery time, so in case of MySQL crash or power failure it may take long time before MySQL Server is operational again.


如果对 Innodb 数据表有大量的写入操作,那么选择合适的 innodb_log_file_size 值对提升MySQL性能很重要。然而设置太大了,就会增加恢复的时间,因此在MySQL崩溃或者突然断电等情况会令MySQL服务器花很长时间来恢复。


阅读全文...

MySQL 5.5 my.cnf配置说明及优化

MySQL 5.5.13
参数说明:
[client]
character-set-server = utf8
port    = 3306
socket  = /data/mysql/3306/mysql.sock
[mysqld]
character-set-server = utf8
user    = mysql
port    = 3306
socket  = /data/mysql/3306/mysql.sock
basedir = /usr/local/webserver/mysql
datadir = /data/mysql/3306/data
log-error = /data/mysql/3306/mysql_error.log
pid-file = /data/mysql/3306/mysql.pid

阅读全文...

mysql查询时间戳和日期的转换

在数据库的使用中,经常需要按指定日期来查询记录,以便于统计,而在数据库中,有很多存储的是时间戳,

也有的直接存日期,查询的时候可能不是那么好弄.

mysql提供了两个函数:

          from_unixtime(time_stamp)   ->  将时间戳转换为日期

          unix_timestamp(date)             ->  将指定的日期或者日期字符串转换为时间戳

也可以指定格式
 

阅读全文...

mysql查询更新时的锁表机制

为了给高并发情况下的mysql进行更好的优化,有必要了解一下mysql查询更新时的锁表机制。

一、概述

MySQL有三种锁的级别:页级、表级、行级。

MyISAM和MEMORY存储引擎采用的是表级锁(table-level locking);BDB存储引擎采用的是页面锁(page-level
locking),但也支持表级锁;InnoDB存储引擎既支持行级锁(row-level locking),也支持表级锁,但默认情况下是采用行级锁。

阅读全文...

解决MyISAM锁表

服务器上经常出现mysql进程占CPU100%的情况,使用show processlist命令后,看到出现了很多状态为LOCKED的sql。使用show status like 'table%'检查Table_locks_immediate和Table_locks_waited,发现Table_locks_waited偏 大。出问题的表是MyISAM,分析大概是MyISAM的锁表导致。

MyISAM适合于读频率远大于写频率这一情况。而我

阅读全文...

mysql批量删除表

mysql中没有现有的工具可以直接实现,所以原理就是使用现有的功能实现drop table 的语句的自动拼写,从而达到简化目的。


下边举例说明,如果想删除”abc_”开头的表:


select CONCAT( 'DROP TABLE IF EXISTS ', table_name, ';' ) FROM information_schema.tables Where table_name LIKE 'abc_%';


阅读全文...