MySQL调优

all：显示所有性能信息   show profile all for query n

block io：显示块io操作的次数 show profile block io for query n

context switches：显示上下文切换次数，被动和主动 show profile context switches for query n

cpu：显示用户cpu时间、系统cpu时间   show profile cpu for query n

IPC：显示发送和接受的消息数量   show profile ipc for query n

page faults：显示页错误数量   show profile page faults for query n

source：显示源码中的函数名称与位置   show profile source for query n

swaps：显示swap的次数   show profile swaps for query n

使用方法：

此工具默认是禁用的，可以通过服务器变量在绘画级别动态的修改
set profiling=1;
当设置完成之后，在服务器上执行的所有语句，都会测量其耗费的时间和其他一些查询执行状态变更相关的数据。
select * from tuser;
在mysql的命令行模式下只能显示两位小数的时间，可以使用如下命令查看具体的执行时间
show profiles;
执行如下命令可以查看详细的每个步骤的时间：
show profile for query 1;

2、使用show processlist查看连接的线程个数，来观察是否有大量线程处于不正常的状态或者其他不正常的特征

属性介绍：

id表示session id；user表示操作的用户；host表示操作的主机；db表示操作的数据库

command表示当前状态：{ sleep：线程正在等待客户端发送新的请求；query：线程正在执行查询或正在将结果发送给客户端；locked：在mysql的服务层，该线程正在等待表锁；analyzing and statistics：线程正在收集存储引擎的统计信息，并生成查询的执行计划；Copying to tmp table：线程正在执行查询，并且将其结果集都复制到一个临时表中；sorting result：线程正在对结果集进行排序；sending data：线程可能在多个状态之间传送数据，或者在生成结果集或者向客户端返回数据 }

info表示详细的sql语句；time表示相应命令执行时间；state表示命令执行状态

二、数据类型优化

1、数据类型优化

最小的通常最好、尽量避免null,尽量使用满足需求的最小数据类型

1、整型比字符操作代价更低，因为字符集和校对规则是字符比较比整型比较更复杂，
2、使用mysql自建类型而不是字符串来存储日期和时间
3、用整型存储IP地址

整数类型:可以使用的几种整数类型：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT分别使用8，16，24，32，64位存储空间。

字符类型：1、char长度固定，即每条数据占用等长字节空间；最大长度是255个字符，适合用在身份证号、手机号等定长字符串
2、varchar可变程度，可以设置最大长度；最大空间是65535个字节，适合用在长度可变的属性
3、text不设置长度，当不知道属性的最大长度时，适合用text
按照查询速度：char>varchar>text

BLOB和TEXT类型：MySQL 把每个 BLOB 和 TEXT 值当作一个独立的对象处理。
两者都是为了存储很大数据而设计的字符串类型，分别采用二进制和字符方式存储。

日期类型：

datetime：占用8个字节，可保存到毫秒，可保存时间范围大，与时区无关，数据库底层时区配置，对datetime无效

timestamp：占用4个字节，精确到秒，时间范围：1970-01-01到2038-01-19，依赖数据库设置的时区

date：占用的字节数比使用字符串、datetime、int存储要少，使用date类型只需要3个字节，使用date类型还可以利用日期时间函数进行日期之间的计算，date类型用于保存1000-01-01到9999-12-31之间的日期

1、不要使用字符串类型来存储日期时间数据
2、日期时间类型通常比字符串占用的存储空间小
3、日期时间类型在进行查找过滤时可以利用日期来进行比对
4、日期时间类型还有着丰富的处理函数，可以方便的对时间类型进行日期计算
5、使用int存储日期时间不如使用timestamp类型

IP地址使用特殊类型数据储存：

人们经常使用varchar(15)来存储ip地址，然而，它的本质是32位无符号整数不是字符串，可以使用INET_ATON()和INET_NTOA函数在这两种表示方法之间转换
案例：
select inet_aton('1.1.1.1')
select inet_ntoa(16843009)

2、合理使用范式和反范式

范式-优点：范式化的更新通常比反范式要快，当数据较好的范式化后，很少或者没有重复的数据，范式化的数据比较小，可以放在内存中，操作比较快

范式-缺点：通常需要进行关联

