mysql索引整理

一.索引：

1.创建一般索引 create index index_name on table_name(columnname[...]); 

2.分析sql执行语句 在select 之前加explain eg:explain select * from table_name;

3.mysql从5.5.5之后默认采用innerdb为默认的存储引擎,该引擎支持的两种常见索引：b+树索引和hash索引，hash索引的效率高于b+树，但hash索引只支持=类型。

b+树可以支持范围的eg:>< ，b+树一般三层即可表示，即访问3次io可找到数值。

 

二.常见丢失索引的情况

1.左前缀法则：建立的联合索引，如果查询条件没有第一个带索引的列，则会丢失索引效果。

2.在索引的列表上有函数eg:max() left() avg()，则会丢失索引

3.使用<>或!=会无法使用索引 遇到不等于的情况，建议改用范围查询大于 or 小于

4.建议少用or

5.is null && is not null 会引起全表扫描

6.varchar类型字段加'' 否则会出现2的场景，因为mysql会隐式转化，先将数字转成varchar

数据库中定义为日期类型的字段，查询时程序中用str_to_date转成日期在查询，否则mysql也会进行隐式转化

7.like '%_%' && like '%_' 会引起全表扫描，将%放在右边eg: like '_%'能引用到索引，索引类型type=range

实际情况下如果左右两边都要加百分号，我们可以用覆盖索引来解决，eg: select name from table where name like '%name%'; create index index_name on table(name,age);

要求所查询的字段必须被索引所覆盖到，在explain的时候，输出的extra信息中如果有“Using Index”，就表示这条查询使用了覆盖索引,这就是覆盖索引。

在mybatis持久层框架中我们可以用concat拼接的方式处理。

8.减少select * 的使用

 

三

1.小表驱动大表

select * from A where id in (select id from B); 当B表的数据集小于A表的数据集时，用in比exists效率高

2.order by:默认为升序，index高于filesort，遵照索引建的最佳左前缀原则

mysql4.1之后改进为单路排序，一次查询所需的列，如果buffer不足够大，会导致重复访问io，可适当调整sort_buffer_size max_length_for_sort_date 参数值

3.group by: 同order by，且where 高于 having，限制条件尽量卸载where中

4.开启慢查询日志：set global show_query_log=1;(只对当前数据库有效，重启后失效)

设置慢查询的时间值3秒：set global long_query_time=3

使用mysql提供的mysqlddumpshow查询慢日志的所需的信息 eg:得到访问次数最多的10个SQL：mysqldumpslow -s c -t 10 /var/lib/mysql/sunys.log

5.使用show profile 查看sql执行的生命周期，进行sql分析：

 关注status的四种状态：1)converting HEAP to MyISAM 查询结果太大,内存不够用往磁盘上写2)Creating tmp table 创建临时表（拷贝数据到临时表再删除耗性能）3)copying to tmp table on disk 把内存中的临时表复制到磁盘4)locked  

四：mysql锁

1.无索引行锁会升级为表锁：

update/delete sql尽量带上where条件并在where条件中设定索引过滤条件，否则会锁表，性能低效。