mysql 索引详解

• 索引存储方式
  • MyISAM与InnoDB的区别是什么？
    • 存储结构
      • MyISAM：每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。
      • InnoDB：所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。
    • 存储空间
      • MyISAM：可被压缩，存储空间较小。支持三种不同的存储格式：静态表(默认，但是注意数据末尾不能有空格，会被去掉)、动态表、压缩表。
      • InnoDB：需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。
    • 可移植性、备份及恢复
      • MyISAM：数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。
      • InnoDB：免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了。
    • 事务支持
      • MyISAM：强调的是性能，每次查询具有原子性,其执行数度比InnoDB类型更快，但是不提供事务支持。
      • InnoDB：提供事务支持事务，外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
    • AUTO_INCREMENT
      • MyISAM：可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，他可以根据前面几列进行排序后递增。
      • InnoDB：InnoDB中必须包含只有该字段的索引。引擎的自动增长列必须是索引，如果是组合索引也必须是组合索引的第一列。
    • 表锁差异
      • MyISAM：只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。
      • InnoDB：支持事务和行级锁，是innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。
    • 全文索引
      • MyISAM：支持 FULLTEXT类型的全文索引
      • InnoDB：不支持FULLTEXT类型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。
    • 表主键
      • MyISAM：允许没有任何索引和主键的表存在，索引都是保存行的地址。
      • InnoDB：如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值。
    • 表的具体行数
      • MyISAM：保存有表的总行数，如果select count(*) from table;会直接取出出该值。
      • InnoDB：没有保存表的总行数，如果使用select count(*) from table；就会遍历整个表，消耗相当大，但是在加了wehre条件后，myisam和innodb处理的方式都一样。
    • CURD操作
      • MyISAM：如果执行大量的SELECT，MyISAM是更好的选择。
      • InnoDB：如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表。DELETE 从性能上InnoDB更优，但DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除，在innodb上如果要清空保存有大量数据的表，最好使用truncate table这个命令。
    • 外键
      • MyISAM：不支持
      • InnoDB：支持
• 为什么使用索引
  • 在无索引的情况下，MySQL会扫描整张表来查找符合sql条件的记录，其时间开销与表中数据量呈正相关。对关系型数据表中的某些字段建索引可以极大提高查询速度
• 索引类型
  • 添加PRIMARY KEY（主键索引）
    • ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
  • 添加UNIQUE(唯一索引)
    • ALTER TABLE `table_name` ADD UNIQUE (`column`)
  • 添加INDEX(普通索引)
    • ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
  • 添加FULLTEXT(全文索引)
    • ALTER TABLE `table_name` ADD FULLTEXT ( `column`)
  • 添加复合索引
    • ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )
• 删除索引
  • DROP INDEX index_name ON talbe_name
  • ALTER TABLE table_name DROP INDEX index_name
• 查看索引
  • show index from table_name;
• explain
  • 
  • simple 表示简单的select,没有union和子查询
  • type 连接类型
  • possible_keys 提示使用哪个索引会在该表中找到行，不太重要
  • keys 使用的索引，简单且重要
  • key_len 使用的索引长度
  • ref   列显示使用哪个列或常数与key一起从表中选择行
  • rows 显示MYSQL执行查询的行数，简单且重要，数值越大越不好，说明没有用好索引
  • Extra  该列包含MySQL解决查询的详细信息。
• 创建索引注意事项
  • 索引不会包含有NULL值的列
    • 只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
  • 使用短索引
    • 对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
  • 索引列排序
    • MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
  • like语句操作（最左原则）
    • 一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
• 索引的不足之处
  • 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
  • 建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。
  • 索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。
• 不同的索引的应用场景
  • 当数据多且字段值有相同的值得时候用普通索引。
  • 当字段多且字段值没有重复的时候用唯一索引。
  • 当有多个字段名都经常被查询的话用复合索引。
  • 主键索引属于一种特殊的唯一索引，不允许有空值
  • 但是，若是这张表增删改多而查询较少的话，就不要创建索引了，因为如果你给一列创建了索引，那么对该列进行增删改的时候，都会先访问这一列的索引，
  • 若是增，则在这一列的索引内以新填入的这个字段名的值为名创建索引的子集，
  • 若是改，则会把原来的删掉，再添入一个以这个字段名的新值为名创建索引的子集，
  • 若是删，则会把索引中以这个字段为名的索引的子集删掉。
  • 所以，会对增删改的执行减缓速度，
  • 所以，若是这张表增删改多而查询较少的话，就不要创建索引了。
  • 更新太频繁地字段不适合创建索引。
  • 不会出现在where条件中的字段不该建立索引。
• 应用场景
  • 快速查找符合where条件的记录
  • 多表做join操作时应该使用索引
  • 若某字段已建立索引，求该字段的min()或max()时，MySQL会使用索引
  • 对建立了索引的字段做sort或group操作时，MySQL会使用索引
• 查看表结构
  • desc 表名
• 索引存储结构
  • 存储结构分四类：顺序存储、链接存储、索引存储 和 散列存储。
    • 顺序存储：在计算机中用一组地址连续的存储单元依次存储线性表的各个数据元素,称作线性表的顺序存储结构。
      • 随机存取表中元素。
      • 插入和删除操作需要移动元素。
    • 链接存储：在计算机中用一组任意的存储单元存储线性表的数据元素(这组存储单元可以是连续的,也可以是不连续的)。它不要求逻辑上相邻的元素在物理位置上也相邻.因此它没有顺序存储结构所具有的弱点,但也同时失去了顺序表可随机存取的优点。
      • 比顺序存储结构的存储密度小 (每个节点都由数据域和指针域组成，所以相同空间内假设全存满的话顺序比链式存储更多)。
      • 逻辑上相邻的节点物理上不必相邻。
      • 插入、删除灵活 (不必移动节点，只要改变节点中的指针)。
      • 查找结点时链式存储要比顺序存储慢。
      • 每个结点是由数据域和指针域组成。
    • 索引存储：除建立存储结点信息外，还建立附加的索引表来标识结点的地址。索引表由若干索引项组成。
      • 索引存储结构是用结点的索引号来确定结点存储地址，其优点是检索速度快，缺点是增加了附加的索引表,会占用较多的存储空间。
    • 散列存储：散列存储，又称hash存储，是一种力图将数据元素的存储位置与关键码之间建立确定对应关系的查找技术。
      • 基本思想：由节点的关键码值决定节点的存储地址。散列技术除了可以用于查找外，还可以用于存储。
      • 散列是数组存储方式的一种发展，相比数组，散列的数据访问速度要高于数组，因为可以依据存储数据的部分内容找到数据在数组中的存储位置，进而能够快速实现数据的访问，理想的散列访问速度是非常迅速的，而不像在数组中的遍历过程，采用存储数组中内容的部分元素作为映射函数的输入，映射函数的输出就是存储数据的位置，这样的访问速度就省去了遍历数组的实现，因此时间复杂度可以认为为O(1)，而数组遍历的时间复杂度为O(n)。