反范式-优点:所有的数据都在同一张表中，可以避免关联，可以设计有效的索引；

反范式-缺点：表格内的冗余较多，删除数据时候会造成表有些有用的信息丢失

3、主键

主键一般两种：1、代理主键 2、自然主键

推荐使用代理主键UUID它们不与业务耦合，因此更容易维护，一个大多数表，最好是全部表，通用的键策略能够减少需要编写的源码数量，减少系统的总体拥有成本

4、字符集

1、不包含汉字的表。纯拉丁字符能表示的内容，没必要选择 latin1 之外的其他字符编码，因为这会节省大量的存储空间。

2、utf8 只允许2字符汉字建议utf8mb4允许4个字符汉字(字符范围比较大)

5、存储引擎选择

存储引擎：MyISAM、 InnoDB

哈希 B+树

索引类型：非聚簇索引聚簇索引

支持事务：否是

支持表锁：是是

支持行锁：否是

支持外键：否是

支持全文索引：是是(5.6版本后)

适合操作类型：大量select 大量insert、delete、update

6、适当数据冗余和拆分

冗余：.这样的场景由于每次Join仅仅只是为了取得某个小字段的值，Join到的记录又大，会造成大量不必要的 IO，完全可以通过空间换取时间的方式来优化。不过，冗余的同时需要确保数据的一致性不会遭到破坏，确保更新的同时冗余字段也被更新。

拆分：垂直切分(按照业务把各种表放到不同服务器)、水平切分(把1000条数据放到服务器1，2，3)

三、执行计划

EXPLAIN select * from WR_IB_IBS_PROJ_BASIC_INFO;

1、id select查询的序列号，表示查询的顺序

①如果id相同，那么执行顺序从上到下

②如果id不通，如果是子查询，id序号会递增，id值越大优先级越高，越先被执行

③如果id相同和不同，同时存在，相同的被认为是一组，从上往下顺序执行；在所有组中，id值越大越先执行

2、select_type 分辨查询类型

SIMPLE 普通查询

PRIMARY 主键查询

UNION union查询

DEPENDENT UNION 依赖union查询

DEPENDENT SUBQUERY 相关子查询

3、type 访问方式 ALL 全表扫描暴力扫描

system> const > eq_ref> ref> fulltext> ref_or_null> index_merge> unique_subquery> index_subquery> range > index> ALL

一般来说，可以达到range级别，最好能到达ref

--all 全局扫描最慢，待优化

explain select* from emp；

--index 全索扫描这个比all好，主要有两种情况，一种是当前查询覆盖索引，即我们需要的数据在索引中就可以索取，或者是使用了索引进行排序，这就避免数据重排序

explain select empno from emp;

--range 表示利用索引查询的时候限制了范围，在指定范围内查询，这样避免了index的全索引扫描，使用的操作符：=, <>, >, >=, <, <=, IS NULL, BETWEEN, LIKE, or IN()

explain select * from emp where empno between 7000 and 8000;

--index_subquery 利用索引关联子查询，不用扫描全表

explain select * from emp where emp.job in(select job from t_job)

--unique_subquery 该连接类似与index_subquery，使用唯一索引

explain select * from emp where emp.deptno in(select distinct deptno from dept)

--index_subquery 在查询中需要多个索引组合使用，未模拟出来

--ref_or_null 对于某个字段需要关联条件，也需要null值情况下，查询优化器会选择这种访问方式

explain select * from emp e where e.mgr is null or e.mgr=7369;

--ref：使用了非唯一性索引进行数据的查找

--eq_ref :使用唯一性索引进行数据查找

explain select * from emp,emp2 where emp.empno=emp2.empno;

--const 这个表至多有一个匹配行

explain select * from emp where empno=7369;

--system 表只有一行数据

4、possible_keys 可能用到的索引

5、key 实际查询用到的索引

6、key_len索引长度，越小越快

7、extra额外信息

--using filesort 说明mysql无法利用索引排序，只能使用排序算法排序

explain select * from emp order by sal;

--using temporary 建立临时表来保存中间表结果，查询完成后把临时表删除

explain select ename,count(*) from emp where deptno=10 group by ename;

--using index 这个表示当前查询覆盖索引，直接从索引中读取数据，不访问数据库,如果同时出现 using where 表明索引被用来执行索引键值的查找，如果没有，表面索引被用来读取数据，而不是真的查找

explain select deptno,count(*) from emp group by deptno limit 10;

--using where 使用where条件过滤

explain select * from t_user where id=1;

--impossible where :where语句的结果总是false

explain select * from emp where empno=7469;

四、索引优化

1、索引基本知识

优点：1、大大减少了服务器需要扫描的数据量 2、帮助服务器避免排序和临时表 3、将随机io变成顺序io

作用：1、快速查找匹配WHERE子句的行 2、从consideration中消除行,如果可以在多个索引之间进行选择，mysql通常会使用找到最少行的索引 3、如果表具有多列索引，则优化器可以使用索引的任何最左前缀来查找行 4、当有表连接的时候，从其他表检索行数据 5、查找特定索引列的min或max值 6、如果排序或分组时在可用索引的最左前缀上完成的，则对表进行排序和分组 7、在某些情况下，可以优化查询以检索值而无需查询数据行

分类：主键索引、唯一索引、普通索引、全文索引、组合索引

技术名词：回表、覆盖索引、最左匹配、索引下推

索引的数据结构：哈希表(MyISAM)、B+树(Innodb)

2、索引匹配方式

全值匹配：全值匹配指的是和索引中的所有列进行匹配

匹配最左前缀：只匹配前面的几列

匹配列前缀：可以匹配某一列的值的开头部分

匹配范围值：可以查找某一个范围的数据

精确匹配某一列并范围匹配另外一列：精确匹配某一列并范围匹配另外一列

只访问索引的查询：查询的时候只需要访问索引，不需要访问数据行，本质上就是覆盖索引

3、组合索引规则

索引示例建立组合索引a,b,c

where a=3 是，只使用a

where a=3 and b=4 是，使用a，b

where a=3 and b=4 and c=5 是，使用a，b，c

where b=3 or where c=4 否

where a=3 and c=4 是，仅使用a

where a=3 and b>10 and c=7 是，使用a，b

where a>3 and b=10 and c=7 是，只使用a

where a=3 and b like '%xx%' and c=7 使用了 a，b

4、聚簇索引与非聚簇索引

Innodb为聚簇索引：不是单独的索引类型，而是一种数据存储方式，指的是数据行跟相邻的键值紧凑的存储在一起。优点：1、可以把相关数据保存在一起 2、数据访问更快，因为索引和数据保存在同一个树中 3、使用覆盖索引扫描的查询可以直接使用页节点中的主键值缺点：1、聚簇数据最大限度地提高了IO密集型应用的性能，如果数据全部在内存，那么聚簇索引就没有什么优势 2、插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式 3、更新聚簇索引列的代价很高，因为会强制将每个被更新的行移动到新的位置 4、基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂的问题 5、聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候

MYISAM和memory为非聚簇索引数据文件跟索引文件分开存放

5、覆盖索引

如果一个索引包含所有需要查询的字段的值，我们称之为覆盖索引

由于INNODB的聚簇索引，覆盖索引对INNODB表特别有用

6、新建索引注意事项

①类似性别这类区别不大的值，建索引是没有意义的，不能区别数据

②一把区分度到达80以上可以建索引，区分度是查询 select count(distinct (列名))/count(1) 来计算

③创建索引的字段，最好不是null，因为连null都不等于null，有可能会出问题

④如果明确知道返回只有一行，最好加上limit 1

⑤使用前缀索引 ‘%张’

⑥使用索引来排序

⑦强制类型转换会全表扫描

⑧范维列可以用到索引范围条件是：<、<=、>、>=、between，但是范围列后面的列无法用到索引，索引最多用于一个范围列

⑨单表索引建议控制在5个以内，单索引字段数不允许超过5个（组合索引）

五、查询优化

1、强行指定left join的加载顺序

select straight_join A,B,C from T1 inner join T2 on xxx inner T3 on

会改优化器以此固定顺序顺序加载 T1,T2,T3三张表

2. 排序优化

双次排序：第一次数据读取是将需要排序的字段读取出来，然后进行排序，第二次是将排好序的结果按照需要去读取数据行。

