MYSQL索引原理及使用_神雕大侠mu

MYSQL索引原理及使用

1、存储过程（在正式表演mysql的索引前我们先准备数据）

1.1 创建表

CREATE TABLE pa_emp  
(  
	pa_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,  /*UNSIGNED存储非负数值*/
	pa_empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*编号*/  
	pa_ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/  
	pa_job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/  
	pa_mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上级编号*/  
	pa_hiredate DATE NOT NULL,/*入职时间*/  
	pa_sal DECIMAL(7,2) NOT NULL,/*薪水*/  
	pa_comm DECIMAL(7,2) NOT NULL,/*红利*/  
	pa_deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/  
)ENGINE=INNODB DEFAULT CHARSET=utf8 ;
1
2
3
4
5
6
7
8
9
10
11
12

1.2 修改mysql设置（创建存储过程前要修改设置，否则mysql不让创建）

查看：show variables like 'log_bin_trust_function_creators';
设置：set global log_bin_trust_function_creators=1;

1.3 创建生产随机字符串函数

DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
 DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
 DECLARE return_str VARCHAR(255) DEFAULT '';
 DECLARE i INT DEFAULT 0;
 WHILE i < n DO
 SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
 SET i = i + 1;
 END WHILE;
 RETURN return_str;
END $$
1
2
3
4
5
6
7
8
9
10
11
12

1.4 创建生成随机数字函数

DELIMITER $$
CREATE FUNCTION rand_num( ) 
RETURNS INT(5)  
BEGIN   
 DECLARE i INT DEFAULT 0;  
 SET i = FLOOR(100+RAND()*10);  
RETURN i;  
 END $$
1
2
3
4
5
6
7
8

1.5 创建存储过程

DELIMITER $$
CREATE PROCEDURE insert_pa_emp(IN START INT(10),IN max_num INT(10))  
BEGIN  
DECLARE i INT DEFAULT START;   
#set autocommit =0 把autocommit设置成0  
 SET autocommit = 0;    
 REPEAT  
 SET i = i + 1;  
 INSERT INTO pa_emp (pa_empno, pa_ename ,pa_job ,pa_mgr ,pa_hiredate ,pa_sal ,pa_comm ,pa_deptno ) VALUES ((START+i) ,rand_string(6),'programmer',0001,CURDATE(),20000,4000,rand_num());  
 UNTIL i = max_num  
 END REPEAT;  
 COMMIT;  
 END $$
1
2
3
4
5
6
7
8
9
10
11
12
13

1.6 调用函数存储过程

CALL insert_pa_emp(0,5000000);
1

这样数据就会被插入pa_emp表 速度还挺快

2、索引的原理

2.1 解释

索引是帮助mysql高效获取数据的数据结构。
在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向)数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构就是索引。
原理：建立索引的数据，就是通过事先排好顺序，在查找时可以应用二分查找来提高查询效率
索引优势：通过索引列对数据进行排序，降低数据排序成本，降低cpu消耗。
索引劣势：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MYSQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息

2.2 索引分类

在MySQL数据库中，回表（Look Up）指的是在进行索引查询时，首先通过索引定位到对应页，然后再根据行的物理地址找到所需的数据行。换句话说，回表是指根据索引查询到的主键值再去访问主键索引，从而获取完整的数据记录。

索引：①聚簇索引 ② 非聚簇索引

聚簇索引： 将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据（一张表只能有一个聚簇索引）
非聚簇索引：将数据与索引分开存储，索引结构的叶子节点指向了对应位置。
在innodb中，在聚簇索引之上创建的索引称之为辅助索引，非聚簇索引都是辅助索引。辅助索引访问数据都是需要二次查找。 如图：
①查找where id=7 直接查找主键索引(即 聚簇索引)，获取行数据。
②查找 where name=‘EWson’ ,第一步是在辅助索引的叶子节点查找’EWson’ 对应的主键id，第二步使用主键在主键索引执行一次检索获取行数据

