explain 的使用方法和索引(主键索引/唯一索引/全文索引/普通索引/联合索引)

本文介绍了MySQL中不同类型的索引,包括主键索引、唯一索引、全文索引、普通索引和联合索引,并讨论了它们在模糊查询中的应用。同时,解释了为何更新、删除和插入操作会因索引而变慢。通过`show indexes`和`desc`命令展示了如何查看表的索引信息,以及`explain`关键字的使用来分析查询效率。此外,还提到了`handler_read_key`和`handler_read_rnd_next`两个指标用于评估索引的使用情况。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

索引总结

索引为什么提高了查询的速率,但是update delete insert 的速度会变慢

因为:这三种操作会破坏二叉树的,所以会造成速度变慢

什么样的索引使用什么样的方法进行查询

对于模糊查询

1 使用全文索引的目的就是为了弥补,其他的普通索引不能在前面不能加'%'的模糊查询。

CREATE TABLE articles (
       id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
       title VARCHAR(200),
       body TEXT,
       FULLTEXT (title,body) -- --如果只有这句话是不生效的只有针对myIsam 生效--
     )engine=myisam charset utf8; -- --全文索引针对MyISAM有用--

INSERT INTO articles (title,body) VALUES
     ('MySQL Tutorial','DBMS stands for DataBase ...')
     ('How To Use MySQL Well','After you went through a ...'),
     ('Optimizing MySQL','In this tutorial we will show ...'),
     ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
     ('MySQL vs. YourSQL','In the following database comparison ...'),
     ('MySQL Security','When configured properly, MySQL ...');

show indexes from articles; 

desc articles

如果mysql估计使用全表扫描要比使用索引快,则不使用索引。
如何查看索引使用的情况:
show status like ‘Handler_read%’;

handler_read_key:这个值越高越好,越高表示使用索引查询到的次数。
handler_read_rnd_next:这个值越高,说明查询低效。 

错误用法:
select * from articles where body like ‘%mysql%’; 【不会使用到全文索引】
证明没有用到全文索引:
explain  select * from articles where body like '%mysql%'

这个possible_key 是空的,索引没有用到全文索引

正确的用法是:

select * from articles where match(title,body) against(‘database’); 【可以】

database 表示的是含有database 的字段

可以看出来真的是可以找到database??????不懂

  • Select match(title,body) against('database') from articles;显示的是在每一行查出来database

字符串的概率

 

只会是针对不太常用的词才会创建索引

Eg 常用的词不会创建索引,就像a字符串,查找的匹配度为0 ,就是没有创建索引,这种词叫做停止词。

Selectmatch(title,body) against('a') from articles;

 

利用sphinx (coreseek) 技术处理中文

对于使用like的查询,查询如果是  ‘%aaa’ 不会使用到索引
    ‘aaa%’ 会使用到索引。
比如: explain select * from dept where dname like '%aaa'
不能使用索引,即,在like查询时,关键的 ‘关键字’ , 最前面,不能使用 % 或者 _这样的字符.,

如果一定要前面有变化的值,则考虑使用 全文索引->sphinx.

explain select * from dept where dname like 'aaa%'
explain select * from dept where dname like 'aaa'
explain select * from dept where dname like 'a_aa'

所以要用全文索引要替代模糊查询,由于模糊查询时的就是不能把%放到前面去,

如果一定要前面有变化时一定要全文索引

如果条件中有or,即使其中有条件带索引也不会使用。换言之,就是要求使用的所有字段,

都必须建立索引, 我们建议大家尽量避免使用or 关键字
select * from dept where dname=’xxx’ or loc=’xx’ or deptno=45
1)select * from dept where dname=’xxx’ or loc=’xx’

实际上没有用到索引,由于loc 是联合索引右面,联合索引不生效。
2)select * from dept where dname=’xxx’ or loc=’xx’ or deptno=45

实际上没有用到索引,由于loc 是联合索引右面,联合索引不生效。

explain select * from dept where dname='xxx' or  deptno=45

这个时候是两个索引都生效了,只要是有一个不带索引都是不行的

如果列类型是字符串,那一定要在条件中将数据使用引号引用起来。否则不使用索引。(添加时,字符串必须’’), 也就是,如果列是字符串类型,就一定要用 ‘’ 把他包括起来,即使查询的是字符串也要用单引号包括起来
explain select * from dept where dname='45'

 

 

 

适合使用索引的情况

经常使用但是不经常更新的适合建立索引

1 我一个表 students 表,有3个字段 ,id,name,age 我要查询 通过 name 和age

一般来说mysql会选择其中一个索引,name的可能性比较大,因为mysq会统计每个索引上的重复度,选用低重复度的字段。
另外一个age的索引就不会用到,但还有维护索引的开销,所以age的索引不需要创建。回过来看,有必要使用联合索引吗?我的看法是没有必要,因为学校里可能会有重名的人,但比较少。用name就可以比较精准的找到记录,即使有重复的也比较少。

 

一般都where、order by 或者 group by 后面的字段

 

 

多胞胎不适合建立索引

唯一性太差的字段不太适合建索引,唯一性太差的字段(适合建在不怎么重复的列上),

比如说就只有两个性别,建立二叉树的时候全是平级的,找的时候就不适合建立索引,

变化太多的例如字段的状态在一直变化

 

主键索引(单列索引)

主键索引在创建表的时候创建

create table aaa
(id int unsigned(无符号与有符号就是范围上的区别) primary key auto_increment ,
name varchar(32) not null defaul ‘’);

主键索引添加修改删除

alter table bbb add primary key (id);

 

DROP INDEX index_name ON tbl_name;

alter table table_name drop index index_name;
删除主键(索引)比较特别: alter table 表名 drop primary key; 【如果这个主键是自增的,先取消自增长.】主键只有一个,所以不用指出是哪一个列名

 

删除自增属性:

ALTER TABLE `articles` CHANGE id id INT(10) UNSIGNED NOT NULL ;

添加自增属性:

ALTER TABLE `articles` CHANGE id id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;

alter table articles add primary key(id)

 

 

普通索引(单列索引)

普通索引要先创建表在创建索引

create table ccc(id int unsigned,name varchar(32))

 

添加索引

create index 索引名 on 表 (列1,列名2);

 

 

给emp 表加了索引以后

查询时间大幅度缩短

二叉树的算法是如何写出来的

比如它要检索11 个数字
1234567891011  
写二叉树的写法,11 个树最中间的数是6写在根节点
小于6的一共有12345 这5个数的中间是3写在6的左侧,大于6的一共有7891011
这5个数的中间是9 写在6的右侧,比3小的写在3的左侧,比3小的只有1,2两个数字,向上取整就是向大的数取整,所以2在3的左侧,1比2小放在2的左侧,比3大的比6小的还有两个数字,5,4 向上取整是5,放在3的右侧,比5小的放在5的左侧,比9小比6大的还有两个,7,8 向上取整是8,放在9 的左侧,7比8小放在8的左侧,比9大的还有两个10,11 向上取整11 放在9的右侧,10比11小放在11的左侧
二叉树的比较的方法是
例如select * from emp where emp =4;
4直接和6比较,比6小的话比6大的所有的数就不用比较了,3和4比较比4大,那就和5比较,比5小,最后就找到了4,所以一共才比较了4次就找到了,二叉树的效率是2^n n是比较次数,2^n 就是它可以扫描的数的范围,所以二叉树的查询方式是非常快速的。

 

唯一索引(单列索引)

可以对一个表定义多个 UNIQUE 约束,但只能定义一个 PRIMARY KEY 约束。
而且,UNIQUE 约束允许 NULL 值,这一点与 PRIMARY KEY 约束不同。不过,当与参与 UNIQUE 约束的任何值一起使用时,每列只允许一个空值。
FOREIGN KEY 约束可以引用 UNIQUE 约束。

①当表的某列被指定为unique约束时,这列就是一个唯一索引

createtable ddd(id int primary key auto_increment , name varchar(32) unique);

这时, name 列就是一个唯一索引.

unique字段可以为NULL,并可以有多个NULL, 但是如果是具体内容,则不能重复.

Insertddd values(1,null);

Insertddd values(1,null);

是允许的,但是空字符串不能是认为是

主键字段,不能为NULL,也不能重复.

 

 

②在创建表后,再去创建唯一索引

create table eee(id int primary key auto_increment, name varchar(32));

create unique index 索引名  on 表名 (列表..);

 

 

添加唯一索引

create table ddd(id int primary key auto_increment , name varchar(32) unique);

这时, name 列就是一个唯一索引.

 

联合索引

这种情况下适合建立联合索引

 

 什么情况下使用联合索引比较好呢? 举一个例子,大学选认课老师,需要创建一个关系对应表,有2个字段,student_id 和 teacher_id,想要查询某个老师和某个学生是否存在师生关系。
一个学生会选几十个老师,一个老师会带几百个学生
如果只为student_id建立索引的情况下,经过索引会选出几十条记录,然后在内存中where一下,去除其余的老师。
相反如果只为teacher_id建立索引,经过索引会选出几百条记录,然后在内存中where一下,去除其余的学生。
两种情况都不是最优的,这个时候使用联合索引最合适,通过索引直接找到对应记录。
创建联合索引的语法:create index 索引名 on 表名(字段名1,字段名2)
给name和age创建联合索引:create index students _name_age on students (name,age)

 

alter table dept add index my_ind (dname,loc); //  dname 左边的列,loc就是右边的列,my_ind 是索引的名字,创建的是联合索引

询时使用联合索引的一个字段,如果这个字段在联合索引中所有字段的第一个,那就会用到索引,否则就无法使用到索引

如果是多个列创建索引的情况下
1 只要查询条件使用了最右边的列,联合索引一般就不会被使用。
explain select * from dept where loc ='aaa'

2查询条件用到了最左边的列,联合索引一般就会被使用。

explain select * from dept where dname = 'aaa'

 

如果条件中有or,即使其中有条件带索引也不会使用。换言之,就是要求使用的所有字段,都必须建立索引, 我们建议大家尽量避免使用or 关键字
select * from dept where dname=’xxx’ or loc=’xx’ or deptno=45
1)select * from dept where dname=’xxx’ or loc=’xx’

实际上没有用到索引,由于loc 是联合索引右面,联合索引不生效。
2)select * from dept where dname=’xxx’ or loc=’xx’ or deptno=45

实际上没有用到索引,由于loc 是联合索引右面,联合索引不生效。

explain select * from dept where dname='xxx' or  deptno=45

这个时候是两个索引都生效了,只要是有一个不带索引都是不行的

查看表的文件结构




Eg:emp.frm 表示的emp 的表的结构,emp.MYD表示的是表的数据本身,emp.MYI 表示的是索引的大小,如果这个时候给这个表添加索引的话,这个时候文件的大小就变大了

 

explain的使用方法

Explain 的查询结果

table

显示这一行的数据是关于哪张表的

type

显示连接使用何种类型,从最好到最差的连接类型为const,eq_reg,ref,range,index,All(全文搜索)

Possible_keys

显示的是可能应用在这张表中的索引,如果为空,没有可能的索引

key

实际使用的索引,如果为null,则没有使用索引

Key_len

使用的索引的长度,在不损失精确性的情况下,长度越短越好

Ref

显示索引的那一列被使用了,如果可能的话,是一个常数

rows

Mysql 认为必须检查的用来返回请求数据的行数

extra

Using filesort 看到这个的时候,查询就需要优化,当query 中包含order by 操作,而且无法利用索引完成排序

Using temporary 看到这个的时候,需要优化了,这里,mysql 需要创建一个临时表来存储结果,这通常发生在对不同的列集进行order by 上,而不是group by

 

1 优化group by 语句
注意:在使用group by 语句的时候没有使用索引

2 由上面可以知道,默认情况,MySQL对所有的group by col1,col2进行排序。这与在查询中指定order by col1, col2类似。如果查询中包括group by但用户想要避免排序结果的消耗,则可以使用order by null禁止排序
在group by 后面增加 order by null 就可以防止排序.


3 优化子查询(使用连接代替子查询)

有些情况下,可以使用连接来替代子查询。因为使用join,MySQL不需要在内存中创建临时表。
select * from dept, emp where dept.deptno=emp.deptno; [简单处理方式]
select * from dept left join emp on dept.deptno=emp.deptno;  [左外连接,更ok!] 

 

Using where 不用读取表中的所有信息,仅通过索引就可以获取所需的数据


联合索引:

 

先创建存储过程,向dept 这张表中插入10条记录

create procedure insert_dept(in start int(10),in max_num int(10))
begin
declare i int default 0;
 set autocommit = 0;  
 repeat
 set i = i + 1;
 insert into dept values ((start+i) ,rand_string(10),rand_string(8));
  until i = max_num
 end repeat;
   commit;
 end
call insert_dept(100,10);

把dept表中,我增加几个部门:

 

 

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值