零、MySQL调优事项
目录
一、性能监控
1、使用show profile查询剖析工具,可以指定具体的type
2、使用show processlist查看连接的线程个数,来观察是否有大量线程处于不正常的状态或者其他不正常的特征
二、数据类型优化
1、数据类型优化
2、合理使用范式和反范式
3、主键
4、字符集
5、存储引擎选择
6、适当数据冗余和拆分
三、执行计划
1、id select查询的序列号,表示查询的顺序
2、select_type 分辨查询类型
3、type 访问方式 ALL 全表扫描暴力扫描
4、possible_keys 可能用到的索引
5、key 实际查询用到的索引
6、key_len索引长度,越小越快
7、extra额外信息
四、索引优化
1、索引基本知识
2、索引匹配方式
3、组合索引规则
4、聚簇索引与非聚簇索引
5、覆盖索引
6、新建索引注意事项
五、查询优化
1、强行指定left join的加载顺序
2. 排序优化
3、优化count()查询
4、优化limit分页 数据量大查询时
5、优化关联查询
6、优化union查询
六、分区表管理
1、应用场景
2、分区表的限制
3、分区表类型
hash分区 一般不这样使用:
4、使用分区表查询
5、分区表注意事项
七、服务器参数设置
1、general 基础配置
2、character字符集配置
3、connection数据库连接配置
4、log配置
5、cache配置
一、性能监控
1、使用show profile查询剖析工具,可以指定具体的type
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>textBLOB和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安装路径 myf下
也可以通过 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 #此参数确定为每张表分配一个新的文件
更多推荐
MySQL调优
发布评论