MyException - 我的异常网
当前位置:我的异常网» 数据库 » MySQL数据库目录

MySQL数据库目录

www.MyException.Cn  网友分享于:2013-08-11  浏览:0次
MySQL数据库索引

为什么使用索引?

在无索引的情况下,MySQL会扫描整张表来查找符合sql条件的记录,其时间开销与表中数据量呈正相关。对关系型数据表中的某些字段建索引可以极大提高查询速度(当然,不同字段是否selective会导致这些字段建立的索引对查询速度的提升幅度不同,而且索引也并非越多越好,因为写入或删除时需要更新索引信息)。

对于MySQL的Innodb储存引擎来说,大部分类型的index均以B-Tree数据结构的变种B+Tree来存储(MEMORY类型的表还支持hash类型的索引)。B-Tree是数据库或文件系统中常用的一种数据结构,它是一种N叉平衡树,这种树结构保证了同层节点保存的key有序,对于某个节点来说,其左子树保存的所有key均小于该节点保存的key,其右子树保存的所有key均大于该节点保存的key。此外,在工程实现上,还结合操作系统的局部性原理做了很多优化,总之,b-tree的各种特性或优化技巧能保证:1) 查询磁盘记录时,读盘次数最少;2) 任何insert和delete操作对树结构的影响均很小;3) 树本身的rebalance操作很高效。

 

MySQL使用索引的场景

MySQL在以下操作场景下会使用索引:

1) 快速查找符合where条件的记录

2) 快速确定候选集。若where条件使用了多个索引字段,则MySQL会优先使用能使候选记录集规模最小的那个索引,以便尽快淘汰不符合条件的记录。

3) 如果表中存在几个字段构成的联合索引,则查找记录时,这个联合索引的最左前缀匹配字段也会被自动作为索引来加速查找。

例如,若为某表创建了3个字段(c1, c2, c3)构成的联合索引,则(c1), (c1, c2), (c1, c2, c3)均会作为索引,(c2, c3)就不会被作为索引,而(c1, c3)其实只利用到c1索引。

4) 多表做join操作时会使用索引(如果参与join的字段在这些表中均建立了索引的话)

5) 若某字段已建立索引,求该字段的min()或max()时,MySQL会使用索引

6) 对建立了索引的字段做sort或group操作时,MySQL会使用索引

 

哪些SQL语句会真正利用索引

从MySQL官网文档"Comparison of B-Tree and Hash Indexes"可知,下面这些类型的SQL可能会真正用到索引:

1) B-Tree可被用于sql中对列做比较的表达式,如=, >, >=, <, <=及between操作

2) 若like语句的条件是不以通配符开头的常量串,MySQL也会使用索引

比如,SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%'或SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%'可以利用索引,而SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%'(以通配符开头)和SELECT * FROM tbl_name WHERE key_col LIKE other_col(like条件不是常量串)无法利用索引。

对于形如LIKE '%string%'的sql语句,若通配符后面的string长度大于3,则MySQL会利用Turbo Boyer-Moore algorithm算法进行查找。

3) 若已对名为col_name的列建了索引,则形如"col_name is null"的SQL会用到索引

4) 对于联合索引,sql条件中的最左前缀匹配字段会用到索引

5) 若sql语句中的where条件不只1个条件,则MySQL会进行Index Merge优化来缩小候选集范围

 

 

索引分单列索引和组合索引。单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。组合索引,即一个索包含多个列。

1)普通索引

创建索引:

CREATE INDEX indexName ON tableName(columName);

删除索引:

DROP INDEX [indexName] ON mytable; 

 

2) 唯一索引

与普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。

创建索引:

CREATE UNIQUE INDEX indexName ON tableName(columName); 

 

3) 主键索引

它是一种特殊的唯一索引,不允许有空值。一般是在建表的时候同时创建主键索引。

 

4) 组合索引

 

