MYSQL索引优化

mysql索引的优化

以下有关的索引都是基于InnoDB搜索引擎

---

索引是什么?

简单说，索引就是对数据库表中一列或多列的值进行排序的一种结构，能够快速定位数据的位置。

mysql的索引类型(概念性的东西)

1.聚簇索引： 不是一种索引类型，而是MySQL组织存储数据的方式，在InnoDB引擎中，聚簇索引是通过主键来聚集数据的，聚簇索引实际上是在一个数据结构中保存了B-Tree索引和数据行的整合。
优点：将相关联的数据聚集到一起，可以最大速度的访问有关数据，适合IO密集型的操作，因为叶子节点存放的数据，通过索引一次就可以获取到数据。
缺点：更新、插入、删除数据时可能会造成大量数据的移动和数据的页分裂，这样导致行稀疏，全表扫描会变得更慢（使用OPTIMIZE TABLE tablename重新组织表结构），二级索引会变得很大。

2.B-Tree索引： B-Tree索引的数据结构

B-Tree索引适用的查询条件有：全键值、键值范围、键前缀（联合索引的前几列或一列数据的开始部分）。
优点：范围查询非常高效、对于按照索引条件进行排序非常容易。
缺点：只能按照索引列顺序使用索引。

使用索引的注意事项及优化

1.使用索引时，如果给某个字段建立了索引，那么在使用这个字段作为过滤条件时(where) ，不能使用表达式或者函数。 例如：

select id,name,sex,birthday from user where left(birthday ,10) between '1990-01-01' and '1990-01-31';

这样的一条sql，即使birthday 这个字段建立了索引，使用了left函数后，这个索引也不会使用。

2.组合索引需要遵循最左前缀原则。 例如：在user表中建立了name，city_id, birthday 这样的一个组合索引

select id,name,birthday，city_id from user where city_id = 1 and  birthday between '1990-01-01' and '1990-01-31' ;

这样的一条sql是不会使用刚才那个组合索引的，那我想这样查怎么办呢？

select id,name,birthday，city_id from user where name != '' and city_id = 1 and birthday between '1990-01-01' and '1990-01-31' ;

这样就可以使用这个组合索引(但是这样做和扫描全表没什么区别，所以在建立组合索引时，考虑一下业务上面的需求需要怎么样查询数据的，根据自身情况而定)。想使用最左前缀原则的组合索引，你的条件只能指定为name,city_id,birthday或者name , city_id或name这三个条件。

3.尽量不要自己指定使用索引。 FORCE INDEX([索引名])，虽然mysql可以使用这个关键字指定索引，但是尽量不要使用，mysql的分析器可以分析出我们的sql具体可以使用那些索引，会查找出最佳的索引策略。

4.LIMIT使用索引列延迟加载数据提升性能。 首先使用过滤条件在主键中进行查询过滤，然后在根据主键筛选数据。例如：

select * from user limit 500000, 100; -- 需要取出100条数据，从50w开始。
select u1.* from (select id from user LIMIT 5000000, 10) u inner join user u1 ON u.id=u1.id; -- 可以这样优化

5.COUNT(*)使用索引列加快统计。 一般我们可以指定id作为count的统计，当数据表没有id作为主键时，我们可以使用count(*)让mysql自己去优化这个count语句。

6.范围查询和IN查询条件的改变使用索引。 一般范围查询就是使用索引的结束，它后面的字段不会被索引使用，但是in条件可以使用索引。

以上总结并不是绝对的，可根据自身情况做最优的解决方案。