首页 > 数据库技术 > 详细

MYSQL笔记-索引

时间:2019-11-25 13:49:04      阅读:80      评论:0      收藏:0      [点我收藏+]
1.索引
作用:
- 约束
- 加速查找
索引分类(约束):
- 普通索引:加速查找
- 主键索引:加速查找 + 不能为空 + 不能重复
- 唯一索引:加速查找 + 不能重复
- 联合索引(多列) :
- 联合主键索引
- 联合唯一索引
- 联合普通索引

索引分类(加速查找):
hash索引:
索引表存储(哈希值的顺序和数据表中的数据顺序不相同):
1.所有索引列数据转换成的哈希值
2.索引列数据的内存地址
hash索引优点:
查找单个值快
查找范围值慢
btree索引:二叉树(innodb)
二叉树存储:
先将索引列数据转化成数字,每个二叉树节点存储一个数字及索引列数据的地址,
二叉树节点的数字与子节点的数字有顺序关系(左子节点数字小于父节点,有子节点数字大于父节点)
hash索引优点:
查找单个值慢
查找范围值快

建立索引:
        - a.额外的文件保存特殊的数据结构
        - b.查询加快,插入更新删除相对也会变慢
        - c.命中索引(此处email列建立了索引)
            select * from userinfo where email = asdf;
            # 由于未命中索引,查询速度慢
            select * from userinfo where email like asdf;
        普通索引:
        - create index 索引名称 on 表名(列名)
        - drop index 索引名称 on 表名
        唯一索引:
        - create unique index 索引名称 on 表名(列名)
        - drop unique index 索引名称 on 表名
        组合(唯一)索引:
        - create unique index 索引名称 on 表名(列名,列名)
        - drop index 索引名称 on 表名
        - create unique index ix on userinfo3(name,email);
        - 最左前缀匹配(组合索引(name,email))
            select * from userinfo3 where name=alex;
            select * from userinfo3 where name=alex and email=asdfg;
            # 无法使用索引,组合索引会优先匹配组合中左边的列
            select * from userinfo3 where email=asdfg;
            组合索引效率>索引合并
        名词:
            覆盖索引:
                - 在索引文件中直接获取数据(此处sid是索引)
                select sid from userinfo where sid = 5;
            索引合并:
                - 把多个单列索引合并使用,对多列单独建立索引一起使用
                select * from userinfo3 where name=alex and email=asdfg;

        部分索引(text类的使用短索引):
            create index ix__ on tb1(title(15)) 表示对title列前15个字符进行索引


2.不能命中索引情况:
        - like %xx
            # name是索引,但是使用like和通配符进行查找,不能命中索引
            select * from tb1 where name like %cn;
        - 使用函数
            # email是索引,但是使用reverse函数后不会命中索引
            select * from tb1 where reverse(email) = alex155@163.com;
        - or(仅当or中没有建立索引的列才会不使用索引)
            # nid是索引 name不是索引 email是索引
            select * from tb1 where nid = 1 or name = alex;
            # 以下会走索引
            select * from tb1 where nid = 1 or email = seven@live.com and name = alex
        - 类型不一致(主键除外)
            # nid int类型,name是varcha类型
            select * from tb1 where nid = 1 or name = 888;
        - !=(主键除外)
            # name是索引,使用!=不能命中索引
            select * from tb1 where name != alex;
        - >(主键和整数类型除外)
            # name是索引,使用>不能命中索引
            select * from tb1 where name >alex;
        - order by(主键除外)
            # 当根据索引排序时候,选择的映射如果不是索引,则不走索引
            select name from tb1 order by email desc;


3.其他注意事项(不同的数据库情况不同)
- 避免使用select *
- count(1)或count(列) 代替 count(*)
- 创建表时尽量时 char 代替 varchar
- 表的字段顺序固定长度的字段优先
- 组合索引代替多个单列索引(经常使用多个条件查询时)
- 尽量使用短索引
- 使用连接(JOIN)来代替子查询(Sub-Queries)
- 连表时注意条件类型需一致
- 索引散列值(重复少)不适合建索引,例:性别不适合

4.时间
执行计划(explain):让mysql预估执行操作
查询时的访问方式:
性能:all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const

    慢:
        explain select * from userinfo3 where name = alex
        type = all(全表扫描)
    快:
        explain select * from userinfo3 where email = alex
        type = ref(索引查找)


5.DBA工作
慢日志
- 执行时间 >10
- 未命中索引
- 日志文件路径

配置
- 在内存中设置
 # 查询变量
            show variables like %query%
            show variables like %queries%
            # 打开慢查询日志
            set global slow_query_log = ON
            # 记录没有使用索引的SQL
            set global log_queries_not_using_indexes = ON
       # 设置慢日志文件存储地址
       set slow_query_log_file = D:/....
 
        - 在配置文件中设置(启动客户达端时可以指定配置文件的地址)
注意:修改配置文件之后,需要重启服务
mysqld --defaults-file = ‘D:\my.conf‘
            my.conf中添加内容:
          # 打开慢查询日志   slow_query_log
= ON

          # 记录没有使用索引的SQL
         
set global log_queries_not_using_indexes = ON
          # 设置慢日志文件存储地址
         slow_query_log_file
= D:/....


6.分页(基于数据库实现类似网页中的分页功能)
问题:同时对大量的数据进行分页会浪费很多时间
方案一:只给用户看到若干页面
缺点:这样不能在根本上解决问题
方案二:索引表
    缺点:
在索引中扫描比在数据表中速度快,但是实际上不能加快很多,本质上还得扫描200000条数据
 select * from userinfo3 where id in(select id from userinfo3 limit 200000,10);

方案三:记录当前页
(id不一定连续,所以无法直接使用id范围进行查找)
        1.页面只有上一页,下一页
            # max_id 当前页面的最大id
            # min_id 当前页面的最小id
            下一页
            select * from userinfo3 where id > max_id limit 10;
            上一页
            select * from userinfo3 where id < max_id  order by desc limit 10;
        2.上一页 192 193 196 197 198 199 x下一页
            # 当前处于193页,需要跳到196页
            select * from userinfo3 where id in (
            select id from (select * from userinfo3 where id > max_id limit 30) as N order by N.id desc limit 10;
            )

 

MYSQL笔记-索引

原文:https://www.cnblogs.com/lovejjy/p/11927014.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!