网站建设公司南昌,温岭市市住房和城乡建设规划局网站,360建站工具,南宁seo外包平台MySQL中的联合索引的学习要点总结联合索引又叫复合索引。对于复合索引:Mysql从左到右的使用索引中的字段#xff0c;一个查询可以只使用索引中的一部份#xff0c;但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找#xff0c;但不…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查询里使用。在复合索引里包含太多的列不仅不会给带来太多好处。而且由于使用相当多的内存来存储复合索引的列的值其后果是内存溢出和性能降低。复合索引对排序的优化复合索引只对和索引中排序相同或相反的order by 语句优化。在创建复合索引时每一列都定义了升序或者是降序。如定义一个复合索引CREATE INDEX idx_exampleON table1 (col1 ASC, col2 DESC, col3 ASC)其中 有三列分别是col1 升序col2 降序 col3 升序。现在如果我们执行两个查询1Select col1, col2, col3 from table1 order by col1 ASC, col2 DESC, col3 ASC和索引顺序相同2Select col1, col2, col3 from table1 order by col1 DESC, col2 ASC, col3 DESC和索引顺序相反查询12 都可以别复合索引优化。如果查询为Select col1, col2, col3 from table1 order by col1 ASC, col2 ASC, col3 ASC排序结果和索引完全不同时此时的查询不会被复合索引优化。查询优化器在在where查询中的作用如果一个多列索引存在于 列 Col1 和 Col2 上则以下语句Select * from table where col1val1 AND col2val2 查询优化器会试图通过决定哪个索引将找到更少的行。之后用得到的索引去取值。1 如果存在一个多列索引任何最左面的索引前缀能被优化器使用。所以联合索引的顺序不同影响索引的选择尽量将值少的放在前面。如一个多列索引为 (col1 col2 col3)那么在索引在列 (col1) 、(col1 col2) 、(col1 col2 col3) 的搜索会有作用。SELECT * FROM tb WHERE col1 val1SELECT * FROM tb WHERE col1 val1 and col2 val2SELECT * FROM tb WHERE col1 val1 and col2 val2 AND col3 val32 如果列不构成索引的最左面前缀则建立的索引将不起作用。如SELECT * FROM tb WHERE col3 val3SELECT * FROM tb WHERE col2 val2SELECT * FROM tb WHERE col2 val2 and col3val33 如果一个 Like 语句的查询条件不以通配符起始则使用索引。如%车 或 %车% 不使用索引。车% 使用索引。索引的缺点1. 占用磁盘空间。2. 增加了插入和删除的操作时间。一个表拥有的索引越多插入和删除的速度越慢。如 要求快速录入的系统不宜建过多索引。下面是一些常见的索引限制问题1、使用不等于操作符(, !)下面这种情况即使在列dept_id有一个索引查询语句仍然执行一次全表扫描select * from dept where staff_num 1000;但是开发中的确需要这样的查询难道没有解决问题的办法了吗有通过把用 or 语法替代不等号进行查询就可以使用索引以避免全表扫描上面的语句改成下面这样的就可以使用索引了。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子句中对存在索引的列使用函数时会使优化器忽略掉这些索引。下面的查询就不会使用索引select * from staff where trunc(birthdate) 01-MAY-82;但是把函数应用在条件上索引是可以生效的把上面的语句改成下面的语句就可以通过索引进行查找。select * from staff where birthdate (to_date(01-MAY-82) 0.9999);4、比较不匹配的数据类型比较不匹配的数据类型也是难于发现的性能问题之一。下面的例子中dept_id是一个varchar2型的字段在这个字段上有索引但是下面的语句会执行全表扫描。select * from dept where dept_id 900198;这是因为oracle会自动把where子句转换成to_number(dept_id)900198就是3所说的情况这样就限制了索引的使用。把SQL语句改为如下形式就可以使用索引select * from dept where dept_id 900198;恩这里还有要注意的比方说有一个文章表我们要实现某个类别下按时间倒序列表显示功能SELECT * FROM articles WHERE category_id ... ORDER BY created DESC LIMIT ...这样的查询很常见基本上不管什么应用里都能找出一大把类似的SQL来学院派的读者看到上面的SQL可能会说SELECT *不好应该仅仅查询需要的字段那我们就索性彻底点把SQL改成如下的形式SELECT id FROM articles WHERE category_id ... ORDER BY created DESC LIMIT ...我们假设这里的id是主键至于文章的具体内容可以都保存到memcached之类的键值类型的缓存里如此一来学院派的读者们应该挑不出什么毛病来了下面我们就按这条SQL来考虑如何建立索引不考虑数据分布之类的特殊情况任何一个合格的WEB开发人员都知道类似这样的SQL应该建立一个”category_id, created“复合索引但这是最佳答案不不见得现在是回头看看标题的时候了MySQL里建立索引应该考虑数据库引擎的类型如果我们的数据库引擎是InnoDB那么建立”category_id, created“复合索引是最佳答案。让我们看看InnoDB的索引结构在InnoDB里索引结构有一个特殊的地方非主键索引在其BTree的叶节点上会额外保存对应主键的值这样做一个最直接的好处就是Covering Index不用再到数据文件里去取id的值可以直接在索引里得到它。如果我们的数据库引擎是MyISAM那么建立category_id, created复合索引就不是最佳答案。因为MyISAM的索引结构里非主键索引并没有额外保存对应主键的值此时如果想利用上Covering Index应该建立category_id, created, id复合索引。唠完了应该明白我的意思了吧。希望以后大家在考虑索引的时候能思考的更全面一点实际应用中还有很多类似的问题比如说多数人在建立索引的时候不从Cardinality(SHOW INDEX FROM ...能看到此参数)的角度看是否合适的问题Cardinality表示唯一值的个数一般来说如果唯一值个数在总行数中所占比例小于20%的话则可以认为Cardinality太小此时索引除了拖慢insert/update/delete的速度之外不会对select产生太大作用还有一个细节是建立索引的时候未考虑字符集的影响比如说username字段如果仅仅允许英文下划线之类的符号那么就不要用gbkutf-8之类的字符集而应该使用latin1或者ascii这种简单的字符集索引文件会小很多速度自然就会快很多。这些细节问题需要读者自己多注意我就不多说了。本文地址http://www.45fan.com/a/question/41066.html
