012-MySQL 索引添加以及优化说明

一、索引概述

　　数据库的索引可以加快查询速度，原因是索引使用特定的数据结构(B-Tree)对特定的列额外组织存放,加快存储引擎(索引是存储引擎实现)查找记录的速度。

　　如果查询语句使用索引（通常是where条件匹配索引)就会利用树的结构加快查找，索引会按值查找到要查找的行在表中位置，不需回表查询数据的就是聚簇索引(索引和数据存放在一起)。通常是需要回表再查数据，需要消耗额外的磁盘IO。所以有些时候（如按顺序读取数据）全表扫描会比使用索引快的原因就在于此。

　　查询条件只有一个字段时，在该字段建立索引即可，可优化的地方是对于text blob字段使用前缀索引。

　　当查询条件有多个字段时，单列索引和多列索引有很大的区别。如果使用多列索引，where条件中字段的顺序非常重要，需要满足最左前缀列。最左前缀：查询条件中的所有字段需要从左边起按顺序出现在多列索引中，查询条件的字段数要小于等于多列索引的字段数，中间字段不能存在范围查询的字段(<,like等)，这样的sql可以使用该多列索引。

1.1、索引操作

1.2.1、增加索引

1.PRIMARY KEY（主键索引）

mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

2.UNIQUE(唯一索引)

mysql>ALTER TABLE `table_name` ADD UNIQUE (`column` )

3.INDEX(普通索引)

mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

4.FULLTEXT(全文索引)

mysql>ALTER TABLE `table_name` ADD FULLTEXT ( `column` )

5.多列索引
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

1.2.2、删除索引

　　alter table table_name drop index emp_name;

1.2.3、重建索引

mysql> REPAIR TABLE table_name QUICK;

1.2.4、查看某个数据表的索引

mysql> SHOW INDEX FROM tbl_name;

1.2.5、注意事项

1、创建报错：Specified key was too long; max key length is 767 bytes简单解决办法

数据库表采用utf8编码，其中varchar(255)的column进行了唯一键索引

而mysql默认情况下单个列的索引不能超过767位(不同版本可能存在差异)

方案：如果数据内容允许，直接修改数据类型：table_name 表里的column_name 字段原来长度是 150个字符，现长度要改成64个字符

　　alter table table_name modify column column_name varchar(64) COMMENT '注释' ;

1.2、列索引、多列索引

　　列索引就是单列创建一个索引。多列索引是多列创建一个索引。

1.2.1、多列索引适合的场景

　　1.全字段匹配

　　2.匹配部分最左前缀

　　3.匹配第一列

　　4.匹配第一列范围查询(可用用like a%,但不能使用like %b)

　　5.精确匹配某一列和和范围匹配另外一列

　　order by操作中出现的字段同样适用于按值查找的规则，where+order by中出现的字段需可以建立满足如上五种规则多列索引。使用多列所需需要按照最左索引列查找；不能跳过中间列；如果某一列是范围查询，那么其右边所有列无法使用索引。

　　IN什么情况下是范围查询，什么情况下是多个等值查询？如果有order by排序时，多个等于条件查询就是范围查询，没有order by排序就没有限制。

　　例如,建立多列索引(name, age, id),只能使用索引的前两列。in是范围查询

　　　　... where name='nginx.cn' and age in(15,16,17) order by id

　　可以使用整个索引，in是按值查询

　　　　... where name='nginx.cn' and age in(15,16,17) and id ='3'

1.2.2、复合索引的建立以及最左前缀原则

　　索引字符串值的前缀（prefixe）。如果你需要索引一个字符串数据列，那么最好在任何适当的情况下都应该指定前缀长度。

　　例如，如果有CHAR(200)数据列，如果前面10个或20个字符都不同，就不要索引整个数据列。索引前面10个或20个字符会节省大量的空间。你可以索引CHAR、VARCHAR、BINARY、VARBINARY、BLOB和TEXT数据列的前缀。

　　假设你在表的state、city和zip数据列上建立了复合索引。索引中的数据行按照state/city/zip次序排列，因此它们也会自动地按照state/city和state次序排列。这意味着，即使你在查询中只指定了state值，或者指定state和city值，MySQL也可以使用这个索引。因此，这个索引可以被用于搜索如下所示的数据列组合：

　　　　state, city, zip

　　　　state, city

　　　　state

　　MySQL不能利用这个索引来搜索没有包含在最左前缀的内容。例如，如果你按照city或zip来搜索，就不会使用到这个索引。如果你搜索给定的state和具体的ZIP代码（索引的1和3列），该索引也是不能用于这种组合值的，尽管MySQL可以利用索引来查找匹配的state从而缩小搜索的范围。

