mysql数据库的索引及优化小技巧！

数据库优化小技巧：

　　可以通过show status 命令来了解各种sql的执行频率。

　　show [session|global]status。

　　session:当前连接的统计结果。

　　global:至数据库上次启动至今的统计结果。

　　如果没有明确写明是session级还是global级则会默认为session级。

　　show status like "Com_%"；

　　Com_xxx 表示每个xxx语句的执行次数(Com_select,Com_update,Com_insert,Com_delete.)。

　　对于select 每次查询 数值则累计加一，对于批量insert只累加一次。

　　以下参数则是单独针对InnoDB存储引擎特有的参数：

　　innodb_rows_read,innodb_rows_insert,innodb_rows_update,innodb_rows_delete.

　　可以通过以上的统计命令查看当前数据库是偏向查询型数据库还是操作型数据库为主。

　　

　　Com_commit,Com_rollback则是对提交和回滚的数据统计。

　　Connections:试图连接Mysql服务器的连接次数。

　　Uptime:服务器工作时间。

　　Slow_queries:慢查询次数。 

 

  explain的使用：

　　explain select * from xxx;

　　返回值:

　　　　select_type:表示select查询语句的类型，常见的取值有SIMPLE(简单表，即不适用表连接或子查询),PRIMARY(主查询，即外层的查询),UNION(UNION联合查询中的第二个或后面的查询语句),SUBQUERY(子查询中的第一个SELECT)。

　　　　table:输出结果集的表。

　　　　type:表示表的连接类型，性能由好到差的连接类型为system(表中仅有一行，即常量表)，const(单表中最多有一个匹配行，即primary key或者 unique index)，eq_ref(对于前面的每一行，在此表中只查询一条记录，即多表连接中使用primary key或者unique index)，ref(与eq_ref类似，区别在于不适用primary key 或者unique index 而是使用普通索引)，ref_or_null（与ref类似，区别在于条件中对null的查询），index_merge(索引合并优化)，unique_subquery（in的后面是一个查询主键字段的子查询），index_subquery（与unique_subquery类似，区别在于in的后面是查询非唯一索引字段的子查询），range（单表中的范围查询），index（对于前面的每一行，都通过查询索引来得到数据），all（对于前面的每一行，都通过全表扫描来得到数据）

　　　　possible_keys:表示查询时，可能会使用的索引。

　　　　key:表示实际使用的索引。

　　　　ken_len:索引字段的长度。

　　　　rows:扫描行的数量。

　　　　Extra:执行情况的说明和描述。

 

针对表的优化：

　　PROCEDURE ANALYSE();

　　select * from xxx procedure analyse();

　　这个函数可以输出mysql对表给出的字段修改建议(根据当前表中存在的数据给出字段的长度或者类型的调整的建议！)

数据库索引:

　　数据库索引，就是数据库表中某个字段的一个属性，将该属性标注为索引，那么针对这个表的这个字段的查询将变得快速起来。

　　当没有建立索引时，每次针对该字段的查询都需要扫描全表来对比结果，但是一旦对该字段建立索引，就无需扫描全表，而是

　　根据值去查询对应的结果区间来对比结果。

　　可以把索引对应的字段看成一个字典，字典大家都知道，就是你可以根据汉字的声母去找到对应的汉字的大致范围是在哪。然后直接

　　在范围内查到自己想找的具体汉字。索引就是通过这样的方式来避免每次查询都去扫描全表从而提高查询的效率。但是盲目的创建索引

　　也是不合理的，因为一个字段 的索引就对应着一本字典，如果频繁无节制的去创建索引，那么相应的字典数量也会充斥整个服务器内存。

　　所以创建索引要适当且合理！

 

主键与唯一索引的区别:

　　主键是一种约束，而唯一索引是一种索引，二者存在本质上的区别。

　　唯一索引允许字段为空，但是主键不允许字段为空。主键包含了唯一索引的特性，但唯一索引不一定是主键

　　主键可以是其他表的外键，但是唯一索引不行。一个表只能有一个主键，但可以允许有多个唯一索引。

 

复合索引(联合索引):

　　联合索引就是多个字段一起组成的索引，字段 a ， b， c 3个字段组成一个索引

　　但是联合索引必须要遵循一个原则:最左前缀原则！

　　最左前缀原则原理:当创建一个联合索引时，多个字段按顺序从左到右顺序排列，如 a，b，c 这样的顺序

　　然后在使用时 where a= 1 and b=1 或者 where a = 1 and c=1  或者 where a=1 and b=1 and c=1

　　都能使用索引。

