MySQL之索引

索引的分类:
    1. 索引
         作用:
            - 约束
            - 加速查找
         索引:
            - 主键索引:加速查找 + 不能为空 + 不能重复
            - 普通索引:加速查找
            - 唯一索引:加速查找 + 不能重复
            - 联合索引(多列):
                - 联合主键索引
                - 联合唯一索引
                - 联合普通索引
            
         加速查找:
            快:
                select * from tb where name='asdf';
                select * from tb where id=999;
            假设:
                id  name  email
                 ...
                 ...
                 ...
                    
                无索引:从前到后依次查找
                    索引:
                      id       创建额外文件(某种格式存储)
                      name     创建额外文件(某种格式存储)
                      email     创建额外文件(某种格式存储)  
                          create index ix_name on userinfo3(email); 创建非主键索引文件
                      name  email 创建额外文件(某种格式存储)
                        
                索引种类(某种格式存储):
                    hash索引: 
                        单值快
                        范围小
                    btree索引: 
                        二叉树
                        
                ========》 结果:快 《========
                建立索引:
                    - a. 额外的文件保存特殊的数据结构、
                    - b. 查询快;插入更新删除慢
                    - c. 命中索引
                            
                            select * from userinfo3 where email= 'asdf' ;
                            select * from userinfo3 where email like 'asdf'; 查询速度慢
                            ...
                            
                    主键索引:id auto_increment primary key;
                        
                    普通索引:
                        - create index 索引名称 on 表名(列名,);  创建
                        - drop index 索引名称 on 表名;           删除
                        
                    唯一索引:
                        - create unique index 索引名称 on 表名(列名,);  创建
                        - drop unique index 索引名称 on 表名;          删除
                    
                    组合索引(最左前缀匹配):
                        - create unique index 索引名称 on 表名(列名,列名);
                        - drop unique index 索引名称 on 表名;
                        
                        - create index ix_name_email on userinfo3(name,email,);
                        - 最左前缀匹配
                                select * from userinfo3 where name='alex';
                                select * from userinfo3 where name='alex' and email='asdf';
                                select * from userinfo3 where email='alex@qq.com';
                            
                        组合索引效率 > 索引合并  *****
                            组合索引
                                - (name,email,)
                                    select * from userinfo3 where name='alex' and email='asdf'; 
                                    select * from userinfo3 where name='alex';
                            索引合并:
                                - name
                                - email
                                    select * from userinfo3 where name='alex' and email='asdf';
                                    select * from userinfo3 where name='alex';
                                    select * from userinfo3 where email='alex';
                    
                    名词:
                        覆盖索引:
                            - 在索引文件中直接获取数据
                        
                        索引合并:
                            - 把多个单列索引合并使用
                
                
    2. 频繁查找的列创建索引
        - 创建索引
        - 命中索引  *****

            - like '%xx'
                select * from tb1 where email like '%cn';
                
            - 使用函数
                select * from tb1 where reverse(email) = 'wang';    reverse翻转
                
            - or
                select * from tb1 where nid = 1 or name = 'seven@live.com';
                
                特别的:当or条件中有未建立索引的列才失效,以下会走索引
                    select * from tb1 where nid = 1 or name = 'seven';
                    select * from tb1 where nid = 1 or name = 'seven@live.com' and email = 'alex';
                        
                        
            - 类型不一致
                如果列是"字符串"类型,传入条件是必须用引号引起来,不然...
                select * from tb1 where email = 999;
                
                
            - !=
                select * from tb1 where email != 'alex';
                
                特别的:如果是主键,则还是会走索引
                    select * from tb1 where nid != 123;
                    
            - ">"
                    select * from tb1 where email > 'alex';
                
                
                特别的:如果是主键或索引是整数类型,则还是会走索引
                    select * from tb1 where nid > 123;
                    select * from tb1 where num > 123;
                    
                    
            - order by
                select name from tb1 order by email desc;
                
                当根据索引排序时候,选择的映射如果不是索引,则不走索引
                特别的:如果对主键排序,则还是走索引:
                    select * from tb1 order by nid desc;
             
            - 组合索引最左前缀 *****
                如果组合索引为:(name,email)
                name and email       -- 使用索引
                name                 -- 使用索引
                email                -- 不使用索引
                
                
    3. 时间
           
           执行计划:让mysql预估执行操作(一般正确)
                all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const(走索引路线)
                id,email
                
                慢:
                    select * from userinfo3 where name='alex';
                    
                    explain select * from userinfo3 where name='alex';
                    type: ALL(全表扫描)
                        select * from userinfo3 limit 1;
                快:
                    select * from userinfo3 where email='alex';
                    type: const(走索引)
                
        4. DBA工作
        
            慢日志
                - 执行时间 > 10
                - 未命中索引
                - 日志文件路径
                
            配置:
                - 内存
                    show variables like '%query%'  显示内存数据
                    set global 变量名 = 值            重新设置内存中的 flag
                - 配置文件
                    mysqld --defaults-file='E:\wang\mysql-winx64\mysql-winx64\my-default.ini'
                    
                    my.conf内容:
                        slow_query_log = ON
                        slow_query_log_file = D:/....
                        
                    注意:修改配置文件之后,需要重启服务
                        
        5. ******分页*******
            
            a. select * from userinfo3 limit 20,10;
            b.
                - 不让看
                - 索引表中扫:
                    select * from userinfo3 where id in(select id from userinfo3 limit 200000,10);
                - 方案:
                    记录当前页最大或最小ID *****
                    1. 页面只有上一页,下一页
                        # max_id
                        # min_id
                        下一页:
                            select * from userinfo3 where id > max_id limit 10;
                        上一页:
                            select * from userinfo3 where id < min_id order by id desc limit 10;
                            
                    2. 上一页 192 193  [196]  197  198  199 下一页
                        
                        select * from userinfo3 where id in (
                            select id from (select id from userinfo3 where id > max_id limit 30) as N order by N.id desc limit 10
                        );
                    
                    

 

posted @ 2018-12-02 22:43  随性岁分享  阅读(14)  评论(0)    收藏  举报