索引的增删改查

11课程总结 索引的增删改查

建立索引

alter table t100w add index idx_k2(k2);

在t100w表中创建一个关于k2列的名为idx_k2的索引

mysql> alter table t100w add unique index idx_k1(k1);

在t100w表中创建一个关于k2列的名为idx_k2的唯一索引

mysql> alter table city add index idx_name(name(5));

在city表中创建一个关于name列的名为idx_name的前缀索引索引

mysql> alter table city add index idx_co_po(countrycode,population);

在city表中创建一个关于countrycode,population列的名为idx_co_po的联合索引

查看表中索引

desc t100w

+-------+-----------+------+-----+-------------------+-----------------------------+

| Field | Type      | Null | Key | Default          | Extra                      |

| k2    | char(4)  | YES  | MUL | NULL              |                            |

show index from t100w\G;

              Table :  t100w

  Non_unique :  1

      Key_name :  idx_k2

  Seq_in_index :  1

Column_name :  k2

        Collation :  A

      Cardinality :  3773

        Sub_part :  NULL

          Packed :  NULL

              Null :  YES

    Index_type :  BTREE

      Comment :

Index_comment :

1 row in set (0.00 sec)

删除索引

alter table city drop index idx_co_po;

删除city表中 的 idx_co_po索引

建立索引的原则

(1) 必须要有主键,如果没有可以做为主键条件的列,创建无关列

(2) 经常做 为 where条件列  order by  group by  join on, distinct 的条件(业务:产品功能+用户行为)

(3) 最好使用唯一值多的列作为索引,如果索引列重复值较多,可以考虑使用联合索引

(4) 列值长度较长的索引列,我们建议使用前缀索引.

(5) 降低索引条目,一方面不要创建没用索引,清理不常使用的索引,percona toolkit(xxxxx)

percona-toolkit中有个工具,专门分析索引是否有用

限制索引的数目

可能会产生的问题:

(1) 每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。

(2) 修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。

(3) 优化器的负担会很重,有可能会影响到优化器的选择.

(6) 索引维护要避开业务繁忙期

(7)尽量少在经常更新值的列上建索引

关于联合索引 *****

(1) where A、GROUP BY B、ORDER BY C  ---> (A,B,C)

(2) where  A B C 

(2.1) 都是等值 ,在5.5 以后无关索引顺序，把控一个原则：唯一值多的列放在联合索引的最左侧

(2.2) 如果有不等值，例如以下情况

select where  A= and  B> and  C=

索引顺序，ACB ，语句改写为 AC  否则不走索引