MySQL性能优化(三)-- 索引

栏目: 数据库 · 发布时间: 4年前

内容简介:索引是一种数据结构索引的特点:查找速度快,排好序,数据结构索引的数据结构类型有:BTREE索引和HASH索引,下面展示的是BTREE索引。

索引是一种数据结构

索引的特点:查找速度快,排好序,数据结构

索引的数据结构类型有:BTREE索引和HASH索引,下面展示的是BTREE索引。

BTREE:balance tree (平衡树)

BTREE的特点实例:

假设有一张表,表中的数据为下图中的左侧,则索引中数据为下图中的右侧:

MySQL性能优化(三)-- 索引

如果查询id为9,name为ii的,在表中需要查询9次,但是在二叉树中需要查询3次。

二、索引的“类型”

1.聚集索引:节点就是数据本身,即索引表中存的就是数据本身

2.非聚集索引:节点仍然是索引节点,只不过有指向对应数据块的指针,上面所说的BTREE索引就是非聚集索引

聚集索引的速度比非聚集索引快。

三、索引的类型

1.单列索引(一个索引只包含一个列,一个表可以有多个单列索引)

1-1.主键索引:primary key

创建主键索引,有两种方式,其中t1和t2位表名,id为列:

1)建表的时候创建:create table t1 (id int primary key);

2)通过alter语句:alter table t2 add primary key(id);

1-2.普通索引:index,最基本的索引

创建普通索引:

(1)create index 索引名 on 表(列),

例如: create index idx_id on t3(id);

(2)通过alter语句:alter table t4 add index(id);

1-3.唯一索引:unique

创建唯一索引:

(1)建表的时候创建:create table t5 (id int unique);

(2)create unique index 索引名 on 表(列),

例如:create unique index idx_id on t5(id);

注意:unique的字段可以为null,也可以重复,“”不可以重复出现。

1-4.全文索引:fulltext

数据库自带的全文索引,对中文支持不友好,可以借助第三方的框架,如:sphinx(斯芬克斯)、coreseek

2.多列索引(组合索引,一个组合索引包含两个或两个以上的列)

创建:create index idx_列名1_列名2 on 表名(列名1,列名2)

实例:

建表:create table student(id int,name varchar(20),address varchar(20),remark varchar(20));

建组合索引:create index idx_name_address_remark on student(name,address,remark);

查询 sql 是否使用到了索引,可以使用explain进行分析,后续会给出介绍。

在使用查询的时候遵循 mysql 组合索引的“最左前缀”,where时的条件要按建立索引的时候字段的 排序 方式,下面都是基于多列索引讲述的:

1、不按索引最左列开始查询

(1) where address='深圳' 不会走索引

MySQL性能优化(三)-- 索引

(2)where address = '深圳' and remark='程序员' 不会走索引

MySQL性能优化(三)-- 索引

2、查询中某个列有范围查询,则其右边的所有列都无法使用查询(多列查询)

where name='xbq' and address like '%深%' and remark='程序员' ,该查询只会使用索引中的前两列,因为like是范围查询

MySQL性能优化(三)-- 索引

3、查询中第一个索引字段出现like '%xxx%'或者'%xxx',不会走索引

MySQL性能优化(三)-- 索引

4.查询中多条件用or连接,此类型和1相似

MySQL性能优化(三)-- 索引

那么对于索引怎么删除呢?删除索引:alter table 表名 drop index 索引名称

查询索引有两种方式:

  • show index from t1;
  • show keys from t1;

四、索引的优点和缺点

优点:

1.可以通过建立唯一索引或者主键索引,保证数据库表中每一行数据的唯一性.

2.提高检索速度,降低磁盘读取IO

索引是排序好的,不需要进行全表扫描,降低了数据排序的运算成本,也就是降低了CPU的消耗

缺点:

1.索引也需要存储,所以也需要空间,实际上索引也是一张表,保存了索引字段的值和指向实体表的指针

2.降低更新(增删改)表的速度,更新不仅仅只是数据本身,如果数据正好是索引字段,同时需要更新索引信息

当索引字段对应的数据改变了,则索引表也会改变,例如,当图书馆中 的书柜和书,书的类型为科普类,书柜上的目录也是科普类,

当此书柜中的书改变了位置的时候,则对应的书柜的目录表也要改变。

欢迎关注我的公众号,第一时间接收最新文章~ 搜索公众号:码咖或者 扫描下方二维码:

MySQL性能优化(三)-- 索引

以上所述就是小编给大家介绍的《MySQL性能优化(三)-- 索引》,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持!

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

技术元素

技术元素

[美] 凯文·凯利 / 张行舟、余倩、周峰、管策、金鑫、曾丹阳、李远、袁璐 / 译言·东西文库/电子工业出版社 / 2012-5 / 55.00元

我会将我不成熟的想法、笔记、内心争论、草稿以及对其他文章的回应都写在《技术元素》中,这样我就能知道自己到底在想些什么。——KK “技术元素”(technium)是凯文•凯利专门创造出来的词语。“技术元素不仅仅包括一些具象的技术(例如汽车、雷达和计算机等),它还包括文化、 法律、社会机构和所有的智能创造物。”简而言之,技术元素就是从人的意识中涌现出来的一切。KK把这种科技的延伸面看成一个能产生......一起来看看 《技术元素》 这本书的介绍吧!

CSS 压缩/解压工具
CSS 压缩/解压工具

在线压缩/解压 CSS 代码

MD5 加密
MD5 加密

MD5 加密工具

UNIX 时间戳转换
UNIX 时间戳转换

UNIX 时间戳转换