7. 老男孩-深圳标杆班-DBA-day04-第四章索引及执行计划.txt

落枫0907

103

4页

1次

2022-10-21

50墨值下载

索引及执行计划

1. 索引的作用

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

2. 索引的分类(算法)

B 树默认使用的索引类型

R 树

Hash

FullText

GIS 索引

3. BTREE 索引算法演变(了解)

看图说话.

4. Btree 索引功能上的分类

4.1 辅助索引

(1) 提取索引列的所有值,进行排序

(2) 将排好序的值,均匀的存放在叶子节点,进一步生成枝节点和根节点

(3) 在叶子节点中的值,都会对应存储主键 ID

4.2 聚集索引

(1)MySQL 会自动选择主键作为聚集索引列,没有主键会选择唯一键,如果都没有会生成隐藏的.

(2)MySQL 进行存储数据时,会按照聚集索引列值得顺序,有序存储数据行

(3)聚集索引直接将原表数据页,作为叶子节点,然后提取聚集索引列向上生成枝和根

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

(1) 表中任何一个列都可以创建辅助索引,在你有需要的时候,只要名字不同即可

(2) 在一张表中,聚集索引只能有一个,一般是主键.

(3) 辅助索引,叶子节点只存储索引列的有序值+聚集索引列值.

(4) 聚集索引,叶子节点存储的时有序的整行数据.

(5) MySQL 的表数据存储是聚集索引组织表

5. 辅助索引细分

5.1 单列辅助索引

5.2 联合索引(覆盖索引) *****

5.3 唯一索引

6. 索引树高度

索引树高度应当越低越好,一般维持在 3-4 最佳

6.1 数据行数较多

分表 : parttion 用的比较少了.

分片,分布式架构.

6.2 字段长度

业务允许,尽量选择字符长度短的列作为索引列

业务不允许,采用前缀索引.

6.3 数据类型

char 和 varchar

enum

7. 索引的命令操作

7.1 查询索引

desc city;

PRI ==> 主键索引

MUL ==> 辅助索引

UNI ==> 唯一索引

mysql> show index from city\G

7.2 创建索引

单列的辅助索引:

mysql> alter table city add index idx_name(name);

多列的联合索引:

mysql> alter table city add index idx_c_p(countrycode,population);

唯一索引:

mysql> alter table city add unique index uidx_dis(district);

mysql> select count(district) from city;

mysql> select count(distinct district) from city;

前缀索引

mysql> alter table city add index idx_dis(district(5));

7.3 删除索引

mysql> alter table city drop index idx_name;

mysql> alter table city drop index idx_c_p;

mysql> alter table city drop index idx_dis;

8. 压力测试准备:

mysql> use test

mysql> source /tmp/t100w.sql

8.1 未做优化之前测试

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 -p123 -verbose

[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 -p123 -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: 755.861 seconds

Minimum number of seconds to run all queries: 755.861 seconds

Maximum number of seconds to run all queries: 755.861 seconds

Number of clients running queries: 100

Average number of queries per client: 20

8.2 索引优化后

[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100

--iterations=1 --create-schema='test' --query="select * from test.t100w where

of 4

50墨值下载

索引 mysql索引 mysql创建索引

关注

评论