主键的重要性及int型自增主键的必要性
聚簇索引默认是主键，如果表中没有主键，InnoDB会选择一个唯一且非空的列来代替主键，如果没有这样的列，INNODB会隐式定义一个主键（类似oracle的rowId）来作为聚簇索引。
使用建议：
①主键为聚簇索引，最好不要使用uuid作主键。因为uuid的值太过离散，不适合排序，且可能出现新增记录的uuid插在树中间的位置导致索引树调整复杂度变大，消耗更多资源和时间。
②建议使用int类型自增做主键，方便排序，且默认会在索引树的末尾增加主键值，对索引树的结构影响最小，自增主键索引紧邻相凑，磁盘碎片少，效率大大提高

2.3 索引树的底层结构

B+树的结构图

B树的结构图：

对比B树和B+树：
①B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了
②B+树的查询效率更加稳定：由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。
③由于B+树的数据都存储在叶子结点中，分支结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，所以通常B+树用于数据库索引

B+树的范围查询：B+树的叶子节点是从小到大一次排列的，相互之间是用指针连接的，可以做范围查询

3、索引的常见操作语法

3.1 索引常用语法

1、查看当前表拥有的索引 SHOW INDEX FROM table_name
2、创建索引 CREATE INDEX index_name ON table_name(columnname)
3、删除 索引 DROP INDEX index_name ON table_name

3.2 索引建立与不建立的选择

哪些情况适合建立索引？
1、主键自动建立唯一索引
2、频繁作为查询条件的字段应该创立索引
3、查询中与其他表关联的字段，外键关系建立索引
4、排序字段要建立索引
5、查询中统计或分组字段需要建立索引

哪些情况不适合建立索引？
1、表记录太少（上几十万或百万数据再建立索引）
2、经常增删改查的字段不建立索引（因为频繁更新，增加开销）
3、重复且平均分配的字段不建立索引（比如：某字段是性别，只有男、女两种情况，boolean类型只有true和false两种，某字段只有0/1两种情况）区分度太小就不适合建立索引

3.3 索引性能分析之EXPLAIN关键字

explain的作用：
1、表的读取顺序
2、数据读取操作的操作类型
3、哪些索引可以使用
4、哪些索引被实际使用
5、表之间的引用
6、每张表有多少行被优化器查询

explain的各种含义分析：
①id:SQL执行的顺序的标识,SQL从大到小的执行。id如果相同，可以认为是一组，从上往下顺序执行
②select_type：示查询中每个select子句的类型
（1）SIMPLE(简单SELECT,不使用UNION或子查询等)
（2) PRIMARY(查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)
（3) UNION(UNION中的第二个或后面的SELECT语句)
（4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)
（5) UNION RESULT(UNION的结果)
（6) SUBQUERY(子查询中的第一个SELECT)
（7) DEPENDENT SUBQUERY(子查询中的第一个SELECT，取决于外面的查询)
（8) DERIVED(派生表的SELECT, FROM子句的子查询)

③table：表名
④type:MySQL在表中找到所需行的方式，又称“访问类型”。是分析”查数据过程”的重要依据
性能顺序：system > const > eq_ref > ref > range > index > ALL
（1)all : 即全表扫描
（2)index : 按索引次序扫描，先读索引，再读实际的行，结果还是全表扫描，主要优点是避免了排序。因为索引是排好的。
（3)range:以范围的形式扫描。explain select * from a where a_id > 1\G
（4)ref:非唯一索引访问(只有普通索引)
（5)eq_ref:使用唯一索引查找(主键或唯一索引)
（6)const:常量查询,在整个查询过程中这个表最多只会有一条匹配的行，比如主键 id=1 就肯定只有一行，只需读取一次表数据便能取得所需的结果，且表数据在分解执行计划时读取
（7)system:系统查询
⑤possible_keys:可能用到的索引
⑥key:实际用到的索引
⑦key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）不损失精确性的情况下，长度越短越好
⑧ref：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
⑨rows：表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数
十、Extra：显示以上信息之外的其他信息。性能从好到坏:useing index>usinh where > using temporary | using filesort
（1)Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询
（2)Using filesort：MySQL中无法利用索引完成的排序操作称为“文件排序”
（3)Using where:列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤
（4)Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。
（5)Impossible where：这个值强调了where语句会导致没有符合条件的行

