MySQL 运维 - 数据库索引
- 一、数据库索引
- 二、索引的作用
- 三、索引的副作用
- 四、创建索引的原则依据
- 五、索引的分类
- 六、索引的创建方法
- ① 普通索引
- ② 唯一索引
- ③ 主键索引
- ④ 组合索引
- ⑤ 全文索引
- 七、删除索引
- ① 直接删除索引
- ② 修改表方式删除索引
- ③ 删除主键索引
- 八、查看索引
- ① 各个字段的含义
- 九、总结
- ① 索引的优缺点
- ② 随机IO和顺序IO
一、数据库索引
索引是一种特殊的文件，包含着对数据表中所有记录的引用指针，索引就好比是一本书的目录，能加快数据库的查询速度，有了相应的索引后，数据库会直接在索引中查找符合条件的选项
数据库索引就是为了提高表的搜索效率而对某些字段中的值建立的目录
二、索引的作用
- 在数据量特别大的时候减少查询时间极大提升效率
- 降低数据库IO成本 降低数据库的排序成本 降低服务器的成本
- 关键字段的值有规律可循会进行排序
- 多表联合查询时，加快表与表的连接
- 在使用分组和排序时，可以大大减少排序时间
三、索引的副作用
- 占用额外的磁盘空间
- 更新表数据时耗时会更多
四、创建索引的原则依据
- 有索引时先看索引，没有索引时才会去检索全文，如果检索使用不当会增加负担
- 表的主键、外键必须有索引，因为主键具有唯一性
- 记录数超过
300行的表应该有索引，因为每次查询都会把表遍历一边，就会造成负担 - 经常和其他表连接的表
- 唯一性太差的字段不适合建立索引
- 更新太频繁的字段不适合建立索引
- 经常出现在
where子句中的字段应该建立索引 - 索引应该建在选择性高的字段上[唯一性不是太差的字段]
- 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建立索引
五、索引的分类
普通索引、唯一索引、主键索引、组合索引、全文索引
六、索引的创建方法
1.直接创建索引(主键索引无法使用此方式)[create index]
2.修改表结构方式添加索引[alter update]
3.创建表结构时创建索引[create table]
① 普通索引
没有唯一性类的限制
create index 索引名 on 表名(列名[(length)]);►举例：
create table user(id int(10),name varchar(10),message text);
show keys from user\G;
创建表时设置索引
create table 表名 (列名 类型,index 索引名 列名(length))length可选项，如忽略则整个列的值作为索引
指定列的前length个字符来创建索引，可以有利于减小索引文件的大小
② 唯一索引
索引列每个值都唯一
直接创建唯一索引
create unique index 索引名 on 表名 (列名);►举例：
create unique index index_id on user(id);
show create table user\G;
修改表方式创建索引
alter table 表名 add unique 表名 (列名);③ 主键索引
必须指定为主键
添加主键将自动创建主键索引
create table 表名 primary key (列名);►举例：
create table user(id int(10),name varchar(10),message text,primary key (id));
show index from user\G;
④ 组合索引
需要满足最左原则
使用select语句查询时使用where条件使用字段顺序必须从左到右
create table 表名 (列名 数据类型,列名2 数据类型, index xx_xx (列名1,列名2));►举例：
create table user(id int(10),name varchar(10),message text,index index_list (id,name));
show create table user;
⑤ 全文索引
适合在进行模糊查询时使用
mysql 5.6 版本以前fulltext索引只能在myisam引擎上使用
mysql 5.6 版本以后innodb 引擎也支持fulltext 每张表只允许右一个全文索引
直接创建索引
create fulltext index 索引名 on 表名(列名);►举例：
create fulltext index index_text on user (info);
全文检索查询
select * from 表名 where match(列名) against(关键字);►举例：
七、删除索引
① 直接删除索引
drop index 索引名 on 表名;② 修改表方式删除索引
alter table 表名 drop index 索引名;►举例：
create fulltext index index_text on user (message);③ 删除主键索引
alter table 表名 drop primary key;八、查看索引
show index from 表名;
show keys from 表名;
show index from member\G;① 各个字段的含义
字段 | 含义 |
Table | 表的名称 |
Non_unique | 如果索引不能包括重复词，则为 0；如果可以，则为 1 |
Key_name | 索引的名称 |
Seq_in_index | 索引中的列序号，从 1 开始 |
Column_name | 列名称 |
Collation | 列以什么方式存储在索引中。在 MySQL 中，有值‘A’（升序）或 NULL（无分类） |
Cardinality | 索引中唯一值数目的估计值 |
Sub_part | 如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为 NULL |
Packed | 指示关键字如何被压缩。如果没有被压缩，则为 NULL |
Null | 如果列含有 NULL，则含有 YES。如果没有，则该列含有 NO |
Index_type | 用过的索引方法（BTREE, FULLTEXT, HASH, RTREE） |
九、总结
① 索引的优缺点
优点 | 缺点 |
减少创建临时表和排序对服务器的负担 | 索引需要占用物理空间 |
减少需要扫描的数据量，大大加快检索速度 | 后期维护随着数据量增大会逐渐繁重 |
将随机IO变成顺序IO提高性能 | 对于非常小的表，大部分情况下全表扫描会更高效 |
② 随机IO和顺序IO
- 顺序IO比随机IO快
- 顺序IO一般只扫描一次数据
- 随机IO查询特定的行
- 顺序IO查询数据块上所有的行
