Mysql的优化的相关知识

• 优化的几个方面

• SQL语句优化:使用EXPLAIN来理解SQL语句本身的执行过程,执行计划;

• 索引优化:

• 数据库设计优化

• InnoDB表优化;

• MyISAM表优化;

• 理解查询执行计划;

• 缓冲和缓存;

  • 将经常缓冲的数据缓存在内存中,缓存的数据例如:表信息[表名], 表结构信息;

• 锁优化;

  • 需要指定合适的锁策略,并且使用粒度较小的锁,来使用行锁;

• Mysql服务器优化

  • 可以用于缓存结果,Mysql自己也包括查询缓存;

• 性能评估;

• Mysql优化内幕;

• Mysql优化需要在三个不同层次上面协调进行:Mysql级别,OS级别和硬件级别,Mysql级别的优化包括表级别优化,查询级别优化和Mysql服务配置优化,最终Mysql的各种数据结构,直接作用于OS甚至是硬件设备;因此还需要了解每种结构对OS级别的资源的需求,通常还涉及对于CPU以及IO操作的优化,用来提升性能;

• 数据库层面的优化着眼点

• 1.是否正确设计了表结构的相关信息,尤其是每个字段类型是否为最佳,同时为特定乐行的工作组织使用了合适的表以及表字段,例如,对于频繁更新的表,应该使用较多的表,较少的字段,对于复杂数据查询,应该使用较少的表,较多的字段,例如对于字符型来说,VARCHAR可以显著的节省空间,但是对于CHAR可以显著的提升性能;

• 2.是否为了高效的查找而创建了合适的索引;常见的索引包括HASH索引[不适用于范围查找]和B+树索引;

• 3.是否为每张表创建了合适的存储引擎,并且有效利用率存储引擎本身的有事和特性;

• 4.是否基于存储引擎为表选取了合适的行格式,例如压缩表在读写操作中会降低IO操作需求并且占用较少的磁盘空间,但是MyISAM仅仅在读环境中支持压缩表;

• 5.是否使用了合适的锁策略,如在并发操作使用使用共享锁,同时还应该考虑存储引擎支持的锁类型;

• 6.是否为InnoDB的缓冲池,MyISAM的键缓存以及Mysql的查询缓存设定了合适大小的内存空间,用于存储频繁访问的数据,而又不会引起页面换出;

• 操作系统和硬件级别又换的着眼点

• 1.是否为实际工作选取了合适的CPU,如对于CPU密集型,需要选取更快速度的以及更多数量的CPU,为查询场景较多的情况下,选择更多的CPU,甚至采用基于多核甚至是超线程技术,通常来说,CPU性能提升的的目标包括:低延迟和高吞吐量;对于mysql来说,一个查询语句只能够运行在一个CPU上面;

• 2.是否包含由合适的物理内存.并且通过合适的配置平衡内存和磁盘管理,减低甚至避免磁盘IO,程序设计通常具有局部性原理,为了这个原理通过指定合适的缓存策略,可以实现延迟写入,优化写入;

• 3.是否选择了合适的网络设备,网络设备导致的延迟和带宽,以及丢包等问题,如果存在量少但是数据请求大的连接,就应该提升网卡性能,对于连接请求多,数据量少,因为启用连接重用;

• 4.是否选取了合适的文件系统,因为Mysql对于数据安全的要求,应该尽量选择带有日志功能的文件系统;

• 5.Mysql为了响应每一个用户的请求,通常维护了一个单独的线程,并且还需要创建内部使用的线程,特殊目的使用的线程以及存储引擎创建的线程,Mysql需要对大量的线程进行管理;

• 使用InnoDB存储引擎最佳实践

• 1.基于Mysql查询语句中最常用的字段或者字段组合创建主键,如果没有适合的主键建议使用AUTO_INCREMENT类型的某字段为主键;

  • 一般是主索引(聚集索引),辅助索引,基于辅助索引的查询仍然会使用到主索引;
  • 索引是需要装入内存的;
  • 聚集索引:对于表中的数据只能够按照一种方式进行聚集索引;
  • 非聚集索引:
  • 主索引:主索引
  • 辅助索引:
  • 稠密索引:每一个变化值都是对应一个匹配的索引条目;
  • 稀疏索引:不是每一个变化值都对应一个匹配的索引条目;
  • 多级索引:
  • B+树索引:是一种多集索引,从根到每一个数据节点的路径是等长的,成为平衡树索引;虽然查询速度显著提升,但是导致数据的更新速度变慢;
    • 适用于全键值,键值范围,适合于最左左前查找,例如Li%开始的范围查找,例如select name like lixu%;但是不适合于select name like %u%,这种查找;
      • 只适合于左前查找;
      • 不能够跳过索引中的列:如果创建的索引是name,age,salary,在进行查找时,查找的是name, salary这个过程是不能够跳过age这一列的;但是支持name,age这种查找方式,也就是说可以跳过最后一个值,但是不能够跳过中间的索引;
      • 存储引擎不能够优化访问任何在第一个范围条件右边的列;对于创建的索引name, age,salary,如果不使用name字段,那么索引就是没有使用的,并且如果select语句是select name like 'chen%' and salary > 3000,那么存储引擎是不能够对salary字段进行优化的;
  • Hash索引:键值对,键是hash码,值是数据的值,value是某一张所在的位置,对于主索引不能够使Hash索引,可以用于等值查找,但是不适用于范围查找,因为频繁的计算hash码;memory支持显示的hash索引,InnoDB存储引擎支持自适应hash索引,会自动创建hash索引;
    • 适用于等值条件比较,例如= IN() <=>;查询速度快;
    • 索引只包含了hash码和行指针,所以无法进行有效的索引排序;
    • 不支持部分匹配,例如不支持like '%%',这种匹配方式;
  • 空间索引:
  • 全文索引:
  • 覆盖索引:数据查询是索引创建里面的一部分,例如创建的索引是name,age,在进行查找时,查找age,就属于覆盖索引;

• 2.如果需要使用多表查询,将这些表建立外键约束关系;

• 3.关闭autocommit;

• 4.使用事务START TRANSACTION语句组合相关的修改操作或者一个整体的工作单元,但是事务不应该过于大;

• 5.停止使用LOCK TABLES语句,InnoDB存储引擎可以搞笑的处理来自于多个会话的并发读写请求,如果需要在一系列的行上面获取独占访问权限,建议使用SELECT .... FROM UPDATE锁定仅需要更新的行;

• 6.启用Innodb_file_pre_table选项,将各张表的数据和索引分别存放;

• 7.评估数据和访问模式是否能够从InnoDB的表压缩功能中收益,决定是否在创建表时,使用FROM_FORMAT=COMPRESSED选项;

• MyISAM常用的几个调优参数

• key_buffer_size:用于调整键缓冲大小,用于存储索引的内存大小的,默认是8MB,最大为4GB,主要适用于加速查询操作;

• concurrent_insert:表示是否支持并发插入,一次执行多个插入操作,用于提高写入性能;

  • 0:表示禁止并发插入;
  • 1:默认值,表示数据之间存在不存在空隙的话,允许并发插入;
  • 2:表示如果数据之间存在空隙,那么允许并发插入;
  • 数据之间的空隙是因为某些行被删除,而之间的空隙违背填充;

• delay_key_write:用于延迟键写入操作,数据更新,索引就需要更新,索引更新就会触发缓存冲的索引失效,之后就需要重新读取索引,这个表示的含义是数据更新,并不立即更新索引操作;

• max_write_lock_count:

• preload_buffer_size:为了键缓冲预先准备的内存大小;

• InnoDB存储引擎优化

• InnoDB_data_file_path:表示InnoDB表空间的路径;

• InnoDB_data_home_dir:表示数据文件的目录;

• InnoDB_file_per_table:表示每表一个表空间文件;

• innodb_buffer_pool_size:用于定义InnoDB的缓冲池的大小,这个适用于缓存索引和数据的缓存区域,对于这段空间建议还是用大内存页;

• innodb_flush_log_at_trx_commit:表示在事物提交时,是否刷新log文件,为了事务的安全性,这个是建议打开的;

  • 0:这个不建议使用
  • 1:表示事务提交,并且每隔一段时间就会flush;
  • 2:仅仅在事物提交时,才进行flush操作;
  • 使用这个选项的前提是关系auto_commit;

• innodb_log_file_size:表示的是事务日志的大小,通常这个值建议调大;

• 查询缓存:

  • 键是select语句的hash码,值是语句的查询结果;对于select语句来说,如果使用的语句存在大小写交叉的情况,会导致select语句在计算缓存时得到的hash码不统一,无法有效利用查询缓存;
  • query_alloc_block_size:表示默认的查询缓存大小;
  • query_cache_size:数值为0,表示禁用查询缓存,否则就是启用,并且制定了一个大小,对于非决定性以及不具有时效性的时间都是会进行缓存的;
  • query_cache_limit:用于设置查询的结果大于某个值,就不在进行缓存;
  • query_cache_min_limit:查询结果如果小于这个值,不进行缓存;
  • query_cache_type:用于定义查询缓存的类型;
    • OFF:表示不进行缓存;
    • ON:表示进行缓存,这个是默认的选项,尽量缓存能够缓存的语句;
    • DEMAND:表示按照需要进行缓存,需要显示的提示进行缓存,才会进行缓存,例如select name from student where age = 30 SQL chachep[要求进行缓存];

• EXPLAIN语句解析:

• id:select语句的标识符,一般是数字,表示对应的SELECT语句中的原始语句中的位置,没有子查询或者联合的整个查询只有一个SELECT语句,因此其ID通常为1,在联合或者子查询语句中,内层的SELECT语句通常接在原始语句中的次序进行编号,但UNION操作通常最后会有一个id为NULL的行,因此UNION的结果通常保存在临时表中,而Mysql需要到次临时表中取得结果;

• 架构师---->自动化运维---->DBA(Mysql--->Oracle)