博客

Mysql索引

索引的概念：

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

索引的作用：

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

创建索引的原则依据：

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

索引的分类

普通索引

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

CREATE INDEX index_name ON table_name（column（length））；

ALTER TABLE table_name ADD INDEX index_name （column（length））；

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 urf8_general_ci NULL,
                'time'int(10) NULL DEFAULT NULL,
                PRIMARY KEY ('id'），
                INDEX index_name （title（length））
                ）
                

唯一性索引

• 与“普通索引”基本相同
• 与普通索引的区别是索引列的所有值只能出现一次，即必须唯一
• 创建唯一索引的方式

CREATE UNIQUE INDEX index_name ON table_name(column(length));

ALTER TABLE table_name ADD UNIQUE index_name (column(length))；

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 urf8_general_ci NULL,
                'time'int(10) NULL DEFAULT NULL,
                PRIMARY KEY ('id'），
                INDEX index_name （title（length））
                ）

主键索引

• 是一种特殊的唯一索引，指定为 “PRIMARY KEY”
• 一个表只能有一个主键， 不允许有空格
• 创建主键索引的方式

CREATE TABLE 'table_name' (
                 'id' int(11) NOT NULL AUTO_INCREMENT,
                 'title' char(255) NOT NULL,
                 PRIMARY KEY ('id')
                 ）；

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

• 可以是单列上创建的索引，也可以是在多列上创建的索引
• 最左原则，从左往右依次执行
• 创建组合索引的方式

CREATE TABLE user(
                 name varchar(9)，
                 age int(3),
                 sex tinyint(1)，
                 INDEX user(name，age，sex）
                 ）；
            

全文索引

• Mysql 从3.23.23版开始支持全文索引和全文检索
• 索引类型为FULLTEXT
• 可以在CHAR、VARCHAR或者TEXT类型的列上创建

CREATE INDEX index_name ON table_name(column(length))；

ALTER TABLE table_name ADD INDEX index_name(column(length));

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 urf8_general_ci NULL,
                'time'int(10) NULL DEFAULT NULL,
                PRIMARY KEY ('id'），
                INDEX index_name （title（length））
                ）

查看索引

mysql> show index from tablename；
mysql> show keys from tablename；

删除索引：

删除索引语法

DROP INDEX index_name ON table_name;
ALTER TABLE table_name DROP INDEX index_name；

删除索引的示例

mysql> drop index index_puid on mapping；
mysql> alter table mapping drop index gameid；

以上