索引的优点
- 大大加快数据的查询速度
- 使用分组和排序进行数据查询时,可以显著减少查询时分组和排序的时间
- 创建唯一索引,能够保证数据库表中每一行数据的唯一性
- 在实现数据的参考完整性方面,可以加速表和表之间的连接
索引的缺点
- 创建索引和维护索引需要消耗时间,并且随着数据量的增加,时间也会增加
- 索引需要占据磁盘空间
- 对数据表中的数据进行增加,修改,删除时,索引也要动态的维护,降低了维护的速度
创建索引的原则
- 更新频繁的列不应设置索引
- 数据量小的表不要使用索引(毕竟总共2页的文档,还要目录吗?)
- 重复数据多的字段不应设为索引(比如性别,只有男和女,一般来说:重复的数据超过百分之15就不该建索引)
- 首先应该考虑对where 和 order by 涉及的列上建立索引
补充:确认索引是否已使用:explain select username from user where id=1
结果为:
字段说明:
- select_type simple表示简单查询 还有其他如primary,union,subquery等
- table 表名
- partitions 匹配的分区
- type 引擎在表中找到所需行的方式 由差到好为:all(全表扫描),index(只遍历索引树),range(索引范围扫描,常见于between,>,< 等查询中),ref(非唯一性索引扫描),eq_ref(唯一性索引扫描),const / system(当MySQL对查询某部分进行优化,并转换为一个常量时,使用这些类型访问),null(MySQL在优化过程中分解语句,执行时甚至不用访问表或索引)
- possible_keys 可供选择的索引
- key 使用的索引
- key_len 索引字节数的长度,数值越小,运行速度越快
- ref 连接匹配条件,即哪些列或常量被用于查找索引列上的值
- rows 返回的数据行数
- filtered 被表条件过滤的行数的百分比
- extra 额外信息 类型: using index(表示select操作中使用了覆盖索引),using where(mysql服务器在存储引擎受到记录后进行“后过滤“),using temporary(表示mysql需要使用临时表来存储结果集,常见于排序和分组查询), using filesort(mysql中无法使用索引完成的排序操作,成为“文件排序”)
注意: 通过key就能判断索引是否执行
优化mysql查询语句
- 不要在where条件语句 '=' 的左边进行函数,运算符或表达式的计算,如 select name from tb_user where age/2=20,因为索引不会生效(引擎会放弃使用索引,进行全表扫描)
- 不要使用 <>,!=,not in ,因为索引不会生效
- 避免对字段进行null的判断,因为索引不会生效(可以用一个值代替null,如-999)
- 使用like模糊查询时,like '%xx%'会导致索引不生效,like 'xx%' 索引能够被使用,所以避免使用第一种
- 避免使用or,可以用union替代(要想使用or,又让索引生效,or条件中的每个列都必须加上索引)
- 使用exist代替in(表中数据越多,exist的效率就比in要越大)
- 数据类型隐形转换,索引不会生效:如 select name from user where phone=13155667788;(phone字段在数据库中为varchar类型,应改成 phone='13155667788')
- 联合索引必须要按照顺序才会生效:如创建的索引顺序为a,b,where a="xx" and b="xx" 生效,但 b="xx" and a="xx" 则不会生效,补充:a="xx" 没有后面的,索引也会生效
- 尽量避免使用游标(游标效率低)
- 不要使用 select *
- 还有很多...
在已经存在的表上创建索引
- create index index_name on table_name (column_name[length], ...) [asc|desc]
- alter table table_name add [unique|fulltext|spatial] [index|key] index_name(column_name[length, ...]) [asc|desc]
删除索引
- alter table table_name drop index index_name
- drop index index_name on table_name
--------------------------------------------------------------------------------
作者:羽觞醉月
来源:博客园