神出鬼没的Enderman

索引的作用

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

索引的分类(算法)

• 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