mysql 创建高性能索引

时间:2021-10-13 20:18:09

高性能索引的策略

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

  1. 全值匹配
  2. 匹配最左前缀
  3. 匹配列的前缀
  4. 匹配范围
  5. 一个精确匹配一个范围匹配

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

2. 索引分类

  1. Btree(用的是B+tree技术)
  2. Hash(memory引擎)
  3. 全文索引(myisam引擎)
  4. 空间数据索引(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. 独立的列
    1. 索引列不是表达式的一部分,也不是函数的参数(select actor_id from table where actor_id+1= 5 [ TO_DAYS( date ) ])
  2. 前缀索引和索引选择性
    1. 选择性高的 通过 select count(distinct(city))/count(*) from table.数值越大越适合
  3. 多列索引
    1. 注意AND和OR 链接多列的区别
    2. 当explain Extra 出现union的合并索引的时候提醒我们索引建的很糟糕
  4. 选择合适的索引列顺序
    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)二级索引访问需要两次索引查找,第一次找到主键值,第二次根据主键值找到行数据。
二级索引的叶节点存储的是主键值,而不是行指针,这是为了减少当出现行移动或数据页分裂时二级索引的维护工作,但会让二级索引占用更多的空间。
  1. 聚簇索引并不是一种单独的索引类型,而是一种数据存储的方式,具体细节依赖于实现方式,但是INNODB的聚簇索引,实际上在一个结构中保存了B-tree索引和数据行

5.覆盖索引

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

  1. mysql 有两种方式可以生成有序的结果
    1. 排序操作(orderby)
    2. 按照索引顺序扫描(如果Explain结果中的type为“index”则说明使用了索引扫描来做排序)
  2. 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. 支持多种过滤条件

    1. 使用In来构建索引的最左前缀。(country .sex , region where country = “XX” and sex in (0,1) and region = “beijing” )
      in 技巧不鞥呢滥用因为每次多一个IN 优化器的组合数会以指数的形式增长。最终可能会极大的降低效率
  2. 避免多个范围条件

    1. 范围查询(><)和等值查询(in = ) explain中的type可以显(range)示 但是无法很好地区别出来.还需要根据where条件来判断
      两种查询的不同在于。范围查询不能使用后续的列索引。等值查询可以。

    2. 避免多个范围条件(范围条件多影响索引的使用)

    3. 优化排序

总结

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