数据库优化-------创建索引与最左前缀原则

一.Mysql 创建索引

在执行CREATE TABLE语句时可以创建索引，也可以单独用CREATE INDEX或ALTER TABLE来为表增加索引。

1．ALTER TABLE

ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

 

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)

 

其中table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。索引名index_name可选，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。

2．CREATE INDEX

CREATE INDEX可对表增加普通索引或UNIQUE索引。

 

CREATE INDEX index_name ON table_name (column_list)

CREATE UNIQUE INDEX index_name ON table_name (column_list)

 

table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义，索引名不可选。另外，不能用CREATE INDEX语句创建PRIMARY KEY索引。

二.索引创建原则

1、表的主键、外键必须有索引；

2、数据量超过300的表应该有索引；

 

3、经常与其他表进行连接的表，在连接字段上应该建立索引；

 

4、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；

 

5、索引应该建在选择性高的字段上；

 

6、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；

 

7、复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：

8、经常出现order by、group by 、join等关键字的字段，应该建立索引；

哪些不需要建立索引？

1.经常插入、更新、删除的

2.表量级太小

3.大量重复数据的 例如性别 只有男女 

索引失效的情况：

 1. 对索引列进行运算导致索引失效,我所指的对索引列进行运算包括(+，-，*，/，! 等)

 错误的例子：select * from test where id-1=9;

 正确的例子：select * from test where id=10;

 2. 以下使用会使索引失效，应避免使用；

  a. 使用 <> 、not in 、not exist、!=

 b. like "%_" 百分号在前（可采用在建立索引时用reverse(columnName)这种方法处理）

 c. 单独引用复合索引里非第一位置的索引列.应总是使用索引的第一个列，如果索引是建立在多个列上, 只有在它的第一个列被where子句引用时，优化器才会选择使用该索引。

 d. 字符型字段为数字时在where条件里不添加引号.

 e. 当变量采用的是times变量，而表的字段采用的是date变量时.或相反情况。

数据库最左前缀原则

最左前缀原则：顾名思义是最左优先，以最左边的为起点任何连续的索引都能匹配上，

注：如果第一个字段是范围查询需要单独建一个索引

注：在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。

当创建(a,b,c)复合索引时，想要索引生效的话,只能使用 a和a,b和a,b,c三种组合

实例：以下是常见的几个查询：
mysql>SELECT `a`,`b`,`c` FROM A WHERE `a`='aa' ;

mysql>SELECT `a`,`b`,`c` FROM A WHERE  `b`='bb' AND `c`='cc';

mysql>SELECT `a`,`b`,`c` FROM A WHERE `a`='aa' AND `c`='cc';

请问：想要索引最大化的使用需要至少建几个索引

答：需要建立两个复合索引：a,c、b,c