美文网首页
DBA之路 7_MySQL_不走索引情况及存储引擎

DBA之路 7_MySQL_不走索引情况及存储引擎

作者: Linux_淡忘 | 来源:发表于2019-06-21 19:27 被阅读0次

    Key_len长了好还是短了好

    维度1:索引列值长度来看
    varchar(255)
    越短越好,一般是针对于前缀索引
    维度2:从联合索引覆盖长度
    覆盖长度越长越好
    

    8.2 不走索引的情况(开发规范)

    8.2.1 没有查询条件,或者查询条件没有建立索引
    select * from tab;       全表扫描。
    select  * from tab where 1=1;
    在业务数据库中,特别是数据量比较大的表。
    是没有全表扫描这种需求。
    1、对用户查看是非常痛苦的。
    2、对服务器来讲毁灭性的。
    (1)
    select * from tab;
    SQL改写成以下语句:
    select  * from  tab  order by  price  limit 10 ;    需要在price列上建立索引
    (2)
    select  * from  tab where name='zhangsan'          name列没有索引
    改:
    1、换成有索引的列作为查询条件
    2、将name列建立索引
    
    8.2.2 查询结果集是原表中的大部分数据,应该是25%以上。
    查询的结果集,超过了总数行数25%,优化器觉得就没有必要走索引了。
    
    假如:tab表 id,name    id:1-100w  ,id列有(辅助)索引
    select * from tab  where id>500000;
    如果业务允许,可以使用limit控制。
    怎么改写 ?
    结合业务判断,有没有更好的方式。如果没有更好的改写方案
    尽量不要在mysql存放这个数据了。放到redis里面。
    
    8.2.3 索引本身失效,统计数据不真实
    索引有自我维护的能力。
    对于表内容变化比较频繁的情况下,有可能会出现索引失效。
    一般是删除重建
    
    现象:
    有一条select语句平常查询时很快,突然有一天很慢,会是什么原因
    select?  --->索引失效,,统计数据不真实
    DML ?   --->锁冲突
    
    8.2.4 查询条件使用函数在索引列上,或者对索引列进行运算,运算包括(+,-,*,/,! 等)
    例子:
    错误的例子:select * from test where id-1=9;
    正确的例子:select * from test where id=10;
    
    算术运算
    函数运算
    子查询
    
    8.2.5 隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.
    这样会导致索引失效. 错误的例子:
    mysql> alter table tab add index inx_tel(telnum);
    Query OK, 0 rows affected (0.03 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    mysql>
    mysql> desc tab;
    +--------+-------------+------+-----+---------+-------+
    | Field  | Type        | Null | Key | Default | Extra |
    +--------+-------------+------+-----+---------+-------+
    | id    | int(11)    | YES  |    | NULL    |      |
    | name  | varchar(20) | YES  |    | NULL    |      |
    | telnum | varchar(20) | YES  | MUL | NULL    |      |
    +--------+-------------+------+-----+---------+-------+
    3 rows in set (0.01 sec)
    mysql> select * from tab where telnum='1333333';
    +------+------+---------+
    | id  | name | telnum  |
    +------+------+---------+
    |    1 | a    | 1333333 |
    +------+------+---------+
    1 row in set (0.00 sec)
    mysql> select * from tab where telnum=1333333;
    +------+------+---------+
    | id  | name | telnum  |
    +------+------+---------+
    |    1 | a    | 1333333 |
    +------+------+---------+
    1 row in set (0.00 sec)
    mysql> explain  select * from tab where telnum='1333333';
    +----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
    | id | select_type | table | type | possible_keys | key    | key_len | ref  | rows | Extra                |
    +----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
    
    |  1 | SIMPLE      | tab  | ref  | inx_tel      | inx_tel | 63      | const |    1 | Using index condition |
    +----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
    1 row in set (0.00 sec)
    mysql> explain  select * from tab where telnum=1333333;
    +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
    | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra      |
    +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
    |  1 | SIMPLE      | tab  | ALL  | inx_tel      | NULL | NULL    | NULL |    2 | Using where |
    +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
    1 row in set (0.00 sec)
    mysql> explain  select * from tab where telnum=1555555;
    +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
    | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra      |
    +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
    |  1 | SIMPLE      | tab  | ALL  | inx_tel      | NULL | NULL    | NULL |    2 | Using where |
    +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
    1 row in set (0.00 sec)
    mysql> explain  select * from tab where telnum='1555555';
    +----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
    | id | select_type | table | type | possible_keys | key    | key_len | ref  | rows | Extra                |
    +----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
    |  1 | SIMPLE      | tab  | ref  | inx_tel      | inx_tel | 63      | const |    1 | Using index condition |
    +----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
    1 row in set (0.00 sec)
    mysql>
    
    8.2.6 <> ,not in 不走索引(辅助索引)
    EXPLAIN  SELECT * FROM teltab WHERE telnum  <> '110';
    EXPLAIN  SELECT * FROM teltab WHERE telnum  NOT IN ('110','119');
    
    mysql> select * from tab where telnum <> '1555555';
    +------+------+---------+
    | id  | name | telnum  |
    +------+------+---------+
    |    1 | a    | 1333333 |
    +------+------+---------+
    1 row in set (0.00 sec)
    mysql> explain select * from tab where telnum <> '1555555';
    
    
    单独的>,<,in 有可能走,也有可能不走,和结果集有关,尽量结合业务添加limit
    or或in  尽量改成union
    EXPLAIN  SELECT * FROM teltab WHERE telnum  IN ('110','119');
    改写成:
    
    
    EXPLAIN SELECT * FROM teltab WHERE telnum='110'
    UNION ALL
    SELECT * FROM teltab WHERE telnum='119'
    
    8.2.7 like "%_" 百分号在最前面不走
    EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '31%'  走range索引扫描
    EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '%110'  不走索引
    
    %linux%类的搜索需求,可以使用elasticsearch+mongodb 专门做搜索服务的数据库产品
    

    存储引擎

    1.介绍

    类似于Linux系统中的文件系统
    

    2.功能

    1.数据读写
    2.数据安全和一致性
    3.提高性能
    4.热备份
    5.自动故障恢复
    6.高可用方面支持
    等
    

    3.种类

    3.1Oracle的MySQL
    1>InnoDB
    mysql> select table_schema,table_name,engine from information_schema.tables where engine='InnoDB';
    查询数据库中所有InnoDB引擎的表
    2>MyISAM
    3>MEMORY
    4>ARCHIVE
    5>CSV
    熟悉InnoDB核心原理,:ACID,MVCC,事务,锁等
    
    3.2其他的引擎
    show engines;
    PerconaDB:默认是XtraDB
    MariaDB:默认是InnoDB
    其他的存储引擎支持
    TokuDB   压缩比比较高,可以达到1:5
    RocksDB
    MyRocks
    以上三个存储引擎的共同点:压缩比较高,数据插入性能极高
    现在很多的NewSQL使用比较多的功能特性
    推荐TokuDB
    

    项目案例:监控系统架构整改

    环境:zabbix 3.2 mariaDB 5.5 centos 7.3
    现象:zabbix特别卡,每隔3-4个月,都要重新搭建一遍zabbix,存储空间经常爆满
    问题:
    1.zabbix版本
    2.数据库版本
    3.zabbix数据库500G,存在一个文件里
    优化建议:
    1.数据库(mariaDB)版本升级到10.0,zabbix升级更高版本
    2.存储引擎改为TokuDB
    3.监控数据按月份进行切割(二次开发:zabbix 数据保留机制重写,数据库分表)
    4.关闭binlog和双1   
    5.参数调整
    
    为什么?
    1>原生态支持TokuDB,另外经过测试环境,10.0要比5.5性能高2-3倍
    2>TokuDB:insert数据比innodb快的多,数据压缩比要比innodb高
    3>监控数据按月份进行切割,为了能够truncate每个分区表,立即释放空间
    4>关闭binlog ------>减少无关日志的记录,zabbix不需要特别注重安全,需要注重性能
    5>参数调整----->安全性参数关闭,提高性能
    

    4.InnoDB存储引擎介绍

    image.png
    InnoDB核心特性
    事务(Transaction)***********
    行级锁(Row_level_lock)***********
    MVCC(Multi-Version concurrency control 多版本并发控制)**********
    外键**
    热备**********
    自动故障恢复(ACSR)Auto Crash Safey Recovery************
    复制Replication:Group Commit
    GTID(Global Transaction ID)
    多线程(Multi-Threads-SQL)
    

    5.存储引擎操作类命令

    5.1使用select确认会话存储引擎

    mysql> select @@default_storage_engine;
    +--------------------------+
    | @@default_storage_engine |
    +--------------------------+
    | InnoDB                   |
    +--------------------------+
    1 row in set (0.01 sec)
    

    5.2默认存储引擎设置

    会话级别:
    set default_storage_engine=myisam;
    mysql> set default_storage_engine=myisam;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select @@default_storage_engine;
    +--------------------------+
    | @@default_storage_engine |
    +--------------------------+
    | MyISAM                   |
    +--------------------------+
    
    全局级别(仅影响新会话):
    set global default_storage_engine=myisam;
    重启之后,所有参数均失效.
    如果要永久生效:
    写入配置文件
    vim /etc/my.cnf
    [mysqld]
    default_storage_engine=myisam
    存储引擎是表级别的,每个表创建时可以指定不同的存储引擎,但是我们建议统一为innodb.
    
    拓展:
    在线修改MySQL参数;
    会话级别:例如:
    set default_storage_engine=myisam
    功能:只会影响当前会话
    全局级别:例如
    set global default_storage_engine=myisam
    功能:不影响当前和历史会话,值影响新会话
    以上两种方法,在重启后会失效,除非写入配置文件。
    
    5.3show 确认每个表的存储引擎
    show create table city\G;
    show table status like 'countrylanguage' \G
    
    5.4information_schema确认每个表的存储引擎
    [world]>select table_schema,table_name ,engine from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema');
    Master [world]>show table status;
    Master [world]>show create table city;
    
    5.5修改一个表的存储引擎
    替换引擎单表:
    mysql> alter table tb1 engine=innodb;
    注意:此命令我们经常使用他,进行innodb表的碎片整理
    
    
    5.6平常处理过的MySQL问题-----碎片处理
    环境CentOS7.4 MySQL 5.7.20 InnoDB存储引擎
    业务特点:数据量级大,经常需要按月删除历史数据
    问题:磁盘空间占用很大,不释放
    处理方法:
    以前:将数据逻辑导出,手工drop表,然后导入进去
    现在:对表进行按月进行分表(partition,中间件)
    业务替换为truncate方式
    

    6.InnoDB引擎的存储结构

    6.0 最直观的存储方式(/data/mysql/data)

    -rw-r----- 1 mysql mysql    12748 Jun 20 22:11 ib_buffer_pool
    -rw-r----- 1 mysql mysql 79691776 Jun 21  2019 ibdata1
    -rw-r----- 1 mysql mysql 50331648 Jun 21 12:04 ib_logfile0
    -rw-r----- 1 mysql mysql 50331648 Jun 19 16:09 ib_logfile1
    -rw-r----- 1 mysql mysql 12582912 Jun 21  2019 ibtmp1
    ib_buffer_pool:热数据,会把缓冲区的数据mysql进行判断,然后有用的刷写进这个文件
    ibdata1:系统数据字典信息(统计信息),UNDO(回滚)表空间等数据
    ib_logfile0 ~ ib_logfile1:REDO(重做日志)日志文件,事务日志文件
    ibtmp1:临时表空间磁盘位置,存储临时表。
    frm:存储列信息
    idb: 表的数据行和索引
    
    6.1表空间(tablespace)
    6.1.1共享表文件
    需要将所有数据存储到同一个表空间,管理比较混乱
    5.5版本出现的管理模式,也是默认的管理模式。(数据字典,undo,临时表,索引,表数据)
    5.6版本以共享表空间保留,只用来存储:数据字典,undo,临时表
    5.7版本临时表也被独立出来
    8.0版本,undo也被独立出来
    
    6.1.2共享表空间设置
    mysql> select @@innodb_file_per_table;
    +-------------------------+
    | @@innodb_file_per_table |
    +-------------------------+
    |                       1 |
    +-------------------------+
    1 row in set (0.00 sec)
    等于1表示为独立表空间模式,等于0则为共享 表空间模式
    共享表空间设置(在搭建MySQL时,初始化数据之前设置到参数文件中)
    [(none)]>select @@innodb_data_file_path;
    [(none)]>show variables like '%extend%';
    innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend
    innodb_autoextend_increment=64
    
    6.1.3独立表空间
    从5.6,默认表空间不再使用共享表空间,替换为独立表空间,主要存储的是用户数据
    存储特点为:一个表一个idb文件,存储数据行和索引
    xxx.frm
    最终结论:
          元数据            数据行+索引
    mysql表数据    =(ibdataX+frm)+ibd(段、区、页)
            DDL             DML+DQL
    
    MySQL的存储引擎日志:
    Redo Log: ib_logfile0  ib_logfile1,重做日志
    Undo Log: ibdata1 ibdata2(存储在共享表空间中),回滚日志
    临时表:ibtmp1,在做join union操作产生临时数据,用完就自动
    
    6.1.4 独立表空间设置问题
    db01 [(none)]>select @@innodb_file_per_table;
    +-------------------------+
    | @@innodb_file_per_table |
    +-------------------------+
    |                      1 |
    +-------------------------+
    alter table city dicard tablespace;
    alter table city import tablespace;
    
    6.1.5 真实的学生案例
    案例背景:
    硬件及软件环境:
    联想服务器(IBM) 
    磁盘500G 没有raid
    centos 6.8
    mysql 5.6.33  innodb引擎  独立表空间
    备份没有,日志也没开
    
    开发用户专用库:
    jira(bug追踪) 、 confluence(内部知识库)    ------>LNMT
    
    
    故障描述:
    断电了,启动完成后“/” 只读
    fsck  重启,系统成功启动,mysql启动不了。
    结果:confulence库在  , jira库不见了
    
    学员求助内容:
    求助:
    这种情况怎么恢复?
    我问:
    有备份没
    求助:
    连二进制日志都没有,没有备份,没有主从
    我说:
    没招了,jira需要硬盘恢复了。
    求助:
    1、jira问题拉倒中关村了
    2、能不能暂时把confulence库先打开用着
    将生产库confulence,拷贝到1:1虚拟机上/var/lib/mysql,直接访问时访问不了的
    
    问:有没有工具能直接读取ibd
    我说:我查查,最后发现没有
    
    
    我想出一个办法来:
    表空间迁移:
    create table xxx
    alter table  confulence.t1 discard tablespace;
    alter table confulence.t1 import tablespace;
    虚拟机测试可行。
    
    处理问题思路:
    confulence库中一共有107张表。
    1、创建107和和原来一模一样的表。
    他有2016年的历史库,我让他去他同时电脑上 mysqldump备份confulence库
    mysqldump -uroot -ppassw0rd -B  confulence --no-data >test.sql
    拿到你的测试库,进行恢复
    到这步为止,表结构有了。
    2、表空间删除。
    select concat('alter table ',table_schema,'.'table_name,' discard tablespace;') from information_schema.tables where table_schema='confluence' into outfile '/tmp/discad.sql';
    source /tmp/discard.sql
    执行过程中发现,有20-30个表无法成功。主外键关系
    很绝望,一个表一个表分析表结构,很痛苦。
    set foreign_key_checks=0 跳过外键检查。
    把有问题的表表空间也删掉了。
    3、拷贝生产中confulence库下的所有表的ibd文件拷贝到准备好的环境中
    select concat('alter table ',table_schema,'.'table_name,' import tablespace;') from information_schema.tables where table_schema='confluence' into outfile '/tmp/discad.sql';
    4、验证数据
    表都可以访问了,数据挽回到了出现问题时刻的状态(2-8)
    

    8.事务的ACID特性

    保证一个单元的语句执行具有ACID的特性。

    针对DML语句增删改insert update delete部分selete
    影响了DML语句
    Atomic(原子性)
    所有语句作为一个单元全部成功执行或全部取消,不能出现中间状态
    
    Consistent(一致性)
    如果数据库在事务开始时处于一致状态,则在执行该事务期间将保留一致状态。
    
    Isolated(隔离性)
    事务之间不互相影响
    
    Durable(持久性)
    事务成功完成后,所做的所有更改都会准确地记录在数据库中。所做的更改不会丢失。
    

    9.事务的生命周期

    9.1 事务的开始
    begin
    说明:在5.5 以上的版本,不需要手工begin,只要你执行的是一个DML,会自动在前面加一个begin命令。
    
    9.2 事务的结束
    commit:提交事务
    完成一个事务,一旦事务提交成功 ,就说明具备ACID特性了。
    rollback :回滚事务
    将内存中,已执行过的操作,回滚回去
    
    9.3 自动提交策略(autocommit)
    db01 [(none)]>select @@autocommit;
    db01 [(none)]>set autocommit=0;
    db01 [(none)]>set global autocommit=0;
    注:
    自动提交是否打开,一般在有事务需求的MySQL中,将其关闭
    不管有没有事务需求,我们一般也都建议设置为0,可以很大程度上提高数据库性能
    (1)
    set autocommit=0;   
    set global autocommit=0;
    (2)
    vim /etc/my.cnf
    autocommit=0
    
    9.4事务的隐式特性
    begin 
    a
    b
    begin         ####会提交
    或执行
    SET AUTOCOMMIT = 1
    也会提交
    导致提交的非事务语句:
    DDL语句: (ALTER、CREATE 和 DROP)
    DCL语句: (GRANT、REVOKE 和 SET PASSWORD)
    锁定语句:(LOCK TABLES 和 UNLOCK TABLES)
    导致隐式提交的语句示例:
    TRUNCATE TABLE
    LOAD DATA INFILE
    SELECT FOR UPDATE
    这些在同一个会话里都会提交会话
    
    
    9.5 开始事务流程:
    1、检查autocommit是否为关闭状态
    select @@autocommit;
    或者:
    show variables like 'autocommit';
    2、开启事务,并结束事务
    begin
    delete from student where name='alexsb';
    update student set name='alexsb' where name='alex';
    rollback;
    
    begin
    delete from student where name='alexsb';
    update student set name='alexsb' where name='alex';
    commit;
    

    10.InnoDB事务的ACID如何保证?

    10.0一些概念
    redo log ---->重做日志 ib_logfile0~1 大小默认50M 轮询使用
    redo log bufferc --->redo内存区域
    ibd     ----->存储数据行和索引
    buffer pool  ---->数据缓冲区池,数据和索引的缓冲
    LSN:日志序列号(数据页变更一次则添加一次序列号,用来控制)
    会有日志序列号的地方:
    磁盘数据页,redo文件,buffer pool, redo buffer 
    MySQL 每次数据库启动,都会比较磁盘数据页和redolog的LSN,必须要求两者LSN一致数据库才能正常启动
    WAL:write ahead log 日志优先写的方式实现持久化
    脏页:内存脏页,内存中发生了修改,没写入到磁盘之前,我们把内存页称之为脏页.
    CKPT:Checkpoint,检查点,将脏页刷写到磁盘中的动作叫做CKPT
    TXID:事务ID,InnoDB会为每一个事务生成一个事务号,伴随着整个事务
    

    10.1redo log

    10.1.1 Redo是什么?
    redo,顾名思义“重做日志”,是事务日志的一种。
    
    10.1.2 作用是什么?
    在事务ACID过程中,实现的是“D”持久化的作用。对于AC也有相应的作用
    
    10.1.3 redo日志位置
    redo的日志文件:iblogfile0 iblogfile1
    
    10.1.4 redo buffer
    redo的buffer:数据页的变化信息+数据页当时的LSN号
    LSN:日志序列号  磁盘数据页、内存数据页、redo buffer、redolog
    
    10.1.5 redo的刷新策略
    commit;
    刷新当前事务的redo buffer到磁盘
    还会顺便将一部分redo buffer中没有提交的事务日志也刷新到磁盘
    
    10.1.6 MySQL CSR——前滚
    MySQL : 在启动时,必须保证redo日志文件和数据文件LSN必须一致, 如果不一致就会触发CSR,最终保证一致
    情况一:
    我们做了一个事务,begin;update;commit.
    1.在begin ,会立即分配一个TXID=tx_01.
    2.update时,会将需要修改的数据页(dp_01,LSN=101),加载到data buffer中
    3.DBWR线程,会进行dp_01数据页修改更新,并更新LSN=102
    4.LOGBWR日志写线程,会将dp_01数据页的变化+LSN+TXID存储到redobuffer
    5. 执行commit时,LGWR日志写线程会将redobuffer信息写入redolog日志文件中,基于WAL原则,
    在日志完全写入磁盘后,commit命令才执行成功,(会将此日志打上commit标记)
    6.假如此时宕机,内存脏页没有来得及写入磁盘,内存数据全部丢失
    7.MySQL再次重启时,必须要redolog和磁盘数据页的LSN是一致的.但是,此时dp_01,TXID=tx_01磁盘是LSN=101,dp_01,TXID=tx_01,redolog中LSN=102
    MySQL此时无法正常启动,MySQL触发CSR.在内存追平LSN号,触发ckpt,将内存数据页更新到磁盘,从而保证磁盘数据页和redolog LSN一值.这时MySQL正长启动
    以上的工作过程,我们把它称之为基于REDO的"前滚操作"
    

    相关文章

      网友评论

          本文标题:DBA之路 7_MySQL_不走索引情况及存储引擎

          本文链接:https://www.haomeiwen.com/subject/gcyeqctx.html