mysql索引，事务与存储引擎

索引

数据库索引
是一个排序的列表，存储着索引值和这个值所有对应的物理地址。
无需对整个表进行扫描，通过物理地址就可以找到所需数据
是表中一列或者若干列值排序的方法
需要额外的磁盘空间

数据库的作用

索引的作用，
数据库利用各种快速地位技术，能够大大加快查询速率
当表很大或者查询设计到的多个表时，可以成千上万备提高查询速度
可以降低数据库的io成本，并且还可以降低数据库的排序成本
可以通过创建唯一性索引保证数据表数据的唯一性
可以加快表与表直接的连接
在使用分组和排序时，可大大减少分组和排序的时间

索引的分类

普通索引
唯一性索引
主键索引
组合索引

索引的副作用

索引需要占空间
对myisam引擎，索引文件和数据文件是分离的，索引文件用于保存数据记录的地址，而，innodb引擎表数据文件就是索引文件
在插入和修改数据时需要花费更多的时间，索引也要随之变动

创建索引的原则依据

索引岁可以提升数据库的查询的速度，但并不是任何情况搜要创建索引，因为索引本身消耗系统资源，在有索引的情况下，数据库会进行索引查询，然后定位到具体的数据行，如果索引不当，会增加数据库的负担
表的主键，外键必须有索引，因为主键具有唯一性，外键记录超过300行的表应该有索引
经常与其他表进行连接的表，在连接西段上应该建立索引唯一性太差的字段不适合创建索引
在更新太平凡的字段不适合常见索引经常出现为何热字段中额字段，特备是大表的字段，创索引
索引应该建立在选择性高的字段上索引应该在小字段上，对于文件字段甚至超长字段，不要建索引

索引的分类和创建

普通索引

第一种方式，直接创建索引

CREATE INDEX 索引名 ON 表名 (列名[(length)]);
示例：create index phone_index on member (phone);

第二种，修改表方式创建
ALTER TABLE 表名 ADD INDEX 索引名 (列名);
例：alter table member add index id_index (id);

第三种，创建表的时候指定索引
CREATE TABLE 表名 ( 字段1 数据类型,字段2 数据类型[,…],INDEX 索引名 (列名));
例：create table test(id int(4) not null,name varchar(10) not null,cardid varchar(18) not null,index id_index (id));

mysql> create database class;
Query OK, 1 row affected (0.01 sec)

mysql> use class;
Database changed
mysql> create table member (id int(10),name varchar(10),cardid varchar(18),phone varchar(11),address varchar(50),remark text);
Query OK, 0 rows affected (0.01 sec)

mysql> desc member;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(10)     | YES  |     | NULL    |       |
| name    | varchar(10) | YES  |     | NULL    |       |
| cardid  | varchar(18) | YES  |     | NULL    |       |
| phone   | varchar(11) | YES  |     | NULL    |       |
| address | varchar(50) | YES  |     | NULL    |       |
| remark  | text        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

mysql> create index phone_index on member (phone);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select phone from member
    -> ;
Empty set (0.00 sec)

mysql> insert into member values (1,'zhangsan','123','111111','nanjing','this is vip');
o member values (5,'zhaoliu','123456','555555','nanjing','this is vip');
insert into member values (3,'qianqi','1234567','333333','shanghai','this is vip');Query OK, 1 row affected (0.01 sec)

mysql> insert into member values (4,'lisi','1234','444444','nanjing','this is normal');
Query OK, 1 row affected (0.00 sec)

mysql> insert into member values (2,'wangwu','12345','222222','benjing','this is normal');
Query OK, 1 row affected (0.00 sec)

mysql> insert into member values (5,'zhaoliu','123456','555555','nanjing','this is vip');
Query OK, 1 row affected (0.00 sec)

mysql> insert into member values (3,'qianqi','1234567','333333','shanghai','this is vip');
Query OK, 1 row affected (0.00 sec)

mysql> select * from member;
+------+----------+---------+--------+----------+----------------+
| id   | name     | cardid  | phone  | address  | remark         |
+------+----------+---------+--------+----------+----------------+
|    1 | zhangsan | 123     | 111111 | nanjing  | this is vip    |
|    4 | lisi     | 1234    | 444444 | nanjing  | this is normal |
|    2 | wangwu   | 12345   | 222222 | benjing  | this is normal |
|    5 | zhaoliu  | 123456  | 555555 | nanjing  | this is vip    |
|    3 | qianqi   | 1234567 | 333333 | shanghai | this is vip    |
+------+----------+---------+--------+----------+----------------+
5 rows in set (0.01 sec)

