数据库高级之索引优化策略

本片文章以MySQL数据库为例,来阐述数据库中的高级知识点,不同关系型数据库有着不同的优化策略.

数据库之索引优化分析

什么是索引

    在关系型数据库中,索引是一种单独的,物理的对数据库表的一列或者多列进行排序或者排序存储的数据结构,它是表中一列或多列值的集合和响应的指针表表里物理标识这些值的数据页的逻辑指针清单.(简而言之,就像书的目录,可以根据目录进行检索对于的页码值).对于索引,就是提供在数据之外,有着快速查找的算法的数据结构,以某种方式指引着数据内容.
    下面这张图就是数据对应列的索引二叉树。我们平常说的索引，如果没有特别指定都是值b树。

那么什么情况下该建立索引，什么情况下不可以建立索引呢

建立索引	不可建立
主键自动建立	表记录太少，没意义
外键关系建立索引	频繁修改的列
频繁使用的列	列中重复率高
查询统计分组的字段	where条件用不到的

    性能下降，执行时间长，等待时间长，SQL语句写的太烂，索引失效，关联查询太多，使用join太多，这个时候就要考虑我们是否用了索引，或者操作问题导致索引失效。

索引分类

    单值索引： 一个索引字包含以个字段，一个表可以有多个单值索引（index<=5）
    唯一索引：unique 索引列的值必须唯一，无重复，可以有空值
    复合索引：一个索引可以作用在多个字段
    基本语法
        

CREATE INDEX indexName on a(locadd); 建立索引
ALTER TABLE a add INDEX indexNAME(locadd)
ALTER TABLE a ADD UNIQUE indexNAME(locadd)
ALTER TABLE a ADD PRIMARY KEY indexNAME(locadd)
ALTER TABLE a ADD FULLTEXT indexNAME(locadd)
SHOW INDEX FROM a查看索引
DROP INDEX indexName on TABLE_NAME删除索引

如果确定索引使用情况

    explain SQL

    如果id相同，按照顺序执行
        select_type:代表查询类型
        type访问类型排序，代表这条SQL的好坏
        possible_key理论上用的索引
        key：实际上用到的索引
        ref被用的索引
        row： 根据表统计信息及索引选用情况，大致估算找到记录需要执行的行数
        extra：关于MYSQL如何解析查询的额外信息，坏例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢
下面详细讲解type特点

type类型	特点
system	const的特例，系统表，只有一行记录
const	通过索引一次就找到记录，一般为primary key ，unique 索引，常量，读一行
eq_ref	唯一性索引扫描，只有一条记录匹配，但是会扫描整个索引
ref	非唯一性索引扫描，返回匹配记录的所有行，属于查找和扫描的混合
range	只检索给定范围的行，一般在where条件后使用between ， in ， <，>触发，比全盘扫描效率高，因为只要扫描索引一段
index	full index scan，只遍历索引树的全盘扫描
all	读磁盘的全盘扫描，效率最低，IO次数决定时长

    关于extra ：
        代表对于索引使用情况的解释，对中间数据库操作的记录
        using filesort：使用外部索引，自己建立索引失效
        using tempoary:使用了临时表保存中间结果，安全性降低
        using where：有回表参数
        using index： 使用了我们建立的索引

下面就是针对案例解释

    两表关联：左右连接
左连接：索引加载右表，效率高
右连接：索引加载左表，效率高

    三表关联


        两表 加了索引后的效果，可以看见使用了索引
    小表驱动大表
        小的数据表驱动大的数据表

分析：不管大表是驱动表还是小表是驱动表，比较次数永远是一样

              想了很久

              小表驱动大表优势在哪？？？

              优势在于：			 1. 大表具有索引：查询大表时间是O(Log n)

                                 2. 大表全表扫描：磁盘块查询速度快

    为排序使用order by
        order by 尽量使用useing index,避免using filesort
我们在使用复合索引的时候，order by 操作一定要让复合索引大哥在前面，否则就会出现filesort ，另外一种情况，就是降序排序 ，因为order by 默认是升序。

Oder by 情况	方法
第一种	使用索引最佳左前缀原则
第二种	where 和order by 混合使用，满足索引最左前列

如何避免索引失效

方法
全值匹配
最佳左前缀
不在索引列上做任何操作（计算，函数，or类型转换，会导致索引失效）
减少select * 尽量使用覆盖索引查询
使用 ！= ， <，>，导致全盘扫描
is null ，is not null无法使用索引
like以通配符开头会使索引失效（%abc…）
字符串不加单引号 ，索引失效
少用or，用or连接条件时会索引失效

    
    

下篇关于数据库高级之锁策略