mysql索引太多了? [英] mysql too many indexes?
问题描述
我花了一些时间来优化我们当前的数据库。
I am spending some time optimizing our current database.
我正在专门研究索引。
有几个问题:
- 是否存在索引太多的事情?
- 什么索引会加速吗?
- 索引会减慢什么?
- 什么时候添加索引是个好主意?
- 什么时候添加索引是个坏主意?
- 多个索引与多列索引的Pro和Con?
- Is there such a thing as too many indexes?
- What will indexes speed up?
- What will indexes slow down?
- When is it a good idea to add an index?
- When is it a bad idea to add an index?
- Pro's and Con's of multiple indexes vs multi-column indexes?
推荐答案
索引加速的是什么?
What will indexes speed up?
数据检索 - SELECT语句。
Data retrieval -- SELECT statements.
索引会减慢什么?
What will indexes slow down?
数据操作 - INSERT,UPDATE,DELETE语句。
Data manipulation -- INSERT, UPDATE, DELETE statements.
添加索引是个好主意吗?
When is it a good idea to add an index?
如果您希望获得更好的数据检索性能。
If you feel you want to get better data retrieval performance.
什么时候添加索引是个坏主意?
When is it a bad idea to add an index?
在会看到大量数据操作的表格上 - - 插入,更新...
On tables that will see heavy data manipulation -- insertion, updating...
多个索引与多列索引的Pro和Con?
Pro's and Con's of multiple indexes vs multi-column indexes?
在索引列定义中从左到右处理覆盖索引(多个列上的索引)时,查询需要解决列的顺序。语句中的列顺序无关紧要,只有列1,2和3的列顺序 - 在使用索引之前,语句需要引用第1列。如果只有对第2列或第3列的引用,则无法使用1/2/3的覆盖索引。
Queries need to address the order of columns when dealing with a covering index (an index on more than one column), from left to right in index column definition. The column order in the statement doesn't matter, only that of columns 1, 2 and 3 - a statement needs have a reference to column 1 before the index can be used. If there's only a reference to column 2 or 3, the covering index for 1/2/3 could not be used.
在MySQL中,每个只能使用一个索引查询中的SELECT /语句(子查询/ etc被视为单独的语句)。并且MySQL允许的每个表的空间量有限制。此外,在索引列上运行函数会使索引无效 - IE:
In MySQL, only one index can be used per SELECT/statement in the query (subqueries/etc are seen as a separate statement). And there's a limit to the amount of space per table that MySQL allows. Additionally, running a function on an indexed column renders the index useless - IE:
WHERE DATE(datetime_column) = ...
这篇关于mysql索引太多了?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!