MySql之索引

  分类:默认   评论:无  浏览: 318次

mysql

什么是索引?

  MySql官方定义为:索引(Index)是帮助MySql高效获取数据的数据结构。所以说,我们可以得到‘索引’的本质:索引是数据结构。
  更通俗的讲,索引的目的是在于提高查询效率,可以类比字典:假如要查“mysql”这个单词,我们肯定定位到“m”字母,然后接着找“y”字母以及“s”“q”“l”。如果没有索引,那么我们只能从字母“a”开始找了。

手写与机读

  程序员自己写的sql顺序与MySql执行的sql顺序是有些不一样的;

手写:
SELECT DISTINCT
	<select_list>
FROM
	<left_table>
    <join_type> JOIN <right_table> on <join_condition>
WHERE
	<where_condition>
GROUP BY
	<group_by_list>
HAVING
	<having_condition>
ORDER BY
	<order_by_condition>
LIMIT
	<limit_number>
机读:
FROM <left_table>
ON <join_condition>
<join_type> JOIN <right_table>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
SELECT
DISTINCT <select_list>
ORDER BY <order_by_condition>
LIMIT <limit_number>

所以总结下,可以用下图表示: mysql

索引的创建

  MySql单值索引复合索引,可分别由下列语句创建:

单值索引:
create index idx_<table>_<column> on <table>(<column>);
alter <table> add index idx_<table>_<column> on <table>(<column>);

复合索引:
create index idx_<table>_<column1><column2> on <table>(<column1,column2>);
alter <table> add index idx_<table>_<column1><column2> on <table>(<column1,column2>);

删除:

drop index <index_name> on <table>;

查看:

show index from <table>;
或者
show index from <table>\G

索引创建与否的条件

索引优化分析

  MySql性能分析及explain的使用。 explain的相关属性,如下图: explain

  以上最重要的几个指标是:idtypekeyrowsExtra,其中type指标中,结果值从最好到最坏依次是:system>const>eq_ref>ref>range>index>all,一般来说,得保证查询至少达到range级别,最好能达到ref级别;其中Extra指标中,出现Using index很不错,但是出现Using filesort或者Using temporary那你就得小心了。

索引失效原因及建议

索引失效原因:

  1. 全值匹配不当;
  2. 没有遵循最佳左前缀法则;
  3. 在索引列上进行各种操作(如:计算、函数、自动或手动类型转换);
  4. 存储引擎不能使用索引中范围条件右边的列(范围之后的索引会失效);
  5. 尽量使用覆盖索引(只访问索引的查询(索引列与查询列一致)),减少select *;
  6. mysql在使用不等于(!=或者<>)的时候,不能使用索引;
  7. is null,is not null 无法使用索引;
  8. like以通配符%开头,索引失效;
  9. 字符串不加单引号,索引失效;
  10. 少用or,用or做连接的时候,索引失效;

优化建议:   在使用join的时候,永远使用小结果集驱动大结果集,减少NestedLoop的循环次数,保证join语句中被驱动表上的join条件字段已经被索引;

下面口诀不错:

【优化总结口诀】

全值匹配我最爱,最左前缀要遵守;
带头大哥不能死,中间兄弟不能断;
索引列上少计算,范围之后全失效;
like百分写最右,覆盖索引不写星;
不等空值还有or,索引失效要少用;
VAR引号不可丢,SQL高级也不难!
点击这里给我发消息
嘿!有什么可以帮助你吗?