DBA告诉你必须掌握的mysql知识：参数详解，搜索引擎、慢查询介绍

这篇文章主要讲解Mysql数据库参数详解(my.cnf)，有需要的老铁可以参考~~

[mysqld] //服务器端配置

datadir=/data/mysql //数据存储目录

socket=/var/lib/mysql/mysql.sock //socket通信文件

user=mysql //使用mysql用户启动

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0 //是否支持快捷方式

log-bin=mysql-bin //开启bin-log日志

server-id = 1 //mysql服务ID

auto_increment_offset=1

auto_increment_increment=2

(mysql中有自增长字段，在做数据库的主主同步时需要设置自增长的两个相关配置：

auto_increment_offset和auto_increment_increment。

auto_increment_offset表示自增长字段从那个数开始，他的取值范围是1 -65535

auto_increment_increment表示自增长字段每次递增的量，其默认值是1，取值范围是1-65535

在主主同步配置时，需要将两台服务器的auto_increment_increment增长量都配置为2，而要把auto_increment_offset分别配置为1和2,这样才可以避免两台服务器同时做更新时自增长字段的值之间发生冲突。)

[mysqld_safe] //mysql服务安全启动配置

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

key_buffer_size 指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。一般为内存的50%

show variables like 'key_buffer_size'；

max_connections = 3000

# 每个客户端连接最大的错误允许数量,如果达到了此限制，这个客户端将会被MySQL服务阻止直到执行了"FLUSH HOSTS"或者服务重启.

innodb_buffer_pool_size

对于InnoDB表来说，innodb_buffer_pool_size的作用就相当于key_buffer_size对于MyISAM表的作用一样。InnoDB使用该参数指定大小的内存来缓冲数据和索引。

对于单独的MySQL数据库服务器，最大可以把该值设置成物理内存的80%。

内存32G，24G

根据MySQL手册，对于2G内存的机器，推荐值是1G（50%）。

basedir = path //使用给定目录作为根目录(安装目录)。

datadir = path //从给定目录读取数据库文件。

pid-file = filename //为mysqld程序指定一个存放进程ID的文件(仅适用于UNIX/Linux系统);

[mysqld]

socket = /tmp/mysql.sock //为MySQL客户程序与服务器之间的本地通信指定一个套接字文件(Linux下默认是/var/lib/mysql/mysql.sock文件)

port = 3306 //指定MsSQL侦听的端口

key_buffer = 384M //key_buffer是用于索引块的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)。索引块是缓冲的并且被所有的线程共享，key_buffer的大小视内存大小而定。

table_cache = 512 //为所有线程打开表的数量。增加该值能增加mysqld要求的文件描述符的数量。可以避免频繁的打开数据表产生的开销

sort_buffer_size = 2M //每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY或GROUP BY操作。注意：该参数对应的分配内存是每连接独占！如果有100个连接，那么实际分配的总共排序缓冲区大小为100×6=600MB

read_buffer_size = 2M //读查询操作所能使用的缓冲区大小。和sort_buffer_size一样，该参数对应的分配内存也是每连接独享。

query_cache_size = 32M //指定MySQL查询结果缓冲区的大小

read_rnd_buffer_size = 8M //改参数在使用行指针排序之后，随机读用的。

myisam_sort_buffer_size = 64M //MyISAM表发生变化时重新排序所需的缓冲

thread_concurrency = 8 //最大并发线程数，取值为服务器逻辑CPU数量×2，如果CPU支持H.T超线程，再×2

thread_cache = 8 //缓存可重用的线程数

skip-locking //避免MySQL的外部锁定，减少出错几率增强稳定性。

[mysqldump]

max_allowed_packet =16M //服务器和客户端之间最大能发送的可能信息包

MySQL引擎MyISAM与InnoDB

1）MyISAM引擎:

默认表类型，它是基于传统的ISAM类型,ISAM是Indexed Sequential Access Method (有索引的顺序访问方法) 的缩写,它是存储记录和文件的标准方法.不是事务安全的，而且不支持外键，如果执行大量的select， MyISAM比较适合。

2）InnoDB引擎：

支持事务安全的引擎，支持外键、行锁、事务是他的最大特点。Innodb最初是由innobase Oy公司开发，2005年10月由oracle公司并购，目前innodb采用双授权，一个是GPL授权，一个是商业授权。如果有大量的update和insert，建议使用InnoDB,特别是针对多个并发和QPS较高的情况。

