MySQL++：MySQL复合索引、普通索引总结
对于复合索引:Mysql从左到右的使⽤索引中的字段，⼀个查询可以只使⽤索引中的⼀部份，但只能是最左侧部分。例如索引是key index (a,b,c). 可以⽀持a | a,b| a,b,c 3种组合进⾏查，但不⽀持 b,c进⾏查 .当最左侧字段是常量引⽤时，索引就⼗分有效。下⾯⽤⼏个例⼦对⽐查询条件的不同对性能影响.
create table test(
a int,
b int,
c int,
KEY a(a,b,c)
);
优: select * from test where a=10 and b>50
差: select * from test where a50
优: select * from test where order by a
差: select * from test where order by b
差: select * from test where order by c
优: select * from test where a=10 order by a
优: select * from test where a=10 order by b
差: select * from test where a=10 order by c
优: select * from test where a>10 order by a
差: select * from test where a>10 order by b
差: select * from test where a>10 order by c
优: select * from test where a=10 and b=10 order by a
优: select * from test where a=10 and b=10 order by b
优: select * from test where a=10 and b=10 order by c
优: select * from test where a=10 and b=10 order by a
优: select * from test where a=10 and b>10 order by b
差: select * from test where a=10 and b>10 order by c
索引原则
1.索引越少越好
原因：主要在修改数据时，第个索引都要进⾏更新，降低写速度。
2.最窄的字段放在键的左边
3.避免file sort排序，临时表和表扫描.
于是上⽹查了下相关的资料：（关于复合索引优化的）
两个或更多个列上的索引被称作复合索引。
利⽤索引中的附加列，您可以缩⼩搜索的范围，但使⽤⼀个具有两列的索引不同于使⽤两个单独的索引。复合索引的结构与电话簿类似，⼈名由姓和名构成，电话簿⾸先按姓⽒对进⾏排序，然后按名字对有相同姓⽒的⼈进⾏排序。如果您知道姓，电话簿将⾮常有⽤；如果您知道姓和名，电话簿则更为有⽤，但如果您只知道名不姓，电话簿将没有⽤处。
所以说创建复合索引时，应该仔细考虑列的顺序。对索引中的所有列执⾏搜索或仅对前⼏列执⾏搜索时，复合索引⾮常有⽤；仅对后⾯的任意列执⾏搜索时，复合索引则没有⽤处。
如：建⽴姓名、年龄、性别的复合索引。
复合索引的建⽴原则：
如果您很可能仅对⼀个列多次执⾏搜索，则该列应该是复合索引中的第⼀列。如果您很可能对⼀个两列索引中的两个列执⾏单独的搜索，则应该创建另⼀个仅包含第⼆列的索引。
如上图所⽰，如果查询中需要对年龄和性别做查询，则应当再新建⼀个包含年龄和性别的复合索引。
包含多个列的主键始终会⾃动以复合索引的形式创建索引，其列的顺序是它们在表定义中出现的顺序，⽽不是在主键定义中指定的顺序。在考虑将来通过主键执⾏的搜索，确定哪⼀列应该排在最前⾯。
请注意，创建复合索引应当包含少数⼏个列，并且这些列经常在select查询⾥使⽤。在复合索引⾥包含太多的列不仅不会给带来太多好处。⽽且由于使⽤相当多的内存来存储复合索引的列的值，其后果是内存溢出和性能降低。
复合索引对排序的优化：
复合索引只对和索引中排序相同或相反的order by 语句优化。
在创建复合索引时，每⼀列都定义了升序或者是降序。如定义⼀个复合索引：
Sql代码
1. CREATE INDEX idx_example
2. ON table1 (col1 ASC, col2 DESC, col3 ASC)
其中有三列分别是：col1 升序，col2 降序， col3 升序。现在如果我们执⾏两个查询
1：Select col1, col2, col3 from table1 order by col1 ASC, col2 DESC, col3 ASC
和索引顺序相同
2：Select col1, col2, col3 from table1 order by col1 DESC, col2 ASC, col3 DESC
和索引顺序相反
查询1，2 都可以别复合索引优化。
如果查询为：
Select col1, col2, col3 from table1 order by col1 ASC, col2 ASC, col3 ASC
排序结果和索引完全不同时，此时的查询不会被复合索引优化。
查询优化器在在where查询中的作⽤：
如果⼀个多列索引存在于列 Col1 和 Col2 上，则以下语句：Select  * from table where  col1=val1 AND col2=val2 查询优化器会试图通过决定哪个索引将到更少的⾏。之后⽤得到的索引去取值。
1．如果存在⼀个多列索引，任何最左⾯的索引前缀能被优化器使⽤。所以联合索引的顺序不同，影响索引的选择，尽量将值少的放在前⾯。
如：⼀个多列索引为 (col1 ，col2， col3)
那么在索引在列 (col1) 、(col1 col2) 、(col1 col2 col3) 的搜索会有作⽤。
Sql代码
1. SELECT * FROM tb WHERE  col1 = val1
2. SELECT * FROM tb WHERE  col1 = val1 and col2 = val2
3. SELECT * FROM tb WHERE  col1 = val1 and col2 = val2  AND col3 = val3
2．如果列不构成索引的最左⾯前缀，则建⽴的索引将不起作⽤。
如：
Sql代码
1. SELECT * FROM  tb WHERE  col3 = val3
2. SELECT * FROM  tb  WHERE  col2 = val2
3. SELECT * FROM  tb  WHERE  col2 = val2  and  col3=val3
3．如果⼀个 Like 语句的查询条件不以通配符起始则使⽤索引。
如：%车或 %车%  不使⽤索引。
车%              使⽤索引。
索引的缺点：
1.      占⽤磁盘空间。
2.      增加了插⼊和删除的操作时间。⼀个表拥有的索引越多，插⼊和删除的速度越慢。如要求快速录⼊的系统不宜建过多索引。
下⾯是⼀些常见的索引限制问题
1、使⽤不等于操作符(<>, !=)
下⾯这种情况，即使在列dept_id有⼀个索引，查询语句仍然执⾏⼀次全表扫描
select * from dept where staff_num <> 1000;
但是开发中的确需要这样的查询，难道没有解决问题的办法了吗？
有！
通过把⽤ or 语法替代不等号进⾏查询，就可以使⽤索引，以避免全表扫描：上⾯的语句改成下⾯这样的，就可以使⽤索引了。
Sql代码
1. select * from dept shere staff_num < 1000 or dept_id > 1000;
2、使⽤ is null 或 is not null
使⽤ is null 或is nuo null也会限制索引的使⽤，因为数据库并没有定义null值。如果被索引的列中有很多null，就不会使⽤这个索引（除⾮索引是⼀个位图索引，关于位图索引，会在以后的blog⽂章⾥做详细解释）。在sql语句中使⽤null会造成很多⿇烦。
解决这个问题的办法就是：建表时把需要索引的列定义为⾮空(not null)
3、使⽤函数
如果没有使⽤基于函数的索引，那么where⼦句中对存在索引的列使⽤函数时，会使优化器忽略掉这些索引。下⾯的查询就不会使⽤索引：Sql代码
1. select * from staff where trunc(birthdate) = '01-MAY-82';
但是把函数应⽤在条件上，索引是可以⽣效的，把上⾯的语句改成下⾯的语句，就可以通过索引进⾏查。
Sql代码
1. select * from staff where birthdate < (to_date('01-MAY-82') + 0.9999);
4、⽐较不匹配的数据类型
⽐较不匹配的数据类型也是难于发现的性能问题之⼀。
下⾯的例⼦中，dept_id是⼀个varchar2型的字段，在这个字段上有索引，但是下⾯的语句会执⾏全表扫描。
Sql代码
1. select * from dept where dept_id = 900198;
这是因为oracle会⾃动把where⼦句转换成to_number(dept_id)=900198，就是3所说的情况，这样就限制了索引的使⽤。
把SQL语句改为如下形式就可以使⽤索引
Sql代码
1. select * from dept where dept_id = '900198';
还有就是参见⽼王的blog上的⽂章
1、普通索引
  普通索引（由关键字KEY或INDEX定义的索引）的唯⼀任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件（WHEREcolumn=）或排序条件（ORDERBYcolumn）中的数据列创建索引。只要有可能，就应该选择⼀个数据最整齐、最紧凑的数据列（如⼀个整数类型的数据列）来创建索引。
  2、唯⼀索引
  普通索引允许被索引的数据列包含重复的值。⽐如说，因为⼈有可能同名，所以同⼀个姓名在同⼀个“员⼯个⼈资料”数据表⾥可能出现两次或更多次。
  如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该⽤关键字UNIQUE把它定义为⼀个唯⼀索引。这么做的好处：⼀是简化了MySQL对这个索引的管理⼯作，这个索引也因此⽽变得更有效率；⼆是MySQL会在有新记录插⼊数据表时，⾃动检查新记录的这个字段的值是否已经在某个记录的这个字段⾥出现过了；如果是，MySQL将拒绝插⼊那条新记录。也就是说，唯⼀索引可以保证数据记录的唯⼀性。事实上，在许多场合，⼈们创建唯⼀索引的⽬的往往不是为了提⾼访问速度，⽽只是为了避免数据出现重复。
  3、主索引
  在前⾯已经反复多次强调过：必须为主键字段创建⼀个索引，这个索引就是所谓的“主索引”。主索引与唯⼀索引的唯⼀区别是：前者在定义时使⽤的关键字是PRIMARY⽽不是UNIQUE。
  4、外键索引
  如果为某个外键字段定义了⼀个外键约束条件，MySQL就会定义⼀个内部索引来帮助⾃⼰以最有效率的⽅式去管理和使⽤外键约束条件。
  5、复合索引
  索引可以覆盖多个数据列，如像INDEX（columnA，columnB）索引。这种索引的特点是MySQL可以有选择地使⽤⼀个这样的索引。如果查询操作只需要⽤到columnA数据列上的⼀个索引，就可以使⽤复合索引INDEX（columnA,columnB）。不过，这种⽤法仅适⽤于在复合索引中排列在前的数据列组合。⽐如说，INDEX（A，B，C）可以当做A或（A,B）的索引来使⽤，但不能当做B、C或（B，C）的索引来使⽤。
  6、索引的长度
  在为CHAR和VARCHAR类型的数据列定义索引时，可以把索引的长度限制为⼀个给定的字符个数（这个数字必须⼩于这个字段所允许的最⼤字符个数）。这么做的好处是可以⽣成⼀个尺⼨⽐较⼩、检索速
