存储引擎、索引、注意事项、慢日志查询、大数据量分页优化第四十二天 2018.12.05

上节回顾

全栈---->前端、后端、数据库

视图---->虚拟的表(临时表永久的存储起来)

触发器---->监视某种情况，执行某种动作地点、事件(触发条件)、时间(前、后、后置使用多)、动作(增、删、改)

存储过程---->把一堆SQL语句封装成类似函数的过程、优先编译(第一次使用时间编译、后面不需要再次编译)效率高

函数---->内置函数、自定义函数开发过程中使用不多(开发过程中直接使用后端函数即可)

函数与存储过程的区别

　　1、声明不一样函数(function) 存储过程(procedure)

　　2、函数一般用于运算存储过程用于执行任务

　　3、函数要求必须有一个返回值存储过程可以没返回值还可以有多个返回值

　　4、函数不可以单独使用必须配合增删改查语句使用存储过程不能配合增删改查语句

事物---->4个特性原子性、一致性、隔离性、持久性(面试题)

锁---->悲观锁、乐观锁悲观锁(开启事物，在SQL查询语句后加 for update 是否有主键)

库---->右键---->逆向数据库模型(把整数据库所有的表罗列出来包括表之间的关系)

数据库的备份

　　导出表转储SQL---->库---->右键---->转储SQL文件(结构和数据、仅结构)

正则(和like关键字基本一样、like功能单一)

MySQL存储引擎

存储引擎

存储引擎 : 其实就是指定表如何存储数据，如何为存储的数据建立索引以及如何更新，查询数据等技术实现的方法。

　　　　因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）

MySQL存储引擎比较

MyISAM

MyISAM 　　是MySQL (mysql 5.5版本以前) 原来的默认存储引擎.

MyISAM　　这种存储引擎不支持事务，不支持行级锁，只支持并发插入的表锁

MyISAM 　　类型的表支持三种不同的存储结构：静态型、动态型、压缩型。

　　静态型：就是定义的表列的大小是固定（即不含有：xblob、xtext、varchar等长度可变的数据类型)---->用空间换取效率

　　动态型：如果列（即使只有一列）定义为动态的（xblob, xtext, varchar等数据类型）---->用效率换取空间

　　压缩型：如果在这个数据库中创建的是在整个生命周期内只读的表，则这种情况就是用myisam的压缩型表来减少空间的占用。---->压缩后只能查询、不能增删改

InnoDB

MySQL默认存储引擎(MySQL 5.5 版本后，因为innodb被收购了)

innodb 　　支持自增长列（auto_increment）,自增长列的值不能为空，(一个表只允许存在一个自增,并且要求自增列必须为索引)

innodb 　　支持事务，回滚以及系统崩溃修复能力和多版本迸发控制的事务的安全

innodb 　　支持外键（foreign key） ,外键所在的表称为子表,而所依赖的表称为父表

innodb　　存储引擎支持行级锁

重点

　　大容量的数据集时趋向于选择Innodb。因为它支持事务处理和故障的恢复。Innodb可以利用数据日志来进行数据的恢复。

　　主键的查询在Innodb也是比较快的。

　　大批量的插入语句时（这里是INSERT语句）在MyISAM引擎中执行的比较的快，但是UPDATE语句在Innodb下执行的会比较的快，尤其是在并发量大的时候。

存储引擎的异同

两种引擎索引数据结构:都是B+树

MyISAM引擎，B+树的数据结构中存储的内容实际上是实际数据的地址值。也就是说它的索引和实际数据是分开的，

　　　　　　只不过使用索引指向了实际数据。这种索引的模式被称为非聚集索引。

Innodb引擎的索引的数据结构也是B+树，只不过数据结构中存储的都是实际的数据，这种索引有被称为聚集索引。

Memory存储引擎（之前称为Heap）将表中数据存放在内存中，如果数据库重启或崩溃，数据丢失，因此它非常适合存储临时数据。

Archive非常适合存储归档数据，如日志信息。它只支持INSERT和SELECT操作，其设计的主要目的是提供高速的插入和压缩功能。

NDB存储引擎是一个集群存储引擎，类似于Oracle的RAC，但它是Share Nothing(非共享)的架构，

　　　　因此能提供更高级别的高可用性和可扩展性NDB的特点是数据全部放在内存中，因此通过主键查找非常快。

innodb 　　 .frm 表结构　　 .ibd数据文件

