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的单值索引与复合索引,可分别由下列语句创建:

单值索引: 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索引创建与否的条件主键自动建立唯一索引;

频繁作为查询条件的字段应该创建索引;

查询中与其他表关联的字段,外键关系建立索引;

where条件里用不到的字段不创建索引;

在高并发下倾向创建复合索引;

查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度;

查询中统计或者分组字段;

表中数据较少的情况下,不适合创建索引;

经常有增删改操作的表,不适合创建索引;

频繁更新的字段,不适合创建索引;

数据重复且分布平均的表字段,不适合创建索引;

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

id:select查询的序列号,表示查询中执行select子句或操作表的顺序。

id相同:执行顺序自上而下;

id不同:如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行。

select_type:查询语句的类型。

UNION RESULT

table:查询的表。

type:访问类型,显示查询使用了何种类型,常见如下:

system:表只有一行记录(等价于系统表),这是const类型的特例,平时不会出现,可忽略;

const:表示通过索引一次就找到了,const用于比较primary或者unique索引,因为只匹配一行记录;

ref:非唯一性索引扫描,返回匹配某个单独值的所有行;

range:只检索给定范围的行,一般就是where语句中使用了“<”、“>”、“between”、“in”等;

index:与all的区别就是只遍历索引树,通常比all快;

all:全表扫描;

possible_keys:mysql认为可能用到的索引;

key:实际用到的索引;

key_len:长度;

ref:引用;

rows:扫描的行;

Extra:常用Using filesort、Using temporary、Using index、Using where

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

索引失效原因:

全值匹配不当;

没有遵循最佳左前缀法则;

在索引列上进行各种操作(如:计算、函数、自动或手动类型转换);

存储引擎不能使用索引中范围条件右边的列(范围之后的索引会失效);

尽量使用覆盖索引(只访问索引的查询(索引列与查询列一致)),减少select *;

mysql在使用不等于(!=或者<>)的时候,不能使用索引;

is null,is not null 无法使用索引;

like以通配符%开头,索引失效;

字符串不加单引号,索引失效;

少用or,用or做连接的时候,索引失效;

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

下面口诀不错:

【优化总结口诀】 全值匹配我最爱,最左前缀要遵守; 带头大哥不能死(全部用不到),中间兄弟不能断(用到部分); 索引列上少计算,范围之后全失效(之后的失效,该范围的索引不失效); like百分写最右,覆盖索引不写星; 不等空值还有or,索引失效要少用; VAR引号不可丢,SQL高级也不难!长按下面二维码,关注公众号: