Mysql的优化的相关知识

秒速五厘米 2022-05-17 09:38 151阅读 0赞

*  **优化的几个方面**
 *  `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)`