高性能mysql-第五章索引（3）

• EXPLAIN 出来的type列值为type列的值位index 说明mySQL使用了索引扫描来排序。
• 使用索引排序要满足最左前缀要求。或者签到列为常量
• eg: SELECT rental_id,staff_id FROM renttal WHERE rental_date =’2015-8-8’ ORDER BY inventory_id ,customer_id
  
  • 其中有索引 rental_date(rental_date,inventory_id,customer_id)

一、压缩索引

MyISAM使用前缀压缩来减少索引的大小，默认只压缩字符串，但是通过设置也可以对整数做压缩。
压缩可以使用更少的空间，代价是某些操作可能更慢。特别是倒序的查询，测试表明，对于CPU密集型的应用，查询会很慢，特别是倒序。对于I/O密集型应用，查询可能会不错。
可以在CREATE TABLE的语句中指定PACK_KEYS参数来指定索引压缩方式。

二、冗余和重复索引

重复索引：指在相同的列上按照相同的顺序创建的相同类型的索引，要尽量避免重复索引，除非在同一列上创建不同类型的索引来满足不同的查询需求。比方说 key(col) 和 fulltext key(col)。
冗余索引：如果创建了索引（A，B），再创建索引（A）就是冗余索引。因为索引（A，B）也可以当作（A）来使用（只针对B-Tree）。但是如果再创建索引（B，A）或者（B），则不是冗余索引。或者类型不同，比方说哈希，全文索引等。
冗余索引通常发生在添加索引的时候，大多数情况下都不需要冗余索引，而是尽量扩展已有的索引，除非扩展已有的索引会导致索引很大，从而影响其他使用索引的性能。
例如：在一个整数列的索引上扩展一个很长的varchar列的索引，性能可能就会急剧下降。但是增加一个新的索引，就会对增删改操作影响很大，所以要平衡使用。

如何找到这写索引，以便删除，可以访问INFORMATION_SCHEMA，或者一些现有的工具来定位。
有一个值得注意的地方：
对于InnoDB因为二级索引包换了主键，所以列（A）上的索引就相当与（A，ID），所以类似
where A=5 order by id 这样的查询，这个索引会很有用。但是扩展为索引（A，B）
之后，则实际变成了（A，B，ID），则order by就无法用到索引排序了。

三、索引和锁

索引可以让查询锁定更少的行，因为索引可以让查询不访问那些不需要的行，那么就会锁定更少的行。这有2点好处：
1、减少锁定行带来的额外的开销。
2、锁定超过需要的行会增加锁争用和减少并发性。

InnoDB只有在访问行的时候才会对其加锁，而索引能减少InnoDB访问的行数，从而减少锁的数量。但这只有当InnoDB在存储引擎层能够过滤掉所有不需要的行时才有效。如果索引无法过滤掉无效的行，那么在InnoDB检索到数据返回给服务器层以后，MySQL服务器才能应用WHERE子句进行过滤。这时候已经无法避免锁定行了，InnoDB已经锁定了所有返回的数据行。

InnoDB在二级索引上使用共享读锁，在主键索引需要排它锁

等以后再详细看锁的东西。

创建索引案例里学到的：
- 如果创建联合索引， (A, B, C) 为了以AC为条件进行查询时可以使用到索引，可以在B上给他默认值。使他可以使用到索引。
- 避免多个范围查询。宁可使用 IN() ，多个范围条件查询，MYsql无法使用范围列后面的其他索引。但是IN()可以
- 优化排序（可以使用延迟关联，首先使用到覆盖索引查询出主键ID，然后再查询ID对应的数据）

     - SELECT <COLS> FROM profiles INNER JOIN （
         - SELECT id FROM profiles WHERE x.sex='M' ORDER BY rating LIMIT 1000,10 
     - ）AS x USING id