叩丁狼—Java培训实战教程之mysql优化

亦凉 2023-02-22 12:18 28阅读 0赞

Java培训实战教程之mysql优化

Java培训过程中精点、难点知识解析

1. mysql引擎1.1. 引擎类型MySQL常用的存储引擎为MyISAM、InnoDB、MEMORY、MERGE，其中InnoDB提供事务安全表，其他存储引擎都是非事务安全表。MyISAM是MySQL的默认存储引擎。MyISAM不支持事务、也不支持外键，但其访问速度快，对事务完整性没有要求。innoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是比起MyISAM存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引MEMORY存储引擎使用存在内存中的内容来创建表。每个MEMORY表只实际对应一个磁盘文件。MEMORY类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用HASH索引。但是一旦服务关闭，表中的数据就会丢失掉。MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同。MERGE表本身没有数据，对MERGE类型的表进行查询、更新、删除的操作，就是对内部的MyISAM表进行的。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDc4MjE0MA_size_16_color_FFFFFF_t_70][]

2. 如何选择合适的存储引擎选择标准: 根据应用特点选择合适的存储引擎,对于复杂的应用系统可以根据实际情况选择多种存储引擎进行组合.

下面是常用存储引擎的适用环境:

1.MyISAM：默认的 MySQL 插件式存储引擎，它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一

2.InnoDB：用于事务处理应用程序，具有众多特性，包括ACID 事务支持。

3.Memory：将所有数据保存在 RAM 中，在需要快速查找引用和其他类似数据的环境下，可提供极快的访问。

4.Merge：允许 MySQL DBA 或开发人员将一系列等同的MyISAM 表以逻辑方式组合在一起，并作为 1 个对象引用它们。

对于诸如数据仓储等 VLDB 环境十分适合设置高速缓存注：可以通过order by语句测试缓存，order by语句执行速度慢!

设置和查询高速缓存大小SET GLOBAL query\_cache\_size = 41984; \#40KSHOW VARIABLESLIKE query\_cache\_size +------------------+-------+| Variable\_name | Value |+------------------+-------+| query\_cache\_size | 41984 |+------------------+-------+2.1.3. 缓存开启的方式查看是否开启SHOW VARIABLES LIKE query\_cache\_type SET SESSION query\_cache\_type =ON;如果查询缓存大小设置为大于0，query\_cache\_type变量影响其工作方式。这个变量可以设置为下面的值：0或OFF：将阻止缓存或查询缓存结果。

1或ON：将允许缓存，以SELECTSQL\_NO\_CACHE开始的查询语句除外。2或DEMAND：仅对以SELECTSQL\_CACHE开始的那些查询语句启用缓存。出自Java培训GLOBAL：设置所有链接的客户端session：设置单个客户端2.1.4. 设置缓存结果的最大值最小值如果不设置缓存的上线下线，查询结果过大将不会缓存。查询上线：SHOW VARIABLES LIKE query\_cache\_limit 设置上下线：SET GLOBAL query\_cache\_limit=10485760; \#10MSET GLOBAL query\_cache\_min\_res\_unit=41984;2.1.5. 查询高速缓冲状态和维护可以使用下面的语句检查MySQL服务器是否提供查询缓存功能：SHOW VARIABLES LIKE have\_query\_cache +------------------+-------+| Variable\_name | Value |+------------------+-------+| have\_query\_cache | YES |+------------------+-------+FLUSH QUERY CACHE：语句来清理查询缓存碎片以提高内存使用性能。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDc4MjE0MA_size_16_color_FFFFFF_t_70 1][]

该语句不从缓存中移出任何查询。RESET QUERY CACHE：语句从查询缓存中移出所有查询。FLUSH TABLES语句也执行同样的工作。SHOW STATUS：为了监视查询缓存性能，使用SHOWSTATUS查看缓存状态变量，例如：mysql SHOW STATUS LIKE Qcache% +-------------------------+--------+| Qcache\_free\_blocks | 36 || Qcache\_free\_memory | 138488 || Qcache\_hits | 79570 || Qcache\_inserts | 27087 || Qcache\_lowmem\_prunes | 3114 || Qcache\_not\_cached | 22989 || Qcache\_queries\_in\_cache | 415 || Qcache\_total\_blocks | 912 |+-------------------------+--------+QCACHE\_free\_blocks：空闲内存块的数量。QCACHE\_free\_memory：空闲内存的大小。QCACHE\_hits：查询缓存被访问的次数(命中数)。

QCACHE\_inserts：加入到缓存的查询数量。QCACHE\_lowmem\_prunes：由于内存较少从缓存删除的查询数量。QCACHE\_not\_cached：非缓存查询数(不可缓存，或由于query\_cache\_type设定值未缓存)。Qcache\_queries\_in\_cache：登记到缓存内的查询的数量。Qcache\_total\_blocks：查询缓存内的总块数。

2.2. 高速缓存语句要求下面的两个查询被查询缓存认为是不相同的：SELECT \* FROM tbl\_nameSelect \* from tbl\_name查询必须是完全相同的(逐字节相同)才能够被认为是相同的。

2.3. 不缓存的语句如果一个查询包含下面函数中的任何一个，它不会被缓存：BENCHMARK()CONNECTION\_ID()CURDATE()CURRENT\_DATE()CURRENT\_TIME()CURRENT\_TIMESTAMP()CURTIME()DATABASE()带一个参数的ENCRYPT()FOUND\_ROWS()GET\_LOCK()LAST\_INSERT\_ID()LOAD\_FILE()MASTER\_POS\_WAIT()NOW()RAND()RELEASE\_LOCK()SYSDATE()不带参数的UNIX\_TIMESTAMP()USER()Java培训学院始终认为你将会是最优秀的存在，看好你，加油!!!

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDc4MjE0MA_size_16_color_FFFFFF_t_70 2][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDc4MjE0MA_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/2020070314315781.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDc4MjE0MA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDc4MjE0MA_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200703143131996.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDc4MjE0MA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDc4MjE0MA_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/2020070314310187.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDc4MjE0MA==,size_16,color_FFFFFF,t_70