电脑做网站服务器改端口,1元涨1000粉丝网站,广州番禺区邮政编码,陕西省住房和城乡建设网站目录
一、什么是索引
二、索引的原理
三、优缺点
四、分类
1、聚簇索引--顺序IO
2、非聚簇索引--随机IO
五、索引的设计原则
六、创建索引
1、创建表时创建索引
2、在已经存在的表上创建索引
3、使用ALTER TABLE语句来创建索引 1)普通索引 2#xff09;唯一性索引 …目录
一、什么是索引
二、索引的原理
三、优缺点
四、分类
1、聚簇索引--顺序IO
2、非聚簇索引--随机IO
五、索引的设计原则
六、创建索引
1、创建表时创建索引
2、在已经存在的表上创建索引
3、使用ALTER TABLE语句来创建索引 1)普通索引 2唯一性索引 3全文索引FULLTEXT——只能创建在char,varchar或text类型的字段上。 4单列索引 5多列索引 6空间索引
七、查询索引
八、删除索引
九、MySQL使用索引的场景
十、explain 索引是一种特殊的数据库结构可以用来快速查询数据库表中的特定记录。索引是提高数据库性能的重要方式。MySQL中所有的数据类型都可以被索引。
MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。
索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)它们包含着对数据表里所有记录的引用指针。更通俗的说数据库索引好比是一本书前面的目录能加快数据库的查询速度。注意一般数据库默认都会为主键生成索引。
# 当我们为一张表建立主键建立索引
# 当我们为某一个设置为唯一约束的时候对应的也会建立索引 一、什么是索引 模式(schema)中的一个数据库对象 在数据库中用来加速对表的查询 通过使用快速路径访问方法快速定位数据,减少了磁盘的I/O 与表独立存放但不能独立存在必须属于某个表 由数据库自动维护表被删除时该表上的索引自动被删除。 索引的作用类似于书的目录几乎没有一本书没有目录因此几乎没有一张表没有索引。
二、索引的原理
就是把无序的数据变成有序的查询 把创建的索引的列的内容进行排序 对排序结果生成倒排表 在倒排表内容上拼上数据地址链 在查询的时候先拿到倒排表内容再取出数据地址链从而拿到具体数据
三、优缺点
1、优 可以提高检索数据的速度这是创建索引的最主要的原因对于有依赖关系的子表和父表之间的联合查询时可以提高查询速度使用分组和排序子句进行数据查询时同样可以显著节省查询中分组和排序的时间。
2、缺 创建和维护索引需要耗费时间耗费时间的数量随着数据量的增加而增加索引需要占用物理空间每一个索引要占一定的物理空间增加、删除和修改数据时要动态的维护索引造成数据的维护速度降低了。
四、分类
按「数据结构」分类Btree索引、Hash索引、Full-text索引。
按「物理存储」分类聚簇索引主键索引、二级索引辅助索引。
按「字段特性」分类主键索引、唯一索引、普通索引、前缀索引。
按「字段个数」分类单列索引、联合索引。 1、聚簇索引--顺序IO 按照数据存放的物理位置为顺序的 索引中键值的逻辑顺序与数据行的物理顺序相同一个表中只能有一个聚簇索引 能提高多行检索的速度 对于非聚簇索引来说每次通过索引检索到所需行号后还需要通过叶子上的磁盘地址去磁盘内取数据回行消耗时间。为了优化这部分回行取数据时间InnoDB 引擎采用了聚簇索引。 聚簇索引即将数据存入索引叶子页面上。对于 InnoDB 引擎来说叶子页面不再存该行对应的地址而是直接存储数据这样便避免了回行操作所带来的时间消耗。 2、非聚簇索引--随机IO 不按照数据存放的物理位置为顺序的 索引的逻辑顺序与磁盘上的的物理存储顺序不同一个表中可以拥有多个非聚簇索引 对于单行的检索很快 索引节点的叶子页面就好比一片叶子。叶子头便是索引键值。对于MYISAM引擎如果创建 id 和 name 为索引。对于下面查询select * from user where id 1。会利用索引先在索引树中快速检索到 id但是要想取到id对应行数据必须找到改行数据在硬盘中的存储位置因此MYISAM引擎的索引叶子页面上不仅存储了主键id 还存储着 数据存储的地址信息。 五、索引的设计原则
为了使索引的使用效率更高在创建索引的时候必须考虑在哪些字段上创建索引和创建什么类型的索引。
1. 选择惟一性索引 2. 为经常需要排序、分组和联合操作的字段建立索引 3. 为常作为查询条件的字段建立索引 4. 限制索引的数目 5. 尽量使用数据量少的索引 6. 尽量使用前缀来索引 7. 删除不再使用或者很少使用的索引
六、创建索引
创建索引是指在某个表的一列或多列上建立一个索引以便提高对表的访问速度。
1、创建表时创建索引
创建表的时候可以直接创建索引这种方式最简单、方便。其基本形式如下
CREATE TABLE 表名 ( 属性名 数据类型 [完整性约束条件],
属性名 数据类型 [完整性约束条件],
…
属性名 数据类型
[UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY
[别名](属性名1 [(长度)] [ASC | DESC])
);
2、在已经存在的表上创建索引 可通过命令查看语法help create index)
CREATE [ UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 ON 表名 (属性名 [ (长度) ] [ ASC | DESC] );
3、使用ALTER TABLE语句来创建索引
ALTER TABLE 表名 ADD [ UNIQUE | FULLTEXT | SPATIAL ] INDEX
索引名属性名 [ (长度) ] [ ASC | DESC]; 1)普通索引
# 直接创建索引
mysql create index index_name on class(class_id);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0# 创建表的时候同时创建索引
mysql create table tb_index(- id int,- name varchar(20),- sex boolean,- index(id)- );
Query OK, 0 rows affected (0.03 sec)#使用ALTER TABLE语句来创建索引
mysql alter table emp add index index_emp(empno);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0 2唯一性索引
mysql create unique index id_index on course(id);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql create table index1(- id int unique,- name varchar(20),- unique index index1_id(id asc)- );
Query OK, 0 rows affected, 1 warning (0.03 sec)mysql alter table course add unique index_name(id);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0 3全文索引FULLTEXT——只能创建在char,varchar或text类型的字段上。
mysql create fulltext index index_name on course(name);
Query OK, 0 rows affected, 1 warning (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 1mysql create table index2(- id int,- info varchar(20),- fulltext index index2_info(info)- );
Query OK, 0 rows affected (0.10 sec)mysql alter table course add fulltext index index_name(name);
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0 4单列索引
mysql create index index1_name on course(id);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql create table index3(- id int,- subject varchar(20),- index index3_st(subject(10))- );
Query OK, 0 rows affected (0.02 sec)mysql alter table course add index index_name(name);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0 5多列索引 使用多列索引时一定要特别注意只有使用了索引中的第一个字段时才会触发索引。如果没有使用索引中的第一个字段那么这个多列索引就不会起作用。 也就是说多个单列索引与单个多列索引的查询效果不同因为执行查询时MySQL只能使用一个索引会从多个索引中选择一个限制最为严格的索引。
mysql create table index4(- id int,- name varchar(20),- sex char(4),- index index4_ns(name,sex)- );
Query OK, 0 rows affected (0.02 sec) 6空间索引 建空间索引时表的存储引擎必须是myisam类型而且索引字段必须有非空约束。空间数据类型包括geometry,point,linestring和polygon类型等。平时很少用到。
mysql create table index5(- id int,- space geometry not null,- spatial index index5_sp(space)- )enginemyisam;
Query OK, 0 rows affected, 1 warning (0.01 sec) 七、查询索引
查询索引
show create table 表名 \G查询某张表中索引情况
show indeix from 表名;使用计划查询SQL使用索引情况
Explain select * from 表名 where id1 \G
八、删除索引 删除索引是指将表中已经存在的索引删除掉。一些不再使用的索引会降低表的更新速度影响数据库的性能。对于这样的索引应该将其删除。 对应已经存在的索引可以通过DROP语句来删除索引。
DROP INDEX 索引名 ON 表名 ;
mysql drop index index_name on class;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0 九、MySQL使用索引的场景 1) 快速查找符合where条件的记录 2) 快速确定候选集。若where条件使用了多个索引字段则MySQL会优先使用能使候选记录集规模最小的那个索引以便尽快淘汰不符合条件的记录。 3) 如果表中存在几个字段构成的联合索引则查找记录时这个联合索引的最左前缀匹配字段也会被自动作为索引来加速查找。 例如若为某表创建了3个字段(c1, c2, c3)构成的联合索引则(c1), (c1, c2), (c1, c2, c3)均 会作为索引(c2, c3)就不会被作为索引而(c1, c3)其实只利用到c1索引。 4) 多表做join操作时会使用索引如果参与join的字段在这些表中均建立了索引的话。 5)若某字段已建立索引求该字段的min()或max()时MySQL会使用索引 6)对建立了索引的字段做sort或group操作时MySQL会使用索引 十、explain
explain select * from table where id1;
EXPLAIN分析结果的含义: table这是表的名字。 type连接操作的类型ALL、index、range、 ref、eq_ref、const、system、NULL从左到右性能从差到好 possible_keys可能可以利用的索引的名字 Key它显示了MySQL实际使用的索引的名字。如果它为空或NULL则MySQL不使用索引。 key_len索引中被使用部分的长度以字节计。 ref它显示的是列的名字或单词“const”MySQL将根据这些列来选择行 rowsMySQL所认为的它在找到正确的结果之前必须扫描的记录数。显然这里最理想的数字就是1 Extra这里可能出现许多不同的选项其中大多数将对查询产生负面影响 索引失效的情况浅谈mysql数据库索引 - 简书
