MYSQL数据库的索引 事务 及存储引擎

索引

什么是索引

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。
索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用二叉树（二叉树是指计算机科学中每个结点最多有两个子树的树结构。通常子树被称作“左子树”（left subtree）和“右子树”（right subtree）。）
更通俗的说，索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录。索引是一个文件，它是要占据物理空间的。

索引的分类

主键索引: 数据列不允许重复，不允许为NULL，一个表只能有一个主键。
唯一索引: 数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。
可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引 可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一组合索引
普通索引: 基本的索引类型，没有唯一性的限制，允许为NULL值。
可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引 可以通过ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);创建组合索引
全文索引： 是目前搜索引擎使用的一种关键技术。 （用的不多常用在电商上的客户收货地址 *局家庭地址等领域）
可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引

索引的应用场景（创建索引的原则）

表的主键外键必须有索引
记录数据超过300行的表必须有索引
经常与其他的表进行链接的表必须有索引
唯一性太差的字段不适合建立索引
更新太频繁的字段不适合创建索引
经常出现where字句中的字段特别是大表字段应该建立索引
索引应该建立在选择性高的字段
索引应该建立在小的字段上对于大的字段甚至超长的字段不要建立索引

索引的优缺点

索引的优点
可以大大加快数据的检索速度，这也是创建索引的最主要的原因。通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
索引的缺点
时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率；空间方面：索引需要占物理空间。

索引的创建方法

创建索引的三种方式，删除索引
普通索引的创建
第一种方式：在执行CREATE TABLE时创建索引 即在创建表的时候直接创建

mysql> create table info (id int(4) not null,name char(10) not null,score decimal(5,2),addr varchar(50) default '未知' ,primary key (id));
Query OK, 0 rows affected (0.00 sec)
mysql> desc info;       ##查询索引的第一个方法
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(4)       | NO   | PRI | NULL    |       |
| name  | char(10)     | NO   |     | NULL    |       |
| score | decimal(5,2) | YES  |     | NULL    |       |
| addr  | varchar(50)  | YES  |     | 未知    |       |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

第二种方式：使用ALTER TABLE命令去增加索引

mysql> alter table info add index index_addr(addr);
Query OK, 0 rows affected (0.03 sec)
mysql> show index from info;    ##第二种分查看索引的方法
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| info  |          0 | PRIMARY    |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| info  |          1 | index_addr |            1 | addr        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

CREATE INDEX可对表增加普通索引或UNIQUE索引。（但是，不能创建PRIMARY KEY索引）
第三种方式：使用CREATE INDEX命令创建

mysql> create index index_name on info (name);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc info;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(4)       | NO   | PRI | NULL    |       |
| name  | char(10)     | NO   | MUL | NULL    |       |
| score | decimal(5,2) | YES  |     | NULL    |       |
| addr  | varchar(50)  | YES  | MUL | 未知    |       |
+-------+--------------+------+-----+---------+-------+

删除索引
根据索引名删除普通索引、唯一索引、全文索引：alter table 表名 drop KEY 索引名

mysql> alter table info drop column addr;
mysql> desc info;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(4)       | NO   | PRI | NULL    |       |
| name  | char(10)     | NO   | MUL | NULL    |       |
| score | decimal(5,2) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

删除主键索引
mysql> alter table info drop primary key; ##若是主键自增长那么不能删除主键索引
需要取消自增长再行删除：
alter table user_index
– 重新定义字段
MODIFY id int,
drop PRIMARY KEY
1234
但通常不会删除主键，因为设计主键一定与业务逻辑无关。
####创建唯一索引###
##查看索引##

mysql> show index from info;
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| info  |          0 | PRIMARY   |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| info  |          1 | index_age |            1 | age         | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
##删除原来创建的索引##
mysql> drop index index_age on info;   ###删除索引
##用create unique方法，创建唯一索引
mysql> create unique index unique_name on info (name);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
##查看唯一索引##
mysql> show index from info;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| info  |          0 | PRIMARY     |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| info  |          0 | unique_name |            1 | name        | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
#
###删除唯一索引##
mysql> drop index unique_name on info;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
#
#
###使用alter table方法创建唯一索引###
mysql> alter table info add unique index_name (name);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
#
###使用新建表的方法创建唯一索引##
mysql> create table user1 (
    -> id int(4) not null primary key auto_increment,
    -> name varchar(10) not null,
    -> score decimal not null,
    -> hobby int(2) not null default '1',
    -> index index_scrore (score));
###组合索引###
mysql> create table user3 (
    -> name varchar(10) not null,
    -> age int(3) not null,
    -> sex tinyint(1) not null,
    -> index user3(name,age,sex));
####查看组合索引，发现keyname统统是一样的####
mysql> show keys from user3;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user3 |          1 | user3    |            1 | name        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| user3 |          1 | user3    |            2 | age         | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| user3 |          1 | user3    |            3 | sex         | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
####创建全文索引####
mysql> create table user4 (
    -> id int(11) not null auto_increment,
    -> tile char(255) character set utf8 collate utf8_general_ci not null,
    -> content text character set utf8 collate utf8_general_ci not null,
    -> primary key (`id`),
    -> fulltext (content));
