Mysql中索引的使用

什么是索引

是按照用户任意指定的字段对数据排序的一种数据结构（默认主键即索引）

1.innodb引擎–使用b+树自动对索引排序

1.1.主键索引

插入时会自动排序，所以插入慢，查询快
SELECT * FROM t1;.

explain SELECT * FROM t1 WHERE a=6;//查询索引是否使用

2.b+树

2.1.完全二叉树–AVL树

2.2.b+树

【注】b+树一个节点里可以有多个元素，从而降低树的高度，减少磁盘读取次数
在sql中，b+树的叶子节点存的是具体数据，非叶子节点存的是索引的数据（以空间换时间）

【注】此图既有索引数据，又有具体数据，成为“聚集索引”，即索引和数据放在一起
当执行select * from t1，直接从左至右返回叶子节点层1-8的所有数据（全表扫描）
当执行explain SELECT * FROM t1 WHERE a=6，通过索引查询。
当执行explain SELECT * FROM t1 WHERE a>6，先找=6，再取6右边的所有数据。

3.MyISAM引擎—按照插入顺序，不排序

select * from t2;

4.自己创建索引—辅助索引

create index id_t1_bcd on t1(b,c,d);-- 手动创建自己的索引
【注】这一句的排序规则为：
按照b/c/d字段排序，先按照b排序，b相等时按照c，bc都相等时按照d
当执行explain SELECT * FROM t1 WHERE b=1 and c=1 and d=1时，现根据索引字段找到数据对应的主键，再根据主键去主键索引里面取出数据（这一步叫【回表】）

5.最左前缀原则

5.1. 索引最左前缀没有时，不走索引

explain SELECT * FROM t1 WHERE c=1 and d=1;
这一句无法使用索引（ALL是全表扫描的意思）

因为索引数据第一个字段不全，故无法在b+树中进行比较

5.2. 索引最左前缀有，缺少其他时，可以走索引

explain SELECT * FROM t1 WHERE b=2;
这一句可以执行索引，因为最左前缀存在，故可以比对b+树

5.3. 索引最左前缀不全，不走索引

explain SELECT * FROM t1 WHERE b like ‘%2’;
这一句无法使用索引

5.4.sql自动选择查询方式

explain SELECT * FROM t1 WHERE b>2;
此句虽然可以用索引，但sql自动选择不使用索引，用了全表扫描，因为sql会根据磁盘读写次数较少的那种选择type

例1：explain SELECT * FROM t1 WHERE b>6;此句又会选择索引

例2：explain SELECT b FROM t1 WHERE b>2;此句使用索引（成为【索引覆盖】）

例3：explain SELECT b FROM t1;此句没有where，但因为【索引覆盖】，所以也会用索引

【注】辅助索引的b+树只存了索引和主键（单位数据量少，不全），所以相同一页的空间内，辅助索引可以存的数据比主键索引多，所需查询的页数也更少了

6.利用索引加快limt的查询速度

当limt 50000,100时，会变慢，因为找到第50000条需要大量时间，可以使用主键索引加快
原始为select * from t1 limit 50000,100
改进后select * from t1 where id>49999 limit 1,100;

7.关于模糊查询与索引

参考地址：https://blog.csdn.net/zhenwei1994/article/details/81913531
【注】
explain SELECT * FROM tbl_user WHERE name like ‘%aa’-- 超过了覆盖索引不能用%开头，单可以用%结尾

explain SELECT name,age FROM tbl_user WHERE name like ‘%aa’-- 覆盖索引内字段的模糊查询可以正常使用%

explain SELECT b FROM t1 WHERE b like ‘%2’;-- 可以使用索引，因为查询字段在覆盖索引内
explain SELECT * FROM t1 WHERE b like ‘%2’; – 不能使用索引，因为查询字段在覆盖索引外

8.拓展：红黑树—平衡二叉树

8.1.起源

普通的二叉树在遇到单边增长时会失效，无法提高查询效率

8.2.红黑树效果

相对于二叉树解决了不平衡的问题，但还是容易出现不平衡

所以mysql选择b+树
【附】sql语句

CREATE table t1(
a int(11) primary key,
b int,
c int,
d int,
e varchar(20)
)engine=INNODB;

INSERT INTO t1 VALUES(4,2,1,1,'d');
INSERT INTO t1 VALUES(1,1,1,1,'a');
INSERT INTO t1 VALUES(8,8,8,8,'h');
INSERT INTO t1 VALUES(2,2,2,2,'b');
INSERT INTO t1 VALUES(5,2,3,5,'e');
INSERT INTO t1 VALUES(3,3,2,2,'c');
INSERT INTO t1 VALUES(7,4,5,5,'g');
INSERT INTO t1 VALUES(6,6,4,4,'f');



create table t2(
a int primary key,
b int,
c int,
d int,
e varchar(20)
) engine=MyISAM;

INSERT INTO t2 VALUES(4,2,1,1,'d');
INSERT INTO t2 VALUES(1,1,1,1,'a');
INSERT INTO t2 VALUES(8,8,8,8,'h');
INSERT INTO t2 VALUES(2,2,2,2,'b');
INSERT INTO t2 VALUES(5,2,3,5,'e');
INSERT INTO t2 VALUES(3,3,2,2,'c');
INSERT INTO t2 VALUES(7,4,5,5,'g');
INSERT INTO t2 VALUES(6,6,4,4,'f');


CREATE table t3(
a int,
b int,
c int,
d int,
e varchar(20)
)engine=INNODB;

INSERT INTO t3 VALUES(4,2,1,1,'d');
INSERT INTO t3 VALUES(1,1,1,1,'a');
INSERT INTO t3 VALUES(8,8,8,8,'h');
INSERT INTO t3 VALUES(2,2,2,2,'b');
INSERT INTO t3 VALUES(5,2,3,5,'e');
INSERT INTO t3 VALUES(3,3,2,2,'c');
INSERT INTO t3 VALUES(7,4,5,5,'g');
INSERT INTO t3 VALUES(6,6,4,4,'f');

SELECT * FROM t1;

select * from t2;

select * from t3;
-- mysql自定义一页的大小=16kb
show global status like 'Innodb_page_size';

explain SELECT * FROM t1 WHERE a>6;

create index id_t1_bcd on t1(b,c,d);-- 手动创建自己的索引

explain SELECT * FROM t1 WHERE b=1 and c=1 and d=1;

explain SELECT * FROM t1 WHERE c=1 and d=1;

explain SELECT * FROM t1 WHERE b=2;
explain SELECT b FROM t1 WHERE b like '%2';
SELECT * FROM t1 WHERE b=2;

explain SELECT * FROM t1 WHERE b>6;

explain SELECT b FROM t1 WHERE b>2;

explain SELECT b FROM t1;

-- 索引的模糊查询
CREATE TABLE `tbl_user`(
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`age`INT(11) DEFAULT NULL,
`email` VARCHAR(20) DEFAULT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('1aa1',21,'a@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('2bb2',23,'b@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('3cc3',24,'c@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('4dd4',26,'d@163.com');

create index idx_user_nameAge on tbl_user(name,age)

explain SELECT * FROM tbl_user WHERE name like '%aa'-- 超过了覆盖索引不能用%开头，单可以用%结尾

explain SELECT name,age FROM tbl_user WHERE name like '%aa'-- 覆盖索引内字段的模糊查询可以正常使用%

SELECT name,age FROM tbl_user WHERE name like '%aa%'

explain SELECT id,name,age,email FROM tbl_user WHERE name like '%aa%'