SQL语句长度限制MYSQL配置

mysql配置参数汇总

mysql是一个关系型数据库管理系统，由瑞典mysql ab公司开发，目前属于oracle公司。mysql是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。专职优化、域名注册、、网站空间、虚拟主机、服务器托管、vps主机、服务器租用的中国信息港来为你详细介绍！

1. back_log

指定mysql可能的连接数量。当mysql主线程在很短的时间内得到非常多的连接请求，该参数就起作用，之后主线程花些时间（尽管很短）检查连接并且启动一个新线程。

back_log参数的值指出在mysql暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中。如果系统在一个短时间内有很多连接，则需要增大该参数的值，该参数值指定到来的tcp/ip连接的侦听队列的大小。不同的操作系统在这个队列大小上有它自己的限制。试图设定back_log高于你的操作系统的限制将是无效的。

当观察mysql进程列表，发现大量 264084 | unauthenticated user |

| null | connect | null | login | null 的待连接进程时，就要加大 back_log 的值。back_log默认值为50。

2. basedir

mysql主程序所在路径，即：--basedir参数的值。

3. bdb_cache_size

分配给bdb类型数据表的缓存索引和行排列的缓冲区大小，如果不使用dbd类型数据表，则应该在启动mysql时加载 --skip-bdb 参数以避免内存浪费。

_log_buffer_size

分配给bdb类型数据表的缓存索引和行排列的缓冲区大小，如果不使用dbd类型数据表，则应该将该参数值设置为0，或者在启动mysql时加载 --skip-bdb 参数以避免内存浪费。

_home

参见 --bdb-home 选项。

6. bdb_max_lock

指定最大的锁表进程数量（默认为10000），如果使用bdb类型数据表，则可以使用该参数。如果在执行大型事物处理或者查询时发现 bdb: lock table is out of available locks

or got error 12 from ... 错误，则应该加大该参数值。

7. bdb_logdir

指定使用bdb类型数据表提供服务时的日志存放位置。即为 --bdb-logdir 的值。

8. bdb_shared_data

如果使用 --bdb-shared-data 选项则该参数值为on。

9. bdb_tmpdir

bdb类型数据表的临时文件目录。即为 --bdb-tmpdir 的值。

10. binlog_cache_size

为binary log指定在查询请求处理过程中sql 查询语句使用的缓存大小。如果频繁应用于大量、复杂的sql表达式处理，则应该加大该参数值以获得性能提升。

11. bulk_insert_buffer_size

指定 myisam 类型数据表表使用特殊的树形结构的缓存。使用整块方式(bulk)能够加快插入操作( insert ... select, insert ... values (...), (...), ..., 和 load data infile)

的速度和效率。该参数限制每个线程使用的树形结构缓存大小，如果设置为0则禁用该加速缓存功能。注意：该参数对应的缓存操作只能用户向非空数据表中执行插入操作！默认值为

8mb。

12. character_set

mysql的默认字符集。

13. character_sets

mysql所能提供支持的字符集。

14. concurrent_inserts

如果开启该参数，mysql则允许在执行 select 操作的同时进行 insert 操作。如果要关闭该参数，可以在启动 mysqld 时加载 --safe 选项，或者使用 --skip-new 选项。默认为on。

15. connect_timeout

指定mysql服务等待应答一个连接报文的最大秒数，超出该时间，mysql向客户端返回

bad handshake。

16. datadir

指定数据库路径。即为 --datadir 选项的值。

17. delay_key_write

该参数只对 myisam 类型数据表有效。有如下的取值种类：

off: 如果在建表语句中使用 create table ... delayed_key_writes，则全部忽略

delayed_key_writes；

on: 如果在建表语句中使用 create table ... delayed_key_writes，则使用该选项（默认）；

all: 所有打开的数据表都将按照 delayed_key_writes 处理。

如果 delayed_key_writes 开启，对于已经打开的数据表而言，在每次索引更新时都不刷新带有

delayed_key_writes 选项的数据表的key buffer，除非该数据表关闭。该参数会大幅提升写入键值的速

度。如果使用该参数，则应该检查所有数据表：myisamchk --fast --force。

d_insert_limit

在插入delayed_insert_limit行后，insert delayed处理模块将检查是否有未执行的select语句。如果有，在继续处理前执行允许这些语句。

19. delayed_insert_timeout