Query OK, 0 rows affected (0.04 sec)
mysql> show keys from user4;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user4 |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| user4 |          1 | content  |            1 | content     | NULL      |           0 |     NULL | NULL   |      | FULLTEXT   |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
mysql> drop index content on user4;
mysql>alter table user4 add fulltext index_content(content);
mysql> show keys from user4;
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user4 |          0 | PRIMARY       |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| user4 |          1 | index_content |            1 | content     | NULL      |           0 |     NULL | NULL   |      | FULLTEXT   |         |               |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
mysql> drop index index_content on user4;
mysql>create fulltext index index_content on user4(content);
mysql> show keys from user4

事务

什么是事务

事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。事务是逻辑上的一组操作，要么都执行，要么都不执行。
事务最经典也经常被拿出来说例子就是转账了。
假如小明要给小红转账1000元，这个转账会涉及到两个关键操作就是：将小明的余额减少1000元，将小红的余额增加1000元。万一在这两个操作之间突然出现错误比如银行系统崩溃，导致小明余额减少而小红的余额没有增加，这样就不对了。事务就是保证这两个关键操作要么都成功，要么都要失败。

事物的四大特性(ACID)

1、原子性： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
2、一致性： 执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；
3、隔离性： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
4、持久性： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

事务的控制语句

begin 或 start transaction
commit 提交
rollback 回滚
savepoint identifier 设置断点 存档
release savepoint identifier 回滚
set transaction 控制类型

事务的控制方法

手动对事务进行控制的方法
事务处理命令控制事务
bebin:开始一个事务
commit:提交一个事务
rollback:回滚一个事务
使用set命令进行控制
set autocommit=0:禁止自动提交
set autocommit=1:开启自动提交

实验

1、创建数据库、创建表

mysql> show databases;  ###查看数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| myadm              |
| mysql              |
| new                |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql> create database school;  ###创建数据库school
Query OK, 1 row affected (0.00 sec）

mysql> show databases;   ###查看创建的school
+--------------------+
| Database           |
+--------------------+
| information_schema |
| myadm              |
| mysql              |
| new                |
| performance_schema |
| school             |
| sys                |
+--------------------+
7 rows in set (0.00 sec)
mysql> use school;    ###进入school
Database change


##创建info表##
mysql> create table info (
    -> id int(4) not null primary key auto_increment,
    -> name varchar(10) not null,
    -> address varchar(50) default 'nanjing',
    -> age int(3) not null);
Query OK, 0 rows affected (0.00 sec)

##info表中插入数据##
mysql> insert into info (name,address,age) values ('zhangsan','beijing',20),('lisi','shanghai',22);
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from info;
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
+----+----------+----------+-----+
2 rows in set (0.00 sec)
mysql>begin;   ###开始事务
mysql> insert into info (name,address,age) values ('wangwu','beijing',15);
mysql> select * from info;
mysql> savepoint a;
mysql> insert into info (name,address,age) values ('zhaoliu','beijing',16);
mysql> savepoint b;
mysql> select * from info;
mysql> rollback to a;
mysql> select * from info;
mysql> rollback to b;  ###回滚不回去
mysql>commit;   ###提交事务

事务一旦回滚这个数据将从数据中内存中删除 不能再回滚过去。
rollback和commint都可以结束事务

存储引擎

存储引擎概念介绍
MySQL中的数据用各种不同的技术存储在文件中，每一种技术都使用不同的存储机制、索引技巧、锁定水平并最终提供不同的功能和能力，这些不同的技术以及配套的功能在MySQL中称为存储引擎存储引擎是MySQL将数据存储在文件系统中的存储方式或者存储格式

MySQL存储引擎MyISAM与InnoDB区别

存储引擎Storage engine：MySQL中的数据、索引以及其他对象是如何存储的，是一套文件系统的实现。
常用的存储引擎有以下：
Innodb引擎：Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。MyIASM引擎(原本Mysql的默认引擎)：不提供事务的支持，也不支持行级锁和外键。MEMORY引擎：所有的数据都在内存中，数据的处理速度快，但是安全性不高。
MyISAM与InnoDB区别

##存储引擎###
mysql> show engines;    //查看存储引擎类型
###修改表的存储引擎###
mysql> alter table info engine=MyISAM;
##
###查看表存储引擎###
mysql> show create table info \G
vi /etc/my.cnf        //通过 改配置文件修改引擎
default-storage-engine=MyISAM
###
systemctl restart mysqld
###
mysql> show variables like '%storage_engine%';
+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| default_storage_engine           | MyISAM |
| default_tmp_storage_engine       | InnoDB |
| disabled_storage_engines         |        |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
4 rows in set (0.01 sec)

通过 Mysql_convert_table_format 转化存储引擎。
[[e-mail] /]# yum -y install perl-DBI perl-DBD-MySQL
[[e-mail]/]# /usr/local/mysql/bin/mysql_convert_table_format --user=root --password='123456' --sock=/tmp/mysql.sock auth