MySQL⼤表优化⽅案（当数据库表数据量过⼤造成CRUD操作性能很差时该如
何优化）
前⾔
当MySQL单表记录数过⼤时，增删改查性能都会急剧下降。可以通过单表优化、限定数据的范围、表分区、读写分离等⽅法进⾏优化。这篇⽂章主要内容是转载的博主manong， ，然后⾃⼰进⾏了⼀些修改。单表优化这块⽅法不是太全可以翻阅我的其它相关博⽂-----和
单表优化
除⾮单表数据未来会⼀直不断上涨，否则不要⼀开始就考虑拆分，拆分会带来逻辑、部署、运维的各种复杂度，⼀般以整型值为主的表在千万级以下，字符串为主的表在五百万以下是没有太⼤问题的。⽽事实上很多时候MySQL单表的性能依然有不少优化空间，甚⾄能正常⽀撑千万级以上的数据量。
字段
(1)尽量使⽤TINYINT、SMALLINT、MEDIUM_INT作为整数类型⽽⾮INT，如果⾮负则加上UNSIGNED
(2)VARCHAR的长度只分配真正需要的空间
(3)使⽤枚举或整数代替字符串类型
js炫酷科技特效(4)尽量使⽤TIMESTAMP⽽⾮DATETIME，
(5)单表不要有太多字段，建议在20个以内
(6)避免使⽤NULL字段，很难查询优化且占⽤额外索引空间，并且导致索引失效
(7)⽤整型来存IP
索引
(1)索引并不是越多越好，要根据查询有针对性的创建，考虑在WHERE和ORDER BY命令上涉及的列建⽴索引，可根据EXPLAIN来查看是否⽤了索引还是全表扫描
(2)尽量避免在WHERE⼦句中对字段进⾏NULL值判断，否则将导致引擎放弃使⽤索引⽽进⾏全表扫描
(3)值分布很稀少的字段不适合建索引，例如"性别"这种只有两三个值的字段
(4)字符字段只建前缀索引
(5)字符字段最好不要做主键
fclose函数必须要有参数吗
(6)不⽤外键和级联更新，级联更新是强阻塞操作，且对于数据更新不优化，容易导致更新风暴，外键和级联更新在应⽤程序中实现。
(7)尽量不⽤UNIQUE，由程序保证约束
(8)使⽤多列索引时主意顺序和查询条件保持⼀致，同时删除不必要的单列索引
SQL优化
(1)可通过开启慢查询⽇志来出较慢的SQL
(2)不对索引字段进⾏运算，会导致索引失效：SELECT id WHERE age + 1 = 10，任何对列的操作都将导致表扫描，它包括数据库教程函数、计算表达式等等，查询时要尽可能将操作移⾄等号右边mysql面试题sql优化
(3)sql语句尽可能简单：⼀条sql只能在⼀个cpu运算；⼤语句拆⼩语句，减少锁时间；⼀条⼤sql可以堵死整个库
(4)不⽤SELECT *，查询所有数据会增加I/O和⽹络传输负担。
(5)OR改写成IN：OR的效率是n级别，IN的效率是log(n)级别，in的个数建议控制在200以内
(6)不⽤函数和触发器，在应⽤程序实现
(7)避免%xxx式查询
(8)优先使⽤联表查询⽽不是嵌套⼦查询。
(9)使⽤同类型进⾏⽐较，⽐如⽤’123’和’123’⽐，123和123⽐
(10)尽量避免在WHERE⼦句中使⽤!=或<>操作符，否则将引擎放弃使⽤索引⽽进⾏全表扫描
(11)对于连续数值，使⽤BETWEEN不⽤IN：SELECT id FROM t WHERE num BETWEEN 1 AND 5
(12)列表数据不要拿全表，要使⽤LIMIT来分页，每页数量也不要太⼤
引擎
⽬前⼴泛使⽤的是MyISAM和InnoDB两种引擎。
MyISAM
MyISAM引擎是MySQL 5.1及之前版本的默认引擎，它的特点是：
不⽀持⾏锁，读取时对需要读到的所有表加锁，写⼊时则对表加排它锁
不⽀持事务
不⽀持外键
不⽀持崩溃后的安全恢复
在表有读取查询的同时，⽀持往表中插⼊新纪录
⽀持BLOB和TEXT的前500个字符索引，⽀持全⽂索引
⽀持延迟更新索引，极⼤提升写⼊性能
对于不会进⾏修改的表，⽀持压缩表，极⼤减少磁盘空间占⽤
InnoDB
InnoDB在MySQL 5.5后成为默认索引，它的特点是：
⽀持⾏锁，采⽤MVCC来⽀持⾼并发
⽀持事务
⽀持外键
⽀持崩溃后的安全恢复
⽀持全⽂索引( innodb在mysql 5.6.4已经⽀持全⽂索引)
总体来讲，MyISAM适合SELECT密集型的表，⽽InnoDB适合INSERT和UPDATE密集型的表系统调优参数
可以使⽤下⾯⼏个⼯具来做基准测试：
(1)sysbench：⼀个模块化，跨平台以及多线程的性能测试⼯具
(2)iibench-mysql：基于 Java 的 MySQL/Percona/MariaDB 索引进⾏插⼊性能测试⼯具
(3)tpcc-mysql：Percona开发的TPC-C测试⼯具
具体的调优参数内容较多，具体可参考官⽅⽂档，这⾥介绍⼀些⽐较重要的参数：
(1)back_log：back_log值指出在MySQL暂时停⽌回答新请求之前的短时间内多少个请求可以被存在堆栈中。也就是说，如果MySql的连接数据达到max_connections时，新来的请求将会被存在堆栈中，以等待某⼀连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。可以从默认的50升⾄500
(2)wait_timeout：数据库连接闲置时间，闲置连接会占⽤内存资源。可以从默认的8⼩时减到半⼩时
(3)max_user_connection: 最⼤连接数，默认为0⽆上限，最好设⼀个合理上限
(4)thread_concurrency：并发线程数，设为CPU核数的两倍
(5)skip_name_resolve：禁⽌对外部连接进⾏DNS解析，消除DNS解析时间，但需要所有远程主机⽤IP访问
(6)key_buffer_size：索引块的缓存⼤⼩，增加会提升索引处理速度，对MyISAM表性能影响最⼤。对于内存4G左右，可设为256M或384M，通过查询(7)show status like ‘key_read%’，保证key_reads / key_read_requests在0.1%以下最好
(8)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越⾼越好
(9)innodb_additional_mem_pool_size：InnoDB存储引擎⽤来存放数据字典信息以及⼀些内部数据结构的内存空间⼤⼩，当数据库对象⾮常多的时候，适当调整该参数的⼤⼩以确保所有数据都能存放在内存中提⾼访问效率，当过⼩的时候，MySQL会记录Warning信息到数据库的错误⽇志中，这时就需要该调整这个参数⼤⼩
(10)innodb_log_buffer_size：InnoDB存储引擎的事务⽇志所使⽤的缓冲区，⼀般来说不建议超过32MB
(11)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使⽤⼤⼩
(12)read_buffer_size：MySql读⼊缓冲区⼤⼩。对表进⾏顺序扫描的请求将分配⼀个读⼊缓冲区，MySql会为它分配⼀段内存缓冲区。如果对表的顺序扫描请求⾮常频繁，可以通过增加该变量值以及内存缓冲区⼤⼩提⾼其性能
(13)sort_buffer_size：MySql执⾏排序使⽤的缓冲⼤⼩。如果想要增加ORDER BY的速度，⾸先看是否可以让MySQL使⽤索引⽽不是额外的排序阶段。如果不能，可以尝试增加sort_buffer_size变量的⼤⼩
(14)read_rnd_buffer_size：MySql的随机读缓冲区⼤⼩。当按任意顺序读取⾏时(例如，按照排序顺序)，将分配⼀个随机读缓存区。进⾏排序查询时，MySql会⾸先扫描⼀遍该缓冲，以避免磁盘搜索，提⾼查询速度，如果需要排序⼤量数据，可适当调⾼该值。但MySql会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过⼤。
(15)record_buffer：每个进⾏⼀个顺序扫描的线程为其扫描的每张表分配这个⼤⼩的⼀个缓冲区。如果你做很多顺序扫描，可能想要增加该值
(16)thread_cache_size：保存当前没有与连接关联但是准备为后⾯新的连接服务的线程，可以快速响应连接的线程请求⽽⽆需创建新的
(17)table_cache：类似于thread_cache_size，但⽤来缓存表⽂件，对InnoDB效果不⼤，主要⽤于MyISAM
升级硬件
Scale up，这个不多说了，根据MySQL是CPU密集型还是I/O密集型，通过提升CPU和内存、使⽤SSD，都能显著提升MySQL性能
读写分离
读写分离也是⽬前常⽤的优化，从库读主库写，⼀般不要采⽤双主或多主引⼊很多复杂性，尽量采⽤⽂中的其他⽅案来提⾼性能。同时⽬前很多拆分的解决⽅案同时也兼顾考虑了读写分离。
缓存
缓存可以发⽣在这些层次：
(1)MySQL内部：在系统调优参数介绍了相关设置
(2)数据访问层：⽐如MyBatis针对SQL语句做缓存，⽽Hibernate可以精确到单个记录，这⾥缓存的对象主要是持久化对象Persistence Object
(3)应⽤服务层：这⾥可以通过编程⼿段对缓存做到更精准的控制和更多的实现策略，这⾥缓存的对象是数据传输对象Data Transfer Object
(4)Web层：针对web页⾯做缓存
(5)浏览器客户端：⽤户端的缓存
可以根据实际情况在⼀个层次或多个层次结合加⼊缓存。这⾥重点介绍下服务层的缓存实现，⽬前主要有两种⽅式：
(1)直写式（Write Through）：在数据写⼊数据库后，同时更新缓存，维持数据库与缓存的⼀致性。这也是当前⼤多数应⽤缓存框架如Spring Cache的⼯作⽅式。这种实现⾮常简单，同步好，但效率⼀般。
(2)回写式（Write Back）：当有数据要写⼊数据库时，只会更新缓存，然后异步批量的将缓存数据同步到数据库上。这种实现⽐较复杂，需要较多的应⽤逻辑，同时可能会产⽣数据库与缓存的不同步，但效率⾮常⾼。
表分区
MySQL在5.1版引⼊的分区是⼀种简单的⽔平拆分，⽤户需要在建表的时候加上分区参数，对应⽤是透明的⽆需修改代码。
对⽤户来说，分区表是⼀个独⽴的逻辑表，但是底层由多个物理⼦表组成，实现分区的代码实际上是通过对⼀组底层表的对象封装，但对SQL层来说是⼀个完全封装底层的⿊盒⼦。MySQL实现分区的⽅式也意味着索引也是按照分区的⼦表定义，没有全局索引。
⽤户的SQL语句是需要针对分区表做优化，SQL条件中要带上分区条件的列，从⽽使查询定位到少量的分区上，否则就会扫描全部分区，可以通过EXPLAIN PARTITIONS来查看某条SQL语句会落在那些分区上，从⽽进⾏SQL优化，如下图5条记录落在两个分区上：
mysql>explain partitions select count(1)from user_partition where id in(1,2,3,4,5);
+----+-------------+----------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type |table| partitions |type| possible_keys |key| key_len | ref  |rows| Extra                    |
+----+-------------+----------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
|1|SIMPLE| user_partition | p1,p4      | range |PRIMARY|PRIMARY|8|NULL|5|Using where;Using index|
equalsignorecase方法重载几数
+----+-------------+----------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
1row in set(0.00 sec)java常量类的命名
分区的好处
(1)可以让单表存储更多的数据
(2)分区表的数据更容易维护，可以通过清楚整个分区批量删除⼤量数据，也可以增加新的分区来⽀持新插⼊的数据。另外，还可以对⼀个独⽴分区进⾏优化、检查、修复等操作
(3)部分查询能够从查询条件确定只落在少数分区上，速度会很快
(4)分区表的数据还可以分布在不同的物理设备上，从⽽搞笑利⽤多个硬件设备
(5)可以使⽤分区表赖避免某些特殊瓶颈，例如InnoDB单个索引的互斥访问、ext3⽂件系统的inode锁竞争
(6)可以备份和恢复单个分区
分区的限制和缺点
(1)⼀个表最多只能有1024个分区
(2)如果分区字段中有主键或者唯⼀索引的列，那么所有主键列和唯⼀索引列都必须包含进来
(3)分区表⽆法使⽤外键约束
(4)NULL值会使分区过滤⽆效
(5)所有分区必须使⽤相同的存储引擎
分区的类型
(1)RANGE分区：基于属于⼀个给定连续区间的列值，把多⾏分配给分区
(2)LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配⼀个离散值集合中的某个值来进⾏选择
(3)HASH分区：基于⽤户定义的表达式的返回值来进⾏选择的分区，该表达式使⽤将要插⼊到表中的这些⾏的列值进⾏计算。这个函数可以包含MySQL中有效的、产⽣⾮负整数值的任何表达式
(4)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),toggle to shared stash
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
);
查询时加上时间范围条件效率会⾮常⾼，同时对于不需要的历史数据能很容易的批量删除。
如果数据有明显的热点，⽽且除了这部分数据，其他数据很少被访问到，那么可以将热点数据单独放在⼀个分区，让这个分区的数据能够有机会都缓存在内存中，查询时只访问⼀个很⼩的分区表，能够有效使⽤索引和缓存。
垂直拆分
垂直分库是根据数据库⾥⾯的数据表的相关性进⾏拆分，⽐如：⼀个数据库⾥⾯既存在⽤户数据，⼜存在订单数据，那么垂直拆分可以把⽤户数据放到⽤户库、把订单数据放到订单库。垂直分表是对数据表进⾏垂直拆分的⼀种⽅式，常见的是把⼀个多字段的⼤表按常⽤字段和⾮常⽤字段进⾏拆分，每个表⾥⾯的数据记录数⼀般情况下是相同的，只是字段不⼀样，使⽤主键关联
⽐如原始的⽤户表如下
垂直拆分后如下
垂直拆分的优点
(1)可以使得⾏数据变⼩，⼀个数据块(Block)就能存放更多的数据，在查询时就会减少I/O次数(每次查询时读取的Block 就少)
(2)可以达到最⼤化利⽤Cache的⽬的，具体在垂直拆分的时候可以将不常变的字段放⼀起，将经常改变的放⼀起
(3)数据维护简单
垂直拆分的缺点
(1)主键出现冗余，需要管理冗余列
(2)会引起表连接JOIN操作（增加CPU开销）可以通过在业务服务器上进⾏join来减少数据库压⼒
(3)依然存在单表数据量过⼤的问题（需要⽔平拆分）
(4)事务处理复杂
⽔平拆分（分⽚）

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。