我想做个网站怎么做 找谁做好,做外汇上什么网站看新闻,导购类网站怎么做的,自己怎么做搬家网站一#xff1a;MySQL 索引介绍
索引是一个排序的列表#xff0c;在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址。在数据十分庞大的时候#xff0c;索引可以大大加快查询的速度。这是因为使用索引后可以不用扫描全表来定位某行的数据#xff0c;而是先通过…一MySQL 索引介绍
索引是一个排序的列表在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址。在数据十分庞大的时候索引可以大大加快查询的速度。这是因为使用索引后可以不用扫描全表来定位某行的数据而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据。
1.1索引概述
以如果需要在一个无序字段上进行搜索就要执行一个线性搜索Linear Search的过程平均需要访问 N/2 的数据块N 是表示所占据的数据块数目。如果这个字段是一个非主键字段也就是说不包含唯一的访问入口那么需要在 N 个数据块上搜索整个表格空间
但是对于一个有序字段可以运用二分查找Binary Search这样只需要访问 log2 (N)的数据块。
索引是对记录集的多个字段进行排序的方法。在一张表中为一个字段创建一个索引将创建另外一个数据结构包含字段数值以及指向相关记录的指针然后对这个索引结构进行排序允许在该数据上进行二分法排序。使用索引的副作用是需要额外的磁盘空间。
备注
Log2N是数学中的对数是以2为底N的对数为多少也就是2的多少次方是N
备注
线性搜索线性搜索是在列表中查找元素的基本算法。 它依次检查列表的每个元素直到找到目标元素或确定目标不存在。 线性搜索也称为蛮力搜索因为它检查列表中的每个元素而不管是否检查了任何先前的元素。
索引是一个排序的列表在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址类似于C语言的链表通过指针指向数据记录的内存地址使用索引后可以不用扫描全表来定位某行的数据而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据因此能加快数据库的查询速度。索引就好比是一本书的目录可以根据目录中的页码快速找到所需的内容。索引是表中一列或者若干列值排序的方法建立索引的目的是加快对表中记录的查找或排序
1.2索引作用
数据库利用各种各样的快速定位技术能够大大提高查询效率。特别是当数据量非常大查询涉及多个表时使用索引往往能使查询速度加快成千上万倍。
优点
设置了合适的索引之后数据库利用各种快速定位技术能够大大加快查询速度这是创建索引的最主要的原因。当表很大或查询涉及到多个表时使用索引可以成千上万倍地提高查询速度。可以降低数据库的IO成本并且索引还可以降低数据库的排序成本。通过创建唯一性索引可以保证数据表中每一行数据的唯一性。可以加快表与表之间的连接。在使用分组和排序时可大大减少分组和排序的时间。
1.3索引的分类
创建测试数据库和表
mysql create database auth;
mysql use auth
mysql create table users (id int(10),user_name char(20),user_pass char(50));
1普通索引
普通索引是最基本的索引它没有任何限制也是大多数情况下用到的索引。
1直接创建索引语法
mysqlCREATE INDEX index_name ON table_name(column(length));
例
mysql create index aaa on users(user_name(20));
备注
其中 length 是可选项。如果忽略 length 的值则使用整个列的值作为索引。如果指定使用列前的 length 个字符来创建索引就是使用列的一部分来创建索引这样有利于减小索引文件的大小节省索引列所占的空间。在某些情况下只能对列的前缀进行索引。索引列的长度有一个最大上限 255 个字节MyISAM 和 InnoDB 表的最大上限为 1000 个字节如果索引列的长度超过了这个上限就只能用列的前缀进行索引。另外BLOB二进制大对象或TEXT文本类型的列也必须使用前缀索引。
注意
数字类型的列不能指定其长度字符串的可以
2修改表结构的方式添加索引语法
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是为该字段定义的字符长度只能比初始值小不能比初始值大
3创建表结构时同时创建索引
【例】创建table01表指定title列为普通索引
CREATE TABLE table01 (
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),
INDEX index_table01_title (title(11))
);
【例】在book表中的year_publication字段上建立普通索引SQL语句如下
1创建表结构时同时创建索引
mysql use auth; CREATE TABLE book
(
bookid INT NOT NULL,
bookname VARCHAR(255) NOT NULL,
authors VARCHAR(255) NOT NULL,
info VARCHAR(255) NULL,
comment VARCHAR(255) NULL,
year_publication YEAR NOT NULL,
INDEX(year_publication)
); mysql SHOW CREATE table book \G
*************************** 1. row *************************** Table: book
Create Table: CREATE TABLE book ( bookid int(11) NOT NULL, bookname varchar(255) NOT NULL, authors varchar(255) NOT NULL, info varchar(255) DEFAULT NULL, comment varchar(255) DEFAULT NULL, year_publication year(4) NOT NULL, KEY year_publication (year_publication)
) ENGINEInnoDB DEFAULT CHARSETlatin1 1 row in set (0.00 sec) 2直接创建索引
mysql create index aaa on book(bookid); 3修改表结构的方式添加索引语法
mysql alter table book add index bbb(bookname(255)); 2唯一索引
唯一索引与普通索引类似不同的就是唯一索引的索引列的值必须唯一但允许有空值注意和主键不同。如果是组合索引则列值的组合必须唯一。 1创建唯一索引语法
mysqlCREATE UNIQUE INDEX index_name ON table_name(column(length));
2修改表结构的时候添加唯一索引语法
mysqlALTER TABLE table_name ADD UNIQUE index_name (column(length)); 3创建表的时候同时创建唯一索引
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关键字创建唯一索引。
1创建表的时候同时创建唯一索引 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)
由结果可以看到id字段上已经成功建立了一个名为UniqIdx的唯一索引。 2创建唯一索引语法
mysql create unique index ccc on t1(id); 3修改表结构的时候添加唯一索引语法
MariaDB [test] alter table t1 add unique ddd(id); 3主键索引
主键索引是一种特殊的唯一索引一个表只能有一个主键不允许有空值。一般是在建表的时候同时创建主键索引。 创建主键索引语法
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) 4组合索引最左前缀
平时用的 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 的顺序必须如组合索引中一致排序否则索引将不会生效。 5全文索引FULLTEXT
在1M大小的文件中搜索一个词可能需要几秒在100M的文件中可能需要几十秒如果在更大的文件中搜索那么就需要更大的系统开销这样的开销是不现实的。
在数据库中常用的查询方式一般是等价范围方式。当然也有LIKE %的模糊查询虽然用不到索引在文本内容比较少时是比较合适但是对于大量的文本数据检索全文索引在大量的数据面前能比 LIKE % 快很多速度不是一个数量级 1创建表的全文索引语法
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; 2修改表结构添加全文索引语法
mysqlALTER TABLE article ADD FULLTEXT index_content(content);
3直接创建索引语法
mysqlCREATE FULLTEXT INDEX index_content ON article(content); 【例】创建表t4在表中的info字段上建立全文索引SQL语句如下
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
由结果可以看到info字段上已经成功建立了一个名为FullTxtIdx的FULLTEXT索引。全文索引非常适合于大型数据集对于小的数据集它的用处可能比较小。 2修改表结构添加全文索引语法
mysql alter table t4 add fulltext fff(name); 3直接创建索引语法
mysql create fulltext index ggg on t4(info); 1.4创建索引的原则依据
数据库建立索引的原则
确定针对该表的操作是大量的查询操作还是大量的增删改操作
尝试建立索引来帮助特定的查询。检查自己的 sql 语句为那些频繁在 where 子句中出现的字段建立索引
尝试建立复合索引来进一步提高系统性能。修改复合索引将消耗更长时间同时复合索引也占磁盘空间
对于小型的表建立索引可能会影响性能
应该避免对具有较少值的字段进行索引
避免选择大型数据类型的列作为索引。 索引建立的原则
索引查询是数据库中重要的记录查询方法要不要建立索引以及在那些字段上建立索引都要和实际数据库系统的查询要求结合来考虑下面给出实际生产环境中的一些通用的原则 表的主键、外键必须有索引。因为主键具有唯一性外键关联的是子表的主键查询时可以快速定位。记录数超过300行的表应该有索引。如果没有索引需要把表遍历一遍会严重影响数据库的性能。经常与其他表进行连接的表在连接字段上应该建立索引。唯一性太差的字段不适合建立索引。更新太频繁地字段不适合创建索引。经常出现在 where 子句中的字段特别是大表的字段应该建立索引。索引应该建在选择性高的字段上。索引应该建在小字段上对于大的文本字段甚至超长字段不要建索引。 1.5查看索引
mysqlshow index from t1\G;
mysqlshow keys from t2\G;
mysqlshow create table t3 \G 1.6删除索引
索引的删除有如下两种方法
DROP INDEX 索引名 ON 表名;
ALTER TABLE 表名 DROP INDEX 索引名; 【例】
mysql drop index eee on t3;
mysql alter table t1 drop index ccc;
注意
删除前可以先用show命令查看一下表中的索引名 二MySQL 事务 MySQL 事务主要用于处理操作量大复杂度高的数据。比如说在人员管理系统中要删除一个人员即需要删除人员的基本资料又需要删除和该人员相关的信息如信箱文章等等。这样这些数据库操作语句就构成一个事务。 是一种机制、一个操作序列包含了一组数据库操作命令并且把所有的命令作为一个整体一起向系统提交或撤销操作请求即这一组数据库命令要么都执行要么都不执行
是一个不可分割的工作逻辑单元在数据库系统上执行并发操作时事务是最小的控制单元
适用于多用户同时操作的数据库系统的场景如银行、保险公司及证券交易系统等等
通过事务的整体性以保证数据的一致性 事物的ACID特性
原子性Atomicity
事务是一个完整的操作事务的各元素是不可分的事务中的所有元素必须作为一个整体提交或回滚如果事务中的任何元素失败则整个事务将失败 一致性Consistency
当事务完成时数据必须处于一致状态在事务开始前数据库中存储的数据处于一致状态在正在进行的事务中数据可能处于不一致的状态当事务成功完成时数据必须再次回到已知的一致状态 隔离性Isolation
对数据进行修改的所有并发事务是彼此隔离的表明事务必须是独立的它不应以任何方式依赖于或影响其他事务修改数据的事务可在另一个使用相同数据的事务开始之前访问这些数据或者在另一个使用相同数据的事务结束之后访问这些数据 持久性Durability
指不管系统是否发生故障事务处理的结果都是永久的一旦事务被提交事务的效果会被永久地保留在数据库中 注意
在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。 mysql SET AUTOCOMMIT0; mysqluse auth;
mysqlCREATE TABLE kgc_transaction_test( id int(5)) engineinnodb;
mysqlselect * from kgc_transaction_test; mysqlbegin; //开始事务
mysqlinsert into kgc_transaction_test value(1);
mysql insert into kgc_transaction_test value(2);
mysql commit; //提交事务
mysqlselect * from kgc_transaction_test; mysqlbegin; //开始事务
mysqlinsert into kgc_transaction_test values(3);
mysqlrollback; //回滚
mysql select * from kgc_transaction_test; //因为回滚所以数据没有插入
