索引入门介绍

2.1 索引概述

2.1.1 介绍

索引（ index ）是帮助 MySQL 高效获取数据的数据结构 ( 有序 ) 。在数据之外，数据库系统还维护着满足

特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构

上实现高级查找算法，这种数据结构就是索引

 

在无索引情况下，就需要从第一行开始扫描，一直扫描到最后一行，我们称之为 全表扫描，性能很低。

如果我们针对于这张表建立了索引，假设索引结构就是二叉树，那么也就意味着，会对 age这个字段建立一个二叉树的索引结构。

此时我们在进行查询时，只需要扫描三次就可以找到数据了，极大的提高的查询的效率。

 

2.3 特点

创建表

create table tb_user( id int primary key auto_increment comment '主键', name varchar(50) not null comment '用户名', phone varchar(11) not null comment '手机号', email varchar(100) comment '邮箱', profession varchar(11) comment '专业', age tinyint unsigned comment '年龄', gender char(1) comment '性别 , 1: 男, 2: 女', status char(1) comment '状态', createtime datetime comment '创建时间' ) comment '系统用户表';

 插入数据

INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('吕布', '17799990000', 'lvbu666@163.com', '软件工程', 23, '1', '6', '2001-02-02 00:00:00'); INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('曹操', '17799990001', 'caocao666@qq.com', '通讯工程', 33, '1', '0', '2001-03-05 00:00:00'); INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('赵云', '17799990002', '17799990@139.com', '英语', 34, '1', '2', '2002-03-02 00:00:00'); INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('孙悟空', '17799990003', '17799990@sina.com', '工程造价', 54, '1', '0', '2001-07-02 00:00:00'); INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('花木兰', '17799990004', '19980729@sina.com', '软件工程', 23, '2', '1', '2001-04-22 00:00:00'); INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('大乔', '17799990005', 'daqiao666@sina.com', '舞蹈', 22, '2', '0', '2001-02-07 00:00:00'); INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('露娜', '17799990006', 'luna_love@sina.com', '应用数学', 24, '2', '0', '2001-02-08 00:00:00'); 12345678910111213141516171819
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('程咬金', '17799990007', 'chengyaojin@163.com', '化工', 38, '1', '5', '2001-05-23 00:00:00'); INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('项羽', '17799990008', 'xiaoyu666@qq.com', '金属材料', 43, '1', '0', '2001-09-18 00:00:00'); INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('白起', '17799990009', 'baiqi666@sina.com', '机械工程及其自动 化', 27, '1', '2', '2001-08-16 00:00:00'); INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('韩信', '17799990010', 'hanxin520@163.com', '无机非金属材料工 程', 27, '1', '0', '2001-06-12 00:00:00'); INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('荆轲', '17799990011', 'jingke123@163.com', '会计', 29, '1', '0', '2001-05-11 00:00:00'); INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('兰陵王', '17799990012', 'lanlinwang666@126.com', '工程造价', 44, '1', '1', '2001-04-09 00:00:00'); INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('狂铁', '17799990013', 'kuangtie@sina.com', '应用数学', 43, '1', '2', '2001-04-10 00:00:00'); INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('貂蝉', '17799990014', '84958948374@qq.com', '软件工程', 40, '2', '3', '2001-02-12 00:00:00'); INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('妲己', '17799990015', '2783238293@qq.com', '软件工程', 31, '2', '0', '2001-01-30 00:00:00'); INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('芈月', '17799990016', 'xiaomin2001@sina.com', '工业经济', 35, '2', '0', '2000-05-03 00:00:00'); INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('嬴政', '17799990017', '8839434342@qq.com', '化工', 38, '1', '1', '2001-08-08 00:00:00'); INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('狄仁杰', '17799990018', 'jujiamlm8166@163.com', '国际贸易', 30, '1', '0', '2007-03-12 00:00:00'); 202122232425262728293031
 
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('安琪拉', '17799990019', 'jdodm1h@126.com', '城市规划', 51, '2', '0', '2001-08-15 00:00:00'); INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('典韦', '17799990020', 'ycaunanjian@163.com', '城市规划', 52, '1', '2', '2000-04-12 00:00:00'); INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('廉颇', '17799990021', 'lianpo321@126.com', '土木工程', 19, '1', '3', '2002-07-18 00:00:00'); INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('后羿', '17799990022', 'altycj2000@139.com', '城市园林', 20, '1', '0', '2002-03-10 00:00:00'); INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('姜子牙', '17799990023', '37483844@qq.com', '工程造价', 29, '1', '4', '2003-05-26 00:00:00');

A. name 字段为姓名字段，该字段的值可能会重复，为该字段创建索引。

 

CREATE INDEX idx_user_name ON tb_user(name);

 

B. phone 手机号字段的值，是非空，且唯一的，为该字段创建唯一索引。

 

CREATE UNIQUE INDEX idx_user_phone ON tb_user(phone);

不要在索引列上进行运算操作（函数），索引将失效

EXPLAIN SELECT * from tb_user where SUBSTRING(phone,10,2)='15'

字符串字段不加引号，索引将失效

EXPLAIN SELECT * from tb_user where profession='软件工程' and age =31 and STATUS=0

如果只是尾部模糊匹配，索引不会失效，如果是头部模糊匹配，索引失效

EXPLAIN SELECT * from tb_user where profession like '%软件%'
EXPLAIN SELECT * from tb_user where profession like '%软件'

用 or 分割开的条件， 如果 or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

explain select * from tb_user where id = 10 or age = 23;
 explain select * from tb_user where phone = '17799990017' or age = 23;

由于 age 没有索引，所以即使 id 、 phone 有索引，索引也会失效。所以需要针对于 age 也要建立索引。

然后，我们可以对 age 字段建立索引。

 CREATE index idx_id_user_age on tb_user(age)

如果 MySQL 评估使用索引比全表更慢，则不使用索引。

如果查询一个字段，涉及到两个索引，mysql会根据算法选择，最终 用到联合索引

#创建单例索引，并查询数据
 CREATE index idx_professionon on tb_user(profession);
EXPLAIN SELECT * from tb_user where profession ='软件工程';

索引建议使用，mysql可能不使用

use index; 

explain select * from tb_user use index(idx_user_pro) where profession = '软件工 程';

忽略索引

IGNORE INDEX

EXPLAIN SELECT * from tb_user IGNORE INDEX(idx_user_pro_age_sta) where profession ='软件工程'

 强制 索引

force index

EXPLAIN SELECT * from tb_user force INDEX(idx_professionon) where profession ='软件工程'

 覆盖索引

删除索引

drop index idx_id_user_age on tb_user;
 drop index idx_email on tb_user;
 drop index idx_professionon on tb_user;

当某个字段只有前几列有用时（如邮箱），可以截取前缀作为索引，节约索引空间，提高效率

CREATE INDEX idx_emaill_5 on tb_user(email(5))