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.行锁升级为表锁(使用索引时字符串没加单引号,索引失效,行锁变表锁)