数据库优化

　　转自：sql优化提速整理-猴子哥

　　随着业务的不断增加，数据量也在不断的攀升，这样就离不开一个问题：数据查询效率优化。优化主要通过以下几个点来进行总结分析：索引、语句本身、分区存储、分库分表。

  • 索引

　　sql优化首先需要考虑的就是索引，因为添加索引能够很直观的提升查询效率，但是添加索引也需要考虑实际情况。

　　查询执行的大部分开销是I/O，使用索引提高性能的一个主要目标是避免全表扫描，因为全表扫描需要从磁盘上读取表的每一个数据页，如果有索引指向数据值，则查询只需要读少数次的磁盘就行。所以合理的使用索引能加速数据的查询。但是索引并不总是提高系统的性能，带索引的表需要在数据库中占用更多的存储空间，同样用来增删数据的命令运行时间以及维护索引所需的处理时间会更长。

　　SQL索引根据存储关系，分为两类：聚合索引和非聚合索引。

　　聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续。

　　聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致。以字典为例。字典的拼音查询法就是聚集索引，字典的部首查询就是一个非聚集索引。

　　聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个。

　　SQL索引根据使用关系，分为四类：主键索引、唯一索引、普通索引（组合索引）、全文索引。

    • 普通索引(索引值可出现多次)
　　　　CREATE INDEX index_name ON table_name(column_name,column_name) include(score) ;
　　　　alter table table_name add index index_name (column_list) ;
　　　　drop index index_name on table_name ;
    • 唯一索引(索引的值必须是唯一的<除了 null 外，null 可能会出现多次>)
　　　　CREATE UNIQUE INDEX index_name ON table_name (column_name) ;
　　　　alter table table_name add unique (column_list) ;
　　　　alter table table_name drop index index_name ;
    • 主键索引(索引值必须是唯一的，且不能为 null)
　　　　CREATE PRIMARY KEY INDEX index_name ON table_name (column_name) ;
　　　　alter table table_name add primary key (column_list) ;
　　　　alter table table_name drop primary key ;
    • 全文索引

　　　　alter table tbl_name add fulltext index_name (column_list) ;

　　通过show index from table_name g查看表关联的索引。

　　给字段创建索引的分类：

    • 主键列、外键列适合创建聚集索引。
    • 经常作为分组排序的列(group by、 order by)适合创建聚集索引。
    • 频繁更新的列不适合创建聚集索引，可以创建非聚集索引。
    • 列的取值极少不同，不需要创建索引。
    • 列的取值重复率较低，适合创建非聚集索引。
    • 列的取值重复率较高，适合创建聚集索引。
    • 列经常做范围查询，适合创建聚集索引。

　　创建索引的原则：

    • 定义主键的数据列一定要建立索引。
    • 定义有外键的数据列一定要建立索引。
    • 对于经常查询的数据列最好建立索引。
    • 对于需要在指定范围内的快速或频繁查询的数据列建立索引
    • 经常用在WHERE子句中的数据列建立索引。
    • 经常出现在关键字order by、group by、distinct后面的字段，建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致，否则索引不会被使用。
    • 对复合索引，按照字段在查询条件中出现的频度建立索引。
    • 定义为text、image和bit的数据类型的列不要建立索引。
    • 经常存取的列避免建立索引。
    • 查询中很少涉及的列，重复值比较多的列不要建立索引。

　　索引碎片化处理（重构索引）:

　　在实际开发中，有时候会发现新增了索引，但是效率还是没有明显提升，这时候需要考虑是否由于数据的更新编辑产生了索引碎片化，并处理。

检查是否有索引碎片：

---- 检查一个表索引碎片化        
　　use 库名
    DBCC ShowContig(待查询的表)


---- 执行结果实例：
DBCC SHOWCONTIG 正在扫描 'SYS_Confige' 表...
表: 'SYS_Confige' (37575172)；索引 ID: 1，数据库 ID: 7
已执行 TABLE 级别的扫描。
- 扫描页数................................: 7885
- 扫描区数..............................: 986
- 区切换次数..............................: 985
- 每个区的平均页数........................: 8.0
- 扫描密度 [最佳计数:实际计数].......: 100.00% [986:986]
- 逻辑扫描碎片 ..................: 0.01%
- 区扫描碎片 ..................: 1.12%
- 每页的平均可用字节数.....................: 23.0
- 平均页密度(满).....................: 99.72%
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
----数据结构分析：处理
　　Logical Scan Fragmentation-逻辑扫描碎片：无序页的百分比。该百分比应该在0％到10％之间，高了则说明有外部碎片。
 　　解决方式：
	　　解决方式有两种方式:整理索引碎片、重建索引，在实际操过程中建议采用：重建索引。
	　　重建索引的SQL语句：
	　　use 库名
	　　DBCC DBREINDEX(待重建索引的表名称)

  • 查询语句优化

　　查询语句可以借助专业的分析工具来分析，查询语句的优化方向：

    • 查询字段禁止出现 selete * 。
    • where 及 order by 涉及的列建立索引。
    • where子句中避免出现空判断：比如：select from table where num is null
      　此时可以给num赋一个默认值0，语句修改为：select from table where num=0。
    • where 子句中应尽量避免使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
    • where 子句中应尽量避免使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，可以使用union all代替。
    • where 子句中in 和 not in 也要慎用，否则会导致全表扫描，对于连续的数值，能用 between 就不要用 in。
    • 能够用关联查询的不要用exists
    • 查询时避免使用like '%待查询关键字%' 查询。like “%aaa%” 不会使用索引，而like “aaa%”可以使用索引。
    • 不要在索引列上进行运算。这将导致索引失效而全表扫描。
    • 对类型为字符的列进行所引，如果可能，应指定一个前缀禅古，使用短索引。例如，如果有如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。这样可以提高查询速度节省磁盘空间和I/O操作。
    • 在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
    • 尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理

  • 分区存储

　　当单表的数量达到一定量时，为了提高查询效率，需要对数据表分区存储。
　　分区就是把一张表的数据分成多个区块，这些区块可以在同一个磁盘上，也可以在不同的磁盘上，通过减少文件大小，提高IO处理效率，间接提高查询效率
　　分区存储，只是在数据存储上采用分区，但是在逻辑上还是一张表。

　　表分区有以下优点：
　　　　1、改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。
　　　　2、增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用；
　　　　3、维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可；
　　　　4、均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能。
　　缺点：
　　　　分区表相关：已经存在的表没有方法可以直接转化为分区表

  • 分库分表

　　分库分表其实原理也是将一个大表拆分不同的小表，在拆分上有两种拆分方式：

    • 横向拆分：主要针对一个表的字段比较多，可以根据字段的查询频率、更新频率进行分割存储，可以理解为表扩展。
    • 纵向拆分：纵向拆分主要是根据数据量，将数据存储在不同的表，常用的拆分方式有：按照时间、按照哈希等等。

　　分库分表不仅仅会增加数据维护难度，同时也会需要投入大量的开发工作，系统有一定的规模，公司有一定的资源支持才进行分库分表。分库分表两种可以配合使用，比如在分表后，还可以对表进行分区存储。

  引用：SQL索引建立原则和使用






        

	      

	    

	  

	  

	    
      
        【推广】
        免费学中医，健康全家人
      
	  

    

    

          原文地址：https://www.cnblogs.com/amytal/p/11750919.html