存储引擎 索引

存储引擎

mysql默认存储引擎为innodb，可以通过以下代码在创建表时指定存储引擎

create table my_myisam(
    --
    --
) engine = MyIsaM;

 

查看当前数据库支持的存储引擎

show engines;

在MySQL的InnoDB引擎支持行锁，与Oracle不同，MySQL的行锁是通过索引加载的，如果对应的SQL语句没有走索引，则会全表扫描，行锁变为表锁。行锁又分为共享锁和排他锁，共享锁允许两个事务可以锁同一个索引，排它锁不允许。insert，delete，update在事务中都会默认加上排它锁。

 

 

 

引擎选择

 

索引分类

 

聚集索引：

数据行的物理存储顺序与列值（一般是主键的那一列）的 逻辑顺序相同，一个表中只能拥有一个聚集索引，而且聚集索引的叶子节点保存了完整的数据记录，可以直接获取。

二级索引：

也称辅助索引，非聚集索引，

数据行的物理存储顺序与列值（一般是主键的那一列）的 逻辑顺序不同，一个表中可以有多个非聚集索引，而且非聚集索引的叶子节点保存的是指向数据结点的指针，需要两次查询，第一次查询指针，第二次查询数据。

 

如图聚集索引叶子节点是数据，辅助索引叶子节点是主键

 

创建索引

create [unique | fulltext] index index_name on table_name (index_col_name,...);

-- 一个索引可以关联多个字段，称作联合索引或组合索引
-- 关联单个字段称作单列索引

查看索引

show index from table_name;

删除索引

drop index index_name on table_name;

索引性能分析

 

 

 

 

 explain执行计划

 

 最左前缀法则

 在使用联合索引时，如果存在范围查询，尽量加=，不加=也会导致后边的索引失效

索引失效

• 在索引列上进行运算操作，索引失效
• 字符串类型字段使用时，不加引号，索引失效
• 模糊查询时，如果仅仅是尾部模糊匹配，则不会失效，如果是头部模糊匹配，则失效
• or连接的条件，当一边有索引，一边没有时，则失效，只有两边都有索引，才会成功
• 如果mysql评估使用索引比全表更慢，则不使用索引

sql提示

 覆盖索引

查询使用的索引包含（或者说覆盖）所有需要查询字段的值，我们就称 之为“覆盖索引”。只需要查询索引就可以获取结果，不需要访问数据行。

注意：二级索引的叶子节点的值会包含主键，所以如果查询字段中出现了主键，则也认为包含在查询索引中

前缀索引

将长字符串的一部分前缀，建立索引，节省索引空间

• 语法

create index idx_xxxx on table_name(column(n));
-- n为前缀长度

• 前缀长度

　　可以根据索引的选择性决定，选择性是指不重复的索引值和数据表记录总数的比值，索引值越高则查询效率越高

唯一索引的选择性为1

select count(distinct email) / count(*) from tb_user;

select count(distinct substring(email, 1, 10)) / count(*) from tb_user;