Mysq 索引优化

MYSQL支持的索引类型
  BTREE索引
    特点:
      通过引用以B+权的结构存储数据
      能够加快数据的查询速度
      更适合进行范围查找
    应用:
      全值匹配的查询 =
      匹配最左前缀的查询
      匹配列前缀查询 LIKE 'XX%'
      匹配范围值的查询
      精确匹配左前列并范围匹配另外一列
      只访问索引的查询
    使用限制:
      如果不是按照索引最左列查找,则无法使用索引
      使用索引时不能跳过索引中的列,否则只能部分使用或不能使用
      NOT IN 和 <> 操作无法使用索引
      如果查询中有某个列的范围查询,则其右边所有列都无法使用索引

  HASH索引(INNODB自动生成,无法手动创建)
    是基于HASH表实现的,只有查询条件精确匹配,HASH索引中的所有列时,才能使用HASH索引。
    对于HASH索引中的所有列,存储引擎都会为每一行计算一个HASH码,HASH索引中存储的就是HASH码。
    必须进行二次查找
    无法用于排序
    不支持部分索引查找也不支持范围查找
    HASH码计算可能存在HASH冲突

为什么要使用索引?
  索引大大减少了存储引擎需要扫描的数据量
  索引可以帮助我们进行排序以避免使用临时表
  索引可以把随机IO变为顺序IO

索引带来的问题?
  索引会增加写操作的成本
  太多的索引会增加查询优化器的选择时间

索引优化策略
  索引列上不能使用表达式或函数
  前缀索引和索引列的选择性
  联合索引
    如何选择索引列的顺序:经常会使用到的列优先;选择性高的列优先;宽度小的列优先;
  覆盖索引
    优点:可以优化缓存,减少磁盘IO操作;可以减少随机IO,变随机IO操作为顺序IO操作;可以避免对INNODB主键索引的二次查询;可以避免MYISAM表进行系统调用。
    无法使用覆盖索引的情况:存储引擎不支持覆盖索引;查询中使用了太多的列;使用了双%号的LIKE查询;

使用索引扫描来优化排序的条件:
  索引的列顺序和ORDER BY 子句的顺序完成一致
  索引中所有列的方向(升降序)和ORDER BY 子句的顺序完成一致
  ORDER BY 中的字段全部在关联表中的第一张表中

模拟HASH索引优化查询
  用MD5,用BTREE索引进行模拟,需要单独建一列保存MD5值
  只能处理键值的全值匹配查找
  所使用的HASH函数决定着索引键的大小


使用索引来优化查询
  利用索引优化锁
  索引可以减少锁定的行数
  索引可以加快处理速度,同时也加快了锁的释放。

索引的维护和优化
  删除重复和冗余的索引 pt-duplicate-key-checker h=127.0.0.1
  查找未被使用过的索引
  mysql>SELECT object_schema,object_name,index_name,b.TABLE_ROWS FROM `performance_schema`.table_io_waits_summary_by_index_usage a join information_schema.`TABLES` b on a.object_schema=b.TABLE_SCHEMA and a.object_name=b.TABLE_NAME where index_name is not null and count_star=0 order by object_schema,object_name;

更新索引统计信息及减少索引碎片
  analyze table table_name
  optimize table table_name 使用会锁表

posted on 2017-08-15 20:19  AF陈  阅读(179)  评论(0编辑  收藏  举报

导航