Q:什么是索引?
A:官方对索引的定义为:索引(Index)是帮助MYSQL高效获取数据的数据结构
所以索引的本质是数据结构可以简单理解为"排好序的快速查找数据结构"
索引的目的在于提高查询效率,可以类比字典
索引会影响where和order by后的语句
性能分析目录
1.是什么
2.能干嘛
3:怎么用
来了来了!!重要!!
数据本身之外,数据库还维护着一个满足特定查找算法的数据结构,这些数据结构以某种方式指向数据,
这样就可以在这些数据结构的基础上实现高级查找算法,这种数据结构就是索引.
我们平常所说的索引,如果没有特别指明,都是指B树(多路搜索树,并不一定是二叉的)结构组织的索引.
索引的优势:提高了数据检索的效率,降低数据库的IO成本;通过索引列对数据进行排序,降低数据排序的成本,降低了CPU消耗
索引的劣势:虽然索引大大提高了查询速度,同时却会降低更新表的速度,因为更新表是,MySQL不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段,都会调整因为更新所带来的键值变化后的索引信息.
--
索引的分类
单值索引:即一个索引只包含单个列,一个表可以有多个单列索引
唯一索引:索引列的值必须唯一,但允许有空值
复合索引:即一个索引包含多个列
基本语法:
创建:CREATE [UNIQUE] INDEX indexName ON mytable(columnname(length));
删除:DROp INDEX [indexName] ON mytable;
查询:show index from tbl_emp;
--
以下情况下需要创建索引:
1.主键自动建立唯一索引
2.频繁作为查询条件的字段应该创建索引 例如:银行卡号.手机号
3.查询中与其他表关联的字段,外键关系建立索引
4.查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度
5.查询中统计或者分组字段
以下情况下不需要创建索引:
1.表记录太少
2.经常增删改的表
3.数据重复且分布平均的表字段,因此应该职位最经常查询和最经常排序的数据列建立索引.
3.注意,如果某个数据列包含许多重复的内容,为它建立索引就没有太大的实际效果.
-- 性能分析
Mysql Query Optimizer mysql自带的优化器
-- 重点来了!!!!!
EXPLAIN
+----+-------------+---------+------------+------+---------------+------+---------+------+------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+
1.是什么
使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而知道Mysql是如何处理你的SQL语句的
分析你的查询语句或是表结构的性能瓶颈
2.能干嘛
-- 区分表的读取顺序
上图中的id:id值相同时,顺序执行,id值不同时,从大向小执行
-- 数据读取操作的操作类型
对于select_type:常见六种
1.simple 简单的select查询,查询中不包含子查询或者union
2.primary 查询中若保护任何复杂的子部分,最外层查询则被标记为primary
3.subquery 在select或where列表中包含了子查询
4.derived 在from列表中包含的子查询被标记为DERIVED(衍生)Mysql会递归执行这些子查询,把结果放在 临时表里,形成的临时表名字会被定义为derived+id id代表由对应id的表衍生
5.union 若第二个select出现在union之后,则被标记为union
若union包含在from子句的子查询中,外层select将被标记为derived
6.union result 从union表获取结果的select
查询的类型,主要是用于区别普通查询、联合查询、子查询等复杂查询
--
对于table:显示这一行数据是关于哪张表的
-- 显示查询使用了何种类型
对于type:有
1.all Full Table Scan,将遍历全表以找到匹配的行
2.index Full Index Scan,index与all区别为index类型只遍历索引树.这通常比all快,
因为索引文件通常比数据文件小.
EXPLAIN select id from tbl_emp;
(也就是说虽然all和index都是读全表,但index是从索引中读取的,而all是从硬盘中读的)
3.range 只检索给定范围的行,使用一个索引来选择行.key列显示使用了哪个索引
一般就是在你的where语句中出现了between、<、>、in等的查询
这种范围扫描 索引扫描比全表扫描要好,因为他们只需要开始于索引的某一点,
而结束于另一点,不用扫描全部索引
EXPLAIN select * from tbl_emp where deptId between 2 and 3;
4.ref 非唯一性索引扫描,返回匹配某个单独值的所有行.
本质上也是一种索引访问,它返回所有匹配某个单独值的行,然而,
它可能会找到多个符合条件的行,所以他应该属于查找和扫描的混合体
CREATE INDEX index1 on tbl_emp(id,deptId);
EXPLAIN select * from tbl_emp where deptId=1;
5.eq_ref 唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配.常见于主键或唯一索引扫描
EXPLAIN select * from tbl_emp a,tbl_dept b where a.deptId = b.id;
6.const 表示通过索引一次就找到了,const用于比较primary key或者union索引.因为只匹配一行数据, 所以很快
如将主键置于where列表中,Mysql就能将该查询转换为一个常量
EXPLAIN select id from (select * from tbl_emp where tbl_emp.id = 1) new;
7.system 表只有一行记录(等于系统表,这是const类型的特例,平时不会出现,这个也可以忽略不计)
8.NULL
从最好到最差依次是:
system>const>eq_ref>ref>range>index>all
一般来说,需要保证查询至少达到range级别,最好能达到ref
--
+----+-------------+---------+------------+------+---------------+------+---------+------+------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+
对于possible_keys:显示可能应用在这张表上的索引,一个或多个
查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用
对于key:实际使用的索引.如果为null,则没有使用索引
查询中若使用了覆盖索引,则该索引仅出现在key列表中
EXPLAIN select id,deptId from tbl_emp;
所谓使用覆盖索引,就是查询的内容与创建的索引顺序,长度一一对应.
--
对于key_len:表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度.
在不损失精确性的情况下,长度越短越好
key_len显示的值为索引字段的最大可能长度,并非实际使用长度,
即key_len是根据表定义计算而得,不是通过表内检索出的.
--
对于ref:显示索引的哪一列被使用了,如果可能的话,是一个常数.
哪些列或常量被用域查找索引列上的值
--
对于rows:根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数
--
对于extra:包含不适合在其他列中显示但十分重要的额外信息
1.Using filesort 说明mysql会对数据使用一个外部的索引排序,
而不是按照表内的索引顺序去进行读取
Mysql中无法利用索引完成的排序操作称为"文件排序".
例:假如我创建了一个复合索引,用到了列1.列2.列3三个字段
select * from 表 where 列1='sb' group by 列3; 这样在extra中会出现Using filesort
因为使用到了复合索引但是列2没有用到.没有按照1.2.3的顺序去走
假如order by 列2,列3.则不会有相关内容
2.Using temporary 使用了临时表保存中间结果,Mysql在对查询结果排序是使用临时表.
常见于排序order by 和分组查询group by.
3.Using index 表示相应的selct操作中使用了覆盖索引(Covering Index),避免访问了表的数据行,效率不错!
如果同时出现using where,表名索引被用来执行索引键值的查找;
如果没有同时出现using where,表名索引用来读取数据而非执行查找动作.
至于什么是覆盖索引:查询列要被所建的索引覆盖.
4.Using where 表明使用了where过滤
5.Using join buffer 使用了连接缓存 可能是join使用过多
6.impossible where where子句的值总是false,不能用来获取任何元组
7.select tables optimized away 在group by子句下,基于索引优化min/max操作
或者对于MyISAM存储引擎优化count(*)操作,不必等到执行阶段再进行计算,
查询执行计划生成的阶段即完成优化.
8.distinct 优化distinct操作,再找到第一匹配的元组后即停止找同样值的动作
3:怎么用
用法就是EXPLAIN+SQL语句→打印执行计划包含的信息
