sql优化大全

mysql优化大全
1、通过show status了解各种sql的执行效率
show status;
2、通过explain分析低效率的sql语句
3、建立合适的索引
4、通过show status like 'Handler_%'查看索引的使用情况
Handler_read_key:根据索引读取行的请求数，如果该值很大，说明查询和表都建立了很好的索引，表的索引效率很高。
Handler_read_rnd_key:根据固定位置读取行的请求数，如果执行很多需要排序的查询，该值会很高，你可能有很多需要完整表扫描的查询，或者使用了不正确的索引来多表查询。
Handler_read_rnd_next:从数据文件中读取行的请求数，如果扫描很多表，该值会很大，通常情况下这意味着表没有做好索引，或者查询语句没有使用好索引字段。
5、定期分析表和检查表
analyze table test_table和check table test_table;然后查看Msg_text字段的值是否是ok。
6、定期优化表optimize table test_table;
如果对表的可变字段varchar blob,text等进行了很多更改，则应用optimize优化
在多数的设置中，您根本不需要运行optimize table，即使您对可变长度进行了大量的更新，您也不需要经常运作，每周一次或每月一次即可，只对待定的表运行。
optimize table 只对MyISAM、BDB和InnoDB表起作用。
对MyISAM表，optimize table按如下方式操作；
如果表已经删除或分解了行，则修复表。
如果未对索引页进行分类，则进行分类。
如果表的统计数据没有更新（并且通过对索引进行分类不能实现修复），则进行更新。
7、优化order by or group by 等。


优化数据库对象
1、选择表合适存储引擎：
MyISAM：应用时以读和插入操作为主，支队少量的更新和删除，并且对事物的完成性、并发性要求不是很高。
InnoDB：事务处理，以及并发调价下要求数据的一致性，包括很多的更新和删除。（InnoDB有效地降低删除和更新导致的锁定）。对于支持事务的InnoDB类型的表来说，
影响速度的主要原因是autocommit默认设置是打开的，而且程序没有显示调用begin开始事务，导致每插入一条都自动提交，严重影响了速度。可以在执行sql前调用begin，
多条sql形成一个事务（即使autocommit打开也可以），将大大提高性能。
Memory：数据保存在RAM,快速访问数据。要求表不能太大或者对mysql异常终止后不用恢复数据。
2、优化表的数据类型，选择合适的数据类型：
原则:越小越好，简单最好，所有字段都有默认值，尽量避免null；
例如：数据库表设计时候更小的占磁盘空间尽可能使用更小的数据类型（mediumint要比int更合适）
比如时间字段：datetime和timestamp,datetime占用8个字节，而timestamp占用4个字节，只用了一半，而timestamp表示的范围是1970--2037适合做更新时间。
MySql可以很好的支持大数据量的存取，但是一般来说，数据库中的表越小，它上面执行的查询也就越快。因此在创建表的时候，为了获得更好的性能，我们可以
将表中的字段的宽度设置的尽可能小。例如，在定义邮政编码这个字段的时候，如果将其设置为char(255)，显然给数据库增加了不必要的空间，甚至使用varchar
这种类型也是多余的，因此char(6)就可以很好的完成任务了，同样的，如果可以的话，我们应该使用medimuint而不是bigint来定义整形字段。
另外一个提高效率的方法时尽可能的情况下，应该尽量把字段设置为not null,这样在将来执行查询的时候，数据库不用去比较null的值。
对于某些文本字段，例如“省份”或者“性别”，我们可以将他们定义为enum类型。因为在mysql中，enum类型被当做数值型数据来处理，而数值型数据
被处理起来的速度要比文本类型快的多，这样我们又可以提高数据库的性能。
3、字符串数据类型：char、varchar、text选择区别
1)长度的区别：char范围0-255，varchar最长是64k，但是注意这里的64k是整个row的长度，要考虑到其他的column，还有如果存在not null的时候也会占用以为，对不同的字符集，
有效长度还不一样，比如utf-8的最多21845，还要出去别的column，但是varchar在一般情况下存储都够用了，如果遇到了大文本，考虑使用text，最大能达到4G。
2)效率：基本上是char>varchar>text，但是如果使用的是InnoDB引擎的话，推荐使用varchar。
3)默认值：char和varchar都可以有默认值，text不能指定默认值。
4、mysql中float数据类型的问题
1)float或double列与具体数值类型的数值进行比较，不能使用等式（=）比较，这个是因为浮点数精度的问题，产生误会。
2)对货币等对精度敏感的数据，应该使用定点数表示或存储。
数据库选择合适的数据类型存储还是很有必要的，对性能有一定影响，这里在零碎记录两笔，对int类型的，如果不需要存取负值，最好加上unsigned；对于经常出现在where语句中的
字段，考虑加索引，整形尤其适合加索引。
5、在InnoDB数据表设计中，我们需要注意几点：
1)显示的定义一个int类型自增字段的主键，这个字段可以仅用于主键，不做其他用途。
2)如果不显示定义主键的话，可能会导致InnoDB每次都需要对新数据进行排序，严重损害性能。
3)尽量保证不对主键字段进行更新修改，防止主键字段发生变化，引发数据存储碎片，降低IO性能。
4)如果需要对主键字段进行更新，请将字段转变成一个唯一索引约束字段，另外创建一个没有其他业务意义的自增字段做主键。
5)主键字段尽可能小，能用smallint就不用int,能用int就不用bigint
6)主键字段放在数据表的第一顺序