查看mysql索引度却⽐较快的索引⽂件。在绝⼤多数应⽤⾥，数据库中的字符串数据⼤都以各种各样的名字为主，把索引的长度设置为10~15个字符已经⾜以把搜索范围缩⼩到很少的⼏条数据记录了。在为BLOB和TEXT类型的数据列创建索引时，必须对索引的长度做出限制；MySQL所允许的最⼤索引全⽂索引⽂本字段上的普通索引只能加快对出现在字段内容最前⾯的字符串（也就是字段内容开头的字符）进⾏检索操作。如果字段⾥存放的是由⼏个、甚⾄是多个单词构成的较⼤段⽂字，普通索引就没什么作⽤了。这种检索往往以的形式出现，这对MySQL来说很复杂，如果需要处理的数据量很⼤，响应时间就会很长。
  这类场合正是全⽂索引（full-textindex）可以⼤显⾝⼿的地⽅。在⽣成这种类型的索引时，MySQL将把在⽂本中出现的所有单词创建为⼀份清单，查询操作将根据这份清单去检索有关的数据记录。全⽂索引即可以随数据表⼀同创建，也可以等⽇后有必要时再使⽤下⾯这条命令添加：
  ALTERTABLEtablenameADDFULLTEXT（column1，column2）有了全⽂索引，就可以⽤SELECT查询命令去检索那些包含着⼀个或多个给定单词的数据记录了。下⾯是这类查询命令的基本语法：
  SELECT*FROMtablename
  WHEREMATCH（column1,column2）AGAINST（‘word1','word2','word3’）
  上⾯这条命令将把column1和column2字段⾥有word1、word2和word3的数据记录全部查询出来。
总结：复合索引最⽜C，所以你懂的。。！

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。