Mysql索引分类及其使用实例详解
MySQL 索引
MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。打个比方,如果合理的设计且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车。
拿汉语字典的目录页(索引)打比方,我们可以按拼音、笔画、偏旁部首等排序的目录(索引)快速查找到需要的字。索引分单列索引和组合索引。单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。组合索引,即一个索引包含多个列。创建索引时,你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录。上面都在说使用索引的好处,但过多的使用索引将会造成滥用。因此索引也会有它的缺点:虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。
单列索引又可以叫普通索引,一个索引只包含一个列,一个表中可以有多个单列索引.
创建单列索引的几种方式:外部创建
CREATE INDEX indexName ON table_name (column_name)修改表结构(添加索引)
ALTER table tableName ADD INDEX indexName(columnName)创建表的时候直接指定
如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定 length。
CREATE TABLE mytable(ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX [indexName] (username(length))
它与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。它有以下几种创建方式:
创建唯一索引的几种方式:外部创建
CREATE UNIQUE INDEX indexName ON mytable(username(length))修改表结构(添加索引)
ALTER table mytable ADD UNIQUE [indexName] (username(length))创建表的时候直接指定
CREATE TABLE mytable(ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
UNIQUE [indexName] (username(length))
联合索引(复合索引)
复合索引是索引中功能最强大的一个,索引能够同时覆盖多个数据列,
创建联合索引(复合索引)的方式:外部创建
CREATE INDEX indexName ON mytable(c1,c2,c3 ) Mysql的索引类型 INDEX | NORMAL 普通索引大多数情况下都可以使用,允许出现相同的索引内容。
UNIQUE 唯一索引不可以出现相同的值,可以有NULL值,如果该字段信息保证不会重复例如身份证号用作索引时,可以设置为UNIQUE
约束唯一标识数据库表中的每一条记录,即在单表中不能用每条记录是唯一的(例如身份证就是唯一的),UNIQUE(要求列唯一)和Primary Key(primary key = unique + not null 列唯一)约束均为列或列集合中提供了唯一性的保证,Primary Key是拥有自动定义的UNIQUE约束,但是每个表中可以有多个UNIQUE约束,但是只能有一个Primary Key约束。
PRIMARY KEY 主键索引不允许出现相同的值,且不能为NULL值,一个表只能有一个PRIMARY KEY索引。
FULLTEXT 全文索引全文索引,可以针对值中的某个单城,比如一篇文章中的某个词,然而并没有什么卵用,因为只有myisam以及英文支持,并且效率让人不敢恭维,但是可以用coreseek和xunsearch等第三方应用来完成这个需求。
SPATIAL 空间索引空间索引是对空间数据类型的字段建立的索引,MYSQL中的空间数据类型有4种,分别是GEOMETRY、POINT、LINESTRING、POLYGON。MYSQL使用SPATIAL关键字进行扩展,使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列,必须将其声明为NOT NULL,空间索引只能在存储引擎为MYISAM的表中创建。
Mysql的索引方法 BTREEB树(可以是多叉树),mysql默认使用的方法,通过BTREE算法建立索引的字段,比如扫描20行就能得到未使用BTREE前扫描了2^20行的结果。
哈希算法,哈希算法通过建立特征值,然后根据特征值来快速查找。这种方式对范围查询支持得不是很好
hash 索引结构的特殊性,其检索效率非常高,索引的检索可以一次定位,不像 BTREE 索引需要从根节点到枝节点,最后才能访问到页节点这样多次的IO访问,所以 Hash 索引的查询效率要远高于 BTREE 索引。
可能很多人又有疑问了,既然 Hash 索引的效率要比 BTREE 高很多,为什么大家不都用 Hash 索引而还要使用 BTREE 索引呢?任何事物都是有两面性的,Hash 索引也一样,虽然 Hash 索引效率高,但是 Hash 索引本身由于其特殊性也带来了很多限制和弊端,主要有以下这些。
(1)Hash 索引仅仅能满足 = , IN 和 = 查询,不能使用范围查询。
由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值,所以它只能用于等值的过滤,不能用于基于范围的过滤,因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系,并不能保证和Hash运算前完全一样。
(2)Hash 索引无法被用来避免数据的排序操作。
由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值,而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样,所以数据库无法利用索引的数据来避免任何排序运算;
(3)Hash 索引不能利用部分索引键查询。
对于组合索引,Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值,而不是单独计算 Hash 值,所以通过组合索引的前面一个或几个索引键进行查询的时候,Hash 索引也无法被利用。
(4)Hash 索引在任何时候都不能避免表扫描。
前面已经知道,Hash 索引是将索引键通过 Hash 运算之后,将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中,由于不同索引键存在相同 Hash 值,所以即使取满足某个 Hash 键值的数据的记录条数,也无法从 Hash 索引中直接完成查询,还是要通过访问表中的实际数据进行相应的比较,并得到相应的结果。
(5)Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。
对于选择性比较低的索引键,如果创建 Hash 索引,那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦,会浪费多次表数据的访问,而造成整体性能低下。
在实际操作过程中,应该选取表中哪些字段作为索引?
为了使索引的使用效率更高,在创建索引时,必须考虑在哪些字段上创建索引和创建什么类型的索引,有7大原则:
1.选择唯一性索引
2.为经常需要排序、分组和联合操作的字段建立索引
3.为常作为查询条件的字段建立索引
4.限制索引的数目
5.尽量使用数据量少的索引
6.尽量使用前缀来索引
7.删除不再使用或者很少使用的索引
8. 经常更新修改的字段不要建立索引(针对mysql说,因为字段更改同时索引就要重新建立,排序,而Orcale好像是有这样的机制字段值更改了,它不立刻建立索引,排序索引,而是根据更改个数,时间段去做平衡索引这件事的)
9、不推荐在同一列建多个索引
接下的所有索引操作都围绕这张表进行演示,在演示索引之前,先介绍一下explain,更多内容看菜鸟索引优化
mysql explain的作用是:
模拟Mysql优化器是如何执行SQL查询语句的,从而知道Mysql是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。(这里只做简单介绍,使用方法,在select语句前加上explain就行了)
单列索引使用示例1,为monitor_concentration表的site_number字段创建普通索引
创建索引 siteNumber:索引名称唯一,monitor_concentration:表名,site_number:字段名CREATE INDEX siteNumber ON monitor_concentration(site_number)
成功创建如下:
使用explain 优化查询检测语,查看使用索引和没有使用索引的区别
select site_number from monitor_concentration where site_number = 2036没有使用索引前:
可以看到没有使用索引时,查询时间为2.132秒
使用索引后:
可以使用了索引在此执行这个语句,查询时间为0.598秒(差距明显)
创建的索引不作为条件使用
可以看到返回值用到了索引,在查询该列的时候一样有效,效率比没有索引更高
与其他字段配合使用索引
结果一
结果二
结果三
总结单列索引使用方式:可以看出,我们为字段siteNumber创建了索引,通过它来作为条件和返回语句时(作为where条件有它,返回值也有它。不作为where条件,返回值有它即可),查询的时候是能快速的帮助我们实现效果,但是与其他字段在一起使用的时候。起不到效果。所以我们在对单个列作为查询的时候可以使用单列索引。如果想多个字段都能组合使用,下面我使用复合索引来实现(这种方式比单列更常用)
复合索引使用示例1,为monitor_concentration表的site_number,date_time字段创建复合索引
创建复合索引CREATE INDEX idx_c1_c2 ON monitor_concentration(date_time,site_number)
成功创建如下:
使用
结果一
结果二
结果三
结果四
结果五
结果六
总结复合索引使用方式:从使用几个结果来看,复合索引做到了单列使用方式。在单列索引我们提出想要实现的效果,在复合索引中成功实现。复合索引比较灵活,我们可以根据实际需要来建立复合索引,下面在举一个列子。
1,给state,date_time创建复合索引
CREATE INDEX idx_st_dt ON monitor_concentration(state,date_time)使用
结果一
结果二
这里就不做过多结果展示了,前面使用复合索引页演示过了(注意之前的结果五和结果六,我这里要做一点更改),我将演示这样的效果
需求,查询monitor_concentration表state=3的最新时间
select max(date_time) date_time from monitor_concentration where state = 3创建索引后,怎么查询还这么慢,查询时间8.441秒
看到了吗,我们已经为state和date_time建立了复合索引,可是我们这里并没有生效哦!
因为使用聚合函数的列不能使用索引(可是我就是想用到索引怎么办呢。。。可以实现的)
删掉刚才创建的复合索引,重新创建复合索引,这里与上面创建的时候区别在于state和date_time交换了位置
CREATE INDEX idx_st_dt ON monitor_concentration(date_time,state)重新使用
select max(date_time) date_time from monitor_concentration where state = 3可以看到,比刚才有明显的差距,查询时间1.819
可以看到,这次我们创建的复合索引是有效的
创建复合索引字段顺序总结:通过列子可以看到,如果我们在使用比较特殊的语句,想要使其生效的话,我们对复合索引字段的顺序需要注意一下。从这里得出,作为条件的列应当放在返回使用的列后面
我想要获取技术服务或软件
服务范围:MySQL、ORACLE、SQLSERVER、MongoDB、PostgreSQL 、程序问题
服务方式:远程服务、电话支持、现场服务,沟通指定方式服务
技术标签:数据恢复、安装配置、数据迁移、集群容灾、异常处理、其它问题
本站部分文章参考或来源于网络,如有侵权请联系站长。
数据库远程运维 Mysql索引分类及其使用实例详解
相关文章
- MySQL解锁:免密码登录的简单操作(mysql跳过密码登陆)
- 词MySQL:如何实现快速名词查询(mysql实例名)
- php 7安装MySQL:从入门到精通(php7安装mysql)
- 缓存高效利用多实例 MySQL 缓存,提升性能(多实例mysql)
- 掌握MySQL中的所有查询函数(mysql查询所有函数)
- 复制MySQL主主复制解决数据冗余、高可用性(mysql主主)
- MySQL 函数处理事务实例(mysql函数事务)
- MySQL索引:加快查询速度的利器(mysql索引有什么用)
- MySQL快速复制创建相同表(mysql创建相同的表)
- MySQL 字符集函数简介(mysql字符集函数)
- MySQL编程实例:25个精选实践经验分享(mysql编程实例)
- MySQL如何实现多实例?(mysql多实例)
- MySQL组合索引:优化查询效率的利器(mysql组合索引)
- MySQL数据表的整形设计与优化方法(mysql整形)
- MySQL索引:提高查询效率的不可或缺策略(mysql索引建立)
- MySQL新建索引:最佳实践(mysql新建索引)
- 深入探究MySQL复合索引的顺序选择问题(mysql复合索引顺序)
- 使用MySQL主键约束确保数据完整性(mysql主键约束)
- 如何选择适合你的MySQL下载版本?(mysql下载哪个)
- 如何利用CMD快速进入MySQL数据库(cmd进入mysql数据库)
- 将CSV文件导入MySQL数据库(csv文件存到mysql)
- MySQL 32进制乱码解决方案(32进制乱码mysql)
- MySQL中的不可见索引对数据库性能的影响(mysql不可见索引)
- mysql锁表锁行语句分享(MySQL事务处理)