索引

一,索引的介绍

数据库中专门用于帮助用户快速查找数据的一种说数据结构,类似于字典中的目录,查找字典内容时可以根据目录查找到数据的存放位置,然后直接获取

二,索引的的作用

约束加速查找

三,常见的几种索引:

- 普通索引
- 唯一索引
- 主键索引
- 联合索引
    联合主键索引
    联合唯一索引
    联合普通索引

无索引: 从前往后一条一条查询
有索引: 创建索引的本质,就是创建额外的文件(某种格式存储,查询的时候,先去格外的文件找,定好位置,然后再去源市表中直接查找,大师创建的索引越多,会对硬盘也是有损耗,
建立索引的目的;
a, 额外的文件保存特殊的数据结构
b, 查询快,但是插入更新删除依然慢
c, 创建索引之后,必然命中索引才能有效

hash索引和Btree索引
(1), hash类型的索引: 查询单条块,范围查询慢
(2), btree类型的索引: b+树,层数越多,数据量指数级增长(我们就用它,因为innodb默认支持它)

普通索引

作用: 仅有一个加速查找

create table userinfo(
                   nid int not null auto_increment primary key,
                   name varchar(32) not null,
                   email varchar(64) not null,
                   index ix_name(name)
               );

create index 索引的名字 on 表名 (列明)

drop index 索引的名字 on 表名

show index from 表名

唯一索引

唯一索引有两个功能: 加速查找和唯一约束(可含null)

 create table userinfo(
                   id int not null auto_increment primary key,
                   name varchar(32) not null,
                   email varchar(64) not null,
                   unique  index  ix_name(name)
               );

create unipue index 索引名 on 表名(列明)

drop index索引名 on 表名;

主键索引

主键索引有两个功能: 加速查找和唯一约束(不含null)

alter table 表名 add primary key(列名);

alter table 表名 drop primary key;
alter table 表名 modify 列名 int, drop, primary key;

组合索引

组合索引时将n个列组合成一个索引

其应用场景为: 频繁的同时使用n列来进行查询,如: where name = 'alex' 俺的Email = 'alex@qq.com".

create index  索引名 on 表名(列名1, 列名2);

索引名词

覆盖索引; 在索引文件中直接获取数据
例如:
    select name from userinfo where name = 'chenrun123';


索引合并: 把多个单列索引合并成使用
    例如:
    select * from userinfo where name = 'chenrun123' and if = 12345;

正确使用索引的情况

数据库表中添加索引后确实会让查询速度起飞，但前提必须是正确的使用索引来查询，如果以错误的方式使用，则即使建立索引也会不奏效。

使用索引,我们必须知道

　　(1), 创建索引

　　(2), 命中索引

　　(3), 正确使用索引

复制代码
　　　　 - like '%xx'
            select * from userinfo where name like '%al';
        - 使用函数
            select * from userinfo where reverse(name) = 'alex333';
        - or
            select * from userinfo where id = 1 or email = 'alex122@oldbody';
            特别的：当or条件中有未建立索引的列才失效，以下会走索引
                    select * from userinfo where id = 1 or name = 'alex1222';
                    select * from userinfo where id = 1 or email = 'alex122@oldbody' and name = 'alex112'
        - 类型不一致
            如果列是字符串类型，传入条件是必须用引号引起来，不然...
            select * from userinfo where name = 999;
        - !=
            select count(*) from userinfo where name != 'alex'
            特别的：如果是主键，则还是会走索引
                select count(*) from userinfo where id != 123
        - >
            select * from userinfo where name > 'alex'
            特别的：如果是主键或索引是整数类型，则还是会走索引
                select * from userinfo where id > 123
                select * from userinfo where num > 123
        - order by
            select email from userinfo order by name desc;
            当根据索引排序时候，选择的映射如果不是索引，则不走索引
            特别的：如果对主键排序，则还是走索引：
                select * from userinfo order by nid desc;
         
        - 组合索引最左前缀
            如果组合索引为：(name,email)
            name and email       -- 使用索引
            name                 -- 使用索引
            email                -- 不使用索引

索引的注意事项

(1)避免使用select * 
(2)count(1)或count(列)代替count(*)
(3)创建表时尽量使用char代替varchar
(4)表的字段顺序固定长度的字段优先
(5)组合索引代替多个单列索引(经常使用多个条件查询时)
(6)尽量使用短索引(create index ix_title on tb(title(16)); 特殊的数据类型text类型)
(7)使用连接(join)来代替字查询
(8)连表时注意条件类型需一致
(9)索引散列(重复少)不适用于键索引,例如: 性别不合适

执行计划

explain + 查询sql 用于显示sql执行信息参数,根据参考信息可以进行sql优化

 

慢日志记录

开启慢查询日志，可以让MySQL记录下查询超过指定时间的语句，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。

(1) 进入MySql 查询是否开了慢查询
         show variables like 'slow_query%';
         参数解释：
             slow_query_log 慢查询开启状态  OFF 未开启 ON 为开启
        slow_query_log_file 慢查询日志存放的位置（这个目录需要MySQL的运行帐号的可写权限，一般设置为MySQL的数据存放目录）

（2）查看慢查询超时时间
       show variables like 'long%';
       ong_query_time 查询超过多少秒才记录   默认10秒 

（3）开启慢日志（1）（是否开启慢查询日志，1表示开启，0表示关闭。）
           set global slow_query_log=1;
（4）再次查看
              show variables like '%slow_query_log%';

（5）开启慢日志（2）：（推荐）
         在my.cnf 文件中
         找到[mysqld]下面添加：
           slow_query_log =1
   　　　　 slow_query_log_file=C:\mysql-5.6.40-winx64\data\localhost-slow.log
    　　　  long_query_time = 1

    参数说明：
        slow_query_log 慢查询开启状态  1 为开启
        slow_query_log_file 慢查询日志存放的位置
        long_query_time 查询超过多少秒才记录   默认10秒 修改为1秒

分页性能相关方案

第1页：
select * from userinfo limit 0,10;
第2页：
select * from userinfo limit 10,10;
第3页：
select * from userinfo limit 20,10;
第4页：
select * from userinfo limit 30,10;
......
第2000010页
select * from userinfo limit 2000000,10;

PS:会发现，越往后查询，需要的时间约长，是因为越往后查，全文扫描查询，会去数据表中扫描查询。

最优的解决方案

（1）只有上一页和下一页
        做一个记录：记录当前页的最大id或最小id
        下一页：
        select * from userinfo where id>max_id limit 10;

        上一页：
        select * from userinfo where id<min_id order by id desc limit 10;


  (2) 中间有页码的情况
           select * from userinfo where id in(
               select id from (select * from userinfo where id > pre_max_id limit (cur_max_id-pre_max_id)*10) as A order by A.id desc limit 10
           );