分布式存储 mysql调优 mysql分布式

 

分布式存储,mysql调优

一.影响MYSQL性能的主要因素

1.1 数据库结构设计

1.2 数据库存储引擎

1.3 SQL语句的书写

1.4 数据库配置参数

1.5 操作系统和硬件

二.MYSQL底层设计调

2.1 三范式讲解

2.1.1 第一范式要求 数据库的某一列不可再被拆分。简单举个例子，比如有个系主任字段为经济系王强，其实可以拆分为系名、系主任这两个字段。

2.1.2  第二范式要求  满足第一范式、有主键、其他字段可以由主键确定  减少冗余。

（下面表1中，成绩是主键和课名挂钩的，必须主键加课名可定位出某个分数）

存在问题：删除学生高芳芳会导致法律系这个数据消失

2.1.3 第三范式要求 满足第二范式、字段直接依赖主键、拆封主体为主表和从表。上面第二张表中 系主任王强并不直接依赖于主键，而是通过系名来决定

其实数据结构设计多了第三范式就会很自然的理解和实现，难度不大。但实际开发中时常会反范式设计表，达到减少表、简化SQL的书写的目的。需要灵活使用。

2.2 反范式

纯粹的范式设计只会出现实验室

反范式示例：

优缺点：

2.3 实体关系分析

设计数据结构式，仔细分析实体之间的关系

1.实体关系种类，一对一，一对多，多对多

2.实体关系需双向分析

3.落地但具体的表设计上，实体关系决定表关系

2.4 自然主键和代理主键

推荐使用代理主键，比如mysql自增主键，自增主键使用Btree索引，会提高查询效率，

2.5 数据类型选择

选择最合适的数据类型可提高写入效率

2.5.1.字段类型优先级 数字类型 > 时间和二进制 > 字符串类型

2.5.2.varchar 和char

1varchar 可变长。比如使用是varchar（255）存‘abc’，再加一个字符记录长度，它占的空间并不是255，是以实际安大小为准。字段长度不固定的情况使用varchar

2.char 定长最大长度255，char类型的效率比varchar的效率稍高，适合存储经常更新的字符串。

2.5.3.日期 datetime 和timestamp

1.datetime，与时区无关、可保存到毫秒、可保存范围大

2.timestamp 占用4个字节,范围1079-01-01 到2038-01-19.依赖于时区

三.MYSQL存储引擎选择与优化

3.1存储引擎介绍：

innodb：

3.mysql 5.5.8以后 默认存储引擎为innodb

1.innodb表级别的存储方式，不是数据库级别，此模式下默认使用独立表空间tablename.ipd

2推荐使用utf8mb4字符，可以存4个字节的表情符号

3.innodb的默认缓存机制会有效提升查询效率

4.支持事务。

5.行级锁 在innodb中 只有利用索引的更新、删除操作，即where条件使用了索引字段，会使用行级锁，否则会是锁表。

MySAM：

 表级锁不适合高并发，适合非事务应用例如日志，适合只读类应用;(5.7之前版本)空间类应用，GIS

Memory：适合读多写少；不支持事务，内存读写效率超高

3.2.MYSQL索引

索引的目简单来说就是要更快查询

3.3.1索引使用技巧(索引不是越多越好)

btree索引：

1.Btree 索引适合范围查询

2.创建索引 : 创建uid普通索引 create index idx_uid on t_content(uid)

3.只支持前缀查询（xxx%）,不支持后缀和模糊查询

4.复合索引 ：创建uid复合索引 create index idx_id_source on t_content(id,source_id),使用时必须包含左侧列，not in和<>不会使用索引。

hash索引：

1.hash 索引适合匹配

2.创建索引

3.只有memory存储模式支持hash索引，只支持精确匹配

3.3.2索引优化策略：

1.不会使用索引的情况： 索引选择性太差（通过索引查出来的数据占总数的3成左右，可能就会使用全表扫描）、有使用not关键字、where条件跳过左侧索引列、对索引列进行计算或者使用函数

2.复合索引优化排序，order by .单字段排序，需要使用左侧字段进行排序。多字段排序，左侧字段必须升序，且顺序不被打乱

3.删除冗余索引  ,需下载工具percona-toolkit

4.查看索引使用情况(mysql 系统表 performance_schema)

 SELECT

OBJECT_TYPE,

OBJECT_SCHEMA,

OBJECT_NAME,

INDEX_NAME,

COUNT_READ,

COUNT_FETCH,

COUNT_INSERT,

COUNT_UPDATE,

        COUNT_DELETE

FROM

        `performance_schema.table_io_waits_summary_by_index_usage`

ORDER BY

SUM_TIMER_WAIT（根据索引的等待总时间降序）

各字段解释：

OBJECT_NAME    为空表示进行了全表扫描

count_*                   为各种操作索引的使用情况

5.减少表和索引的碎片

anaylze table； 索引重新计算

optimize table；优化表空间（会锁表）

3.4.多表关联优化

左侧表为驱动表，左侧表越少越好。左侧取值后，再筛选右表符合的数据

多表关联查询注意两点：

1.外键加索引,比如上图tbl2的col3字段

2.查询条件加索引，比如上图tabl的col3字段

3.5 慢sql 日志

把mysql中执行较慢的sql记录下来，过后查询出来再针对性的解决

1.配置慢sql记录

set gloobal slow_query-log =on

set global log_query_time =0.300(单位秒)

set show_query_log_file="slow-sql.log";

