临汾网站建设价格,手游推广渠道,网站域名续费多少钱,摄影网站哪个最好小阿轩yx-MySQL索引、事务
MySQL 索引介绍
是一个排序的列表#xff0c;存储着索引的值和包含这个值的数据所在行的物理地址数据很多时#xff0c;索引可以大大加快查询的速度使用索引后可以不用扫描全表来定位某行的数据而是先通过索引表找到该行数据对应的物理地址然后访…小阿轩yx-MySQL索引、事务
MySQL 索引介绍
是一个排序的列表存储着索引的值和包含这个值的数据所在行的物理地址数据很多时索引可以大大加快查询的速度使用索引后可以不用扫描全表来定位某行的数据而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据类似于图书的目录可以根据目录的页码快速找到所需的内容
索引概述
数据保存在磁盘类存储介质上时它是作为数据块存放数据块被当作一个整体来访问的可以保证操作的原子性记录集只能在某个关键字段上进行排序如果对一个无序字段进行搜索就要执行一个线性搜索Linear Search的过程平均需要访问N/2的数据块N表示所占数据块数目如果字段是一个非主键字段需要在N个数据块上搜索整个表格空间对记录集的多个字段进行排序的方法使用索引的副作用是需要额外的磁盘空间
索引作用
数据库利用各种快速定位技术大大提高查询效率数据库非常大查询涉及多个表时索引能使查询速度加快千万倍
优点
设置了合适的索引之后数据库利用各种快速定位技术能够大大加快查询速度这是创建索引的最主要的原因。当表很大或查询涉及到多个表时使用索引可以成千上万倍地提高查询速度。可以降低数据库的IO成本并且索引还可以降低数据库的排序成本。通过创建唯一性索引可以保证数据表中每一行数据的唯一性。可以加快表与表之间的连接。在使用分组和排序时可大大减少分组和排序的时间。
3个未索引的表 t1、t2、t3,分别只包含列 c1、c2、c3每个表分别含有 1000行数据组成均为 1~1000 的数值
mysqISELECT C1,C2,C3 FROM t1,t2,t3 WHERE C1C2 AND C1c3;
如果对每个表进行索引就能极大地加速查询进程利用索引的查询处理如下
从表 t1 中选择第一行查看此行所包含的数据使用表 t2 上的索引直接定位 t2 中与 t1 的值匹配的行。同理利用表 t3 上的索引直接定位 t3 中与 t1 的值匹配的行扫描表 t1 的下一行并重复前面的过程直到遍历t1 中所有的行
索引的分类
数据库表中对字段建立索引可以大大提高查询速度。善用这些索引可以令MySQL的查询和运行更加高效。索引是快速搜索的关键。MySQL索引的建立对于 MySQL的高效运行是非常重要的。
常见的 MySQL 索引类型
从物理存储的角度来划分
索引分为
聚簇索引非聚簇索引
聚簇索引
按照数据存放的物理位置为顺序的
区别
聚簇索引能提高多行检索的速度而非聚簇索引对于单行的检索更快
从逻辑的角度来划分
索引分为
普通索引唯一索引主键索引组合索引全文索引
普通索引 是最基本的索引 它没有任何限制 也是大多数情况下用到的索引
创建方式
直接创建索引语法
mysqlCREATE INDEX index_name ON table_name(column(length));
column 是指定要创建索引的列名。通常可以考虑将查询语句中在JOIN 子句和WHERE 子句里经常出现的列作为索引列。length 是可选项。如果忽略 length 的值则使用整个列的值作为索引 如果指定使用列前的 length 个字符来创建索引就是使用列的一部分来创建索引这样有利于减小索引文件的大小节省索引列所占的空间某些情况下只能对列的前缀进行索引索引列的长度有一个最大上限 255 个字节MyISAM 和 InnoDB 表的最大上限为 1000 个字节如果索引列的长度超过了这个上限就只能用列的前缀进行索引BLOB二进制大对象或TEXT文本类型的列也必须使用前缀索引数字类型的列不能指定其长度字符串的可以
mysql create index aaa on users(user_name(20));
修改表结构的方式添加索引语法
mysqlALTER TABLE table_name ADD INDEX index_name (column(length));
mysql drop index aaa on users;
mysql alter table users add index aaa (user_pass(45));
注45是为该字段定义的字符长度只能比初始值小不能比初始值大
唯一索引
与普通索引类似
不同
唯一索引的索引列的值必须唯一但允许有空值注意和主键不同如果是组合索引则列值的组合必须唯一
唯一索引创建方法和普通索引类似
创建唯一索引
mysqlCREATE UNIQUE INDEX index_name ON table_name(column(length));
修改表结构的时候添加唯一索引语法
mysqlALTER TABLE table_name ADD UNIQUE index_name (column(length));
创建表的时候同时创建唯一索引
CREATE TABLE table02 (
id int(11) NOT NULL AUTO_INCREMENT ,
title char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
content text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
time int(10) NULL DEFAULT NULL ,
PRIMARY KEY (id),
UNIQUE index_table02_title (title(11))
);
创建一个表t1在表中的id字段上使用UNIQUE关键字创建唯一索引
创建表的时候同时创建唯一索引
CREATE TABLE t1
(
id INT NOT NULL,
name CHAR(30) NOT NULL,
UNIQUE INDEX UniqIdx(id)
);mysql SHOW CREATE table t1 \G
************* 1. row *************Table: t1
CREATE Table: CREATE TABLE t1 (id int(11) NOT NULL,name char(30) NOT NULL,UNIQUE KEY UniqIdx (id)
) ENGINEInnoDB DEFAULT CHARSETutf8
1 row in set (0.00 sec)
创建唯一索引语法
mysql create unique index ccc on t1(id);
修改表结构的时候添加唯一索引语法
MariaDB [test] alter table t1 add unique ddd(id);
主键索引 是一种特殊的唯一索引 一个表只能有一个主键不允许有空值 一般是在建表的时候同时创建主键索引
创建主键索引语法
CREATE TABLE table_name (
id int(11) NOT NULL AUTO_INCREMENT ,
title char(255) NOT NULL ,
PRIMARY KEY (id)
);
创建表t2并将表中的id列设为主键
CREATE TABLE t2
(
id int(11) NOT NULL AUTO_INCREMENT ,
title char(255) NOT NULL ,
PRIMARY KEY (id)
);mysql SHOW CREATE table t2 \G
***************1. row ***************Table: t2
Create Table: CREATE TABLE t2 (id int(11) NOT NULL AUTO_INCREMENT,title char(255) NOT NULL,PRIMARY KEY (id)
) ENGINEInnoDB DEFAULT CHARSETlatin1
1 row in set (0.00 sec)
组合索引最左前缀 平时用的 SQL 语句一般都有较多的限制条件 为了进一步榨取 MySQL 的效率
组合索引创建两种场景
单列索引多列索引
t3用户表有 nameagesex 三个字段分别分三次建立了 INDEX 普通索引
select * from user where name AND age AND sex ;数据查询语句会分别检索三条索引
扫描效率有所提升但却还未达到最优
create table t3(
name varchar(9),
age int(3),
sex tinyint(1),
index eee(name, age, sex)
);
注
select 语句的 where 条件是依次从左往右执行的
mysqlselect * from user where name AND age AND sex ;
若使用的是组合索引 index user(name, age, sex)
在查询中name、age、sex 的顺序必须如组合索引中一致排序否则索引将不会生效
MySQL一个知识点
最左原则
select 语句的 where 条件是依次从左往右执行
mysqlselect * from user where name AND age AND sex ,
使用组合索引index user(name,age,sex)
査询中name、age、sex的顺序必须如组合索引中一致排序否则索引将不会生效
mysqlselect * from user where age AND name AND sex
如果采用“select * from user where age AND nameAND sex ;查询方式组合索引将无效化
一般在建立索引时要先想好相应的查询业务尽量避免虽然有索引但是使用不上的问题。
全文索引FULLTEXT
MySQL 从 3.23.23 版开始支持全文索引和全文检索在 MySQL5.6 版本以前FULLTEXT 索引仅可用于 MyISAM 表在 5.6之后innodb 引擎也支持 FULLTEXT 索引可以从 CHAR、VARCHAR或TEXT 列中作为 CREATE TABLE 语句的一部分被创建或是随后使用 ALTER TABLE 或 CREATEINDEX 被添加。
常用的查询方式 一般是等价 范围方式
LIKE %的模糊查询虽然用不到索引适用于文本内容较少
优势
大量的文本数据检索能比 LIKE % 快很多速度不是一个数量级
创建表的全文索引语法
CREATE TABLE table (
id int(11) NOT NULL AUTO_INCREMENT ,
title char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
content text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
time int(10) NULL DEFAULT NULL ,
PRIMARY KEY (id),
FULLTEXT (content)
)ENGINEMyISAM;
修改表结构添加全文索引语法
mysqlALTER TABLE article ADD FULLTEXT index_content(content);
直接创建索引语法
mysqlCREATE FULLTEXT INDEX index_content ON article(content);
创建表t4在表中的info字段上建立全文索引
CREATE TABLE t4(
id INT NOT NULL,
name CHAR(30) NOT NULL,
age INT NOT NULL,
info VARCHAR(255),
FULLTEXT INDEX FullTxtIdx(info)
) ENGINEMyISAM;
默认存储引擎为InnoDBInnoDB不支持全文索引
这里创建表时需要修改表的存储引擎为MyISAM不然创建索引会出错
语句执行完毕之后使用SHOW CREATE TABLE查看表结构
mysql SHOW CREATE table t4 \G
************* 1. row *************Table: t4
CREATE Table: CREATE TABLE t4 (id int(11) NOT NULL,name char(30) NOT NULL,age int(11) NOT NULL,info varchar(255) DEFAULT NULL,FULLTEXT KEY FullTxtIdx (info)
) ENGINEMyISAM DEFAULT CHARSETutf8
修改表结构添加全文索引语法
mysql alter table t4 add fulltext fff(name);
直接创建索引语法
mysql create fulltext index ggg on t4(info);
创建索引的原则依据
数据库建立索引的原则 确定针对该表的操作是大量的查询操作还是大量的增删改操作 尝试建立索引来帮助特定的查询。检查自己的 sql 语句为那些频繁在 where 子句中出现的字段建立索引 尝试建立复合索引来进一步提高系统性能。修改复合索引将消耗更长时间同时复合索引也占磁盘空间 对于小型的表建立索引可能会影响性能 应该避免对具有较少值的字段进行索引 避免选择大型数据类型的列作为索引
索引建立的原则 索引查询是数据库中重要的记录查询方法 要不要建立索引以及在那些字段上建立索引都要和实际数据库系统的查询要求结合来考虑
生产环境中通用的原则
表的主键、外键必须有索引。因为主键具有唯一性外键关联的是子表的主键查询时可以快速定位。记录数超过300行的表应该有索引。如果没有索引需要把表遍历一遍会严重影响数据库的性能。经常与其他表进行连接的表在连接字段上应该建立索引。唯一性太差的字段不适合建立索引。更新太频繁地字段不适合创建索引。经常出现在 where 子句中的字段特别是大表的字段应该建立索引。索引应该建在选择性高的字段上。索引应该建在小字段上对于大的文本字段甚至超长字段不要建索引在 SQL 语句中经常进行 GROUP BY、ORDER BY 的字段上建立索引缺省情况下建立的是非簇集索引但在以下情况下最好考虑簇集索引
查看索引
mysqlshow index from t1\G;
mysqlshow keys from t2\G;
mysqlshow create table t3\G;
删除索引
索引创建后会占用磁盘空间从性能考虑最好删除无用索引
两种方法
DROP INDEX 索引名 ON 表名;
ALTER TABLE 表名 DROP INDEX 索引名;
删除eee索引
mysql drop index eee on t3;
mysql alter table t1 drop index ccc;
注删除前可以先用show命令查看一下表中的索引名
MySQL 事务
主要用于处理操作量大复杂度高的数据一种机制一个操作序列包含了一组数据库操作命令并且把所有的命令作为一个整体一起向系统提交或撤销操作请求即这一组数据库命令要么都执行要么都不执行一个不可分割的工作逻辑单元在数据库系统上执行并发操作时事务是最小的控制单元适用于多用户同时操作的数据库系统的场景MySQL 中只有使用了 Innodb)数据库引擎的数据库或表才支持事务事务用来管理 insert,update,delete 语句
一般事务必须满足4个条件ACID
原子性一致性隔离性持久性
原子性Atomicity
或称不可分割性事务是一个完整的操作事务中的所有元素必须作为一个整体提交或回滚如果事务中的任何元素失败则整个事务将失败
一致性Consistency
当事务完成时数据必须处于一致状态在事务开始前数据库中存储的数据处于一致状态在正在进行的事务中数据可能处于不一致的状态当事务成功完成时数据必须再次回到已知的一致状态
隔离性Isolation
对数据进行修改的所有并发事务是彼此隔离的表明事务必须是独立的它不应以任何方式依赖于或影响其他事务修改数据的事务可在另一个使用相同数据的事务开始之前访问这些数据或者在另一个使用相同数据的事务结束之后访问这些数据
持久性Durability
指不管系统是否发生故障事务处理的结果都是永久的一旦事务被提交事务的效果会被永久地保留在数据库中
MySQL 默认设置
事务都是自动提交的即执行 SQL 语句后就会马上执行 COMMIT 操作
显式地开启一个事务必须使用命令禁止使用当前会话的自动提交
BEGINSTARTTRANSACTIONSETAUTOCOMMIT0
事务控制语句包含
BEGIN 或 START TRANSACTION:显式地开启一个事务;COMMIT也可以使用 COMMIT WORK不过二者是等价的。COMMIT会提交事务并使已对数据库进行的所有修改变为永久性的;ROLLBACK又可以使用 ROLLBACK WORK不过二者是等价的。回滚会结束用户的事务并撤销正在进行的所有未提交的修改;SAVEPOINT identifierSAVEPOINT 允许在事务中创建一个保存点一个事务中可以有多个 SAVEPOINT;RELEASE SAVEPOINT identifier删除一个事务的保存点当没有指定的保存点时执行该语句会抛出一个异常;ROLLBACK TO identifier把事务回滚到标记点;SETTRANSACTION:用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有 READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和SERIALIZABLE
MySQL 事务处理主要两种方法
用 BEGIN, ROLLBACK,COMMIT 来实现
BEGIN 开始一个事务ROLLBACK 事务回滚COMMIT 事务确认
直接用 SET 来改变 MySQL 的自动提交模式
SETAUTOCOMMIT0 禁止自动提交SETAUTOCOMMIT1 开启自动提交
事务示例
mysqluse kgc,
Database changed
mysqICREATE TABLE kgc_transaction_test( id int(5)) engineinnodb;//创建数据表Query OK, 0 rows affected (0.04 sec)mysqlselect*from kgc_transaction test;
Empty set (0.01 sec)//开始事务
mysqlbegin;
Query Ok, 0 rows affected (0.00 sec)
mysqlinsert into kgc transaction test value(1);
Query OK, 1 rows affected (0.01 sec)mysql insert into kgc_transaction test value(2);
Query OK, 1 rows affected (0.00 sec)//提交事务
mysqlcommit;
Query OK, 0 rows affected (0.01 sec)mysqlselect *from kgc_transaction test;
----
|id |
----
|1 |
----
|2 |
----
2 rows in set (0.01 sec)//开始事务
mysqlbegin;
Query OK, 0 rows affected (0.00 sec)mysqlinsert into kgc transaction test values(3);
Query OK, 1 rows affected (0.00 sec)//回滚
mysqlrollback;
Query OK, 0 rows affected (0.00 sec)//因为回滚所以数据没有插入
mysqlselect*from kgc transaction test;
----
|id |
|2 |
----
2 rows in set (0.01 sec)mysql
MySQL 存储引擎
MySQL中的数据用各种不同的技术存储在文件(或者内存)中。
这些技术中的每一种都使用不同
存储机制索引技巧锁定水平
最终提供两种功能和能力
广泛的不同的
通过选择不同的技术能够获得额外的速度或者功能从而改善应用的整体性能。
存储引擎(也称作表类型)
研究大量的临时数据也许需要使用内存类存储引擎。内存存储引擎能够在内存中存储相关数据。又或者需要使用一个支持事务处理的数据库(以确保事务处理不成功时数据的回退能力)。
MySQL
默认配置了许多不同的存储引擎可以预先设置或者在 MySQL 服务器中启用
选择适用于服务器、数据库和表格的存储引擎可以在存储信息、检索数据时提供最大的灵活性。
关系数据库表
用于存储组织信息的数据结构可以将表理解为由行和列组成的表格类似于 Excel电子表格的形式
特性
有的表简单有的表复杂有的表根本不用来存储任何长期数据有的表读取时非常快但是插入数据时却很差
查看 MySQL 支持引擎命令
mysqlshow engines;
MyISAM 存储引擎
不支持事务不支持外键
特点
访问速度快对事物完整性没有要求
以 SELECT、INSERT 为主的应用基本都可以使用这个引擎来创建表。
每个 MyISAM 表在磁盘上
存储成3个文件
其中相同的是
文件名表名
扩展名分为
frm(存储表定义)MYD(MYData存储数据)MYI(MYIndex存储索引)
可以放置在不同目录是
数据文件索引文件
平均分配 IO获取更快的速度
要指定数据文件和索引文件的路径需要在创建表的时候通过 DATA DIRECTORY 和 INDEXDIRECTORY语句指定
文件路径需要使用绝对路径。 MyISAM 类型的表可能会损坏可以使用 CHECK TABLE 语句来检查 MyISAM 表的健康,
MyISAM 表还支持3种不同的存储格式
静态(固定长度)表动态表压缩表
静态表(固定长度)
默认的存储格式字段都是非可变每个记录都是固定长度
优点
存储非常迅速容易缓存出现故障容易恢复
缺点
占用的空间通常比动态表多
注在某些情况下可能需要返回字段后的空格而使用这种格式时后面的空格会被自动处理掉。
动态表包含
可变字段记录不是固定长度
优点
占用空间较少
缺点
易产生碎片
频繁更新删除记录出现故障时恢复相对较困难
压缩表
由 myisamchk 工具创建占据非常小空间每条记录都是被单独压缩
InnoDB 存储引擎
一个健壮事务型存储引擎已被很多互联网公司使用为用户操作很大数据存储提供了一个强大解决方案MySQL 从5.5.5版本开始默认使用还引入了行级锁定和外键约束
以下场景使用是最理想选择
更新密集的表:InnoDB 存储引擎特别适合处理多重并发的更新请求。事务:InnoDB 存储引擎是支持事务的标准 MySQL存储引擎。自动灾难恢复:与其它存储引擎不同InnoDB表能够自动从灾难中恢复。外键约束:MySQL支持外键的存储引擎只有InnoDB。支持自动增加列 AUTO INCREMENT 属性。
一般如果需要事务支持并且有较高的并发读取频率InnoDB是不错的选择。
MyISAM 和 InnoDB 两种存储引擎的区别主要表现
InnoDB 支持事务MyISAM 不支持这一点是非常重要的。事务是一种高级的处理方式如对一些表中的列进行增删改的过程中只要哪个出错还可以回滚还原,而 MyISAM就不可以。MyISAM 适合查询、插入为主的应用InnoDB 适合频繁修改以及涉及到安全性较高的应用。InnoDB 支持外键MyISAM 不支持。从 MySQL5.5.5 以后InnoDB 是默认引擎。MySQL 从 5.6 版本开始 InnoDB 引擎才支持 FULLTEXT 类型的索引InnoDB 中不保存表的行数对于自增长的字段InnoDB中必须包含只有该字段的索引但是在 MISAM 表中可以和其他字段一起建立组合索引。清空整个表时InnoDB 是一行一行的删除效率非常慢。MyISAM 则会重建表。InnoDB 支持行锁(某些情况下还是锁整表如 update table set a1 where user like%lee%,)。
关于 MyISAM 与 InnoDB 选择使用
是 MySQL 数据库提供的两种存储引擎
优劣
InnoDB
会支持一些关系数据库的高级功能如事务功能和行级锁MyISAM 不支持。
MyISAM
性能更优占用的存储空间少
修改默认的存储引擎
有4种方法
通过 alter table 修改
MySQLalter table user info engineMylSAM;
通过修改 my.cnf指定默认存储引擎并重启服务
[rootMysql /#vim my.cnf
default-storage-engineInnoDB
通过 create table 创建表时指定存储引擎
MySQLcreate table engineTest(id int) engineMylSAM;
通过 Mysql convert table format 转化存储引擎
[rootMysql /]# yum -y install perl-DBl perl-DBD-MySQL
[rootMysql /]# /usr/local/mysql/bin/mysql convert table_format
--userroot -password123456-sock/tmp/mysql.sock auth 小阿轩yx-MySQL索引、事务
