数据库优化四个方面

数据库优化四个方面

1. Sql索引优化
2. 数据库表结构
3. 系统配置
4. 硬件资源

总结：按顺序优化从高到低，成本从低到高。

1、 Sql索引优化

1.1、什么是索引

    索引分为两种：聚集索引和非聚集索引。索引的目的就是为了提高Sql的性能，加快查询速度，减少系统响应的时间。
    
    聚集索引和 非聚集索引 的根本区别就在于表记录的排列顺序是否和索引的排列顺序是否一致。举个例子：拿字典去查汉字，会根据拼音查询或者部首查询。
拼音查询都是 a-z ，具有连续性，那拼音查询就是聚集索引。而部首查询，比如要查“木”字旁的林 和杉，可能林在100页，杉在1000页，物理上不连续，这就是非聚集索引。

1.2如何创建索引

CREATE [UNIQUE][CLUSTERED | NONCLUSTERED]  INDEX  index_name 
ON {table_name | view_name} [WITH [index_property [,....n]]

说明：

 1.UNIQUE：建立唯一索引。如果不指明采用的索引结构，则SQL Server系统默                            认为采用非聚集索引结构。
 2.CLUSTERED ：聚集索引
 3.NONCLUSTERED：非聚集索引。

1.3删除索引

DROP INDEX Table_Name. Index_Name 

1.4创建索引的时机

1.4.1常查询数据建立索引

    我们知道，对于数据的查询，一般是通过条件查询（WHERE语句），而如果条件查询中的字段上没有建立索引的话，就会进行一次全表扫描，这是非常耗时的操作。

    CREATE TABLE TEXT{
    ID INT,
    NAME VARCAHR(20),
    SEX VARCHAR(10)
    };
SELECT name FROM test WHERE id=100; // 这条语句会导致全表查询，然后再进行where语句筛选

这时候我们需要对ID进行建立索引

    CREATE INDEX INDEX_NAME
    ON TEXT(ID)

这样再进行查询语句就会使用B+树进行索引查询得到id=100的索引叶节点，然后根据查到的聚合索引的值，进行二次查找得到name

更优化的一个索引

    CREATE INDEX INDEX_NAME
    ON TEXT(ID,NAME)

这样就不需要进行二次查询，而是直接完全通过索引B+树的叶子节点内容久能得到name。

1.4.2 最左前缀原则

跟据上面的TEXT表 ，建立一个组合索引

    CREATE INDEX TABLE_NAME
    ON TEXT(ID,NAME,SEX)

先看几个SQL语句

SELECT ID FROM TEXT WHERE ID = "1111";
SELECT ID FROM TEXT WHERE NAME ="小红"
SELECT ID FROM TEXT WHERE ID = "1111" AND NAME ="小红"
SELECT ID FROM TEXT WHERE ID = "1111" AND NAME = "小红" AND SEX = "男"
SELECT ID FROM TEXT WHERE ID = "1111" AND SEX = "男"

我们逐渐分析每条SQL语句的查询性能。

1. 查询字段ID在建立的索引里，而条件语句中WHERE ID='1111'也在索引里，而且符合最左前缀规则，索引毫无疑问，这次查询会直接使用索引查询，而且查询数据在索引叶子节点里，查询效率是很高的。
2. 查询字段ID在建立的索引里，而条件语句中WHERE NAME ="小红"也在索引里，但不符合最左前缀原则。会进行全索引查询，然后再比较WHERE语句条件
3. 查询字段ID在建立的索引里，而条件语句中WHERE ID = "1111" AND NAME ="小红"也在索引里。符合最左原则。
4. 查询字段ID在建立的索引里，而条件语句中WHERE ID = "1111" AND NAME ="小红" AND SEX = "男" 也在索引里。符合最左原则。
5. 查询字段ID在建立的索引里，而条件语句中WHERE NAME ="小红"也在索引里，但不符合最左前缀原则。会进行全索引查询，然后再比较WHERE语句条件