10 分钟 MySQL 索引从入门到精通

标签: mysql | 发表时间:2015-11-19 15:20 | 作者:
出处:http://my.oschina.net/leejun2005

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索引需要了解的几个注意

http://thephper.com/?p=142

[2] mysql 查询可以同时使用几条索引

http://dwz.cn/2cJuCH

[3] MySQL 索引详解

http://my.oschina.net/leejun2005/blog/134932?fromerr=DQVDBPHK

[4] 关于mysql 索引自动优化机制: 索引选择性(Cardinality:索引基数)

http://my.oschina.net/leejun2005/blog/75688

[5] 如何应对并发(1) - 关于数据索引

http://dwz.cn/2cJwCQ

[6] 如何应对并发(2) - 请求合并及异步处理

http://dwz.cn/2cJxgS

相关 [mysql 索引] 推荐:

[MySQL] B+树索引

- - CSDN博客推荐文章
B+树是一种经典的数据结构,由平衡树和二叉查找树结合产生,它是为磁盘或其它直接存取辅助设备而设计的一种平衡查找树,在B+树中,所有的记录节点都是按键值大小顺序存放在同一层的叶节点中,叶节点间用指针相连,构成双向循环链表,非叶节点(根节点、枝节点)只存放键值,不存放实际数据. 保持树平衡主要是为了提高查询性能,但为了维护树的平衡,成本也是巨大的,当有数据插入或删除时,需采用拆分节点、左旋、右旋等方法.

mysql 索引技巧

- - 小彰
MySQL索引的建立对于MySQL的高效运行是很重要的. 下面介绍几种常见的MySQL索引类型. 在数据库表中,对字段建立索引可以大大提高查询速度. 假如我们创建了一个 mytable表:. CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL  );   我们随机向里面插入了10000条记录,其中有一条:5555, admin.

mysql选择索引

- - CSDN博客数据库推荐文章
1、尽量为用来搜索、分类或分组的数据列编制索引,不要为作为输出显示的数据列编制索引. 最适合有索引的数据列是那些在where子句中数据列,在联结子句中出现的数据列,或者是在Group by 、Order by子句中出现的数据列. select 后的数据列最好不要用索引. 2、综合考虑各数据列的维度.

mysql 索引详解

- - 行业应用 - ITeye博客
本文以MySQL数据库为研究对象,讨论与数据库索引相关的一些话题. 特别需要说明的是,MySQL支持诸多存储引擎,而各种存储引擎对索引的支持也各不相同,因此MySQL数据库支持多种索引类型,如BTree索引,哈希索引,全文索引等等. 为了避免混乱,本文将只关注于BTree索引,因为这是平常使用MySQL时主要打交道的索引,至于哈希索引和全文索引本文暂不讨论.

mysql索引认识

- - 数据库 - ITeye博客
数据在磁盘中是以 “块”的形式存储的,所以一张表涉及的数据可能会存在多个块中,而在磁盘中查询数据则会根据字段是否为有序与无序来区分,. 无序情况:1.数值具有唯一性则需要查找 总块数/2.                   2.无序+无唯一性则需要查找  总块数. 有序情况:1.数值唯一性:log2(总块数/2)   (log2是二分查找算法).

MySQL 索引方式

- - zzm
本文配图来自《高性能MySQL(第二版)》. 在数据库中,对性能影响最大的几个策略包括数据库的锁策略、缓存策略、索引策略、存储策略、执行计划优化策略. 索引策略决定数据库快速定位数据的效率,存储策略决定数据持久化的效率. MySQL中两大主要存储引擎MyISAM和InnoDB采用了不同的索引和存储策略,本文将分析它们的异同和性能.

MySQL InnoDB B+树索引

- - OurMySQL
B+树索引在DB中有一个特点就是高扇出性,一般在DB中B+树的高度在2-3层左右,也就意味着只需要2-3次的IO操作即可. 而现在的磁盘每秒差不多在100次IO左右,2-3次意味着查询时间只需0.02-0.03秒. InnoDB存储引擎表是索引组织表,即表中数据安装主键顺序存放. 而聚集索引就是按照每张表的主键构造一颗B+,并且叶节点存放着整张表的行记录数据,因此也让聚集索引也是索引的一部分.

MySQL B+树索引及索引优化

- - 数据库 - ITeye博客
    MySQL的索引实现由很多种实现,包括hash索引,B+索引,全文索引等,本文只讨论B+树索引. 1.评价一个索引好坏主要看IO的访问次数,B+树红黑树来说,树高很小(出度很大)即可以有效降低IO的访问次数. B+数的高度h=logd(n),d越大,h越小,查询效率越高. 相对B树,B+树d可以很大,因为非叶子节点不存储数据,只存储key,在一个存储页上可以存储更多的key值.

Mysql中B+树索引的管理

- - CSDN博客数据库推荐文章
索引的创建和删除可以通过两种方法;一种是alter table ,另一种是create /drop index.alter table 创建索引的语法: alter table tbl_name |ADD {INDEX|KEY} {INDEX_NAME} {INDEX_TYPE}(INDEX_COL_NAME,......)[INDEX_OPTION].

mysql索引原理之B+/-Tree

- - CSDN博客架构设计推荐文章
索引,是为了更快的查询数据,查询算法有很多,对应的数据结构也不少,数据库常用的索引数据结构一般为B+Tree. 关于B-Tree的官方定义个人觉得比较难懂,通俗一点就是举个例子. 假如:一本英文字典,单词+详细解释组成了一条记录,现在需要索引单词,那么以单词为key,单词+详细解释为data,B-Tree就是以一个二元组{key,data}来定义一条记录.