您当前位置：首页 > 数据库 > 数据库应用 > MySQL数据库存储引擎与数据库优化

MySQL数据库存储引擎与数据库优化

来源：程序员人生发布时间：2016-07-01 13:36:51 阅读次数：3154次

存储引擎

（1）MySQL可以将数据以不同的技术存储在文件（内存）中，这类技术就成为存储引擎。

每种存数引擎使用不同的存储机制、索引技能、锁定水平，终究提供广泛且不同的功能。

（2）使用不同的存储引擎也能够说不同类型的表

（3）MySQL支持的存储引擎

1. MyISAM
1. InnoDB
1. Memory
1. CSV
1. Archive

查看数据表的创建语句：

SHOW CREATE TABLE 表名

相干概念：
（1）.并发控制：1个人读数据，另外1个人在删除这个数据。

当多个连接对记录进行修改时保证数据的1致性和完全性。系统使用锁系统来解决这个并发控制，这类锁分为：

1）.同享锁（读锁）—在同1时间内，多个用户可以读取同1个资源，读取进程中数据不会产生任何变化。

2）.排他锁（写锁）—在任什么时候候只能有1个用户写入资源，当进行写锁时会阻塞其他的读锁或写锁操作。

3.锁的力度（也叫锁的颗粒）

锁颗粒(锁定时的单位）

—表锁，是1种开消最小的锁策略。得到数据表的写锁

—行锁，是1种开消最大的锁策略。并行性最大

表锁的开消最小，由于使用锁的个数最小，行锁的开消最大，由于可能使用锁的个数比较多。

并发性

就是多个链接对同1份数据进行操作时，要保证数据的完全性和1致性。

事务的特性 —–》转账业务：从1个人减去 100，另外1个人加上100。

事务（包括1连串的操作，事务（Transaction）是1个对数据库履行工作单元）是为了保护数据的完全性。几个进程作为整体即事务每一个进程出现毛病都恢复到原来的数据

1.原子性（Atomicity）：确保工作单位内的所有操作都成功完成，否则，事务会在出现故障时终止，之前的操作也会回滚到之前的状态。

2.1致性（Consistency)：确保数据库在成功提交的事务上正确地改变状态。

3.隔离性（Isolation）：使事务操作相互独立和透明。

4.持久性（Durability）：确保已提交事务的结果或效果在系统产生故障的情况下依然存在。

ACID；

外键和索引

1、外键：保证数据1致性的策略
2、索引：类似目录，是对数据表中1列或多列的值进行排序的1种结构，方便快速查找到数据

索引：普通索引、唯1索引、全文索引、Btree索引、hash索引……

各种存储引擎的特点

这里写图片描述
使用最多的：MyISAM，InnoDB。

MyISAM：适用于事务的处理不多的情况，支持数据紧缩，容量大；
InnoDB：适用于事务处理比较多，需要有外键支持的情况。

CSV存储引擎：以逗号为分隔符，不支持索引；
BlackHole：黑洞引擎，写入的数据都会消失，1般用于做数据复制的中继；

存储引擎：
MyISAM：存储限制可达256TB,支持索引，表级锁定，数据紧缩
InnoDB: 存储限制为64TB,支持事务和索引，锁颗粒为行锁。

设置存储引擎
（1）通过修改MySQL配置文件实现

default-storage-engine = engine

（2）通过创建数据表命令实现

CREATE TABLE table_name(\
...
) ENGINE = engine;

例如：

CREATE TABLE tp1(
s1 VARCHAR(10)
) ENGINE = MyISAM;
SHOW CREATE TABLE tp1; // 查看数据表的结构

（3）通过修改数据表命令实现

ALTER TABLE table_name ENGINE [=] engine_name;

例如：

ALTER TABLE tp1 ENGINE = InnoDB;

MYSQL数据库优化

1、数据字典的保护

保护数据字典：

1.第3方工具：针对不同的DBMS
2.利用数据库本身的备注字段：对表和列增加备注字段，举例如图

3.导出数据字典（很通用）但是注意：更改表备注时，只需要更改表备注，其
他的1些列的属性（列的长度、宽度、是不是非空）必须保持原样

2、保护索引

建立索引的列：

1、出现在where、group by、order by 从句中的列
2、可选择性高的列放到索引前面（条件列顺序不要求与索引列顺序1致）
3、索引列数据不要太长，（如text进行md5处理）
注意：1、索引不是越多越好（过量的索引也会下降读的效力:多个索引选择的进程）

2、定期保护索引碎片
3、（MySQL)SQL中不要使用强迫索引关键字

3、保护（修改）表结构

注意事项
1、MySQL5.5之前会锁表，可以使用第3方工具；5.6以后本身支持在线表结构变更
2、同时保护数据字典
3、控制表的宽度和大小

合适的操作

