程序员人生 网站导航

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

栏目:数据库应用时间:2016-07-01 13:36:51

存储引擎

(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需要优化的地方

explain select customer_id,first_name,last_name from customer;
+—-+————-+———-+——+—————+——+———+——+——+——-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+———-+——+—————+——+———+——+——+——-+
| 1 | SIMPLE | customer | ALL | NULL | NULL | NULL | NULL | 599 | NULL |
+—-+————-+———-+——+—————+——+———+——+——+——-+

  • 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层。

  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

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

最新技术推荐