高性能索引

 

 

1。索引基础:索引的作用类似’目录’帮助Query来快速定位数据行。

Preface

general log&slow query log

1.1索引类型:

* *

对于MySQL的一般查询日志和慢查询日志,开启比较简单,其中公用的一个参数是log_output,log_output控制着慢查询和一般查询日志的输出方向
可以是表(mysql.general_log,mysql.slow_log)或者文件(有参数general_log_file和slow_query_log_file配置决定)
或者同时输出到表和文件(想不明白,什么时候需要同时输出到表和文件)。
但是两者受log_output参数影响,输出的目标总是一致的,也就是要么都写入表,或者要么都写入文件,不会一个输出到表,一个输出到文件。

1.1.1

    I’ve got a db design job about
meeting room booking system last week.There’re many suitable tools which
can be used to handle this kind of job such as power
designer,ERwin,HeidiSQL,dbschema,etc.Today,I’m gonna demonstrate the
last one —— dbschema.This is the official website:**https://www.dbschema.com

--slow log 相关参数
select * 
from performance_schema.global_variables 
where variable_name in
('slow_query_log','log_output','slow_query_log_file','long_query_time')

--general log 相关参数
select * 
from performance_schema.global_variables 
where variable_name in
('general_log','log_output','general_log_file')

b-tree索引

 

对于上述两种日志,系统默认不会清理,因此在开启了相关日志之后,需要人为清理。

b-tree(balance
tree)索引:使用平衡树(非平衡二叉树)来保存索引值,叶子结点的到根节点的距离相差不超过1;对于不同的引擎,不同的索引类型叶子结点保存的值可以不同,同过索引找到数据行的方式也不同

Introduce

如何清理历史general log&slow query log

可以使用b-tree索引的查询类型:1.全值索引;2.最左前缀;3.前缀索引;4.匹配范围;5.精准匹配某一列,范围匹配另一列(可以在看作最左前缀的扩大化)6.覆盖查询(只访问索引的查询)

 

1,当输出目标为表的时候

局限:范围查询对复合索引的截断作用。

   
dbschema is a rather simply used tool even you’re a novice in designing
db system.The dbschema which is downloaded on official website only free
for 15 days,then you have to pay for license for later useage but
there’s no limit in function at all.It provide two modes in designing
layout of your system.One is offline mode and the other one is
connecting to db servers.You can easily synchronize tables of database
with your designed tables as soon as possible by refreshing them from
time to time.It also supports almost all popular rdbms such as
oracle,db2,MySQL,postgreSQL.There’re many key features which you can
found in the homepage of official website.I’m not going to describe each
one of them.

  无法直接删除,如果直接删除的话,会出现“ERROR 1556 (HY000): You can’t
use locks with log tables.”的错误提示

1.1.2

 

  图片 1

hash索引

Procedure

以general
log为例,需要先关闭general_log,然后重命名general_log这个表,
图片 2 

hash索引:hash索引使用索引字段的hash值(散列值)作为索引寻址的标识,找到对应hash值时即可通过hash表对应的指针找到表数据。

 

在对重命名之后的表执行删除,最后在重命名回来,最后开启general_log(如果有必要的话)

散列值通过散列函数获得:书中对一般例子中使用的MD5()与sha1()两个散列函数的劣势做了分析(这两个函数本来是两种加密函数),两者得到的散列值长度过长,浪费空间;

*    The meeting room booking
system(I’ll call it “mrbs” .) I  contains four
tables:employee,department,conference_room,room_reservation.The detail
of tables shows below.

*

SET GLOBAL general_log = 'OFF';
RENAME TABLE general_log TO general_log_temp;
DELETE FROM general_log_temp WHERE event_time < DATE(NOW());
RENAME TABLE general_log_temp TO general_log;
SET GLOBAL general_log = 'ON';


--slow log 同理
SET GLOBAL slow_query_log = 'OFF';
RENAME TABLE slow_log TO slow_log_temp;
DELETE FROM slow_log_temp WHERE start_time < DATE(NOW());
RENAME TABLE slow_log_temp TO slow_log;
SET GLOBAL slow_query_log = 'ON';

hash冲突:使用一个hash函数传入两个不同的值可能产生一样的散列值,这就会导致hash冲突;解决办法一般为修改散列函数,与再散列;以及增加where条件人工消除hash冲突的影响三种。

 

  如果对重命名之后的表(general_log或者是slow_log)没有再次重命名回来,会发生什么?
  参加如下截图,如果没有找到对应的表(general_log或者是slow_log),在输出目标为表的情况下,会提示无法找到对应的表,将无法开启对应的日志

文中提到:比较廉价的方式是截取md5()返回值的一部分作为散列值。

employee table

图片 3

应该注意到:myisam,innodb引擎并不支持hash索引。但是innodb有一个特殊的功能“自适应hash索引”,使其给予b-tree索引建立一个hash索引,具有一定hash索引性质。

1 id             自增id                int(11)
2 user_id        工号                  int(11)
3 user_name      用户名称              varchar(20)
4 user_phone     用户手机号             bigint
5 user_email     用户邮箱              varchar(50)
6 user_dept_id   用户所在部门id         int(11)
7 user_status    在职、离职等           tinyint(4)
8 create_time    用户创建时间           datetime
9 update_time    用户信息修改时间       datetime

