10 分钟 MySQL 索引从入门到精通
10分钟,只要10分钟,带你了解 MySQL 索引的精华所在,走一走看一看,看了不吃亏,看了不上当~ :-D
1、索引是做什么的?
索引用于快速找出在某个列中有一特定值的行。不使用索引,MySQL必须从第1条记录开始然后读完整个表直到找出相关的行。
表越大,花费的时间越多。如果表中查询的列有一个索引,MySQL能快速到达一个位置去搜寻到数据文件的中间,没有必要看所有数据。
大多数MySQL索引(PRIMARY KEY、UNIQUE、INDEX和FULLTEXT)在B树中存储。只是空间列类型的索引使用R-树,并且MEMORY表还支持hash索引。
2、索引好复杂,我该怎么理解索引,有没一个更形象点的例子?
有,想象一下,你面前有本词典,数据就是书的正文内容,你的大脑就是那个cpu,而索引,则是书的目录
3、索引越多越好?
大多数情况下索引能大幅度提高查询效率,但:
-
数据的变更(增删改)都需要维护索引,因此更多的索引意味着 更多的维护成本
-
更多的索引意味着也需 要更多的空间 (一本100页的书,却有50页目录?)
-
过小的表,建索引可能会更慢哦 :) (读个2页的宣传手册,你还先去找目录?)
4、索引的字段类型问题
-
text类型,也可建索引(需指定长度)
-
myisam存储引擎索引键长度综合不能超过1000字节
-
用来筛选的值尽量保持和索引列同样的数据类型
5、like 不能用索引?
-
尽量减少like,但不是绝对不可用,”xxxx%” 是可以用到索引的,
想象一下,你在看一本成语词典,目录是按成语拼音顺序建立,查询需求是,你想找以 “一”字开头的成语(”一%“),和你想找包含一字的成语(“%一%”)
-
除了like,以下操作符也可用到索引:
<,<=,=,>,>=,BETWEEN,IN
<>,not in ,!=则不行
6、什么样的字段不适合建索引?
-
一般来说,列的值唯一性太小(如性别,类型什么的),不适合建索引(怎样叫太小?一般说来,同值的数据超过表的百分之15,那就没必要建索引了)
-
太长的列,可以选择只建立部分索引,(如:只取前十位做索引)
-
更新非常频繁的数据不适宜建索引(怎样叫非常?意会)
7、一次查询能用多个索引吗?
不能。
比如:
我有一个A表,有b,c,d字段,其中在b,c上各自加了索引。(可以使用SQL:SHOW INDEX from A 查看A表的索引)
select * from A WHERE b=x and c=x; --x表示要查询的值
通过这条SQL,我们可以看到有两列:possible_keys和key
pk表示此条SQL语句可能用到的索引,k表示实际用到的索引。
你会发现,实际只用到了一个索引,至于是b还是c,mysql会通过分析选择使用给出结果集小的索引(cardinality),如通过b索引查询的记录比较少, 则会使用b作为实际索引。
注:嵌套、关联查询 explain 下可以看做多次查询,而每次独立的查询都可以有单独的索引被使用。
8、多列查询该如何建索引?
一次查询只能用到一个索引,所以 首先枪毙 a,b各建索引方案
a还是b? 谁的区分度更高(同值的最少),建谁!
当然,联合索引也是个不错的方案,ab,还是ba,则同上,区分度高者,在前
9、联合索引的问题?
where a = “xxx” 可以使用 AB 联合索引
where b = “xxx” 则不可 (再想象一下,这是书的目录?)
所以,大多数情况下,有AB索引了,就可以不用再去建一个A索引了
10、哪些常见情况不能用索引?
-
like “%xxx”
-
not in , !=
-
对列进行函数运算的情况(如 where md5(password) = “xxxx”)
-
WHERE index=1 OR A=10
-
存了数值的字符串类型字段(如手机号),查询时记得不要丢掉值的引号,否则无法用到该字段相关索引,反之则没关系
也即
select * from test where mobile = 13711112222;
可是无法用到mobile字段的索引的哦(如果mobile是char 或 varchar类型的话)
btw,千万不要尝试用int来存手机号(为什么?自己想!要不自己试试)
11、覆盖索引(Covering Indexes)拥有更高效率
索引包含了所需的全部值的话,就只select 他们,换言之,只select 需要用到的字段,如无必要,尽量避免select *
12、NULL 的问题
NULL会导致索引形同虚设,所以在设计表结构时应避免NULL 的存在(用其他方式表达你想表达的NULL,比如 -1?)
13、如何查看索引信息,如何分析是否正确用到索引?
show index from tablename;
explain select ……;
关于explain,改天可以找个时间专门写一篇入门帖,在此之前,可以尝试 google
14、了解自己的系统,不要过早优化!
过早优化,一直是个非常讨厌而又时刻存在的问题,大多数时候就是因为不了解自己的系统,不知道自己系统真正的承载能力
比如:几千条数据的新闻表,每天几百几千次的正文搜索,大多数时候我们可以放心的去like,而不要又去建一套全文搜索什么的,毕竟cpu还是比人脑厉害太多。
15、后记:
看到这儿,也许你花了不止10分钟,但建议多看几遍,有条件的话最好上机尝试加深理解,纸上得来终觉浅,绝知此事要躬行嘛~
还有这篇文章几乎覆盖了《高性能MYSQL》中索引一章的所有结论,建议和那本书参照起来一起看,也许那本书就不那么难读了~
Refer:
[1] mysql索引需要了解的几个注意
[2] mysql 查询可以同时使用几条索引
[3] MySQL 索引详解
http://my.oschina.net/leejun2005/blog/134932?fromerr=DQVDBPHK
[4] 关于mysql 索引自动优化机制: 索引选择性(Cardinality:索引基数)
http://my.oschina.net/leejun2005/blog/75688
[5] 如何应对并发(1) - 关于数据索引
[6] 如何应对并发(2) - 请求合并及异步处理