优化索引
索引是提高数据库性能的常用方法，他可以令数据库服务器以比没有索引快的多的速度检索特定的行，尤其是在查询语句中包含有max(),min()和order by 这些命令的时候，性能挺高十分明显
该对那些字段建立索引那？
一般来说，索引应该简历在那些将用于join，where判断和order by排序的字段上。尽量不要对数据库中某个含有大量重复值得字段建立索引。
对于一个enum类型的字段来说，出现大量重复值是很有可能的情况。一旦这种情况发生，可能降低数据库的性能，我们在创建表的时候可以同时创建合适的索引，
也可以使用alter table 或者create index在以后创建索引。
1)普通索引
普通索引（由关键字key或index定义的索引）的唯一任务就是加快对数据的访问速度，因此，应该只为那些最经常出现的查询条件（where column=.....）或排序条件
（order by column）中的数据列创建索引，只要有可能，就应该选择一个数据最整齐，最紧凑的数据列类创建索引。
2)唯一索引
普通索引允许被索引的数据列包含重复的值。比如说，因为人有可能同名，所以同一个姓名在同一个“员工个人资料”数据表里可能出现两次或更多次。
如果能确定某个数据列将只包含彼此不相同的值，在为这个数据列创建索引的时候就应该用关键字unique把他定义为一个唯一索引。这么做的好处：一是简化了Mysql对这个索引的管理工作。
这个索引一因此变得更有效率，二是mysql会在有新纪录插入数据表的时候，自动检查记录在这个字段的值是否已经在某个记录在这个字段里出现过了。如果是，mysql将拒绝插入那条新纪录，
也就是说，唯一索引可以保证数据记录的唯一性。事实上，在许多场合，人们创建唯一索引的目往往不是提高访问速度，而是为了避免重复数据出现。
3)主索引
必须为主键字段创建一个索引，这个索引就是所谓的“主索引”。主索引与唯一索引的唯一区别就是：前者在定义时使用的关键字是primary 而不是unique
4)外键索引
如果为某个外键字段定义了一个外键约束条件，mysql就会定义一个内部索引来帮助自己以最有效的方式去管理和使用外键约束条件。
5)复合索引
索引可以覆盖多个数据列，如像index(columnA,columnB)索引，这种索引的特点是mysql可以有选择的使用一个这样的索引。如果查询操作只需要用到columnA数据列上的一个索引，就可以使用复合索引
index(columnA,columnB)。不过，这种用法仅适用于在复合索引中排列在前的数据列组合。比如说，index(A,B,C)可以当做A或(A,B)的索引来使用，但不能当做B,C或(B,C)的索引来使用
表锁的问题
跟性能相关的最重要的区别就是MyISAM和InnoDB实现的锁机制不一样！MyISAM使用的是表锁，而InnoDB实现的是行锁。
1)MyISAM为表级锁
由于MyISAM写进程先获得锁，使得读锁请求靠后等待队列，不仅如此，即使请求先到锁等待队列，写请求后到，写锁也会插到读锁请求之前！这是因为MySql认为MySql认为写请求一般读请求要重要。
如果在大量更新操作的情况下，使得很难获得读锁。从而造成阻塞。
索引MyISAM不适合做大量更新操作的原因。
2)InnoDB的行锁是基于索引实现，如果不通过索引访问数据，InnoDB会使用表锁。
表级锁更适合以查询为主，只有少量索引条件更新数据的应用。
行级锁更适合于大量按索引条件并发更新少量不同数据，同时又并发查询，因为只锁定要操作的行，可以多个线程同时操作不同的行（只要不操作其他线程已锁定的行就行）
MySql server服务器配置优化
1)使用show variables了解服务器参数。
2)show status 了解服务器的运行状态，如锁等待情况，当前连接数情况等
3)影响mysql性能的重要参数
key_buffer_size设置索引块的缓存大小，
key_buffer_size是对MyISAM表性能影响最大的一个参数。通过show global status like 'key_read%';可以获得相应信息。
key_read_requests:从缓存读取索引的请求次数。
key_reads：从磁盘读取索引的请求次数。
通常人们认为key_read_requests、key_reads越大越好。
需要适当大的key_buffer_size。table_cache数据库打开表的缓存数量，每个链接进来后都会至少打开一个表缓存，因此table_cache和max_connections有关
例如对于200个并发运行的链接，应该让表的缓存至少是200*N,其中N是可以执行查询的一个连接中的表的最大数
4)还有Innodb_buffer_pool_size等InnoDB参数的设置
磁盘IO优化
对于我们数据库调优来说，磁盘IO优化是首屈一指的调优重点，我们都知道到木桶原理，短板决定整体的好坏，而数据库系统中这个短板正是由于我们使用的硬件设备里
最弱的磁盘锁导致，很多时候，我们会发现系统中IO累的不行，而CPU却在那里闲的等待，主要是由于IO执行相应时间太长，处理读写的速度远远落后于cpu的处理速度，这时我们会
尽可能的让操作放到内存中运行，由于磁盘与cpu的关系，转变成内存与cpu的关系，但是，我们始终不能回避磁盘IO的弱点，优化是必须的，
磁盘搜索是巨大的性能瓶颈，当数据量变得非常大以至于缓存性能变得不可能有效是，该问题变得更加明显，对于大数据库，其中你或多或少的随机访问数据，你可以确信对读取
操作需要至少一次磁盘搜索，写操作需要多次磁盘搜索，要想使该问题最小化，应使用搜索次数较小的磁盘。
1)使用磁盘阵列RAID(廉价磁盘冗余阵列)
Raid就是按照一定的策略将数据分不到若干个物理磁盘上，这样不仅增强了数据存储的可靠性，而且提高数据读写的性能（RAID有不同的级别）
1)读写很频繁的，可靠性要求也很高的，最好RAID 10
2)数据读很频繁，写相对较少的，对可靠行一定要求的，选择Raid 5
3)数据读写都很复杂，但是可靠性要求不高的可以选择Raid 0
2)使用符号链接 分布IO
mysql在默认的情况下，数据库和数据表都存放在参数datadir定义的目录下，这样如果不使用Raid或者逻辑卷，所有的数据都存放在一个磁盘设备上，无法发挥多磁盘并行读写的优势
可以将表和数据库从数据库目录移动到其他的位置并且用指向新位置的符号链接进行替换，推荐的方法只需要将数据库通过符号链接指导不同的磁盘，符号链接表仅作为最后的办法。
符号链接一个数据库的方法是，首先在一些有空闲空间的磁盘上创建一个目录，然后从mysql数据目录中创建他的一个符号链接。
注意：
只有MyISAM表完全支持符号链接。对于其他表类型，如果试图在操作系统中的文件上用前面的任何语句使用符号链接，可能会出现奇怪的问题。
对于MyISAM表的符号链接的处理如下：
1)在数据目录中，一定会有表定义文件、数据文件、索引文件。数据文件和索引文件可以移动到别处和在数据目录中符号链接替代。
表定义文件不能进行符号链接替换。
2)可以分别通过符号链接将数据文件和索引文件指定到不同的目录。
3)如果mysqld没有运行，符号链接可以从服务器命令行使用ln -s手动完成。同样，通过data directory 和index directory 选项创建表，你可以指示运行的MySql服务器执行符号链接
4)myisamchk不用数据文件或索引文件替换符号链接。它直接工作在符号链接指向的文件，任何临时文件创建在数据文件或索引文件所处的目录中。
5)注释：当你删掉一个表时，如果该表使用了符号链接，符号链接和该符号指向的文件都被删除掉，这就是你不应该以系统root用户运行mysqld或允许系统用户对mysql数据库目录有写访问权限的原因
6)如果你用alter table .... rename重命名一个表并且不将表移到另一个数据库，数据库目录中的符号链接被重新命名为一个新名字并且数据文件和索引文件也相应的重命名。
7)如果你用alter table .... rename移动一个表到另外一个数据库中，表移动到另外一个数据库目录，旧的符号链接和其指向的文件被删除，换句话说，新表不再被链接。
8)如果不使用符号链接，你应对mysqld使用--skip-symbolic-links选项确保没有人能够使用mysqld来删除或重新命名数据目录之外的文件。
表符号链还不支持一下操作。
1)alter table 忽略data directory 和index directory表选项。
2)backup table和restore table 不考虑符号链接。
3) .frm 文件必须绝不能是一个符号链接（如前面所述，只有数据和索引文件可以是符链接）。如果试图这样做（例如，生成符号链接）会产生不正确的结果。
3) 禁止操作系统更新文件的atime属性


