MySQL数据库---存储引擎及索引

存储引擎

常用的存储引擎： Innodb 和 MyIASM

两种存储引擎比较

Innodb 和 MyIASM 比较
1、MyISAM不支持事务，InnoDB是事务类型的存储引擎
2、MyISAM只支持表级锁(BDB支持页级锁和表级锁默认为页级锁）而InnoDB支持行级锁和表级锁默认为行级锁
3、DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除。

不考虑并发情况和事务情况下： MyIASM 数据引擎 完爆 Innodb 数据引擎
在有索引的情况下 MyIASM查询可以加快4206.09733倍 Innodb查询加快510.72921倍

索引

索引 可以优化 查询速度 但是也会降低数据的写入速度

索引类型

1. 普通索引 INDEX 最基本的索引，没有任何限制
2. 唯一索引 UNIQUE 与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值（注意和主键不同）如果是组合索引，则列值的组合必须唯一
3. 全文索引 FULLTEXT 空间索引
4. 主键索引 PRIMARY KEY 数据记录里面不能有 null,数据内容不能重复，在一张表里面不能有多个主键索引，一般是在创建表的时候自动生成。
5. 组合索引 组合索引一般适用于特定的sql语句

命中索引的查询

MySQL只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE才会使用索引(查询数据前面有% _ 不会命中索引)，所以如果是其他场景，你建立了索引也没有用的

是否命中索引 (explain)

//使用 explain + SQL 语句
explain select * from users where id = 1;
//返回数据格式：
+----+-------------+-------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table       | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | admin_users | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL  |
+----+-------------+-------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
//id   SQL执行的顺序的标识
//select_type   查询中每个select子句的类型 
    SIMPLE (简单SELECT语句)
    PRIMARY (查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)
    UNION (UNION中的第二个或后面的SELECT语句)
    DEPENDENT UNION (UNION中的第二个或后面的SELECT语句，取决于外面的查询)
    UNION RESULT  (UNION的结果)
    SUBQUERY (子查询中的第一个SELECT)
    DEPENDENT SUBQUERY  (子查询中的第一个SELECT，取决于外面的查询)
    DERIVED  (派生表的SELECT, FROM子句的子查询)
    UNCACHEABLE SUBQUERY (一个子查询的结果不能被缓存，必须重新评估外链接的第一行)
    
//table 显示这一行的数据是关于哪张表的，有时不是真实的表名字,看到的是derivedx(x是个数字,我的理解是第几步执行的结果)

//type 访问类型：ALL、index、range、ref、eq_ref、const、system、NULL（从左到右，性能从差到好）    
    ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行
    INDEX: Full Index Scan，index与ALL区别为index类型只遍历索引树
    RANGE: 只检索给定范围的行，使用一个索引来选择行
    REF: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
    eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
    const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，
                   MySQL就能将该查询转换为一个常量,system是const类型的特例，当查询的表只有一行的情况下，使用system
    NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。
    
//partitions 

//possible_keys 指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用

//Key key列显示MySQL实际决定使用的键（索引）

//key_len 索引中使用的字节数，可通过该列计算查询中使用的索引的长度
（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）不损失精确性的情况下，长度越短越好
    
//ref  上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

//rows   MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

//Extra   MySQL解决查询的详细信息