day41mysql索引原理与慢查询优化

01&＃xff1a;索引的出现是为了提高查询数据的效率02&＃xff1a;索引在mysql叫做“键” 或则“key“&＃xff08;primary key&＃xff0c;uniquekey &＃xff0c;还有一个index key&＃xff09;&＃xff0c;是存储引擎用于快速找到记录的一种数据结构。索引能减少io次数&＃xff0c;加速查询&＃xff08;primary key 和unique key &＃xff0c;除了有加速查询的效果外&＃xff0c;还有约束的效果&＃xff0c;primary key 不为空且唯一&＃xff0c;unique key唯一&＃xff0c;而index key 只有加速查询的效果&＃xff0c;没有约束效果&＃xff09;03&＃xff1a;优缺点&＃xff1a;索引太多&＃xff0c;应用程序的性能会受到影响&＃xff0c;索引太少&＃xff0c;对查询性能又会产生影响&＃xff0c;需要找到一个平衡点。应该在一开始在需要的地方添加索引。04&＃xff1a;索引的原理&＃xff1a;索引时在没有数据的时候先建立索引然后再往里面添加数据&＃xff0c;这样数据在查找的时候就能不断缩小需要查找的数据的范围了&＃xff0c;索引就像是书的目录。在不断添加数据后&＃xff0c;索引的能增加查询效率。但是会降低写入效率&＃xff08;一旦有新的数据块写入&＃xff0c;原来的索引就没用了。需要重新建立索引。&＃xff09;索引的影响&＃xff1a;01&＃xff1a;在表中有大量的数据的前提下&＃xff0c;创建索引速度会很慢02&＃xff1a; 在创建索引后&＃xff0c;对表的查询速度会大幅度提升&＃xff0c;但是写性能会降低。索引的本质&＃xff1a;通过不断缩小想要的数据的范围来筛选出最终想要的结果&＃xff0c;同时把随机事件变成顺序时间&＃xff0c;也就是说&＃xff0c;有了索引机制&＃xff0c;可以总是用同一种查找方式来锁定数据。

磁盘读取数据是靠机械运动&＃xff0c;每次读取数据花费的时间由寻道时间&＃xff08;磁臂移动到磁道所用的时间&＃xff09;&＃xff0c;旋转时间&＃xff08;磁盘转速&＃xff09;和 传输时间&＃xff08;将数据从磁盘读取或则写入&＃xff09;三个部分。每次访问磁盘需要花费很多时间&＃xff08;相对于cpu处理数据的速度&＃xff09;。所以考虑到io操作每次读取数据占用的时间大小&＃xff0c;计算机操作系统做了优化&＃xff0c;当一次io操作的时候&＃xff0c;不光把当前磁盘地址的数据&＃xff0c;还把相邻的数据也都读取到内存缓冲区内。因为局部预读性原理告诉我们&＃xff0c;当计算机访问一个地址的数据的时候&＃xff0c;与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关&＃xff0c;一般为4k或8k&＃xff0c;也就是我们读取一页内的数据时候&＃xff0c;实际上才发生了一次IO&＃xff0c;这个理论对于索引的数据结构设计非常有帮助。

　　 说明&＃xff1a;b&＃43;数主要是为了磁盘读取查找数据出现的。&＃xff08;树形演示&＃xff09;索引的结构就是为了减少io&＃xff0c;加速查询。索引的数据结构是 ”b&＃43;树&＃xff08;B&＃43;树是通过二叉查找树&＃xff0c;再由平衡二叉树&＃xff0c;b数演化而来。&＃xff09;”

　　　　　　 .frm (存储表的结构).myd&＃xff08;是数据文件&＃xff09;.myi&＃xff08;索引文件&＃xff09;

　　　　　　.frm(存储结构).idb&＃xff08;存储数据和索引&＃xff09;

　　　　数据库中的B&＃43;树索引可以分为聚集索引和辅助索引聚集索引和辅助索引相同的是&＃xff1a;两者内部都是b&＃43;数的形式&＃xff0c;高度是平衡的&＃xff0c;叶子结点存放着所有的数据聚集索引和辅助索引的不同点是&＃xff1a;叶子结点是否存放的是一整行的信息。

