MySql之索引

一.索引

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

 二 索引使用

1、主键索引

create table in1(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    index ix_name (name)
)

OR

create table in1(
    nid int not null auto_increment,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    primary key(ni1),
    index ix_name (name)
)

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

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

2.普通索引

create table in1(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    index ix_name (name)
)

create index index_name on table_name(column_name)

create index index_name on table_name(column_name)

show index from table_name;

注意：对于创建索引时如果是BLOB 和 TEXT 类型，必须指定length。

create index ix_extra on in1(extra(32));

3.唯一索引

create table in1(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    unique ix_name (name)
)

create unique index 索引名 on 表名(列名)

drop unique index 索引名 on 表名

4.组合索引

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

其应用场景为：频繁的同时使用n列来进行查询，如：where n1 = 'alex' and n2 = 666。

create table in3(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text
)

create index ix_name_email on in3(name,email);

如上创建组合索引之后，查询：

• name and email  -- 使用索引
• name                 -- 使用索引
• email                 -- 不使用索引

注意：对于同时搜索n个条件时，组合索引的性能好于多个单一索引合并。

主键索引：            
普通索引：
    - create index 索引名称 on 表名(列名,)
    - drop index 索引名称 on 表名
唯一索引：
    - create unique index 索引名称 on 表名(列名)
    - drop unique index 索引名称 on 表名

组合索引(最左前缀匹配)：
    - create  index 索引名称 on 表名(列名,列名)
    - drop    index 索引名称 on 表名
    
    - create index ix_name_email on userinfo3(name,email,)
    - 最左前缀匹配
            select  * from userinfo3 where name='Tom';
            select  * from userinfo3 where name='Tom' and email='asdf';
            
            select  * from userinfo3 where email='Tom@qq.com';
        
    组合索引效率 > 索引合并 
        组合索引
            - (name,email,)
                select  * from userinfo3 where name='Tom' and email='asdf';
                select  * from userinfo3 where name='Tom';
        索引合并：
            - name
            - email
                select  * from userinfo3 where name='Tom' and email='asdf';
                select  * from userinfo3 where name='Tom';
                select  * from userinfo3 where email='Tom';

名词：
    覆盖索引：
        - 在索引文件中直接获取数据
    
    索引合并：
        - 把多个单列索引合并使用

三. 正确使用索引

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

#--- id,email 是创建的索引
            
    - like '%xx'
        select * from tb1 where email like '%cn';
        
        
    - 使用函数
        select * from tb1 where reverse(email) = 'Tom';
        
        
    - or
        select * from tb1 where nid = 1 or name = 'seven@live.com';  # name不是 索引字段，无法命中
        
        
        特别的：当or条件中有未建立索引的列才失效，以下会走索引

                select * from tb1 where id = 1 or name = 'seven@live.com' and email = 'Tom'   #会忽略 name
                
                
    - 类型不一致
        如果列是字符串类型，传入条件是必须用引号引起来，不然...
        select * from tb1 where email = 999;
        
        
    - !=
        select * from tb1 where email != 'Tom'
        
        特别的：如果是主键，则还是会走索引
            select * from tb1 where nid != 123
    - >
        select * from tb1 where email > 'Tom'
        
        
        特别的：如果是主键或索引是整数类型，则还是会走索引
            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                -- 不使用索引

 四.其他注意事项

- 避免使用select *
- count(1)或count(列) 代替 count(*)
- 创建表时尽量时 char 代替 varchar
- 索引散列值（重复少）不适合建索引，例：性别不适合

- 表的字段顺序固定长度的字段优先
- 组合索引代替多个单列索引（经常使用多个条件查询时）
- 尽量使用短索引
- 使用连接（JOIN）来代替子查询(Sub-Queries)
- 连表时注意条件类型需一致

五. limit分页

- 索引表中扫：(依然不快)
        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
            )

六. 执行计划

explain + 查询SQL - 让mysql预估执行操作(一般正确)

mysql> explain select * from tb2;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | tb2   | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
    #-- id,email 是索引
    
    慢：
        select * from userinfo3 where name='Tom'
        
        explain select * from userinfo3 where name='Tom'
        type: ALL(全表扫描)
            select * from userinfo3 limit 1; (依然快--因为特殊情况)
    快：
        select * from userinfo3 where email='Tom'
        type: const(走索引)