存储引擎、索引

存储引擎

定义：

　　就是数据库存储数据的技术，可以类比成发动机引擎，不同的引擎性能各不相同

分类*****

最常见的两个存储引擎是innodb和myisam，区别如下：

1、innodb

　　--1、（默认版本是5.5及以上）

　　--2、支持事务

　　--3、不支持全文索引

　　--4、索引和数据在同一文件中，.ibd

　　　　 表的结构是在.frm文件中

2、myisam

　　--1、（默认版本5.5以下，主要5.3用的人最多）

　　--2、不支持事务

　　--3、支持全文索引

　　--4、.frm：表结构

　　　　 .MYD：表数据

　　　　 .MYI：表索引

3、memory（不常用）

ps：innodb不支持全文索引

　　国内普遍用的全文索引  sphinx（斯芬克斯）

索引

类比

相当于新华字典的目录，我们可以把索引理解成一个特殊的文件，

如果没有这个文件，查询是从前往后查找数据的，

如果有这个文件，会按照一种特殊的数据结构(二叉树)查找数据

作用

加快查询的数据

分类

1、主键索引：加快查询  +  不能重复  +  不能为空  primary key

2、唯一索引：加快查询  +  不能重复   unique(列名)

　  联合唯一索引：加快查询 + 不能重复  unique(列名1,列名2,..)

3、普通索引：加快查询  index(列名)

创建及删除

创建

1、建表时创建

create table t1(
# 主键的创建
id int auto_increment primary key,  
name varchar(32) not null default '',
age int not null default 0,
num int not null default 0,
# 联合唯一索引的创建
unique uni_name_num (name,age),  
# 普通索引的创建
index ix_age (age)            
).engine=innodb charset=utf8;

2、表存在后创建

# 主键索引的创建
alter table t1 change id id int auto_increment primary key
# 唯一索引的创建
create unique index ix_name on t1(name)
create unique index ix_name_age on t1(name,age)
# 普通索引的创建
create index ix_age on t1(age)

删除

# 如果主键是自增的，则无法删除，要先取消自增再删除
alter table t1 change id id int;
alter table t1 drop primary key;

# 删除唯一索引和普通索引一样
drop index ix_name on t1;

索引的优缺点

优点：

　　查询数据速度快

缺点：

　　版本5.3以下：

　　　　删除和修改数据的速度会变得很慢，会重构索引

　　版本5.5以上：

　　　　删除和修改数据的速度不是特别慢

 

索引的使用

　　数据库表中添加索引后确实会让查询速度起飞，但前提必须是正确的使用索引

　　来查询，如果以错误的方式使用，则即使建立索引也不会生效

错误索引案例

# -like '%xx'
    select * from t1 where name like '%cn%';

# -使用函数
    select * from t1 where reverse(name) = 'xxx';

# -order by
    select email from t1 order by name desc;
    # 当根据索引排序时，选择的映射如果不是索引，则不走索引
    # 特别的：如果对主键排序，则还是走索引
        select * from t1 order by id;

explain  工具

　　查看sql语句是否用得上索引，或者查看sql执行效率的工具

　　给执行的sql语句出一个报告，通过此报告来判断sql语句的

　　执行效率和效果（不一定都按结果上来）

SQL语句的规则

　　1、不建议使用like进行搜索

　　2、不建议使用函数

　　3、组合索引最左前缀

　　　　如果组合索引为：(name,email)

　　　　where name and email 　　　　--使用索引

　　　　where name 　　　　　　　　  --使用索引

　　　　where email　　　　　　　　   --不适用索引

explain查询之后参数的含义（了解）

id
        查询顺序标识
    select_type
        查询类型
            SIMPLE          简单查询
            PRIMARY         最外层查询
            SUBQUERY        映射为子查询
            DERIVED         子查询
            UNION           联合
            UNION RESULT    使用联合的结果
            ...
    table
        正在访问的表名
    type
        查询时的访问方式，性能：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
            ALL             全表扫描，对于数据表从头到尾找一遍
                            select * from tb1;
                            特别的：如果有limit限制，则找到之后就不在继续向下扫描
                                   select * from tb1 where email = 'xxxx@live.com'
                                   select * from tb1 where email = 'xxxx@live.com' limit 1;
                                   虽然上述两个语句都会进行全表扫描，第二句使用了limit，则找到一个后就不再继续扫描。
            INDEX           全索引扫描，对索引从头到尾找一遍
                            select nid from tb1;
            RANGE          对索引列进行范围查找
                            select *  from tb1 where name < 'xxx';
                            PS:
                                between and
                                in
                                >   >=  <   <=  操作
                                注意：!= 和 > 符号
            REF             根据索引查找一个或多个值
                            select *  from tb1 where name = 'xxx';
            CONST           常量
                            表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次。
                            select nid from tb1 where nid = 2 ;
            SYSTEM          系统
                            表仅有一行(=系统表)。这是const联接类型的一个特例。
    possible_keys
        可能使用的索引
    key
        真实使用的
    key_len
        MySQL中使用索引字节长度
    rows
        mysql估计为了找到所需的行而要读取的行数 ------ 只是预估值
    extra
        该列包含MySQL解决查询的详细信息
        “Using index”
            此值表示mysql将使用索引覆盖，以避免访问表。不要把覆盖索引和index访问类型弄混了。
        “Using where”
            这意味着mysql服务器将在存储引擎检索行后再进行过滤，许多where条件里涉及索引中的列，当（并且如果）它读取索引时，就能被存储引擎检验，因此不是所有带where子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示：查询可受益于不同的索引。
        “Using temporary”
            这意味着mysql在对查询结果排序时会使用一个临时表。
        “Using filesort”
            这意味着mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。mysql有两种文件排序算法，这两种排序方式都可以在内存或者磁盘上完成，explain不会告诉你mysql将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成。