您现在的位置是：首页 >技术杂谈 >mysql索引和事务网站首页技术杂谈

mysql索引，事务与存储引擎

数据库索引概念

是一个排序的列表，存储着索引的值和包含着这个值的数据所在行的物理地址（类似于C语言指针指向内存地址）

索引还是表中一个或若干列表排序的方法

索引作用：

（直接记录下来要查表行的物理地址加快数据库的查询速度对表记录的查找，mysql查整个表，再定位某行数据。）

降低数据库的io成本和排序成本（升序排序）

创建唯一性索引，可以保证数据表中每行数据的唯一性。

可以加快表与表的连接速度（多表时）

在使用分组和排序时，可大大减少分组和排序的时间

grop by

order by

索引副作用

要占用额外的磁盘空间（myISAM引擎，索引文件和数据文件分离，索引文件用于保护数据记录的地址innoDB引擎的表数据文件本身就hi索引文件）

更新索引的表比没有索引的表数据花费更多的时间，理想的做法是仅仅再常用的搜索列上面创建索引。

创建索引的原则

索引自身消耗资源有索引时，数据库会先进行索引查询，使用不当会增加数据库的负担

表的主键，外键必须索引。因为主键具有唯一性，外键干练的是主表的主键，查询时可以快速定位

记录超过300行时表因该有索引，没有会先把表遍历一遍，影响数据库性能

经常与其他表进行连接的表，再连接字段上应该建立索引

唯一性太差的字段不适合建立索引

更新太频繁的字段不适合建立索引

经常出现where子句中的字段，特别是大表的字段，应建立索引

再经常grop by （分组）order by（排序）字段上建立索引

索引应该建立在选择性高的字段上

索引因该建再小字段上（内容小），对大文本字段甚至超长字段不要建立索引

索引总结

作用：加快表的查询速度，可以对字段排序

如何实现：没有索引的情况下，要查询某行数据，需要先扫描全表的定位某行数据。索引后会通过查找条件的字段找到其索引对应的行数据的物理地址，然后根据物理地址访问相应的数据

副作用：会额外占用用磁盘空间，跟新包含索引的数据效率慢

1）创建索引的原则：通常对超过300行的表

2）表的主键，外键字段，多表连接的字段，多表连接的字段唯一性较好的字段，不经常更新的字段，经常出现再where，grop by ，order by语句的字段，小字段

索引的分类和创建

create table member (id int(0)，name varchar(10),cardid int(18),phone int(11),address varchar(50),remark text);

索引类型命令

普通索引 create index 索引名 on 表明 （字段（len））；

alter table 表名 add index 索引名 （字段）；

唯一索引create unique 索引名 on 表明 （字段（len））；

alter table 表名 add unique 索引名 （字段）；

主键索引 alter table 表明 add primary key （字段）；

组合索引 create index 索引名 on 表明 （字段1，字段2. . . . ））；

alter table 表名 add index 索引名 （字段1,字段2

. . . .）；

全文索引 create fulltext index 索引名 on 表明（字段）； alter table 表明 add fulltext 索引名 （字段）；

模糊查询 select 字段 from 表 where match （字段） against（‘查询内容’）；

show index from 表明；

show keys from 表明；

show create table 表明；

drop index 索引名 on 表明； alter table 表明 drop index 索引名

普通索引，最基本的索引类型，没有唯一性之类的限制create index 索引名 表明 （列名【（length）】）；

FK，外键 索引名_index结尾

修改表结构的方式创建

alter table 表明 add index 索引名（列明）；

创建表的时候指定索引

create table 表名 （字段1 数据类型，字段2 数据类型【，. . . .】,index 索引名 （列名））；

不推荐先导数据再建立索引

create index name_index on member(name);

show create table member;

alter table member1 add index name_index(name);

唯一索引：唯一索引有空值

直接创建唯一索引

create unique index 索引名 on 表名（列名）

create unique key

alter table member1 add unique phone_index  (member);

主键索引必须指定·primary key，不能为空

删除

drop index name_index on member

alter table member drop index(删除索引必须为index) phone_index

组合索引：单列索引，多列索引，满足最左原则，因为select语句的where条件一次是从左往右执行的，使用select 语句查询时where 台哦见使用的字段顺序必需和组合索引中的排列一直，否则索引不会生效。

 alter table member add index name_cardid_phone_index (name,cardid,phone);

show create table member;查看

select * from member where name='xx' and cardid='xxxx' and phone='xxxxx';条件使用的字段顺序必须和组合索引中的排序一直，否则不生效

全文索引 ：适合创建再模糊查询的时候,fulltext

索引

 create fulltext index remark_index on member(remark);

alter table member add fulltext index remark_index(remark)

select * from member where match (remark ) against('vip');

列 字段 栏目

行 记录

show index from member1;G

 non_unique 为零时唯一

btree

cteate index name_index on membber1 (name(3));

alter table member add index addr_index (address(2));

show keys from member1G

show index from member1G

数据库查询时间过长

1，查询语句是否用到索引 explain

explain select * from member1where 'id' =231

2,网速原因，服务器运行状态io占用系统性能，执行语句慢，应用本身问题mysql配置优化增加缓存带宽，语句优化语法。

mysql事务

事务概念，是一种机制，一个操作数序列，包含一组数据库操作命令，并把所有的命令作为一个整体一起向系统提交或者撤销操作亲求，这组数据库命令要么都执行，要么不执行。

是一个不可分割的工作逻辑单元

可适用于多用户同时操作的数据库系统的场景，如银行保险公司及证券交易系统

事务通过事务的整体性一保证数据的一致性；

事务能够提高再向表中更新和插入的信息期间的可靠性

事务应该具备特性acid特性 原子性，一致性，隔离性，持久性。

原子性，要么都发生要么都不发生

一致性，事务结束前后要保持一致

隔离性，决定事务的可见级别

mysql事务支持四种隔离，用以控制事务所做的修改，并将修改通告值其他并发的事务；

并发执行效率越来越低

隔离级别分为两种

全局级别：

查询全局事务隔离等级

通配符%任意

show global variables like '%isolation%';

select @@global.tx_isolation;

会话级别

show session variables like '%isolation%';

select @@session.tx_isolation;

修改运行级别为提交读

set global transaction isolation level read uncommitted；

begin；开启事务

update account set money=money-100 where name='yht';

rollback ；撤回。

commit；提交结束事务

持久性：在事务完成后，该事务所对数据库所做的更改便持久的保存在数据库中，不会回滚。

总结：在事务管理中，原子性是基础，隔离时手段，一致性时目的，持久性时结果。

事务的控制语句

begin 开始

commit 结束提交事务

rollback撤销事务，后结束

savepoint s1（自定义）保存回滚点（快照）

rollback to s1（自定义）回滚到快照

mysql自动开启事物的提交，把每条增删改当作一个事务show variables like 'autocommit';查看自动提交

set autocommit=0；关闭