MySQL索引作用和用法详解 |
您所在的位置：网站首页 › mysql索引有什么 › MySQL索引作用和用法详解 |
|
一、引言
对于该文章，照例尽量通俗的写，并注明一些细节和可能出现问题的地方。此外，该文章只是写了索引的创建，不代表检索（查询）的应用，检索后面会写道。标注：红色表示十分重要，蓝色表示细节；在阅读这文章之前，如果初学MySQL不久，应已经熟悉：表约束操作、表的创建以及增删改查数据、熟知InnoDB和M有ISAM存储引擎、SQL数据类型更好
二、索引基本概念和功能
1.索引概念
索引本质：索引就是数据库表中字段值的复制，该字段称为索引的关键字索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录索引往往通过复杂的数据结构（双向链表、B+树/B树、hash）实现MyISAM存储引擎的表支持主索引，InnoDB存储引擎的表支持聚簇索引(主索引)与非聚簇索引(辅助索引)索引优化使用
2.索引的优缺点
@优点： MySQL的索引的建立对于MySQL的高效运行非常重要，可以大大提高MySQL的检索速度。在分组和排序字句进行数据检索,可以减少查询时间中 分组 和 排序时所消耗的时间(数据库的记录会重新排序)在表连接的连接条件 可以加速表与表直接的相连@缺点: 但过多使用索引会造成索引滥用，提高了检索速度，缺降低了表的更新速度如：对表进行增删改查操作时，MySQL不仅要保存数据，还要保存索引文件，建立索引会占用磁盘空间的索引文件 在创建索引和维护索引 会耗费时间,随着数据量的增加而增加 3.不使用索引的情况 查询中很少使用的列和很少数据的字段，不应该创建索引，否则会降低MySQL性能、增加空间需求text、image、bit数据类型字段不应该增加索引修改操作远大于检索操作，不应该增加索引 4.索引关键字选取规则 离散度高。例如性别只要男女，离散度低；而学号，每个人对应一个，离散度高。占用存储空间少的字段 。例如 字符串与 int ，选int存储空间固定的字段。例如text与char，选charwhere语句字段、group by分组字段、order by排序字段通常使用索引 ，单独order by 用不了索引，可以使用where/limit尽量使用前缀索引 ，即将字段值的前部分作为索引 前缀索引能有效减小索引文件的大小，提高索引的速度。但是前缀索引也有它的坏处：MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引 三、索引分类PS：此处没有讲MyISAM与InnoDB引擎，所以这个分类代表常用索引分类。后续参考索引拓展部分。该两个引擎在MySQL5.6版本之后，都支持以下索引。读者在阅读其它文章时，需要分辨一下。 1. 索引按用法技巧分为 单列索引 和 组合索引 (最左前缀索引或者多列索引或者复合索引) 单列索引：即一个索引只包含单个字段，一个表可以有多个单列索引组合索引： 即一个索引包含多个字段。遵从最左前缀原则 2. 单列索引： 普通索引:(最基本):没有任何约束的索引；通常创建外键约束时自动创建普通索引，索引名与外键约束名相同主键索引(主索引):与主键约束相关。通常在创建主键时自动创建了主索引，索引名与主键约束名相同+ 主键只能作用于一个列上，添加主键索引时，你需要确保该主键默认不为空（NOT NULL） 唯一索引 unique：与唯一约束相关。通常在创建唯一约束时自动创建唯一索引，索引名与唯一约束名相同+ 索引列的值必须唯一，但允许有空值 全文索引 fulltext：全文索引的诞生是为了解决模糊查询效率低的+ 全文索引支持的字段类型包括 char ，varchar ， text 。是目前实现大数据搜索的关键技术 3.组合索引即一个索引包含多个字段。遵从最左前缀原则。知道这点即可 四、索引的创建和删除语法 1.索引的创建按创建索引的时间分为两大类 创建表的时候创建索引在建表反括号之前 [无|unique|fulltext]index 索引名(字段名[(长度)][asc|desc]) [ ]内属于可选选项，根据参数创建不同索引 asc与desc表示可选参数，分别表示升序与降序，通常默认asc就行了 例如：创建一个名为information的表，依次定义唯一性、（普通、主）、全文索引、组合索引。 PS : 普通索引与主索引创建方式相同 ，故写一个 ，蓝色为索引名 create table information ( ID char(20) primary key, ID普通字符主键 name char(20）not null, 名字普通字符字段 introduction text not null, 介绍文本字段 price int , 价格整型字段 time_s date not null, 时间date类型字段 unique index ID_unique (No), 唯一性索引 index name_key (name(20)), 普通、主索引 fulltext index introName_fulltext (name,introduction), 全文索引 有name和introduction两个字段 index priTime_group(price,time_s) 组合索引 有price与time两个字段 ) engine = MyISAM default charset = utf8; 在已有的表上创建索引 （通常推荐该种创建索引的方式）主要有两种 CREATE [无|unique|fulltext]index索引名 ON 表名(字段名[(长度)][asc|desc]) ALTER TABLE 表名 ADD [无|unique|fulltext]index 索引名(字段名[(长度)][asc|desc]） 例如：创建上方的唯一性索引 create unique index introName_fulltext on information (name,introduction) 等同于 alter table information add unique introName_fulltext (name,introduction) 2.索引的删除drop index 索引名 on 表名 例如：删除上方的唯一性索引 drop index introName_fulltext on information 五、索引的失效可以查看一下别人的分享：http://blog.csdn.net/guoxingege/article/details/51034387 六、索引的拓展—存储引擎 1.常用索引实现的数据结构 MySQL普遍使用B+Tree实现索引结构 2.MyISAM索引实现 MyISAM引擎使用B+Tree为索引结构MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分 3.InnoDB索引实现 InnoDB也用B+Tree为索引结构。与MyISAM区别No.1 ：InnoDB的数据文件本身就是索引文件，MyISAM索引文件与数据文件是分开的 No.2 ：InnoDB的辅助索引data域存储相应记录主键的值，而MyISAM是地址 PS：这篇文章相当于是初级索引，如果想更加深入，可以学习索引的调优，数据结构，存储引擎等方面。 |
今日新闻 |
推荐新闻 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |