mysql索引

一、索引的根本概述

1、什么是索引

索引是创建表上的，是对数据库表中一列或多列的值举行分列的一种布局，使用索引可快速访问数据库表中的特定信息。
举个例子吧：如果把数据库看成一本辞书，索引就相当于一本辞书的目次，我们可以通过索引快速的找到辞书中的词。对于数据库来说，可以通过索引快速查找表中的数据。
2、索引创建的位置及布局存放位置

1）索引是创建在表上的
2）索引一般以文件形式存在磁盘中（也可以存于内存中），存储的索引的原理大抵概括为以空间换时间，数据库在未添加索引的时候举行查询默认的是举行全量搜索，也就是举行全局扫描，有多少条数据就要举行多少次查询，然后找到相匹配的数据就把他放到效果会集，直到全表扫描完。而创建索引之后，会将创建索引的KEY值放在一个n叉树上（B+树）。因为B树的特点就是适合在磁盘等直接存储设备上组织动态查找表，每次以索引举行条件查询时，会去树上根据key值直接举行搜索。
此中:（1）Innodb引擎
.frm 文件存储表布局
.ibd 文件存储数据和索引
（2）myIsam引擎：
.myi 文件存储索引
.myd文件存储数据
.frm 文件存储表布局
3、索引的优点

（1）创建索引可以有效地缩短检索的时间
（2）创建索引可以加快表与表之间的毗连
（3）为用来排序大概分组的字段加上索引可以加快索引的分组及排序
（4）创建索引的列可以包管行的唯一性
总之创建索引的目标：加快对表中的纪录的查询和排序
4、索引的缺点

在此处想到一句话：事物都有两面性（相对论），索引也是不破例的
（1）创建索引和维护索引需要淹灭时间，而且随着数据量的增加而增加
（2）索引需要占用物理的空间，而且一个索引都占用一定的空间（数据表占用的是数据库的空间）
（3）会低落表增删改的效率，因为每次举行增删改索引要举行动态维护，造成数据的维护速度低落了，这就导致时间变长
以后处我们也可以看出来，不是啥时候都可以选择索引，要充实思量到上述的优缺点。
当数据库中的数据量大时，查询的响应速度不能满足自己的需求，此时我们可以思量使用公道的索引来提升查询速度。
5、索引的种类

（1）平常索引：在创建平常索引时，不附加任何限制条件。这类索引可以创建在任何数据范例中，其值是否唯一和非空由字段自己的完整性约束条件决定。创建索引以后，查询时可以通过索引举行查询。
（2）唯一性索引：使用UNIQUE参数可以设置索引为唯一性索引。**在创建唯一性索引时，限制该索引的值必须是唯一的。**通过唯一性索引。可以更快速简直定某条纪录。主键就是一种特殊唯一性索引。
（3）全文索引：使用FULLTEXT参数可以设置索引为全文索引。全文索引只能创建在char,varchar或text范例的字段上。查询数据量较大的字符串范例的字段时，使用全文索引可以提高查询速度。MySQL数据库中3.23.23版开始支持全文索引，但只有MyISAM存储引擎支持全文索引。在默认情况下，全文索引的搜索执行方式不区分巨细写。但索引的列使用二进制排序后，可以执行区分巨细写的全文索引。
（4）单列索引：在表中的单个字段上创建索引。单列索引只根据该字段举行索引。单列索引可以是平常索引，也可以是唯一性索引，还可以是全文索引。只要包管该索引只对应一个字段即可。
（5）多列索引：多列索引是在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段举行查询。但是，只有查询条件中使用了这些字段中第一个字段时，索引才会被使用。
（6）空间索引：使用SPATIAL参数可以设置索引为空间索引。空间索引只能创建在空间数据范例上，这样可以提高系统获取空间数据的效率。MySQL中的空间数据范例包罗GEOMETRY和POINT、LINESTRING和POLYGON等。现在只有MyISAM存储引擎支持空间检索，而且索引的字段不能为空值
（7）主键索引：索引创建在主键上。 固然反之则是辅助索引
主键索引：
InnoDB ：叶子节点存储主键和这个主键所对应的所有数据。
MyISAM ：叶子节点存储的是主键（创建索引的属性）和数据的所在。
辅助索引：
InnoDB ：叶子节点存储创建索引的属性和属性所对应的主键值。
MyISAM ：叶子节点存储创建索引的属性和属性对应的数据的所在。
6、底层数据布局

B+树索引和哈希索引
此中哈希索引：无法做范围查询但是查询单个字段速度快
7、索引的设计原则

（1）为常常需要分组、排序和团结使用的字段创建索引
（2）限制索引的数目，
（3）只管使用数量少的索引
（4）只管使用前缀来索引
（5）选择唯一性索引
（6）删除不再使用大概很少使用的索引
（7）为常作为查询条件的字段创建索引
注意：选择索引的最终目标是提高查询的速度。
二、索引的创建和修改删除等下令

1、索引的创建

创建表时直接创建

（1）创建平常索引

（2）创建唯一性索引

（3）创建全文索引

（4）创建单例索引

（5）创建多例索引

（6）创建空间索引

2、在已存在的表上创建索引

（1）创建平常索引

（2）创建唯一性索引

（3）创建全文索引

（4）创建单列索引

（5）创建多列索引

（6）创建空间索引

3、用ALTERTABLE语句来创建索引

（1）创建平常索引

（2）创建唯一性索引

（3）创建全文索引

（4）创建单列索引

（5）创建多列索引

（6）创建空间索引

4、删除索引

大概

1. alter table '表名' drop index '索引名';

复制代码

5、索引的查询

1. show indexes from '表名';大概show keys from '表名';

复制代码

三、常见的索引情况

1、隐式转换导致索引失效

这一点应当引起重视，也是开发中常常会犯的错误。
由于表的字段name界说为varchar(20)，但在查询时把该字段作为number范例以where条件传给Mysql，这样会导致索引失效。
强转会引起索引失效：
user id name
index（name）; varchar（20）
错误的例子：

1. select * from test where name = 13333333333;

复制代码

我们需要对name先强转再比力
100行数据 把100行数据的name属性分别取出来先强转在比力。
原因：需要把所有的数据对应的name属性取出举行强转。
需要将每一行数据都拿出来举行强换之后再比力。
强转之后改变了原有字段的比力规则
正确的例子：

1. select * from test where name='13333333333'; //能用到索引

复制代码

2、对索引列举行运算导致索引失效

我所指的对索引列举行运算包罗(+，-，*，/，! 等)
index(id)
错误的例子：

1. select * from test where id - 1 < 9;

复制代码

需要将每一个元组中的id值先取出，再减一然后和9再去比力
正确的例子：

1. select * from test where id < 10;//能用到索引 select * from test where id = 9+1;//能用到索引

复制代码

3、使用MySQL内部函数导致索引失效

对于这样情况应当创建基于函数的索引。
错误的例子：

1. select * from test where 函数(id)=10;

复制代码

说明，此时id的索引已经不起作用了
要使用索引的话需要将：k(x) = y; → x = R(y);
错误的例子：

1. select * from test where id < 函数(age);  照旧用不到索引

复制代码

需要将每一个元组中的id值先取出,再通过函数盘算，盘算之后的值在和10比力
4、以下使用会使索引失效，应制止使用

（1）使用  、not in 、!=

index(id)

1. select * from id not in (12,13,15); //用不到 没有明白查询条件select * from id  in (12,13,15);   // 能用到

复制代码

（2）like “%_” 百分号在前

可接纳在创建索引时用reverse(columnName)这种方法处置惩罚

1. %:任意个任意字符

复制代码

index(name)

1. select * from name like &#39;%a&#39;;  //由于字符串的最左比力法  用不到

复制代码

字符串的索引是如何创建：从左到右一个字符一个字符举行巨细比力然后得出的B+树的布局。

1. select * from name like &#39;a%&#39;;  //由于字符串的最左比力法  能用到

复制代码

5、用OR关键字查询语句

查询语句的查询条件中只有OR关键字，且OR前后的两个条件中列都是索引时，查询中才会使用索引。否则，查询将不使用索引。

1.   name = "tom" or(或)  age < 90;  ---->index(name)   index(age);  name = "tom" and  age < 90;   ---->  index(name,age)  

复制代码

四、训练题

一、根据表布局，判断当分别执行如下5条语句时想要使用索引，分别应该如何创建。

1. //创建表    create table stu_index1 (id int primary key,name varchar(20),score double);    insert stu_index1 values(1,&#39;liu&#39;,89);    insert stu_index1 values(2,&#39;sam&#39;,78);    insert stu_index1 values(3,&#39;jim&#39;,95); //根据哪个属性筛选的就要在哪个属性上创建索引

复制代码

1、explain select * from stu_index where id = 3\G
InnoDB：由于主键索引默认创建，所以不需要任何使用；
叶子节点：存储主键和所有的数据；
MyISAM：能用到索引就行，由于主键索引默认创建，所以不需要任何使用；
叶子节点：存储创建索引的属性和数据相应的所在；
要求只查询一次：index(id,name,score)；
2、explain select * from stu_index where name=‘liu’\G
InnoDB:index(name); （查询两次）
叶子节点存储创建索引的属性的值和所对应的主键的值；
查询一次：index(name,score);
MyIsam：index(name) （查询两次）
叶子节点存储：创建索引的属性和数据对应的所在。
查询一次：index(name,id,score);
3、explain select * from stu_index where name = ‘liu’ and score = 89;
InnoDB：index(name,score); 可以 ；index(score,name); 因为mysql内部有优化，所以也可以。（查询一次）
叶子节点存储创建索引的属性的值和所对应的主键的值。
MyIsam：index(name,score); index(score,name); （查询两次）
叶子节点存储：创建索引的属性和数据对应的所在。
查询一次：index(name,score,id);
4、explain select id from stu_index where name=‘liu’\G
InnoDB： index(name); （查询一次）
叶子节点存储创建索引的属性的值和所对应的主键的值。
MyIsam：index(name) ;（查询两次）
叶子节点存储：创建索引的属性和数据对应的所在。
查询一次： index(name,id); 对 index(id,name); 错 （原因：无法进入B+树的布局）
5、explain select score from stu_index where name=‘liu’\G
MyIsam/InnoDB：查询一次：index(name,score);
原因：两个引擎下的索引的叶子节点都存储创建索引的属性的值。
二、根据表布局，判断当分别执行如下语句时想要使用索引，应该如何创建。

1. mysql> create table orderlist(name varchar(20) not null,    -> productid int not null,    -> date timestamp);

复制代码

1、explain select * from orderlist where name =‘Tom’ order by date\G
InnoDB：index(name,data)；对；index(data,name)；错；（查询两次）
MyISAM：index(name,data)；对；index(data,name)；错；（查询两次）
三、根据以下表布局和创建的索引分别判断以下sql语句能否使用到所创建的索引，能用到答复能，不能用到则回到不能。

1. CREATE TABLE `student_index` (        `id` int(11) NOT NULL,        `name` varchar(255) DEFAULT NULL,        `cid` int(11) DEFAULT NULL,        `school` char(20) NOT NULL DEFAULT &#39;&#39;,        KEY `name_cid_INX` (`name`,`cid`,`school`)        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;insert into student_index values        (1,&#39;weixin&#39;,12,&#39;caijing&#39;),        (2,&#39;weixin&#39;,13,&#39;ligong&#39;),        (3,&#39;weixin&#39;,14,&#39;gongye&#39;);

复制代码

最左前缀原则 ：从左往右举行比力的
要使用索引的话：左边第一个属性一定不能缺少。
要是想把索引用完全：从左到右一个属性都不能少，否则索引将使用不完全。
1、explain select * from student_index where name = ‘abc’ and cid = 123 and school = ‘ligong’ \G
能
2、explain select * from student_index where name = ‘abc’ and cid = 123\G
能
explain select * from student_index where cid = 123 and name = ‘abc’ \G
能，因为MySQL会根据索引对SQL语句举行优化；该语句会被优化为与上面SQL语句相同的语句；
3、explain select * from student_index where name = ‘abc’\G
能
4、.explain select * from student_index where name = ‘abc’ and school=‘ligong’\G
能
5、explain select * from student_index where cid = 123 and school = ‘ligong’\G
不能
四、团结查询中如何使用索引，以下两张表均在stu_id 字段上添加索引 index(stu_id)。判断哪张表的索引能用到。
表的布局如下：

MySQL会首先判断rb1（别名表a）和rb2（别名表b）哪个表小，这里表小主要指的是行数少，很显然rb2表小，MySQL会对rb2表举行整表遍历，然后在a表上根据stu_id字段举行查询，所以rb2表就是小表，无论如何都是要整表遍历的，是使用不到索引的，但是大表rb1表的stu_id字段创建索引，就能使用到了！所以在链接查询的时候，小表总是要整表搜索的，创建索引没有用，大表创建索引是能提高查询效率的，小表决定查询次数，大表决定查询时间（有索引就快，没有索引就慢）能不能用到索引，用到的是那张表的索引。
1、explain select * from rb1 a,rb2 b where a.stu_id = b.stu_id\G
where a.stu_id = b.stu_id底层处置惩罚：
把b中的所有的stu_id取出来，分别和a中的stu_id作比力；
a.stu_id in (23,24,25)；比力三次
把a中的所有的stu_id取出来，分别和b中的stu_id作比力；
b.stu_id in (23,24,25,40,41,42)；比力六次
MySQL接纳第一种做法：把小表中的所有的数据取出来然后和大表中的数据做对比；
rb1使用到索引；rb2没有使用到索引；即大表能用到索引，小表使用不到索引；

2、explain select * from rb1 a join rb2 b on a.stu_id = b.stu_id where a.stu_id < 25\G
此时rb1是小表：（