MySQL优化
kider 1 目录 MYSQL优化 ............................................................................................................ 1 1. 我们可以且应该优化什么？ ................................................................................... 2 2. 优化硬件 ......................................................................................................... 2 3. 优化磁盘 ......................................................................................................... 2 4. 优化操作系统 ................................................................................................... 3 5. 选择应用编程接口 .............................................................................................. 3 6. 优化应用 ......................................................................................................... 4 7. 应该使用可移植的应用 ........................................................................................ 4 8. 如果你需要更快的速度 ........................................................................................ 4 9. 优化MYSQLD .................................................................................................. 4 10. 编译和安装MYSQL ......................................................................................... 5 11. 维护 ........................................................................................................... 5 12. 优化SQL ..................................................................................................... 5 13. 不同SQL服务器的速度差别（以秒计） ................................................................ 6 14. 重要的MYSQL启动选项 ................................................................................... 7 15. 优化表 ........................................................................................................ 7 16. MYSQL如何次存储数据 ................................................................................... 8 17. MYSQL表类型 .............................................................................................. 8 18. MYSQL行类型（专指IASM/MYIASM表） ............................................................ 8 19. MYSQL缓存 ................................................................................................. 8 20. MYSQL表高速缓存工作原理 .............................................................................. 9 21. MYSQL扩展/优化-提供更快的速度 ...................................................................... 9 22. MYSQL何时使用索引 .................................................................................... 10 23. MYSQL何时不使用索引 ................................................................................. 10 24. 学会使用EXPLAIN ....................................................................................... 11 25. 学会使用SHOW PROCESSLIST ...................................................................... 11 26. 如何知晓MYSQL解决一条查询 ........................................................................ 11 27. MYSQL非常不错 .......................................................................................... 12 28. MYSQL应避免的事情 .................................................................................... 12 29. MYSQL各种锁定 .......................................................................................... 12 30. 给MYSQL更多信息以更好地解决问题的技巧 ........................................................ 12 31. 事务的例子 ................................................................................................. 13 32. 使用REPLACE的例子 ................................................................................... 13 33. 一般技巧 .................................................................................................... 14 34. 使用MYSQL 3.23的好处 ............................................................................... 14 35. 正在积极开发的重要功能 ................................................................................ 14 MySQL优化 (本文是Monty在O'Reilly Open Source Convention 2000大会上的演讲)
kider 2 1. 我们可以且应该优化什么？ 硬件 操作系统/软件库 SQL服务器(设置和查询) 应用编程接口(API) 应用程序 2. 优化硬件 如果你需要庞大的数据库表(>2G)，你应该考虑使用64位的硬件结构，像Alpha、Sparc或即将推出的IA64。因为MySQL内部使用大量64位的整数，64位的CPU将提供更好的性能。 对大数据库，优化的次序一般是RAM、快速硬盘、CPU能力。 更多的内存通过将最常用的键码页面存放在内存中可以加速键码的更新。 如果不使用事务安全(transaction-safe)的表或有大表并且想避免长文件检查，一台UPS就能够在电源故障时让系统安全关闭。 对于数据库存放在一个专用服务器的系统，应该考虑1G的以太网。延迟与吞吐量同样重要。 3. 优化磁盘 为系统、程序和临时文件配备一个专用磁盘，如果确是进行很多修改工作，将更新日志和事务日志放在专用磁盘上。 低寻道时间对数据库磁盘非常重要。对与大表，你可以估计你将需要： log(行数)/log(索引块长度/3*2/(键码长度 + 数据指针长度))+1 次寻到才能找到一行。 对于有500000行的表，索引Mediun int类型的列，需要： log(500000) / log(1024/3*2/(3 + 2))+1=4次寻道。 上述索引需要500000*7*3/2=5.2M的空间。实际上，大多数块将被缓存，所以大概只需要1-2次寻道。 然而对于写入（如上），你将需要4次寻道请求来找到在哪里存放新键码，而且一般要2次寻道来更新索引并写入一行。 对于非常大的数据库，你的应用将受到磁盘寻道速度的限制，随着数据量的增加呈N log N数据级递增。 将数据库和表分在不同的磁盘上。在MySQL中，你可以为此而使用符号链接。 条列磁盘(RAID 0)将提高读和写的吞吐量。带镜像的条列(RAID 0+1)将更安全并提高读取的吞吐量。写入的吞吐量将有所降低。不要对临时文件或可以很容易地重建的数据所在的磁盘使用镜像或RAID(除了RAID 0)。 在Linux上，在引导时对磁盘使用命令hdparm -m16 -d1以启用同时读写多个扇区和DMA功能。这可以将响应时间提高5~50%。 在Linux上，用async (默认)和noatime挂载磁盘(mount)。对于某些特定应用，可以对某些特定表使用内存磁盘，但通常不需要。
MySQL中Like模糊查询速度太慢该如何进行优化
MySQL中Like模糊查询速度太慢该如何进⾏优化⽬录⼀、前⾔：⼆、第⼀个思路建索引三、INSTR附：Like是否使⽤索引?总结
⼀、前⾔：
我建了⼀个《学⽣管理系统》，其中有⼀张学⽣表和四张表（⼩组表，班级表，标签表，城市表）进⾏联合的模糊查询，效率⾮常的低，就想了⼀下如何提⾼like模糊查询效率问题注：看本篇博客之前请查看：
⼆、第⼀个思路建索引
1、like %keyword 索引失效，使⽤全表扫描。2、like keyword% 索引有效。3、like %keyword% 索引失效,使⽤全表扫描。使⽤explain测试了⼀下：原始表（注：案例以学⽣表进⾏举例）-- ⽤户表create table t_users( id int primary key auto_increment,-- ⽤户名 username varchar(20),-- 密码 password varchar(20),-- 真实姓名 real_name varchar(50),-- 性别 1表⽰男 0表⽰⼥ sex int,-- 出⽣年⽉⽇ birth date,-- ⼿机号 mobile varchar(11),-- 上传后的头像路径 head_pic varchar(200));建⽴索引#create index 索引名 on 表名(列名); create index username on t_users(username);like %keyword% 索引失效，使⽤全表扫描explain select id,username,password,real_name,sex,birth,mobile,head_pic from t_users where username like '%h%';
like keyword% 索引有效。 explain select id,username,password,real_name,sex,birth,mobile,head_pic from t_users where username like 'wh%';
MySql如何使用notin实现优化
MySql如何使⽤notin实现优化
最近项⽬上⽤select查询时使⽤到了not in来排除⽤不到的主键id⼀开始使⽤的sql如下：
select
s.SORT_ID，
s.SORT_NAME,
s.SORT_STATUS,
s.SORT_LOGO_URL,
s.SORT_LOGO_URL_LIGHTfrom SYS_SORT_PROMOTE s
WHERE
s.SORT_NAME = '必听经典'
AND s.SORT_ID NOT IN ("SORTID001")
limit 1;
表中的数据较多时这个sql的执⾏时间较长、执⾏效率低，在⽹上找资料说可以⽤ left join进⾏优化，优化后的sql如下：
select
s.SORT_ID，
s.SORT_NAME,
s.SORT_STATUS,
s.SORT_LOGO_URL,
s.SORT_LOGO_URL_LIGHTfrom SYS_SORT_PROMOTE s
left join (select SORT_ID from SYS_SORT_PROMOTE where SORT_ID=#{sortId}) b
on s.SORT_ID = b.SORT_ID
WHERE
b.SORT_ID IS NULL
AND s.SORT_NAME = '必听经典'
limit 1;
上述SORT_ID=#{sortId} 中的sortId传⼊SORT_ID这个字段需要排除的Id值，左外连接时以需要筛选的字段（SORT_ID）作为
连接条件，最后在where条件中加上b.SORT_ID IS NULL来将表中的相关数据筛选掉就可以了。
这⾥写下随笔，记录下优化过程。
以上就是本⽂的全部内容，希望对⼤家的学习有所帮助，也希望⼤家多多⽀持。
MySQL性能优化之参数配置
MySQL性能优化之参数配置
1、⽬的：
通过根据服务器⽬前状况，修改Mysql的系统参数，达到合理利⽤服务器现有资源，最⼤合理的提⾼MySQL性能。
2、服务器参数：
32G内存、4个CPU,每个CPU 8核。
3、MySQL⽬前安装状况。
MySQL⽬前安装，⽤的是MySQL默认的最⼤⽀持配置。拷贝的是f.编码已修改为UTF-8.具体修改及安装MySQL,可以参考
<>帮助⽂档。
4、修改MySQL配置
打开MySQL配置⽂件f
vi /etc/f
4.1 MySQL⾮缓存参数变量介绍及修改
4.1.1修改back_log参数值:由默认的50修改为500.（每个连接256kb,占⽤：125M）
back_log=500
back_log值指出在MySQL暂时停⽌回答新请求之前的短时间内多少个请求可以
被存在堆栈中。也就是说，如果MySql的连接数据达到max_connections时，新来
的请求将会被存在堆栈中，以等待某⼀连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。将会报：
unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的
待连接进程时.
back_log值不能超过TCP/IP连接的侦听队列的⼤⼩。若超过则⽆效，查看当前系
统的TCP/IP连接的侦听队列的⼤⼩命令：cat /proc/sys/net/ipv4/tcp_max_syn_backlog⽬前系统为1024。对于Linux系统推
荐设置为⼩于512的整数。
修改系统内核参数，）/html/64/n-810764.html
查看mysql 当前系统默认back_log值，命令：
show variables like 'back_log'; 查看当前数量
MySql（十）：MySQL性能调优——MySQLServer性能优化
MySql（⼗）：MySQL性能调优——MySQLServer性能优化
本章主要通过针对MySQL Server（ mysqld）相关实现机制的分析，得到⼀些相应的优化建议。主要涉及MySQL的安装以及相关参数设置的
优化，但不包括mysqld之外的⽐如存储引擎相关的参数优化，存
储引擎的相关参数设置建议将主要在下⼀章“ 常⽤存储引擎的优化” 中进⾏说明。
⼀、MySQL安装和优化
1.选择合适的发⾏版本
a.⼆进制发⾏版（包括RPM 等包装好的特定⼆进制版本）
由于MySQL 开源的特性，不仅仅MySQL AB 提供了多个平台上⾯的多种⼆进制发⾏版本可以供⼤家选择，还有不少第三⽅公司（或者个
⼈）也给我们提供了不少选择。
使⽤MySQL AB 提供的⼆进制发⾏版本我们可以得到哪些好处？
a) 通过⾮常简单的安装⽅式快速完成MySQL 的部署；
b) 安装版本是经过⽐较完善的功能和性能测试的编译版本；
c) 所使⽤的编译参数更具通⽤性的，且⽐较稳定；
d) 如果购买了MySQL 的服务，将能最⼤程度的得到MySQL 的技术⽀持；
b.第三⽅提供的MySQL 发⾏版本
⼤多是在MySQL AB 官⽅提供的源代码⽅⾯做了或多或少的针对性改动，然后再编译⽽成。这些改动有些是在某些功能上⾯的改进，也有些
是在某写操作的性能⽅⾯的改进。还有些由各OS ⼚商所提供的发⾏版本，则可能是在有些代码⽅⾯针对⾃⼰的OS 做了⼀些相应的底层调
⽤的调整，以使MySQL 与⾃⼰的OS 能够更完美的结合。当然，也有⼀些第三⽅发⾏版本并没有动过MySQL ⼀⾏代码，仅仅只是在编译参
数⽅⾯做了⼀些相关的调整，⽽让MySQL 在某些特定场景下表现更优秀。
这样⼀说，听起来好像第三⽅发⾏的MySQL ⼆进制版本要⽐MySQL AB 官⽅提供的⼆进制发⾏版有更⼤的吸引⼒，那么我们是否就应该选
⽤第三⽅提供的⼆进制发⾏版呢？
需要进⼀步分析⼀下第三⽅发⾏版本可能存在哪些问题?
MySQL中的表分区和索引选择优化建议
MySQL中的表分区和索引选择优化建议
在大数据时代的背景下，数据库的性能和优化变得越发重要。MySQL作为最流行的开源数据库管理系统之一，在数据分析与存储方面扮演着重要的角色。在MySQL中，表分区和索引选择是优化数据库性能的两个关键因素。本文将探讨MySQL中的表分区和索引选择，并给出优化建议。
一、表分区的概述
表分区是将一张表划分为多个较小的独立部分，每个部分可以存储在不同的物理位置上。表分区的主要目的是提高查询和维护的性能。通过将数据分布在多个分区上，可以减少查询的数据量，并且可以针对每个分区进行独立的维护操作。
在选择表分区的策略时，应该考虑数据的特点和查询模式。以下是一些建议：
1. 按范围分区：根据数据的范围进行分区，在每个分区上存储数据的范围是连续的。这种分区策略适用于按照时间或者连续的数值范围进行查询的场景。
2. 按列表分区：按照某个字段的固定值进行分区，在每个分区上存储的数据具有相同的特征。这种分区策略适用于按照某个字段值进行查询的场景。
3. 按哈希分区：根据某个字段的哈希值进行分区。这种分区策略适用于需要将数据均匀分布在不同分区上的场景。
二、索引选择的优化
索引是提高数据库查询效率的关键。选择合适的索引可以大大加快查询的速度，并减少数据库的资源消耗。以下是一些建议：
1. 唯一索引：在表中选择合适的字段创建唯一索引。唯一索引可以确保数据的唯一性，并且加快查询速度。通常，在主键或者唯一标识的字段上创建唯一索引是一个明智的选择。 2. 组合索引：对于频繁同时查询多个字段的操作，可以考虑创建组合索引。组合索引可以减少磁盘I/O次数和内存消耗。
3. 索引覆盖：尽量减少全表扫描，保证使用索引能够满足查询的需求。使用索引覆盖可以减少数据库的资源消耗。
4. 索引统计信息：及时更新索引的统计信息。MySQL提供了ANALYZE
TABLE或者OPTIMIZE TABLE命令来更新索引的统计信息，确保数据库的查询优化器能够选择合适的索引进行查询。
mysql中的optimize执行原理
mysql中的optimize执行原理
MySQL是一种常用的关系型数据库管理系统，它可以存储和管理大量的数据。在使用MySQL时，经常会遇到查询性能下降的情况。为了提升查询性能，MySQL提供了一些优化机制，其中之一就是optimize命令。本文将详细介绍MySQL中optimize命令的执行原理。
一、什么是optimize命令？
在MySQL中，optimize命令用于对表进行优化。当表中的数据被频繁地增删改时，会导致表的碎片化，即数据在磁盘上的存储位置不连续。这样的碎片化会影响查询性能。使用optimize命令可以对表进行重组，使得数据在磁盘上存储连续，从而提高查询性能。
二、optimize命令的执行步骤
当执行optimize命令时，MySQL会根据以下步骤来进行表的优化：
1. 锁定表
在执行optimize命令之前，MySQL会自动锁定要优化的表，以防止其他会话对表进行读写操作。这是为了确保在优化过程中表的数据一致性。
2. 创建新表
MySQL会创建一个新的表，用于存放优化后的数据。这个新表的结构和原表完全相同。
3. 从原表复制数据到新表 MySQL会逐行地从原表中读取数据，并将其复制到新表中。在复制过程中，MySQL会根据行的顺序将数据写入新表，从而让数据在磁盘上存储连续。
4. 关闭原表
当所有的数据都从原表复制到新表之后，MySQL会关闭原表。这意味着原表不再接受任何读写操作。
5. 重命名新表
MySQL会将新表重命名为原表的名称，这样就完成了表的优化过程。
6. 释放表锁
在表优化完成后，MySQL会释放对表的锁定，其他会话就可以继续访问该表。
三、optimize命令需要注意的细节
在使用optimize命令时，需要注意以下几点：
1. 表的大小
如果要优化的表很大，optimize命令的执行时间可能会比较长。在执行过程中，表会被锁定，这会对其他查询和事务产生影响。因此，需要在合适的时间执行optimize命令，避免对系统性能产生较大的影响。
MySQL数据表的性能优化与规划
MySQL数据表的性能优化与规划
章节1：引言
MySQL是一个流行的关系型数据库管理系统。它可以用于存储和管理各种类型的数据。MySQL具有良好的可扩展性和灵活性，使其成为许多网站和应用程序的首选数据库。然而，数据表在MySQL中的性能和规划方面是关键问题。MySQL的性能优化和规划可以帮助提高应用程序的响应时间，减少请求延迟，并促进数据库的可靠性。在本文中，我们将探讨MySQL数据表的性能优化和规划。
章节2：表的设计规划
数据表设计是数据库管理的核心任务之一。在MySQL中，表的性能优化和规划必须始于表的设计和规划。下面是一些表的设计规划原则：
2.1.规范表的命名
命名约定是表设计中的重要元素。命名必须为英文单词或者短语，明确表达表的意图。同时也要注意表名大小写的一致性和字符集的统一。建议在表名中使用下划线“_”来分隔单词。
2.2.确定表的字段
表的字段是建立数据库的基础。为了使表的性能达到最佳状态，确定表中的正确的字段非常重要。为表的每个字段选择正确的数据类型，以便最大限度地减少存储空间和提高性能。例如，选择INT data-type而不是VARCHAR data-type来存储小数值。
2.3.优化索引
索引在数据库性能方面起着非常重要的作用。如果正确地优化索引，可以大大减少查询时间和响应时间。MySQL支持各种类型的索引，包括B-Tree索引、哈希索引和全文索引。
2.4.规划表的大小和宽度
MySQL表的大小对查询性能有很大影响。规划表的大小和宽度是重要的优化因素。建议在一个表中最多包含200万行。如果您需要存储更多的数据，则应将其分解为多个表。
2.5.使用分区表
分区表是MySQL提供的一个高级功能，用于把一张大表（1000万行以上）分成较小的表块，以实现更快的查询速度和更好的数据管理。
章节3：表的性能优化
优化表是MySQL管理的核心任务之一。通过优化表，可以提高查询性能，快速响应客户请求，减少数据库中的负载并有效地管理数据。下面是表性能优化的一些方法：
Mysql优化之innodb_buffer_pool_size篇
Mysql优化之innodb_buffer_pool_size篇
前段时间，公司领导反映服务瞬时查询缓慢，压⼒⽐较⼤，针对这点，进⾏了⼀些了解与分析
1. 为什么需要innodb buffer pool？
在MySQL5.5之前，⼴泛使⽤的和默认的存储引擎是MyISAM。MyISAM使⽤操作系统缓存来缓存数据。InnoDB需要innodb buffer pool中处理缓存。所以⾮常需要有⾜
够的InnoDB buffer pool空间。
2. MySQL InnoDB buffer pool ⾥包含什么？
数据缓存InnoDB数据页⾯
索引缓存索引数据
缓冲数据
脏页（在内存中修改尚未刷新(写⼊)到磁盘的数据）
内部结构
如⾃适应哈希索引，⾏锁等。
3. 如何设置innodb_buffer_pool_size?
innodb_buffer_pool_size默认⼤⼩为128M。最⼤值取决于CPU的架构。在32-bit平台上，最⼤值为2**32 -1,在64-bit平台上最⼤值为2**64-1。当缓冲池⼤⼩⼤于1G时，
将innodb_buffer_pool_instances设置⼤于1的值可以提⾼服务器的可扩展性。
⼤的缓冲池可以减⼩多次磁盘I/O访问相同的表数据。在专⽤数据库服务器上，可以将缓冲池⼤⼩设置为服务器物理内存的80%。
3.1 配置缓冲池⼤⼩时，请注意以下潜在问题
物理内存争⽤可能导致操作系统频繁的paging
InnoDB为缓冲区和control structures保留了额外的内存，因此总分配空间⽐指定的缓冲池⼤⼩⼤约⼤10％。
缓冲池的地址空间必须是连续的，这在带有在特定地址加载的DLL的Windows系统上可能是⼀个问题。
初始化缓冲池的时间⼤致与其⼤⼩成⽐例。在具有⼤缓冲池的实例上，初始化时间可能很长。要减少初始化时间，可以在服务器关闭时保存缓冲池状态，并在服务器启动时将其还原。
innodb_buffer_pool_dump_pct：指定每个缓冲池最近使⽤的页⾯读取和转储的百分⽐。 范围是1到100。默认值是25。例如，如果有4个缓冲池，每个缓冲池有
MySQL的数据表压缩和存储空间优化
MySQL的数据表压缩和存储空间优化
引言
在数据库管理系统中，数据表占据着重要的地位。对于大型网站或应用程序来说，数据量庞大是常态。因此，优化数据表的存储空间是数据库管理的重要一环。本文将深入探讨MySQL数据表的压缩和存储空间优化的方法和策略。
一、数据表压缩的必要性
随着数据量的不断增长，数据库存储空间的成本也在逐渐增加。在这种情况下，对数据表进行压缩变得尤为重要。数据表压缩可以减少磁盘的占用空间，降低存储成本，并提高系统的整体性能。而MySQL提供的数据表压缩功能，则可以帮助我们更好地实现这一目标。
二、MySQL数据表压缩方法
1. 行压缩
行压缩是MySQL中常用的一种压缩方法。它通过对行的存储方式进行优化，减少了每行的存储空间。行压缩的原理是将NULL值和较短的VARCHAR类型字段存储为占用更少空间的格式。通过配置MySQL的参数innodb_page_compression，可以在数据表层面实现行压缩。在数据插入和查询的过程中，行压缩对数据库性能几乎没有负面影响，而且可以显著减少存储空间的占用。
2. 列压缩
另一种常见的MySQL数据表压缩方法是列压缩。相比于行压缩，列压缩更关注各列数据的存储方式。MySQL提供了多种列压缩算法，如字典压缩、位图压缩和前缀压缩等。这些算法在不同的数据类型和数据分布情况下，可以帮助数据库节省更多的存储空间。在创建数据表或修改表结构时，可以通过指定列的压缩类型来实现列压缩。 三、数据库存储空间优化策略
1. 合理选择数据类型
在设计数据表时，选择适当的数据类型是存储空间优化的基础。对于较小的整数型数据，可以考虑使用TINYINT或SMALLINT替代INT，从而减少存储空间的占用。此外，尽量避免使用浮点型数据类型，因为它们通常占用的存储空间较大。
2. 优化索引
索引在提高查询性能的同时，也会占用额外的存储空间。因此，合理地优化索引可以减少存储空间的占用。对于长文本或大字段数据，可以使用前缀索引来减小索引的大小。此外，通过删除冗余或不必要的索引，也能够减少存储空间的占用。
mysql-参数thread_cache_size优化方法小结
mysql-参数thread_cache_size优化⽅法⼩结
全局，动态，默认值-1表⽰⾃动调整⼤⼩，公式：8 + (max_connections / 100)。
最⼩值0，最⼤值16384，查看当前：
MySQL [(none)]> show variables like 'thread_cach%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| thread_cache_size | 64 |
+-------------------+-------+
在经常创建新的连接的情况下，提⾼该值可提⾼mysql性能，因为减少了连接的分配，但使⽤了java的连接池等，性能提升没
那么显著。如果每秒有上百的连接，需要将该值设置⾜够⾼。
#尝试连接次数，⽆论是否成功连接
MySQL [(none)]> show global status like 'connections';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| Connections | 177312707 |
+---------------+-----------+
1 row in set (0.00 sec)
MySQL [(none)]> show global status like 'thread%';
+-------------------+--------+
| Variable_name | Value |
+-------------------+--------+
| Threads_cached | 49 | #线程缓存中的空闲线程数
| Threads_connected | 416 | #当前打开的连接数