常见的索引失效情况及SQL优化

1.使用组合索引时使用全值索引

2.使用组合索引时执行最左前缀法则

3.使用组合索引时范围查询后的查询索引失效

4.不要在索引列上做运算操作，否则索引将失效

5.字符串不加单引号，造成索引失效

6.尽量使用覆盖索引，避免select *

7.用or分割开的条件，如果or前的条件中的列有索引，而后边的列没有索引，那么涉及的索引都不会被用到

8.以%开头的模糊查询索引将失效，可用覆盖索引解决此问题

9.如果mysql评估使用索引比全表更慢，则不适用索引

10.is null ，is not null   有时会导致索引失效

11.in走索引，not in 索引失效

12.尽量使用复合索引代替单列索引；同时使用多个单列索引时，会使用最优单列索引

 

二、SQL优化

1.批量插入数据（1.建议用有序数据，主键顺序插入  2.关闭唯一性校验   3.手动提交事物）

load data local infile '/root/sql1.log' into table 'tb_user_1' fields terminated by ',' lines terminated by '\n';

2.插入数据时的优化（1.添加可以用一条SQL执行  2.事物手动提交  3.按主键顺序添加）

3．order by 优化（使用覆盖索引，升序降序尽量统一）

4.group by  优化（1. 分组后加 order by null  2.  创建索引）

5.多表连接查询代替子查询

6.用or关键字时前后的字段必须都是单列索引字段（用union代替or）

7.limit 优化（1.根据主键关联回原表查询  2.主键自增且不能出现断层才可以用  id>2000000 limit 10）

 

8.使用SQL提示

1.use index 在表名后使用use index （name）

2.ignore index 忽略某个所用，用法同上

3.force index 强制使用某一索引

 

二、mysql锁问题

myisam表锁（适合读表居多的存储引擎）

加读锁：lock table table_name read;  unlock tables

加写锁：lock table table_name write;

1.加了读锁以后当前线程只能读锁定的表的数据，不能做其他操作； 其他链接读取数据不受影响，其他操作页不能做。

2.加了写锁后当前线程是可以读取的，可更新可插入；其他线程处于等待状态

总结：读锁会阻塞写，但不会阻塞读。写锁既会阻塞读又会阻塞写

3.show open tables 查看锁的征用情况

4.show status like 'table_locks%'

 

三、InnoDB的行锁模式

  2.行锁升级为表锁（使用索引时字符串没加单引号，索引失效，行锁变表锁）