MySQL系列(14)： 索引设计与使用

　　所有 MySQL列类型都可以被索引，对相关列使用索引是提高select操作性能的最佳途径。

　　MySQL中最常用的存储引擎有InnoDB和MyISAM，这两种存储引擎的表默认创建的都是B-Tree索引；MEMORY存储引擎使用HASH索引，也支持B-Tree索引。

索引类型

• 普通索引：这是最基本的索引，无任何限制。普通索引还包括了 前缀索引和组合索引。
  • 前缀索引：指定字段开始的部分字符作为索引，一般用在字段内容多的时间，如：varchar,Blob,TEXT等。
  • 组合索引：基于多列创建的索引，也叫复合索引，列顺序非常重要。
• 主键索引：是一种特殊的唯一索引，不允许有NULL值，通常在建表时创建。
  • 联合主键：基于多列创建的主键。
• 唯一索引：与普通索引类似，索引列值必须唯一，允许NULL值。
• 全文索引：基于词干方式创建索引，多用于 BLOB 数据类型。
• 空间索引，用作地理数据存储。

索引优缺点

优点

• 大大减少了服务器需要扫描的数据量。
• 可以帮助服务器避免排序或减少使用临时表排序。
• 索引可以随机I/O变为顺序I/O。
• 前缀索引能使索引更小，更快的有效办法。

缺点

• 需要占用磁盘空间，因此冗余低效的索引将占用大量的磁盘空间。
• 降低DML性能，对于数据的任意增删改都需要调整对应的索引，甚至出现索引分裂。
• 索引会产生相应的碎片，产生维护开销。
• 前缀索引无法做ORDER BY和GROUP BY，无法做覆盖扫描。

索引设计

1. 最适索引的列是出现在WHERE子句中的列。
2. 索引列的基数越大，效果越好(尽可能不同的值，容易区分行)。
3. 具有唯一特性的字段，即使多个字段的组合，也应建立唯一索引。
4. 使用短索引，长字符串列索引可以使用前缀索引，指定前缀长度，节省空间，查询更快，长度为 20 的索引，区分度会高达 90% 以上，可以使用 count(distinct left(列名, 索引长度))/count(*) 的区分度来确定。
5. Order By 最后的字段是组合索引的一部份，并且放在索引组合顺序的最后。
6. 建立组合索引时，区分度最高的在最左边，即利用最左匹配原则。
7. 不要过度索引，只保留有利于查询优化的索引。
8. InnoDB存储引擎的表尽量指定一个主键。
   InnoDB存储引擎的表默认会按照一定顺序保存。如果有明确定义的主键，则按照主键顺序保存。如果没有主键但有唯一索引，就按照唯一索引的顺序保存。如果即没有主键，也没有唯一索引，那么表中会自动生成一个内部列，按这个列的顺序保存。按照主键或内部列进行的访问是最快的， 所以尽可能指定主键。当表中同时有几个列都是唯一的，都可以作为主键的时候，要选择最常作为访问的条件的列作为主键，提高查询效率。

   `InnoDB`表的普通索引都会保存主键的值，所以主键要选择尽可能短的数据类型，可以有效减少索引的磁盘占用，提高索引缓存效果。
   

注意：虽然为列索引创建索引带来的查询性能效果是显著的，但并不是为每一个列创建一个索引为好，不必要的索引会浪费空间和时间，索引也会增加插入、更新和删除的成功，因为每个索引都必须更新。创建 MySQL 决定使用的那些索引，才能使用最坐索引集快速查询。

索引操作

MySQL，索引关键字index和key两个通用。
一个表中只有一个主键索引，

查看索引

查看表创建的索引

-- 查看已创建的索引
SHOW INDEX FROM tb_name;
SHOW KEYS FROM tb_name;

创建索引

1. 在执行CREATE TABLE语句时创建索引。
2. 使用CREATE INDEX或AALTER TABLE为表增加索引。

   -- create 创建表时创建索引
   CREATE TABLE userinfo (
     id INT NOT NULL,
     age INT (11),
     last_name CHAR(30) NOT NULL,
     first_name CHAR(30) NOT NULL,
     PRIMARY KEY (id), --主键索引(唯一)
     INDEX idx_age (age), -- 单列索引
     INDEX idx_last_first_name (last_name, first_name) --复合索引
   ) ;
   
   CREATE TABLE `student` (
     `id` int(11) NOT NULL,
     `age` int(11) DEFAULT NULL,
     `last_name` char(30) NOT NULL,
     `first_name` char(30) NOT NULL,
     PRIMARY KEY (`id`),
     KEY `idx_last_first_name` (`last_name`,`first_name`),
     KEY `idx_age` (`age`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8
   
   -- alert 添加索引：index_name可选，缺省时将根据第一个索引赋一个名称
   ALTER TABLE table_name ADD INDEX index_name (column_list)
   ALTER TABLE table_name ADD UNIQUE (column_list)
   ALTER TABLE table_name ADD PRIMARY KEY (column_list)
   -- 示例
   ALTER TABLE userinfo ADD INDEX idx_first_name (first_name); 
   -- 一次修改多个索引
   ALTER TABLE userinfo ADD PRIMARY KEY (id), ADD INDEX idx_age (age); 
   ALTER TABLE `MyTest`.`userinfo` ADD PRIMARY KEY (`id`), ADD INDEX `idx_age` (`age`); 
   
   -- create index 添加索引，index_name必填，不可创建PRIMARY KEY
   CREATE INDEX index_name ON table_name (column_list)
   CREATE UNIQUE INDEX index_name ON table_name (column_list)
   -- 示例
   create index idx_firstname on userinfo (first_name);

   修改索引

   MySQL 并没有提供直接修改索引的操作，而是通过ALTER DROP或DROP先删除索引，再重新创建来实现的。

删除索引

1. DROP INDEX或ALTER TABLE来删除索引。

   -- 删除索引
   DROP INDEX index_name ON talbe_name
   ALTER TABLE table_name DROP INDEX index_name
   ALTER TABLE table_name DROP PRIMARY KEY
   -- 示例
   DROP INDEX idx_age ON userinfo;
   ALTER TABLE userinfo DROP INDEX idx_firstname;