• 129136

    文章

  • 809

    评论

  • 12

    友链

  • 最近新加了换肤功能,大家多来逛逛吧~~~~
  • 喜欢这个网站的朋友可以加一下QQ群,我们一起交流技术。

MySQL - MySQL 性能优化

服了这份高薪指南,涨多少你说了算>>

一、MySQL 性能优化的维度

硬件: CPU、内存、存储、网络设备等

系统配置: 服务器系统、数据库服务参数等

数据库表结构: 高可用、分库分表、读写分离、存储引擎、表设计等

SQL 及 索引: SQL 语句优化、索引使用等

二、查询优化

1. 慢查询

  • 慢查询开启日志
  • 慢查询分析

2. Explain

帮助开发人员分析 SQL 问题

  • id:查询序列号
  • select_type:表示查询的类型
    • simple:简单的查询
    • primary:最外面的select
    • union:
    • dependent union:
    • union result :union 的结果
    • subquery:子查询的第一个select
    • dependent subquery:子查询的第一个select,取决于外面的查询
    • derived:导出表的 select
  • table:查询数据是关于哪张表
  • type:区间索引,显示连接用了何种类型,最好到最差的连接类型(至少达到range,最后达到 ref)
    • system
    • const:数据表最多只有一个匹配行,因为只匹配一行数据,所以很快
    • eq_ref:使用=比较带索引的列
    • ref:可用于 = 或 < 或 > 操作符的带索引的列
    • ref_or_null
    • index_merge
    • unique_subquery
    • index_subquery
    • range:只检索给定范围的行,使用一个索引来选择行
    • index:该连接类型与 all 相同,除了只有索引树被扫描,通常比 all 快,因为索引文件通常比数据文件少
    • all:对于每个来自于先前的表的行组合,进行完整的表扫描
  • possible_keys:指出 MySQL 能使用哪个索引在该表中找到行,如果是空的,没有想关的索引,这时要提高性能,可通过检验 where 子句,看是否引用某些字段,或者检查字段是否适合索引
  • key:实际使用到的key
  • key_len:最长的索引宽度
  • ref:显示使用哪个列或常数与key一起从表中选择行
  • rows:显示MySQL认为它执行查询时必须检查的函数
  • Extra:执行状态说明,包含MySQL解决查询的详细信息

三、索引优化

1. 索引的类型

  • 主键索引 Primary Key
  • 唯一索引 Unique
  • 普通索引 Index
  • 组合索引(联合索引) Index
  • 全文索引 FullText

2. 索引的存储结构

  • BTree 索引
  • 哈希索引
  • full-text 全文索引

3. 索引的使用场景

索引对性能造成的损失

  • 索引会增加写操作的成本
  • 太多的索引会增加查询优化器的选择时间

使用索引的场景

  • 主键自动创建唯一索引
  • 经常作为查询条件在 where 或者 order by 语句中出现
  • 作为排序的列要建立索引
  • 查询占用与其他表关联的字段,外键关系建立索引
  • 高并发条件下倾向建立组合索引
  • 用于聚合函数的列可以建立索引

不使用索引的场景

  • 有大量重复的列不单独建立索引
  • 表记录太少不要建立索引,因为没有太大的作用
  • 不会作为查询的列不要建立索引

四、存储优化

1. InnoDB 存储引擎

特定:

  • InnoDB 存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全
  • 提供了对数据事务 ACID(原子性,一致性,隔离性,持久性)的支持,实现 SQL 标准的四种隔离级别
  • 设计目标就是处理最大容量的数据库系统
  • 执行 select count(*) from table 语句是需要扫描全表
  • InnoDB 引擎是行锁,粒度更小,所以写操作不会锁定全表,在并发较高时,使用 InnoDB 会提升效率,即存在大量 update/insert 操作时,效率较高
  • InnoDB 清空数据量大的表时,是非常缓慢的,这是因为 InnoDB 必须处理表中的每一行

使用场景:

  • 经常 update/insert 的表,使用处理多并发的写请求
  • 支持事务,必选 InnoDB
  • 可以从灾难中恢复(日志 + 事务回滚)
  • 外键约束,列属性 AUTO_INCREMENT 支持

