数据库性能优化-索引与sql相关优化

洋仔聊编程

发布于 2019-01-15 16:36:09

1.8K0

发布于 2019-01-15 16:36:09

文章被收录于专栏：Java开发必知必会

一：前言

什么是索引？

索引是帮助MySQL高效获取数据的数据结构。索引是在存储引擎中实现的，所以每种存储引擎中的索引都不一样。如MYISAM和InnoDB存储引擎只支持BTree索引；MEMORY储存引擎可以支持HASH和BTREE索引。

首先，一些注意点:

mysql每次只使用一个索引
mysql只有在查询中量数据时才会使用索引,查询绝大部分数据会拒绝使用索引,从而进行全表扫描，对于极少量的数据，mysql也会优化为不使用索引
对于联合索引“a b c”,在B+树中单独看b是无序的,在a等值匹配下,看b部分是有序的
索引不会包含null值的列
每次对数据进行操作,数据库也会对索引进行相应的操作
索引优化，注意回表问题！！！！回表问题，请移步：https://blog.csdn.net/csdn___lyy/article/details/81329020
并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段 sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。
索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数较好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

二、优化相关（仅针对InnoDB存储引擎所支持的BTree索引）

1.索引的设计原则

选择唯一性索引
- 唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名现象，从而降低查询速度。
为经常需要排序、分组和联合操作的字段建立索引
- 经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。
为常作为查询条件的字段建立索引
- 如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。
限制索引的数目
- 索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。
尽量使用数据量少的索引
- 如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR（100）类型的字段进行全文检索需要的时间肯定要比对CHAR（10）类型的字段需要的时间要多。a
尽量使用前缀来索引
- 如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。
删除不再使用或者很少使用的索引
- 表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。
选取不经常修改的列
- 对索引列的修改在索引文件中也会进行操作
联合索引中，最好将分辨度大的字段放在前面
- 计算分辨度公式：select distinct(name)/count(name) from table_name
- 值越大说明分辨度越大，则应该放在联合索引的前面

注意：选择索引的最终目的是为了使查询的速度变快。上面给出的原则是最基本的准则，但不能拘泥于上面的准则。需要根据应用的实际情况进行分析和判断，选择最合适的索引方式。

2.sql不使用索引的情况

参与算术运算的索引
参与函数运算的索引
like中“%aaa%”类型索引,而“aaa%”类型使用索引
类型显式隐式转换
如果mysql估计全表扫描比使用索引快时,也不会使用索引
不满足最左匹配原则
用or分割开的条件，or前条件有索引，or后的列没有索引
order by在select 中查询的列，包含索引没有包含的列，也会不使用索引
not in
is null \ is not null ：用其它相同功能的操作运算代替，如：a is not null 改为 a>0 或a>’’等。不允许字段为空，而用一个缺省值代替空值，如申请中状态字段不允许为空，缺省为申请。
记住, 索引只能告诉你什么存在于表中, 而不能告诉你什么不存在于表中.

3.sql优化

