mysql-索引-日志

索引:基于元数据之上的在某个字段或多个字段取出来,索引是加速读操作的,但对写操作时有副作用的

  BTree 索引:抽取出来重新排序,是左前缀索引每一个叶子结点到根结点的距离相同;

哈希索引:基于键查找值的索引方式innodb不支持,

位图索引:mysql不支持

全文索引:

适用于B+ TREE索引的查询类型:全键值、键值范围或键前缀

全值匹配:精确匹配某个值;WHERE COLUMN = 'value'

匹配最左前缀:只精确起头的部分WEHRE COLUMN LIKE 'PREFIX%'

匹配范围值:

精确匹配某一列,范围匹配另一列;

只用访问索引的查询:覆盖索引;是一种快速的查询

不适用B+ TREE索引:

1如果查条件不是从最左侧列开始,索引无效;

2不能跳过索引中的某列;

3如果查询中的某个列是为范围查询,那么其右侧的列都无法再使用索引优化查询;

Hash索引:基于哈希表实现,特别适用于值的精确匹配查询;

适用场景:

只支持等值比较查询,例如=, IN(), <=>精确比较

不用场景:

所有非精确值查询;MySQL仅对memory存储引擎支持显式的hash索引;

索引优点:

降低需要扫描的数据量,减少IO次数;

可以帮助避免排序操作索引本身就是顺序的,避免使用临时表;

帮助将随机IO转为顺序IO;

高性能索引策略:

(1) 在WHERE中独立使用列,尽量避免其参与运算;

WHERE age+2 > 32 ;

(2) 左前缀索引:索引构建于字段的最左侧的多少个字符,要通过索引选择性来评估

索引选择性:不重复的索引值和数据表的记录总数的比值;

(3) 多列索引:

AND连接的多个查询条件更适合使用多列索引,而非多个单键索引;

(4) 选择合适的索引列次序:选择性最高的放左侧;如等值比较的放左侧

创建索引:

CREATE   INDEX   AGE  on   students(age);   创建索引

SHOW   INDEXES  FROM  students;   查看创建的索引

EXPLAIN   select   name  from   students   WHERE   age  >  90;   查看有没有用到索引

mysql-索引-日志

id:当前查询语句中,第个SELECT语句的编号;

select_type:查询类型:

  简单查询:SIMPLE

  复杂查询:

    简单子查询:SUBQUERY

·用于FROM中的子查询:DERIVED

·联合查询中的第一个查询:PRIMARY

联合查询中的第一个查询之后的其它查询:UNION

联合查询生成的临时表:UNION RESULT

table:查询针对的表;

type:关联类型,或称为访问类型,即MySQL如何去查询表中的行

  ALL:全表扫描

  index:根据索引的顺序进行的全表扫描;但同时如果Extra列出现了"Using index”表示使用了覆盖索引

  range:有范围限制地根据索引实现范围扫描;扫描位置始于索引中的某一项,结束于另一项;

  ref:根据索引返回的表中匹配到某单个值的所有行(匹配给定值的行不止一个);

  eq_ref:根据索引返回的表中匹配到某单个值的单一行,仅返回一个行,但需要与某个额外的参考值比较,而不是常数;

   const,system:与某个常数比较,且只返回一行;精确比较,返回一行

possiable_keys:查询中可能会用到的索引

key:查询中使用的索引;

key_len:查询中用到的索引长度;

ref:在利用key字段所显示的索引完成查询操作时所引用的列或常量值;

rows:MySQL估计出的为找到所有的目标项而需要读取的行数

Extra:额外信息

  Using index:使用了覆盖索引进行的查询;

Using where:拿到数据后还要再次进行过滤;

Using temporary:使用了临时表以完成查询;

Using filesort:对结果使用了一个外部索引排序;

mysql的账号授权

1 mysql的域名解析需要关闭防止反解

skip_name_resolve=on

mysql-索引-日志

创建用户:create  user   ‘user’@‘host’identified  by  passwd

重命名:rename  user  old_user  to  newuser

删除用户:drop  user ‘user‘@‘host’

修改用户密码:

1 set  password  [for  ‘user’@’host’] = password (‘cleartext  password’);

如:set  password  for ‘root’@’localhost’ = password(‘magedus’) ;

2update  mysql.user  set  password=PASSWORD(‘cleartext  passwd’) where  user = ‘username’  and  host= ‘host’ ;

3 mysqladmin  -uuser  -hlocalhost –p  password  ‘new_pass’

忘记密码解决方法:

1 启动mysql进程时 使用—skip-grant-tables 和 –skip-networking

2通过update命令修改管理员密码

3 已正常方式启动mysql

如:在配置文件中的ExecStart=……. 后面加上—skip-grant-tables   –skip-networking

重载: systemctl  daemon-reload

启动:

再连接mysql是不需要密码的

update   mysql.user  set  password=PASSWORD=(‘magedu’) ;

改完后在停止服务,

再把配置文件加的两项删了

在重启服务。

让mysql重新加载授权表:fiush   privileges

当数据库存在用户的时候GRANT会对用户进行授权,但当数据库不存在该用户的时候,就会创建相应的用户并进行授权。

grant   select   on  hidb.students  TO ‘text’@’172.16.8.%’ IDENTIFIED  BY  ‘mageud’;

mysql-索引-日志

mysql-索引-日志

缓存:

所有缓存都有有效期限,为了保持缓存中的数据与原始数据保持一致,需要让缓存区数据联动失效,mysql自身具有缓存功能,

外部的缓存系统:redis ,当mysql的节点过多时

mysql的查询流程:

客户端----缓存(有缓存直接返回)----解析器  -------预处理器-----查询优化器