　　　　　优点&＃xff1a;01&＃xff1a;对主键的排序查找和范围查找速度非常快&＃xff0c;叶子结点的数据就是用户需要的数据。02&＃xff1a; 范围查询 &＃xff1a; 如果需要查找主键某一范围的数据&＃xff0c;通过叶子结点的上层中间结点就可以得到页的范围&＃xff0c;之后直接读取数据页即可

　　表中除了聚集索引外其他索引都是辅助索引&＃xff08;Secondary Index&＃xff0c;也称为非聚集索引&＃xff09;&＃xff08;unique key啊、index key啊&＃xff09;&＃xff0c;与聚集索引的区别是&＃xff1a;辅助索引的叶子节点不包含行记录的全部数据

　　 01&＃xff1a;索引的功能是快速查找02&＃xff1a;mysql中德primary key unique&＃xff0c;联合唯一都是索引&＃xff0c;这些索引除了加速查找以外还有约束功能03&＃xff1a;常用的索引001&＃xff1a;普通索引index&＃xff1a; 加速查找002&＃xff1a;唯一索引&＃xff1a;主键索引 primary key &＃xff1a;加速查找和约束unique&＃xff08;id&＃xff0c;name&＃xff09; &＃xff1a;联合唯一索引index&＃xff08;id&＃xff0c;name&＃xff09;&＃xff1a;联合普通索引04&＃xff1a;建立索引删除索引创建索引三种方法&＃xff1a;001&＃xff1a;alter table 表名 add primary key&＃xff08;需要加索引的字段&＃xff09; (eg: alter table t1 add primary key(id) 给t1表的id加索引)002&＃xff1a;create index 索引名 on 表名&＃xff08;字段名&＃xff09;&＃xff08;eg&＃xff1a; create index ix_id on t1(id) 给t1表的id加索引&＃xff09;003&＃xff1a;创建表时给其加索引create table 表名&＃xff08;字段名1 数据类型 完整约束条件字段名2 数据类型 完整约束条件索引名 字段名3&＃xff08;长度&＃xff09;&＃xff09;&＃xff1b;例子&＃xff1a;create table t1(id int,name char,age int,sex enum("male","female"),unique key uni_id(id),index ix_name(name) #index 没有key
);删除索引&＃xff1a;drop index 索引名字 on 表名字

create table s1(id int,name varcahr(20),gender cahr(6),email varchar(50));

　　 delimiter //create procedure auto_insert()begindecalare i in default 1;while (i<3000000) doinsert into s1 values(i,"TOM","male",concat("TOM",i,"oneboy"));set i&＃61;i&＃43;1;end while;end //delimiter ;

show create procedure auto_insert\G

call auto_insert();

总结&＃xff1a;#1. 一定是为搜索条件的字段创建索引&＃xff0c;比如select * from s1 where id &＃61; 333;就需要为id加上索引#2. 在表中已经有大量数据的情况下&＃xff0c;建索引会很慢&＃xff0c;且占用硬盘空间&＃xff0c;建完后查询速度加快
比如create index idx on s1(id);会扫描表中所有的数据&＃xff0c;然后以id为数据项&＃xff0c;创建索引结构&＃xff0c;存放于硬盘的表中。建完以后&＃xff0c;再查询就会很快了。#3. 需要注意的是&＃xff1a;innodb表的索引会存放于s1.ibd文件中&＃xff0c;而myisam表的索引则会有单独的索引文件table1.MYI
MySAM索引文件和数据文件是分离的&＃xff0c;索引文件仅保存数据记录的地址。而在innodb中&＃xff0c;表数据文件本身就是按照B&＃43;Tree&＃xff08;BTree即Balance True&＃xff09;组织的一个索引结构&＃xff0c;这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键&＃xff0c;因此innodb表数据文件本身就是主索引。因为inndob的数据文件要按照主键聚集&＃xff0c;所以innodb要求表必须要有主键&＃xff08;Myisam可以没有&＃xff09;&＃xff0c;如果没有显式定义&＃xff0c;则mysql系统会自动选择一个可以唯一标识数据记录的列作为主键&＃xff0c;如果不存在这种列&＃xff0c;则mysql会自动为innodb表生成一个隐含字段作为主键&＃xff0c;这字段的长度为6个字节&＃xff0c;类型为长整型.