mysql事务、索引、优化总结_优化索引、事务处理

Mysql事务和优化总结
一、mysql事务
1、原理：
在进行数据的insert、update、delete等复杂的业务处理时，要不业务处理每个环节都成功，要么都失败。在mysql中使用了Innodb数据库引擎的数据库表才支持事务
2、事务的特点：
（1）、一致性
在事务开启之前和事务结束之后，数据库的完整性没有被破坏。
（2）、原子性
一个事务（transaction）中的所有操作，要么全部成功，要么全部失败。
（3）、隔离性
数据库允许多个并发操作同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。

（4）、持久性
事务处理结束后，对数据的修改就是永久的
3、事务的隔离级别
（1）、读未提交的read uncommitted
例如存在事务A和B,事务A没有提交的数据，事务B可以读取到，这样就造成了脏读。这种隔离级别最低，理论上存在，实际情况中不会使用。
（2）、读已提交的read committed
例如存在事务A和B,事务A提交的数据，事务B才可以读取到，防止了脏读。这种隔离级别高于读位提交的，但是会导致不可重复读取，Oracle默认的隔离级别
（3）可重复读repeatable read
例如存在事务A和B,事务A提交的数据，事务B读取不到，事务B是可重复读取数据。这种隔离级别高于读已提交，这种隔离级别可以避免“不可重复读取”，达到可重复读取， 虽然可以达到可重复读取，但是会导致“幻像读”。是mysql默认的隔离级别

（4）串行化serializable
例如存在事务A和B,事务A在操作数据时，事务B只能排队等待，这种隔离级别很少使用，吞吐量太低，用户体验差。这种级别可以避免“幻像读”，每一次读取的都是数据库中真实存在数据，事务A与事务B串行，而不并发
4、MYSQL 事务设置
（1）、用 BEGIN, ROLLBACK, COMMIT来实现
BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认
（2）、直接用 SET 来改变 MySQL 的自动提交模式:
SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交
5、MYSQL 隔离级别设置
（1）修改my.ini配置（永久修改）
– READ-UNCOMMITTED //读取未提交的
– READ-COMMITTED //读取已提交的
– REPEATABLE-READ //不可重复读
– SERIALIZABLE //串行化

• 例如：
[mysqld]
transaction-isolation = READ-COMMITTED
（2）通过set命令动态设置隔离级别（临时修改）
– READ UNCOMMITTED
– READ COMMITTED
– REPEATABLE READ
– SERIALIZABLE
• 例如： SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
6、隔离级别的作用范围
（1）全局级：对所有的会话有效
例如，设置会话级隔离级别为READ COMMITTED ：
mysql> SET TRANSACTION ISOLATION LEVEL READ COMMITTED；
或：
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED；
（2）会话级：只对当前的会话有效
例如，设置全局级隔离级别为READ COMMITTED ：
mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED；
二、mysql数据结构存储引擎
1、MyISAM（B+tree）
MyISAM存储引擎的特点是：表级锁、不支持事务和全文索引，适合一些CMS内容管理系统作为后台数据库使用，但是使用大并发、重负荷生产系统上，表锁结构的特性就显得力不从心；
MyISAM适合：

（1）做很多count 的计算；
（2）插入不频繁，查询非常频繁，如果执行大量的SELECT，MyISAM是更好的选择；
（3）没有事务。
2、InnoDB（B+tree）
InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键，mysql5.5以后成为mysql的默认引擎
InnoDB适合：
（1）可靠性要求比较高，或者要求事务；
（2）表更新和查询都相当的频繁，并且表锁定的机会比较大的情况指定数据引擎的创建；
（3）如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表；
（4）DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的 删除；
（5）LOAD TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性（例如外键）的表不适用。
3、MEMORY（hash）
如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎，MySQL中使用该引擎作为临时表，存放查询的中间结果。数据的处理速度很快但是安全性不高。
4、Archive
如果只有INSERT和SELECT操作，可以选择Archive，Archive支持高并发的插入操作，但是本身不是事务安全的。Archive非常适合存储归档数据，如记录日志信息可以使用Archive
三、mysql优化

