mysql学习笔记

2019/10/29 20:57
阅读数 183

影响性能的方面

1, 硬件

CPU与可用内存大小

mysql 不支持多CPU对sql的并发处理

选择高版本的mysql, 低版本对多核CPU不支持

InnoDB 同时将 索引与数据缓存在内存中,提高效率。

内存越多越好, 缓存对写入也是有益的。

硬盘:

 

 

2. 操作系统

cenos系统参数优化

 

 

 

 

 

 

 

3. 数据库存储引擎

4. 数据库参数配置

5. 数据库结构设计与SQL语句

6. 网络对性能的影响

网络带宽,网络质量

文件系统对性能的影响

mysql 体系结构

常用存储引擎 Innodb

默认ON

innodb 建议使用 表空间

存储引擎CSV

Archive 存储引擎

memory 存储引擎

Federated 存储引擎

mysql服务器参数

内存配置相关参数

以上4个参数要主要是对每个连接设置的内存,如果过大, 连接很多就会造成内存溢出

key_buffer_size 是给myisam 存储引擎的配置的

IO配置参数

 

业务繁忙,可以配置 Innodb_log_file_size 增加

 

doublewrite 增加 数据安全

 

安全相关配置

默认 max_allowed_packet 比较小

以上参数主要用于 主从时候,从服务器的配置

sql模式 谨慎在 已经使用中的 生产环境的 配置

其他参数

max_connections 默认100 太小了。 一般设置1000到2000 具体看情况

数据库设计与优化

1,太多列,对性能影响也是很大的

2. 关联表建议在10个以内

 

mysql基准测试

基准测试可以看做一种压力测试

 

 

如何进行基准测试

 

 

单独对某个组件比如mysql 进行基准测试

mysql 基准测试常见指标

 

mysql基准测试工具

sysbench基准测试工具

 

需求分析及逻辑设计举例

 

 

关联表越多性能越差, 商品价格变化呢?

优化与反范式化设计

 

设计表

 

mysql 高可用

复制解决的问题

mysql二进制日志

二进制日志格式对复制的影响

mysql复制工作方式

 

mysql 复制拓扑

 

 

 

 

主-主复制拓扑

 

不建议使用 主-主模式

主备模式

可以作为高可用的方式来使用

 

拥有备库的主主 复制拓扑

级联复制

可以避免主库因为复制而造成的额外负载,因为如果一个主库太多从库,主从同步 负载影响太大

在分页主库上启动  savle_log_update 参数 即可

mysql 复制性能优化

 

 

复制常见问题处理

mysql 无法解决的问题

1, 无法分担主数据库的写负载

2.  无法自动进行故障转移及主从切换

3. 提供读写分离功能

 

msql 高可用架构

 

 

如何避免mysql 单点故障

共享存储的 磁盘 就算一个 单点,因此并不是一个很有的方式

 

DRDB

故障转移处理时间比较长, 成本比较高等,并不是理想的方案

因为其他方式都不是完美方案。

 

MMM (Multi-Master Replication Manager) 架构

 

 

 

部署所需资源

优缺点

 

提供了从服务器监控功能

 

MHA 架构

 

 

优缺点

 

读写分离与负载均衡

程序实现读写分离

读写分离中间件

mysql-proxy 

性能和稳定性不足,不建议使用

maxScale

中间件高并发 性能很差

读负载均衡

 

索引优化

B-tree 索引

比较常见,一般都是 这种索引类型

b-tree 索引是顺序存储的

 

btree索引的限制

参考 https://blog.csdn.net/xiao2shiqi/article/details/54140934

Hash 索引

 

索引优化策略

索引的选择性: 即 索引值 越唯一,索引查询越高效

如果 字符串 内容太大了,可以建立 前缀索引。 虽然 会减低索引的选择性。

但是如果不建立前缀索引,字符串内容太大的话,加了索引查询也是比较低效的。

前缀索引 长度不要超过255 

联合索引

索引越多不仅仅是耗内存,而且 查询的时候,并不是都会用到所有的索引列的,可能只会用某一个独立的索引。

所以联合索引是 有必要的。

 

覆盖索引

也就是 避免回表的意思。 

或者索引的 对应的值 比较大

当 language_id 建立了索引
SELECT  language_id from film where language_id=1
以上的 SQL  就使用到了 覆盖索引,直接将索引上 language_id 对应的值返回了

如果是  
SELECT  * from film where language_id=1 
以上就 不会使用到 覆盖索引了


比如: 表 actor  last_name建立有 索引

  EXPLAIN SELECT actor_id,last_name from actor where last_name='Joe'

以上也是会用到覆盖索引的, 因为actor_id 为主键,而 last_name 有索引 

 

索引优化查询

 


使用 主键来排序,可用看到 type 为 index ,如果不使用 就算 all  
EXPLAIN SELECT * from rental WHERE rental_date>'2005-01-01' order by rental_id;

 

联合索引 字段排序, 二级索引