4、索引失效情况

①带头大哥不能死，中间兄弟不能断。查询从索引的最左前列开始并且不跳过索引中的列。最左前缀要遵守（火车可以没车厢不能没车头）
②不等空值还有OR，索引失效要少用。使用不等于（!= 或者<>）的时候无法使用索引会导致不等之后的索引失效或全表扫表 is null ,is
not null，or 也无法使用索引
③LIKE百分写最右，覆盖索引不写*。like使用索引需要①把百分号加在最右边 或者 ②使用覆盖索引。覆盖索引是查询字段处于索引字段中，这可以随便使用like 都不会失效。like的%在左边时，索引失效的原因：由于B+树是按顺序查找的,前面无法确定的话后面是无法使用索引,所以前面有%会导致索引失效,改为全表扫描，这会导致索引失效并且查询速度变慢
④索引列上少计算，范围之后全失效。不要再索引列上做任何计算、函数、类型转换（包括隐式的类型转换），使用范围查询（如
id>5),范围之后的索引失效

5.SQL优化

尽量避免使用子查询
用IN来替换OR
读取适当的记录LIMIT M,N，而不要读多余的记录
禁止不必要的Order By排序
总和查询可以禁止排重用union all 避免随机取记录
将多次插入换成批量Insert插入
只返回必要的列，用具体的字段列表代替select * 语句
区分in和exists 优化Group By语句
尽量使用数字型字段
优化Join语句

6.为什么要遵循最左前缀原则？

可以看到a的值是有顺序的，1，1，2，2，3，3，而b的值是没有顺序的1，2，1，4，1，2。但是我们又可发现a在等值的情况下，b值又是按顺序排列的，但是这种顺序是相对的。
MySQL创建联合索引的规则是首先会对联合索引的最左边第一个字段排序，在第一个字段的排序基础上，然后在对第二个字段进行排序。所以b=2这种查询条件没有办法利用索引

为什么得最左匹配，不按照这个来为什么失效?

联合索引B+树中的键值是排好序的，这里的排序是相对排序。例如，(a,b,c)联合索引，a首先排好序，而b是在a列排序的基础上再做排序，同样c列是在a,b的有序的基础上 再做排序。所以 where a=“张三” order by b=“34” 这种请求的话，可以直接在索引树上查询出来，不用对b额外排序。而 where a=“张三” order by c=“成都” 这种请求 还需要额外对c列进行一次排序。

7.字符串和整数类型哪个更适合做索引？

整数类型
①整型索引占用更小的存储空间，查询速度也更快，因为整型的比较是直接二进制的比较，uuid是比较ASC码。
②和整型索引相比，字符串索引占用更大的存储空间，因为需要根据字符集对字符串进行转换，所以存储空间的大小取决于字符集的定义
③ uuid的大小不确定，可能是B+树的中间的某个值，那么会导致B+树分裂，再平衡。用整型递增就依次加在后面，不会导致树分裂

雪花算法是趋势递增，是可以接受的。

8.如何从千万级别的mysql表中快速查询出 第 10000001到10000010之间的数据？

例如： employees表，有id，name，age，position,desc字段
①建立一个关于那么的索引 CREATE INDEX sys_badge_name_age_position ON employees(name，age，position);
②使用内连接
分页查询优化

1.根据自增且连续的主键排序的分页查询
select * from employees where id > 90000 limit 5;
或者（主键不自增） select * from employees where id >(select id from employees limit 90000,1) limit 5;
2.根据非主键字段排序的分页查询
select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;