　　但是如果在where子句中不包含a的条件 只是单纯的 使用 b 或 c 条件查询的话 那么就无法使用索引

　　这就是最左前缀原则，当联合索引创建时 最左边的字段为生效标识，只有当where子句中包含

　　联合索引的最左边列该索引才生效，至于该列在子句中的位置则不做要求。凡是子句中包含了该列即联合

　　索引生效，但是如果是where b=1 and a =1 这样的方式虽然索引生效，但是mysql搜索引擎会优先将顺序排序，

　　所以最好还是根据联合索引的顺序来编写where子句

 

 

聚集索引与非聚集索引:

　　　实际上，您可以把索引理解为一种特殊的目录。微软的SQL SERVER提供了两种索引:

　　　聚集索引（clustered index，也称聚类索引、簇集索引）和非聚集索引（nonclustered index，也称非聚类索引、非簇集索引）。

　　    聚集索引和非聚集索引的区别：
    　　其实，我们的汉语字典的正文本身就是一个聚集索引。比如，我们要查“安”字，就会很自然地翻开字典的前几页，因为“安”的拼音是“an”，而按照拼音排序汉字的字典是以英文字母“a”开头并以“z”结尾的，那么“安”字就自然地排在字典的前部。如果您翻完了所有以“a”开头的部分仍然找不到这个字，那么就说明您的字典中没有这个字；同样的，如果查“张”字，那您也会将您的字典翻到最后部分，因为“张”的拼音是“zhang”。也就是说，字典的正文部分本身就是一个目录，您不需要再去查其他目录来找到您需要找的内容。我们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。
    　　如果您认识某个字，您可以快速地从自动中查到这个字。但您也可能会遇到您不认识的字，不知道它的发音，这时候，您就不能按照刚才的方法找到您要查的字，而需要去根据“偏旁部首”查到您要找的字，然后根据这个字后的页码直接翻到某页来找到您要找的字。但您结合“部首目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法，比如您查“张”字，我们可以看到在查部首之后的检字表中“张”的页码是672页，检字表中“张”的上面是“驰”字，但页码却是63页，“张”的下面是“弩”字，页面是390页。很显然，这些字并不是真正的分别位于“张”字的上下方，现在您看到的连续的“驰、张、弩”三字实际上就是他们在非聚集索引中的排序，是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到您所需要的字，但它需要两个过程，先找到目录中的结果，然后再翻到您所需要的页码。我们把这种目录纯粹是目录，正文纯粹是正文的排序方式称为“非聚集索引”。

以上说明例子 摘自 https://www.cnblogs.com/aspnethot/articles/1504082.html

 

关于数据库的优化方面的小技巧:

　　对于一些频繁的查询条件(where 或者 order by) 涉及的字段应当给与创建索引，避免每次查询都全表扫描。

　　当表中索引列所在的字段的数据充斥这大量的重复数据时，索引也是不会起作用的。

　　索引虽然增加了查询的效率，但是却降低了insert 和 update 的效率，因为每次insert update 都可能会去重建索引(建议一个表的索引不要超过5个)

　　还有尽量避免update 索引所在的字段，因为该字段的数据一旦被修改那么意味着索引所在的物理存储顺序也要跟着发生改变才可以。

　　尽量选择数字型字段，因为字符型字段在查找时是将字符串的每个字符做对比，但是数字的话是整个数字做比较，比较次数上就差很多

　　尽量用varchar代替char 因为varchar 是可变长字段，char是定长字段。不但可以节省空间，而且查询时内存较小的字段也会查询较快

　　

　　任何查询尽量避免使用* 而是使用具体的字段来代替

　　利用好exists 来代替in 是个很好的选择如select num from a where num in(select num from b) ==》select num from a where exists(select 1 from b where num=a.num)

　　避免使用where xxx != 或者 xxx <>这样的语句，因为这样的语句会导致索引失效

　　避免在sql语句中使用 where xx is null 这样的语句，搜索引擎针对这样的判断不会去使用索引，而是进行全表扫描。

　　避免使用or，in，not in 来连接条件，应该使用 union all ， between x and x来连接条件。不然索引也会失效 导致全表扫描

　　sql中的like 也要谨慎使用  SELECT * FROM user  WHERE uname  LIKE "后盾%" -- 走索引 
　　　　　　　　　　　　　 SELECT * FROM user  WHERE uname  LIKE "%后盾%" -- 不走索引

　　避免在where子句中使用函数 表达式 算数运算，不然也可能无法正常使用索引

　　order by 子句混合ASC和DESC则没法对order by子句中的字段使用索引