比如 
存在一个 联合索引 : UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`)

EXPLAIN SELECT * from rental WHERE rental_date='2005-01-01' ORDER BY inventory_id,customer_id

这里 在 extra 里面 就是 Using index condition

如果是 

EXPLAIN SELECT * from rental WHERE rental_date='2005-01-01' ORDER BY customer_id,inventory_id
那么就可以看到使用到了 文件排序 ,性能不高效
extra :  Using index condition; Using filesort


如果是:
EXPLAIN SELECT * from rental WHERE rental_date='2005-01-01' ORDER BY customer_id desc ,inventory_id
那么就可以看到使用到了 文件排序 ,性能不高效
extra :  Using index condition; Using filesort

这就是 前面说的  排序方式和索引的排序方式不一致,无法使用二级索引排序了


如果是 
EXPLAIN SELECT * from rental WHERE rental_date>'2005-01-01' ORDER BY customer_id desc ,inventory_id
extra :  Using index condition; Using filesort
因为 rental_date 是联合索引第一列, 范围的时候,就无法使用二级索引了

 

使用betree 索引来模拟 hash 索引优化查询

CREATE TABLE `film` (
  `film_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `description` text,
  `release_year` year(4) DEFAULT NULL,
  `language_id` tinyint(3) unsigned NOT NULL,
  `original_language_id` tinyint(3) unsigned DEFAULT NULL,
  `rental_duration` tinyint(3) unsigned NOT NULL DEFAULT '3',
  `rental_rate` decimal(4,2) NOT NULL DEFAULT '4.99',
  `length` smallint(5) unsigned DEFAULT NULL,
  `replacement_cost` decimal(5,2) NOT NULL DEFAULT '19.99',
  `rating` enum('G','PG','PG-13','R','NC-17') DEFAULT 'G',
  `special_features` set('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') DEFAULT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`film_id`),
  KEY `idx_title` (`title`),
  KEY `idx_fk_language_id` (`language_id`),
  KEY `idx_fk_original_language_id` (`original_language_id`),
  CONSTRAINT `fk_film_language` FOREIGN KEY (`language_id`) REFERENCES `language` (`language_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_film_language_original` FOREIGN KEY (`original_language_id`) REFERENCES `language` (`language_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8;

如果 要对 title 进行 查询, 因为 title 比较长,怎么提高查询 性能?

可以对title 增加一个列 存储对应的 md5 值

为了避免hash 冲突,所以上面的SQL  使用 title_md5= 值  与  title=值

这样两个条件来避免 hash 冲突

 

利用索引优化锁

如果没有索引,加锁的话,会锁住 整个表,导致其他查询无法 执行

索引的维护和优化

以上都是 索引的重复和冗余

不要将 主键给建立到 联合索引里面。

以下 pt 工具可以 检查 重复的索引

查找未被使用过的索引

未被使用的索引就应该 去掉

以上SQL 可以查询到表的 索引的使用次数

 

SQL 查询优化

使用慢查询日志获取有性能问题的SQL

慢查询分析工具

 

 

 

pt-query-digest

 

如果使用 explain 参数,那么建议在从 数据库上执行,因为会对数据库 运行有点影响

 

实时获取性能问题SQL

比如 以上的SQL就可以实时查看了。 通过脚本 实时运行以上SQL就可以收集实时数据了

 

SQL 解析预处理及生成执行计划

 

查询缓存对SQL性能的影响

如果hash 查找到了,还需要 判断用户的权限, 如果有权限,就从缓存中直接获取结果返回了

也就是 SQL语句必须 一致才可以命中 缓存 ,

而且  检查缓存是否命中都会对缓存加锁。

而且如果 表 结构 修改之后,都会将缓存 给清空,导致缓存无法命中。

 

执行计划

mysql 优化器可以优化的SQL类型

 

优化 min , max , count ,只在存在索引下或者主键下生效

 

等价变换: 当 发现 返回的所有列都包含在索引里面了,就直接从索引里面获取数据返回了

 

子查询优化: 将 其转为关联查询

提前终止查询: 当发现 不成立的条件时候,直接返回 空数据

对 in() 条件进行优化

 

如何确定查询处理各个阶段所消耗的时间

使用 profile

执行查询

 

但是 show profile 不推荐使用, 推荐使用 Performance Schema  

以上是 启用 , 而且是全局的 。

performance_schema 在 5.6 以上 性能消耗很小,推荐 设置使用

 

特定SQL的优化

以下是 实例 修改表 的存储过程脚本

 

 

如何修改大表的表结构

 

以上步骤有工具: pt-online-schema-change

如何优化 not in 和  != 查询

可以改写为 关联查询

改为

 

使用汇总表优化查询

数据库分库分表

1.

 

2.

 

数据库表的 水平拆分

如何选择分区键

如何存储无需分片的表

 

如何在节点上部署分片

如何分配分片中的数据

如何生成全局唯一ID

或者雪花算法全局id 等

分片工具

 

分库分表不容易,不到万不得已不要使用

数据库监控

可用性监控

如何监控数据库的连接数

可以定义一个指标 ,然后可用 报警, 

比如  threads_connected / max_connections > 0.8 就可以报警了

数据库性能监控

如何监控数据库的并发请求数量

 

如何监控Innodb 的阻塞

 

mysql 主从复制进行监控

如何验证主从复制的数据是否一致

可以使用工具  pt-table-checksum 

 

 

 

 

 

 

 

 

展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
OSCHINA
登录后可查看更多优质内容
返回顶部
顶部