这里,着重讲一下,单列索引和组合索引的区别和各自应该使用的场合。

1.首先要确定优化的目标,在什么样的业务场景下,表的大小等等。如果表比较小的话,可能都不需要加索引。

2.哪些字段可以建索引,一般都where、order by 或者 group by 后面的字段。

3.记录修改的时候需要维护索引,所以会有开销,要衡量建了索引之后的得与失。

 

例如:学生表。可以认为name的重复度比较小,而age的重复度比较大,对于单列索引来说,比较适合建在重读度低的列上。

对于select * from students where name='张三’and age=18; 分为两种情况 :
A. name 和 age 各自单独建立索引
一般来说mysql会选择其中一个索引,name的可能性比较大,因为mysq会统计每个索引上的重复度,选用低重复度的字段。另外一个age的索引就不会用到,但还有维护索引的开销,所以age的索引不需要创建。

B. name和age的联合索引
这种索引的切合度最好,mysql会直接选用这个索引。但相对单独的name索引来说,维护的成本要大一些,并且索引数据占用的存储空间也要更大一些。

 

那么综合看来,有必要使用联合索引吗? 我的看法是没有必要,因为学校里可能会重名的人比较少。用name就可以比较精准的找到记录,即使有重复的也会比较少。

 

什么情况下使用联合索引比较好呢?
举一个例子,大学选认课老师,需要创建一个关系对应表,有2个字段,student_id 和 teacher_id,想要查询某个老师和某个学生是否存在师生关系。 
一个学生会选几十个老师,一个老师会带几百个学生。
如果只为student_id建立索引的情况下,经过索引会选出几十条记录,然后在内存中where一下,去除其余的老师。
相反如果只为teacher_id建立索引,经过索引会选出几百条记录,然后在内存中where一下,去除其余的学生。
两种情况都不是最优的,这个时候使用联合索引最合适,通过索引直接找到对应记录。

 

再进行一个例子的分析:

 

CREATE TABLE myIndex (
testID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
name VARCHAR(50) NOT NULL, 
city VARCHAR(50) NOT NULL, 
age INT NOT NULL, 
schoolID INT NOT NULL
);
 

 

插入10000条数据,其中有六条name = 'jack' 的记录,但city,age,school 的组合各不相同。

来看这条T-SQL:SELECT testID FROM myIndex WHERE name='jack' AND city='上海' AND age=28;

首先考虑单列索引:

在name列上建立了索引。执行 T-SQL 时,MYSQL 很快将目标锁定在了name=jack的 6 条记录上,取出来放到一中间结果集。在这个结果集里,先排除掉 city 不等于"上海"的记录,再排除 age 不等于 28 的记录,最后筛选出唯一的符合条件的记录。

虽然在 vc_Name 上建立了索引,查询时MYSQL不用扫描整张表,效率有所提高,但离我们的要求还有一定的距离。同样的,在 city 和age 分别建立的MySQL单列索引的效率相似。

 

 

为了进一步榨取 MySQL 的效率,就要考虑建立组合索引。就是将 name,city,age 建到一个索引里:

ALTER TABLE myIndex ADD INDEX name_city_age (name(10), city, age);

 

建表时,name 长度为 50,这里为什么用 10 呢?因为一般情况下名字的长度不会超过 10,这样会加速索引查询速度,还会减少索引文件的大小,提高 INSERT 的更新速度。

执行 T-SQL 时,MySQL 无须扫描任何记录就到找到唯一的记录。

 

如果分别在 name,city,age 上建立单列索引,让该表有 3 个单列索引,查询时和上述的组合索引效率一样吗?大不一样,远远低于我们的组合索引。虽然此时有了三个索引,但 MySQL 只能用到其中的那个它认为似乎是最有效率的单列索引。

 

建立这样的组合索引,其实是相当于分别建立了:(name, city, age) 、(name, city) 、(name) 这样的三个组合索引。因为 mysql 组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引。

 

