MYSQL 索引优化

SQL优化

1. 被驱动表（大表）会用到索引

2. left join在优化关联查询，只有被驱动表建立索引才有效,LEFT JOIN 小表驱动大表 左表驱动右表，因此在右表构建索引生效，而在左表构建则不生效，即使左表用到了索引扫描的行数依旧未变

3. inner join 自动选择小结果集作为驱动表，如果两表都构建索引，且数据量一样，默认使用右表索引，如果削减右表数据量使左表作为大表此时 索引使用 左表，依旧使用大表上构建的索引

   # 测试SQL
   select id,status_code from event_plan ORDER BY status_code desc
   
   select id,status_code from event_plan ORDER BY field(status_code,2,1,3) 
   
    alter  table event_plan add key idx_field_status_code((field(status_code,2,1,3)));  
    
    
    CREATE TABLE IF NOT EXISTS `class` (
   `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `card` INT(10) UNSIGNED NOT NULL, PRIMARY KEY (`id`)
   );
   
   CREATE TABLE IF NOT EXISTS `book` (
   `bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `card` INT(10) UNSIGNED NOT NULL, PRIMARY KEY (`bookid`)
   );
    
   INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
   INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
   INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
   INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
   INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
   INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
   INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
   INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
   INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
   INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
   INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
   INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
   INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
   INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
   INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
   INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
   INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
   INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
   INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
   INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
   INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
   INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
   INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
   INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
   INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
   INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
   INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
   INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
   INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
   INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
   INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
   INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
   INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
   INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
   INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
   INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
   INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
   INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
   INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
   INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
   
   select * from book inner join class on class.card = book.card
   delete from class where id <10
   
   -- alter table class add INDEX idx_card(card)
   -- alter table book add INDEX idx_card(card)
   
   select * from book left join class on class.card = book.card
   select * from class left join book on class.card = book.card
   
   

4. id相同，从上至下 id不同 越大越优先 69

5. 子查询尽量不要放在被驱动表，有可能使用不到索引，left join尽量用实体表作为被驱动表

6. 多表关联优于子查询 where 条件和 on 的判断这些过滤条件，作为优先优化的部门，是要被先考虑的！其次，如果有分组和排序，那么 也要考虑 grouo by 和 order by。

7. 无过滤，不索引。where，limt 都相当于一种过滤条件，所以才能使用上索引 顺序错，必排序，order by后不能更换顺序，方向反必排序，如果order by的条件是两个相反方向则触发filesort，构建索引遇到范围查询索引无效，但可以跳过范围搜索构建索引