Mysql索引

user8288612657632
125 阅读6分钟

索引

  • MySQL官方对于索引的定义：索引是帮助MySQL高效获取数据的数据结构。
  • MySQL在存储数据之外，数据库系统中还维护着满足特定查找算法的数据结构，这些数据结构以某种引用(指向)表中的数据，这样我们就可以通过数据结构上实现的高级查找算法来快速找到我们想要的数据。而这种数据结构就是索引。
  • 简单理解为“排好序的可以快速查找数据的数据结构”。

image.png

索引设计原则

  • 针对于数据量较大，且查询比较频繁的表建立索引。
  • 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
  • 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引
  • 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
  • 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
  • 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。

索引使用场景

哪些情况需要创建索引：

  • 主键自动建立唯一索引
  • 频繁作为查询条件的字段应该创建索引(where 后面的语句)
  • 查询中与其它表关联的字段，外键关系建立索引
  • 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度

哪些情况不推荐建立索引:

  • 表记录太少
  • 经常增删改的表
  • Where条件里用不到的字段不建立索引

索引分类

  • 主键索引
    表中的列设定为主键后，数据库会自动建立主键索引。
    单独创建和删除主键索引语法：
-- 创建主键索引语法： 
alter table 表名 add primary key (字段); 
-- 删除主键索引语法：
alter table 表名 drop primary key;
  • 唯一索引 表中的列创建了唯一约束时，数据库会自动建立唯一索引。
    单独创建和删除唯一索引语法：
-- 创建唯一索引语法：
alter table 表名 add unique 索引名(字段)； 
-- 删除唯一索引语法：
drop index 索引名 on 表名;
  • 单值索引 即一个索引只包含单个列，一个表可以有多个单值索引。
    建表时可随表一起建立单值索引
    单独创建和删除单值索引语法：
-- 创建单值索引：
alter table 表名 add index 索引名(字段); 
-- 删除单值索引：
drop index 索引名 on 表名;
  • 复合索引

即一个索引包含多个列。
建表时可随表一起建立复合索引
单独创建和删除复合索引语法：

-- 创建复合索引：
alter table 表名 add index 索引名(字段,字段2); 
-- 删除复合索引：
drop index 索引名 on 表名;

索引失效

  • 最佳左前缀法则：如果索引了多列，要遵循最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列。
  • 不在索引列上做任何计算、函数操作，会导致索引失效而转向全表扫描。
  • 存储引擎不能使用索引中范围条件右边的列。
  • Mysql在使用不等于时无法使用索引会导致全表扫描。
  • is null可以使用索引，但是is not null无法使用索引。
  • like以通配符开头会使索引失效导致全表扫描。
  • 字符串不加单引号索引会失效。
  • 使用or连接时索引失效。

代码演示：

drop table if exists students; 
CREATE TABLE students ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT "主键id", sname VARCHAR (24) COMMENT '学生姓名', age INT COMMENT '年龄', score INT COMMENT '分数', time TIMESTAMP COMMENT '入学时间' ); 
INSERT INTO students(sname,age,score,time) VALUES('小明',22,100,now()); INSERT INTO students(sname,age,score,time) VALUES('小红',23,80,now()); 
INSERT INTO students(sname,age,score,time) VALUES('小绿',24,80,now()); 
INSERT INTO students(sname,age,score,time) VALUES('黑',23,70,now()); 
-- 添加复合索引 
alter table students add index idx_sname_age_score(sname,age,score);
-- 索引失效情况 explain select * from students where sname="小明" and age = 22 and score = 100; 
explain select * from students where sname="小明" and age = 22;
explain select * from students where sname="小明"; 
explain select * from students where sname="小明" and score = 80; 
-- 不在索引列上做任何计算、函数操作，会导致索引失效而转向全表扫描。 
explain select * from students where left(sname,2) = "小明";
-- 存储引擎不能使用索引中范围条件右边的列。 
explain select * from students where sname="小明" and age > 22 and score = 100; 
-- Mysql在使用不等于时无法使用索引会导致全表扫描。 
explain select * from students where sname!="小明"; 
-- is null可以使用索引，但是is not null无法使用索引。 
explain select * from students where sname is not null; 
-- like以通配符开头会使索引失效导致全表扫描。 
explain select * from students where sname like "明%"; 
-- 字符串不加单引号索引会失效。
explain select * from students where sname = 123;
-- 使用or连接时索引失效。 
explain select * from students where sname="小明" or age = 22;

复合索引练习

image.png

优化总结口诀

全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
Like百分写最右，覆盖索引不写星；
不等空值还有or，索引失效要少用；
VAR引号不可丢，SQL高级也不难！

索引应用时有什么弊端？

  1. 索引会占用额外的存储空间(InnoDB存储引擎中索引和数据存储一起)
  2. 对更新操作会带来一定的复杂度.(更新记录时需要更新索引)

MySQL中的索引是如何分类的？

  1. 从逻辑应用维度可分为主键,普通,联合,唯一,空间索引(MyISAM存储引擎)等
  2. 从物理存储维度可分为聚簇索引(数据和索引存储在一起)和非聚簇索引。
  3. 从数据存储结构维度可分为hash索引,B+Tree索引(InnoDB存储引擎默认)等。

如何查看MySQL表中的索引？

show index from 表名

如何为表中字段添加索引？

  1. 创建表的同时创建索引.(例如 create table tablename(....,index 索引名 (字段名)))
  2. 创建表后通过create语句创建索引(例如 create index 索引名 on 表名(字段名))
  3. 创建表后通过alter语句创建索引(例如 alter table add index 索引名(字段名))

准备工作

 create table if not exists student
 (
    id int auto_increment,
    first_name varchar(50) not null comment '名字',
    last_name varchar(50) not null comment '姓',
    email varchar(100) default '' comment '邮箱',
    phone varchar(20) not null comment '手机号',
    unique key (phone),-- 这里的key等效于index,没有指定索引名,默认系统自动起名.
    primary key (id)
 );
  • 创建普通索引
create index index_first_name on student(first_name);
alter table student add index index_last_name (last_name);
  • 创建唯一索引
create unique index index_first_name on student(first_name);
alter table student add unique index index_email (email)
  • 创建组合(联合)索引案例分享
create index index_first_last on student(first_name,last_name);
alter table student add index index_first_last (first_name,last_name);

如何删除表中字段上的索引？

drop index 索引名 on 表名;

例如：

drop index index_first_last on student;
avatar
文章
阅读
粉丝