一个insert delayed线程应该在终止之前等待insert语句的时间。

20. delayed_queue_size

为处理insert delayed分配的队列大小（以行为单位）。如果排队满了，任何进行insert

delayed的客户必须等待队列空间释放后才能继续。

21. flush

在启动mysql时加载 --flush 参数打开该功能。

22. flush_time

如果该设置为非0值，那么每flush_time秒，所有打开的表将被关，以释放资源和sync到磁盘。注意：只建议在使用 windows9x/me 或者当前操作系统资源严重不足时才使用该参数！

23. ft_boolean_syntax

搜索引擎维护员希望更改允许用于逻辑全文搜索的操作符。这些则由变量

ft_boolean_syntax 控制。

24. ft_min_word_len

指定被索引的关键词的最小长度。注意：在更改该参数值后，索引必须重建！

25. ft_max_word_len

指定被索引的关键词的最大长度。注意：在更改该参数值后，索引必须重建！

26. ft_max_word_len_for_sort

指定在使用repair, create index, or alter table等方法进行快速全文索引重建过程中所能使用的关键词的最大长度。超出该长度限制的关键词将使用低速方式进行插入。加大该参数的值，mysql将会建立更大的临时文件（这会减轻cpu负载，但效率将取决于磁盘i/o效率），并且在一个排序取内存放更少的键值。

27. ft_stopword_file

从 ft_stopword_file 变量指定的文件中读取列表。在修改了 stopword 列表后，必须重建 fulltext 索引。

28. have_innodb

yes: mysql支持innodb类型数据表； disable: 使用 --skip-innodb 关闭对innodb类型数据表的支持。

29. have_bdb

yes: mysql支持伯克利类型数据表； disable: 使用 --skip-bdb 关闭对伯克利类型数据表的支持。

30. have_raid

yes: 使mysql支持raid功能。

31. have_openssl

yes: 使mysql支持ssl加密协议。

32. init_file

指定一个包含sql查询语句的文件，该文件在mysql启动时将被加载，文件中的sql语句也会被执行。

33. interactive_timeout

服务器在关上它前在一个交互连接上等待行动的秒数。一个交互的客户被定义为对mysql_real_connect()使用client_interactive选项的客户。也可见wait_timeout。

34. join_buffer_size

用于全部联合(join)的缓冲区大小(不是用索引的联结)。缓冲区对2个表间的每个全部联结分配一次缓冲区，当增加索引不可能时，增加该值可得到一个更快的全部联结。（通常得到快速联结的最佳方法是增加索引。）

35. key_buffer_size

用于索引块的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)，到你能负担得起那样多。如果你使它太大，系统将开始变慢慢。必须为os文件系统缓存留下一些空间。为了在写入多个行时得到更多的速度。

36. language

用户输出报错信息的语言。

37. large_file_support

开启大文件支持。

38. locked_in_memory

使用 --memlock 将mysqld锁定在内存中。

39. log

记录所有查询操作。

40. log_update

开启update log。

41. log_bin

开启 binary log。

42. log_slave_updates

如果使用链状同步或者多台slave之间进行同步则需要开启此参数。

43. long_query_time

如果一个查询所用时间超过该参数值，则该查询操作将被记录在slow_queries中。

44. lower_case_table_names

1: mysql总使用小写字母进行sql操作；

0: 关闭该功能。

注意：如果使用该参数，则应该在启用前将所有数据表转换为小写字母。

45. max_allowed_packet

一个查询语句包的最大尺寸。消息缓冲区被初始化为net_buffer_length字节，但是可在需要时增加到max_allowed_packet个字节。该值太小则会在处理大包时产生错误。如果使用大的blob列，必须增加该值。

46. net_buffer_length

通信缓冲区在查询期间被重置到该大小。通常不要改变该参数值，但是如果内存不足，可以将它设置为查询期望的大小。（即，客户发出的sql语句期望的长度。如果语句超过这个长度，缓冲区自动地被扩大，直到max_allowed_packet个字节。）

47. max_binlog_cache_size

指定binary log缓存的最大容量，如果设置的过小，则在执行复杂查询语句时mysql会出错。

48. max_binlog_size

指定binary log文件的最大容量，默认为1gb。

49. max_connections

允许同时连接mysql服务器的客户数量。如果超出该值，mysql会返回too many

connections错误，但通常情况下，mysql能够自行解决。

