索引的作用

类似于一本书中的目录,起到优化查询的作用

索引的分类(算法)

1.png

  • B树(默认使用的索引类型)
  • R树
  • Hash
  • FullText
  • GIS 索引

索引树高度应当越低越好,一般维持在3-4最佳.如果数据行数较多,可以采用parttion分表(用的比较少),分片,分布式架构.业务允许,尽量选择字符长度短的列作为索引列.业务不允许,采用前缀索引.

Btree索引功能上的分类

辅助索引

  1. 提取索引列的所有值,进行排序
  2. 将排好序的值,均匀的存放在叶子节点,进一步生成枝节点和根节点
  3. 在叶子节点中的值,都会对应存储主键ID

辅助索引细分

  • 单列辅助索引
  • 联合索引(覆盖索引)*
  • 唯一索引

聚集索引

  1. MySQL 会自动选择主键作为聚集索引列,没有主键会选择唯一键,如果都没有会生成隐藏的.
  2. MySQL进行存储数据时,会按照聚集索引列值得顺序,有序存储数据行
  3. 聚集索引直接将原表数据页,作为叶子节点,然后提取聚集索引列向上生成枝和根

聚集索引和辅助索引的区别

  1. 表中任何一个列都可以创建辅助索引,在你有需要的时候,只要名字不同即可
  2. 在一张表中,聚集索引只能有一个,一般是主键.
  3. 辅助索引,叶子节点只存储索引列的有序值+聚集索引列值.
  4. 聚集索引,叶子节点存储的时有序的整行数据.
  5. MySQL的表数据存储是聚集索引组织表

索引的命令操作

查询索引

desc city;
show index from city;
  • PRI : 主键索引
  • MUL : 辅助索引
  • UNI : 唯一索引

创建索引

  • 单列的辅助索引
ALTER TABLE city ADD INDEX idx_name(Name);
  • 多列的联合索引
ALTER TABLE city ADD INDEX idx_c_p(CountryCode,Population);
  • 唯一索引
ALTER TABLE city ADD UNIQUE INDEX uidx_dis(District);
  • 前缀索引
ALTER TABLE city ADD INDEX idx_dis(District(5));
  • 删除索引
ALTER TABLE city DROP INDEX idx_name;
ALTER TABLE city DROP INDEX idx_c_p;
ALTER TABLE city DROP INDEX idx_dis;

执行计划分析

将优化器,选择后的执行计划,截取出来.便于管理管判断语句得执行效率

DESC SELECT * FROM city;
EXPLAIN SELECT * FROM city;
  • 查看运行结果
mysql> DESC SELECT * FROM city;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | city  | ALL  | NULL          | NULL | NULL    | NULL | 4046 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.04 sec)

分析执行计划

  • table : 表名
  • type : 查询的类型

查询类型又分为全表扫描和索引扫描

  • 全表扫描 : ALL
  • 索引扫描 : index,range,ref,eq_ref,const(system),NULL,从左到右,index性能最差,NULL性能最好

index: 全索引扫描

DESC SELECT countrycode FROM city;

range: 索引范围扫描(>,<,>=,<=,between and,or,in,like`)

DESC SELECT countrycode FROM city WHERE id>20000;
DESC SELECT countrycode FROM city WHERE Countrycode LIKE 'CH%';
#对于辅助索引来讲,!= 和not in等语句是不走索引的
#对于主键索引列来讲,!= 和not in等语句是走range
DESC SELECT * FROM city WHERE countrycode = 'CHN' OR countrycode = 'USA';
DESC SELECT * FROM city WHERE countrycode IN ('CHN','USA');

ref: 辅助索引等值查询

DESC SELECT * FROM city WHERE countrycode='CHN';
一般会把or语句修改为UNION ALL来提高性能
DESC SELECT * FROM city WHERE countrycode='CHN' UNION ALL SELECT * FROM city WHERE countrycode='USA';

eq_ref : 多表连接时,子表使用主键列或唯一列作为连接条件

DESC SELECT b.NAME, a.NAME, a.population FROM city AS a JOIN country AS b ON a.countrycode = b.CODE WHERE a.population < 100;

const(system) : 主键或者唯一键的等值查询

DESC SELECT countrycode FROM city WHERE id=100;

NULL

DESC SELECT countrycode FROM city WHERE id=20000;
  • possible_key : 可能会用到的索引
  • key : 真正选择了哪个索引
  • key_len : 索引覆盖长度
  • Extra : 出现Using filesort,说明在查询中有关排序的条件列没有合理的应用索引order by,group by,distinct,union

索引优化压力测试

  • 使用以下命令创建一个100万行的表
  • 使用sql文件导入一个100万行的表t100w.rar
CREATE DATABASE test charset utf8mb4 COLLATE utf8mb4_bin;
USE test;
CREATE TABLE t100w ( id INT, num INT, k1 CHAR ( 2 ), k2 CHAR ( 4 ), dt TIMESTAMP ) charset utf8mb4 COLLATE utf8mb4_bin;

delimiter //
CREATE PROCEDURE rand_data ( IN num INT ) BEGIN
    DECLARE
        str CHAR ( 62 ) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
    DECLARE
        str2 CHAR ( 2 );
    DECLARE
        str4 CHAR ( 4 );
    DECLARE
        i INT DEFAULT 0;
    WHILE
            i < num DO

            SET str2 = concat(
                substring( str, 1+floor ( rand( ) * 61 ), 1 ),
                substring( str, 1+floor ( rand( ) * 61 ), 1 ) 
            );

        SET str4 = concat(
            substring( str, 1+floor ( rand( ) * 61 ), 2 ),
            substring( str, 1+floor ( rand( ) * 61 ), 2 ) 
        );

        SET i = i + 1;
        INSERT INTO t100w
        VALUES
            ( i, floor( rand( ) * num ), str2, str4, now( ) );

    END WHILE;

END;
// 
delimiter;
CALL rand_data ( 1000000 );
COMMIT;

优化前测试

[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k2='MN89'" engine=innodb --number-of-queries=2000 -uroot -p123456 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
    Running for engine rbose
    Average number of seconds to run all queries: 545.034 seconds
    Minimum number of seconds to run all queries: 545.034 seconds
    Maximum number of seconds to run all queries: 545.034 seconds
    Number of clients running queries: 100
    Average number of queries per client: 20

添加索引

#添加单列的辅助索引
ALTER TABLE test.t100w ADD INDEX k2(k2);

优化后测试

[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k2='MN89'" engine=innodb --number-of-queries=2000 -uroot -p123456 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
    Running for engine rbose
    Average number of seconds to run all queries: 0.927 seconds
    Minimum number of seconds to run all queries: 0.927 seconds
    Maximum number of seconds to run all queries: 0.927 seconds
    Number of clients running queries: 100
    Average number of queries per client: 20