MYSQL性能调优
- varchar类型的字段在编写SQL时，一定要加单引号，否则如果该字段是索引的话，则不起作用，在大数量查询时，性能很差
PS：大坑 - 建立必要的索引提高SQL查询性能
主键（聚集）索引、非聚集索引（普通索引，唯一索引，全文索引）
可以看出聚集索引的好处了，索引的叶子节点就是对应的数据节点（MySQL的MyISAM除外，此存储引擎的聚集索引和非聚集索引只多了个唯一约束，其他没什么区别），可以直接获取到对应的全部列的数据，而非聚集索引在索引没有覆盖到对应的列的时候需要进行二次查询，后面会详细讲。因此在查询方面，聚集索引的速度往往会更占优势。
非聚集索引叶节点仍然是索引节点，只是有一个指针指向对应的数据块，此如果使用非聚集索引查询，而查询列中包含了其他该索引没有覆盖的列，那么他还要进行第二次的查询，查询节点上对应的数据行的数据。
PS：二次查询所花费的查询开销占比很大，达到50%
要注意使用复合索引需要满足***最左侧索引的原则***，也就是查询的时候如果where条件里面没有最左边的一到多列，索引就不会起作用。
- 使用聚集索引的查询效率要比非聚集索引的效率要高，但是如果需要频繁去改变聚集索引的值，写入性能并不高，因为需要移动对应数据的物理位置。
- 非聚集索引在查询的时候可以的话就避免二次查询，这样性能会大幅提升。
- 不是所有的表都适合建立索引，只有数据量大表才适合建立索引，且建立在选择性高的列上面性能会更好。
- 临时表和文件排序（Using temporary; Using filesort问题解决）,虽然排序字段已经建立索引
总结：
- 1.mysql查询存在直接关联和非直接关联的问题，这两种查询效率差别很大；
- 2.mysql排序尽量使用索引；
- 3.mysql多表关联left join其他表的时候，如果以其他表的字段作为查询条件都会产生临时表；
- 4.mysql在非直接关联的基础上进行排序会很慢，需要进行优化；PS： 赞！！！
解决方案：1） 使用子查询，适合子查询部分不作为查询条件
2） 非直接关联转变成直接关联
- straight_join
如果出现Using temporary和Using filesort 落在非排序字段的其他表中，则说明MYSQL优化器给出的并不是最优的关联顺序，可以使用straight_join改变顺序
PS：慎用 - 对于那些查询中很少涉及的列、重复值比较多的列不要建立索引。例如，在查询中很少使用的列，有无索引并不能提高查询的速度，相反增加了系统维护时间和消耗了系统空间；又如，“性别”列只有列值“男”和“女”，增加索引并不能显著提高查询的速度。
PS: 性别字段选择性低，使用索引查找还不不如便利表的效率高，即使加了索引SQL Server也不会使用该索引查找。优化器也是会认为此索引使用的成本过高，而不会使用索引。
- 对于定义为text、image和bit数据类型的列不要建立索引。因为这些数据类型的数据列的数据量要么很大，要么很小，不利于使用索引。
- explain 和 set profiling=1开启时间记录 — show profiles;
- 频繁更新的字段不适合创建索引，因为每次更新不单单是更新记录，还会更新索引，保存索引文件；
- count函数
innodb没有保存表的总行数，如果使用select count(*) from table；就会遍历整个表，消耗相当大，但是在加了wehre 条件后，myisam和innodb处理的方式都一样。
- （行级锁）死锁举例分析
表Test：（ID,STATE,TIME） 主键索引：ID 非主键索引：STATE 当执行"UPDATE STATE =1011 WHERE STATE=1000" 语句的时候会锁定STATE索引，由于STATE 是非主键索引,所以Mysql还会去请求锁定ID索引 当另一个SQL语句与语句1几乎同时执行时：“UPDATE STATE=1010 WHERE ID=1” 对于语句2 Mysql会先锁定ID索引，由于语句2操作了STATE字段，所以Mysql还会请求锁定STATE索引。这时。彼此锁定着对方需要的索引，又都在等待对方释放锁定。所以出现了"死锁"的情况。 - myIsam
myISAM表的写操作，会阻塞其他用户对同一个表的读和写操作。
myISAM表的读、写操作之间、以及写操作之间是串行的。
- InnoDB
排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁- 共享锁（S） ： SELECT * FROM table_name WHERE … LOCK IN SHARE MODE
排他锁（X）： SELECT * FROM table_name WHERE … FOR UPDATE.
使用select … in share mode获取共享锁，主要用在需要数据依存关系时，确认某行记录是否存在，并确保没有人对这个记录进行update或者delete。
个人总结
- 使用 left join 加排序额外注意，优化器选择的查询顺序会影响到排序索引的使用
- t_alarm_info AS a force index (t_alarm_info_time) 强制索引也可以改变优化器的查询顺序，有可能比straight join 更好
- 使用COUNT（）、MAX（）等函数时，在大数量的情况下需要改变
- 如果使用use index（）之后还出现filesort，可以试试force index（），出现filesort时，查询会很慢 。use index 只是向优化器提供参考，force index 强制索引使用。两者都可以使用多个索引
- 能用 and 尽量用 and ,不要用or ,or 跟着的索引字段不起作用（有时业务传参设计也很重要，先按大索引过滤，再按小索引过滤）
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
