mysql 创建高性能索引

高性能索引的策略

1. 索引对如下类型有效：

全值匹配
匹配最左前缀
匹配列的前缀
匹配范围
一个精确匹配一个范围匹配

索引BTree中的节点是有序的，所以除了按值查找以外，还可以用于order by 操作（按顺序查找）。一般来说如果Btree 可以按照某种方式查询到值，那么也可以按照这种方式拥有排序

2. 索引分类

Btree（用的是B+tree技术）
Hash(memory引擎)
全文索引（myisam引擎）
空间数据索引（R-tree myisam引擎）

MySql 最常用存储引擎 InnoDB 和 MyISAM 都不支持 Hash 索引，它们默认的索引都是 B-Tree。但是如果你在创建索引的时候定义其类型为 Hash，MySql 并不会报错，而且你通过 SHOW CREATE TABLE 查看该索引也是 Hash，只不过该索引实际上还是 B-Tree

当数据库中有URL字段需要大量查询的时候需要构造一个伪哈希值来处理。请求的时候
Select * from abc where url = www.baidu.com” and url_crc = CRC32(“www.baidu.com”)

3.高性能索引策略

独立的列
1. 索引列不是表达式的一部分，也不是函数的参数（select actor_id from table where actor_id+1= 5 [ TO_DAYS( date ) ]）
前缀索引和索引选择性
1. 选择性高的通过 select count（distinct（city））/count(*) from table.数值越大越适合
多列索引
1. 注意AND和OR 链接多列的区别
2. 当explain Extra 出现union的合并索引的时候提醒我们索引建的很糟糕
选择合适的索引列顺序
1. 一般来说将选择性高的放在前列（select * from sum(staff_id = 2) and sum(custom_id = 666) from payment）[ 从具体的数值角度出发 ]
2. 还考虑基数的分布 (select count(Distinct staff_id)/count()as staff_sec , count(Distinct custom_id)/count() as custom_sec,count（*）from payment)[ 从整体的角度出发 ]

4.聚簇索引

   聚簇索引是顺序结构与数据存储物理结构一致的一种索引，通常来说物理顺序的机构只有一种，那么表的聚簇索引也只有一个，通常默认都是主键，设置了主键，系统就会默认加上聚簇索引

   对于普通的堆组织表来说，表数据和索引是分成存储的，主键索引和二级索引存储上没有任何区别。
   而对于聚簇索引表来说，表数据是和主键一起存储的，主键索引的叶结点存储行数据，二级索引的叶结点存储行的主键值。
   聚簇索引表最大限度地提高了I/O密集型应用的性能，但它也有以下几个限制：
   1）插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键。
   2）更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。
   3）二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。
   二级索引的叶节点存储的是主键值，而不是行指针，这是为了减少当出现行移动或数据页分裂时二级索引的维护工作，但会让二级索引占用更多的空间。

聚簇索引并不是一种单独的索引类型，而是一种数据存储的方式，具体细节依赖于实现方式，但是INNODB的聚簇索引，实际上在一个结构中保存了B-tree索引和数据行

5.覆盖索引

6.使用索引扫描来做排序

mysql 有两种方式可以生成有序的结果
1. 排序操作（orderby）
2. 按照索引顺序扫描（如果Explain结果中的type为“index”则说明使用了索引扫描来做排序）
orderby
1. 只有当索引的列顺序和Order by 字句的顺序完全一致并且所有列的排序方向（倒叙或者正序）都一样时，Mysql才能够使用索引来对结果进行排序
2. 如果查询需要关联多张表，只有当orderby 字句的字段全为第一个表时才能使用索引做排序，orderby字句和查找型查询原则是一致的需要满足最左原则，
3. 有一种情况下可以不满足索引最左的要求，那就是前倒列为常量的时候（翻译过来就是说where 中的一个条件为最左索引,where字段和Orderby字段一起形成最左原则）
  
  select rentai_id,staff_id from rental where rental_date = “2000-1-1” order by inventory_id , customer_id;
  (其中rental_date，inventory_id , customer_id 为联合索引)

7.压缩（前缀压缩）索引

  Myisam使用前缀压缩来减少索引的大小，可以让更多索引放在内存中，但是代价可能是操作更慢

8.冗余和重复索引

explain 中的extra信息中 Useing where 表示MySQL存储引擎返回行以后在应用where过滤。

索引案例学习

支持多种过滤条件
1. 使用In来构建索引的最左前缀。（country .sex , region where country = “XX” and sex in (0,1) and region = “beijing” )
  in 技巧不鞥呢滥用因为每次多一个IN 优化器的组合数会以指数的形式增长。最终可能会极大的降低效率
避免多个范围条件
1. 范围查询（><）和等值查询(in = ) explain中的type可以显（range）示但是无法很好地区别出来.还需要根据where条件来判断
  两种查询的不同在于。范围查询不能使用后续的列索引。等值查询可以。
2. 避免多个范围条件（范围条件多影响索引的使用）
3. 优化排序

总结

单行访问时很慢的
按顺序访问范围是很快的
索引覆盖查询是很快的

秒客网