菜鸟谈——mysql之索引

刺骨的言语ヽ痛彻心扉 2024-02-18 17:51 26阅读 0赞

## 索引 ##

### 1. SQL慢，执行时间久，等待时间长 ###

1.1 SQL语句写的不好
     1.2 索引失效
         1.2.1 单值索引
         1.2.2 符合索引
     1.3 关联查询太多join
     1.4 服务器调优及各个参数设置（缓冲、线程数等）

### 2. 索引简介 ###

2.1 索引是什么
        排好序的快速查找数据结构
    2.2 索引的结构Btree简介
    
    2.3 索引的优势
        减少IO操作
        增强cpu执行效率
    2.4 索引的劣势
        对数据表的修改，需要更新表缓存，增加cpu压力
    2.5 索引的分类
        单值索引
        唯一索引
        复合索引
    2.6 索引的结构
        Btree索引  主要
        Hash索引
        全文索引
        Rtree索引
    2.7 什么时候使用索引
        主键字段自动增加唯一索引
        经常查询字段，添加索引
        外键字段，添加索引
        查询条件中，不经常使用的字段，不添加索引
        排序字段，添加索引
        统计分组字段，添加索引
        单值索引和复合索引  优先符合索引
    2.8 什么时候不使用索引
        表记录很少
        经常增删改的表
        如果某个数据列包含许多重复的内容，为它建立索引没有太大的实际意义

### 3. 性能分析 ###

3.1 性能慢的原因
        IO操作
        Cpu执行效率
        硬件方面的问题
    3.2 explain的使用
        3.2.1   能干嘛
            诊断sql语句，打印化验单
            表的读取顺序
            数据读取操作类型
            哪些索引可以使用
            哪些索引被实际使用
            表之间的引用
            每张表有多少行被优化器查询
        3.2.2   怎么玩
            Explain + sql语句
        3.2.3   什么作用
            可以模拟Mysql的optimizer执行循序，出sql语句的化验单
        3.2.4   字段分析
            3.2.4.1 ID(重要)
                相同，顺序执行
                不同，数字越大，越优先执行
                相同和不同同时存在，数字越大，越优先执行，相同顺序执行
            3.2.4.2 select_type
                Simple  简单查询
                Primary  主查询
                Subquery 子查询
                Derived  临时表
                Union   联合查询
                Union Result 联合查询的结果
            3.2.4.3 table
                表名，如果起了别名，就是别名
            3.2.4.4 type
                性能排序：system>const>eq_ref>ref>range>index>all
                一般来说知识查询到range级别，最好是ref级别
                3.2.4.4.1 system
                    系统表，并且只有一条数据，这是const类型的特例，平时不会出现
                3.2.4.4.2 const
                    表示通过索引一次就找到了，const用于比较primary key和唯一索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，mysql就能将该查询转换为一个常量。
                3.2.4.4.3 eq_ref
                    唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。
                    常见主键或唯一索引扫描
    
                3.2.4.4.4 ref
                    非唯一索引扫描，返回匹配的某个单个单独值得所有行
                    本质上是一种索引访问，它返回所有匹配某个单独值得行，然而，他可能会找到多个符合条件的行，所以应该属于查找和扫描的混合体

![这里写图片描述][SouthEast]

3.2.4.4.5 range
                    使用的范围
                    Where语句中出现 between ，>， <， in的查询
                3.2.4.4.6 index
                    Full index Scan ，index和all的区别为index类型只遍历索引树，这通常比all快，索引文件通常比数据文件小。
                    （也就是说虽然index和all都是读全表，但index是从索引中读取，而all是从硬盘中读取）
    
                3.2.4.4.7 all
                    Full table Scan，将遍历全表以找到匹配的行
            3.2.4.5 possible_keys
                可能使用的索引
                显示可能应用在这张表中的索引，一个或多个
                查询涉及到的字段上若存在索引，则索引将被列出，但不一定被查询实际使用
            3.2.4.6 key
                实际使用的索引，如果为null，则没有使用索引
                查询中若使用了覆盖索引，则该索引仅出现在key列表中
            3.2.4.7 key_len
                查询中使用的索引的长度。在不损失精度的情况下，长度越短越好。
            3.2.4.8 ref
                显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值
            3.2.4.9 row
                根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数
            3.2.4.10 extra  
                3.2.4.10.1  USING filesort
                3.2.4.10.2  Using temporary
                3.2.4.10.3  Using index
                3.2.4.10.4  Using where
                3.2.4.10.5  Using join
        3.2.5   热身小case

EXPLAIN SELECT d1.name ,(select id from t3) d2 
    from (select id,name from t1 where other_column='') d1
    union
    (select name,id from t2)

![这里写图片描述][SouthEast 1]

1） 第四行 id为4，select\_type为union，说明select是union里的第二个select，最先执行【select name,id from t2】  
2） 第二行 id为3，是整个查询中第三个select的一部分。因查询包含在from中，所以为derived。【select id,name from t1 where other\_column=”】  
3） 第三行 id为2，select\_type为subquery，为整个查询中第二个select。【select id from t3】  
4） 第一行 id为1，表示在union里面的第一个select，select\_type表示为外层查询，table被标记为，表示查询的结果来自一个衍生表，其中derived3中3代表该查询衍生自第三个select查询，即ID为3的select。【SELECT d1.name ,……】  
5） 第5行，id为null 代表从union的临时表中读取行的阶段，table列的

### 4. 索引优化 ###

4.1 索引分析
        4.1.1 单表
                Select * from t1 where name = ‘hua’ and age > 12 and email=’358824@11.com’
                不要建立三个字段的复合索引，应该建name和email字段的复合索引
    
        4.1.2 两表
                Left连接，索引加右表
                Right连接，索引加左表   
                特殊情况，可以互换两个表名，进行查询
        4.1.3 三表
                基本和两表的规则一样
    4.2 索引失     
        1)  全值匹配我最爱
        2)  最做前缀    法则
        3)  不在索引列上做任何（计算，函数，（自动或者手动）类型转换），会导致索引失效而转向全表扫描。
        4)  存储引擎不能使用索引中范围条件右边的列？？？？
        5)  尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *
        6)  Mysql在使用不等于（！=或者<>）的时候，无法使用索引会导致全表扫描
        7)  Is null,is not null也无法使用索引
        8)  Like以通配符开头（“%abc”）mysql索引失效转为全表扫描
        9)  字符串不加单引号索引失效
        10) 少用or，用它来连接时，索引会失效
    口诀      
        带头大哥不能死
        中间兄弟不能断
        索引列上无计算
        Like查询放右边
        少用or加引号

### 一般性建议 ###

对于单键索引，尽量选择针对当前query过滤性更好的索引  
在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。  
在选择组合索引的时候，尽量选择可以能够包含当前query中where字句中更多字段的索引  
尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

[SouthEast]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/c1810e78e83c48c8a23bd7e823a5c25c.png
[SouthEast 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/ca78f37f7321453bb2275dc8f5dd1e7e.png