这种方式效率比较低，原因是第二次读取数据的时候因为已经排好序，需要去读取所有记录而此时更多的是随机IO，读取数据成本会比较高

两次传输的优势，在排序的时候存储尽可能少的数据，让排序缓冲区可以尽可能多的容纳行数来进行排序操作

单次排序：先读取查询所需要的所有列，然后再根据给定列进行排序，最后直接返回排序结果，此方式只需要一次顺序IO读取所有的数据，而无须任何的随机IO，问题在于查询的列特别多的时候，会占用大量的存储空间，无法存储大量的数据

当需要排序的列的总大小超过max_length_for_sort_data定义的字节，mysql会选择双次排序，反之使用单次排序，当然，用户可以设置此参数的值来选择排序的方式

3、优化count()查询

count(1) 和count(*) 是一样的，执行计划一样，执行时间一样

4、优化limit分页数据量大查询时

查询较快select * from rental a join (select rental_id from rental limit 10000000,5)b on a.rental_id=b.rental_id;

查询较慢：select * from rental limit 10000000,5

5、优化关联查询

确保on或者using子句中的列上有索引，在创建索引的时候就要考虑到关联的顺序

确保任何的groupby和order by中的表达式只涉及到一个表中的列，这样mysql才有可能使用索引来优化这个过程。也就是说使用多个表多个字段排序，就不会走想象中的索引

6、优化union查询

mysql总是通过创建并填充临时表的方式来执行union查询，因此很多优化策略在union查询中都没法很好的使用。经常需要手工的将where、limit、order by等子句下推到各个子查询中，以便优化器可以充分利用这些条件进行优化

六、分区表管理

1、应用场景

表非常大以至于无法全部都放在内存中，或者只在表的最后部分有热点数据，其他均是历史数据

分区表的数据更容易维护，分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备，可以使用分区表来避免某些特殊的瓶颈，可以备份和恢复独立的分区

2、分区表的限制

一个表最多只能有1024个分区，在5.7版本的时候可以支持8196个分区

如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来

分区表无法使用外键约束

3、分区表类型

范围分区：它的分区方式是：每个分区都包含行数据且分区的表达式在给定的范围内，分区的范围应

该是连续的且不能重叠，可以使用values less than运算符来定义

1、创建普通的表

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
);

2、创建带分区的表，下面建表的语句是按照store_id来进行分区的，指定了4个分区

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN (21)
);
--在当前的建表语句中可以看到，store_id的值在1-5的在p0分区，6-10的在p1分区，11-15的在p3分区，16-20的在p4分区，但是如果插入超过20的值就会报错，因为mysql不知道将数据放在哪个分区

3、可以使用less than maxvalue来避免此种情况

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);
--maxvalue表示始终大于等于最大可能整数值的整数值

4、可以使用相同的方式根据员工的职务代码对表进行分区

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE (job_code) (
    PARTITION p0 VALUES LESS THAN (100),
    PARTITION p1 VALUES LESS THAN (1000),
    PARTITION p2 VALUES LESS THAN (10000)
);

5、可以使用date类型进行分区：如虚妄根据每个员工离开公司的年份进行划分，如year(separated)

CREATE TABLE members (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATE NOT NULL
)
PARTITION BY RANGE( YEAR(joined) ) (
    PARTITION p0 VALUES LESS THAN (1960),
    PARTITION p1 VALUES LESS THAN (1970),
    PARTITION p2 VALUES LESS THAN (1980),
    PARTITION p3 VALUES LESS THAN (1990),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);
CREATE TABLE quarterly_report_status (
    report_id INT NOT NULL,
    report_status VARCHAR(20) NOT NULL,
    report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
    PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),
    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),
    PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),
    PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),
    PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
    PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),
    PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),
    PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
    PARTITION p9 VALUES LESS THAN (MAXVALUE)
);

列表分区：

类似于按range分区，区别在于list分区是基于列值匹配一个离散值集合中的某个值来进行选择

CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
)
PARTITION BY LIST(store_id) (
PARTITION pNorth VALUES IN (3,5,6,9,17),
PARTITION pEast VALUES IN (1,2,10,11,19,20),
PARTITION pWest VALUES IN (4,12,13,14,18),
PARTITION pCentral VALUES IN (7,8,15,16)
);