50. max_connect_errors篇二：浅谈mysql配置优化和sql语句优化

浅谈mysql配置优化和sql语句优化

做优化，我在这里引用淘宝系统分析师蒋江伟的一句话：只有勇于承担，才能让人有勇气，有承担自己的错误的勇气。有承担错误的勇气，就有去做事得勇气。无论做什么事，只要是对的，就要去做，勇敢去做。出了错误，承担错误，改正错误，这样的人没有人会去责怪。

很久之前就想写一篇关于mysql优化方面的文章了，忙于工作，在着也比较懒散。现在网上mysql优化方面的帖子很多，也不乏精品。很早听一dba前辈说过:mysql的优化，三分配置的优化，七分sql语句的优化。之前不是很理解，但接触到高并发大数据的时候，这句话的含义尤为突出，甚至可以说一分配置的优化，九分语句的优化。

mysql的优化，一般分为配置的优化、sql语句的优化、表结构的优化、索引的优化，这里着重说前两点。

一、配置的优化

配置的优化其实包含两个方面的：操作系统内核的优化和mysql配置文件的优化

1、系统内核的优化对专用的mysql服务器来说，无非是内存实用、连接数、超时处理、tcp处理等方面的优化，根据自己的硬件配置来进行优化，这里不多讲；

2、mysql配置的优化，一般来说包含：io处理的常用参数、最大连接数设置、缓存使用参数的设置、慢日志的参数的设置、innodb相关参数的设置等，如果有主从关系在设置主从

同步的相关参数即可，网上的相关配置文件很多，大同小异，常用的设置大多修改这些差不多就够用了。

以下是我的一个mysql配置文件，仅供参考：

###############################################

[mysqld]

skip-name-resolve

server-id = 1

bind-address = 0.0.0.0

port = 3306

datadir = /home/mysql

tmpdir = /tmp

default_storage_engine = innodb

character_set_server = utf8

innodb_file_per_table = 1

innodb_log_file_size = 512m

innodb_log_files_in_group = 4

innodb_rollback_on_timeout = 1

slow_query_log = 1

slow_query_log_file =/var/log/mysql/

long_query_time = 1

#log-queries-not-using-indexes#这个参数不安全，说是记录没有用到索引的语句，其实记录的全部的日志，占用大量的io，建议不要打开

#relay_log_recovery=1#这个参数在丛库上一定要加上

query_cache_type = off

query_cache_size = 0

#这两项是禁用缓存，这个使服务器用途而定：写比较多的数据库最好禁用，因为没写一次他要修改缓存中的数据，给数据库带来额外的开销，读比较的可以开启，可以提高查询效率

#一下4个参数是mysql5.6上的新特性

innodb_buffer_pool_dump_at_shutdown = 1 #解释：在关闭时把热数据dump到本地磁盘。 innodb_buffer_pool_dump_now = 1 #解释：采用手工方式把热数据dump到本地磁盘。

innodb_buffer_pool_load_at_startup = 1 #解释：在启动时把热数据加载到内存。

innodb_buffer_pool_load_now = 1 #解释：采用手工方式把热数据加载到内存。

read_buffer_size = 2m

sort_buffer_size = 2m

join_buffer_size = 1m

key_buffer_size = 2g

thread_cache_size = 2048

open_files_limit=65535

innodb_open_files = 8192

max_allowed_packet = 64m

thread_stack = 512k

max_length_for_sort_data = 16k

tmp_table_size = 256m

max_heap_table_size = 256m

max_connections = 4000

max_connect_errors = 30000

innodb_read_io_threads = 8

innodb_write_io_threads = 16

innodb_flush_method = o_direct

innodb_io_capacity = 20000#根据硬盘的情况修改，stat的用100，sas的200，sas做riad10的为400fision-io的可以设置为20000

innodb_buffer_pool_size = 72g#内存的80%

innodb_buffer_pool_instances=18

thread_concurrency=0

innodb_thread_concurrency = 0

innodb_log_buffer_size = 16m

innodb_lock_wait_timeout = 60

innodb_old_blocks_time=1000

innodb_use_native_aio = 1

innodb_purge_threads=1

innodb_change_buffering=inserts

##############################################

二、sql语句的优化

前期的配置优化做完，其实在很长的一段时间内，基本上不用在去优化了。而一条sql使用不当，致使整个数据库故障的情况相信做dba的经常遇到。所以，sql语句的优化和审核才是每个dba的重中之重。

sql语句的优化，一般的基本原则有以下几个方面：

1、 尽量稍作计算

mysql的作用是用来存取数据的，不是做计算的，做计算的话可以用其他方法去实现，mysql做计算是很耗资源的。

2.尽量少 join

mysql 的优势在于简单，但这在某些方面其实也是其劣势。mysql 优化器效率高，但是由于其统计信息的量有限，优化器工作过程出现偏差的可能性也就更多。对于复杂的多表

join，一方面由于其优化器受限，再者在 join 这方面所下的功夫还不够，所以性能表现离

oracle 等关系型数据库前辈还是有一定距离。但如果是简单的单表查询，这一差距就会极小甚至在有些场景下要优于这些数据库前辈。

3.尽量少排序

排序操作会消耗较多的 cpu 资源，所以减少排序可以在缓存命中率高等 io 能力足够的场景下会较大影响 sql的响应时间。

对于mysql来说，减少排序有多种办法，比如：

通过利用索引来排序的方式进行优化

减少参与排序的记录条数

非必要不对数据进行排序

4.尽量避免 select *

在数据量少并且访问量不大的情况下，select * 没有什么影响，但是量级达到一定级别的时候，在执行效率和io资源的使用上，还是有很大关系的，用什么字段取什么字段，减少不必要的资源浪费。

之前遇到过因为一个字段存储的数据比较大，并发高的情况下把网络带宽跑满的情况，造成网站打不开或是打开速度极慢的情况。

5.尽量用 join 代替子查询

虽然 join 性能并不佳，但是和 mysql 的子查询比起来还是有非常大的性能优势。mysql

的子查询执行计划一直存在较大的问题，虽然这个问题已经存在多年，但是到目前已经发布的所有稳定版本中都普遍存在，一直没有太大改善。虽然官方也在很早就承认这一问题，并且承诺尽快解决，但是至少到目前为止我们还没有看到哪一个版本较好的解决了这一问题。

6.尽量少 or

当 where 子句中存在多个条件以“或”并存的时候，mysql 的优化器并没有很好的解决其执行计划优化问题，再加上 mysql 特有的 sql 与 storage 分层架构方式，造成了其性能比较低下，很多时候使用 union all 或者是union(必要的时候)的方式来代替“or”会得到更好的效果。

7.尽量用 union all 代替 union

union 和 union all 的差异主要是前者需要将两个(或者多个)结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的 cpu 运算，加大资源消耗及延迟。所以当我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候，尽量使用 union all 而不是 union。

8.尽量早过滤

这一优化策略其实最常见于索引的优化设计中(将过滤性更好的字段放得更靠前)。

在 sql 编写中同样可以使用这一原则来优化一些 join 的 sql。比如我们在多个表进行分页数据查询的时候，我们最好是能够在一个表上先过滤好数据分好页，然后再用分好页的结果集与另外的表 join，这样可以尽可能多的减少不必要的 io 操作，大大节省 io 操作所消耗的时间。篇三：mysql常用基本sql语句小结

mysql:常用基本sql语句小结（转载）

sql分类：

ddl—数据定义语言(create，alter，drop，declare)

dml—数据操纵语言(select，delete，update，insert)

首先,简要介绍基础语句：

1、说明：创建数据库

create database database-name

2、说明：删除数据库

drop database dbname

3、说明：备份sql server

--- 创建 备份数据的 device

use master

exec sp_addumpdevice ?disk?, ?testback?, ?c:mssql7backupmynwind_? --- 开始 备份

backup database pubs to testback

4、说明：创建新表

create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)

根据已有的表创建新表：

a：create table tab_new like tab_old (使用旧表创建新表)

b：create table tab_new as select col1,col2„ from tab_old definition only

5、说明：

删除新表：drop table tabname

6、说明：

增加一个列：alter table tabname add column col type

注：列增加后将不能删除。db2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。

7、说明：

添加主键：alter table tabname add primary key(col)

说明：

删除主键：alter table tabname drop primary key(col)

8、说明：

创建索引：create [unique] index idxname on tabname(col„.)

删除索引：drop index idxname

注：索引是不可更改的，想更改必须删除重新建。

9、说明：

创建视图：create view viewname as select statement

删除视图：drop view viewname

10、说明：几个简单的基本的sql语句

