命名规则：表名_字段名
1、需要加索引的字段, 要在where条件中
2、数据量少的字段不需要加索引(推荐2000条为界)
3、如果where条件中是OR关系, 加索引不起作用
4、符合最左原则
联合索引又叫复合索引。对于复合索引: Mysql从左到右的使用索引中的字段, 一个查询可以只使用索引中的一部份, 但只能是最左侧部分。
例如: 索引是key index (a,b,c), 可以支持 a | a,b | a,b,c 3种组合进行查找, 但不支持 b,c 进行查找, 当最左侧字段是常量引用时, 索引就十分有效。
两个或更多个列上的索引被称作复合索引。
利用索引中的附加列, 您可以缩小搜索的范围, 但使用一个具有两列的复合索引, 不同于使用两个单独的索引。
复合索引的结构与电话簿类似, 人名由姓和名构成, 电话簿首先按姓氏对进行排序, 然后按名字对有相同姓氏的人进行排序。
如果您知道姓, 电话簿将非常有用; 如果您知道姓和名, 电话簿则更为有用, 但如果您只知道名不姓, 电话簿将没有用处。
所以说创建复合索引时, 应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时, 复合索引非常有用; 仅对后面的任意列执行搜索时, 复合索引则没有用处。
当一个表有多条索引可走时, Mysql根据查询语句的成本来选择走哪条索引, 联合索引的话, 它往往计算的是第一个字段(最左边那个), 这样往往会走错索引. 如:
索引Index_1(create_Time, Category_id), Index_2(Category_id)
如果每天的数据都特别多, 而且有很多category, 但具体每个category的记录不会很多.
当查询SQL条件为 select ... where create_time ... and category_id= ... 时, 很可能不走索引Index_1, 而走索引Index_2, 导致查询比较慢.
解决办法是将索引字段的顺序调换一下Index_1(category_id, create_Time)。
什么情况下应不建或少建索引
1 表记录太少, 如果一个表只有5条记录, 采用索引去访问记录的话, 那首先需访问索引表, 再通过索引表访问数据表, 一般索引表与数据表不在同一个数据块, 这种情况下至少要往返读取数据块两次。
而不用索引的情况下, 会将所有的数据一次读出, 处理速度显然会比用索引快。
2 经常插入、删除、修改的表
3 数据重复且分布平均的表字段
假如一个表有10万行记录, 有一个字段A只有T和F两种值, 且每个值的分布概率大约为50%, 那么对这种表A字段建索引一般不会提高数据库的查询速度。
4 经常和主字段一块查询但主字段索引值比较多的表字段
对于复合索引:MySQL从左到右的使用索引中的字段, 一个查询可以只使用索引中的一部份, 但只能是最左侧部分。
例如索引是key index (a,b,c), 可以支持a | a,b | a,b,c 3种组合进行查找, 但不支持 b, c进行查找; 当最左侧字段是常量引用时, 索引就十分有效。下面用几个例子对比查询条件的不同对性能影响.
create table test(
a int,
b int,
c int,
KEY a(a,b,c)
);
优: select * from test where a=10 and b>50
差: select * from test where c = 50
优: select * from test where order by a
差: select * from test where order by b
差: select * from test where order by c
优: select * from test where a=10 order by a
优: select * from test where a=10 order by b
差: select * from test where a=10 order by c
优: select * from test where a>10 order by a
差: select * from test where a>10 order by b
差: select * from test where a>10 order by c
优: select * from test where a=10 and b=10 order by a
优: select * from test where a=10 and b=10 order by b
优: select * from test where a=10 and b=10 order by c
优: select * from test where a=10 and b=10 order by a
优: select * from test where a=10 and b>10 order by b
差: select * from test where a=10 and b>10 order by c
复合索引对排序的优化：
复合索引只对和索引中排序相同或相反的order by 语句优化。
在创建复合索引时, 每一列都定义了升序或者是降序。如定义一个复合索引：
CREATE INDEX idx_example ON table1 (col1 ASC, col2 DESC, col3 ASC)
其中 有三列分别是：col1 升序, col2 降序, col3 升序。现在如果我们执行两个查询
1：select col1, col2, col3 from table1 order by col1 ASC, col2 DESC, col3 ASC
和索引顺序相同
2：select col1, col2, col3 from table1 order by col1 DESC, col2 ASC, col3 DESC
和索引顺序相反
查询1, 2 都可以对复合索引优化。
如果查询为：
Select col1, col2, col3 from table1 order by col1 ASC, col2 ASC, col3 ASC
排序结果和索引完全不同时, 此时的查询不会被复合索引优化。
查询优化器在在where查询中的作用：
如果一个多列索引存在于列 col1 和 col2 上, 则以下语句：select * from table where col1=val1 AND col2=val2 查询优化器会试图通过决定哪个索引将找到更少的行。
之后用得到的索引去取值。
1． 如果存在一个多列索引, 任何最左面的索引前缀能被优化器使用。所以联合索引的顺序不同, 影响索引的选择, 尽量将值少的放在前面。
如：一个多列索引为 (col1, col2, col3)
那么在索引在列 (col1)、(col1 col2)、(col1 col2 col3) 的搜索会有作用。
SELECT * FROM tb WHERE col1 = val1
SELECT * FROM tb WHERE col1 = val1 and col2 = val2
SELECT * FROM tb WHERE col1 = val1 and col2 = val2 AND col3 = val3
2． 如果列不构成索引的最左面前缀，则建立的索引将不起作用。
如：
SELECT * FROM tb WHERE col3 = val3
SELECT * FROM tb WHERE col2 = val2
SELECT * FROM tb WHERE col2 = val2 and col3=val3
3． 如果一个 Like 语句的查询条件不以通配符起始则使用索引。
如：%车 或 %车% 不使用索引。
车% 使用索引。
索引的缺点：
1. 占用磁盘空间。
2. 增加了插入和删除的操作时间。一个表拥有的索引越多, 插入和删除的速度越慢。如: 要求快速录入的系统不宜建过多索引。