07 MySQL之索引原理

一.介绍

为什么有索引:使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构.

作用：

1. 快速查询数据

2. 保证数据的唯一性

3. 实现表与表之间的参照完整性

4. 在使用order by、group by子句进行数据检索时，利用索引可以减少排序和分组的时间。

优点：

1. 大大加快数据检索速度

2. 创建唯一索引，保证数据库表中每行数据的唯一性

3. 加速表之间的连接

4. 减少查询中分组和排序的时间

　　索引在MySQL中也叫做“键”或者"key"（primary key，unique key，还有一个index key），是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要，减少io次数，加速查询。（其中primary key和unique key，除了有加速查询的效果之外，还有约束的效果，primary key 不为空且唯一，unique key 唯一，而index key只有加速查询的效果，没有约束效果）

2.索引的数据结构:

Mysql索引管理:

功能:

#1. 索引的功能就是加速查找

#2. mysql中的primary key，unique，联合唯一也都是索引，这些索引除了加速查找以外，还有约束的功能

常用的索引:

普通索引INDEX：加速查找

唯一索引：

    -主键索引PRIMARY KEY：加速查找+约束（不为空、不能重复）

    -唯一索引UNIQUE:加速查找+约束（不能重复）

联合索引：

    -PRIMARY KEY(id,name):联合主键索引

    -UNIQUE(id,name):联合唯一索引

    -INDEX(id,name):联合普通索引

创建,删除缩印:

#方法一：创建表时

    　　CREATE TABLE 表名 (

                字段名1  数据类型 [完整性约束条件…],

                字段名2  数据类型 [完整性约束条件…],

                [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY

                [索引名]  (字段名[(长度)]  [ASC |DESC])

                );

#方法二：CREATE在已存在的表上创建索引

        CREATE  [UNIQUE | FULLTEXT | SPATIAL ]  INDEX  索引名

                     ON 表名 (字段名[(长度)]  [ASC |DESC]) ;

#方法三：ALTER TABLE在已存在的表上创建索引

        ALTER TABLE 表名 ADD  [UNIQUE | FULLTEXT | SPATIAL ] INDEX

                             索引名 (字段名[(长度)]  [ASC |DESC]) ;

#删除索引：DROP INDEX 索引名 ON 表名字;

# 实例:

#方式一

create table t1(

    id int,

    name char,

    age int,

    sex enum('male','female'),

    unique key uni_id(id),

    index ix_name(name) #index没有key

);

#方式二

create index ix_age on t1(age);

#方式三

alter table t1 add index ix_sex(sex);

#查看

mysql> show create table t1;

| t1    | CREATE TABLE `t1` (

  `id` int(11) DEFAULT NULL,

  `name` char(1) DEFAULT NULL,

  `age` int(11) DEFAULT NULL,

  `sex` enum('male','female') DEFAULT NULL,

  UNIQUE KEY `uni_id` (`id`),

  KEY `ix_name` (`name`),

  KEY `ix_age` (`age`),

  KEY `ix_sex` (`sex`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

查询优化神器(explain):

执行计划：让mysql预估执行操作(一般正确)

    all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const

    id,email

    慢：

        select * from userinfo3 where name='alex'

        explain select * from userinfo3 where name='alex'

        type: ALL(全表扫描)

            select * from userinfo3 limit 1;

    快：

        select * from userinfo3 where email='alex'

        type: const(走索引)

巴特西

07 MySQL之索引原理

最新文章

热门文章