MySql索引基础指南
MySql索引指南
索引概述
所有MYSQL列类型都能被索引，对相关列使用索引是提高SELECT操作性能的最佳途径
索引类型
1.主键索引：数据列不允许重复，不允许为NULL，一个表只有一个主键。
2.唯一索引：数据列不允许重复，允许为NULL，一个表允许多个列创建唯一索引。
3.普通索引：基本的索引类型，没有唯一性的限制，允许为NULL值。
4.全文索引：是目前搜索引擎使用的一种关键技术，对文本的内容进行分词、搜索。
5.覆盖索引：查询列要被创建的索引覆盖，不必读取数据行。
6.组合索引：多列值组成一个索引，用于组合搜索，效率大于索引合并。
等
一、创建索引
普通索引
create index index_name on table(column(length));
alter table table_name add index index_name(column(length));
唯一索引
create index index_name on table(column(length));
alter table table_name add index index_name(column(length));
全文索引
create fulltext index index_name on table(column(length));
alter table table_name add fulltext index_name(column);
组合索引
alter table table_name add index index_name_(title(50),time(10),......);
二、查看索引
show index from table_name
三、删除索引
DROP INDEX deptname_index on dept
四、设计索引原则
1.要在条件列上创建索引
就是在where子句中的列上
2.尽量使用唯一索引
当为性别，只有两种值得情况下，唯一索引就没有使用必要，因为，都能得到大约一半得值
3.使用短索引
对字符串索引，应该指定一个前缀长度，当有char（200）的列时，当前10~20个字符就能区分出来时就只需要选择前10-20个字符
4.利用最左前缀
要运用组合索引时，必须从遵循最左原则(就是最左边的列必须出现在where后的第一个)
在创建一个n列的索引，实际相当于创建了MySQL可利用的n个索引。
多列索引可以起到几个索引的作用，因为可以利用最左边的列集来匹配行。这样的列集称为最左前缀。列入以a，b，c的顺序创建一个组合索引之后，利用a = ？或者a = ？and b= ？或者a = ？and b = ？and c = ？这三种条件查询，能有效提高效率。
5.对于InnoDB存储引擎的表，尽量手工指定主键。
默认主键顺序保存，没有主键，有唯一索引，按唯一索引顺序保存。都没有表自动生成一个内部列，按这个顺序来。当表有多个唯一，且都可以为主键，要选择常用，且短的。
五、索引设计的误区
1.不是所有表都需要创建索引
通常来说，常见的代码表、配置表等数据量很小的表。除了主键外，没有创建索引的必要。而大表的CRUD尽可能通过索引，如果通过全表扫描，极其浪费性能。
2.不要过度使用索引
索引都要额外占用磁盘空间，并降低写操作。在修改表的内容，索引必须进行更新。
3.谨慎创建低选择度索引
对于选择性低，且分布不均的列，过滤的结果集大，效果不好。例如性别只有两个选择。
六、索引设计的一般步骤
通过上面介绍，对于大表的设计步骤如下
1.整理表上所有sql，重点包括select，update，delete操作的where条件所用到的列的组合，
关联查询的关联条件
2.整理所有sql的预期执行频率
3.整理所有涉及列的选择度，选择度越高越好，比如主键列选择度最高。
4.给表选择合适的主键
5.有限给执行频率最高的SQL创建索引，
6.按执行频率排序，依次检查是否需要为每个SQL创建索引
7.索引合并，利用复合索引降低索引的总数，充分利用最左原则。
浙公网安备 33010602011771号