尽量减少访问数据库次数，将一些逻辑放在后台代码中处理
- 在不影响业务的情况下，整合简单,无关联和有关联的数据库访问。
- 数据库在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等。没访问一次数据库便会消耗这部分资源。(从缓存读取数据情况除外)
in和exists的不同使用状况
- 如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in, 反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。
- 其实我们区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键)，如果是exists，那么以外层表为驱动表，先被访问，如果是in，那么先执行子查询，所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系了，另外in时不对NULL进行处理。
- in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。
union（去重复）与union all （不去重复）
- 所以union all比union效率高，都满足的情况下尽量使用union all。
where语句后面的条件顺序
- 这一个网络上很多说有用，但我感觉没有作用，因为数据库都会自动优化查询，如果连where后面的条件顺序都不能优化的话也太差劲了，并且通过我个人的测试，在千万数量级的表中，顺序并没有影响，不知道在网上为什么那么多说有影响的，以前的版本不可以？有人测试过这方面，有不同的看法，欢迎在评论区讨论
select 避免使用“*：
- 因为在mysql中，服务器响应给用户的数据通常会很多，由多个数据包组成。但是当服务器响应客户端请求时，客户端必须完整的接收整个返回结果，而不能简单的只取前面几条结果，然后让服务器停止发送。因而在实际开发中，尽量保持查询简单且只返回必需的数据，减小通信间数据包的大小和数量是一个非常好的习惯。
- 使用*可能会导致order by不适用索引。
用truncate替代delete
- Truncate是一个能够快速清空资料表内所有资料的SQL语法。并且能针对具有自动递增值的字段，做计数重置归零重新计算的作用。
- 另外，当你不再需要该表时，用 drop；当你仍要保留该表，但要删除所有记录时，用 truncate；当你要删除部分记录时（always with a WHERE clause), 用 delete.
having只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销。
用>=替代> ：
- 两者的区别在于, 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录。
- 如一个表有100万记录，一个数值型字段A，30万记录的A=0，30万记录的A=1，39万记录的A=2，1万记录的A=3。那么执行A>2与A>=3的效果就有很大的区别了，因为A>2时ORACLE会先找出为2的记录索引再进行比较，而A>=3时ORACLE则直接找到=3的记录索引。
join 代替子查询
- MySQL从4.1版开始支持子查询（一个查询的结果作为另一个select子句的条件），子查询虽然灵活但执行效率不高，因为使用子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表，然后外层查询语句从临时表中查询记录，查询完毕后再撤销这些临时表，因此子查询的速度会相应的受到影响。而连接查询不需要建立临时表其查询速度快于子查询！
使用表的别名(Alias)
- 当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误。
用UNION替换OR (适用于索引列)
- 通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR可能造成全表扫描. 注意, 以上规则只针对多个索引列有效. 如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低.
尽量使用数字型字段
- 若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
尽量避免使用游标
- 因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。
尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。
尽量避免大事务操作，提高系统并发能力。
不要用存储过程了，缺点太多了，完全可以由缓存或者其他方式代替
另外：在优化sql的同时，着重优化系统中的慢查询sql（sql本身层面优化、业务优化后的sql优化等），慢查询sql才是系统的瓶颈所在。

三：其他

1：order by 优化 (来自网络）

mysql支持两种方式的排序，FileSort（不使用索引）和Index（使用索引）。
Index效率比FileSort高，它指MySQL扫描索引本身完成排序。所以对于order by子句，尽量使用Index方式排序，避免使用FileSort方式排序。　　
order by 满足三种情况，会使用Index方式排序
- order by 语句使用索引最左前列
- 使用where子句与order by子句条件列组合满足索引最左前列
- select 查询的列需要全部属于使用索引的索引所包含的列

2:最左前缀原则

通俗的说
- 最左匹配原则针对的是联合索引(name,age,phoneNum) ，B+树是按照从左到右的顺序来建立搜索树的。如（'张三',18,'18668247652'）来检索数据的时候，B+树会优先匹配name来确定搜索方向，name匹配成功再依次匹配age、phoneNum，最后检索到最终的数据。
- 也就是说这种情况下是有三级索引，当name相同，查找age，age也相同时，去比较phoneNum；但是如果拿（18,'18668247652'）来检索时，B+树没有拿到一级索引，根本就无法确定下一步的搜索方向。（'张三','18668247652'）这种场景也是一样，当name匹配成功后，没有age这个二级索引，只能在name相同的情况下，去遍历所有的phoneNum。
- B+树的数据结构决定了在使用联合索引的时候必须遵守最左前缀原则，在创建联合索引的时候，尽量将经常参与查询的字段放在联合索引的最左边。
原则测试：

1.测试用表

2.索引

3.测试sql与解释

explain select * from testIndex where bid = 2 and cid = 3 and did = 4 ;

最左匹配原则，没有使用索引

EXPLAIN select * from testIndex where bid = 1 and aid = 1 and did = 1; -- aid,bid,did 只有aid,bid使用索引，did不适用

ref中只有两个const,表明只有两个字段使用了索引

EXPLAIN select * from testIndex where bid = 1 and aid = 1 and cid = 1; -- aid,bid,did 都使用索引

测试是否正确：mysql会按照联合索引从左往右进行匹配，直到遇到范围查询，如：>,<,between,like等就停止匹配，a = 1 and b =2 and c > 3 and d = 4，如果建立（a,b,c,d）顺序的索引，d是不会使用索引的。但如果联合索引是（a,b,d,c）的话，则a b d c都可以使用到索引，只是最终c是一个范围值。

explain select * from testIndex where aid = 1 and bid = 2 and did = 4 and cid = 3 ;