3）总体来讲：

InnoDB和MyISAM是在使用MySQL最常用的两个表类型，各有优缺点，视具体应用而定。基本的差别为：MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持。MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快，但是不提供事务支持，而InnoDB提供事务支持已经外部键等高级数据库功能。

4）MyISAM适合：(1)做很多count 的计算；(2)插入不频繁，查询非常频繁；(3)没有事务。

1、InnoDB适合：(1)可靠性要求比较高，或者要求事务；(2)表更新和插入都相当的频繁，并且表锁定的机会比较大的情况。

2、如何查看数据库引擎：

一般情况下，MySQL会默认提供多种存储引擎，可以通过下面的查看:

1）查看MySQL现在已提供什么存储引擎: mysql> show engines;

2）查看MySQL当前默认的存储引擎: mysql> show variables like '%storage_engine%';

3）查看某个表用了什么引擎(在显示结果里参数engine后面的就表示该表当前用的存储引擎): mysql> show create table 表名;

1

查看表使用的引擎：show create table test_t0;

2

5）修改MySQL表空间引擎：

设置InnoDB为默认引擎：在配置文件my.cnf中的 [mysqld] 下面加入default-storage-engine=INNODB 然后重启mysqld服务即可。

可以修改表引擎方法如下：

alter table t1 engine=myisam;

alter table t1 engine=innodb;

如果添加innodb引擎报错，需要执行如下命令：

删除/mysql/data目录下的ib_logfile0,ib_logfile1文件即可。

MySQL索引及慢查询案例讲解

MySQL索引用来快速地寻找那些具有特定值的记录，所有MySQL索引都以B-树的形式保存。如果没有索引，执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录，直至找到符合要求的记录。表里面的记录数量越多，这个操作的代价就越高。

如果作为搜索条件的列上已经创建了索引，MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。如果表有1000个记录，通过索引查找记录至少要比顺序扫描记录快100倍。

常见索引类型：

1）normal：表示普通索引

2）unique：表示唯一的，不允许重复的索引，如果该字段信息保证不会重复例如身份证号用作索引时，可设置为unique

3）full text: 表示 全文搜索的索引。 FULLTEXT 用于搜索很长一篇文章的时候，效果最好。用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。

总结，索引的类别由建立索引的字段内容特性来决定，通常normal最常见。

创建索引命令：

ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。（我这里以t1表为例来讲解）

ALTER TABLE t1 ADD INDEX index_name (name)

ALTER TABLE t1 ADD UNIQUE (name)

ALTER TABLE t1 ADD PRIMARY KEY (name)

或者使用create创建

CREATE INDEX index_name ON t1 (name)

CREATE UNIQUE INDEX index_name ON t1 (name)

删除索引

DROP INDEX index_name ON talbe_name

ALTER TABLE t1 DROP INDEX index_name

ALTER TABLE t1 DROP PRIMARY KEY;

查看索引

show index from t1;

show keys from t1;

MySQL慢查询：

慢查询对于跟踪有问题的查询很有用，可以分析出当前程序里那些Sql语句比较耗费资源。

1）查看当前mysql慢查询

show variables like "%slow%";

3

| slow_launch_time | 2 | 超过2秒定义为慢查询。

| slow_query_log | OFF | 慢查询关闭状态。

| slow_query_log_file |
/data/mysql/var/db-Test2-slow.log | 慢查询日志的文件。

2）开启慢查询日志方法

Mysql数据库里执行：set global slow_query_log=on;

在my.cnf中添加，如下：

log-slow-queries =
/data/mysql/var/db-Test2-slow.log #日志目录。long_query_time = 0.1 #记录下查询时间查过1秒。
log-queries-not-using-indexes #表示记录下没有使用索引的查询。

3）mysqldumpslow分析日志

可用mysql提供的mysqldumpslow，使用很简单，参数可-help查看

-s：排序方式。

c ， t ， l ， r 表示记录次数、时间、查询时间的多少、返回的记录数排序；

# ac ， at ， al ， ar 表示相应的倒叙；

# -t：返回前面多少条的数据；

# -g：包含什么，大小写不敏感的；

mysqldumpslow -s r -t 10 /data/mysql/var/db-Test2-slow.log