mysql配置变量介绍

key_buffer_size

设置这个变量给键缓冲区(或者说键缓存)分配指定大小的空间。但是操作系统只有在实际用到这些空间的时候才会进行分配。例如,将键缓冲区大小设置为1GB,并不意味着服务器就会真正地给它分配1GB空间。

对一个已有的缓存设置非零值将会冲洗缓存,从技术上来说,这是一个在线操作,但是它会阻止所有访问该缓存的动作,直到缓存冲洗完成。

table_cache_size

设置这个变量是不会立即生效,要等到下一个线程打开表的时候才会生效。当它生效的时候,MYSQL会检查变量的值。如果值大于缓存中表的数量,线程就可以把新打开的表插入到缓存中。如果值小于缓存中表的数量,MySQL就会从缓存中删除掉没有使用的表。

thread_cache_size

设置这个变量不会立即生效,生效被延时到了下一次线程关闭的时候。在那时,MySQL检查缓存中是否有空间存储线程。如果是,它会把线程缓存起来,供另外一个连接使用。如果不是,它会直接结束掉线程。在这种情况下,缓存中线程的数量,以及线程缓存使用的内存数量不会立即就下降。只有当新连接为了使用线程把它从缓存中移走的时候才会看到下降。(MySQL只有在连接关闭的时候才会把线程加入缓存,也只有在创建新连接的时候 才从缓存中移除线程。) Continue reading

mysql 数据表读锁机制详解

为了给高并发情况下的mysql进行更好的优化,有必要了解一下mysql查询更新时的锁表机制。
一、概述
MySQL有三种锁的级别:页级、表级、行级。
MyISAM和MEMORY存储引擎采用的是表级锁(table-level locking);BDB存储引擎采用的是页面锁(page-level locking),但也支持表级锁;InnoDB存储引擎既支持行级锁(row-level locking),也支持表级锁,但默认情况下是采用行级锁。
MySQL这3种锁的特性可大致归纳如下:
表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。
行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。

从上述特点可见,很难笼统地说哪种锁更好,只能就具体应用的特点来说哪种锁更合适!仅从锁的角度来说:表级锁更适合于以查询为主,只有少量按索引条件更新数据的应用,如Web应用;而行级锁则更适合于有大量按索引条件并发更新少量不同数据,同时又有并发查询的应用,如一些在线事务处理(OLTP)系统。
二、MyISAM表锁
MyISAM存储引擎只支持表锁,是现在用得最多的存储引擎。
1、查询表级锁争用情况
可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺: Continue reading

MySQL聚簇索引&聚集索引&索引组织表my

MySQL聚簇索引&聚集索引&索引组织表

http://www.cnblogs.com/hustcat/archive/2009/10/28/1591648.html

聚簇索引和聚集索引(Clustered Index)

说起索引,不能不说B+树。

引用:http://blog.codinglabs.org/articles/theory-of-mysql-index.html

MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。提取句子主干,就可以得到索引的本质:索引是数据结构。

我们知道,数据库查询是数据库的最主要功能之一。我们都希望查询数据的速度能尽可能的快,因此数据库系统的设计者会从查询算法的角度进行优化。最基本的查询算法当然是顺序查找(linear search),这种复杂度为O(n)的算法在数据量很大时显然是糟糕的,好在计算机科学的发展提供了很多更优秀的查找算法,例如二分查找(binary search),二叉树查找(binary tree search)等。如果稍微分析一下会发现,每种查找算法都只能应用于特定的数据结构之上,例如二分查找要求被检索数据有序,而二叉树查找只能应用于二叉查找树上,但是数据本身的组织结构不可能完全满足各种数据结构(例如,理论上不可能同时将两列都按顺序进行组织),所以,在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法。这种数据结构,就是索引。

MySQL就普遍使用B+Tree实现其索引结构。

聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。具体的细节依赖于其实现方式,但InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。

mysq_index

当表有聚簇索引时,他的数据行实际上存放在索引的叶子页(leaf page)中。术语 “聚簇”表示数据行和相邻的键值紧凑地存储在一起(这并非总成立)。

因为无法同时把数据行存放在两个不同的地方,索引一个表只能有一个聚簇索引。

mysql_index

注:叶子页面包含完整的元组,而内节点页面仅包含索引的列(索引的列为整型)。一些DBMS允许用户指定聚簇索引,但是MySQL的存储引擎到目前为止都不支持。InnoDB对主键建立聚簇索引。如果你不指定主键,InnoDB会用一个具有唯一且非空值的索引来代替。如果不存在这样的索引,InnoDB会定义一个隐藏的主键,然后对其建立聚簇索引。一般来说,DBMS都会以聚簇索引的形式来存储实际的数据,它是其它二级索引的基础。