myisam　 .frm 表结构　　 .myd存储数据文件　　 .myi存储索引文件

net stop mysql　　net start mysql 用来测试Memory存储引擎

重点[面试题] innodb与MyISAM存储引擎的区别:

　　1.innodb　　是mysql5.5版本以后的默认存储引擎, 而MyISAM是5.5版本以前的默认存储引擎

　　2.innodb　　支持事物,而MyISAM不支持事物

　　3.innodb 　　支持行级锁.而MyIASM 它支持的是并发的表级锁

　　4.innodb 　　支持外键, 而MyIASM 不支持外键

　　5.innodb　　与MyISAM存储引擎都采用B+TREE存储数据, 但是innodb的索引与数据存储在一个文件中,这种方式我们称之为聚合索引

　　　　　　　　而MyISAM则会单独创建一个索引文件,也就是说,数据与索引是分离开的

　　6.在效率方面MyISAM比innodb高,但是在性能方面innodb要好一点

MySQL---->索引(index)

一般的应用系统，读写比例在10:1左右

索引本质：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，

　　　　　有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。

hash类型的索引：查询单条快，范围查询慢

btree类型的索引：b+树，层数越多，数据量越大,范围查询和随机查询快（innodb默认索引类型）

B+TREE 索引

B+树是一种经典的数据结构，由平衡树和二叉查找树结合产生，它是为磁盘或其它直接存取辅助设备而设计的一种平衡查找树

系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的，位于同一磁盘块中的数据会被一次性读取出来，而不是按需读取。

强烈注意: 索引字段要尽量的小,磁盘块可以存储更多的索引

HASH 索引

hash就是一种（key=>value）形式的键值对,允许多个key对应相同的value，但不允许一个key对应多个value

hash类型的索引：查询单条快，范围查询慢

btree类型的索引：b+树，层数越多，数据量越大,范围查询和随机查询快（innodb默认索引类型）

索引类型(普通、唯一、主键、组合)

1、Table 表的名称。

2、 Non_unique 如果索引为唯一索引,则为0,如果可以则为1。

3、 Key_name 索引的名称

4、 Seq_in_index 索引中的列序列号，从1开始。

5、 Column_name 列名称。

6、 Collation 列以什么方式存储在索引中。在MySQL中，有值‘A’（升序）或NULL（无分类）。

7、Cardinality 索引中唯一值的数目的估计值。

8、Sub_part 如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。

9、 Packed 指示关键字如何被压缩。如果没有被压缩，则为NULL。

10、 Null 如果列含有NULL，则含有YES。如果没有，则该列含有NO。

11、 Index_type 用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）。

12、 Comment 多种评注

查看索引列介绍

普通索引(加速查询)

普通索引仅有一个功能---->加速查询

#创建表同时添加name字段为普通索引
create table tb1(
   id int not null auto_increment primary key,
   name varchar(100) not null,
   index idx_name(name)  
);

创建表+索引

key和index都为普通索引 idx表示索引 idx_name为索引名称 idx为index的缩写显得比较专业

create index idx_name on tb1(name);

创建索引

drop index idx_name on tb1;

删除索引

show index from tb1;

查看索引

唯一索引(加速查找+唯一约束，可以存储一个null值)

create table tb2(
  id int not null auto_increment primary key,
  name varchar(50) not null,
  age int not null,
  unique index idx_age (age)   
)

创建表+唯一(unique)索引

create unique index idx_age on tb2(age);

创建唯一索引

主键索引(主键索引加速查找+唯一约束，不能存储null值)

一个表最多只能有一个主键索引

#方式一:
create table tb3(
   id int not null auto_increment primary key,
   name varchar(50) not null,
   age int default 0 
);

#方式二:
create table tb3(
   id int not null auto_increment,
   name varchar(50) not null,
   age int default 0 ,
   primary key(id)
);

创建表 + 创建主键

alter table tb3 add primary key(id);

创建主键

#方式一
alter table tb3 drop primary key;

#方式二:
#如果当前主键为自增主键,则不能直接删除.需要先修改自增属性,再删除

alter table tb3 modify id int ,drop primary key;

删除主键

组合索引(加速查找)

组合索引是将n个列组合成一个索引

其应用场景为：频繁的同时使用n列来进行查询，如：where n1 = 'alex' and n2 = 666。

create table tb4(
  id int not null ,
  name varchar(50) not null,
  age int not null,
  index idx_name_age (name,age)   
)

创建表+组合索引