应用优化
1 )使用连接池
对于访问数据库来说，建立连接的代价比较昂贵，因此，我们有必要建立 " 连接池 " 以提高访问的性能。我们可以把连接当作对象或者设备，池中又有许多已经建立的连接，访 问本来需要与数据库的连接的地方，都改为和池相连，池临时分配连接供访问使用，结果返 回后，访问将连接交还。
2)减少对mysql的访问，使用mem缓存等
3)负载均衡，复制分流查询操作
   利用mysql的主从复制，分流更新操作和查询操作
    1), 创建复制账号：Gran replication slave on *.* to 'rel'@'10.0.1.2' identified by '123456'
    2), 修改主服务器的配置my.conf 开启binlog和设置server-id
    3), 将主服务器的数据一致性恢复到从服务器，保证将要复制的数据时一只的，否则出问题
    4), 在从服务器上修改配置my.conf
    server-id=2
    master-host=10.0.1.3
    master-user='rel'
    master-password='123456'
    master-port='3306'
    5), 从服务器启动slave线程： start slave
show processlist 查看。
4) 分布式cluster 数据库架构

分库分表
1）水平划分
    如果某个表的数据太多，预期有上千条甚至上亿以上，我们可以化整为0：拆表。
    这里就涉及到拆表的算法：
    记录日志的表，也可以按周或者按月来拆。
    记录用户信息的表，按用户id的hash算法来拆。