列分区：

mysql从5.5开始支持column分区，可以认为i是range和list的升级版，在5.5之后，可以使用column分区替代range和list，但是column分区只接受普通列不接受表达式

CREATE TABLE `list_c` (
`c1` int(11) DEFAULT NULL,
`c2` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50500 PARTITION BY RANGE COLUMNS(c1)
(PARTITION p0 VALUES LESS THAN (5) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB) */

CREATE TABLE `list_c` (
`c1` int(11) DEFAULT NULL,
`c2` int(11) DEFAULT NULL,
`c3` char(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50500 PARTITION BY RANGE COLUMNS(c1,c3)
(PARTITION p0 VALUES LESS THAN (5,'aaa') ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (10,'bbb') ENGINE = InnoDB) */

CREATE TABLE `list_c` (
`c1` int(11) DEFAULT NULL,
`c2` int(11) DEFAULT NULL,
`c3` char(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50500 PARTITION BY LIST COLUMNS(c3)
(PARTITION p0 VALUES IN ('aaa') ENGINE = InnoDB,
PARTITION p1 VALUES IN ('bbb') ENGINE = InnoDB) */

hash分区一般不这样使用：

基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含myql中有效的、产生非负整数值的任何表达式

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4;


CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY LINEAR HASH(YEAR(hired))
PARTITIONS 4;

key分区：

类似于hash分区，区别在于key分区只支持一列或多列，且mysql服务器提供其自身的哈希函数，必须有一列或多列包含整数值

CREATE TABLE tk (
    col1 INT NOT NULL,
    col2 CHAR(5),
    col3 DATE
)
PARTITION BY LINEAR KEY (col1)
PARTITIONS 3;

子分区：在分区的基础之上，再进行分区后存储

CREATE TABLE `t_partition_by_subpart`
(
  `id` INT AUTO_INCREMENT,
  `sName` VARCHAR(10) NOT NULL,
  `sAge` INT(2) UNSIGNED ZEROFILL NOT NULL,
  `sAddr` VARCHAR(20) DEFAULT NULL,
  `sGrade` INT(2) NOT NULL,
  `sStuId` INT(8) DEFAULT NULL,
  `sSex` INT(1) UNSIGNED DEFAULT NULL,
  PRIMARY KEY (`id`, `sGrade`)
)  ENGINE = INNODB
PARTITION BY RANGE(id)
SUBPARTITION BY HASH(sGrade) SUBPARTITIONS 2
(
PARTITION p0 VALUES LESS THAN(5),
PARTITION p1 VALUES LESS THAN(10),
PARTITION p2 VALUES LESS THAN(15)
);

4、使用分区表查询

全量扫描数据，不要任何索引：使用简单的分区方式存放表，不要任何索引，根据分区规则大致定位需要的数据为止，通过使用where条件将需要的数据限制在少数分区中，这种策略适用于以正常的方式访问大量数据

索引数据，并分离热点：如果数据有明显的热点，而且除了这部分数据，其他数据很少被访问到，那么可以将这部分热点数据单独放在一个分区中，让这个分区的数据能够有机会都缓存在内存中，这样查询就可以只访问一个很小的分区表，能够使用索引，也能够有效的使用缓存

5、分区表注意事项

①null值会使分区过滤无效 ②分区列和索引列不匹配，会导致查询无法进行分区过滤

③选择分区的成本可能很高 ④打开并锁住所有底层表的成本可能很高 ⑤维护分区的成本可能很高

七、服务器参数设置

配置文件，在mysql安装路径 my.cnf下

也可以通过 show variables like ' xxx';

set xxx=0;

1、general 基础配置

datadir=/var/lib/mysql   #数据文件存放的目录

socket=/var/lib/mysql/mysql.sock   #mysql.socket表示server和client在同一台服务器，并且使用localhost进行连接，就会使用socket进行连接

pid_file=/var/lib/mysql/mysql.pid   #存储mysql的pid

port=3306 #mysql服务的端口号

default_storage_engine=InnoDB   #mysql默认存储引擎

skip-grant-tables #当忘记mysql的用户名密码的时候，可以在mysql配置文件中配置该参数，跳过权限表验证，不需要密码即可登录mysql

2、character字符集配置

character_set_client #客户端数据的字符集

character_set_connection #mysql处理客户端发来的信息时，会把这些数据转换成连接的字符集格式

character_set_results #mysql发送给客户端的结果集所用的字符集

character_set_database # 数据库默认的字符集

character_set_server #mysql server的默认字符集

3、connection数据库连接配置

max_connections #mysql的最大连接数，如果数据库的并发连接请求比较大，应该调高该值

max_user_connections  #限制每个用户的连接个数

back_log  #mysql能够暂存的连接数量，当mysql的线程在一个很短时间内得到非常多的连接请求时，就会起作用，如果mysql的连接数量达到max_connections时，新的请求会被存储在堆栈中，以等待某一个连接释放资源，如果等待连接的数量超过back_log,则不再接受连接资源

wait_timeout  #mysql在关闭一个非交互的连接之前需要等待的时长

interactive_timeout  #关闭一个交互连接之前需要等待的秒数

4、log配置

log_error  #指定错误日志文件名称，用于记录当mysqld启动和停止时，以及服务器在运行中发生任何严重错误时的相关信息

log_bin  #指定二进制日志文件名称，用于记录对数据造成更改的所有查询语句

binlog_do_db   #指定将更新记录到二进制日志的数据库，其他所有没有显式指定的数据库更新将忽略，不记录在日志中

binlog_ignore_db #指定不将更新记录到二进制日志的数据库

sync_binlog #指定多少次写日志后同步磁盘

general_log #是否开启查询日志记录

general_log_file #指定查询日志文件名，用于记录所有的查询语句

slow_query_log  #是否开启慢查询日志记录

slow_query_log_file  #指定慢查询日志文件名称，用于记录耗时比较长的查询语句

long_query_time  #设置慢查询的时间，超过这个时间的查询语句才会记录日志

log_slow_admin_statements #是否将管理语句写入慢查询日志

5、cache配置

key_buffer_size  索引缓存区的大小（只对myisam表起作用）

query_cache_size 查询缓存的大小，未来版本被删除

query_cache_limit 超出此大小的查询将不被缓存

query_cache_min_res_unit 缓存块最小大小

query_cache_type  缓存类型，决定缓存什么样的查询

sort_buffer_size  每个需要排序的线程分派该大小的缓冲区

max_allowed_packet=32M  限制server接受的数据包大小

join_buffer_size=2M 表示关联缓存的大小

thread_cache_size{：服务器线程缓存，这个值表示可以重新利用保存再缓存中的线程数量，当断开连接时，那么客户端的线程将被放到缓存中以响应下一个客户而不是销毁，如果线程重新被请求，那么请求将从缓存中读取，如果缓存中是空的或者是新的请求，这个线程将被重新请求，那么这个线程将被重新创建，如果有很多新的线程，增加这个值即可
Threads_cached：代表当前此时此刻线程缓存中有多少空闲线程

Threads_connected：代表当前已建立连接的数量

Threads_created：代表最近一次服务启动，已创建现成的数量，如果该值比较大，那么服务器会一直再创建线程

Threads_running：代表当前激活的线程数 }

6、Innodb配置

innodb_buffer_pool_size #该参数指定大小的内存来缓冲数据和索引，最大可以设置为物理内存的80%

innodb_flush_log_at_trx_commit  #主要控制innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，值分别为0，1，2

innodb_thread_concurrency  #设置innodb线程的并发数，默认为0表示不受限制，如果要设置建议跟服务器的cpu核心数一致或者是cpu核心数的两倍

innodb_log_buffer_size  #此参数确定日志文件所用的内存大小，以M为单位

innodb_log_file_size  #此参数确定数据日志文件的大小，以M为单位

innodb_log_files_in_group  #以循环方式将日志文件写到多个文件中

read_buffer_size  #mysql读入缓冲区大小，对表进行顺序扫描的请求将分配到一个读入缓冲区

read_rnd_buffer_size #mysql随机读的缓冲区大小

innodb_file_per_table  #此参数确定为每张表分配一个新的文件