create index idx_name_age on tb4(name,age);

创建组合索引

举个例子来说，比如你在为某商场做一个会员卡的系统。

这个系统有一个会员表
有下列字段：
会员编号 INT
会员姓名 VARCHAR(10)
会员身份证号码 VARCHAR(18)
会员电话 VARCHAR(10)
会员住址 VARCHAR(50)
会员备注信息 TEXT

那么这个 会员编号，作为主键，使用 PRIMARY
会员姓名 如果要建索引的话，那么就是普通的 INDEX
会员身份证号码 如果要建索引的话，那么可以选择 UNIQUE （唯一的，不允许重复）

索引应用场景

组合索引应用场景

聚合索引和辅助索引

测试索引

创建表---->用存储过程创建数量大的表

注意:MYISAM存储引擎不产生引擎事务，数据插入速度极快，为方便快速插入测试数据，等我们插完数据，再把存储类型修改为InnoDB

-- 1.创建表
CREATE TABLE userInfo(
    id int NOT NULL,
    name VARCHAR(16) DEFAULT NULL,
    age int,
    sex char(1) not null,
    email varchar(64) default null
)ENGINE=MYISAM DEFAULT CHARSET=utf8;

创建表

用存储过程插入数据(函数---->计算)

-- 2.创建存储过程
delimiter$$
CREATE PROCEDURE insert_user_info(IN num INT)
BEGIN
    DECLARE val INT DEFAULT 0;
    DECLARE n INT DEFAULT 1;
    -- 循环进行数据插入
    WHILE n <= num DO
        set val = rand()*50;
        INSERT INTO userInfo(id,name,age,sex,email)values(n,concat('alex',val),rand()*50,if(val%2=0,'女','男'),concat('alex',n,'@qq.com'));
        set n=n+1;
    end while;
END $$
delimiter;

#delimiter$$ 把结束符换成$$
#执行完后把结束符换回;

创建存储过程

调用存储过程插入数据

call insert_user_info(5000000);

插入数据

插入数据量大时第一次需要时间久

可以将存储引擎，修改为INNODB

ALTER TABLE userinfo ENGINE=INNODB;

修改存储引擎类型

测试索引

在没有索引的前提下测试查询速度

SELECT * FROM userinfo WHERE id = 4567890;

无索引情况,mysql根本就不知道id等于4567890的记录在哪里，只能把数据表从头到尾扫描一遍，此时有多少个磁盘块就需要进行多少IO操作，所以查询速度很慢

在表中已经存在大量数据的前提下，为某个字段段建立索引，建立速度会很慢　　

CREATE INDEX idx_id on userinfo(id);

在索引建立完毕后，以该字段为查询条件时，查询速度提升明显

select * from userinfo where id  = 4567890;

注意:

1. mysql先去索引表里根据b+树的搜索原理很快搜索到id为4567890的数据,IO大大降低，因而速度明显提升

2. 我们可以去mysql的data目录下找到该表，可以看到添加索引后该表占用的硬盘空间多了　

3. 如果使用没有添加索引的字段进行条件查询,速度依旧会很慢(如图:)

正确使用索引

数据库表中添加索引后确实会让查询速度起飞，但前提必须是正确的使用索引来查询，如果以错误的方式使用，则即使建立索引也不会生效

#1. 范围查询(>、>=、<、<=、!= 、between...and)
    #1. = 等号
    select count(*) from userinfo where id = 1000 -- 执行索引,索引效率高
    
    #2. > >= < <= between...and 区间查询
    select count(*) from userinfo where id <100; -- 执行索引,区间范围越小,索引效率越高
    
    select count(*) from userinfo where id >100; -- 执行索引,区间范围越大,索引效率越低
    
    select count(*) from userinfo where id between 10 and 500000; -- 执行索引,区间范围越大,索引效率越低
    
   #3. != 不等于
   select count(*) from userinfo where id != 1000;  -- 索引范围大,索引效率低
   
   
#2.like '%xx%'
    #为 name 字段添加索引
    create index idx_name on userinfo(name);
    
    select count(*) from userinfo where name like '%xxxx%'; -- 全模糊查询,索引效率低
    select count(*) from userinfo where name like '%xxxx';   -- 以什么结尾模糊查询,索引效率低
  
    #例外: 当like使用以什么开头会索引使用率高
    select * from userinfo where name like 'xxxx%'; 

