MySQL数据库存储引擎与数据库优化
存储引擎
（1）MySQL可以将数据以不同的技术存储在文件（内存）中，这类技术就成为存储引擎。
每种存数引擎使用不同的存储机制、索引技能、锁定水平，终究提供广泛且不同的功能。
（2）使用不同的存储引擎也能够说不同类型的表
（3）MySQL支持的存储引擎
- MyISAM
- InnoDB
- Memory
- CSV
- 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层。
- 配置文件的优化；
#重要，缓冲池的大小 推荐总内存量的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