1、批量操作(数据库中）逐条操作（利用程序中）
2、尽可能少用”select * “查询
3、控制使用用户自定义函数（使用函数，索引不起作用）
4、不要使用全文索引（中文支持不好，需要另建索引文件）

4、数据表的水平拆分和垂直拆分

垂直拆分：为了控制表的宽度

这里写图片描述

水平拆分：为了控制表的数据量

这里写图片描述

表示2维的是个平面，上面的情况是非常容易想一想的，问题的关键是要依托1定原则了！
目标是不变的：为了效力、为了可保护性、为了更快更省事！

SQL查询语句优化

explain分析sql的履行计划，并找出sql需要优化的地方

table：表名；
type：连接的类型，const、eq_reg、ref、range、index和ALL；const：主键、索引；eq_reg：主键、索引的范围查找；ref：连接的查找（join），
range：索引的范围查找；index：索引的扫描；
possible_keys：可能用到的索引；
key：实际使用的索引；
key_len：索引的长度，越短越好；
ref：索引的哪1列被使用了，常数较好；
rows：mysql认为必须检查的用来返回要求数据的行数；
extra：using filesort、using temporary（常出现在使用order by时）时需要优化。

Max()和Count()的优化

1.对max()查询，可以为表创建索引，create index index_name on table_name(column_name 规定需要索引的列)，这里就是以付款的日期为索引；,然后在进行查询。

如果没有索引，查询的可能1直到最后1行。
这里写图片描述

2.count()对多个关键字进行查询，比如在1条SQL中同时查出2006年和2007年电影的数量，语句：
这里写图片描述

select count(release_year='2006' or null) as '2006年电影数量',
count(release_year='2007' or null) as '2007年电影数量'
from film;

count(*)时会包括null空这1列，而count(id)这类写法将不包括null这1列.

3.子查询的优化

把子查询改成左连接查询，但是如果两张表里存在1对多的情况，左连接查询结果会出现，所以要使用distinct去掉重复记录

select * from table1 where table1.column1 in (select table2.column2 from table2);
select distinct table1.column1 from table1 join table2 on table1.column1=table2.column2;

4.order by语句优化
group by可能会出现临时表（Using temporary），文件排序（Using filesort）等，影响效力。
可以通过关联的子查询，来避免产生临时表和文件排序，可以节省io
改写前

select actor.first_name,actor.last_name,count(*)
from sakila.film_actor
inner join sakila.actor using(actor_id)
group by film_actor.actor_id;

改写后

select actor.first_name,actor.last_name,c.cnt
from sakila.actor inner join(
select actor_id,count(*) as cnt from sakila.film_actor group by
actor_id
)as c using(actor_id);

5.limit 语句优化

limit经常使用于分页处理，经常会伴随order by从句使用，因此大多时候会使用Filesorts这样会造成大量的io问题

1.使用有索引的列或主键进行order by操作

2.记录上次返回的主键，在下次查询时使用主键过滤
使用这类方式有1个限制，就是主键1定要顺序排序和连续的，如果主键出现空缺可能会致使终究页面上显示的列表不足5条，解决办法是附加1列，保证这1列是自增的并增加索引就能够了

6.选择适合的索引列

1.在where，group by，order by，on从句中出现的列

2.索引字段越小越好(由于数据库的存储单位是页，1页中能存下的数据越多越好 )

3.离散度大得列放在联合索引前面

select count(distinct customer_id), count(distinct staff_id) from payment;

查看离散度通过统计不同的列值来实现 count越大离散程度越高

过量的索引不但影响写入，而且影响查询，索引越多，分析越慢
如何找到重复和过剩的索引，主键已是索引了，所以primay key 的主键不用再设置unique唯1索引了

冗余索引，是指多个索引的前缀列相同，innodb会在每一个索引后面自动加上主键信息
这里写图片描述

冗余索引查询工具
pt-duplicate-key-checker

由于业务变更有些原来使用的索引现在不使用了也是需要清除的，这也是索引优化的1个方面了！有些索引的使用的频率很低，乃至没用过。
注意：作者再次的强调SQL和索引的优化对数据库的优化是相当重要的，这1层的优化如果做好了，其他的优化也能起到1些作用否则其他的优化所能起到的作用是微不足道的，这1层的优化也是本钱最低效果最好的1层了，所以对数据库的优化最好重点放在这1层。

配置文件的优化；

#重要，缓冲池的大小 推荐总内存量的75%，越大越好。
innodb_buffer_pool_size

#默许只有1个缓冲池，如果1个缓冲池中并发量过大，容易阻塞，此时可以分为多个缓冲池；
innodb_buffer_pool_instances

#log缓冲的大小，1般最常1s就会刷新1次，故不用太大；
innodb_log_buffer_size

#重要，对io效力影响较大。0:1s刷新1次到磁盘；1：每次提交都会刷新到磁盘；2：每次提交刷新到缓冲区，1s刷新到磁盘；默许为1。
innodb_flush_log_at_trx_commit

#读写的io进程数量，默许为4
innodb_read_io_threads
innodb_write_io_threads

#重要，控制每一个表使用独立的表空间，默许为OFF，即所有表建立在1个同享的表空间中。
innodb_file_per_table

#mysql在甚么情况下会刷新表的统计信息，1般为OFF。
innodb_stats_on_metadata

生活不易，码农辛苦
如果您觉得本网站对您的学习有所帮助,可以手机扫描二维码进行捐赠
程序员人生

------分隔线----------------------------

上一篇 有图有故事－ Watson Analytics 解析幸福指数

下一篇 Android中asset文件夹和raw文件夹

分享到:

------分隔线----------------------------

为码而活

积分：4237

15粉丝

7关注

栏目热点