当前位置 博文首页 > 、唐城：数据库优化法则，这一篇，很细@|@
目录
一、数据库优化法则
二、数据库优化之SQL语句
三、数据库优化之表结构
四、数据库优化之索引
五、数据库优化之缓存
六、数据库优化之存储引擎
一、数据库优化法则
二、数据库优化之SQL语句
（一）操作符
（二）SQL书写
（三）SQL语句中索引利用
（四）更多
（1）?WHERE子句中的连接顺序：
（2）?SELECT子句中避免使用 *：
（3） 用Where子句替换HAVING子句：
三、数据库优化之表结构
（一）列类型
列类型选择
（二）字符编码
（三）适度拆分
（四）适度冗余
四、数据库优化之索引
（一）索引设计
（二）索引副作用
（三）索引是否越多越好
五、数据库优化之缓存
（一）Query Cache
（二）Binlog Cache
（三）Key Buffer
（四）innodb_buffer_pool_size
（五）innodb_log_buffer_size
六、数据库优化之存储引擎
（一）Mysiam
1-1特性
1-2适用场景
（二）InnoDB
2-1特性
2-2适用场景
（三）NDBCluster
3-1特性
3-2适用场景
?
????????用in操作，数据库会转换成一个多表的连接，如果转换不成功才先执行IN里面的子查询，再查询外层的表记录，如果转换成功则直接采用多个表的连接方式查询。由此可见用IN的SQL至少多了一个转换的过程。
推荐方案：在业务密集的SQL当中尽量不采用IN操作符，用EXISTS?代替。
此操作是强烈不推荐使用的，因为它不能应用表的索引。
推荐方案：用NOT EXISTS?方案代替
判断字段是否为空是不会应用索引的，因为索引是不索引空值的。不能用null作索引，任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下，只要这些列中有一列含有null，该列就会从索引中排除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。
推荐方案：用其它相同功能的操作运算代替，如：a is not null?改为?a>0?或a>’’等。
????????大于或小于操作符一般情况下是不用调整的，因为它有索引就会采用索引查找，但有的情况下可以对它进行优化，如一个表有100万记录，一个数值型字段A，99万记录的A=2，1万记录的A=3。那么执行A>2与A>=3的效果就有很大的区别了，因为A>2时数据库会先找出为2的记录索引再进行比较，而A>=3时数据库则直接找到=3的记录索引。
UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果,如果表数据量大的话可能会导致用磁盘进行排序。实际大部分应用中是不会产生重复的记录。
推荐方案：
????????采用UNION ALL操作符替代UNION，因为UNION ALL操作只是简单的将两个结果合并后就返回。
ORDER BY语句决定了数据库如何将返回的查询结果排序。Order by语句对要排序的列没有什么特别的限制，也可以将函数加入列中。
但是任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。
解决这个问题的办法就是重写order by语句以使用索引，也可以为所使用的列建立另外一个索引，同时应绝对避免在order by子句中使用表达式。
我们在查询时经常在where子句使用一些逻辑表达式，如大于、小于、等于以及不等于等等，也可以使用and(与)、or(或)以及not(非)。NOT可用来对任何逻辑运算符号取反。下面是一个NOT子句的例子:
... where not (status ='VALID')
如果要使用NOT，则应在取反的短语前面加上括号，并在短语前面加上NOT运算符。NOT运算符包含在另外一个逻辑运算符中，这就是不等于(<>)运算符。换句话说，即使不在查询where子句中显式地加入NOT词，NOT仍在运算符中，见下例:
... where status <>'INVALID';
对这个查询，可以改写为不使用NOT：
select * from employee where salary<3000 or salary>3000;虽然这两种查询的结果一样，但是第二种查询方案会比第一种查询方案更快些。第二种查询允许Oracle对salary列使用索引，而第一种查询则不能使用索引。
WHERE子句后面的条件顺序对大数据量表的查询会产生直接的影响。如：?
select * from A where a = 'aaa' and b=1?select * from A where b=1 and a = 'aaa'?? ? ? ? 以上两个SQL中a,b两个字段都没进行索引，所以执行的时候都是全表扫描，第一条SQL的a = 'aaa'条件在记录集内比率为99%，而b=1的比率只为0.5%，在进行第一条SQL的时候99%条记录都进行a及b的比较，而在进行第二条SQL的时候0.5%条记录都进行a及b的比较，以此可以得出第二条SQL的CPU占用率明显比第一条低。
????????采用函数处理的字段不能利用索引。
数据库采用自下而上的顺序解析WHERE子句，根据这个原理，表之间的连接必须写在其他WHERE条件之前，?那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。
解析的过程中,?会将*依次转换成所有的列名, 而这个工作是通过查询数据字典完成的,?这意味着将耗费更多的时间。
避免使用HAVING子句，HAVING?只会在检索出所有记录之后才对结果集进行过滤.。这个处理需要排序、总计等操作。如果能通过WHERE子句限制记录的数目，那就能减少这方面的开销。
对于整数的存储，在数据量较大的情况下，建议区分开?TINYINT / INT / BIGINT?的选择，因为三者所占用的存储空间也有很大的差别。当然，如果数据量较小的数据库，也可以不用严格区分三个整数类型。
定长字段，建议使用CHAR?类型，不定长字段尽量使用VARCHAR，且仅仅设定适当的最大长度，而不是非常随意的给一个很大的最大长度限定。
对于只需要精确到某一天的数据类型，建议使用DATE类型，因为他的存储空间只需要3个字节。
????????纯拉丁字符能表示的内容，没必要选择?latin1?之外的其他字符编码，因为这会节省大量的存储空间。
????????如果我们可以确定不需要存放多种语言，就没必要非得使用UTF8或者其他UNICODE字符类型，这回造成大量的存储空间浪费。
????????有些时候，我们可能会希望将一个完整的对象对应于一张数据库表，这对于应用程序开发来说是很有好的，但是有些时候可能会在性能上带来较大的问题。
????????当我们的表中存在类似于?text或者是很大的?VARCHAR类型的大字段的时候，如果我们大部分访问这张表的时候都不需要这个字段，我们就该将其拆分到另外的独立表中，以减少常用数据所占用的存储空间。
????????这样做的一个明显好处就是每个数据块中可以存储的数据条数可以大大增加，既减少物理?IO?次数，也能大大提高内存中的缓存命中率。
????????被频繁引用且只能通过?Join 2张（或者更多）大表的方式才能得到的独立小字段。这样的场景由于每次Join仅仅只是为了取得某个小字段的值，Join到的记录又大，会造成大量不必要的IO，完全可以通过空间换取时间的方式来优化。
????????不过，冗余的同时需要确保数据的一致性不会遭到破坏，确保更新的同时冗余字段也被更新。
如果我们仅仅只是这样告诉对方的：“帮我确认一本数据库类别的讲述?MySQL?的叫做《MySQL性能调优与架构设计》的书是否在藏”，结果又会如何呢？朋友只能一个大类区域一个大类区域的去寻找“数据库”类别，然后再找到?“MySQL”范畴，再看到我们所需是否在藏。由于我们少说了一个“计算机类”，朋友就必须到每一个大类去寻找。
如果我们是这样说的：“帮我确认一本讲述?MySQL?的数据库范畴的计算机丛书，叫做《MySQL性能调优与架构设计》，看是否在藏”。如果这位朋友并不知道计算机是一个大类，也不知道数据库属于计算机大类，那这位朋友就悲剧了。首先他得遍历每个类别确认“MySQL”存在于哪些类别中，然后从包含?“MySQL”?书籍中再看有哪些是“数据库”范畴的（有可能部分是讲述PHP或者其他开发语言的），然后再排除非计算机类的（虽然可能并没有必要），然后才能确认。
????????如果我们还有这样一个需求：“帮我将图书馆中所有的计算机图书借来”。朋友如果通过索引来找，每次都到索引柜找到计算机书籍所在的区域，然后从书架上搬下一格（假设只能以一格为单位从书架上取下，类比数据库中以block/page为单位读取），取出第一本，然后再从索引柜找到计算机图书所在区域，再搬下一格，取出一本…?如此往复直至取完所有的书。如果他不通过索引来找又会怎样呢？他需要从地一个书架一直往后找，当找到计算机的书，搬下一格，取出所有计算机的书，再往后，直至所有书架全部看一遍。
????????在这个过程中，如果计算机类书籍较多，通过索引来取所花费的时间很可能要大于直接遍历，因为不断往复的索引翻阅所消耗的时间会非常长。
????????如果我们的朋友不知道“数据库”这个类别可以属于“计算机”这个大类，或者图书馆的索引系统中这两个类别属性并没有关联关系，又会怎样呢？也就是说，朋友得到的是2个独立的索引，一个是告知“计算机”这个大类所在的区域，一个是“数据库”这个小类所在的区域（很可能是多个区域），那么他只能二者选其一来搜索我的需求。即使朋友可以分别通过2个索引检索然后自己在脑中取交集再找，那这样的效率实际过程中也会比较低下。
如果我们的这个图书馆只是一个进出中转站，里面的新书进来后很快就会转发去其他图书馆而从这个馆藏中“清除”，那我们的索引就只会不断的修改，而很少会被用来查找图书。
如果我们的书籍量少到只有几本或者就只有一个书架，索引并不会带来什么作用，甚至可能还会浪费一些查找索引所花费的时间。
如果我们的图书馆只有一个10平方的面积，现在连放书架都已经非常拥挤，而且馆藏还在不断增加，我们还能考虑创建索引吗？
?
当我们打开了?Query Cache?功能，MySQL在接受到一条select语句的请求后，MySQL?会直接根据HASH算法将select语句以字符串方式进行hash，然后到Query Cache?中直接查找是否已经缓存。
如果已经在缓存中，该select请求就会直接将数据返回。
当然，Query Cache?也有一个致命的缺陷，那就是当某个表的数据有任何任何变化，都会导致所有引用了该表的select语句在Query Cache?中的缓存数据失效。所以，当我们的数据变化非常频繁的情况下，使用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）。
Binlog Cache?用于在打开了二进制日志记录功能的环境，用于短时间内临时缓存binlog数据。
一般来说，如果我们的数据库中没有什么大事务，写入也不是特别频繁，2MB～4MB是一个合适的选择。
但是如果我们的数据库大事务较多，写入量比较大，可与适当调高binlog_cache_size。
????????key_buffer_size?参数用来设置用于缓存?MyISAM存储引擎中索引文件的内存区域大小。如果我们有足够的内存，这个缓存区域最好是能够存放下我们所有的?MyISAM?引擎表的所有索引，以尽可能提高性能。
????????用来设置用于缓存?InnoDB?索引及数据块的内存区域大小，类似于?MyISAM?存储引擎的?key_buffer_size?参数。
????????简单来说，当我们操作一个?InnoDB?表的时候，返回的所有数据或者去数据过程中用到的任何一个索引块，都会在这个内存区域中走一遭。
????????所以如果我们有足够的内存，尽可将该参数设置到足够大，将尽可能多的?InnoDB?的索引及数据都放入到该缓存区域中，直至全部。我们可以通过?(Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100%?计算缓存命中率，并根据命中率来调整?innodb_buffer_pool_size?参数大小进行优化。
????????这是?InnoDB?存储引擎的事务日志所使用的缓冲区。
????????InnoDB?在写事务日志的时候，为了提高性能，也是先将信息写入?Innodb Log Buffer?中，当满足一定条件（比如日志缓冲区写满）之后，才会将日志写到文件（或者同步到磁盘）中。
????????可以通过?innodb_log_buffer_size?参数设置其可以使用的最大内存空间。
?
?
????????????????如上，技术在于交流流通，/握手
原文作者：高一级花阿梨喜欢吃榴莲
https://blog.csdn.net/weixin_40459875/article/details/80949844
?五年从程序员到架构师！这是我见过史上最好的程序员职业规划
别慌，在Java面试的时候，面试官会这样问关于框架的问题？
想要实时关注更多干货好文，扫描下图关注或微信搜索【万言尽书上】关注公众公众号：