mysql> select phone from member;
+--------+
| phone  |
+--------+
| 111111 |
| 222222 |
| 333333 |
| 444444 |
| 555555 |
+--------+
5 rows in set (0.00 sec)

唯一索引

唯一索引的每个值都是唯一，允许有空，只能有一个空
第一种，直接创建索引
create unique index 索引名 on 表名 （列表）；
create unique index name_index on member (name);

第二种方式；修改表方式创建
ALTER TABLE 表名 ADD UNIQUE 索引名 (列名);
例：alter table member add unique cardid_index (cardid);

第三种，创建表的时候指定
CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型[,…],UNIQUE 索引名 (列名));
例:create table amd2 (id int,name varchar(20),unique id_index (id));

mysql> create table test(id int(4) not null,name varchar(20) not null,index id_index (id));
Query OK, 0 rows affected (0.01 sec)

mysql> create unique index name_index on member(name);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table member add unique cardid_index (cardid);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

主键索引

主键索引是特殊的唯一索引，必须指定为 primary key。一个表只能有一个主键，不允许有空值，添加主键，将自动创建主键索引
第一种方式，创建表时指定
CREATE TABLE 表名 ([…],PRIMARY KEY (列名));

第二种方式；修改时创建
ALTER TABLE 表名 ADD PRIMARY KEY (列名);

组合索引

可以使单列上创建的索引，可可以使在多列上创建的索引
第一种，新建
CREATE TABLE 表名 (列名1 数据类型,列名2 数据类型,列名3 数据类型,INDEX 索引名 (列名1,列名2,列名3));

