MYSQL索引优化

原创 2016年06月01日 16:22:18

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条件可以使用索引。

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

版权声明:本文为博主原创文章,未经博主允许不得转载。

mysql-常见问题,索引优化

  • 2017年11月17日 13:51
  • 388KB
  • 下载

理解MySQL——索引与优化

写在前面:索引对查询的速度有着至关重要的影响,理解索引也是进行数据库性能调优的起点。考虑如下情况,假设数据库中一个表有10^6条记录,DBMS的页面大小为4K,并存储100条记录。如果没有索引,查询将...

MySQL索引分析及优化.pdf

  • 2012年10月24日 15:40
  • 74KB
  • 下载

由浅入深探究mysql索引结构原理、性能分析与优化

目录 第一部分:基础知识 第二部分:MYISAM和INNODB索引结构 1、 简单介绍B-tree B+ tree树 2、 MyisAM索引结构 3、 Annode索引结构...

Oracle与MySQL数据库索引设计与优化

  • 2017年10月26日 08:53
  • 58.51MB
  • 下载

mysql索引优化分享

  • 2017年05月29日 11:32
  • 1020KB
  • 下载

mysql性能优化的21个实践和使用索引

今天,数据库的操作越来越成为整个应用的性能瓶颈了,这点对于Web应用尤其明显。关于数据库的性能,这并不只是DBA才需要担心的事,而这更是我 们程序员需要去关注的事情。当我们去设计数据库表结构,对操作数...

mysql索引分析和优化

  • 2011年12月07日 12:43
  • 38KB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:MYSQL索引优化
举报原因:
原因补充:

(最多只允许输入30个字)