1、索引
（1）、查看表语句
查看表上的索引：show indexes from xxx(表名);
查看创表语句：show create table xxx(表名);
查看数据库的所有连接：show processlist;
查询sql执行时间：SHOW PROFILES;
（2）、主键索引
创建：创建表的时候指定主键索引 其中primary key是指定主键索引
create table table_primarykey
( id int primary key auto_increment ,
name varchar(20)
);
说明：一般情况InnoDB引擎和MyISAM引擎底层使用B+树来存储数据，这种两种存储引擎要求必须存在主键，因为像InnoDB数据时存储在主键索引文件中的，而MyISAM数据地址是存在主键索引中的，mysql在创建数据库表的时候如果没有创建主键，开始找唯一索引如果唯一索引也没有，数据底层会生成一个主键。主键索引的特点和底层数据存储结构决定了主键索引查询效率比较高，因为他的叶子节点存储了数据不会造成回表。
（2）、普通索引
创建：给表中的某一列添加普通索引
（1）创建表的时候创建普通索引：CREATE TABLE xxx(表名)(
xxx(列名),
INDEX(xxx)
);
（2）后期添加普通索引：alter table xxx(表名) add index xxx(索引名)(xxx(列名))
（3）、唯一索引
创建：和主键索引相似
（1）创建表的时候创建唯一索引
CREATE TABLE xxx(表名)(
xxx(列名),
UNIQUE INDEX(xxx)
);
（2）后期添加唯一索引

ALTER TABLE xxx(表名) ADD UNIQUE (xxx(列名))
（4）、全文索引
创建：myisam支持 FULLTEXT类型的全文索引。innodb不支持FULLTEXT类型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。(sphinx 是一个开源软件，提供多种语言的API接口，可以优化mysql的各种查询)
（1）创建表的时候创建：
CREATE FULLTEXT INDEX xxx(索引名) ON xxx(表名) (name) with parser ngram;
（2）后期添加全文索引：
alter table xxx(表名) add fulltext index xxx(索引名)(clumn1,clumn2) with parser ngram;
（5）、组合索引
（1）创建表的时候创建：

CREATE TABLE xxx(表名)(
xxx(列名),
xxx(列名),
KEY sindex (xxx,xxx)
);
（2）后期添加组合索引：
alert table xxx(表名) add INDEX xxx(索引名) (xxx,xxx,xxx)
2、查询执行计划
（1）执行计划
Explain select * from OMS_TASK_INFO

（2）执行计划重要的关键字段
id 这个值越大优先执行
type （很重要，用来查看索引执行情况）system 、const、ref、index、range、all 按顺序查询效率递减，所以在写sql的时候最低要保证在range级别，要不就会进行全表扫描
possible_key :所有可能使用的所有key
key:实际使用的索引key
Key_len:索引字段长度 （和编码有关系utf-8 加3 ，gbk 加2）
rows:表中全表数据（可能不准）
filtered: 实际查询到的数据
extra: 索引执行情况（using where（按where条件查询） ， using index（覆盖索引），using index condition（索引下推）, using filesort（无法按索引排序，只能利用排序算法排序），using temporary（建立临时表来保存中间结果，查询完后把临时数据删除），using join buffer （使用连接缓存） ）
3、回表
当使用其他索引进行数据查询的时候，叶子节点中不包含整行的数据值，需要去主键索引中进行数据查询的过程叫做回表
4、覆盖索引
当查询的索引数据结构中包含要查询的所有数据时，不需要进行回表，此时为覆盖索引（我们在应该多使用覆盖索引）
5、索引下推
Mysql5.7版本才加入的特征，例如在一个查询语句中包含两个查询条件A和B，索引下推是指在存储引擎中把所有的条件过滤都完成，而不用在server中去做条件过滤（特点效率高，减少io次数）
6、哈希索引
memory引擎使用哈希索引，缺点内存消耗大、hash碰撞、不能支持范围查询,优点查询插入效率最高。innodb是自适应hash,是数据库底层自己处理的。研发人员无需关心
7、索引匹配方式
（1）全值匹配
全职匹配指的是和索引中的所有列进行匹配
（2）最左匹配
按照从左到右一次匹配，只要其中的某一个条件断了，后面的都不能走索引
（3）匹配列前缀
可以匹配某一列值得开头部分 例如 like ‘xxxx%’
（4）匹配范围值
如果其中包含隐士类型转换可能造成不走索引，例如字段类型为varchar时查询的条件为int或者什么的类型，在查询的过程中server中的优化器进行优化的时候会做隐士类型转换，此时会造成索引失效
8、索引优化
1、当使用索引进行查询的时候尽量不要使用表达式，把计算放到业务层而不是数据库层。
2、尽量使用主键查询，而不是其他索引，因为主键索引不会触发回表
3、使用前缀索引例如
4、使用索引扫描来排序
5、union all ,in ,or 都能够使用索引，但推荐使用in
6、范围列可以使用索引：<、<=、>、>=、between ,使用范围索引，范围列后面的列无法使用索引，索引最多使用一个范围。
7、更新比较频繁的列区分度不高的列不适合建索引（例如 男、女）
8、创建索引的列不允许为null,可能得不到预期的结果
9、当需要进行表连接的时候，最好不要超过三张，且关联时候join的字段必须类型一致，要不然会放弃索引
10、能使用分页limit尽量使用
11、单表索引最好不要超过5个字段
12、一个索引名称建立组合索引最多不要超过5个字段