mysql> show create table amd1;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| amd1  | CREATE TABLE `amd1` (
  `id` int(11) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `cardid` varchar(20) DEFAULT NULL,
  KEY `index_amd` (`id`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into amd1 values(1,'zhangsan','123123');
Query OK, 1 row affected (0.00 sec)

mysql> select * from amd1 where name='zhangsan' and id=1;
+----+----------+--------+
| id | name     | cardid |
+----+----------+--------+
|  1 | zhangsan | 123123 |
+----+----------+--------+
1 row in set (0.00 sec)

注：组合索引创建的字段顺序是其触发索引的查询顺序

全文索引

适合在进行模糊查询的时候使用，用于一篇文章中索引文本信息

第一种，直接索引
create fulltext index 索引名 on 表名 列表

第二种，修改表凡是创建
alter table 表名 add fulltest 索引名 列名

第三种，创建表的时候之低昂索引
create table 表名 （字段1 数据类型 。。。。。fulltest 索引名 列名）

第四种，使用全文索引时查询
select * from 表名 where match 列名 against （’查询内容‘）
例：select * from member where match(remark) against(‘this is vip’);
or
select * from member where remark=‘this is vip’;

mysql> create fulltext index remark_index on member (remark);
Query OK, 0 rows affected, 1 warning (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> select * from member where match(remark) against('this is vip');
+------+----------+---------+--------+----------+-------------+
| id   | name     | cardid  | phone  | address  | remark      |
+------+----------+---------+--------+----------+-------------+
|    1 | zhangsan | 123     | 111111 | nanjing  | this is vip |
|    5 | zhaoliu  | 123456  | 555555 | nanjing  | this is vip |
|    3 | qianqi   | 1234567 | 333333 | shanghai | this is vip |
+------+----------+---------+--------+----------+-------------+
3 rows in set (0.01 sec)

查看索引

show index from 表名
show index from 表名\G； 竖向显示表索引信息
show keys from 表名;
show keys from 表名\G;

 Table: member
   Non_unique: 1
     Key_name: remark_index
 Seq_in_index: 1
  Column_name: remark
    Collation: NULL
  Cardinality: 5
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: FULLTEXT
      Comment: 
Index_comment: 
5 rows in set (0.00 sec)

各字段的含义如下：

Table 表的名称
Non_unique 如果索引内容唯一，则为 0；如果可以不唯一，则为 1。
Key_name 索引的名称。
Seq_in_index 索引中的列序号，从 1 开始。 limit 2,3
Column_name 列名称。
Collation 列以什么方式存储在索引中。在 MySQL 中，有值‘A’（升序）或 NULL（无分类）。
Cardinality 索引中唯一值数目的估计值。
Sub_part 如果列只是被部分地编入索引，则为被编入索引的字符的数目(zhangsan)。如果整列被编入索引，则为 NULL。
Packed 指示关键字如何被压缩。如果没有被压缩，则为 NULL。
Null 如果列含有 NULL，则含有 YES。如果没有，则该列含有 NO。
Index_type 用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）。
Comment 备注。

总结：
索引分类：
普通索引 （所有字段，没有要求） 直接就是index
唯一索引 （唯一性的字段，仅允许一次空值） unique index
主键索引 （唯一性，不能为空，一张表只能有一个主键索引）primary key
组合索引（多列，多字段组合成一组） index （多个字段）
全文索引（varchar char test） fulltext index 查看时 用where 定位，用单引号
创建索引：
在创建表的时候，指定index
alter修改表结构的时候，进行add添加index
直接创建索引index
主键可以直接创建主键

删除索引

第一种 ，直接删除
drop index索引名 on表名
第二种，修改表方式，删除索引
alter table 表名 drop index 索引名
第三种 ，删除主键索引
alter table 表名 drop primary key；

事务

是一种机制，一个操纵序列，包含一组数据库操作命令，并且把所有命令作为一个整体一起想系统提交撤销操作请求，即这一组数据库命令要么都执行，要么都不执行
是一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时，事务是最小的控制单元
使用于多用户同时操作的数据库系统的场景，通过事务的整体性以保证数据的一致性

事务的ACID特点

原子性：事务是一个完整的操作，各事务各元素是不可分割的，事务中任何元素失败整个事务将失败
一致性：事务完成时，数据必须处于一致状态，在事务开始前，数据库中存储的数据处于一直状态，正在进行的事务中，时刻可能处于不一致状态，完成时，数据必须再次回到已知的已知状态
隔离性：对数据进行修改的所有并发事务彼此隔离的，表名事务必须是独立的，它不应以任何方式依赖于影响其他事务，修改诗句的事务可在另一个相同数据的事务开始之前访问这些数据，或者在另一个形同数据的事务结束Hi好访问这些数据
持久性：不管系统是否发生故障，事务出来的结果都是永久的，一但提交，事务效果会被永久保存在数据库中

事务产生不一致的结果

1，脏读：是读到了别的事务回滚前的脏数据
2，不可重复读：前后多次读取，数据内容不一致，多次查询中，其他事务修改提交引起的
3，幻读：前后多次读取，数据总量不一致，其他事务对表中数据进行修改，这个修改涉及到表中全部行数，另一个事务也在修改行数，
4，丢失更新：两个事务同时读取同一个记录，A先修改记录，B也在修改，B提交的覆盖了A的结果。

mysql及事务隔离级别

1，read uncommitted：读取尚未提交的数据：不可解决脏读
2，read committed：读取已经提交的数据，可以解决脏读
3，repeatable read ：重读读取，默认的，可以解决脏读和不可重复读
4，serializable：串行化，可以解决脏读，不可重复度，和虚读

查询全局事务隔离级别
show global variable like ‘%isolation%’;
select @@global.tx_isolation;

2,查询会话事务隔离级别
show session variable like ‘%isolation%’;
select @@global.tx_isolation;；
select @@tx_isolation;
3,设置全局事务隔离级别
set global transaction isolation level read committed;
4，设置会话事务隔离级别
set session transaction isolation level read committed;

事务控制的语句

begin 或 start transaction ：显示开启一个事务
commit 或commit work ：提交事务
rollback 或 rollback work：回滚结束用户任务
savepoint s1 ：允许在事务中创建一个回滚点，可以设置多个
rollback to s1 ：把事务回滚到标记点

使用set 设置控制事务
set autocommit=0； 禁止自动提交
set autocommit=1 开启自动提交，默认
show variables like 'autocommit ';查看值
如果没有开启自动提交，当前会话连接的mysql所有操作都会当成一个事务直接输入rollback 。当前事务才算提交，当前事务结束前新的mysql连接时无法读取到任何当前会话的操作结果。需要开启

存储引擎

一，概念
mysql中的数据用各种不同的技术存储在文件中，每一种技术都使用不听的存储机制，索引技巧，锁定水平并最终提供不同法人功能和能力，这些不同额技术以及配药的功能在musql中村塾引擎

存储引擎是mysql将数据存储在文件系统中的存储方式或者存储格式

常见的存储引擎：myisam innodb

myisam

myisam：不支持事务，也不支持外键约束，只支持全文索引，数据文件和索引文件是分开白村的，
访问速度快，对事务的完整新没有要求，myisam适合查询，插入为主的应用
myisam在磁盘上村塾三个文件，文件名和表名相同，扩展名为.frm
2,myisam表3中不同存储格式
静态（固定长度）表：存储非常迅速，容易缓存，故障之后容易恢复，缺点是占用空间
动态表：占用空间少，缺点，频繁更新，删除记录会产生碎片，需要定制执行optimize table语句命令来改善性能，并且出故障的时候恢复比较 困难。
压缩表：压缩表由muismchk工具创建，占用非常小的空间，因为每条记录都是被单独压缩的，所以只有非常小额访问开支。
3，myisam适用场景
公司业务不需要事务的支持
单方面读取或写入数据比较多的业务
myisam存储引擎据读和写都比较频繁场景不适合
适用读写并发访问相对较低的业务
数据修改相对较少的业务
对数据业务一致性要求不是非常高的业务
硬件资源相对比较差的

innodb

1，特点：
支持事务，支持4个事务隔离级别，默认的存储引擎为innodb

读写阻塞与事务隔离级别相关
能非常高效的缓存索引和数据
表与主键一簇的方式存储
支持分区，表空间类似oracle数据库
支持外键约束，5.5前不支持全文索引，5,5支持全文索引

对硬件资源要求还是比较高的场合，行级锁定，但是全 表扫描任然会是表锁定

innodb中不保存表的行数
对自增长的字段，innodb中必须包含

死锁：
myisam：表级锁定
innodb：行级锁定
当两个请求分别访问/读取2行记录，同时又需要读取对方的数据，因为行锁的限制，而造成堵塞现象
怎么解决死锁

修改存储引擎

方法一：通过alter table修改
alter table 表名 engine=MyISAM；
show create table 表名 查看
方法二：修改mysql的配置文件
vim /etc/my.cnf
[mysqld]
default-storage-engine=INNODB
systectl restart mysqld
修改配置文件，改变默认方式，重启生效，只对之后建立的表有效，原先不改变引擎方式
方法三：通过create table 创建表示指定存储引擎
create table 表名（）engine=MyISAM；

总结：
1，索引----》mysql的优化
在合适的字段上创建索引
表的主键，外键必须有索引，超过300行的有索引，经常和其他表连接的字段上建立索引，唯一性太差的不能用索引，更新太平凡的不用，经常出现热字段的，特别是大表的字段，用，建立在选择性高的字段，
2，事务的特性
原子性 一致性 持久性 隔离性
3，数据不一致的情况（四种）
脏读，不可重复读，幻读 丢失更新
4，解决避免事务不一致的情况，利用事务的隔离机制（四种，默认rr）

5，myisam 和 innodb区别

InnoDB支持事务，MyISAM不支持

InnoDB支持外键，而MyISAM不支持

InnoDB是聚集索引，数据文件是和（主键）索引绑在一起，MyISAM是非聚集索引，索引和数据文件是分离的
InnoDB不保存表的具体行数，而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快
Innodb不支持全文索引，而MyISAM支持全文索引

MyISAM表格可以被压缩后进行查询操作
InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁
InnoDB表必须有唯一索引（如主键）（用户没有指定的话会自己找/生产一个隐藏列Row_id来充当默认主键），而Myisam可以没有
6，你们公司用哪种存储引擎
常用inndb

1. 是否要支持事务，如果要请选择innodb，如果不需要可以考虑MyISAM；

   2. 如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读也有写，请使用InnoDB。

   3. 系统奔溃后，MyISAM恢复起来更困难，能否接受；

   4. MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的，如果你不知道用什么，那就用InnoDB，至少不会差
      7，你们mysql出现过哪些故障
      行锁

innodb
共享表空间：存储表结构，索引，存储，视图等
独占表空间：
行级锁
表级锁
液级锁

缓冲池，存的是索引，配合共享表空间，在内存

404 数据库行锁，去看数据库连接日志和sql执行语句日志

一直加载不出来，可能是数据库数据类型设置小了。