#3. or 
    select count(*) from userinfo where id = 12334 or email ='xxxx'; -- email不是索引字段,索引此查询全表扫描
    
    #例外：当or条件中有未建立索引的列才失效，以下会走索引
    select count(*) from userinfo where id = 12334 or name = 'alex3'; -- id 和 name 都为索引字段时, or条件也会执行索引

#4.使用函数
    select count(*) from userinfo where reverse(name) = '5xela'; -- name索引字段,使用函数时,索引失效
    
    #例外:索引字段对应的值可以使用函数,我们可以改为一下形式
    select count(*) from userinfo where name = reverse('5xela');

#5.类型不一致
    #如果列是字符串类型，传入条件是必须用引号引起来，不然...
    select count(*) from userinfo where name = 454;
        
    #类型一致
    select count(*) from userinfo where name = '454';

#6.order by
    #排序条件为索引，则select字段必须也是索引字段，否则无法命中  
    select email from userinfo ORDER BY name DESC; -- 无法命中索引

    select name from userinfo ORDER BY name DESC;  -- 命中索引
        
    #特别的:如果对主键排序，则还是速度很快：
    select id from userinfo order by id desc;

组合索引

组合索引: 是指对表上的多个列组合起来做一个索引.

组合索引好处:简单的说有两个主要原因：

"一个顶三个"。建了一个(a,b,c)的组合索引，那么实际等于建了(a),(a,b),(a,b,c)三个索引，因为每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，这可是不小的开销！

索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql:select * from table where a = 1 and b =2 and c = 3,假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W*10%=100w 条数据，然后再回表从100w条数据中找到符合b=2 and c= 3的数据，然后再排序，再分页；如果是组合索引，通过索引筛选出1000w *10% *10% *10%=1w，然后再排序、分页，哪个更高效，一眼便知

最左匹配原则: 从左往右依次使用生效，如果中间某个索引没有使用，那么断点前面的索引部分起作用，断点后面的索引没有起作用；

select * from mytable where a=3 and b=5 and c=4;
　  #abc三个索引都在where条件里面用到了，而且都发挥了作用

select * from mytable where  c=4 and b=6 and a=3;
　　#这条语句列出来只想说明 mysql没有那么笨，where里面的条件顺序在查询之前会被mysql自动优化，效果跟上一句一样

select * from mytable where a=3 and c=7;
　　#a用到索引，b没有用，所以c是没有用到索引效果的

select * from mytable where a=3 and b>7 and c=3;
　　#a用到了，b也用到了，c没有用到，这个地方b是范围值，也算断点，只不过自身用到了索引

select * from mytable where b=3 and c=4;
　　#因为a索引没有使用，所以这里 bc都没有用上索引效果

select * from mytable where a>4 and b=7 and c=9;
　　#a用到了  b没有使用，c没有使用

select * from mytable where a=3 order by b;
　　#a用到了索引，b在结果排序中也用到了索引的效果

select * from mytable where a=3 order by c;
　　#a用到了索引，但是这个地方c没有发挥排序效果，因为中间断点了

select * from mytable where b=3 order by a;
　　#b没有用到索引，排序中a也没有发挥索引效果

组合索引示例规则

注意事项

1. 避免使用select *

2. 其他数据库中使用count(1)或count(列) 代替 count(*),而mysql数据库中count(*)经过优化后,效率与前两种基本一样.

3. 创建表时尽量时 char 代替 varchar

4. 表的字段顺序固定长度的字段优先

5. 组合索引代替多个单列索引（经常使用多个条件查询时）

6. 使用连接（JOIN）来代替子查询(Sub-Queries)

7. 不要有超过4个以上的表连接（JOIN）

8. 优先执行那些能够大量减少结果的连接。

9. 连表时注意条件类型需一致

10.索引散列值不适合建索引，例：性别不适合

查询计划

explain + 查询SQL - 用于显示SQL执行信息参数，根据参考信息可以进行SQL优化

执行计划：让mysql预估执行操作(一般正确)
　　type : 查询计划的连接类型, 有多个参数，先从最佳类型到最差类型介绍

　　性能： null > system/const > eq_ref > ref > ref_or_null > index_merge >  range > index >  all 

    慢：
        explain select * from userinfo where email='alex';
        type: ALL(全表扫描)
        特别的: select * from userinfo limit 1;
    快：
        explain select * from userinfo where name='alex';
        type: ref(走索引)

EXPLAIN 参数详解: http://www.cnblogs.com/wangfengming/articles/8275448.html

