MySQL数据库索引优化策略

理想的索引

1:查询频繁 2:区分度高  3:长度小  4: 尽量能覆盖常用查询字段.

1、索引列上不能使用表达式或者函数

例子：select ...... from product where to_days(out_date) - to_days(current_data)<=30     to_days就是使用了函数，out_date就是索引列。

优化后：select ...... from product where out_date<=data_add(current_data,interval 30 day)   这样对out_date索引列就没有使用函数。

---

2、mysql支持对字符串的前缀建立索引，前缀索引和索引列的选择性

索引的选择性是不重复的索引值和表的记录数的比值

3、覆盖索引

索引覆盖是指 如果查询的列恰好是索引的一部分,那么查询只需要在索引文件上进行,不需要回行到磁盘再找数据.

这种查询速度非常快,称为”索引覆盖”

优点：①优化缓存，减少磁盘IO操作②可以减少随机IO，变随机IO操作变为顺序IO操作③可以避免对Innodb主键索引的二次查询④可以避免MyISAM表进行系统调用

无法使用覆盖索引的场景：

①不是所有的存储引擎都支持覆盖索引②查询中使用了太多的列③使用了双%号的like查询

4、索引不会包含有NULL值的列

    只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

5、索引列排序

    MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

6、使用短索引

    对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

例：CREATE INDEX index_name ON table(column(10 or 20))；

7、like语句操作

     一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

8、不要在列上进行运算

     例如：select * from users where YEAR(adddate)<2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：select * from users where adddate<’2007-01-01′。关于这一点可以围观：一个单引号引发的MYSQL性能损失。

9、innodb的主索引文件上 直接存放该行数据,称为聚簇索引,次索引指向对主键的引用

myisam中, 主索引和次索引,都指向物理行(磁盘位置).

注意: innodb来说,

1: 主键索引 既存储索引值,又在叶子中存储行的数据

2: 如果没有主键, 则会Unique key做主键

3: 如果没有unique,则系统生成一个内部的rowid做主键.

4: 像innodb中,主键的索引结构中,既存储了主键值,又存储了行数据,这种结构称为”聚簇索引”

聚簇索引 

优势: 根据主键查询条目比较少时,不用回行(数据就在主键节点下)

劣势: 如果碰到不规则数据插入时,造成频繁的页分裂.

高性能索引策略

0:对于innodb而言,因为节点下有数据文件,因此节点的分裂将会比较慢.

对于innodb的主键,尽量用整型,而且是递增的整型.

如果是无规律的数据,将会产生的页的分裂,影响速度.

 

最后说一下：MySQL只对一下操作符才使用索引：<,<=,=,>,>=,between,in,以及某些时候的like(不以通配符%或_开头的情形)。而理论上每张表里面最多可创建16个索引。