索引组织表(Index Organized Table, IOT)

其实和聚簇索引说的是一个意思。

索引组织表(Index organized table, IOT)就是存储在一个索引结构中的表。与堆组织表无序存储不同的是,IOT中的数据按主键存储和排序。

相比堆组织表,索引组织表能够节省一部分空间,因为使用堆组织表时,我们必须为表和表的主键上的索引分别留出空间。而IOT则可以省去主键索引的开销,因为数据就是按顺序存储的,可以当做索引使。换句话说,如果你只会通过一个表的主键来访问这个表,这个表就适合创建成索引组织表。

举例:

1.一个客户有很多地址信息,客户是一个表,客户地址信息是另外一个表。读取一个客户地址信息的时候,如果这个客户的所有地址信息都存放在相邻的地方,读取速度就会快一些。这个时候,客户地址信息表适合创建成IOT。

2. 经常查看一支股票的最近几天的信息,股票信息一般是千万级别的数据,如果能够把最近几天的信息存放在一起就会快很多。

=======================END=======================

转:http://my.oschina.net/xinxingegeya/blog/474895

从MyISAM转到InnoDB需要注意什么

当前,绝大多数业务场景用InnoDB已经完全能搞定了,越来越多的业务从MyISAM转向InnoDB引擎,那么有哪些注意事项呢? 分析 当了解完两种引擎的不同之处,很轻松的就能知道有哪些关键点了。

总的来说,从MyISAM转向InnoDB的注意事项有:

1、MyISAM的主键索引中,可以在非第一列(非第一个字段)使用自增列,而InnoDB的主键索引中包含自增列时,必须在最前面;这个特性在discuz论坛中,被设计用于“抢楼”功能,因此,若有类似的业务,则无法将该表从MyISAM转成InnoDB,需要自行变通实现(我们则是将其改到Redis中实现);
2、不带条件频繁统计全表总记录数时(SELECT COUNT(*) FROM TAB),InnoDB相对较慢,而MyISAM则飞快;不过,如果是基于索引条件的统计,则二者相差不大;
3、InnoDB在5.6以前不支持全文索引,不过这个相信无所谓,没什么人会在MySQL里直接跑全文索引,尤其是对中文的全文索引(前阵子有开发同学提需求直接被我否了),确实有需要的话,可以采用Sphinx、Lucene等其他方案实现;
4、一次性导入大量数据并且后续还要进行加工处理的,可以先导入到MyISAM引擎表中,经过一通加工处理完后,再导入InnoDB表(我曾经在业务中用此方法提高数据批量导入及处理效率);
5、InnoDB不支持LOAD TABLE FROM MASTER语法(不过应该也很少人使用吧);

从MyISAM转成InnoDB可以享受的好处则有: Continue reading

brew安装mysql为系统服务

默认情况下,使用brew install mysql 安装完mysql以后,需要手动启动mysql。为了方便以后使用,这里将mysql安装成系统服务。

brew info mysql 已经给了.plist文件,只需要load一下就可以,plist文件名不一定是com.mysql.mysqld.plist, 可以先到 `brew --prefix mysql` 目录看下。具体如下:

mkdir -p ~/Library/LaunchAgents
cp `brew --prefix mysql`/com.mysql.mysqld.plist ~/Library/LaunchAgents/
launchctl load -w ~/Library/LaunchAgents/com.mysql.mysqld.plist

其实http://stackoverflow.com/questions/8014500/autostart-mysql-on-boot-from-terminal这里有说。