2）垂直拆分
　　如果表记录数并不多，可能也就2、3万条，但是字段却很长，表占用空间很大，检索表时需要执行大量I/O，严重降低了性能。这个时候需要把大的字段拆分到另一个表，并且该表与原表是一对一的关系。  


分库解决方案原则：
安全性拆分：
将高安全性数据与低安全性数据分库，这样的好处第一是便于维护，第二是高安全性数据的数据库参数配置可以以安全优先，而低安全性数据的参数配置以性能优先。参见运维优化相关部分。
基于业务逻辑拆分
1)根据数据表的内容构成，业务逻辑拆分，便于日常维护和前端调用。
2)基于业务逻辑拆分，可以减少前端应用请求发送到不同数据库服务器的频次，从而减少链接开销。
3)基于业务逻辑拆分，可保留部分数据关联，前端web工程师可在限度范围内执行关联查询。
基于负载压力拆分
    1)基于负载压力对数据结构拆分，便于直接将负载分担给不同的服务器。
2)基于负载压力拆分，可能拆分后的数据库包含不同业务类型的数据表，日常维护会有一定的烦恼。
混合拆分组合
1)基于安全与业务拆分为数据库实例，但是可以使用不同端口放在同一个服务器上。
2)基于负载可以拆分为更多数据库实例分布在不同数据库上
基于安全拆分出A数据库实例，
基于业务拆分出B,C数据库实例，
数据库存在较高负载，基于负载拆分为C1,C2,C3,C4等实例。
数据库服务器完全可以做到 A+B+C1 为一台，C2,C3,C4各单独一台。


