mysql多表优化 索引怎么创建

Mysql 优化

1. 两个表

表1:
CREATE TABLE IF NOT EXISTS lwl_teacher(
id int(11),
username VARCHAR(20),
age TINYINT(4),
course_id int(11)# 添加索引（外键）
)ENGINE=INNODB CHARSET=UTF8;

表2:
CREATE TABLE IF NOT EXISTS lwl_course(
id int(11),
name VARCHAR(20),
)ENGINE=INNODB CHARSET=UTF8;

---

小表驱动大表：哪个表为主，就以哪个表为主，外键添加索引，

---

例1:

左边链接：

`EXPLAIN SELECT * FROM lwl_teacher t LEFT JOIN lwl_course c on t.course_id = id`

这里由于 t表是全表查询，所以type = all;

*************************************************************************

EXPLAIN SELECT t.* FROM lwl_teacher t LEFT JOIN lwl_course c ON t.course_id = c.id where t.username = 'lwl'

这里由于t表加了 where 条件 username 加了索引，所以 type 级别更加高

---

1. 多个表优化(两个以上)
   i 例如:三个表 A,B,C
   a.小表驱动大表(那个表为主，就是小表的含义)；
   b.索引建立在经常查询的字段上；

2. 索引失效的一些原则
   i 复合索引
   a.复合索引无序使用，最佳做前缀
   b.尽量使用全索引匹配
   c.不要用 in ,not in, !=,<>,is null,is not null
   d.不要再索引上进行操作，(计算，函数，类型转换)，否则会索引失效
   e.最左侧索引使用 >< 号，整个复合索引都失效
   ii 单独索引
   a.不要用 in ,not in, !=,<>,is null,is not null
   b.不要再索引上进行操作，(计算，函数，类型转换)，否则会索引失效

3. 补救索引失效
   i 覆盖索引(using index)：如果一个索引包含(或覆盖)所有需要查询的字段的值，称为‘覆盖索引’,即只需扫描索引而无须回表。
   例如:索引 a,b,c, select a,b,c from xxx where a=’’ and b=’’ and c=’’ 覆盖索引
   ii like 时 前面不能加 %
   例如：select * from xxx where name like ‘lwl’ --索引存在
   例如：select * from xxx where name like ‘%lwl’ --索引失效
   例如：select * from xxx where name like ‘lwl%’ --索引存在

   补救：但是索引覆盖能解决 一部分 模糊查询造成的索引失效
   例如：name 是 索引 select name from xxx where name like '%lwl%'---type 由 all 变成了 index；

4. 类型的转换
   mobile varchar(20)
   select * from mobile = 110 —索引失效，改变了类型
   select * from mobile = ’110‘ —索引存在

5. Or
   or 对索引伤害很大，很大的概率可以将 or 左右两边的索引都失效

EXPLAIN SELECT * FROM lwl_users WHERE mobile = '18088779852' and tel = '18088779852' 没有类型转换，and 时候这两个索引 都没失效

EXPLAIN SELECT * FROM lwl_users WHERE mobile = '18088779852' or tel = '18088779852' 没有类型转换，or 时候这两个索引 都失效

exist 和 in
exist：select …from table where exist (子查询)
in ：select …from table where 字段 in (子查询)
***如果主查询的数据量大，用in 相对效率略高
***如果是子查询的数据量大，用exist 相对效率略高

----获取返回记录最多的三个sql
mysqldumpslow -s r -t 3 /var/lib/mysql/852c299a8a42-slow.log

----获取访问次数最多的三个sql
mysqldumpslow -s c -t 3 /var/lib/mysql/852c299a8a42-slow.log

----按照时间排序，前十条包含 left join 查询语句的sql
mysqldumpslow -s -t 10 -g /var/lib/mysql/852c299a8a42-slow.log

6. 分析海量数据：profiles
   show profiles:–默认关闭
   show variables like ‘%profil%’;
   set profiling=1;
   show profiles;
   会记录所有打开之后的sql,全部sql的语句话费的时间；只能看到总共消费的时间，不能看到各个硬件消费的时间；
   
   –精确分析sql 诊断
   show profile all for query 上一步查询的query的id
   show profile all for query 496;
   

7. 全局查询日志：记录开启之后全部sql 语句
   show variables like ‘%general_log%’;查看状态
   set global general_log = 1; 设置开启
   set global log_output = ‘table’;设置记录到表里
   select * from mysql.general_log; 查询日志

   set global log_output = 'file';设置记录到文件里
   set global general_log = 1; 设置开启
   set global general_log_file = '/tmp/general.log'设置日志路径
   cat /tmp/general.log 查看日志
   

---