#默认启动需在配置文件my.cnf中配置

show variables like '%query%';查看是否配置完成

2.查看慢sql

到日志文件slow-sql.log中查看

3.有些sql是因为没有使用索引才导致的查询慢，可查看未使用索引的查询语句

set global log_querie_not_using_indexes =on; 查看未使用索引的查询语句

（ set global query_cache_size=0; 临时清除缓存，可在测试速度使用）

（ set global query_cache_type=0; 临时清除缓存，可在测试速度使用）

3.6执行计划详解explain（待更新）

语句前加explain

每一列的意思解释：

 

四.分库分表\主从\读写分离设计

4.1.分区

1.使用分区可以实现更少的数据检索范围。条件查询时范围过大时会不使用索引，而是全表检索。通过分区可去掉一部分分区数据，避免全表检索

2.更好的操作频用分区数据，部分常用分区数据可加载到内存

3.数据更容易维护，可独立备份

4.并发io，更常用的数据使用更快的硬盘

5减少锁表范围，避免大表锁表

分区的一些限制：

1.查询时必须包含分区列，不可对分区列进行计算

2.主键必须包含所有列

3.不支持建立外键索引

4.分区字段是数字类型

5.最多1024分区

4.2.分库分表

可通过中间件sharding sphere实现分库分表，具体操作sharding sphere官网查看http://shardingsphere.apache.org/index_zh.html

原理：实现原理是扩展原始的JDBC句；sharding sphere和mybatis整合请前往sharding sphere官网查看

性能评估：

限制：

不要使用自动生成主键会导致不同库中的表有相同的主键值，可使用分布式主键生成策略（雪花算法）

4.3一主多重

原理：从机执行主机执行过的sql语句，达到从机数据和主机数据完全一样的目的，配置时可设置从哪一条sql语句开始做同步

（本实例为一主一从，主机从机是放在一台服务器上）

1.为每个mysql配置server-id，mysql-bin为日志文件名

2.配置主机

生成从机用户，第一个slave为从机的用户名，ip地址为从机的IP地址，第二个salve为从机密码

3.配置从机：

配置主机信息，

mysql-bin.000001为配置文件（到mysql的data文件夹中看是否生成）

951为从第951条日志开始同步复制

配置完成

4.4.读写分离

可通过中间件sharding sphere实现读写分离，具体操作sharding sphere官网查看http://shardingsphere.apache.org/index_zh.html

在mysql主从模式生效后，使用中间件sharding sphere配置文件中设置主机和从机信息，执行增删改会自动使用主机，执行查会自动使用从机，从机默认用轮询方式做负载均衡。

五.MYSQL服务器与硬件调优

5.1连接参数配置

1.连接参数设置方法

2最大允许连接参数max_connections，项目初期不确定连接数可以调大点（默认151）

3各种连接情况多项指标

4通过此值可以看历史最大并发量

5连接池满了的时候，缓冲区放置多少请求，（默认70）

6对于jdbc连接，企业级应用中使用的数据库连接池可以进行有效性检查重建链接，可不考虑非交互式链接自动关闭的情况。

7当前数据库链接详情

show processlist;

5.2.缓存参数

5.2.1缓存设置：（global 一次性设置）

set global query_cache_size=10485760;

query_cache_size  设置缓存大小（单位B）

query_cache_type  缓存类型 5.7以后默认禁用，需要再ini文件配置

query_cache_min_res_unit 缓存快的最小尺寸

query_cache_limit 超出次大小不会被缓存；

5.2.1缓存状态： show status like 'Qcache%';

Qcache_free_memory  剩下多少缓存空间

Qcache_queries_in_cache 当前存储的查询缓存数量

Qcache_hit 命中率

Qcache_insert  缓存失效而进行的读

Qcache_lowmen_prunes 因为内存不足而别清除的查询

Qcache_not_cache 没有被缓存的查询数量

Qcache_total_blocks 当前block的数量

Qcache_free_blocks 碎片

mysql内存中的存储单位为Block默认4KB，不会被拆分。

sort_buffer_size 排序缓冲区，有大量数据要排序，可能需要注意次参数

5.3.innodb性能参数

1.innodb_buffer_pool_size 缓存池的大小单位B   默认128M

2.innodb_flush_log_at_trx_commit 日志写入的间隔控制，对于频繁写入的数据库可能需要注意

3.innodb_file_per_table =1 设置独立表空间，为每个表创建独立表空间。默认0，使用一个表空间

4.innodb_thread_concurrency 并发数。并发过多可能会导致cpu切换时间片导致多余消耗，最好结合机器的cup核心数配置

六.操作系统参数

针对CentOS7系统参数调优

对/etc/sysctl 文件进行配置，修改网络和内存的配置

1.网络连接数

2.尽量使用内存

 swap交换区，Swap分区在系统的物理内存不够用的时候，把硬盘内存中的一部分空间释放出来，以供当前运行的程序使用。

七.服务器相关

7.1服务器操作系统选择

linux 开源 稳定 安全 硬件 灵活性等

红帽：

ubuntu：

CentOS：

SUSE：

debian：

7.2.服务器硬件选择

1.cpu：

2.内存：

数据量很大时为减少内存成本，可将热点数据进行调优和抽取

3.硬盘：高速ssd  > 混合硬盘 >机械硬盘（尽量7200转）

raid磁盘荣誉冗余队列

rain 0

raid 1

raid 5

硬盘大小需要一致，性能以最差硬盘块为准

raid 10