SQL优化（SQL + 索引）

 查看表定义：

show create table users;

查看表的索引：

show index from users;

你要获取第一个表的所有信息，你说全表扫描快呢还是索引扫描快呢？所以当你查找库（包括left join中的临时库）的所有信息时，数据库会选择最优方法——全表扫描！！！

/*s表dept_id、name，及d表的id_o均加了索引!!!*/
/*(只使用了d表的索引)导致只有s表内容全的，d表只现示s.name='zs'对应的值 */
EXPLAIN
select s.name,d.name from student s left join dept d on s.dept_id = d.id_o and s.name='zs'
/*(使用了双表的索引)，很值得记住的写法left join on where!!!*/
EXPLAIN
select s.name,d.name from student s left join dept d on s.dept_id = d.id_o where s.name='zs'

一、对查询进行优化，应尽量避免全表扫描

1. 首先应考虑在 where 及 order by 涉及的列上建立索引，使用选择率高的字段建索引。

联合索引group by 要在where 之前。

2. 不使用 != 或 <> 操作符。

3. 不使用 is null 或 is not null

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

select id from t where num=0

4. 不使用 or

可以这样查询：

select id from t where num=10
union all
select id from t where num=20

5. 如果like是以‘%’开始，将不会使用索引

6. 不使用 in 和 not in

对于连续的数值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

7. 不在where进行函数运算

8. 使用复合索引的第一部分（即最左前缀原则）

9. 建议 exists 代替 in （注意两边表的数量多少）

EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False

EXISTS(包括 NOT EXISTS )子句的返回值是一个BOOL值。 EXISTS内部有一个子查询语句(SELECT ... FROM...)， 我将其称为EXIST的内查询语句。

其内查询语句返回一个结果集。 EXISTS子句根据其内查询语句的结果集空或者非空，返回一个布尔值。

一种通俗的可以理解为：将外查询表的每一行，代入内查询作为检验，如果内查询返回的结果取非空值，则EXISTS子句返回TRUE，这一行行可作为外查询的结果行，否则不能作为结果。

10. 索引并不是越多越好，可以提高select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引（B+Tree的insert导致树结构变化）

二、对索引进行优化

索引基数

索引基数是数据列所包含的不同值的数量。索引的基数相对于数据表行数较高的时候，它的工作效果最好。

若查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。惯用的百分比界线是”30%”

索引选择性

索引选择性=索引基数/数据总数

越接近1就越有可能利用索引，也可以理解1为百分百

高索引选择性的好处就是mysql查找匹配的时候可以过滤更多的行，唯一索引的选择性最佳，值为1。