　　如果你考虑给已经索引过的表添加索引，那么就要考虑你将增加的索引是否是已有的多列索引的最左前缀。如果是这样的，不用增加索引，因为已经有了(例如，如果你在state、city和zip上建立了索引，那么没有必要再增加state的索引)。

二、实例

　　有大数据量的查询，基本查询太耗时，此时需要优化sql，索引是优化查询sql的一个点。

2.1、建表插入数据

　　参看009-MySQL循环while、repeat、loop使用、 010-MySQL批量插入测试数据

　　更具上述文章创建表结构，插入100W条数据。

drop table if exists `test_table_idx`;
create table `test_table_idx`(
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `uid` varchar(64) DEFAULT NULL COMMENT '字符主键',
  `name` varchar(64) DEFAULT NULL COMMENT '名称',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `type` varchar(64) DEFAULT NULL COMMENT '类型',
  `ext_info` text  COMMENT '扩展信息',
  `created_datetime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_datetime` datetime DEFAULT null COMMENT '更新时间',
  `desc` varchar(300) DEFAULT NULL COMMENT '描述',
  primary key (`id`)
) ENGINE=InnoDB charset=utf8 collate=utf8_bin;

View Code

　　说明：年龄 0-100；type 几类：0-20 少年 21-30 青年 31-50 中年 51-70 老年；

　　如果有测试的可以清除表数据：TRUNCATE table test_table_idx；

delimiter //  #定义标识符为双斜杠
DROP PROCEDURE IF EXISTS my_procedure ; #如果存在 my_procedure 存储过程则删除
CREATE PROCEDURE my_procedure () #创建无参存储过程
BEGIN
    DECLARE n INT DEFAULT 1 ; # 申明变量
    set @execSql='insert into test_table_idx (uid,name,age,type,ext_info,`desc`)  values';
    set @execdata = '';

    WHILE n <= 1000001 DO
        set @age=FLOOR(RAND() * 100);
        # 类型
        set @type='';
        if @age<20 THEN
            set @type='少年';
        elseif @age<30 THEN
            set @type='青年';
        elseif @age<50 THEN
            set @type='中年';
        elseif @age<70 THEN
            set @type='老年';
        ELSE
            set @type=concat('高龄',@age);
        end if;

        set @execdata=concat(@execdata,"(",n,",","'name",n,"',",@age,",'",@type,"',","NULL",",'desc'",")");

        if n%1000=0
        then
            set @execSql = concat(@execSql,@execdata,";");
--             select @execSql;
            prepare stmt from @execSql;
            execute stmt;
            DEALLOCATE prepare stmt;
            commit;

            set @execSql='insert into test_table_idx (uid,name,age,type,ext_info,`desc`)  values';
            set @execdata = '';
        ELSE
            set @execdata = concat(@execdata,',');
        end if;

        SET n = n + 1 ; #循环一次,i加一
    END WHILE ; #结束while循环
    #select count(*) from test_table_idx;
END
//　　　　　　　　　　　　　
delimiter ;
call my_procedure(); #调用存储过程
DROP PROCEDURE IF EXISTS my_procedure ; #如果存在 my_procedure 存储过程则删除

View Code

　　执行结果：call my_procedure() OK, Time: 33.94sec 靠谱

2.2、可以根据业务创建查询sql

记得参看下【需在调试过程中临时关闭缓存】：011-MySQL Query Cache 查询缓存设置操作

　　SHOW VARIABLES LIKE '%query_cache%'; 发现已关闭，无需额外处理

查看索引

-- 查看索引
show index from test_table_idx

可以看到只有主键索引

1、主键查询

使用主键查询数据

Select SQL_NO_CACHE * from test_table_idx where id=1000988

可以看到查询耗时<=10ms

查看执行计划

explain Select SQL_NO_CACHE * from test_table_idx where id=1000988

输出：参看：004-mysql explain详解

2、使用type 以及uid查询

方式一、【无索引，全表扫描】

查询两个记录一个靠前一个靠后，

Select SQL_NO_CACHE * from test_table_idx where type='中年' and uid='1'
Select SQL_NO_CACHE * from test_table_idx where type='中年' and uid='999988'

查询时间为：小于等于580ms

查看执行计划

explain Select SQL_NO_CACHE * from test_table_idx where type='中年' and uid='1';
explain Select SQL_NO_CACHE * from test_table_idx where type='中年' and uid='999988';

