MySQL大表优化攻略
MySQL大表优化方案
当MySQL单表记录数过大时，增删改查性能都会急剧下降，可以参考以下步骤来优化：
单表优化
除非单表数据未来会一直不断上涨，否则不要一开始就考虑拆分，拆分会带来逻辑、部署、运维的各种复杂度，一般以整型值为主的表在千万级以下，字符串为主的表在五百万以下是没有太大问题的。而事实上很多时候MySQL单表的性能依然有不少优化空间，甚至能正常支撑千万级以上的数据量：
字段
一般来说，数据库中的表越小，执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，可以将表中的字段的宽度设得尽可能小。如果是定长的数据（eg：邮政编码（手机号码、身份证）可以设置为char(6)）。
尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT，如果非负则加上UNSIGNED
VARCHAR的长度只分配真正需要的空间
char定长，存储效率不如varchar，对于短数据的查询优于varchar
使用枚举或整数代替字符串类型
尽量使用TIMESTAMP而非DATETIME，
单表不要有太多字段，建议在20以内
避免使用NULL字段，很难查询优化且占用额外索引空间。“空
值”和“NULL”不同，空值不占用空间，而“NULL”是占用空间的，“NOT
NULL”不能插入“NULL”，可以插入“空值”。因为“NULL”不是空值，所以在mysql在进行比较的时候，NULL会参与字段比较，所以对效率有一部分影响（如果索引的字段可以为NULL，索引的效率会下降很多）。
用整型来存IP（1、节省空间。2、方便查询，比如要查某一网段的所有ip就可以用select* from table where ip between inet_aton('192.168.0.1') and
inet_aton('192.168.0.255')?）
INT UNSIGNED，否则长度不够。下面给出主要PHP代码：
$ip = '这里是获取到的IP'?
echo $intip = sprintf('%u',ip2long($ip))? //转换为无符号整型
echo long2ip($intip)?//将整型转换为ip
?>
这样保存到数据库之后也很容易比较，例如类似between之类的语句，在sql语句中也可以通过函数实现转换：
将IP地址转换为整型可以使用 select inet_aton(ip) from users?
将整型转换为IP地址可以使用 select inet_ntoa(intip) from users?
索引
索引并不是越多越好，要根据查询有针对性的创建，考虑在WHERE和ORDER BY命令上涉及的列建立索引，可根据EXPLAIN来查看是否用了索引还是全表扫描应尽量避免在WHERE子句中对字段进行NULL值判断，否则将导致引擎放弃使用索引而进行全表扫描
值分布很稀少的字段不适合建索引，例如"性别"这种只有两三个值的字段
字符字段只建前缀索引
字符字段最好不要做主键
不用外键，由程序保证约束
尽量不用UNIQUE，由程序保证约束
使用多列索引时主意顺序和查询条件保持一致，同时删除不必要的单列索引
索引常用规则：
表的主键、外键必须有索引
经常与其他表进行连接的表，在连接字段上应用建立索引
经常出现在where子句中的字段，特别是大表的字段，应该建立索引
索引应该建在选择性高的字段
索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引
频繁进行数据操作的表，不要建立太多的索引
在表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理上的开销
like语句操作（尽量不要在开头使用通配符）
一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%a%” 不会使用索引而like “aaa%”可以使用索引。
不要在列上进行运算
不使用NOT IN和操作
NOT IN和操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替，id3则可使用id>3 or id<3来代替。
查询SQL
可通过开启慢查询日志来找出较慢的SQL
不做列运算：SELECT id WHERE age + 1 = 10，任何对列的操作都将导致表扫描，它包括数据库教程函数、计算表达式等等，查询时要尽可能将操作移至等号右边sql语句尽可能简单：一条sql只能在一个cpu运算；大语句拆小语句，减少锁时间；一条大sql可以堵死整个库
不用SELECT *
OR改写成IN：OR的效率是n级别，IN的效率是log(n)级别，in的个数建议控制在200以内
不用函数和触发器，在应用程序实现
避免%xxx式查询
少用JOIN
使用同类型进行比较，比如用'123'和'123'比，123和123比
尽量避免在WHERE子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描
对于连续数值，使用BETWEEN不用IN：SELECT id FROM t WHERE num BETWEEN 1 AND 5
列表数据不要拿全表，要使用LIMIT来分页，每页数量也不要太大
引擎
目前广泛使用的是MyISAM和InnoDB两种引擎：
MyISAM
MyISAM引擎是MySQL 5.1及之前版本的默认引擎，它的特点是：
不支持行锁，读取时对需要读到的所有表加锁，写入时则对表加排它锁
不支持事务
不支持外键
不支持崩溃后的安全恢复
在表有读取查询的同时，支持往表中插入新纪录
支持BLOB和TEXT的前500个字符索引，支持全文索引
支持延迟更新索引，极大提升写入性能
对于不会进行修改的表，支持压缩表，极大减少磁盘空间占用
InnoDB
InnoDB在MySQL 5.5后成为默认索引，它的特点是：
支持行锁，采用MVCC来支持高并发
支持事务
支持外键
支持崩溃后的安全恢复
不支持全文索引
总体来讲，MyISAM适合SELECT密集型的表，而InnoDB适合INSERT和UPDATE密集型的表
系统调优参数
可以使用下面几个工具来做基准测试：
sysbench：一个模块化，跨平台以及多线程的性能测试工具
iibench-mysql：基于 Java 的 MySQL/Percona/MariaDB 索引进行插入性能测试工具
tpcc-mysql：Percona开发的TPC-C测试工具
具体的调优参数内容较多，具体可参考官方文档，这里介绍一些比较重要的参数：
back_log：back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。也就是说，如果MySql的连接数据达到
max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。可以从默认的50升至500
wait_timeout：数据库连接闲置时间，闲置连接会占用内存资源。可以从默认的8小时减到半小时
max_user_connection: 最大连接数，默认为0无上限，最好设一个合理上限
thread_concurrency：并发线程数，设为CPU核数的两倍
skip_name_resolve：禁止对外部连接进行DNS解析，消除DNS解析时间，但需要所有远程主机用IP访问
key_buffer_size：索引块的缓存大小，增加会提升索引处理速度，对MyISAM 表性能影响最大。对于内存4G左右，可设为256M或384M，通过查询show status like 'key_read%'，保证key_reads / key_read_requests在0.1%以下最好innodb_buffer_pool_size：缓存数据块和索引块，对InnoDB表性能影响最大。通过查询show status like 'Innodb_buffer_pool_read%'，保证
(Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) /
Innodb_buffer_pool_read_requests越高越好
innodb_additional_mem_pool_size：InnoDB存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小，当数据库对象非常多的时候，适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率，当过小的时候，MySQL 会记录Warning信息到数据库的错误日志中，这时就需要该调整这个参数大小innodb_log_buffer_size：InnoDB存储引擎的事务日志所使用的缓冲区，一般来说不建议超过32MB
query_cache_size：缓存MySQL中的ResultSet，也就是一条SQL语句执行的结果集，所以仅仅只能针对select语句。当某个表的数据有任何任何变化，都会导致所有引用了该表的select语句在Query Cache中的缓存数据失效。所以，当我们的数据变化非常频繁的情况下，使用Query Cache可能会得不偿失。根据命中率
(Qcache_hits/(Qcache_hits+Qcache_inserts)*100))进行调整，一般不建议太大，256MB可能已经差不多了，大型的配置型静态数据可适当调大.
可以通过命令show status like 'Qcache_%'查看目前系统Query catch使用大小
read_buffer_size：MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySql会为它分配一段内存缓冲区。如果对表的顺序扫描请求非常频繁，可以通过增加该变量值以及内存缓冲区大小提高其性能
sort_buffer_size：MySql执行排序使用的缓冲大小。如果想要增加ORDER BY 的速度，首先看是否可以让MySQL使用索引而不是额外的排序阶段。如果不能，可以尝试增加sort_buffer_size变量的大小
read_rnd_buffer_size：MySql的随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySql会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySql会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。
record_buffer：每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描，可能想要增加该值
thread_cache_size：保存当前没有与连接关联但是准备为后面新的连接服务的线程，可以快速响应连接的线程请求而无需创建新的
table_cache：类似于thread_cache_size，但用来缓存表文件，对InnoDB效果不大，主要用于MyISAM
升级硬件
Scale up，这个不多说了，根据MySQL是CPU密集型还是I/O密集型，通过提升CPU和内存、使用SSD，都能显著提升MySQL性能
读写分离
也是目前常用的优化，从库读主库写，一般不要采用双主或多主引入很多复杂性，尽量采用文中的其他方案来提高性能。同时目前很多拆分的解决方案同时也兼顾考虑了读写分离 缓存
缓存可以发生在这些层次：
MySQL内部：在系统调优参数介绍了相关设置
数据访问层：比如MyBatis针对SQL语句做缓存，而Hibernate可以精确到单个记录，这里缓存的对象主要是持久化对象Persistence Object
应用服务层：这里可以通过编程手段对缓存做到更精准的控制和更多的实现策略，这里缓存的对象是数据传输对象Data Transfer Object
Web层：针对web页面做缓存
浏览器客户端：用户端的缓存
可以根据实际情况在一个层次或多个层次结合加入缓存。这里重点介绍下服务层的缓存实现，目前主要有两种方式：
直写式（Write Through）：在数据写入数据库后，同时更新缓存，维持数据库与缓存的一致性。这也是当前大多数应用缓存框架如Spring Cache的工作方式。这种实现非常简单，同步好，但效率一般。
回写式（Write Back）：当有数据要写入数据库时，只会更新缓存，然后异步批量的将缓存数据同步到数据库上。这种实现比较复杂，需要较多的应用逻辑，同时可能会产生数据库与缓存的不同步，但效率非常高。
表分区
MySQL在5.1版引入的分区是一种简单的水平拆分，用户需要在建表的时候加上分区参数，对应用是透明的无需修改代码
对用户来说，分区表是一个独立的逻辑表，但是底层由多个物理子表组成，实现分区的代码实际上是通过对一组底层表的对象封装，但对SQL层来说是一个完全封装底层的黑盒子。MySQL实现分区的方式也意味着索引也是按照分区的子表定义，没有全局索引
用户的SQL语句是需要针对分区表做优化，SQL条件中要带上分区条件的列，从而使查询定位到少量的分区上，否则就会扫描全部分区，可以通过EXPLAIN PARTITIONS来查看某条SQL语句会落在那些分区上，从而进行SQL优化，如下图5条记录落在两个分区上：
分区的好处是：
可以让单表存储更多的数据
分区表的数据更容易维护，可以通过清楚整个分区批量删除大量数据，也可以增加新的分区来支持新插入的数据。另外，还可以对一个独立分区进行优化、检查、修复等操作
部分查询能够从查询条件确定只落在少数分区上，速度会很快
分区表的数据还可以分布在不同的物理设备上，从而搞笑利用多个硬件设备
可以使用分区表赖避免某些特殊瓶颈，例如InnoDB单个索引的互斥访问、ext3文件系统的inode锁竞争
可以备份和恢复单个分区
分区的限制和缺点：
一个表最多只能有1024个分区
如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来
分区表无法使用外键约束
NULL值会使分区过滤无效
所有分区必须使用相同的存储引擎
分区的类型：
RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区
LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择
HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式
KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值
分区适合的场景有：
最适合的场景数据的时间序列性比较强，则可以按时间来分区，如下所示：
CREATE TABLE members (
firstname VARCHAR(25) NOT NULL,
lastname VARCHAR(25) NOT NULL,
username VARCHAR(16) NOT NULL,
email VARCHAR(35),
joined DATE NOT NULL
)
PARTITION BY RANGE( YEAR(joined) ) (
PARTITION p0 VALUES LESS THAN (1960),
PARTITION p1 VALUES LESS THAN (1970),
PARTITION p2 VALUES LESS THAN (1980),
PARTITION p3 VALUES LESS THAN (1990),
PARTITION p4 VALUES LESS THAN MAXVALUE
);
查询时加上时间范围条件效率会非常高，同时对于不需要的历史数据能很容的批量删除。
如果数据有明显的热点，而且除了这部分数据，其他数据很少被访问到，那么可以将热点数据单独放在一个分区，让这个分区的数据能够有机会都缓存在内存中，查询时只访问一个很小的分区表，能够有效使用索引和缓存
另外MySQL有一种早期的简单的分区实现 - 合并表（merge table），限制较多且缺乏优化，不建议使用，应该用新的分区机制来替代
垂直拆分
垂直分库是根据数据库里面的数据表的相关性进行拆分，比如：一个数据库里面既存在用户数据，又存在订单数据，那么垂直拆分可以把用户数据放到用户库、把订单数据放到订单库。垂直分表是对数据表进行垂直拆分的一种方式，常见的是把一个多字段的大表按常用字段和非常用字段进行拆分，每个表里面的数据记录数一般情况下是相同的，只是字段不一样，使用主键关联
比如原始的用户表是：
垂直拆分后是：
垂直拆分的优点是：
可以使得行数据变小，一个数据块(Block)就能存放更多的数据，在查询时就会减少I/O次数(每次查询时读取的Block 就少)
可以达到最大化利用Cache的目的，具体在垂直拆分的时候可以将不常变的字段放一起，将经常改变的放一起
数据维护简单
缺点是：
主键出现冗余，需要管理冗余列
会引起表连接JOIN操作（增加CPU开销）可以通过在业务服务器上进行join来减少数据库压力
依然存在单表数据量过大的问题（需要水平拆分）
事务处理复杂
水平拆分
概述
水平拆分是通过某种策略将数据分片来存储，分库内分表和分库两部分，每片数据会分散到不同的MySQL表或库，达到分布式的效果，能够支持非常大的数据量。前面的表分区本质上也是一种特殊的库内分表
库内分表，仅仅是单纯的解决了单一表数据过大的问题，由于没有把表的数据分布到不同的机器上，因此对于减轻MySQL服务器的压力来说，并没有太大的作用，大家还是竞争同一个物理机上的IO、CPU、网络，这个就要通过分库来解决
前面垂直拆分的用户表如果进行水平拆分，结果是：
实际情况中往往会是垂直拆分和水平拆分的结合，即将Users_A_M和Users_N_Z再拆成Users和UserExtras，这样一共四张表
水平拆分的优点是:
不存在单库大数据和高并发的性能瓶颈
应用端改造较少
提高了系统的稳定性和负载能力
缺点是：
分片事务一致性难以解决
跨节点Join性能差，逻辑复杂
数据多次扩展难度跟维护量极大
分片原则
能不分就不分，参考单表优化
分片数量尽量少，分片尽量均匀分布在多个数据结点上，因为一个查询SQL跨分片越多，则总体性能越差，虽然要好于所有数据在一个分片的结果，只在必要的时候进行扩容，增加分片数量
分片规则需要慎重选择做好提前规划，分片规则的选择，需要考虑数据的增长模式，数据的访问模式，分片关联性问题，以及分片扩容问题，最近的分片策略为范围分片，枚举分片，一致性Hash分片，这几种分片都有利于扩容
尽量不要在一个事务中的SQL跨越多个分片，分布式事务一直是个不好处理的问题
查询条件尽量优化，尽量避免Select * 的方式，大量数据结果集下，会消耗大量带宽和CPU资源，查询尽量避免返回大量结果集，并且尽量为频繁使用的查询语句建立索引。
通过数据冗余和表分区赖降低跨库Join的可能
这里特别强调一下分片规则的选择问题，如果某个表的数据有明显的时间特征，比如订单、交易记录等，则他们通常比较合适用时间范围分片，因为具有时效性的数据，我们往往关注其近期的数据，查询条件中往往带有时间字段进行过滤，比较好的方案是，当前活跃的数据，采用跨度比较短的时间段进行分片，而历史性的数据，则采用比较长的跨度存储。 总体上来说，分片的选择是取决于最频繁的查询SQL的条件，因为不带任何Where语句的查询SQL，会遍历所有的分片，性能相对最差，因此这种SQL越多，对系统的影响越大，所以我们要尽量避免这种SQL的产生。
解决方案
由于水平拆分牵涉的逻辑比较复杂，当前也有了不少比较成熟的解决方案。这些方案分为两大类：客户端架构和代理架构。
客户端架构
通过修改数据访问层，如JDBC、Data Source、MyBatis，通过配置来管理多个数据源，直连数据库，并在模块内完成数据的分片整合，一般以Jar包的方式呈现
这是一个客户端架构的例子：
可以看到分片的实现是和应用服务器在一起的，通过修改Spring JDBC层来实现
客户端架构的优点是：
应用直连数据库，降低外围系统依赖所带来的宕机风险
集成成本低，无需额外运维的组件
缺点是：
限于只能在数据库访问层上做文章，扩展性一般，对于比较复杂的系统可能会力不从心
将分片逻辑的压力放在应用服务器上，造成额外风险
代理架构
通过独立的中间件来统一管理所有数据源和数据分片整合，后端数据库集群对前端应用程序透明，需要独立部署和运维代理组件
这是一个代理架构的例子：
代理组件为了分流和防止单点，一般以集群形式存在，同时可能需要Zookeeper之类的服务组件来管理
代理架构的优点是：
能够处理非常复杂的需求，不受数据库访问层原来实现的限制，扩展性强
对于应用服务器透明且没有增加任何额外负载
缺点是：
需部署和运维独立的代理中间件，成本高
应用需经过代理来连接数据库，网络上多了一跳，性能有损失且有额外风险 各方案比较
如此多的方案，如何进行选择？可以按以下思路来考虑：
1. 确定是使用代理架构还是客户端架构。中小型规模或是比较简单的场景倾向于选择
客户端架构，复杂场景或大规模系统倾向选择代理架构
2. 具体功能是否满足，比如需要跨节点ORDER BY，那么支持该功能的优先考虑
3. 不考虑一年内没有更新的产品，说明开发停滞，甚至无人维护和技术支持
4. 最好按大公司->社区->小公司->个人这样的出品方顺序来选择
5. 选择口碑较好的，比如github星数、使用者数量质量和使用者反馈
6. 开源的优先，往往项目有特殊需求可能需要改动源代码
按照上述思路，推荐以下选择：
客户端架构：ShardingJDBC
代理架构：MyCat或者Atlas
兼容MySQL且可水平扩展的数据库
目前也有一些开源数据库兼容MySQL协议，如：
TiDB
Cubrid
但其工业品质和MySQL尚有差距，且需要较大的运维投入，如果想将原始的MySQL迁移到可水平扩展的新数据库中，可以考虑一些云数据库：
阿里云PetaData
阿里云OceanBase
腾讯云DCDB
NoSQL
在MySQL上做Sharding是一种戴着镣铐的跳舞，事实上很多大表本身对MySQL这种RDBMS的需求并不大，并不要求ACID，可以考虑将这些表迁移到NoSQL，彻底解决水平扩展问题，例如：
日志类、监控类、统计类数据
非结构化或弱结构化数据
对事务要求不强，且无太多关联操作的数据
sql语句(mysql优化)绝对经典
sql语句（mysql优化）绝对经典 误区1：count(1)和count(primary_key) 优于count(*) 很多人为了统计记录条数，就使用count(1) 和count(primary_key) 而不是count(*) ，他们认为这样性能更好，其实这是一个误区。对于有些场景，这样做可能性能会更差，应为数据库对count(*) 计数操作做了一些特别的优化。 误区2：count(column) 和count(*) 是一样的 这个误区甚至在很多的资深工程师或者是DBA 中都普遍存在，很多人都会认为这是理所当然的。实际上，count(column) 和count(*) 是一个完全不一样的操作，所代表的意义也完全不一样。count(column) 是表示结果集中有多少个column字段不为空的记录，count(*) 是表示整个结果集有多少条记录 误区3：select a,b from … 比select a,b,c from …可以让数据库访问更少的数据量 这个误区主要存在于大量的开发人员中，主要原因是对数据库的存储原理不是太了解。实际上，大多数关系型数据库都是按照行(row)的方式存储，而数据存取操作都是以一个固定大小的IO单元(被称作block 或者page)为单位，一般为4KB，8KB… 大多数时候，每个IO单元中存储了多行，每行都是存储了该行的所有字段(lob等特殊类型字段除外)。 所以，我们是取一个字段还是多个字段，实际上数据库在表中需要访问的数据量其实是一样的。当然，也有例外情况，那就是我们的这个查询在索引中就可以完成，也就是说当只取a,b两个字段的时候，不需要回表，而c这个字段不在使用的索引中，需要回表取得其数据。在这样的情况下，二者的IO量会有较大差异。(覆盖索引) 误区4：order by 一定需要排序操作 我们知道索引数据实际上是有序的，如果我们的需要的数据和某个索引的顺序一致，而且我们的查询又通过这个索引来执行，那么数据库一般会省略排序操作，而直接将数据返回，因为数据库知道数据已经满足我们的排序需求了。实际上，利用索引来优化有排序需求的SQL，是一个非常重要的优化手段。延伸阅读：MySQL ORDER BY 的实现分析，MySQL 中GROUP BY 基本实现原理以及MySQL DISTINCT 的基本实现原理。（order by null）
mysql服务性能优化my_cnf配置说明详解16G内存
mysql服务性能优化—https://www.360docs.net/doc/7617972009.html,f配置说明详解 （16G内存） MYSQL服务器https://www.360docs.net/doc/7617972009.html,f配置文档详解 硬件：内存16G [client] port = 3306 socket = /data/3306/mysql.sock [mysql] no-auto-rehash [mysqld] user = mysql port = 3306 socket = /data/3306/mysql.sock basedir = /usr/local/mysql datadir = /data/3306/data open_files_limit = 10240 back_log = 600 #在MYSQL暂时停止响应新请求之前，短时间内的多少个请求可以被存在堆栈中。如果系统在短时间内有很多连接，则需要增大该参数的值，该参数值指定到来的TCP/IP连接的监听队列的大小。默认值50。 max_connections = 3000 #MySQL允许最大的进程连接数，如果经常出现Too Many Connections的错误提示，则需要增大此值。 max_connect_errors = 6000 #设置每个主机的连接请求异常中断的最大次数，当超过该次数，MYSQL服务器将禁止host 的连接请求，直到mysql服务器重启或通过flush hosts命令清空此host的相关信息。 table_cache = 614 #指示表调整缓冲区大小。# table_cache 参数设置表高速缓存的数目。每个连接进来，都会至少打开一个表缓存。#因此， table_cache 的大小应与 max_connections 的设置有关。例如，对于 200 个#并行运行的连接，应该让表的缓存至少有 200 × N ，这里 N 是应用可以执行的查询#的一个联接中表的最大数量。此外，还需要为临时表和文件保留一些额外的文件描述符。 # 当 Mysql 访问一个表时，如果该表在缓存中已经被打开，则可以直接访问缓存；如果#还
优化mysql数据库性能
为了提高性能建议作如下优化修改： 优化mysql数据库性能的参数： (1)、max_connections： 允许的同时客户的数量。增加该值增加mysqld 要求的文件描述符的数量。这个数字应该增加，否则，你将经常看到too many connections错误。默认数值是16384，请根据实际情况设置此参数。 (2)、key_buffer_size： 索引块是缓冲的并且被所有的线程共享。key_buffer_size是用于索引块的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)，到你能负担得起那样多。如果你使它太大，系统将开始换页并且真的变慢了。默认数值是10M，请根据实际情况设置此参数。 (3)、sort_buffer： 每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速order by或group by操作。默认数值是256K，请根据实际情况设置此参数。 4)、table_cache： 为所有线程打开表的数量。增加该值能增加mysqld要求的文件描述符的数量。mysql对每个唯一打开的表需要2个文件描述符。默认数值是256，，请根据实际情况设置此参数。 (5)、thread_cache_size： 可以复用的保存在中的线程的数量。如果有，新的线程从缓存中取得，当断开连接的时候如果有空间，客户的线置在缓存中。如果有很多新的线程，为了提高性能修改这个变量值。通过比较connections 和threads_created 状态的变量，可以看到这个变量的作用。默认数值是8，请根据实际情况设置此参数。 注：以上参数的调整可以通过修改C:\AppServ\MySQL\my.ini 文件并重启mysql 实现。这是一个比较谨慎的工作，上面的结果只供参考，请根据具体主机的硬件情况（特别是内存大小）进一步修改。 优化配置文件： C:\zxin10\Was\tomcat\conf\ server.xml (6)、在server.xml中修改标红相关参数。
MySQL5.1性能优化方案
MySQL5.1性能优化方案 1.平台数据库 1.1.操作系统 Red Hat Enterprise Linux Server release 5.4 (Tikanga) ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV), for GNU/Linux 2.6.9, dynamically linked (uses shared libs), for GNU/Linux 2.6.9, stripped 32位Linux服务器，单独作为MySQL服务器使用。 1.2.M ySQL 系统使用的是MySQL5.1，最新的MySQL5.5较之老版本有了大幅改进。主要体现在以下几个方面： 1）默认存储引擎更改为InnoDB InnoDB作为成熟、高效的事务引擎，目前已经广泛使用，但MySQL5.1之前的版本默认引擎均为MyISAM，此次MySQL5.5终于将默认数据库存储引擎改为InnoDB，并且引进了Innodb plugin 1.0.7。此次更新对数据库的好处是显而易见的：InnoDB的数据恢复时间从过去的一个甚至几个小时，缩短到几分钟(InnoDB plugin 1.0.7，InnoDB plugin 1.1，恢复时采用红-黑树)。InnoDB Plugin 支持数据压缩存储，节约存储，提高内存命中率，并且支持adaptive flush checkpoint, 可以在某些场合避免数据库出现突发性能瓶颈。 Multi Rollback Segments：原来InnoDB只有一个Segment，同时只支持1023的并发。现已扩充到128个Segments，从而解决了高并发的限制。 2）多核性能提升
mysql性能优化-慢查询分析、优化索引和配置
mysql性能优化-慢查询分析、优化索引和配置目录 一、优化概述 二、查询与索引优化分析 1性能瓶颈定位 Show命令 慢查询日志 explain分析查询 profiling分析查询 2索引及查询优化 三、配置优化 1) max_connections 2) back_log 3) interactive_timeout 4) key_buffer_size 5) query_cache_size 6) record_buffer_size 7) read_rnd_buffer_size 8) sort_buffer_size 9) join_buffer_size 10) table_cache 11) max_heap_table_size 12) tmp_table_size
13) thread_cache_size 14) thread_concurrency 15) wait_timeout 一、优化概述 MySQL数据库是常见的两个瓶颈是CPU和I/O的瓶颈，CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候。磁盘I/O瓶颈发生在装入数据远大于内存容量的时候，如果应用分布在网络上，那么查询量相当大的时候那么平瓶颈就会出现在网络上，我们可以用mpstat, iostat, sar和vmstat来查看系统的性能状态。 除了服务器硬件的性能瓶颈，对于MySQL系统本身，我们可以使用工具来优化数据库的性能，通常有三种：使用索引，使用EXPLAIN分析查询以及调整MySQL的内部配置。 二、查询与索引优化分析 在优化MySQL时，通常需要对数据库进行分析，常见的分析手段有慢查询日志，EXPLAIN 分析查询，profiling分析以及show命令查询系统状态及系统变量，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。 1 性能瓶颈定位 Show命令 我们可以通过show命令查看MySQL状态及变量，找到系统的瓶颈： Mysql> show status ——显示状态信息（扩展show status like ‘XXX’） Mysql> show variables ——显示系统变量（扩展show variables like ‘XXX’） Mysql> show innodb status ——显示InnoDB存储引擎的状态 Mysql> show processlist ——查看当前SQL执行，包括执行状态、是否锁表等
MySQL大数据量的查询提高性能优化
最近一段时间参与的项目要操作百万级数据量的数据，普通SQL查询效率呈直线下降，而且如果where中的查询条件较多时，其查询速度简直无法容忍。之前数据量小的时候，查询语句的好坏不会对执行时间有什么明显的影响，所以忽略了许多细节性的问题。 经测试对一个包含400多万条记录的表执行一条件查询，其查询时间竟然高达40几秒，相信这么高的查询延时，任何用户都会抓狂。因此如何提高sql语句查询效率，显得十分重要。以下是结合网上流传比较广泛的几个查询语句优化方法： 基本原则：数据量大的时候，应尽量避免全表扫描，应考虑在where 及order by 涉及的列上建立索引，建索引可以大大加快数据的检索速度。但是，有些情况索引是不会起效的，因此，需要下面的做法进行优化： 1、应尽量避免在where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。 2、应尽量避免在where 子句中对字段进行null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如： select id from t where num is null 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询： select id from t where num=0 3、尽量避免在where 子句中使用or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如： select id from t where num=10 or num=20 可以这样查询： select id from t where num=10 union all select id from t where num=20 4、下面的查询也将导致全表扫描：
千万级的mysql数据库与优化方法
千万级的mysql数据库与优化方法 1.对查询进行优化，应尽量避免全表扫描，首先应考虑在where 及order by 涉及的列上建立索引。 2.应尽量避免在where 子句中对字段进行null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如： Sql代码 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询： Sql代码 3.应尽量避免在where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。 4.应尽量避免在where 子句中使用or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：Sql代码 可以这样查询： Sql代码 5.in 和not in 也要慎用，否则会导致全表扫描，如： 对于连续的数值，能用between 就不要用in 了： 6.下面的查询也将导致全表扫描： Sql代码
若要提高效率，可以考虑全文检索。 7.如果在where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描： Sql代码 可以改为强制查询使用索引： 8.应尽量避免在where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如： 应改为: 9.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：Sql代码 应改为: 10.不要在where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。 11.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。 12.不要写一些没有意义的查询，如需要生成一个空表结构：
Mysql千万级别数据优化方案总结
Mysql千万级别数据优化方案 目录 目录 (1) 一、目的与意义 (2) 1)说明 (2) 二、解决思路与根据（本测试表中数据在千万级别） (2) 1)建立索引 (2) 2)数据体现（主键非索引，实际测试结果其中fid建立索引） (2) 3)MySQL分页原理 (2) 4)经过实际测试当对表所有列查询时 (2) 三、总结 (3) 1)获得分页数据 (3) 2)获得总页数：创建表记录大数据表中总数通过触发器来维护 (3)
一、目的与意义 1)说明 在MySql单表中数据达到千万级别时数据的分页查询结果时间过长，对此进行优达 到最优效果，也就是时间最短；(此统计利用的jdbc连接，其中fid为该表的主键;) 二、解决思路与根据（本测试表中数据在千万级别） 1)建立索引 优点：当表中有大量记录时，若要对表进行查询，第一种搜索信息方式是全表搜 索，是将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记 录，这样做会消耗大量数据库系统时间，并造成大量磁盘I/O操作；第二种就是 在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索 引中的ROWID（相当于页码）快速找到表中对应的记录。 缺点：当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降 低了数据的维护速度。 2)数据体现（主键非索引，实际测试结果其中fid建立索引） 未创建索引：SELECT fid from t_history_data LIMIT 8000000,10结果：13.396s 创建索引：SELECT fid from t_history_data LIMIT 8000000,10结果：2.896s select*fromt_history_datawherefidin (任意十条数据的id )结果：0.141s 首先通过分页得到分页的数据的ID,将ID拼接成字符串利用SQL语句 select * from table where ID in （ID字符串）此语句受数据量大小的影响比较小 （如上测试）； 3)MySQL分页原理 MySQL的limit工作原理就是先读取n条记录，然后抛弃前n条，读m条想要 的，所以n越大，性能会越差。 优化前SQL: SELECT * FROM v_history_data LIMIT 5000000, 1010.961s 优化后SQL: SELECT * FROM v_history_data INNER JOIN (SELECT fid FROM t_history_data LIMIT 5000000, 10) a USING (fid)1.943s 分别在于，优化前的SQL需要更多I/O浪费，因为先读索引，再读数据，然后 抛弃无需的行。而优化后的SQL(子查询那条)只读索引(Cover index)就可以了， 然后通过member_id读取需要的列 4)经过实际测试当对表所有列查询时 select * from table 会比select （所有列名）from table 快些（以查询8000000
MySQL数据库查询优化技术
MySQL数据库查询优化技术 MySQL是高效能高稳定的开源数据库产品，由于其超低成本和操作简易便利，在互联网等行业被广泛使用，几乎99%以上的网站都乐于采用mysql作为后台数据库，自从被Oracle收购后，Mysql更是从站长们的宠儿一举成为企业级应用的红人。在当今灸手可热的BAT，Mysql被大量使用。对于想进入互联网行业发展的数据库工程师和DBA们，熟练的Mysql技术无疑是一块很好的敲门砖。炼数成金在过去已经成功举办多种数据库课程，覆盖Oracle，DB2和多种NoSQL数据库，现在再推出MySQL系列，更加丰富了课程线路，也希望可以为大家带来更多学习知识提升价值的机会。 公益性培训课程: 《MySQL数据库查询优化技术》课程概述： 该课程通过15次课程，系统地讲解MySQL数据库的查询优化技术 课程语言：SQL 课程大纲： 第1课数据库与关系代数 综述数据库、关系代数、查询优化技术 综述数据库调优技术 预计时间1小时 第2课数据库查询优化技术总揽 综述查询优化技术范围，包括查询重用、查询重写规则、查询算法优化、并行查询优化等 综述逻辑查询优化，包括子查询的优化、视图重写、等价谓词重写、条件化简、连接消除、非SPJ的优化等 综述逻辑物理优化，包括单表扫描算法、两表连接算法、多表连接算法、基于代价的算法等 初步理解MySQL的查询执行计划。 预计时间1小时
第3课查询优化技术理论与MySQL实践（一）------子查询的优化（一） 第4课查询优化技术理论与MySQL实践（二）------子查询的优化（二） 从理论看，子查询包括的内容和范围，建立清晰的概念 从实践看，MySQL的子查询优化技术的内容和范围，明确掌握子查询优化手段预计时间2小时，每小时一个课程段（子查询是SQL查询优化的重点内容，务必掌握好） 第5课查询优化技术理论与MySQL实践（三）------视图重写与等价谓词重写什么是视图重写？哪些类型的视图可以被优化？MySQL是怎么优化视图的？从而明白在MySQL中怎么写与视图相关的查询语句才能有好的效果？ 什么是等价谓词重写？MySQL中怎么写WHERE子句有利于提高查询效率？ 预计时间1小时 第6课查询优化技术理论与MySQL实践（四）------条件化简 什么是条件化简？MySQL中对什么样的条件自动进行优化？如何写出可利用索引的条件语句？ 预计时间1小时 第7课查询优化技术理论与MySQL实践（五）------外连接消除、嵌套连接消除与连接消除 连接方式有些什么类型？不同类型的连接又是怎么优化的？外连接优化的条件是什么？MySQL中怎么写出可优化的连接语句？MySQL是否支持嵌套连接消除？MySQL是否支持连接消除？MySQL中书写SQL连接查询语句时的优化技巧。 预计时间1小时 第8课查询优化技术理论与MySQL实践（六）------数据库的约束规则与语义优化 数据库的参照完整性（CHECK t NULL等）。什么是语义优化？MySQL是否支持语义优化？怎么利用语义优化的思路人工进行SQL语句的优化？ 预计时间1小时 第9课查询优化技术理论与MySQL实践（七）------非SPJ的优化
Mysql性能优化
MySQL性能优化 性能优化是通过某些有效的方法来提高MySQL的运行速度，减少占用的磁盘空间。性能优化包含很多方面，例如优化查询速度，优化更新速度和优化MySQL服务器等。本文介绍方法的主要有： 优化查询 优化数据库结构 优化MySQL服务器 数据库管理人员可以使用SHOW STATUS语句来查询MySQL数据库的性能。语法：SHOW STATUE LIKE ‘value’；其中value参数是常用的几个统计参数。 Connections：连接MySQL服务器的次数 Uptime：MySQL服务器的上线时间； Slow_queries：慢查询的次数； Com_select：查询操做的次数； Com_insert：插入操作的次数； Com_delete：删除操作的次数； Com_update：更新操作的次数； 1优化查询 查询操作是最频繁的操作，提高了查询速度可以有效提高MySQL 数据库的性能。 首先要对查询语句进行分析，分析查询语句的命令是EXPLAIN语句和DESCRIBE语句。比如 EXPLAIN SELECT * FROM student \G； 索引可以快速定位表中的某条记录。使用索引也可以提高数据库查
询的速度，从而提高数据库的性能。如果不使用索引，查询语句将 表中的所有字段。这样查询的速度会很慢。如果使用了索引，查询语句只会查询索引字段。这样就减少查询的记录数，达到提高查询效率的目的。 现在看一个查询语句中没有索引的使用情况： SELECT * FROM student WHERE name = ‘张三’；这样会对student表中的所有数据都查询一下，对比一下name的字段是否是张三。 然后我们在name字段上建立一个名为index_name的索引：CREATE INDEX index_name ON student（name）； 现在name字段上面已经有索引了，再进行该select语句查询的速度就非常快了，不需要遍历整个表。 但是有些时候即使查询时使用的是索引，但索引并没有起作用。比如使用了LIKE关键字进行查询时，如果匹配字符串的第一个字符 为‘%’，索引不会被使用。如果‘%’不是在第一个位置，索引就会被使用。 另一种情况是在表的多个字段上创建一个索引，比如 CREATE INDEX index ON student（birth，department）；这样只有查询语句条件中使用字段name时，索引才会被用到。因为name字段是多列索引的第一个字段，只有查询条件中使用了name字段才会使索引index起作用。 2优化子查询 很多查询中需要使用子查询。子查询可以使查询语句很灵活，但子查询的执行效率不高。MySQL需要为内层查询语句的查询结果建立一个临时表。然后外层查询语句在临时表中查询记录。查询完毕后，MySQL需要插销这些临时表。所以在MySQL中可以使用连接查询来代替子查询。连接查询不需要建立临时表，其速度比子查询要快。
优化MySQL数据库性能的几个好方法
1、选取最适用的字段属性 MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。例如，在定义邮政编码这个字段时，如果将其设置为CHAR(255),显然给数据库增加了不必要的空间，甚至使用VARCHAR这种类型也是多余的，因为CHAR(6)就可以很好的完成任务了。同样的，如果可以的话，我们应该使用MEDIUMINT而不是BIGIN来定义整型字段。 另外一个提高效率的方法是在可能的情况下，应该尽量把字段设置为NOT NULL，这样在将来执行查询的时候，数据库不用去比较NULL值。 对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的性能。 2、使用连接（JOIN）来代替子查询(Sub-Queries) MySQL从4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。例如，我们要将客户基本信息表中没有任何订单的客户删除掉，就可以利用子查询先从销售信息表中将所有发出订单的客户ID取出来，然后将结果传递给主查询，如下所示： DELETE FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo ) 使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接（JOIN）.. 替代。例如，假设我们要将所有没有订单记录的用户取出来，可以用下面这个查询完成：
万字总结：学习MySQL优化原理,这一篇就够了!
万字总结：学习MySQL优化原理，这一篇就够了！ Java开源分享 说起MySQL的查询优化，相信大家收藏了一堆奇技淫巧：不能使用SELECT *、不使用NULL字段、合理创建索引、为字段选择合适的数据类型..... 你是否真的理解这些优化技巧？是否理解其背后的工作原理？在实际场景下性能真有提升吗？我想未必。因而理解这些优化建议背后的原理就尤为重要，希望本文能让你重新审视这些优化建议，并在实际业务场景下合理的运用。 MySQL逻辑架构 如果能在头脑中构建一幅MySQL各组件之间如何协同工作的架构图，有助于深入理解MySQL服务器。下图展示了MySQL的逻辑架构图。 MySQL逻辑架构整体分为三层，最上层为客户端层，并非MySQL所独有，诸如：连接处理、授权认证、安全等功能均在这一层处理。 MySQL大多数核心服务均在中间这一层，包括查询解析、分析、优化、缓存、内置函数(比如：时间、数学、加密等函数)。所有的跨存储引擎的功能也在这一层实现：存储过程、触发器、视图等。最下层为存储引擎，其负责MySQL中的数据存储和提取。和Linux下的文件系统类似，每种存储引擎都有其优势和劣势。中间的服务层通过API与存储引擎通信，这些API接口屏蔽了不同存储引擎间的差异。 MySQL查询过程
我们总是希望MySQL能够获得更高的查询性能，最好的办法是弄清楚MySQL是如何优化和执行查询的。一旦理解了这一点，就会发现：很多的查询优化工作实际上就是遵循一些原则让MySQL的优化器能够按照预想的合理方式运行而已。 当向MySQL发送一个请求的时候，MySQL到底做了些什么呢？ MySQL查询过程 客户端/服务端通信协议 MySQL客户端/服务端通信协议是“半双工”的：在任一时刻，要么是服务器向客户端发送数据，要么是客户端向服务器发送数据，这两个动作不能同时发生。一旦一端开始发送消息，另一端要接收完整个消息才能响应它，所以我们无法也无须将一个消息切成小块独立发送，也没有办法进行流量控制。 客户端用一个单独的数据包将查询请求发送给服务器，所以当查询语句很长的时候，需要设置max_allowed_packet参数。但是需要注意的是，如果查询实在是太大，服务端会拒绝接收更多数据并抛出异常。 与之相反的是，服务器响应给用户的数据通常会很多，由多个数据包组成。但是当服务器响应客户端请求时，客户端必须完整的接收整个返回结果，而不能简单的只取前面几条结果，然后让服务器停止发送。因而在实际开发中，尽量保持查询简单且只返回必需的数据，减小通信间数据包的大小和数量是一个非常好的习惯，这也是查询中尽量避免使用SELECT *以及加上LIMIT限制的原因之一。 查询缓存
mysql的性能检查和调优
一、识别有问题的查询语句 processlist命令的输出结果显示了有哪些线程在运行，可以帮助识别出有问题的查询语句，两种方式使用这个命令。 1. 进入m ysql/bin目录下输入mysqladmin processlist; 2. 启动m ysql，输入show processlist; 如果有SUPER 权限，则可以看到全部的线程，否则，只能看到自己发起的线程（这是指，当前对应的MySQL帐户运行的线程）。 得到数据形式如下（只截取了三条）： mysql> show processlist; +-----+-------------+--------------------+-------+---------+-------+----------------------------------+---------- | Id | User | Host | db | Command | Tim e| State | Info +-----+-------------+--------------------+-------+---------+-------+----------------------------------+---------- |207|root |192.168.0.20:51718 |m ytest | Sleep | 5 | | NULL |208|root |192.168.0.20:51719 |m ytest | Sleep | 5 | | NULL |220|root |192.168.0.20:51731 |m ytest |Query | 84 | Locked | select bookname,culture,value,type from book where id=001 先简单说一下各列的含义和用途，第一列，id，不用说了吧，一个标识，你要kill一个语句的时候很有用。user列，显示单前用户，如果不是root，这个命令就只显示你权限范围内的sql 语句。host列，显示这个语句是从哪个ip的哪个端口上发出的。呵呵，可以用来追踪出问题语句的用户。db列，显示这个进程目前连接的是哪个数据库。command列，显示当前连接的执行的命令，一般就是休眠（sleep），查询（query），连接（connect）。tim e列，此这个状态持续的时间，单位是秒。state列，显示使用当前连接的sql语句的状态，很重要的列，后续会有所有的状态的描述，请注意，state只是语句执行中的某一个状态，一个sql语句，已查询为例，可能需要经过copying to t m p table，Sorting result，Sending data等状态才可以完成，info列，显示这个sql语句，因为长度有限，所以长的sql语句就显示不全，但是一个判断问题语句的重要依据。 这个命令中最关键的就是state列，m ysql列出的状态主要有以下几种： Checking table 正在检查数据表（这是自动的）。 Closing tables 正在将表中修改的数据刷新到磁盘中，同时正在关闭已经用完的表。这是一个很快的操作，如果不是这样的话，就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中。 Connect Out 复制从服务器正在连接主服务器。 Copying to tmp table on disk 由于临时结果集大于t m p_table_size，正在将临时表从内存存储转为磁盘存储以此节省内存。 Creating t m p table 正在创建临时表以存放部分查询结果。
【黑马程序员】MySQL的性能调优一
【黑马程序员】MySQL的性能调优一 什么是MySQL，怎么安装，怎么使用，我这里不做说明了。 一、MySQL 与其他数据库的简单比较 1.1性能比较 性能方面，一直是MySQL 引以为自豪的一个特点。在权威的第三方评测机构多次测试较量各种数据库TPCC 值的过程中，MySQL 一直都有非常优异的表现，而且在其他所有商用的通用数据库管理系统中，仅仅只有Oracle 数据库能够与其一较高下。至于各种数据库详细的性能数据，我这里就不便记录，大家完全可以通过网上第三方评测机构公布的数据了解具体细节信息。 MySQL 一直以来奉行一个原则，那就是在保证足够的稳定性的前提下，尽可能的提高自身的处理能力。也就是说，在性能和功能方面，MySQL 第一考虑的要素主要还是性能，MySQL希望自己是一个在满足客户99%的功能需求的前提下，花掉剩下的大部分精力来性能努力，而不是希望自己是成为一个比其他任何数据库的功能都要强大的数据库产品。 1.2可靠性 关于可靠性的比较，并没有太多详细的评测比较数据，但是从目前业界的交流中可以了解到，几大商业厂商的数据库的可靠性肯定是没有太多值得怀疑的。但是做为开源数据库管理系统的代表，MySQL 也有非常优异的表现，而并不是像有些人心中所怀疑的那样，因为不是商业厂商所提供，就会不够稳定不够健壮。从当前最火的Facebook 这样大型的网站都是使用MySQL 数据库，就可以看出，MySQL 在稳定可靠性方面，并不会
比我们的商业厂商的产品有太多逊色。而且排在全球前10 位的大型网站里面，大部分都有部分业务是运行在MySQL数据库环境上，如Yahoo，Google 等。 总的来说，MySQL 数据库在发展过程中一直有自己的三个原则：简单、高效、可靠。从上面的简单比较中，我们也可以看出，在MySQL 自己的所有三个原则上面，没有哪一项是做得不好的。而且，虽然功能并不是MySQL 自身所追求的三个原则之一，但是考虑到当前用户量的急剧增长，用户需求越来越越多样化，MySQL 也不得不在功能方面做出大量的努力，来不断满足客户的新需求。比如最近版本中出现的Eent Scheduler （类似于Oracle 的Job 功能），Partition 功能，自主研发的Maria 存储引擎在功能方面的扩展，Falcon 存储引擎对事务的支持等等，都证明了MySQL 在功能方面也开始了不懈的努力。 任何一种产品，都不可能是完美的，也不可能适用于所有用户。我们只有衡量了每一种产品的各种特性之后，从中选择出一种最适合于自身的产品。 二、MySQL 的主要适用场景 据说目前MySQL 用户已经达千万级别了，其中不乏企业级用户。可以说是目前最为流行的开源数据库管理系统软件了。任何产品都不可能是万能的，也不可能适用于所有的应用场景。 那么MySQL 到底在什么场景下适用什么场景下不适用呢？ 1、Web 网站系统 Web 站点，是MySQL 最大的客户群，也是MySQL 发展史上最为重要的支撑力量，这一点在最开始的MySQL Server 简介部分就已经说明过。 MySQL 之所以能成为Web 站点开发者们最青睐的数据库管理系统，是因为
MySQL数据库性能(SQL)优化方案
MySQL数据库性能（SQL）优化方案本文探讨了提高MySQL 数据库性能的思路，并从8个方面给出了具体的解决方法。 1、选取最适用的字段属性 MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。例如，在定义邮政编码这个字段时，如果将其设置为CHAR(255),显然给数据库增加了不必要的空间，甚至使用VARCHAR这种类型也是多余的，因为CHAR(6)就可以很好的完成任务了。同样的，如果可以的话，我们应该使用MEDIUMINT而不是BIGIN 来定义整型字段。 另外一个提高效率的方法是在可能的情况下，应该尽量把字段设置为NOT NULL，这样在将来执行查询的时候，数据库不用去比较NULL值。 对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的性能。 2、使用连接（JOIN）来代替子查询(Sub-Queries) MySQL从4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。例如，我们要将客户基本信息表中没有任何订单的客户删除掉，就可以利用子查询先从销售信息表中将所有发出订单的客户ID取出来，然后将结果传递给主查询，如下所示： DELETE FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )
MySQL数据库性能(SQL)优化方案-期末论文
高级数据库技术——期末论文 基于SQL查询的MySQL数据库性能优化研究 ：XX 学号:2014XXXXX 学院：计算机学院
摘要： 查询是数据库系统中最基本也是最常用的一种操作，是否具有较快的执行速度，已成为数据库用户和设计者极其关心的问题。在研究开源数据库管理系统MySQL 查询优化技术的基础上，主要结合传统SQL操作优化、深度分析 MySQL 源代码、现代数据库发展几方面进行诸如参数调优，MySQL关联查询，重写相关规则等容展开优化分析研究。 关键词：查询优化，查询重用，查询重写，计划优化
一、传统SQL查询优化操作 1.选取最适用的字段属性 MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。例如，在定义邮政编码这个字段时，如果将其设置为CHAR(255),显然给数据库增加了不必要的空间，甚至使用VARCHAR这种类型也是多余的，因为CHAR(6)就可以很好的完成任务了。同样的，如果可以的话，我们应该使用MEDIUMINT而不是BIGIN来定义整型字段。 另外一个提高效率的方法是在可能的情况下，应该尽量把字段设置为NOT NULL，这样在将来执行查询的时候，数据库不用去比较NULL值。 对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的性能。 2.使用连接（JOIN）来代替子查询(Sub-Queries) MySQL从4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。例如，我们要将客户基本信息表中没有任何订单的客户删除掉，就可以利用子查询先从销售信息表中将所有发出订单的客户ID取出来，然后将结果传递给主查询，如下所示： DELETE FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo ) 使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，
mysql性能优化培训
一概述 数据库属于IO 密集型的应用程序，其主要职责就是数据的管理及存储工作。 而我们知道，从内存中读取一个数据库的时间是微秒级别，而从一块普通硬盘上读 取一个IO是在毫秒级别，二者相差3个数量级。所以，要优化数据库，首先第一 步需要优化的就是IO，尽可能将磁盘IO转化为内存IO。 所以我们先从MySQL 数据库IO相关参数（缓存参数）的角度来看看可以通过哪些参数进行IO优化 二表结构优化： 由于MySQL数据库是基于行（Row）存储的数据库，而数据库操作IO 的时候是以page（block）的方式，也就是说，如果我们每条记录所占用的空间量减小， 就会使每个page中可存放的数据行数增大，那么每次IO 可访问的行数也就增多 了。反过来说，处理相同行数的数据，需要访问的page 就会减少，也就是IO 操 作次数降低，直接提升性能。此外，由于我们的内存是有限的，增加每个page中 存放的数据行数，就等于增加每个内存块的缓存数据量，同时还会提升内存换中数 据命中的几率，也就是缓存命中率。 优化原则：使数据量最小，性能的瓶颈在于磁盘性能，数据量越小，磁盘读取数据的速度就越快，同时，在较小的列上建立索引，索引文件占用的资源也会更少。 1.尽可能地使用最有效(最小)的数据类型。 MySQL有很多节省磁盘空间和内存的专业化类型。尽可能使用较小的整数类型使表更小。例如，MEDIUMINT经常比INT好一些，因为MEDIUMINT列使用的空间 要少25% 2.尽量使用NOT NULL NULL 类型比较特殊，SQL 难优化。虽然MySQL NULL类型和Oracle 的NULL 有差异，会进入索引中，但如果是一个组合索引，那么这个NULL 类型的字段会极 大影响整个索引的效率。此外，NULL 在索引中的处理也是特殊的，也会占用额外 的存放空间。很多人觉得NULL 会节省一些空间，所以尽量让NULL来达到节省IO 的目的，但是大部分时候这会适得其反，虽然空间上可能确实有一定节省，倒是带 来了很多其他的优化问题，不但没有将IO量省下来，反而加大了SQL的IO量。所 以尽量确保DEFAULT 值不是NULL，也是一个很好的表结构设计优化习惯。 3.只创建你确实需要的索引。 索引对检索有好处，但是当你需要快速存储东西时就变得糟糕。如果主要通过搜索列的组合来存取一个表，对它们做一个索引。第一个索引部分应该是最常用的 列。如果从表中选择时总是使用许多列，应该首先以更多的副本使用列以获得更好