mysql索引

  1. 什么是索引

    索引是什么，索引相当于字典里面的目录序表，比如查询一个“星”字，如果不按照拼音来找的话，那么我们需要把整个字典全部遍历查询一遍。才能查到这个字，如果按照拼音来找的，那么只需要在几页音序表中查询。就可以通过音序就快速查到，这个字在字典的哪一页。
    在数据库中，索引是建立在表上面的，索引可以很大程度上提高数据库的查询，同时也提高了数据库的性能，不同的存储引擎定义了索引的最大长度和所索引的数量，所有的存储殷勤对每个表最少支持16个索引，索引的长度最少支持位256字节。

    2.优缺点

    索引优点：
    其优点可以提高数据的检索速度，针对于有依赖关系的子表和父表，在联合查询的时候可以提高查询速度。

    索引缺点：
    创建和维护索引需要消耗时间，索引需要占用物理空间，每一个索引都需要占用一定的物理空间，大量的索引会影响插入数据，数据库系统会按照索引进行排序，这样降低了插入数据的速度
    解决办法：在插入数据时，先临时删除表的索引，然后插入数据，数据插入完成后，再创建索引。

    3.索引的种类(算法)

    • B树索引
    • Hash索引
    • R树
    • Full text
    • GIS

    4. 功能上的分类

    辅助索引

    • 1.普通的单列辅助索引

    • 2.联合索引

      多个列作为索引条件,生成索引树,理论上设计的好的,可以减少大量的回表 查询

    • 3.唯一索引
      索引列的值都是唯一的.

    聚集索引

    • (1)表中设置了主键,主键列就会自动被作为聚集索引.
    • (2)如果没有主键,会选择唯一键作为聚集索引.
    • (3)聚集索引必须在建表时才有意义,一般是表的无关列(ID)

    区别

    • 聚集索引只能有一个,非空唯一,一般时主键
    • 辅助索引,可以有多个,时配合聚集索引使用的
    • 聚集索引叶子节点,就是磁盘的数据行存储的数据页

    MySQL是根据聚集索引,组织存储数据,数据存储时就是按照聚集索引的顺序进行存储数据
    + 辅助索引,只会提取索引键值,进行自动排序生成B树结构

    索引高度影响

    1. 数据量级, 解决方法:分表,分库,分布式
2. 索引列值过长 , 解决方法:前缀索引
3. 数据类型:
变长长度字符串,使用了char,解决方案:变长字符串使用varchar

    5.索引建立

    1查看表结构

    desc table;

    Field :列名字
key  :有没有索引,索引类型
PRI: 主键索引
UNI: 唯一索引
MUL: 辅助索引(单列,联和,前缀)

    2.创建索引

    alter table table_name add index index_name(field);

    3.查看已建立的索引

    show index from table_name;

    4.删除索引

    alter table table_name drop index index_name;

    5.创建联合索引

    alter table table_name add index index_name(field1,field2,.....);

    6.创建前缀索引

    数字列不能创建前缀索引 ，5代表长度

    alter table table_name add index index_name(field(5));

    7. 创建唯一索引

    alter table table_name add unique index index_name(field);

    6.索引执行计划

    desc 后面跟需要查询的语句

    重点关注的地方

    table:                                 ---->查询操作的表    **
possible_keys:                         ---->可能会走的索引  **
key:                                   ---->真正走的索引    ***
type:                                  ---->索引类型        *****
Extra:                                 ---->额外信息        *****

    type详解

    • ALL：表示全表扫描 不走索引

    这种情况一般是没有创建索引或者辅助索引列查询语句中出现<> not in like 等字样

    • INDEX： 全索引扫描

    联合索引中 任何一个非最左列作为查询条件时

    index_name(a,b,c) —-> a ab abc

    select * from table where b

    select * from table where c

    或者查询需要获取整个索引树的值时

    • RANG: 索引范围扫描

      辅助索引使用以下语句会触发 > < >= <= like in or

      主键索引使用以下语句触发 包含辅助索引用到的 <> not in

    • REF： 非唯一性索引 等值查询

    • EQ_REF ： 多表连接时 连接条件使用了唯一索引（uk pk）

    • system ,const 唯一索引的等值查询

    extra字段解释

    filesort 文件排序

    • 1.当我们看到执行计划extra位置出现filesort,说明由文件排序出现
    • 2.观察需要排序(ORDER BY,GROUP BY ,DISTINCT )的条件,有没有索引
    • 3.根据子句的执行顺序,去创建联合索引

    desc 使用场景

    题目意思: 我们公司业务慢,请你从数据库的角度分析原因

    mysql出现性能问题,我总结有两种情况:
    + 应急性的慢：突然夯住

    应急情况:数据库hang(卡了,资源耗尽)

    处理过程:
    - 1.show processlist; 获取到导致数据库hang的语句
    - 2.explain desc 分析SQL的执行计划,有没有走索引,索引的类型情况
    - 3.建索引,改语句

    •   一段时间慢(持续性的):
   +	(1)记录慢日志slowlog,分析slowlog
   +	(2)explain 分析SQL的执行计划,有没有走索引,索引的类型情况
   +	(3)建索引,改语句

    不走索引的可能原因

    • 1.没有查询条件或者查询条件没有索引
    • 2.查询结果数据太大 超过25% 建议使用limit
    • 3.索引失效，如内容频繁改动可能会产生 建议删除重建
    • 4.查询时索引运行了运算 比如 where id-1=10
    • 5.隐式转换导致
    • 6.select * from student where tel=131xxxx 不走索引 这个会转换字符串去比较
    • 7.select * from student where tel=’131xxxx’ 走索引
    • 8.like %放前面不走索引
    • 9.<> not in 不走索引
Mysql
#Mysql
mysql索引
http://www.jcwit.com/article/29/
作者
Carlos
发布于
2019年4月2日
许可协议