----查询执行引擎(会通过调用存储引擎来调取数据)------结果返回给客户,并添加到缓存

查询缓存:

如何判断缓存是否命中:

查询缓存相关:

show  global  variables  like  ‘query_cache%’;

set   GLOBAL   query_cache_limit=1024*1024*2;

查询缓存相关的服务器变量:

query_cache_limit:能够缓存的最大查询结果;(单语句结果集大小上限)

有着较大结果集的语句,显式使用SQL_NO_CACHE,以避免先缓存再移出;

query_cache_min_res_unit:内存块的最小分配单位;缓存过小的查询结果集会浪费内存空间;

较小的值会减少空间浪费,但会导致更频繁地内存分配及回收操作;

较大值的会带来空间浪费;

query_cache_size:查询缓存空间的总共可用的大小;单位是字节,必须是1024的整数倍;

query_cache_strip_comments:OFF  一般不需要

query_cache_type:缓存功能启用与否;ON:启用;OFF:禁用;

如select  SQL  NO  CACHE  NAME  FROM  STUDENTS   WHERE  stuID=7; 表示不添加到缓存

query_cache_wlock_invalidate:如果某表被其它连接锁定,是否仍然可以从查询缓存中返回查询结果;默认为OFF,表示可以;ON则表示不可以;

缓存的状态变量:SHOW GLOBAL  STATUS  LIKE ‘Qcache%’;用来对应显示你的空间中缓存的有效性,用来统计数据的,可以判断出来缓存是否有效

mysql> SHOW GLOBAL STATUS LIKE 'Qcache%';

+-------------------------+----------+

| Variable_name           | Value    |

+-------------------------+----------+

| Qcache_free_blocks      | 1        |     剩余的块空间没有用

| Qcache_free_memory      | 16759688 |    内存空间

| Qcache_hits             | 0        |    命中次数

| Qcache_inserts          | 0        |  被使用多少次

| Qcache_lowmem_prunes    | 0        |    定义缓存清理空间的次数

| Qcache_not_cached       | 0        |    没能缓存进来的缓存

| Qcache_queries_in_cache | 0        |    现在缓存中有存多少数据

| Qcache_total_blocks     | 1        |    一共分配出多少块

+-------------------------+----------+

SHOW  GLOBAL  STATUS  LIKE  ‘com_select ’    执行select的次数

日志

mysql把数据存储的文件有两种 一种是数据索引相关的文件,一种是日志文件

查询日志:general_log

慢查询日志:log_slow_queries 查询时间超过指定时长的日志

错误日志:log_error, log_warnings

二进制日志:binlog        记录着运行过程中引起数据改变的操作,像主从操作就是基于这个日志的,重服务器读取这个日志,放在自己的中继日志中,重自己起一个程序,来读一条执行一个。来保持主从一致

这个日志还可以做时间点恢复的。

中继日志:relay_log

事务日志:innodb_log  可以完成事物的回滚操作

1、查询日志

mysql-索引-日志

启动日志

mysql-索引-日志

查看日志位置

mysql-索引-日志

2、慢查询日志

查询:运行时间超出指定时长的查询;默认是关闭的

mysql-索引-日志

mysql-索引-日志

查看超出多长时间算慢

mysql-索引-日志

3、错误日志

记录信息:

(1) mysqld启动和关闭过程 输出的信息;

(2) mysqld运行中产生的错误信息;

(3) event scheduler(事件调度器)运行时产生的信息;

(4) 主从复制架构中,从服务器复制线程启动时产生的日志

mysql-索引-日志

4、二进制日志

用于记录引起数据改变或存在引起数据改变的潜在可能性的语句(STATEMENT)或改变后的结果(ROW),也可能是二者混合;

mysql-索引-日志

binlog_format={STATEMENT|ROW|MIXED}

  记录有三种格式

    STATEMENT:基于语句;

    ROW:基于行; 语句的结果

   MIXED:混编;

查看二进制日志文件列表:

mysql-索引-日志

查看当前正在使用的二进制日志文件:

mysql-索引-日志

查看二进制 日志文件中的事件

mysql-索引-日志

SHOW  BINLOG  EVENTS  IN  ‘master-log.000002’  from  245  LIMIT   3;

也可以查看二进制日志文件:

#mysqlbinlog   master-log.00002

服务器变量:

  log_bin=/PATH/TO/BIN_LOG_FILE

  session.sql_log_bin={ON|OFF}    控制某会话中的“写”操作语句是否会被记录于日志文件中

  max_binlog_size=1073741824

  sync_binlog={1|0}   是否同步二进制日志到磁盘文件中区

  mysqlbinlog   命令用来查看二进制日志的

二进制日志事件格式:

# at 553

#160831  9:56:08 server id 1  end_log_pos 624   Query   thread_id=2     exec_time=0     error_code=0

SET TIMESTAMP=1472608568/*!*/;

BEGIN

/*!*/;

事件的起始位置:# at 553

事件发生的日期时间:#160831  9:56:08

事件发生的服务器id:server id 1

事件的结束位置:end_log_pos 624

事件的类型:Query

事件发生时所在服务器执行此事件的线程的ID: thread_id=2

语句的时间戳与将其写入二进制日志文件中的时间差:exec_time=0

错误代码:error_code=0

事件内容:SET TIMESTAMP=1472608568/*!*/;

中继日志:

从服务器上记录下来从主服务器的二进制日志文件同步过来的事件;

事务日志:

事务型存储引擎innodb用于保证事务特性的日志文件:

上一篇:linux 文件夹说明,用户添加删除,不熟悉的命令


下一篇:linux ubuntu的root密码