一、索引
1.1 概念
排序的列表,索引值和这个值所对应的位置,物理地址;
方便查询,通过物理地址就可以找到所需数据;
表中一列或若干列值排序的方法;
占用额外的磁盘空间;
1.2 索引的作用
快速定位,加快查询速率;
当数据表很大后查询多个表示,效率提高;
降低数据库的IO成本,降低数据库的排序成本;
创建唯一索引保证数据表数据的唯一性;
加快数据表与表之间的连接;
使用分组和排序时,可大大的减少组合排序时间;
1.3 索引的分类
普通索引
最基本的索引类型,没有唯一性之类的限制
创建普通的索引的方式
1、用create方法创建普通索引
mysql> create index index_age on info (age);
2、用alter方法创建普通索引
mysql> alter table info add index index_age (age);
3、创建表的时候创建普通索引
mysql> create table user (
-> 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));
唯一索引
与 ‘普通索引’ 基本相同
区别普通索引:索引列的所有值只能出现一次,必须唯一
1、用create unique方法,创建唯一索引
mysql> create unique index unique_name on info (name);
2、用alter table方法创建唯一索引
mysql> alter table info add unique index_name (name);
3、用新建表的方法创建唯一索引
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',
-> unique index index_scrore (score));
主键索引
是一种特殊的唯一索引,指定为‘primary key’ (小写了)
一个表只能有一个主键,不允许有空值
mysql> create table user2 (
-> id int(4) not null auto_increment,
-> name varchar(10) not null,
-> age int(3) not null,
-> primary key (`id`));
组合索引(单列与多列)
可以是单列上创建的, 也是可以是多列上创建的
最左原则,从左往右依次执行
mysql> create table user3 (
-> name varchar(10) not null,
-> age int(3) not null,
-> sex tinyint(1) not null,
-> index user3(name,age,sex));
全文索引
mysql从3.23.23版本开始支持全文索引和全文检索
索引类型为fulltext
可以在 CHAR,VARCHAR或者TEXT类型的列上创建
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));
二、创建索引的原则依据
表的主键,外键必须有索引
记录数超过300行的表应该有索引
经常与其他表进行连接的表,在连接字段上建立索引
经常出现在 where 子句中的字段,特别是大表的字段
选择性比较高的字段上
小字段也可以建立
唯一性太差的字段不适合建立索引
更新太频繁的字段不适合创建索引
三、查看、删除、索引的方法
查看索引
>show index from 表名;
>show keys from 表名;
例如:
show index from IT_salary;
show keys from IT_salary;
删除索引
>drop index 索引名 on 表名;
>alter table 表名 drop 索引名;
例如:
drop index index_puid on info;
alter table info drop index index_puid;
四、事务
4.1 概念
一种保护机制吧,一个操作序列,包含了一组数据库操作命令,并且把所有的命令作为一个整体一起向系统提交或撤销操作请求,即这组数据库命令要么都执行,要么都不执行
不可分割的工作逻辑单元,在数据库系统上执行并发操作时,事务是最小的控制单元
适用于多用户同时操作的数据库系统的场景,数据比较敏感的关键性操作等。如:银行,证券交易系统…
通过事务的整体性以保证数据的一致性!!
4.2 事务的ACID特点
原子性
事务是一个完整的操作,事务的各元素是不可分的
事务中的所有元素必须作为一个整体提交或回滚
如果事务中的任何元素失败,则整个事务将失败
一致性
当事务完成时,数据必须处于一致状态
在事务开始前,数据库中存储的数据处于-致状态
在正在进行的事务中,数据可能处于不一致的状态
当事务成功完成时,数据必须再次回到已知的一致状态
隔离性
对数据进行修改的所有并发事务是彼此隔离的,表明事务必须是
独立的,它不应以任何方式依赖于或影响其他事务
修改数据的事务可在另一个使用相同数据的事务开始之前访问这
些数据,或者在另一个使用相同数据的事务结束之后访问这些数
据
持久性
指不管系统是否发生故障,事务处理的结果都是永久的
一旦事务被提交,事务的效果会被永久地保留在数据库中
4.3 事务控制语句
Mysql事务默认是自动提交的,当sql语句提交时事务便自动提交
控制语句
BEGIN或START TRANSACTION
COMMIT
ROLLBACK
SAVEPOINT 事务名
RELEASE SAVEPOINT 事务名
ROLLBACK TO 事务名
SET TRANSACTION
控制语句解释:(按自上到下顺序解释:)
显示开启一个事务;
提交事务,并使已对数据库进行的所有修改变为永久性;
回滚, 会结束用户的事务,并撤销正在进行的所有未提交的修改;
允许在事务中创建一个保存点,一个事务中可以有多个
删除一个事务的保存点,当没有指定保存点是,执行该语句会显示异常
把事务会滚到保存的事务点;
用来设置事务的隔离级别。lnnonDB 存储引擎提供事务的隔离级别,有read uncommitted , read committed …
先搭建库,表,添加数据;
创建数据库:
mysql> create database uou;
Query OK, 1 row affected (0.00 sec)
创建数据表:
mysql> create table lol(
-> id int(4) not null primary key auto_increment,
-> name varchar(10) not null,
-> address varchar(50) default 'taihe',
-> age int(3) not null);
在表中添加数据:
mysql> insert into lol
-> (name,address,age) values
-> ('小明','北京',23),
-> ('小刚','南京',20);
查看数据记录
mysql> select * from lol;
+----+--------+---------+-----+
| id | name | address | age |
+----+--------+---------+-----+
| 1 | 小明 | 北京 | 23 |
| 2 | 小刚 | 南京 | 20 |
+----+--------+---------+-----+
事务操作
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into lol
-> (name,address,age)
-> values
-> ('小红','深圳',22);
mysql> select * from lol;
+----+--------+---------+-----+
| id | name | address | age |
+----+--------+---------+-----+
| 1 | 小明 | 北京 | 23 |
| 2 | 小刚 | 南京 | 20 |
| 3 | 小红 | 深圳 | 22 |
+----+--------+---------+-----+
3 rows in set (0.00 sec)
mysql> savepoint a; #保存到一个事务点名a;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into lol #写入数据验证
-> (name,address,age)
-> values
-> ('小兰','上海',25);
Query OK, 1 row affected (0.00 sec)
mysql> select * from lol;
+----+--------+---------+-----+
| id | name | address | age |
+----+--------+---------+-----+
| 1 | 小明 | 北京 | 23 |
| 2 | 小刚 | 南京 | 20 |
| 3 | 小红 | 深圳 | 22 |
| 4 | 小兰 | 上海 | 25 |
+----+--------+---------+-----+
4 rows in set (0.00 sec)
mysql> rollback to a; #回滚到保存点a;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from lol;
+----+--------+---------+-----+
| id | name | address | age |
+----+--------+---------+-----+
| 1 | 小明 | 北京 | 23 |
| 2 | 小刚 | 南京 | 20 |
| 3 | 小红 | 深圳 | 22 |
+----+--------+---------+-----+
3 rows in set (0.00 sec)
mysql> insert into lol #重新写入数据
-> (name,address,age)
-> values
-> ('小兰','上海',25);
Query OK, 1 row affected (0.00 sec)
mysql> commit; #提交数据之后,保证一致性,才会显示!!
退出当前管理用户账号,从新登录进来,会看到数据已经写进去!
五、存储引擎
5.1 介绍
MySQL中的数据用各种不同的技术存储在文件中,每
一种技术都使用不同的存储机制、索引技巧、锁定水平
并最终提供不同的功能和能力,这些不同的技术以及配
套的功能在MySQL中称为存储引擎
存储引擎是MySQL将数据存储在文件系统中的存储方
式或者存储格式
MySQL常用的存储引擎
●MyISAM
●InnoDB
MySQL数据库中的组件,负责执行实际的数据I/0操作
MySQL系统中,存储引擎处于文件系统之上,在数据
保存到数据文件之前会传输到存储引擎,之后按照各个
存储引擎的存储格式进行存储
5.2 MyISAM
MyISAM不支持事务,也不支持外键
访问速度快
对事务完整性没有要求
MyISAM在磁盘.上存储成三个文件
●.frm文件存储表定义
●数据文件的扩展名为.MYD (MYData)
●索引文件的扩展名是.MYI (MYIndex)
■表级锁定形式,数据在更新时锁定整个表
■数据库在读写过程中相互阻塞
●会在数据写入的过程阻塞用户数据的读取
●也会在数据读取的过程中阻塞用户的数据写入
■数据单独写入或读取,速度过程较快且占用资源相对少
■MyIAM支持的存储格式
●静态表
●动态表
●压缩表
5.3 MyISAM适用的生产场景
公司业务不需要事务的支持
单方面读取或写入数据比较多的业务
MyISAM存储引擎数据读写都比较频繁场景不适合
使用读写并发访问相对较低的业务
数据修改相对较少的业务
对数据业务-致性要求不是非常高的业务
服务器硬件资源相对比较差
5.4 InnoDB介绍
支持4个事务隔离级别
行级锁定,但是全表扫描仍然会是表级锁定
MyISAM存储引擎数据读写都比较频繁场景不适合
使用读写并发访问相对较低的业务
数据修改相对较少的业务
对数据业务-致性要求不是非常高的业务
服务器硬件资源相对比较差
5.5 适用生产场景分析
业务需要事务的支持
行级锁定对高并发有很好的适应能力,但需确保查询是
通过索引来完成
业务数据更新较为频繁的场景
如:论坛,微博等
业务数据一致性要求较高
如:银行业务
硬件设备内存较大,利用Innodb较好的缓存能力来提
高内存利用率,减少磁盘IO的压力
5.6 企业选择存储引擎依据
需要考虑每个存储引擎提供的核心功能及应用场景
支持的字段和数据类型
所有引擎都支持通用的数据类型
但不是所有的引|擎都支持其它的字段类型,如二进制对象
锁定类型:不同的存储引擎支持下同级别的锁定
表锁定
行锁定
索引的支持
建立索弓|在搜索和恢复数据库中的数据时能显著提高性能
不同的存储弓|擎提供不同的制作索引|的技术
有些存储引擎根本不支持索引
事务处理的支持
提高在向表中更新和插入信息期间的可靠性
可根据企业业务是否要支持事务选择存储弓|擎
六、修改存储引擎方法
方法1:
alter table 表名 engine=引擎;
方法2:
vi /etc/my.cnf
在[mysqld]配置段内添加,指定默认引擎并重启服务;
default-storage-engine=innoDB
重启mysqld
方法3:
create table 表名 (字段....) engine = 引擎;
方法4:
Mysql_convert_table_format--user=root--password=密码 --sock=/tmp/mysql.sock --engine = 引擎 库名 表名
yum -y install perl-DBI perl-DBD-MySQL
/usr/local/mysql/bin/mysql_convert_table_format --user=root --password='123456' --sock=/tmp/mysql.sock auth