sql 优化

什么是索引?

 

索引用来快速地寻找那些具有特定值的记录，所有MySQL索引都以B-树的形式保存。如果没有索引，执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录，直至找到符合要求的记录。表里面的记录数量越多，这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引，MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。如果表有1000个记录，通过索引查找记录至少要比顺序扫描记录快100倍。

 

http://www.cnblogs.com/oldhorse/archive/2009/11/16/1604009.html

 

索引的类型

 

 索引类型包括：普通索引，唯一性索引， 主键（主键是一种唯一性索引，但它必须指定为“PRIMARY KEY”） 和全文索引
   单列索引和多列索引（复合索引）索引可以是单列索引，也可以是多列索引。对相关的列使用索引是提高SELECT操作性能的最佳途径之一。
   MySQL可以为多个列创建索引。一个索引可以包括15个列。对于某些列类型，可以索引列的左前缀，列的顺序非常重要。

    最左前缀
    多列索引有一个特点，即最左前缀（Leftmost Prefixing）。假如有一个多列索引为key(firstname lastname age)，
    当搜索条件是以下各种列的组合和顺序时，MySQL将使用该多列索引：
    firstname，lastname，age
    firstname，lastname
    firstname
    也就是说，相当于还建立了key(firstname lastname)和key(firstname)。
    索引主要用于下面的操作：
        Ø 快速找出匹配一个WHERE子句的行。
        Ø 删除行。当执行联接时，从其它表检索行。
        Ø 对具体有索引的列key_col找出MAX()或MIN()值。由预处理器进行优化，检查是否对索引中在key_col之前发生所有关键字元素
          使用了WHERE key_part_# = constant。在这种情况下，MySQL为每个MIN()或MAX()表达式执行一次关键字查找，并用常数替换它。
          如果所有表达式替换为常量，查询立即返回。例如：
          SELECT MIN(key2), MAX (key2)  FROM tb WHERE key1=10;
        Ø 如果对一个可用关键字的最左面的前缀进行了排序或分组(例如，ORDER BY key_part_1,key_part_2)，排序或分组一个表。
          如果所有关键字元素后面有DESC，关键字以倒序被读取。
        Ø 在一些情况中，可以对一个查询进行优化以便不用查询数据行即可以检索值。如果查询只使用来自某个表的数字型并且构成某些关键字
          的最左面前缀的列，为了更快，可以从索引树检索出值
        Ø 在通配符的情况下：select * from sales_rep where surname like ‘ser%‘

 

 

如何建立索引

 

MySQL支持很多数据类型，选择合适的数据类型存储数据对性能有很大的影响。通常来说，可以遵循以下一些指导原则：
    (1)越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快。
    (2)简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂。在MySQL中，应该用内置的日期和时间数据类型，
    而不是用字符串来存储时间；以及用整型数据类型存储IP地址。
    (3)尽量避免NULL：应该指定列为NOT NULL，除非你想存储NULL。在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、
    索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。

    选择合适的标识符是非常重要的。选择时不仅应该考虑存储类型，而且应该考虑MySQL是怎样进行运算和比较的。一旦选定数据类型，
    应该保证所有相关的表都使用相同的数据类型。
    (1)整型：通常是作为标识符的最好选择，因为可以更快的处理，而且可以设置为AUTO_INCREMENT。
    (2)字符串：尽量避免使用字符串作为标识符，它们消耗更好的空间，处理起来也较慢。而且，通常来说，字符串都是随机的，所以它们在索引
    中的位置也是随机的，这会导致页面分裂、随机访问磁盘，聚簇索引分裂（对于使用聚簇索引的存储引擎）。

    单键索引还是组合索引?
    在一般的应用场景中，只要不是其中某个过滤字段在大多数场景下能过滤90%以上的数据（这种可能性很小），而其他的过滤字段会频繁的更新，
    一般更倾向于创建组合索引，尤其是在并发量较高的场景下。因为当并发量较高的时候，即使只为每个Query节省了很少的 IO 消耗，但因为执
    行量非常大，所节省的资源总量仍然是非常可观的。 当然，创建组合索引并不是说就须要将查询条件中的所有字段都放在一个索引中，还应该
    尽量让一个索引被多个 Query 语句利用，尽量减少同一个表上的索引数量，减少因为数据更新带来的索引更新成本，同时还可以减少因为索引
    所消耗的存储空间。

 

索引和SQL语句

 

1. 当结果集只有一行数据时使用LIMIT 1
    2. 避免SELECT *，始终指定你需要的列
    从表中读取越多的数据，查询会变得更慢。他增加了磁盘需要操作的时间，还是在数据库服务器与WEB服务器是独立分开的情况下。
    你将会经历非常漫长的网络延迟，仅仅是因为数据不必要的在服务器之间传输
    3. 使用连接（JOIN）来代替子查询(Sub-Queries)
       连接（JOIN）.. 之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。
    4. 使用ENUM、CHAR 而不是VARCHAR，使用合理的字段属性长度
    5. 尽可能的使用NOT NULL
    6. 固定长度的表会更快
    7. 拆分大的DELETE 或INSERT 语句
    8. 查询的列越小越快

    Where条件
    在查询中，WHERE条件也是一个比较重要的因素，尽量少并且是合理的where条件是很重要的，尽量在多个条件的时候，
    把会提取尽量少数据量的条件放在前面，减少后一个where条件的查询时间。
    有些where条件会导致索引无效：
       Ø where子句的查询条件里有！=，MySQL将无法使用索引。这种情况可以用<>来代替
       Ø where子句使用了Mysql函数的时候，索引将无效，比如：select * from tb where left(name, 4) = 'xxx'
       Ø 使用LIKE进行搜索匹配的时候，这样索引是有效的：select * from tbl1 where name like 'xxx%'，而like '%xxx%' 时索引无效 

 

如何优化索引

 

explain 返回了一行记录，它包括了 select语句中用到的各个表的信息。这些表在结果中按照mysql即将执行的查询中读取的顺序列出来。mysql用一次扫描多次连接
  （single- sweep,multi-join）的方法来解决连接。这意味着mysql从第一个表中读取一条记录，然后在第二个表中查找到对应的记录，然后在第三个表 中查找，依次
   类推。当所有的表都扫描完了，它输出选择的字段并且回溯所有的表，直到找不到为止，因为有的表中可能有多条匹配的记录下一条记录将从该表读 取，再从下一个表
   开始继续处理。在mysql version 4.1中，explain输出的结果格式改变了，使得它更适合例如 union语句、子查询以及派生表的结构。更令人注意的是，它新增了2
   个字段： id和 select_type。当你使用早于mysql4.1的版本就看不到这些字段了。

    table：显示这一行的数据是关于哪张表的
    type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL
    possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句
    key： 实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）
    来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引
    key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好
    ref：显示索引的哪一列被使用了，如果可能的话，是一个常数
    rows：MYSQL认为必须检查的用来返回请求数据的行数
     Extra：关于MYSQL如何解析查询的额外信息。将在表4.3中讨论，但这里可以看到的坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，
      结果是检索会很慢
     extra列返回的描述的意义
         Distinct:一旦MYSQL找到了与行相联合匹配的行，就不再搜索了
         Not exists: MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了
         Range checked for each Record（index map:#）:没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返
         回行。这是使用索引的最慢的连接之一
         Using filesort: 看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行
         的行指针来排序全部行
         Using index: 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候
         Using temporary 看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上
         Where used 使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有
         问题不同连接类型的解释（按照效率高低的顺序排序）
         system 表只有一行：system表。这是const连接类型的特殊情况
         const:表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数，因为MYSQL先读这个值然后把它当做常数来对待
         eq_ref:在连接中，MYSQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用
         ref:这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。
         这个类型严重依赖于根据索引匹配的记录多少—越少越好
         range:这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况
         index: 这个连接类型对前面的表中的每一个记录联合进行完全扫描（比ALL更好，因为索引一般小于表数据）
         ALL:这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免