Optimzation and Index
上一篇 / 下一篇 2017-01-17 12:09:30 / 个人分类：MySQL
提高select性能最好的方法是创建一个或多个列上的索引查询中进行测试。索引条目像指针表行,允许查询迅速确定哪些行匹配条件在WHERE子句中,和其他检索列值的行。所有MySQL数据类型可以被索引。但是不是所有的select问题都可以用索引来解决。
索引介绍
索引用于快速找到与特定的列值的行。如果没有索引,MySQL必须从第一行开始,然后通读整个表找到相关的行。表越大,这个成本就越多。如果表有一个索引的列,MySQL可以快速确定立场寻求中间的数据文件,而不必看的所有数据。这是远远超过按顺序阅读每一行。
大多数MySQL索引(PRIMARY KEY, UNIQUE, INDEX,FULLTEXT)存储在B-trees。除了索引空间数据类型使用R- trees,MEMORY类型数据表也支持hash索引。
Mysql何时需要索引
一般来说，要说什么是索引，还不如介绍什么时候使用索引更能让人印象深刻，对一下操作mysql会使用索引
l 根据where条件快速找到行匹配
l 消除行考虑，如果有多个索引之间的选择，mysql通常会用索引找到最小（最选择性的指数）的行数
l 如果表有多个列索引,可以使用任何左边的前缀索引的优化器来查找行，既“组合索引”。举个例子,如果你有一个三列的索引(col1、col2 col3),有索引搜索功能(col1),(col1,col2)和(col1、col2 col3)
l 涉及join表查询，mysql使用更有效的索引列前提是保证列为相同类型和大小，比如：varchar(10)和char(10)相同，varchar(10)与char(15)则为不同
l 对于二进制字符串列之间的比较,这两列应该使用相同的字符集。例如,比较utf8列latin1排除了使用一个索引列中的一个
l 比较不同的列(例如,比较字符串列时间或数字列)可以防止使用索引,如果没有转换值不能直接比较。对于一个给定的值,如1的数字列,它可能比较等于字符串列中任意数量的值如' 1 ',' 1 ',' 00001 ',或' 01. e1。这排除了使用字符串的任何索引列。
l 查找MIN()或MAX()的值为一个特定的索引列key_col。这是优化的预处理程序检查是否你使用key_part_N =常数key_col之前发生的所有关键部件在索引中。在这种情况下,MySQL是一个键查找每个MIN()和MAX()表达式并换成一个常数。如果所有表达式替换为常数,则查询返回。例如:
SELECT MIN(id),MAX(id) FROM wifi_luckydraw_trophyer WHERE activity_id=50207;
其中ID是primary，activity_id走索引
l 进行排序or分组,如果排序或分组是以左边的前缀使用组合索引(例如,ORDER BY key_part1 key_part2)。如果所有得键值都是用DESC那么这些键值是以倒叙排序。
在某些情况下,可以优化查询来检索值有没有在数据行。（索引,提供所有必要的查询的结果被称为覆盖指数）如果一个查询只使用从表列中包含一些指数,选中的值可以从索引检索树得到更大的速度:
SELECT trophyer_tel FROM wifi_luckydraw_trophyer WHERE activity_id=50207;
l 索引对小表的查询是不太重要的,或者是大表查询大部分或所有的行。查询时需要访问的行,顺序阅读速度比通过使用索引快。顺序读取磁盘寻求最小化,即使所有的查询不是所需的行。
使用primary索引
一个表的主键代表所使用的列或一组列在你的最重要的查询。它有一个相关的指数,用于快速查询性能。从非空优化查询性能好处,因为它不能包含任何NULL值。InnoDB存储引擎,表数据是身体组织的超快的查找和排序基于主键列或列。
如果你的表是大的和重要的,但是没有一个明显的列或一组列作为一个主键,您可以创建一个单独的列中使用自动递增值使用的主键。这些独特的id可以作为指向其他表中相应的行,当你使用外键连接表。
使用Foreign 索引
如果一个表有很多列,你查询列的许多不同的组合,这可能是有效较少用到的数据分割成独立的表有几列,和与他们回到主表表从主复制数字ID列。这样,每一个小表都有一个主键快速查找的数据,你可以查询的列集需要使用连接操作。根据数据如何分布,查询可能执行更少的I / O和占用较少的内存缓存磁盘上的拥挤在一起,因为相关的列。(最大化性能,查询试图尽可能少的数据块从磁盘读取;表只有几列在每个数据块可以容纳更多的行。)
列索引
最常见索引通常是单独一列，存储复制值来自一个数据结构列，允许快速查询行对应的列值，B-Tree数据结构让索引快速找到一个特定的值，一组值或一系列值，相对应的操作例如“=”“<”“<=”between ,in等等，在where子句中。
前缀索引
在col_name(N)语法索引规范中对于一个字符串列,您可以创建一个索引,只使用列的前N个字符。只有一个前缀索引列值以这种方式可以使索引文件要小得多。索引一个BLOB或文本列时,您必须指定一个前缀长度的指数。例如:
create index index_account on aas_account_localaccount (account(3));
在查询account是开头3位是任意运营商开头的手机号，这里只是举例来说明前缀索引的使用，当然这里的account并不适合前缀索引的使用，前缀在BLOB或文本上才能真正体现其价值。
前缀可达到1000字节(767字节为InnoDB表,除非你有innodb_large_prefix集)。
FULLTEXT INDEX：全文索引
全文索引用于全文搜索。只有MyISAM存储引擎支持全文索引和CHAR、VARCHAR和文本列。索引总是发生在整个列和列前缀索引不支持
Indexes in the MEMORY Storage Engine：内存存储引擎索引，内存存储引擎使用哈希索引默认情况下,但也支持BTREE索引。不常用
Multiple-Column Indexes（复合索引，也称组合索引）
组合索引应用比较多，很多应用场景中针对复杂数据筛选处理运用组合索引效果比多个单列索引效果要好很多，MySQL可以创建复合索引(也就是说,多个列上的索引)。索引可能有16列。对于特定的数据类型,您可以创建索引前缀。
组合索引的创建原则：在某个查询where条件中需要用到同一table的多个字段来查询过滤时，这是我们就可以尝试组合索引
比如我们的查询语句是下面这样，实践真理告诉我们组合索引的效率远高于单列索引，及时查询时间上不差上下，但是在CPU占用上组合的必定优于单列索引的消耗。
SELECT tr.* FROM wifi_luckydraw_trophyer tr
WHERE tr.trophyer_tel='15704312004' AND
tr.activity_id=50207 AND tr.trophy_id=150615 AND
tr.trophy_day_str='2016-12-17';
Covering index（覆盖索引）
不使用覆盖索引的
1、select选择的字段中含有不在索引中的字段 ，也即索引没有覆盖全部的列。
2、where 条件中不能含有对索引进行like的操作。
那到底什么时候使用覆盖索引：
查询where条件和select地段均在索引中，一般是个复合索引包含索引字段，跟复合索引的区别就是covering了所有使用的column
查看是否使用覆盖索引：
Extra列出现Using Index提示时，就说明该select查询使用了覆盖索引
Verifying index Usage
检验索引是否有效，最直接的方法是使用explain检测查询语句的执行计划
InnoDB and MyISAM Index Statistics Collection
InnoDB和MyISAM索引信息收集，存储引擎收集表的信息供优化器使用，表统计信息是基于值分组，一个值的行组是一组具有相同键前缀值。为了优化器,一个重要的统计平均值组大小
Mysql平均值组使用在一下方面：
1、估计有多少行参与读取访问
2、估计有多少行的部分加入存储，也即是说，这些行将产生一个操作
这是官网给出的解释，感觉说了很模糊很含蓄，还是不懂，太深层次的不理解没关系
TAG: