为Mysql创建最优的索引

Mysql  /  houtizong 发布于 3年前   169

索引对于良好的性能非常关键，尤其是当数据规模越来越大的时候，索引的对性能的影响越发重要。索引经常会被误解甚至忽略，而且经常被糟糕的设计。索引优化应该是对查询性能优化最有效的手段了，索引能够轻易将查询性能提高几个数量级，最优的索引会比较好的索引性能要好2个数量级。1 索引的类型  (1) B-Tree    不出意外，这里提到的索引都是指 B-Tree索引，InnoDB 则使用 B+Tree索引1 B-Tree索引的限制  1 如果不是按照索引的最左列开始查找，则无法使用索引。假设一张表索引中包含了last_name,first_name,birthday三列，  这个组合索引无法用于查找first_name为bill的人，也无法查找某个特定生日的人，因为这两列都不是最左数据列。    2 不能跳过索引中的列这个组合索引无法用于查找last_name为fuck ，birthday为特定日期的数据，因为跳过了某些索引。如果不指定first_namemysql只能使用索引的第一列。  3 如果查询中有某个列的查询范围，则其右边所有列都无法使用索引优化查询。例如 where last_name="fuck" and first_name like "kk%" and birthday = '2015-06-18'这个查询只能使用索引的前两列，因为这里like是一个范围条件。如果范围查询列值的数量有限，可以通过使用多个等于条件来替代范围。综上 索引列的顺序是多么重要。在优化的时候，可以使用不同的顺序完成不同类型要求的优化。    (2) 哈希索引    存储引擎会对所有的索引列计算出一个哈希码，哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。哈希槽是有顺序的，数据行确是混乱的。查询指令：select name from test wnere fname = 'peter';mysql先计算peter的哈希值，并使用该值寻找对应的记录指针，之后比较其值是否为peter以确保准确。哈希索引的限制1 哈希索引的数据并不是按照索引值顺序存储的，所以无法用于排序。2 哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部来计算哈希值的。例如  索引列(A,B) 只查询数据列A 则无法使用该索引。    3 Hash索引只支持等值查询，不支持范围查询。4 如果hash冲突很多的话，一些索引维护操作的代价也会很高。由于这些限制，hash索引只适合某些特定的场合，而一旦适合hash索引，则他带来的性能提升将非常明显。比如在数据仓库中有一种星型schema,就非常适合hash索引。InnoDB 引擎有一个特殊的功能叫做 自适应哈希索引。当InnoDb注意到某些hash索引值被使用的非常频繁，    则她会在内存中基于B-Tree索引之上再创建一个hash索引，这样就让B-Tree索引也具有哈希索引的一些优点，比如快速的hash查找。这是一个可关闭的内部的 用户无法控制 配置的行为。2 索引的优点  1 索引大大减少额服务器需呀扫描的数据量  2 索引可以帮助服务器避免排序和临时表  3 索引可以将随机I/O变为顺序I/O.  3 索引是最好的解决方案吗？  索引并不总是最好的工具，总的来说只有党所有带来的收益大于其带来的额外工作时，索引才是有效的。  对于非常小的表，全表扫描会更有效，对于大中型的表，索引就会非常有效，但是对于特大型的表，建立索引和使用的代价  将随之增长，在这种情况下则需要一种技术可以直接区分出查询需要的一组数据，而不是一条一条记录的匹配，例如  使用分区技术。这将在后面章节讨论。    如果表的数量非常多，可以建立一个元数据信息表，用来查询需要用到的某些特性。例如执行那些需要聚合多个  应用分布在多个表的数据查询，则需要记录哪个用户信息存储在哪个表中的元数据，这样在查询时就可以直接忽略那些          不包含指定用户信息的表。对于大型系统，这是一个常用的技巧，事实上，Infobright 就是使用类似的实现          对于TB级别的数据，定位单条记录的意义不大，所以经常会使用块级别元数据技术带替代索引。  4 禁止犯下的错误        索引不是独立的，这是混蛋逻辑。        禁止犯下面的错误，          1 select id from table where id +1 = 4;            这类sql 完全可以避免，这样的查询不能被mysql解析，也不会使用到查询。          2 select name form table where sum(keg) - sum(lo) <=0;            为什么我们要把计算放进查询条件？ 难道我们脑子进水了吗？5 前缀索引      alter table table_name add key (name(7))前缀索引更小更快，但是前缀索引order by 和group by 也无法使用前缀索引做覆盖扫描6 多列索引       世人常猥琐，一个常见的错误就是为每个列创建独立的索引，或者按照错误的顺序创建多列索引。       create table t (q1 int,q2 int,q3 int                   key(q1),key(q2),key(q3))       这种索引策略是非常错误的，这样一来最好的情况下也只能做到一星，也就是把所有符合的数据找出来，有时如果无法       设计一个三星索引，还不如忽略掉where子句，集中精力优化索引列的顺序，或者创建一个全覆盖索引。       我们的schema设计的真他妈的烂！ 如果几年后的我设计出这种表，还不如去吃屎。              select name from table_name where id1 = 1 or id2 = 1;   如果在老版本的mysql中将会执行的比较糟糕，但是在新版本中(5.0之后) 将会使用两个索引扫描的联合。   explain select name from table_name where id1 = 1 or id2 = 1 \G;       格式化之后将会看到使用了联合。     索引合并策略是一种优化的结果，实际上反映的是表上的索引建的有多糟糕。               索引合并需要注意的事情：    1 当出现服务器对多个索引做相交操作时(多个and条件) 通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。2 服务器对多个索引做联合操作时，需要耗费更多的cpu和内存资源。所以，如果再执行计划中看到有索引合并，应该好好检查一下查询和表的结构。   7 选择合适的索引列顺序       1 当不需要考虑排序和分组时 将选择性最高的列放在最前面通常是最好的。       2 分析查询  select name from table_name where sid = 2 and cid = 120;          我们该创建一个联合索引(sid,cid)还是颠倒一下顺序？先来执行一下          select sum(sid=2),sum(cid=120) from table_name;          sum(sid=2):2100            sum(cid=120) :30          根据经验法则 cid的选择性更高，cid应该放在前面      select count(distinct sid)/count(*),count(distinct cid)/count(*),count(*) from table_name\G;    cid的选择性更高，cid应该放在前面 alter table table_name add key(cid,sid);  8 聚簇索引      如何创建聚簇索引      如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样一个索引，InnoDB会隐式的定义一个主键      来作为聚簇索引。            聚簇索引的优点       1 可以把相关的数据保存在一起。   2 数据访问更快。聚簇索引将索引和数据保存在同一个B-Tree中，因此更快速查询。   3 使用覆盖索引扫描的查询可以直接使用叶节点中的主键值。   当然他也有自己的缺点，自己去找。   9 覆盖索引  如果索引的叶子节点中已经包含要查询的数据，那么久没有必要再回表查询。如果一个索引包含所有需要查询的字段的  值，我们就称之为 覆盖索引。覆盖索引时非常有用的工具，能够极大的提高性能。如果查询只需要扫描索引  而无需回表，会带来很多好处：    1 索引条目通常远小于数据行大小，如果只需要读取索引，那么mysql就会极大地减少数据访问量。这对缓存的负载非常重要，因为这种情况下响应时间大部分话费在数据拷贝上，索引相对较小，更容易全部放入内存中。2 因为索引是按照列值顺序存储的，所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要上的多。3 一些存储引擎MyISAM在内存中只缓存索引，数据则依赖操作系统缓存，因此要访问数据需要一次系统调用，这可能会  导致严重的性能问题，尤其是那些系统调用占了数据访问中的最大开销的场景。4 由于InnoDB聚簇索引，覆盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子节点中保存了行的主键值，  所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询。        不是所有的索引都可以成为覆盖索引。覆盖索引必须要存储索引列的值，当发起一个被索引覆盖的查询，也叫做索引覆盖查询时，在explain 的extra列可以看到 Using index 的信息，        例如在表tab 有一个多列索引(std,fld) ，mysql 只需要访问这两列，就可以使用这个索引锁覆盖索引。select std,fld from tab;         错误案例  select * from tab_temp where keyd = 405 and title like '%8899%'; 这里索引无法覆盖该查询，也就是说这条查询无法使用覆盖索引查询，原因如下：1 没有任何索引能够覆盖这个查询，因为查询从表中选择了所有的列，二没有任何索引覆盖了所有的列。2 mysql 不能再索引中执行like操作，这是底层存储引擎api的限制，mysql5.5版本只允许在索引中做简单比较操作。  mysql 能在索引中做最左前缀匹配的like操作，因为该操作可以转换简单的比较操作，但是如果是通配符  开头的like比较。存储引擎就无法做比较匹配，这种情况下mysql服务器只能提取数据行的值而不是索引值来做比较。  我们可以通过重新设计索引和调整sql 来使用覆盖索引。  设计联合索引(keyd,title,proid)  select * from tab join(select proid from tab where keyd = 405 and title like '%8899%')as t on t.proid=tab.proid;  explain 一下 会发现 extra 列里 出现了Using index . 使用了部分覆盖索引查询。  内部查询中使用了覆盖索引，结合外部一起完成查询。    InnoDB 可以借助主键列来完成覆盖索引查询。  例如表 txt 主键列 t_id,索引列 t_name, 查询 select t_id,t_name from txt where t_name ='jk';  explain 一下会发现 extra 列出现了Using index ,也就是说这条查询使用了覆盖索引。  因为InnoDB 二级索引的叶子节点都包含了主键的值，这意味着InnoDB二级索引可以利用包含的主键完成覆盖索引查询。mysql5.6版本 索引条件推送这个特性  已经在很大程度上完善了这种查询方式。  10 使用索引扫描来做排序        1 如果可能，涉及索引时应该尽可能的使索引既能满足排序又用于查找行。          只有当索引的列顺序和order by 子句的顺序完全一致，并且所有列的排序方向都一样时，mysql才能够使用索引来对结果做排序。  如果需要关联多张表，只有当order by子句引用的字段全部为第一个表时，才能使用索引做排序。          有一种情况，可以在不满足索引的最左前缀的情况下使用索引排序。  在表 rental 存在索引(rental_date,i_id,c_id)  select rental_date,i_id,c_id from rental                      where rental_date = '2015-06-18' order by i_id,c_id;  查看执行计划 explain ,没有发现filesort文件排序。即使没有满足索引的最左前缀的要求，也可以用于索引排序，这是因为索引的第一类被指定为常量。  再看示例 select rental_date,i_id,c_id from rental                      where rental_date = '2015-06-18' order by i_id;  查看执行计划 ，同样使用了索引排序，这是因为索引的第一列使用了常量，中间一列与第一列形成了组合，满足了索引最左前缀的要求，利用了索引排序。    下面看看不能使用索引排序的查询  索引的第一列是范围查询 无法使用索引排序  where rental_date > '2015-06-18' order by i_id,c_id;    索引列的顺序满足最左前缀，但是正序或反序不一致。  where rental_date = '2015-06-18' order by i_id desc,c_id asc;  不满足最左前缀要求  where rental_date = '2015-06-18' order by cun,i_id;  使用索引做排序的一个最重要的做法是当查询同时有order by 字句和limit字句的时候。  11 压缩索引(前缀压缩)   压缩索引，顾名思义就是索引的压缩，先保留索引块中的第一个值，然后将其他值和第一个值进行比较得到相同前缀的字节数和   剩余的不同后缀部分，把这部分存储起来即可。例如索引快的第一个值是 fuck,第二个值是fuckyou,那么第二个前缀的压缩值   是类似于 "4,you".这样的存储方式可以让索引所占的空间只占用之前的十分之一，但是对于cpu密集型的应用，其代价就是会变得相当慢   对于一些倒序操作，效率会下降的更厉害。    12 冗余和重复索引   重复索引是指在相同的列上按照相同的顺序创建相同类型的索引。应该避免这类索引，发现之后应该立即移除。   例如create table test(id,primary key,a int,unique(id),index(id)),这里重复了三次索引，   在同一列建立类型相同的索引，MySQL的唯一限制和主键限制都是通过索引实现的。   但是如果类型不同，并不算重复索引，因此 key(col) 和 fulltextkey(col) 是有很好的理由共同存在的。      冗余索引和重复索引有所不同，如果创建了(A,B)，再创建索引(A) 就是冗余索引，   因为这只是前一个索引的前缀索引，因此(A,B)也可以当做索引(A)来使用，但是如果在创建(B,A)则不是冗余索引，索引   (B) 也不算冗余索引，因为(B)不是(A,B)的最左前缀索引。      冗余索引通常发生在为表添加新索引的时候，例如，有人可能会增加一个新的索引(A,B) 而不是扩展已经存在的索引(A).   还有一种情况是将一个索引扩展至(A,ID) 其中ID是主键，对于InnoDB来说，主键已经包含在二级索引中了，所以这种策略是一种重复。      大多数情况下都不需要冗余索引，尽量在已有的索引上扩展。      过多的索引会让查询的成本更高，插入的时间更久，解决冗余和重复的索引的方法很简单，删除就可以。   可以借助工具 common_schema ,该工具通过分析表结构来找出冗余和重复的索引。          建议使用percona工具箱中的pt-upgrade工具来仔细检查计划中的索引变更。       对于服务器可能永远不会使用的索引，建议完全删除。   最有效的方法是在percona Server 或者MariaDB中先打开userstates服务器变量，   然后让服务器正常运行一段时间，再通过查询information_schema.index_statistics就能   查到每个索引的使用频率。      另外还可以使用percona Tookit中的pt-index-usage，该工具可以读取查询日志，并对日志中的   每条查询explain操作，然后打印出索引和查询的报告。这个工具不仅可以找出哪些索引是未使用的，还可以了解查询的执行计划。   该工具还可以将结果写入到MySQL的表中。    13 索引和锁       关于InnoDB、索引和锁有一些很少人知道的细节：InnoDB在二级索引史上使用共享(读)锁   但访问主键索引需要排它(写)锁。这消除了使用覆盖索引的可能性，并且使得select for update   比 lock in share mode或非锁定查询要慢很多。考虑表上所有的选项。当设计索引时，不要只为现有的查询考虑需要哪些索引，还需要考虑对查询进行优化。如果发现某些查询需要常见新索引，但是这些索引又会降低另一些查询的效率，那么应该想一下能否优化原来的查询，应该同时优化查询和索引以找到最佳的平衡，而不是闭门造车去设计最完美的索引。索引的in技巧设计一张表，已经存在组合索引(sex,contry) ,sex的选择性可能并不高，这时候是建立新的索引还是优化已经存在的索引或者是优化查询呢？好的办法是我们可以通过优化已经存在的查询继续使用已有的索引而无需重新创建索引。sex这一列只有两个值,m or f,所以我们可以在查询条件中新增and sex in('m','f')来让MySQL选择该索引。这样写不会过滤任何行，和没有这个条件时返回的结果相同。但是必须加上这个条件，MySQL才能够匹配索引的最左前缀。这个技巧在这类场景中非常有效，但是列值太长就会让in()的列表太长，这样做就不行了。如果想尽可能重用索引而不是建立大量的组合索引，就可以使用前面提到的in技巧。14 避免多个范围条件   where age>25 和 age in(12,45，25)的效率是不同的，in 是多个等值条件查询。对于范围查询，MySQL无法再使用   范围列后面的其他索引了，但是对于多个等值条件查询则没有这个限制。     索引总结：        在选择索引和编写利用这些索引查询时，需要记住下面三个原则：1 单行查询时很慢的。  如果服务器从存储中读取一个数据块只是为了获取其中一行，那么久浪费了很多工作。最好读取的快中  能包含尽可能多所需要的行，使用索引可以创建未知引用以提升效率。2 索引覆盖查询时很快的。  如果一个索引包含了查询需要的所有列，那么存储引擎就不需要再回表查找行，这样就避免了大量的单行访问，  上面已经说过单行访问是很慢的。3 按照顺序访问范围数据是很快的。  第一 顺序IO不需要多次磁盘寻道，所以比随机IO快很多。第二，如果服务器能够按照需要顺序读取数据，那么就不在不需要而外的排序操作，并且group by   查询也无需再做排序和按行按组进行聚合计算了。 总的来说，编写查询语句时应该尽可能选择合适的索引以避免单行查找、尽可能的使用数据原生顺序从而避免额外的排序操作，并 尽可能使用索引覆盖查询。         理解索引时如何工作的非常重要，应该根据这些理解来创建最合适的索引，而不是根据一些诸如 在多列索引中将 选择性最高的列放在第一列 或 应该为where子句中出现的所有列创建索引之类的经验法则以及推论。        如何判断一个系统创建的索引是否合理呢？ 一般来说，我们建议按响应时间来对查询进行分析。找出那些消耗最长时间的查询挥着那些给服务器带来最大压力的查询，然后检查那些查询的schema、sql和索引结构，判断是否有查询扫描了太多的行，是否做了很多额外的排序或者使用了临时表，是否使用随机IO访问数据，或者是有太多回表查询那些不在索引中的列的操作。如果对于很糟糕的查询，能否通过创建一个更优的索引来提升性能呢？ 答案是不可能。你必须仔细对待你的查询。