2. MyISAM 存储引擎

特点:

  • 不支持事务,不支持外键,select 为主的应用可以使用该引擎
  • 每个 MyISAM 在存储成 3 个文件,扩展名分别是
    • frm:存储的定义
    • MYD:存储数据
    • MYI:存储索引
  • 不同 MyISAM 表的索引文件和数据文件可以放置在不同的路径下

使用场景:

  • 经常 select/insert 的表,插入不频繁,查询非常频繁
  • 不支持事务
  • 做很多 count 的计算

3. InnoDB 和 MyISAM 的区别

  • InnoDB 是事务安全的,MyISAM 是非事务安全的
  • InnoDB 锁的粒度是行级,MyISAM 锁的粒度是表级
  • InnoDB 支持外键,MyISAM 不支持外键
  • MyISAM 相对简单,所以效率上要优于 InnoDB,小型应用可以考虑使用 MyISAM
  • InnoDB 表比 MyISAM 表更安全

4. 存储优化

  • 禁用索引
  • 禁用唯一性检查
  • 禁用外键检查
  • 批量插入数据
  • 禁止自动提交

五、数据库结构优化

1. 表结构优化

  • 尽量将表字段定义为 NOT NULL 约束,NULL 值会使索引以及索引的统计信息表变得很复杂
  • 对于只包含特定类型的字段,可以使用 enum、set 等数据类型
  • 数值型字段的比较比字符串的比较效率高的多,字段类型尽量使用最小,最简单的数据类型
  • 尽量使用小的数据类型,非负加上 UNSIGNED
  • varchar 的长度只分配真正需要的空间
  • 尽量使用 TIMESTAMP 而非 DATETIME,
  • 单表不要有太多字段,20 以内
  • 合理加入冗余字段可以提高查询速度

2. 表拆分

  • 垂直拆分
  • 水平拆分

3. 表分区

  • range 分区
  • list 预定义列表分区
  • hash 分区
  • key 键值分区

4. 读写分离

读写分离操作

主从复制功能

5. 数据库集群

六、硬件优化

  • 内存
  • 磁盘
  • CPU
  • 网络

七、缓存优化

1. 查询缓存

2. 全局缓存

  • key_buffer_size
  • innodb_buffer_pool_size
  • innodb_additional_mem_pool_size
  • innodb_log_buffer_size

3. 局部缓存

  • read_buffer_size
  • sort_buffer_size
  • read_rmd_buffer_size
  • tmp_table_size
  • record_buffer

4. 其它缓存

  • table_cache
  • thread_cache_size

八、服务器优化

1. MySQL 参数

  • back_log
  • wait_timeout
  • max_connections
  • max_user_connections
  • thread_concurrency
  • skip-name-resolve
  • default-storage-engine

2. Linux 系统优化

  • 避免使用 Swap交换分区,因为交换时是从硬盘读取的,速度很慢。
  • 将操作系统和数据分区分开,不仅仅是逻辑上,还包括物理上,因为操作系统的读写会影响数据库的性能。
  • 把 MySQL临时空间和复制日志与数据放到不同的分区,数据库后台从磁盘进行读写时会影响数据库的性能。
  • 避免使用软件磁盘阵列。
  • 在 Linux中设置swappiness的值为0,因为在数据库服务器中不需要缓存文件。
  • 使用 noatime 和 nodirtime 挂载文件系统,因为不需要对数据库文件修改时间。
  • 使用 XFS 文件系统,一种比ext3更快、更小的文件系统。
  • 调整 XFS 文件系统日志和缓冲变量 – 为了最高性能标准。
  • 使用 64位的操作系统,这会支持更大的内存。
  • 删除服务器上未使用的安装包和守护进程,节省系统的资源占用。
  • 把使用 MySQL的host和你的MySQL host放到一个hosts文件中。

695856371Web网页设计师②群 | 喜欢本站的朋友可以收藏本站,或者加入我们大家一起来交流技术!

0条评论

Loading...


发表评论

电子邮件地址不会被公开。 必填项已用*标注

自定义皮肤 主体内容背景
打开支付宝扫码付款购买视频教程
遇到问题联系客服QQ:419400980
注册梁钟霖个人博客