MySQL理解索引、添加索引的原则

in with 0 comment

MySQL理解索引、添加索引的原则

索引用于快速找出在某个列中有一特定值的行。不使用索引,MySQL必须从第1条记录开始然后读完整个表直到找出相关的行,还需要考虑每次读入数据页的IO开销。而如果采取索引,则可以根据索引指向的页以及记录在页中的位置,迅速地读取目标页进而获取目标记录。

大多数情况下都(默认)采用B树来构建索引。只是空间列类型的索引使用R-树,并且MEMORY表还支持hash索引。B树是平衡多叉树,每个节点存放多少个值取决于值所占的空间,这与每一张数据页存放多少条记录与记录信息量有关同理。节点中的值是以非降序进行排列的,节点中的值总是小于等于指向它的结点中的值。

MySQL使用B树构造索引的情况下,是由叶子指向具体的页和记录的。并且一个叶子有一个指针指向下一个叶子。

 

使用索引需要注意:

⑴只对WHERE和ORDER BY需要查询的字段设置索引,避免无意义的硬盘开销;

⑵组合索引支持前缀索引;

⑶更新表的时候,如增删记录,MySQL会自动更新索引,保持树的平衡;因此更多的索引意味着更多的维护成本

 索引的字段类型问题

索引分四类:

index ----普通的索引,数据可以重复

fulltext----全文索引,用来对大表的文本域(char,varchar,text)进行索引。语法和普通索引一样。 

unique ----唯一索引,唯一索引,要求所有记录都唯一

primary key ----主键索引,也就是在唯一索引的基础上相应的列必须为主键

like 不能用索引?

想象一下,你在看一本成语词典,目录是按成语拼音顺序建立,查询需求是,你想找以 “一”字开头的成语(”一%“),和你想找包含一字的成语(“%一%”)

<,<=,=,>,>=,BETWEEN,IN

<>,not in ,!=则不行

 

原则

   1,单表数据太少,索引反而会影响速度;更新非常频繁的数据不适宜建索引

   2,where后的条件,order by ,group by 等这样过滤时,后面的字段最好加上索引。根据实际情况,选择PRIMARY KEY、UNIQUE、INDEX等索引,但是不是越多越好,要适度

    3,联合查询,子查询等多表操作时关连字段要加索引

ps:数据量特别大的时候,最好不要用联合查询,即使你做了索引

多列查询该如何建索引?

一次查询只能用到一个索引,所以 首先枪毙 a,b各建索引方案

a还是b? 谁的区分度更高(同值的最少),建谁!

当然,联合索引也是个不错的方案,ab,还是ba,则同上,区分度高者,在前

联合索引的问题?

where a = “xxx” 可以使用 AB 联合索引
where b = “xxx” 则不可 (再想象一下,这是书的目录?)

所以,大多数情况下,有AB索引了,就可以不用在去建一个A索引了

详解:

联合索引又叫复合索引。对于复合索引: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 a50

优: select * from test order by a
差: select * from test order by b
差: select * from test 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

mysql 复合索引,联合索引 - flyflying1987 - ly

索引原则

1.索引越少越好

原因:主要在修改数据时,第个索引都要进行更新,降低写速度。

2.最窄的字段放在键的左边

3.避免file sort排序,临时表和表扫描.

哪些常见情况不能用索引?

也即

select * from test where mobile = 13711112222;

可是无法用到mobile字段的索引的哦(如果mobile是char 或 varchar类型的话)

btw,千万不要尝试用int来存手机号(为什么?自己想!要不自己试试)

 

覆盖索引(Covering Indexes)拥有更高效率

索引包含了所需的全部值的话,就只select 他们,换言之,只select 需要用到的字段,如无必要,可尽量避免select *

NULL 的问题

NULL会导致索引形同虚设,所以在设计表结构时应避免NULL 的存在(用其他方式表达你想表达的NULL,比如 -1?)

如何查看索引信息,如何分析是否正确用到索引?

show index from tablename;
explain select ……;

关于explain,改天可以找个时间专门写一篇入门帖,在此之前,可以尝试 google

了解自己的系统,不要过早优化!

过早优化,一直是个非常讨厌而又时刻存在的问题,大多数时候就是因为不了解自己的系统,不知道自己系统真正的承载能力

比如:几千条数据的新闻表,每天几百几千次的正文搜索,大多数时候我们可以放心的去like,而不要又去建一套全文搜索什么的,毕竟cpu还是比人脑厉害太多

最后:永远别忘记的关键词 sql注入

转发至: https://www.cnblogs.com/DaBing0806/p/4876228.html

Comments are closed.