选择：select * from table1 where 范围

插入：insert into table1(field1,field2) values(value1,value2)

删除：delete from table1 where 范围

更新：update table1 set field1=value1 where 范围

查找：select * from table1 where field1 like ?%value1%? ---like的语法很精妙，查资料!

排序：select * from table1 order by field1,field2 [desc]

总数：select count * as totalcount from table1

求和：select sum(field1) as sumvalue from table1

平均：select avg(field1) as avgvalue from table1

最大：select max(field1) as maxvalue from table1

最小：select min(field1) as minvalue from table1

11、说明：几个高级查询运算词

a： union 运算符

union 运算符通过组合其他两个结果表（例如 table1 和 table2）并消去表中任何重复行而派生出一个结果表。当 all 随 union 一起使用时（即 union

all），不消除重复行。两种情况下，派生表的每一行不是来自 table1 就是来自 table2。

b： except 运算符

except 运算符通过包括所有在 table1 中但不在 table2 中的行并消除所有重复行而派生出一个结果表。当 all 随 except 一起使用时 (except all)，不消除重复行。

c： intersect 运算符

intersect 运算符通过只包括 table1 和 table2 中都有的行并消除所有重复行而派生出一个结果表。当 all 随 intersect 一起使用时 (intersect all)，不消除重复行。

注：使用运算词的几个查询结果行必须是一致的。

12、说明：使用外连接

a、left outer join：

左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。 sql:

select a.a, a.b, a.c, b.c, b.d, b.f from a left out join b on a.a = b.c b：right

outer join:

右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。 c：full outer join：

全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。 其次，大家来看一些不错的sql语句

1、说明：复制表(只复制结构,源表名：a 新表名：b) (access可用)

法一：select * into b from a where 1<>1

法二：select top 0 * into b from a

2、说明：拷贝表(拷贝数据,源表名：a 目标表名：b) (access可用)

insert into b(a, b, c) select d,e,f from b;

3、说明：跨数据库之间表的拷贝(具体数据使用绝对路径) (access可用)

insert into b(a, b, c) select d,e,f from b in ?具体数据库? where 条件

例子：..from b in ?&h(.)& &? where..

4、说明：子查询(表名1：a 表名2：b)

select a,b,c from a where a in (select d from b ) 或者: select a,b,c from a where

a in (1,2,3)

5、说明：显示文章、提交人和最后回复时间

select ,me,e from table a,(select max(adddate) adddate

from table where =) b

6、说明：外连接查询(表名1：a 表名2：b)

select a.a, a.b, a.c, b.c, b.d, b.f from a left out join b on a.a = b.c

7、说明：在线视图查询(表名1：a )

select * from (select a,b,c from a) t where t.a > 1;

8、说明：between的用法,between限制查询数据范围时包括了边界值,not between不包括

select * from table1 where time between time1 and time2

select a,b,c, from table1 where a not between 数值1 and 数值2

9、说明：in 的使用方法

select * from table1 where a [not] in (?值1?,?值2?,?值4?,?值6?)

10、说明：两张关联表，删除主表中已经在副表中没有的信息

delete from table1 where not exists ( select * from table2 where

1=1 )

11、说明：四表联查问题：

select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner

join d on a.a=d.d where .....

12、说明：日程安排提前五分钟提醒

sql: select * from 日程安排 where datediff(?minute?,f开始时间,getdate())>5

13、说明：一条sql 语句搞定数据库分页

select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段

14、说明：前10条记录

select top 10 * form table1 where 范围

15、说明：选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的

用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)

select a,b,c from tablename ta where a=(select max(a) from tablename tb where

tb.b=ta.b)

16、说明：包括所有在 tablea 中但不在 tableb和tablec 中的行并消除所有重复行而派生出一个结果表

(select a from tablea ) except (select a from tableb) except (select a from tablec)

17、说明：随机取出10条数据

select top 10 * from tablename order by newid()

18、说明：随机选择记录

select newid()

19、说明：删除重复记录

delete from tablename where id not in (select max(id) from tablename group by

col1,col2,...)

20、说明：列出数据库里所有的表名

select name from sysobjects where type=?u?

21、说明：列出表里的所有的

select name from syscolumns where id=object_id(?tablename?)

22、说明：列示type、vender、pcs字段，以type字段排列，case可以方便地实现多重选择，类似select 中的case。