慢日志查询(variables)

将mysql服务器中影响数据库性能的相关SQL语句记录到日志文件，通过对这些特殊的SQL语句分析，改进以达到提高数据库性能的目的。

慢日志查询参数

long_query_time     ：  设定慢查询的阀值，超出设定值的SQL即被记录到慢查询日志，缺省值为10s
slow_query_log      ：  指定是否开启慢查询日志
log_slow_queries    ：  指定是否开启慢查询日志(该参数已经被slow_query_log取代，做兼容性保留)
slow_query_log_file ：  指定慢日志文件存放位置，可以为空，系统会给一个缺省的文件host_name-slow.log
log_queries_not_using_indexes: 如果值设置为ON，则会记录所有没有利用索引的查询.

慢日志常见操作

查看 MySQL慢日志信息

#.查询慢日志配置信息 :
show variables like '%query%';
#.修改配置信息
set global slow_query_log  = on;

查看不使用索引参数状态:

# 显示参数　　
show variables like '%log_queries_not_using_indexes';
# 开启状态
set global log_queries_not_using_indexes  = on;

查看慢日志显示的方式

#查看慢日志记录的方式
show variables like '%log_output%';
 
#设置慢日志在文件和表中同时记录
set global log_output='FILE,TABLE';

测试慢查询日志

#查询时间超过10秒就会记录到慢查询日志中
select sleep(3) FROM user ;
 
#查看表中的日志
select * from mysql.slow_log;

大数据量化分页优化　　

执行此段代码:

select * from userinfo limit 3000000,10;

一. 简单粗暴，就是不允许查看这么靠后的数据，比如百度就是这样的　　

　最多翻到72页就不让你翻了，这种方式就是从业务上解决；　

二.在查询下一页时把上一页的行id作为参数传递给客户端程序

然后代码就改成为:

select * from userinfo where id>3000000 limit 10;

这条语句执行也是在毫秒级完成的，id>300w其实就是让mysql直接跳到这里了，不用依次在扫描全面所有的行

如果你的table的主键id是自增的，并且中间没有删除和断点，那么还有一种方式，比如100页的10条数据

select * from userinfo where id>100*10 limit 10;

三.延迟关联　　

select * from userinfo limit 3000000,10;

玄机就处在这个 * 里面，这个表除了id主键肯定还有其他字段比如 name age 之类的，

因为select * 所以mysql在沿着id主键走的时候要回行拿数据，走一下拿一下数据；　　

如果把语句改成

select id from userinfo limit 3000000,10;

你会发现时间缩短了一半；然后我们在拿id分别去取10条数据就行了；

语句就改成这样了：

select table.* from userinfo inner join ( select id from userinfo limit 3000000,10 ) as tmp on tmp.id=userinfo.id;

这三种方法最先考虑第一种其次第二种，第三种是别无选择　　

posted @ 2018-12-05 21:29 KK筑梦人阅读(195) 评论(0) 收藏举报

刷新页面返回顶部

存储引擎、索引、注意事项、慢日志查询、大数据量分页优化 第四十二天 2018.12.05

上节回顾

函数与存储过程的区别

MySQL存储引擎

存储引擎

MySQL存储引擎比较

MyISAM

InnoDB

重点

存储引擎的异同

重点[面试题] innodb与MyISAM存储引擎的区别:

MySQL---->索引(index)

B+TREE 索引

HASH 索引

索引类型(普通、唯一、主键、组合)

普通索引(加速查询)

唯一索引(加速查找+唯一约束，可以存储一个null值)

主键索引(主键索引 加速查找+唯一约束，不能存储null值)

组合索引(加速查找)

聚合索引和辅助索引

测试索引

创建表---->用存储过程创建数量大的表

测试索引

正确使用索引

组合索引

注意事项

查询计划

慢日志查询(variables)

慢日志查询参数

慢日志常见操作

大数据量化分页优化

一. 简单粗暴，就是不允许查看这么靠后的数据，比如百度就是这样的

二.在查询下一页时把上一页的行id作为参数传递给客户端程序

三.延迟关联

存储引擎、索引、注意事项、慢日志查询、大数据量分页优化第四十二天 2018.12.05

主键索引(主键索引加速查找+唯一约束，不能存储null值)

大数据量化分页优化　　

一. 简单粗暴，就是不允许查看这么靠后的数据，比如百度就是这样的　　

三.延迟关联　　