数据库优化
以下大部分引用自最近看到的博客，链接在最下方，文章只是对自己看过的数据库优化方面的博客做一个复习整理
数据库优化主要可以从四个层面进行考虑，服务层面、系统层面、数据库层面、代码层面。
对数据库优化总的来说就是针对不同需求对数据库的“合理使用”
-
根据服务层面：配置MySQL性能优化参数
修改MySQL配置文件my.cnf（Linux /etc/mysql）# MySQL非缓存参数变量介绍及修改 back_log=500 # 默认是50，MySQL连接数据最大连接请求max_connections，如果连接数据到达max_connections，新来的请求挥别存在堆栈中，直到某一连接释放资源。back_log 即是堆栈数量，如果等待连接的数量超过back_log，将不被授予连接资源。back_log值不能超过TCP/IP连接的侦听队列的大小（1024）。若超过则无效。对于linux系统back_log 推荐设置为小于512的整数。 wait_timeout=1800 # 单位为s，默认是8小时，修改为30分钟。MySQL客户端的数据库连接闲置最大时间值。就是当你的MySQL连接闲置超过一定时间后将会被强行关闭。设置这个值的意义在于，比如你的服务器有大量的MySQL链接请求，由于你的程序的原因有大量的连接请求空闲啥事也不干，白白占用内存资源。在设置之前你可以查看一下你的MYSQL的状态（可用show processlist)，如果经常发现MYSQL中有大量的Sleep进程，则需要 修改wait_timeout值了。 max_connections=3000 # 默认是151，最大为16384 。max_connections是指MySql的最大连接数，如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，当然这建立在机器能支撑的情况下，因为如果连接数越多，介于MySql会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。 max_user_connections=800 # max_user_connections是指每个数据库用户的最大连接，针对某一个账号的所有客户端并行连接到MYSQL服务的最大并行连接数。简单说是指同一个账号能够同时连接到mysql服务的最大连接数。设置为0表示不限制。 thread_concurrency=64 # thread_concurrency应设为CPU核数的2倍，thread_concurrency的值的正确与否, 对mysql的性能影响很大, 在多个cpu(或多核)的情况下，错误设置了thread_concurrency的值, 会导致mysql不能充分利用多cpu(或多核), 出现同一时刻只能一个cpu(或核)在工作的情况。 skip-name-resolve # 默认被注释掉，禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意，如果开启该选项，则所有远程主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求 skip-networking # 默认被注释掉且不建议开启，如果WEB服务器是以远程连接的方式访问MySQL数据库服务器则不要开启该选项！否则将无法正常连接！ default-storage-engine=InnoDB # 设置默认存储引擎 # MySQL缓存参数缓存变量介绍及修改 # 数据库属于IO密集型的应用程序，其主职责就是数据的管理及存储工作。而我们知道，从内存中读取一个数据库的时间是微秒级别，而从一块普通硬盘上读取一个 IO是在毫秒级别，二者相差3个数量级。所以，要优化数据库，首先第一步需要优化的就是IO，尽可能将磁盘IO转化为内存IO。 # 全局缓存：启动MySQL时就要分配并且总是存在的全局缓存。目前有：key_buffer_size(默认值：402653184,即384M)、innodb_buffer_pool_size(默认值：134217728即：128M)、innodb_additional_mem_pool_size（默认值：8388608即：8M）、innodb_log_buffer_size(默认值：8388608即：8M)、query_cache_size(默认值：33554432即：32M)等五个。共：560M。 key_buffer_size=400M # key_buffer_size是用于索引块的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)，对MyISAM表性能影响最大的一个参数。如果你使它太大，系统将开始换页并且真的变慢了。严格说是它决定了数据库索引处理的速度，尤其是索引读的速度。对于内存在4GB左右的服务器该参数可设置为256M或384M。 innodb_buffer_pool_size=1024M # innodb_buffer_pool_size:主要针对InnoDB表性能影响最大的一个参数。功能与Key_buffer_size一样。InnoDB占用的内存，除innodb_buffer_pool_size用于存储页面缓存数据外，另外正常情况下还有大约8%的开销，主要用在每个缓存页帧的描述、adaptive hash等数据结构，如果不是安全关闭，启动时还要恢复的话，还要另开大约12%的内存用于恢复，两者相加就有差不多21%的开销。另外InnoDB和 MyISAM 存储引擎不同， MyISAM 的 key_buffer_size 只能缓存索引键，而 innodb_buffer_pool_size 却可以缓存数据块和索引键。适当的增加这个参数的大小，可以有效的减少 InnoDB 类型的表的磁盘 I/O 。 innodb_additional_mem_pool_size=20M # innodb_additional_mem_pool_size 设置了InnoDB存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小，所以当我们一个MySQL Instance中的数据库对象非常多的时候，是需要适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率的。这个参数大小是否足够还是比较容易知道的，因为当过小的时候，MySQL会记录Warning信息到数据库的error log中，这时候你就知道该调整这个参数大小了。 innodb_log_buffer_size=20M # innodb_log_buffer_size 这是InnoDB存储引擎的事务日志所使用的缓冲区。InnoDB在写事务日志的时候，为了提高性能，也是先将信息写入Innofb Log Buffer中，当满足innodb_flush_log_trx_commit参数所设置的相应条件(或者日志缓冲区写满)之后，才会将日志写到文件 (或者同步到磁盘)中。 InnoDB 将日志写入日志磁盘文件前的缓冲大小。理想值为 1M 至 8M。大的日志缓冲允许事务运行时不需要将日志保存入磁盘而只到事务被提交(commit)。 因此，如果有大的事务处理，设置大的日志缓冲可以减少磁盘I/O。 query_cache_size=40M # query_cache_size: 主要用来缓存MySQL中的ResultSet，也就是一条SQL语句执行的结果集，所以仅仅只能针对select语句。当我们打开了 Query Cache功能，MySQL在接受到一条select语句的请求后，如果该语句满足Query Cache的要求(未显式说明不允许使用Query Cache，或者已经显式申明需要使用Query Cache)，MySQL会直接根据预先设定好的HASH算法将接受到的select语句以字符串方式进行hash，然后到Query Cache中直接查找是否已经缓存。也就是说，如果已经在缓存中，该select请求就会直接将数据返回，从而省略了后面所有的步骤(如SQL语句的解析，优化器优化以及向存储引擎请求数据等)，极大的提高性能。根据MySQL用户手册，使用查询缓冲最多可以达到238%的效率。当然，Query Cache也有一个致命的缺陷，那就是当某个表的数据有任何任何变化，都会导致所有引用了该表的select语句在Query Cache中的缓存数据失效。所以，当我们的数据变化非常频繁的情况下，使用Query Cache可能会得不偿失Query Cache的使用需要多个参数配合，其中最为关键的是query_cache_size和query_cache_type，前者设置用于缓存 ResultSet的内存大小，后者设置在何场景下使用Query Cache。在以往的经验来看，如果不是用来缓存基本不变的数据的MySQL数据库，query_cache_size一般256MB是一个比较合适的大小。当然，这可以通过计算Query Cache的命中率(Qcache_hits/(Qcache_hits+Qcache_inserts)*100))来进行调整。 query_cache_type可以设置为0(OFF)，1(ON)或者2(DEMOND)，分别表示完全不使用query cache，除显式要求不使用query cache(使用sql_no_cache)之外的所有的select都使用query cache，只有显示要求才使用query cache(使用sql_cache)。如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲. 如果Qcache_hits的值也非常大，则表明查询缓冲使用非常频繁，此时需要增加缓冲大小；根据命中率(Qcache_hits/(Qcache_hits+Qcache_inserts)*100))进行调整，一般不建议太大，256MB可能已经差不多了，大型的配置型静态数据可适当调大。 # 局部缓存：除了全局缓冲，MySql还会为每个连接发放连接缓冲。个连接到MySQL服务器的线程都需要有自己的缓冲。大概需要立刻分配256K，甚至在线程空闲时，它们使用默认的线程堆栈，网络缓存等。事务开始之后，则需要增加更多的空间。运行较小的查询可能仅给指定的线程增加少量的内存消耗，然而如果对数据表做复杂的操作例如扫描、排序或者需要临时表，则需分配大约read_buffer_size，sort_buffer_size，read_rnd_buffer_size，tmp_table_size 大小的内存空间. 不过它们只是在需要的时候才分配，并且在那些操作做完之后就释放了。有的是立刻分配成单独的组块。tmp_table_size 可能高达MySQL所能分配给这个操作的最大内存空间了 read_buffer_size=4M # 默认值：2097144即2M，sort_buffer_size是MySql执行排序使用的缓冲大小。如果想要增加ORDER BY的速度，首先看是否可以让MySQL使用索引而不是额外的排序阶段。如果不能，可以尝试增加sort_buffer_size变量的大小 read_rnd_buffer_size=8M # 默认值：8388608即8M，read_rnd_buffer_size 是MySql的随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySql会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySql会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。 tmp_table_size=16M # 默认值：8388608 即：16M，tmp_table_size是MySql的heap （堆积）表缓冲大小。所有联合在一个DML指令内完成，并且大多数联合甚至可以不用临时表即可以完成。大多数临时表是基于内存的(HEAP)表。具有大的记录长度的临时表 (所有列的长度的和)或包含BLOB列的表存储在硬盘上。如果某个内部heap（堆积）表大小超过tmp_table_size，MySQL可以根据需要自动将内存中的heap表改为基于硬盘的MyISAM表。还可以通过设置tmp_table_size选项来增加临时表的大小。也就是说，如果调高该值，MySql同时将增加heap表的大小，可达到提高联接查询速度的效果。 record_buffer # record_buffer每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描，你可能想要增加该值。默认数值是131072 # 其他缓存 table_cache #table_cache指定表高速缓存的大小。每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。通过检查峰值时间的状态值Open_tables和Opened_tables，可以决定是否需要增加table_cache的值。如果你发现open_tables等于table_cache，并且opened_tables在不断增长，那么你就需要增加table_cache的值了注意，不能盲目地把table_cache设置成很大的值。如果设置得太高，可能会造成文件描述符不足，从而造成性能不稳定或者连接失败。 thread_cache_size=64 # 服务器线程缓存，这个值表示可以重新利用保存在缓存中线程的数量,当断开连接时如果缓存中还有空间,那么客户端的线程将被放到缓存中,如果线程重新被请求，那么请求将从缓存中读取,如果缓存中是空的或者是新的请求，那么这个线程将被重新创建,如果有很多新的线程，增加这个值可以改善系统性能 -
系统层面：优化数据 表结构、字段类型、字段索引、分表，分库、读写分离等
由于MySQL数据库是基于行(Row)存储的数据库，而数据库操作 IO 的时候是以 page(block)的方式，也就是说，如果我们每条记录所占用的空间量减小，就会使每个page中可存放的数据行数增大，那么每次 IO 可访问的行数也就增多了。反过来说，处理相同行数的数据，需要访问的 page 就会减少，也就是 IO 操作次数降低，直接提升性能。此外，由于我们的内存是有限的，增加每个page中存放的数据行数，就等于增加每个内存块的缓存数据量，同时还会提升内存换中数据命中的几率，也就是缓存命中率。 数据库操作中最为耗时的操作就是 IO 处理，大部分数据库操作 90% 以上的时间都花在了 IO 读写上面。所以尽可能减少 IO 读写量，可以在很大程度上提高数据库操作的性能。 我们无法改变数据库中需要存储的数据，但是我们可以在这些数据的存储方式方面花一些心思。下面的这些关于字段类型的优化建议主要适用于记录条数较多，数据量较大的场景，因为精细化的数据类型设置可能带来维护成本的提高，过度优化也可能会带来其他的问题。1.数字类型：非万不得已不要使用DOUBLE，不仅仅只是存储长度的问题，同时还会存在精确性的问题。同样，固定精度的小数，也不建议使用DECIMAL，建议乘以固定倍数转换成整数存储，可以大大节省存储空间，且不会带来任何附加维护成本。对于整数的存储，在数据量较大的情况下，建议区分开 TINYINT / INT / BIGINT 的选择，因为三者所占用的存储空间也有很大的差别，能确定不会使用负数的字段，建议添加unsigned定义。当然，如果数据量较小的数据库，也可以不用严格区分三个整数类型。
2.字符类型：非万不得已不要使用 TEXT 数据类型，其处理方式决定了他的性能要低于char或者是varchar类型的处理。定长字段，建议使用 CHAR 类型，不定长字段尽量使用 VARCHAR，且仅仅设定适当的最大长度，而不是非常随意的给一个很大的最大长度限定，因为不同的长度范围，MySQL也会有不一样的存储处理。
3.时间类型：尽量使用TIMESTAMP类型，因为其存储空间只需要 DATETIME 类型的一半。对于只需要精确到某一天的数据类型，建议使用DATE类型，因为他的存储空间只需要3个字节，比TIMESTAMP还少。不建议通过INT类型类存储一个unix timestamp 的值，因为这太不直观，会给维护带来不必要的麻烦，同时还不会带来任何好处。
4.ENUM & SET：对于状态字段，可以尝试使用 ENUM 来存放，因为可以极大的降低存储空间，而且即使需要增加新的类型，只要增加于末尾，修改结构也不需要重建表数据。如果是存放可预先定义的属性数据呢?可以尝试使用SET类型，即使存在多种属性，同样可以游刃有余，同时还可以节省不小的存储空间。
5.LOB类型：强烈反对在数据库中存放 LOB 类型数据，虽然数据库提供了这样的功能，但这不是他所擅长的，我们更应该让合适的工具做他擅长的事情，才能将其发挥到极致。
BLOB与TEXT是为了存储极大的字符串而设计的数据类型，采用二进制与字符串方式存储。mysql对待这两个类型可谓煞费苦心，mysql会把这两种类型的值当做一个独立的对象处理，存储引擎在存储时通常会做特殊处理，当BLOB与TEXT的值太大时，InnoDB会使用专门的“外部”存储区域来进行存储，此时每个值在行内会采用1~4个自己存储指针，在外部存储区域存储实际值。Mysql对BLOB与TEXT类型进行排序的处理上与其他类型不同，只针对最前面的max_sort_length自己进行排序，如果只需要对前面的更少的字节进行排序，那么可以通过设置max_sort_length参数或者substring(value,length)来截取部分字符串。在实际使用中应该慎用这两个类型，尤其是会创建临时表的情况下，因为如果临时表大小超过max_heap_table_size或者tmp_table_size，就会将临时表存储在磁盘上，进而导致整体速度下降！
数据库分库分表思路：
关系型数据库本身比较容易成为系统瓶颈，单机存储容量、连接数、处理能力都有限。当单表的数据量达到1000W或100G以后，由于查询维度较多，即使添加从库、优化索引，做很多操作时性能仍下降严重。此时就要考虑对其进行切分了，切分的目的就在于减少数据库的负担，缩短查询时间。 数据库分布式核心内容无非就是数据切分（Sharding），以及切分后对数据的定位、整合。数据切分就是将数据分散存储到多个数据库中，使得单一数据库中的数据量变小，通过扩充主机的数量缓解单一数据库的性能问题，从而达到提升数据库操作性能的目的。
-
垂直切分/纵向切分
- 垂直分库
根据业务耦合性，将关联度低的不同表存储在不同的数据库、 按业务分类进行独立划分。 将不同模块的数据表分库存储，模块间不相互关联查询，如果有必须通过数据冗余或应用层二次加工来解决。 - 垂直分表
是基于数据库中的"列"进行，某个表字段较多，可以新建一张扩展表，将不经常用或字段长度较大的字段拆分出去到扩展表中。在字段很多的情况下（例如一个大表有100多个字段），通过"大表拆小表"，更便于开发与维护，也能避免跨页问题，MySQL底层是通过数据页存储的，一条记录占用空间过大会导致跨页，造成额外的性能开销。另外数据库以行为单位将数据加载到内存中，这样表中字段长度较短且访问频率较高，内存能加载更多的数据，命中率更高，减少了磁盘IO，从而提升了数据库性能。 - 优点：
- 解决业务系统层面的耦合，
- 业务清晰 能对不同业务的数据进行分级管理、维护、监控、扩展等
- 高并发场景下，垂直切分一定程度的提升IO、数据库连接数、单机硬件资源的瓶颈
- 缺点
- 部分表无法join，只能通过接口聚合方式解决，提升了开发的复杂度
- 分布式事务处理复杂
- 依然存在单表数据量过大的问题（需要水平切分 ）
- 垂直分库
-
水平切分/横向切分
当一个应用难以再细粒度的垂直切分，或切分后数据量行数巨大，存在单库读写、存储性能瓶颈，这时候就需要进行水平切分了。-
库内分表、分库分表。 是根据表内数据内在的逻辑关系，将同一个表按不同的条件分散到多个数据库或多个表中，每个表中只包含一部分数据，从而使得单个表的数据量变小，达到分布式的效果。 库内分表只解决了单一表数据量过大的问题，但没有将表分布到不同机器的库上，因此对于减轻MySQL数据库的压力来说，帮助不是很大，大家还是竞争同一个物理机的CPU、内存、网络IO，最好通过分库分表来解决。
-
优点
- 不存在单库数据量过大、高并发的性能瓶颈，提升系统稳定性和负载能力
- 应用端改造较小，不需要拆分业务模块
-
缺点
- 跨分片的事务一致性难以保证
- 跨库的join关联查询性能较差
- 数据多次扩展难度和维护量极大
-
水平切分后同一张表会出现在多个数据库/表中，每个库/表的内容不同。几种典型的数据分片规则为：
-
根据数值范围
按日期将不同月甚至是日的数据分散到不同的库中；将userId为1 ~ 9999的记录分到第一个库，10000 ~ 20000的分到第二个库，以此类推。某种意义上，某些系统中使用的"冷热数据分离"，将一些使用较少的历史数据迁移到其他库中，业务功能上只提供热点数据的查询，也是类似的实践。
优点：- 单表大小可控 、
- 天然便于水平扩展，后期如果想对整个分片集群扩容时，只需要添加节点即可，无需对其他分片的数据进行迁移 、
- 使用分片字段进行范围查找时，连续分片可快速定位分片进行快速查询，有效避免跨分片查询的问题。
缺点：
- 热点数据成为性能瓶颈。连续分片可能存在数据热点，例如按时间字段分片，有些分片存储最近时间段内的数据，可能会被频繁的读写，而有些分片存储的历史数据，则很少被查询
-
根据数值取模
将表根据一个字段切分放入n个库，余0放入第一个库，余1放入第二个库以此类推。 这样同一个用户的数据会分散到同一个库中。
优点：- 数据分片相对比较均匀，不容易出现热点和并发访问的瓶颈
缺点：
- 后期分片集群扩容时，需要迁移旧的数据
- 容易面临跨分片查询的复杂问题。如果频繁用到的查询条件中不是用于用来进行分库的字段时，将会导致无法定位数据库，从而需要同时向4个库发起查询，分库反而成为拖累。
分布式存储结构下通过数值取模进行分表分库扩容时避免数据迁移的解决方案：使用一致性哈希（下面取自公众号程序员小灰）
一致性哈希(DHT)可以有效解决分布式存储结构下动态增加和删除节点所带来的问题。按Mysql单表存储500万条记录来算，暂时不必分库，单库30个分表是比较合适的水平分表方案。
普通的取模分表操作：
对userID进行取模操作，为了便于描述，简化为5个分表：
一致性哈希的建立过程：
1.首先，我们把全量的缓存空间当做一个环形存储结构。环形空间总共分成2^32个缓存区，在Redis中则是把缓存key分配到16384个slot。
2.每一个缓存key都可以通过Hash算法转化为一个32位的二进制数，也就对应着环形空间的某一个缓存区。我们把所有的缓存key映射到环形空间的不同位置。
3.我们的每一个缓存节点（Shard）也遵循同样的Hash算法，比如利用IP做Hash，映射到环形空间当中。
4.如何让key和节点对应起来呢？很简单，每一个key的顺时针方向最近节点，就是key所归属的存储节点。所以图中key1存储于node1，key2，key3存储于node2，key4存储于node3。
一致性哈希的增删：
1.增加节点当缓存集群的节点有所增加的时候，整个环形空间的映射仍然会保持一致性哈希的顺时针规则，所以有一小部分key的归属会受到影响。
有哪些key会受到影响呢？图中加入了新节点node4，处于node1和node2之间，按照顺时针规则，从node1到node4之间的缓存不再归属于node2，而是归属于新节点node4。因此受影响的key只有key2。
最终把key2的缓存数据从node2迁移到node4，就形成了新的符合一致性哈希规则的缓存结构。
2.删除节点当缓存集群的节点需要删除的时候（比如节点挂掉），整个环形空间的映射同样会保持一致性哈希的顺时针规则，同样有一小部分key的归属会受到影响。
有哪些key会受到影响呢？图中删除了原节点node3，按照顺时针规则，原本node3所拥有的缓存数据就需要“托付”给node3的顺时针后继节点node1。因此受影响的key只有key4。
最终把key4的缓存数据从node3迁移到node1，就形成了新的符合一致性哈希规则的缓存结构。
注意：- 一致性哈希中所说的迁移并不是直接的数据迁移，而是在查询的时候去找顺时针的后继节点，因缓存未命中而刷新缓存
- 缓存节点都是按照ip来hash到环形空间的，那么就可能产生分布不均的问题，比如所有的key不巧都属于同一节点
为了避免这种情况，一致性哈希引入了虚拟节点的概念，所谓虚拟节点就是基于原来的物理节点映射出N个子节点，最后把所有子节点映射到环形空间上
如上图所示，假如node1的ip是192.168.1.109，那么原node1节点在环形空间的位置就是hash（“192.168.1.109”）。
我们基于node1构建两个虚拟节点，node1-1 和 node1-2，虚拟节点在环形空间的位置可以利用（IP+后缀）计算，例如：
hash（“192.168.1.109#1”），hash（“192.168.1.109#2”）
此时，环形空间中不再有物理节点node1，node2，只有虚拟节点node1-1，node1-2，node2-1，node2-2。由于虚拟节点数量较多，缓存key与虚拟节点的映射关系也变得相对均衡了。 - 为什么一致性哈希更多用于像Redis这样的缓存数据库呢？
由于分布式缓存系统的节点部署变化更频繁，而传统关系型数据库的分库分表相对稳定。不过在MySQL分库分表过程中依然可以采用一致性哈希的思想。虽然会导致处理逻辑更加复杂，但是可以避免动态水平扩展时候的数据迁移。
-
- 主从复制 (Master-Slave)
主从复制：是一种数据备份的方案。
简单来说，是使用两个或两个以上相同的数据库，将一个数据库当做主数据库，而另一个数据库当做从数据库。在主数据库中进行相应操作时，从数据库记录下所有主数据库的操作，使其二者一模一样。 - 读写分离 (MySQL-Proxy)
读写分离：是一种让数据库更稳定的的使用数据库的方法。
是在有从数据库的情况下使用，当主数据库进行对数据的增删改也就是写操作时，将查询的任务交给从数据库。 - 为什么要使用主从分离和读写操作呢？
主从复制：
1、当主数据库出现问题时，可以当从数据库代替主数据库，可以避免数据的丢失。
2、可以进行读写分离
读写分离：
1、避免从数据库进行写操作而导致的主从数据库数据不一致的情况，因为当主从数据库数据不一致时，那么从数据库最主要的备份任务就没有意义了。
2、减轻主数据库的压力。因为进行写操作更耗时，所以如果不进行读写分离的话，写操作将会影响到读操作的效率。
-
数据库层面：优化sql语句，合理使用字段索引
尽量避免耗时操作：-
带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎执行，耗费资源的排序(SORT)功能。DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序
-
如果无需排除重复值或是操作集无重复则用UNION ALL， UNION更费事（因为要比较） UNION因为会将各查询子集的记录做比较，故比起UNION ALL ，通常速度都会慢上许多。一般来说，如果使用UNION ALL能满足要求的话， 务必使用UNION ALL。
-
避免在WHERE子句中使用in，not in，or 或者having。（where工作在分组前，having工作在分组后） 可以使用 exist 和not exist代替 in和not in。
可以使用表链接代替 exist。
Having可以用where代替，如果无法代替可以分步处理。
in 、not in会导致全表扫描，连续的数据能用BETWEEN AND就不用in -
避免 ‘%abc%’这样的模糊查询，
可以考虑全文本搜索（MyISAM），可以考虑用正则表达式，LIKE左端不稳定，%在左侧不会使用索引， -
避免在where的时候使用 != <>，否则会进行全表扫描
-
应尽量避免在 where 子句中使用 or 来连接条件，or连接的两个条件，如果有一个没索引就会导致全表扫描。
-
Update 语句，如果只更改1、2个字段，不要Update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志。
-
对于多张大数据量（这里几百条就算大了）的表JOIN，要先分页再JOIN，否则逻辑读会很高，性能很差。
-
使用绑定变量， 当创建一个绑定变量 SQL 时，客户端会向服务器发送一个SQL语句的原型。服务器端收到这个SQL语句框架后，解析并存储这个SQL语句的部分执行计划，返回个客户端一个 SQL 语句处理句柄。以后每次执行这类查询，客户端都指定使用这个句柄。
INSERT INTO tb1(col1, col2, col3) VALUES(?,?,?);
可以通过向服务器端发送各个问号的取值和这个SQL的句柄来执行一个具体的查询。反复使用这样的方式执行具体的查询，正是使用绑定变量的优势所在。具体如何发送取值参数和SQL句柄，这各个客户端的编程语言有关。 -
应尽量避免在where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。
如：
select id from t where num/2=100
应改为:
select id from t where num=100*2 -
应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。
-
尽量使用表变量来代替临时表。
-
避免频繁创建和删除临时表，以减少系统表资源的消耗。
-
临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。
-
在新建临时表时，如果一次性插入数据量很大，那么可以使用select into 代替create table，避免造成大量log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。
-
如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先truncate table ，然后drop table ，这样可以避免系统表的较长时间锁定。
-
尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。 与临时表一样，游标并不是不可使用。对小型数据集使用游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。
-
尽量避免大事务操作，提高系统并发能力。
不要在建立的索引的数据列上进行下列操作:
（1）避免对索引字段进行计算操作
（2）避免在索引字段上使用not，<>，!=
（3）避免在索引列上使用IS NULL和IS NOT NULL
（4）避免在索引列上出现数据类型转换
（5）避免在索引字段上使用函数
（6）避免建立索引的列中使用空值。
使用索引的情况： 1、表的主键、外键必须有索引；
2、数据量超过300的表应该有索引；
3、经常与其他表进行连接的表，在连接字段上应该建立索引；
4、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；
5、索引应该建在选择性高的字段上；
6、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；
7、复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：
a、正确选择复合索引中的主列字段，一般是选择性较好的字段；
b、复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；
c、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；
d、如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；
e、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；
8、频繁进行数据操作的表，不要建立太多的索引；
9、删除无用的索引，避免对执行计划造成负面影响；
什么情况下不推荐使用索引？
1.数据唯一性差（一个字段的取值只有几种时）的字段不要使用索引。比如性别，只有两种可能数据。 当索引列有大量数据重复时，SQL查询可能不会去利用索引
2.频繁更新的字段不要使用索引，比如logincount登录次数，频繁变化导致索引也频繁变化，增大数据库工作量，降低效率
3.字段不在where语句出现时不要添加索引,如果where后含IS NULL /IS NOT NULL/ like ‘%输入符%’等条件，不建议使用索引，只有在where语句出现，mysql才会去使用索引
4.where 子句里对索引列使用不等于（<>），使用索引效果一般
无法有效利用已建索引的案例：
1、如果条件中有or（注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引）
2、对于多列索引，
3、like查询是以%开头，
4、where 子句里对索引列上有数学运算
-
-
代码层面：使用缓存和NoSQL数据库方式存储，如MongoDB/ Memcached/Redis来缓解高并发下数据库查询的压力
redis+mysql -
减少数据库操作次数，尽量使用数据库访问驱动的批处理方法
当我们有多条sql语句需要发送到数据库执行的时候，有两种发送方式，一种是执行一条发送一条sql语句给数据库,另一个种是发送一个sql集合给数据库，也就是发送一个批sql到数据库。很显然两者的数据库执行效率是不同的，我们发送批处理sql的时候数据库执行效率要高。所以我们有必要掌握mysql数据库的sql批处理发送方式方法。java的话用JDBC，C++的话用 bulk insert语句和bcp实用工具 -
不常使用的数据迁移备份，避免每次都在海量数据中去检索
-
提升数据库服务器硬件配置，或者搭建数据库集群
通过数据库集群实现主从读写分离
1.物理服务器增加，负荷增加
2.主从只负责各自的写和读，极大程度的缓解X锁和S锁争用
3.从库可配置myisam引擎，提升查询性能以及节约系统开销
4.从库同步主库的数据和主库直接写还是有区别的，通过主库发送来的binlog恢复数据，但是，最重要区别在于主库向从库发送binlog是异步的，从库恢复数据也是异步的
5.读写分离适用与读远大于写的场景，如果只有一台服务器，当select很多时，update和delete会被这些select访问中的数据堵塞，等待select结束，并发性能不高。 对于写和读比例相近的应用，应该部署双主相互复制
6.可以在从库启动是增加一些参数来提高其读的性能，例如–skip-innodb、–skip-bdb、–low-priority-updates以及–delay-key-write=ALL。当然这些设置也是需要根据具体业务需求来定得，不一定能用上
7.分摊读取。假如我们有1主3从，不考虑上述1中提到的从库单方面设置，假设现在1 分钟内有10条写入，150条读取。那么，1主3从相当于共计40条写入，而读取总数没变，因此平均下来每台服务器承担了10条写入和50条读取（主库不 承担读取操作）。因此，虽然写入没变，但是读取大大分摊了，提高了系统性能。另外，当读取被分摊后，又间接提高了写入的性能。所以，总体性能提高了，说白 了就是拿机器和带宽换性能。
8.MySQL复制另外一大功能是增加冗余，提高可用性，当一台数据库服务器宕机后能通过调整另外一台从库来以最快的速度恢复服务，因此不能光看性能，也就是说1主1从也是可以的。 -
防止SQL注入（非性能优化）
使用JDBC PreparedStatement按位插入或查询、正则表达式过滤（非法字符串过滤）
扫一扫
347
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
