MySQL数据库索引、事务和存储引擎（一）

简述索引

数据库索引

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

索引的作用

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

注解：
IO ： 吞吐量 ‘I’:In ‘O’:Out
多表查询：内联：inner join 外联：left join（左外联） right join（右外联）

创建索引的原则依据

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

索引的分类

索引是依附着表而存在 表有索引则有，表无索引则无。

普通索引

• 最基本的索引类型，没有唯一性之类的限制
• 创建普通索引的方式

创建普通索引语法格式：
三种创建索引方式：
（一）create index 索引名称 on 表名 (列名);

mysql> create index index_name on info (name);

（二）alter table 表名 add index 索引名称 (列名);

mysql> alter table info add index index_addr (addr);

（三）primary key(列名)

创建表的时候创建的  primary key(id)

唯一性索引

• 与“普通索引”基本相同
• 与普通索引的区别是索引列的所有值只能出现一次，即必须唯一
• 创建唯一索引的方式
  （一）create unique index 索引名 on 表名 (列名);
  （二）alter table 表名 add unique 索引名 (列名);

主键索引

• 是一种特殊的唯一索引，指定为“primary key”
• 一个表只能有一个主键，不允许为空
• 创建主键索引的方式
  语法：create table 表名 (字段1 类型 not null primary key,字段2 类型…);

组合索引（单列索引与多列索引）

• 可以是单列上创建的索引，也可以是在多列上创建的索引
• 最左原则，从左往右依次执行
• 创建组合索引的方式
  语法：create table 表名 (字段1 类型,字段2 类型,index 表名(字段1,字段2));

全文索引

• MySQL从3.23.23版开始执行全文索引和全文检索
• 索引类型为fulltext
• 可以在char、varchar或者text类型的列上创建
  创建全文索引语法结构
  （一）alter table 表名 add fulltext index 索引名 (列名);

mysql> alter table info add fulltext index f_addr (addr);

（二）create fulltext index 索引名 on 表名 (列名);

mysql> create fulltext index f_addr on info (addr);

删除索引
语法一：alter table 表名 drop index 索引名;

mysql> alter table info drop index index_addr;

语法二：drop index 索引名 on 表名 ;

mysql> drop index index_addr on info ;

查看索引两种方式：
（一）show index from 表名
（二）show keys from 表名

附加

NULL和’'区别：
两者即为空，一个叫空字符串，一个叫空对象
''是不占用空间的，NULL是占用空间的

唯一索引和主键索引的区别：
唯一索引：可以为空但只能出现一次且唯一
主键索引：不为空且唯一