分表方案解决原则
1、一般数据量过大或者访问压力过大的数据表需要切分
2、表的字段不宜过多。
纵向分表
   单数据表字段过多，可将频繁更新的整数数据与非频繁更新的字符串数据切分
范例user表，个人简介，地址，QQ号，联系方式，头像
 这些字段为字符串类型，更新请求少； 最后登录时间，在线时常，访问次数，信件数这些字段为整数型字段，更新频繁，可以将后面这些更新频繁的字段独立拆出一张数据表，表内容变少，索引结构变少，读写请求变快。
横向切表
1)等分切表，如哈希切表或其他基于对某数字取余的切表。等分切表的优点是负载很方便的分布到不同服务器；缺点是当容量继续增加时无法方便的扩容，需要重新进行数据的切分或转表。而且一些关键主键不易处理。
2)递增切表，比如每1kw用户开一个新表，优点是可以适应数据的自增趋势；缺点是往往新数据负载高，压力分配不平均。
3)日期切表，适用于日志记录式数据，优缺点等同于递增切表。
4)个人倾向于递增切表，具体根据应用场景决定。
热点数据分表
1)将数据量较大的数据表中将读写频繁的数据抽取出来，形成热点数据表。通常一个庞大数据表经常被读写的内容往往具有一定的集中性，如果这些集中数据单独处理，就会极大减少整体系统的负载。
2)热点数据表与旧有数据关系
可以是一张冗余表，即该表数据丢失不会妨碍使用，因源数据仍存在于旧有结构中。优点是安全性高，维护方便，缺点是写压力不能分担，仍需要同步写回原系统。
可以是非冗余表，即热点数据的内容原有结构不再保存，优点是读写效率全部优化；缺点是当热点数据发生变化时，维护量较大。
具体方案选择需要根据读写比例决定，在读频率远高于写频率情况下，优先考虑冗余表方案。
3)热点数据表可以用单独的优化的硬件存储，比如昂贵的闪存卡或大内存系统。
4)热点数据表的重要指标
热点数据的定义需要根据业务模式自行制定策略，常见策略为，按照最新的操作时间；按照内容丰富度等等。
数据规模，比如从1000万条数据，抽取出100万条热点数据。
热点命中率，比如查询10次，多少次命中在热点数据内。
理论上，数据规模越小，热点命中率越高，说明效果越好。需要根据业务自行评估。
5)热点数据表的动态维护
加载热点数据方案选择
定时从旧有数据结构中按照新的策略获取
在从旧有数据结构读取时动态加载到热点数据
剔除热点数据方案选择
基于特定策略，定时将热点数据中访问频次较少的数据剔除
如热点数据是冗余表，则直接删除即可，如不是冗余表，需要回写给旧有数据结构。
6)通常，热点数据往往是基于缓存或者key-value方案冗余存储，所以这里提到的热点数据表，其实更多是理解思路，用到的场合可能并不多….