http://blog.csdn.net/weiwangchao_/article/details/50256673

 

文章评论

10个帮程序员减压放松的网站
10个帮程序员减压放松的网站
程序员都该阅读的书
程序员都该阅读的书
Web开发人员为什么越来越懒了?
Web开发人员为什么越来越懒了?
鲜为人知的编程真相
鲜为人知的编程真相
老程序员的下场
老程序员的下场
初级 vs 高级开发者 哪个性价比更高?
初级 vs 高级开发者 哪个性价比更高?
要嫁就嫁程序猿—钱多话少死的早
要嫁就嫁程序猿—钱多话少死的早
5款最佳正则表达式编辑调试器
5款最佳正则表达式编辑调试器
Java 与 .NET 的平台发展之争
Java 与 .NET 的平台发展之争
60个开发者不容错过的免费资源库
60个开发者不容错过的免费资源库
2013年美国开发者薪资调查报告
2013年美国开发者薪资调查报告
 程序员的样子
程序员的样子
Java程序员必看电影
Java程序员必看电影
什么才是优秀的用户界面设计
什么才是优秀的用户界面设计
总结2014中国互联网十大段子
总结2014中国互联网十大段子
那些性感的让人尖叫的程序员
那些性感的让人尖叫的程序员
聊聊HTTPS和SSL/TLS协议
聊聊HTTPS和SSL/TLS协议
我是如何打败拖延症的
我是如何打败拖延症的
“肮脏的”IT工作排行榜
“肮脏的”IT工作排行榜
如何成为一名黑客
如何成为一名黑客
程序员的鄙视链
程序员的鄙视链
Google伦敦新总部 犹如星级庄园
Google伦敦新总部 犹如星级庄园
编程语言是女人
编程语言是女人
程序员眼里IE浏览器是什么样的
程序员眼里IE浏览器是什么样的
十大编程算法助程序员走上高手之路
十大编程算法助程序员走上高手之路
2013年中国软件开发者薪资调查报告
2013年中国软件开发者薪资调查报告
Web开发者需具备的8个好习惯
Web开发者需具备的8个好习惯
程序员的一天:一寸光阴一寸金
程序员的一天:一寸光阴一寸金
每天工作4小时的程序员
每天工作4小时的程序员
我跳槽是因为他们的显示器更大
我跳槽是因为他们的显示器更大
代码女神横空出世
代码女神横空出世
中美印日四国程序员比较
中美印日四国程序员比较
如何区分一个程序员是“老手“还是“新手“?
如何区分一个程序员是“老手“还是“新手“?
为啥Android手机总会越用越慢?
为啥Android手机总会越用越慢?
程序员周末都喜欢做什么?
程序员周末都喜欢做什么?
写给自己也写给你 自己到底该何去何从
写给自己也写给你 自己到底该何去何从
当下全球最炙手可热的八位少年创业者
当下全球最炙手可热的八位少年创业者
那些争议最大的编程观点
那些争议最大的编程观点
做程序猿的老婆应该注意的一些事情
做程序猿的老婆应该注意的一些事情
漫画:程序员的工作
漫画:程序员的工作
程序员必看的十大电影
程序员必看的十大电影
程序员和编码员之间的区别
程序员和编码员之间的区别
老美怎么看待阿里赴美上市
老美怎么看待阿里赴美上市
程序员最害怕的5件事 你中招了吗?
程序员最害怕的5件事 你中招了吗?
10个调试和排错的小建议
10个调试和排错的小建议
旅行,写作,编程
旅行,写作,编程
为什么程序员都是夜猫子
为什么程序员都是夜猫子
不懂技术不要对懂技术的人说这很容易实现
不懂技术不要对懂技术的人说这很容易实现
亲爱的项目经理,我恨你
亲爱的项目经理,我恨你
软件开发程序错误异常ExceptionCopyright © 2009-2015 MyException 版权所有