输出

查看执行计划一致。type=all 全表扫描数据文件，rows =998185 执行计划中估算的扫描行数，不是精确值

方式二、【创建两个单列索引】

ALTER  TABLE  `test_table_idx`  ADD  INDEX idx_type (`type`);
ALTER  TABLE  `test_table_idx`  ADD  INDEX idx_uid (`uid`);
-- 查看索引
show index from test_table_idx

输出：

执行查询

Select SQL_NO_CACHE * from test_table_idx where type='中年' and uid='1'
Select SQL_NO_CACHE * from test_table_idx where type='中年' and uid='999988'

　　可以看到查询耗时：小于10ms

查看执行计划

解读：type=ref 多列索引，prossile_keys 查询查询可能使用到的索引都会在这里列出来；key 查询真正用到的索引，rows 扫描行数

方式三、【多列索引】

-- 删除 单列索引
alter table test_table_idx drop index idx_type;
alter table test_table_idx drop index idx_uid;
-- 查看索引
show index from test_table_idx ;
-- 创建 多列索引
ALTER  TABLE  `test_table_idx`  ADD  INDEX idx_type_uid (`type`,`uid`);
-- 查看索引
show index from test_table_idx ;
explain Select SQL_NO_CACHE * from test_table_idx where type='中年' and uid='999988';

查看建立的多列索引

　　通单列索引一致，只是名称不一致,以及seq_in_index标记1 和 2 意思是idx_type_uid 索引同时使用 type和uid两个字段，但是 uid不能单独使用，一般是1 的能单独使用

输出执行计划：

　　解读：type=ref 使用了索引，prossile_keys 查询查询可能使用到的索引都会在这里列出来；key 查询真正用到的索引，rows 扫描行数 1

情况一、增加字段，或调整顺序针对上述可以，调整顺序以及增加搜索条件，【使用索引】

explain Select SQL_NO_CACHE * from test_table_idx where age=37 and type='中年' and uid='999988';
explain Select SQL_NO_CACHE * from test_table_idx where type='中年' and age=37 and uid='999988';
explain Select SQL_NO_CACHE * from test_table_idx where type='中年' and uid='999988' and age=37;
explain Select SQL_NO_CACHE * from test_table_idx where uid='999988' and type='中年';

　　通过查看执行计划发现与上述一致，故结果以及时间也会一致

情况二、缺少索引字段【如果使用第一个字段索引会起作用，只是用后面的即seq_in_index>1的不会命中索引】

explain Select SQL_NO_CACHE * from test_table_idx where  type='中年';
explain Select SQL_NO_CACHE * from test_table_idx where  age=37 and type='中年';

英文在索引字段 idx_type_uid 中 type的seq_in_index 是1 所以索引 idx_type_uid能被 type 以及包含type的使用

如果只是用第二个字段uid 或者更多字段但是不包括 type

explain Select SQL_NO_CACHE * from test_table_idx where  uid='999988';

结果发现，不会执行任何索引，原因是 idx_type_uid索引的 uid的 seq_in_index 是2 不能单独被执行，必须有1 的存在才会被执行到

情况三、尝试更换创建索引顺序，会与上述描述一致

alter table test_table_idx drop index idx_type_uid;
ALTER  TABLE  `test_table_idx`  ADD  INDEX idx_uid_type (`uid`,`type`);

　　通过查看，发现执行计划和上述一致。使用也一致。

方式四、多列索引，单列索引同时存在

ALTER  TABLE  `test_table_idx`  ADD  INDEX idx_type (`type`);
ALTER  TABLE  `test_table_idx`  ADD  INDEX idx_uid (`uid`);
-- 查看索引
show index from test_table_idx

进行方式三中的尝试，前提是

情况一、会明总 idx_type_uid 索引

情况二、只有type字段条件：命中了idx_type_uid 索引，不会命中 idx_type索引

　　只有uid字段条件：idx_uid

三、索引创建结论

3.1、基础结论

1、依据实际使用的大部分sql来创建

2、获取慢sql来创建索引

3、索引会增大存储空间、以及降低写入更新速递

4、多列建索引比对每个列分别建索引更有优势，因为索引建立得越多就越占磁盘空间，在更新数据的时候速度会更慢。

5、建立多列索引时，顺序也是需要注意的，应该将严格的索引放在前面，这样筛选的力度会更大，效率更高。

3.1、索引原理

　　011-数据结构-树形结构-B+树[mysql应用]、B*树

发送到

索引