MySQL数据存储引擎/索引

1.引擎

        概述：

        存储引擎就是存储数据，建立索引，更新/查询数据技术实现的方式。存储引擎是基于表的，而不是基于库的，也称为表类型

• 在创建表时可以指定存储引擎

                语法：

CREATE TABLE (         字段1 类型          ........          字段n  类型 )ENGINE = InnDB(默认)

查看存储引擎

show  engines

• InnoDB存储引擎的特点

                介绍：是一种高可靠高性能的存储引擎

        1）支持事务

        2）行级锁，提高并发访问性能

        3）支持外键，保证数据的完整性和正确性

• MyISAM存储引擎特点

                介绍：是早期的默认存储引擎

        1）不支持事务，不支持外键

        2）支持表锁，不支持行锁

        3）访问速度快

• Memory存储引擎特点

                介绍：Memory表数据是存储在内存中的，由于受到硬件问题，或断电问题的影响，只能将这些表作为临时表或缓存使用

        1）内存存放

        2）支持hash索引

2.索引

        概述：索引是帮助mysql高效获取数据的一种数据结构

        mysql的索引是在存储引擎层实现的，不同的存储引擎有不同的结构：

索引结构	描述
B+tree索引	最常用的索引结构，大部分存储引擎都支持
Hash索引	底层数据结构使用哈希索引实现的，只有精确匹配到列查询才有效，不支持范围查询的
R-tree(空间索引)	空间索引是MyISM的一种特殊索引，用的较少
Full-text(全文索引)	是一种通过倒排索引，快速匹配文档的方式

        支持的存储引擎

         hash索引的特点：

                1）hash索引只能用来对等比较，不支持范围索引

                2）无法利用hash索引来排序

                3）查询效率高，通常只需要一次检索就可以了，效率通常要高于B+tree索引

• 索引分类

          在InnoDB存储引擎中根据索引的存储形式分为两种 ：

分类	含义	特点
聚焦索引	将数据存储和索引放到了一块，索引结构的叶子节点保存了行数据	必须有，而且只有一个
二级索引	将数据与索引分开存储，叶子节点关联的是对应的主键	可以存在多个

• 索引语法

        创建索引

CREATE [INDEX|FULLTEXT]  INDEX 索引名  ON  表名

        查看索引

SHOW INDEX FROM  表名

        删除索引

DROP  INDEX  索引名  ON  表名

具体语法

 

 

         查看sql执行频率

SHOW GLOBAL STATUS 'Com______'

慢查询日志：指查询时间超过指定参数的语句

        查看执行耗时语句

•  explain执行计划

EXPLAIN  SELECT  字段列表  FROM  表名  WHERE  条件

         注意：当数据量庞大时，可以针对某个字段创建索引，这样可以提高查询效率

• 最左前缀法则

                概述：联合索引，需要遵守最左前缀法则。指查询从最左列开始，不跳过索引的每一列。如果跳过某一列，后面的索引列失效；

        具体用法

这是一个联合索引

 

         索引的使用

• 对所有进行运算操作，索引会失效
• 对于字符串类型，不加引号，索引会失效
• 模糊查询，对尾部进行模糊查询，索引不会失效，对头部进行模糊查询索引会失效
• or连接时，or后面的字段没有索引，索引将会失效
• 当查询的语句是查询表中大部分数据时，索引会失效

        强制使用某个索引

•    覆盖索引

在查询中，尽量使用覆盖索引，避免使用select*。

   提醒：

using index condition：查找使用了索引，但是需要回表查询数据

using where，using index：查找使用了索引，但是需要的数据在索引中都能找到，不不需要回表查询

• 前缀索引

        概述：当字符串类型字段较长时，可以只将字符串的一部分建立索引，这样可以减少索引空间，提高索引效率。

        语法

        索引长度

3. 索引设计原则