mkdir -p ~/Library/LaunchAgents
cp `brew --prefix mysql`/*mysql*.plist ~/Library/LaunchAgents/
launchctl load -w ~/Library/LaunchAgents/*mysql*.plist

mysql中kill掉所有锁表的进程

很多时候由于异常或程序错误会导致个别进程占用大量系统资源,需要结束这些进程,通常可以使用以下命令Kill进程:

mysql中kill掉所有锁表的进程

3点钟刚睡下, 4点多, 同事打电话告诉我用户数据库挂掉了. 我起床看一下进程列表.

mysql>show process list;

出来哗啦啦好几屏幕的, 没有一千也有几百条, 查询语句把表锁住了, 赶紧找出第一个Locked的thread_id, 在mysql的shell里面执行.

mysql>kill thread_id;

kill掉第一个锁表的进程, 依然没有改善. 既然不改善, 咱们就想办法将所有锁表的进程kill掉吧, 简单的脚本如下.

#!/bin/bash
mysql -u root -e "show processlist" | grep -i "Locked" >> locked_log.txt for line in `cat locked_log.txt | awk '{print $1}'`
do
echo "kill $line;" >> kill_thread_id.sql
done

现在kill_thread_id.sql的内容像这个样子

kill 66402982;
kill 66402983;
kill 66402986;
kill 66402991;
.....

好了, 我们在mysql的shell中执行, 就可以把所有锁表的进程杀死了.

mysql>source kill_thread_id.sql

当然了, 也可以一行搞定

for id in `mysqladmin processlist | grep -i locked | awk '{print $1}'`
do
mysqladmin kill ${id}
done

如果要查看阻塞语句的话,只需要将上面的"Locked"修改成"Sending"即可。

转自:http://blog.csdn.net/jiangguilong2000/article/details/12198583

检查mysql数据库是否存在坏表

shell脚本检测和检查mysql数据库是否存在坏表

此脚本的主要用途是检测mysql服务器上所有的数据库或者单独数据库中的坏表,适用于RHEL/Centos系列

check_mysql

#!/bin/bash
#此脚本的主要用途是检测mysql服务器上所有的db或者单独db中的坏表
#变量说明 pass mysql账户口令 name mysql账号名称 data_path mysql目录路径 directory_list 目录列表 file_list文件列表 db_name 数据库名称 repair_count单库中待修复的表总数
#变量说明 repair_count_all所有库中待修复的表总数 mysql_version mysql版本 _file_name 数据表名称

echo -e "此脚本的主要用途是检测mysql服务器上所有的数据库或者单独数据库中的坏表\n\n"
pass=123456
name=root

read -p "输入mysql存储路径: "  choose
data_path=$choose
unset choose

read -p "请输入mysql命令路径: " mysql_version
#标准输入、标准输出、标准错误输出的文件标示符 由 0、1、2标识
read -p "请选择是检查服务器上所有数据库还是指定的数据库 1:检查全部数据库 2:只检查指定数据库: " choose
if [ $choose == 1 ]; then
  cd $data_path
  for directory_list in $(ls)
    do
      if [ -d $directory_list ];then
          if [ "mysql" != "${directory_list}" -a "test" != "${directory_list}" ];then
              cd ${directory_list}
              echo "当前检查数据库为:"${directory_list}
              for file_list in $(ls *.frm)
              do
                _file_name=${file_list%.frm}
                echo -e "\n" >> /tmp/check_table_all.log
                ${mysql_version} -h 127.0.0.1 -u${name} -p${pass} -e "check table "${directory_list}.${_file_name} 2>&1 >> /tmp/check_table_all.log
              done
              cd ..
          fi
      fi
  done
             cat /tmp/check_table_all.log | grep "Table is marked as crashed" > /tmp/check_table_repair.log
             repair_count_all=` awk 'END{print NR}' /tmp/check_table_repair.log `
             echo -e "所有数据库用有${repair_count_all}张表需要修复!"
             more  /tmp/check_table_repair.log
else
  read -p "请输入要检查的数据库名称: " db_name
  cd ${data_path}/${db_name}
  for file_list in $(ls *.frm)
    do
      _file_name=${file_list%.frm}
      echo -e "\n" >> /tmp/check_${db_name}.log
      ${mysql_version} -h 127.0.0.1 -u${name} -p${pass} -e "check table "${db_name}.$_file_name 2>&1 >> /tmp/check_${db_name}.log
    done
    cat /tmp/check_${db_name}.log | grep "Table is marked as crashed" > /tmp/check_${db_name}_Repair.log
    repair_count=`awk 'END{print NR}' /tmp/check_${db_name}_Repair.log`
    echo -e "${db_name}中共有${repair_count}个表需要修复!\n "
    more /tmp/check_${db_name}_Repair.log
fi

 

"mysql 错误log InnoDB: Error: pthread_create returned 11"的解决办法

最近mysql总是死掉,登录服务器查看,mysql没有启动,启动mysql数据库。

service mysqld start

网站访问正常。

查看服务器没有重启记录,也没有其他用户登录服务器停止mysql数据库。

查看mysql log,有如下提示:

140116 19:38:39 mysqld_safe Number of processes running now: 0

140116 19:38:39 mysqld_safe mysqld restarted

140116 19:38:45  InnoDB: Initializing buffer pool, size = 8.0M

140116 19:38:45  InnoDB: Completed initialization of buffer pool

InnoDB: Error: pthread_create returned 11

140116 19:38:46 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

上网google了下。

临时解决方法:

# ulimit -s unlimited

该方法在系统重启,或者重新登录后将失效。

另外一种方法:

修改 /etc/profile 文件,添加

ulimit -s unlimited