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(走索引)
posted @ 2019-01-25 21:12  small_caier  阅读(122)  评论(0编辑  收藏  举报