DBA之路 7_MySQL_不走索引情况及存储引擎
2019-06-21 本文已影响0人
Linux_淡忘
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的"前滚操作"
