当前位置:   article > 正文

深入分析mysql索引何时创建比较好_mysql大表建索引需要晚上吗

mysql大表建索引需要晚上吗

mysql索引是可以提高数据库的查询速度了,但是需要合理的设置mysql索引才可以达到我们要的需求了,下面小编来为各位整理一篇关于mysql索引何时创建比较好文章,希望 对各位有帮助.

索引可以提高数据的检索效率,也可以降低数据库的IO成本,并且索引还可以降低数据库的排序成本。排序分组操作主要消耗的就是CPU资源和内存,所以能够在排序分组操作中好好的利用索引将会极大地降低CPU资源的消耗。本篇文章将简单的分析一下如何正确创建MySQL数据索引。

如何判定是否需要创建索引?

1、较频繁地作为查询条件的字段

这个都知道。什么是教频繁呢?分析你执行的所有SQL语句。最好将他们一个个都列出来。然后分析,发现其中有些字段在大部分的SQL语句查询时候都会用到,那么就果断为他建立索引。

2、唯一性太差的字段不适合建立索引

什么是唯一性太差的字段。如状态字段、类型字段。那些只存储固定几个值的字段,例如用户登录状态、消息的status等。这个涉及到了索引扫描的特性。例如:通过索引查找键值为A和B的某些数据,通过A找到某条相符合的数据,这条数据在X页上面,然后继续扫描,又发现符合A的数据出现在了Y页上面,那么存储引擎就会丢弃X页面的数据,然后存储Y页面上的数据,一直到查找完所有对应A的数据,然后查找B字段,发现X页面上面又有对应B字段的数据,那么他就会再次扫描X页面,等于X页面就会被扫描2次甚至多次。以此类推,所以同一个数据页可能会被多次重复的读取,丢弃,在读取,这无疑给存储引擎极大地增加了IO的负担。

3、更新太频繁地字段不适合创建索引

当你为这个字段创建索引时候,当你再次更新这个字段数据时,数据库会自动更新他的索引,所以当这个字段更新太频繁地时候那么就是不断的更新索引,性能的影响可想而知。大概被检索几十次会更新一次的字段才比较符合建立索引的规范。而如果一个字段同一个时间段内被更新多次,那么果断不能为他建立索引。

4、不会出现在where条件中的字段不该建立索引


注意:

并非所有的数据库都以相同的方式使用索引。作为通用规则,只有当经常查询索引列中的数据时,才需要在表上创建索引。索引占用磁盘空间,并且降低添加、删除和更新行的速度。在多数情况下,索引用于数据检索的速度优势大大超过它的不足之处。但是,如果应用程序非常频繁地更新数据或磁盘空间有限,则可能需要限制索引的数量。

可以基于数据库表中的单列或多列创建索引。多列索引使您可以区分其中一列可能有相同值的行。

如果经常同时搜索两列或多列或按两列或多列排序时,索引也很有帮助。例如,如果经常在同一查询中为姓和名两列设置判据,那么在这两列上创建多列索引将很有意义。

确定索引的有效性:

● 检查查询的 WHERE 和 JOIN 子句。在任一子句中包括的每一列都是索引可以选择的对象。

● 对新索引进行试验以检查它对运行查询性能的影响。

● 考虑已在表上创建的索引数量。最好避免在单个表上有很多索引。

● 检查已在表上创建的索引的定义。最好避免包含共享列的重叠索引。

● 检查某列中唯一数据值的数量,并将该数量与表中的行数进行比较。比较的结果就是该列的可选择性,这有助于确定该列是否适合建立索引,如果适合,确定索引的类型。

MySQL何时使用索引

对一个键码使用>, >=, =, <, <=, IF NULL和BETWEEN

SELECT * FROM table_name WHERE key_part1=1 and key_part2 > 5; 
SELECT * FROM table_name WHERE key_part1 IS NULL;
当使用不以通配符开始的LIKE

SELECT * FROM table_name WHERE key_part1 LIKE 'jani%'
在进行联结时从另一个表中提取行时

SELECT * from t1,t2 where t1.col=t2.key_part
找出指定索引的MAX()或MIN()值

SELECT MIN(key_part2),MAX(key_part2) FROM table_name where key_part1=10
一个键码的前缀使用ORDER BY或GROUP BY

SELECT * FROM foo ORDER BY key_part1,key_part2,key_part3
在所有用在查询中的列是键码的一部分时间

SELECT key_part3 FROM table_name WHERE key_part1=1

MySQL何时不使用索引

如果MySQL能估计出它将可能比扫描整张表还要快时,则不使用索引。例如如果key_part1均匀分布在1和100之间,下列查询中使用索引就不是很好:

SELECT * FROM table_name where key_part1 > 1 and key_part1 < 90
如果使用HEAP表且不用=搜索所有键码部分。

在HEAP表上使用ORDER BY。

如果不是用键码第一部分

SELECT * FROM table_name WHERE key_part2=1
如果使用以一个通配符开始的LIKE

SELECT * FROM table_name WHERE key_part1 LIKE '%jani%'
搜索一个索引而在另一个索引上做ORDER BY

SELECT * from table_name WHERE key_part1 = # ORDER BY key2


建立MySql数据库索引需要注意的几个问题

1.建立索引的时机:若表中的某字段出现在select、过滤、排序条件中,为该字段建立索引是值得的。 
2.对于like %xxx的模糊查询,普通的索引是无法满足的,需要建立全文索引。 
3.对于有多个条件的,比如: "...where a=xx and b=yy","...where a=xx order by b","...where a=xx group by b"。需要使用组合索引。但是组合索引只能在SQL语句中满足"最左前缀"的条件下使用。且组合索引有一些副作用,如索引尺寸可能比数据本身大,因为组合索引的组合条目多。所以在实际应用中,要量身定做,使用慢查询分析工具分析。 
4.开启索引缓存,直接在内存中查找索引,不用再磁盘中。 
5.建立索引是有代价的,当update、delete语句执行时,会使得索引更新,将耗掉更多的时间。可以使用mysqlreport报告,了解select、update、delete、insert、replace各语句所占的百分比.

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/不正经/article/detail/683950
推荐阅读
相关标签
  

闽ICP备14008679号