mysql---索引原理与慢查询优化
索引出现的原因:查询需要优化,所以用到索引
索引:在MySQL中也叫做‘键’,是存储引擎用于快速找到记录的一种数据结构
索引原理:
本质都是:通过不断地缩小想要获取数据的范围来筛选出最终想要的结果,同时把随机的事件变成顺序的事件,也就是说,有了这种索引机制,我们可以总是用同一种查找方式来锁定数据。
索引的数据结构:
b+树 如下图
如图所示,如果要查找数据项29,那么首先会把磁盘块1由磁盘加载到内存,此时发生一次IO,在内存中用二分查找确定29在17和35之间,锁定磁盘块1的P2指针,内存时间因为非常短(相比磁盘的IO)可以忽略不计,通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存,发生第二次IO,29在26和30之间,锁定磁盘块3的P2指针,通过指针加载磁盘块8到内存,发生第三次IO,同时内存中做二分查找找到29,结束查询,总计三次IO。真实的情况是,3层的b+树可以表示上百万的数据,如果上百万的数据查找只需要三次IO,性能提高将是巨大的,如果没有索引,每个数据项都要发生一次IO,那么总共需要百万次的IO,显然成本非常非常高。
1.索引字段要尽量的小
2.索引的最左匹配特性:当b+树的数据项是复合的数据结构,比如(name,age,sex)的时候,b+数是按照从左到右的顺序来建立搜索树的,
mysql索引管理
功能:
1.索引的功能就是加速查找
2.mysql中的primary key,unique,联合唯一也都是索引,这些索引处理加速查找意外,还有约束的 功能
MySQL的索引分类
普通索引index:加速查找
唯一索引:
主键索引primary key:加速查找+约束(不为空、不能重复)
唯一索引unique:加速查找+约束(不能重复)
联合索引:
primary key(id,name):联合主键索引
unique(id,name):联合唯一索引
index(id,name):l联合普通索引
full text:全文索引
索引的两大类型hash与btree
#我们可以在创建上述索引的时候,为其指定索引类型,分两类 hash类型的索引:查询单条快,范围查询慢 btree类型的索引:b+树,层数越多,数据量指数级增长(我们就用它,因为innodb默认支持它) #不同的存储引擎支持的索引类型也不一样 InnoDB 支持事务,支持行级别锁定,支持 B-tree、Full-text 等索引,不支持 Hash 索引; MyISAM 不支持事务,支持表级别锁定,支持 B-tree、Full-text 等索引,不支持 Hash 索引; Memory 不支持事务,支持表级别锁定,支持 B-tree、Hash 等索引,不支持 Full-text 索引; NDB 支持事务,支持行级别锁定,支持 Hash 索引,不支持 B-tree、Full-text 等索引; Archive 不支持事务,支持表级别锁定,不支持 B-tree、Hash、Full-text 等索引;
创建/删除索引的语法
#方法一:创建表时 CREATE TABLE 表名 ( 字段名1 数据类型 [完整性约束条件…], 字段名2 数据类型 [完整性约束条件…], [UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY [索引名] (字段名[(长度)] [ASC |DESC]) ); #方法二:CREATE在已存在的表上创建索引 CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 ON 表名 (字段名[(长度)] [ASC |DESC]) ; #方法三:ALTER TABLE在已存在的表上创建索引 ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 (字段名[(长度)] [ASC |DESC]) ; #删除索引:DROP INDEX 索引名 ON 表名字;
测试索引
1.准备
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#1. 准备表 create table s1( id int, name varchar(20), gender char(6), email varchar(50) ); #2. 创建存储过程,实现批量插入记录 delimiter $$ #声明存储过程的结束符号为$$ create procedure auto_insert1() BEGIN declare i int default 1; while(i<3000000)do insert into s1 values(i,concat('egon',i),'male',concat('egon',i,'@oldboy')); set i=i+1; end while; END$$ #$$结束 delimiter ; #重新声明分号为结束符号 #3. 查看存储过程 show create procedure auto_insert1\G #4. 调用存储过程 call auto_insert1();
2.在没有索引的前提下测试查询速度
#无索引:从头到尾扫描一遍,所以查询速度很慢 mysql> select * from s1 where id=333; +------+---------+--------+----------------+ | id | name | gender | email | +------+---------+--------+----------------+ | 333 | egon333 | male | 333@oldboy.com | | 333 | egon333 | f | alex333@oldboy | | 333 | egon333 | f | alex333@oldboy | +------+---------+--------+----------------+ rows in set (0.32 sec) mysql> select * from s1 where email='egon333@oldboy'; .... ... rows in set (0.36 sec)
3.加索引
#1. 一定是为搜索条件的字段创建索引,比如select * from t1 where age > 5;就需要为age加上索引 #2. 在表中已经有大量数据的情况下,建索引会很慢,且占用硬盘空间,插入删除更新都很慢,只有查询快 比如create index idx on s1(id);会扫描表中所有的数据,然后以id为数据项,创建索引结构,存放于硬盘的表中。 建完以后,再查询就会很快了 #3. 需要注意的是:innodb表的索引会存放于s1.ibd文件中,而myisam表的索引则会有单独的索引文件table1.MYI
索引的正确使用
一 并不是说我们创建了索引就一定会加快查询速度,如下索引未命中
select sql_no_cache * from s1 where email='xxx'; #命中索引,速度很快
select sql_no_cache * from s1 where email like '%old%'; #无法使用索引,速度依然很慢
覆盖索引与索引合并
覆盖索引:
- 在索引文件中直接获取数据
http://blog.itpub.net/22664653/viewspace-774667/
#分析
select * from s1 where id=123;
该sql命中了索引,但未覆盖索引。
利用id=123到索引的数据结构中定位到该id在硬盘中的位置,或者说再数据表中的位置。
但是我们select的字段为*,除了id以外还需要其他字段,这就意味着,我们通过索引结构取到id还不够,
还需要利用该id再去找到该id所在行的其他字段值,这是需要时间的,很明显,如果我们只select id,
就减去了这份苦恼,如下
select id from s1 where id=123;
这条就是覆盖索引了,命中索引,且从索引的数据结构直接就取到了id在硬盘的地址,速度很快
#索引合并:把多个单列索引合并使用
#分析:
组合索引能做到的事情,我们都可以用索引合并去解决,比如
create index ne on s1(name,email);#组合索引
我们完全可以单独为name和email创建索引
组合索引可以命中:
select * from s1 where name='egon' ;
select * from s1 where name='egon' and email='adf';
索引合并可以命中:
select * from s1 where name='egon' ;
select * from s1 where email='adf';
select * from s1 where name='egon' and email='adf';
乍一看好像索引合并更好了:可以命中更多的情况,但其实要分情况去看,如果是name='egon'
and email='adf',那么组合索引的效率要高于索引合并,如果是单条件查,那么还是用索引合并比较合理
添加索引原则
1.最左前缀匹配原则,(特别重要的原则)
- 最左前缀匹配:必须按照从左到右的顺序匹配