mysql中索引相关及性能优化第一篇（基础+explain的字段解释）

第一部分 基础

一、索引的定义：排好序的快速查找数据结构（BTREE）

二、索引作用：1查找 2排序  具体表现：1提高检索效率，降低数据库io成本 ，2降低排序成本，降低CPU消耗

小tap：java层删除数据有时候是update标志位为删除状态，因为真正删除数据需要重新建索引

一般来说索引本身也很大，所以以文件的形式存放在磁盘上

三、索引创建、查看、删除

创建
第一种：create [unique] index indexname on tableName(columnName(length));
第二种：alter tablename add [unique] index [indexname] on (columnname(length));
删除：drop index [indexname] on tablename;
查看：show index from tablename

四、哪些时候需要建索引

1.主键自动建立唯一索引

2、频繁作为查询字段的列

3、查询中与其他表关联的字段

4、排序和分组可以使用到索引

五、哪些时候不需要建索引

1、表数据很少

2、经常增删改的表

3、数据重复且分布平均的字段

 

第二部分 性能分析

一、explain + sql语句，分析sql语句的必备技能

 

id （表的读取顺序）	select的查询序号，包含一组数字，表示查询中执行select子句或操作表的顺序 三种情况：1、id相同，执行顺序从上到下顺序进行                   2、id不相同，执行按从大到小执行                   3、有相同有不同，结合1/2总和顺序
select_type （数据读取的操作类型）	simple:简单的select查询，不包含子查询和union primary:查询中若包含复杂的子部分，则最外层是这个 subquery：子查询的一些查询 derived（衍生）：在from列表中包含子查询被标记成这个，mysql会递归执行这些子查询，吧结果放入临时表 union:若第二个select出现在union之后，会被标记为union union result：从union表获取结果的select
type (显示查询使用了何种类型)	从好到坏依次是：system》const》eq ref》ref》range》index》all system：表只有一行记录，平时不会出现，忽略不计 const：表示通过索引依次就找到了，主要见于where后面的列为主键索引列等（如主键，不同率是100%） eq ref：唯一性索引扫描，对于美国索引建，表中只有一条记录与之匹配（有不同和相同的，查的是唯一的那个） ref：（有不同和相同的，查的是相同的那些） range：检索范围，一般where中出现between或in的，比全表扫描快 index：遍历索引树，比如select id from t；id列建索引 all：不走索引
possible_keys (可能用到的索引)	显示可能用的索引，一个或多个，但不一定会被实际使用
key （实际用到的索引）	实际使用的索引，如果为null，那么没有用到索引 覆盖索引，理论上没用索引，但是实际中用到
key len	在不损失精度的情况下，长度越短越好
ref	显示哪一列的值被使用去查索引。哪些列或常量被用于查找索引列的值（如果显示const就是指常量）
rows	每张表实际使用索引查询了多少行
extra （额外信息）	using filesort：mysql进行了外部索引排序（mysql自己建了临时索引），没有用已建好的索引 using temporary： mysql建了临时表进行存储，完成排序和分组的查询 以上两个是负面影响 using index：查询用到了覆盖索引