2,当输出目标为文件的时候

1.1.3空间数据索引(R-tree)

 

  当输出目标为文件的时候,在linux下,直接使用rm命名删除即可,如果在开启了一般查询日志或者是慢查询日志,删除对应的日志文件,并不影响数据库的正常使用
  网上有说需要停止MySQL服务然后重命名文件然后在创建新的文件啥的,在Linux下并不是必须的,不知道在windows下是什么情况,没兴趣试。
  当然也不是说就建议始终这种暴力的方式清理日志文件,
  在Linux下,删除了默认的日志文件(或者重命名了原日志文件),要想再次生成日志文件
  1,可以使用mysqladmin flush-logs
  2,是SQL命令flush slow logs;flush general logs;
  3,重启MySQL服务
  均可重新生成对应的日志文件。

MYISAM支持R-tree,但是mysql数据库在空间索引方面是弱势的,pgsql的postGIS做的比较好。

**department
table**

图片 4

1.1.4全文索引

1 id              自增id                 int(11)
2 dept_id         部门id                 int(11)
3 dept_name       部门名称               varchar(30)
4 parent_id       父级id                 tinyint(4)
5 tlevel          层级id                 tinyint(4)
6 create_time     部门创建时间            datetime    
7 update_time     部门信息修改时间        datetime

注意:当对应的文件是存在的时候,上述命名执行之后是没有影响的(也不会清理对应的日志文件)

全文索引与b-tree索引不冲突,适用于match,against操作。第七章会详细讨论

 

 

1.1.5其他索引

**conference_room
table**


这里提到了tokudb引擎使用的分型树索引(fractal  tree 
index);以及后面将要讲解的聚簇索引以及覆盖索引。

1 id                 自增id                int(11)
2 room_id            会议室id              int(11)
3 room_building_id   楼号                  int(11)
4 room_num           房间号                int(11)
5 room_max_num       最大容纳人数           int(11)
6 room_status        会议室状态            tinyint(4)
7 create_time        会议室创建时间         datetime
8 update_time        会议室信息修改时间     datetime

 

 

 

以下偏离主题

5.2

**room_reservation
table**

当输出目标为表的时候的解析

索引优点:

 1 会议室预定表(room_reservation)
 2 id                  自增id               int(11)
 3 book_id             预定工单id            int(11)
 4 book_room_id        预定会议室id          int(11)
 5 book_start_time     预定开始时间          datetime
 6 book_stop_time      预定结束时间          datetime
 7 book_user_id        预定人id              int(11)
 8 book_usage          预定用途              varchar(200)
 9 book_status         预定工单状态           tinyint(4)
10 create_time         预定工单创建时间       datetime
11 update_time         预定工单修改时间       datetime

  不管是general_log或者是slow_log,对应的SQL语句都是二进制格式的,需要使用convert(sql_text
using UTF8)做一个转换,才变得具有可读性。

1.大大减少了服务器需要扫描的数据量;

 

图片 5

2.帮助服务器避免了排序和临时表;

**Configure
the database connection.**

 

3.将随机io变为顺序io;

图片 6

当输出目标为表的时候对性能的影响

 

 

  据个人测试,在请求量不大的数据库上,开启general_log或者是slow_log,对性能影响并没有非常明显。
  理论家们一方面强调说MySQL的处理并发上多强悍,一方面又说开启general_log对性能影响很大,会不会自相矛盾呢?
  关于general_log,在zabbix监控下,测试环境TPS不过百的情况下(每秒写入general
log不超过100条数据),开启general_log之后并CPU负载几乎没有变化,CPU高点是在做其他压力测试。
  尤其是slow_log这种写入不是太频繁的日志,直接写入到表中,对性能的影响有限,比后面再去花时间解析文件……  
  当然不排除TPS在上千或者上万甚至更高之后,开启general_log会产生较大的影响,当然没事也不会闲的蛋疼去开general_log。

5.3高性能索引

Use
mouse to create target tables in dbschema.

  图片 7

5.3.1

 图片 8

 

左值才是索引列

 

 

索引不能使用表达式的一部分例:where a_id+1=2

Check
the primary key & unique key(even other keys but I’m not creating them
ye).

备注:

5.3.2

 图片 9

直接rm掉general log&slow query log
并没有实际删除文件,磁盘空间也不会释放。mysql进程会继续持有,并且持续往里写数据。lsof看一下就知道了

前缀索引与索引的选择性

 

压力大的情况下 大概30-% 性能损耗

这一节详细讨论了前缀索引对于选择多常记录作为索引字段合适的问题:

Check
the foreign key.

提出了一个‘基数’的概念,即前缀对应唯一条目的数量与总数的笔直。前缀的基数应该接近完整列

 图片 10

例:count(distinct  tt(city,3))/count(*)

 

这个值可以称为选择性

After
you click ok button,the table will be created in “mrbs”
database.

注:部分字段后缀索引有时候更加有效

图片 11

5.3.3

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注