关闭

MYSQL索引优化

标签: mysql
75人阅读 评论(0) 收藏 举报
分类:

mysql索引的优化

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


索引是什么?

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

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

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

2.B-Tree索引: 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条件可以使用索引。

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

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:1193次
    • 积分:69
    • 等级:
    • 排名:千里之外
    • 原创:6篇
    • 转载:1篇
    • 译文:0篇
    • 评论:0条
    文章分类