9. 反范式设计（冗余结构设计）
反范式设计的概念
无外键，无连表查询。
便于分布式设计，允许适度冗余，为了容量扩展允许适度开销。
基于业务自由优化，基于i/o 或查询设计，无须遵循范式结构设计。
冗余结构设计所面临的典型场景
原有展现程序涉及多个表的查询，希望精简查询程序
数据表拆分往往基于主键，而原有数据表往往存在非基于主键的关键查询，无法在分表结构中完成。
存在较多数据统计需求（count, sum等），效率低下。
冗余设计方案
1）基于展现的冗余设计
为了简化展现程序，在一些数据表中往往存在冗余字段：
举例，信息表message，存在字段fromuid,touid,msg,sendtime四个字段，其中touid+sendtime是复合索引。存在查询为select * from message where touid=$uid order by sendtime desc limit
 0,30;
展示程序需要显示发送者姓名，此时通常会在message表中增加字段fromusername，甚至有的会增加fromusersex，从而无需连表查询直接输出信息的发送者姓名和性别。这就是一种简单的，为了避免连表查询而使用的冗余字段设计。
2）基于展现的冗余设计
涉及分表操作后，一些常见的索引查询可能需要跨表，带来不必要的麻烦。确认查询请求远大于写入请求时，应设置便于查询项的冗余表。
冗余表要点：
数据一致性，简单说，同增，同删，同更新。
可以做全冗余，或者只做主键关联的冗余，比如通过用户名查询uid，再基于uid查询源表。
实战范例1
用户分表，将用户库分成若干数据表
基于用户名的查询和基于uid的查询都是高并发请求。
用户分表基于uid分成数据表，同时基于用户名做对应冗余表。
如果允许多方式登陆，可以有如下设计方法
nuid,passwd,用户信息等等，主数据表，基于uid分表
nukey,ukeytype,uid基于ukey分表，便于用户登陆的查询。分解成如下两个SQL。
uselect uid from ulist_key_13 where ukey=’$username’ and ukeytype=‘login’;
uselect * from ulist_uid_23 where uid=$uid and passwd=’$passwd’;
nukeytype定义用户的登陆依据，比如用户名，手机号，邮件地址，网站昵称等。Ukey+ukeytype 必须唯一。
此种方式需要登陆密码统一，对于第三方connect接入模式，可以通过引申额外字段完成。
实战范例2：用户游戏积分排名
表结构uid,gameid,score参见前文实时积分排行。表内容巨大，需要拆表。
需求1：基于游戏id查询积分排行
需求2：基于用户id查询游戏积分记录
解决方案：建立完全相同的两套表结构，其一以uid为拆表主键，其二以gameid为拆表主键，用户提交积分时，向两个数据结构同时提交。
实战范例3：全冗余查询结构
主信息表仅包括 主键及备注memo字段（text类型），只支持主键查询，可以基于主键拆表。所以需要展现和存储的内容均在memo字段重体现。
对每一个查询条件，建立查询冗余表，以查询条件字段为主键，以主信息表主键id为内容。
日常查询只基于查询冗余表，然后通过in的方式从主信息表获得内容。
优点是结构扩展非常方便，只需要扩展新的查询信息表即可，核心思路是，只有查询才需要独立的索引结构，展现无需独立字段。
缺点是只适合于相对固定的查询架构，对于更加灵活的组合查询束手无策。
3）基于统计的冗余结构
为了减少会涉及大规模影响结果集的表数据操作，比如count，sum操作。应将一些统计类数据通过冗余数据结构保存。
冗余数据结构可能以字段方式存在，也可能以独立数据表结构存在，但是都应能通过源数据表恢复。
实战范例：
论坛板块的发帖量，回帖量，每日新增数据等。
网站每日新增用户数等。
参见Discuz论坛系统数据结构，有较多相关结构。
参见前文分段积分结构，是典型用于统计的冗余结构。
后台可以通过源数据表更新该数字。
Redis的Zset类型可以理解为存在一种冗余统计结构。
4）历史数据表
历史数据表对应于热点数据表，将需求较少又不能丢弃的数据存入，仅在少数情况下被访问。