MySQL索引

1. 索引简介

索引用于快速找出某个列中有一特定值的行.

比如有一个学生表,有一个字段是学号s_id,共有10000个学生,要查询学号为9999的学生,如果没有索引那么就需要遍历10000条记录,如果有了索引,则直接命中.

定义:索引是对数据库表一列或多列的值进行排序的一种结构.

用处: 使用索引可提高数据库中特定数据的查询速度

1.1 索引的含义和特点

索引的两种类型: BTREE和HASH
INNODB和MyISAM引擎只支持BTREE
MEMORY/HEAP引擎只支持HASH

INNODB是mysql默认的引擎,也是我们最常用的引擎, 查看使用的MySQL的引擎的语句:

show engines;

使用索引的优点:

• 唯一索引可保证数据库表中每一行数据的唯一性.
• 大大加快查询速度
• 可以加速表和表之间的连接
• 可显著减少分组和排序的时间.

使用索引的缺点:

• 创建和维护索引要消耗时间
• 占磁盘空间
• 对表中数据新增、修改、删除时,索引也要动态的维护,耗时

1.2 索引的分类

索引分为: 普通索引、唯一索引、单列索引、组合索引、全文索引、空间索引

1.2.1 普通索引和唯一索引

普通索引: 可插入重复值和空值

唯一索引: 列值必须唯一,但可有空值

组合索引: 列值的组合必须唯一

主键索引: 特殊的唯一索引,不允许空值

1.2.2单列索引和组合索引

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

组合索引: 多个字段组合创建的索引.在查询中使用了这些字段的左边字段时,索引才会被引用

1.2.3 全文索引

• FULLTEXT,列值支持全文查找,可存入空值和重复值,

• 可以创建在char、varchar或text类型的列上

• 只有MyISAM存储引擎支持

1.2.4 空间索引

• 对空间数据类型字段建立的所以呢
• 四种空间类型: GEPMETRY POINT LINESTRING POLYGON
• 使用SPQTIAL关键字扩展创建正规索引的语法来创建空间so因
• 列必须为NOT NULL,只能在MyISAM的表中创建

1.3 索引的设计原则

• 索引并非越多越好,占空间、影响新增、修改、删除的性能
• 不对经常更新的表进行过多的索引,并使索引的列尽可能少,但对经常查询的字段创建索引.
• 数据量小不要使用索引
• 在条件表达式中不同值多的列上建立索引.
• 唯一的值使用唯一索引
• 频繁排序或分组的列上建立索引.

2. 实战 使用navicat创建索引

有一个学生表,有五个字段,分别是姓名、年龄、主键id、性别和学号

使用explain分析查询语句,查询学号为10的学生的信息.

返回的结果有一列是rows,这一列代表了查询了多少条数据.,我们可以看到一共十条数据,这里查询了十条.

2.1 添加索引

在navicat中,右键设计表,然后在索引那一列设置索引

Index Type 共有四个选项,分别是唯一索引、普通索引、全文索引、空间索引,因为我使用的是MySQL默认的INNODB所以只能选择唯一索引和普通索引,选择其他两项会报错,又因为学号是唯一的,所以选择唯一索引会提高查询速度.

Index method同样有两个选择,一个是BTREE,另一个是HASH,INNODB只能选择BTREE

索引设置好了 我们再次使用分析查询语句查看结果

rows这列变为1了, 直接命中!!!!!!!!

2.2 组合索引

navicat中组合索引的设置方式

我们来看看查询分析语句

将s_id 、age、name 设置为组合索引.

只有在使用了s_id的情况下查询分析语句的rows这一列的值才是1.

FBI WARNING: 使用多列索引的查询语句(假如一个索引由a、b、c三个字段组成,必须使用a字段,查询时才能使索引生效,最多包涵16个字段)

3. 建表语句&查询语句

建表语句和查询语句已经上传到GitHub,有兴趣的朋友可以下载下来在MySQL中执行一下

GitHub地址: https://github.com/liuboren0617/MySQL

4. 结语

接下来博主会更新MySQL性能优化的博客,敬请期待..