Mysql面经：SQL索引优化——05（建议收藏）

一，准备工作：

导入mysql为我们准备的Sql表：
Mysql测试数据下载

下载压缩包之后，解压，运行其中的sql语句：

进入sakila数据库之后：

use sakila

查看数据库中的表

show tables

二，索引优化细节：

1，当使用索引列进行查询的时候尽量不要使用表达式，把计算放到业务层而不是数据库层

select actor_id from actor where actor_id=4;

（1）引用主键索引（正确使用）：

	explain	select actor_id from actor where actor_id=4;

（2）错误演示：

	explain select actor_id from actor where actor_id+1=5;

使用了组合索引。
但是我们查看actor的索引的表时候发现并没有上面的索引

 	show index from actor

（3）注意，计算不是表达式：

 explain select actor_id from actor where actor_id=3+1

（4）结论：

在使用索引查询的不要使用表达式，
将计算业务放在业务层而不是数据库层。

2，尽量使用主键查询，而不是其他索引，因为主键查询不会触发回表查询

（1）自然主键和代理组件，为什么建表时要创建一个id？

自然主键：
就是充当主键的字段本身具有一定的含义，是构成记录的组成部分，比如学生的学号，除了充当主键之外，同时也是学生记录的重要组成部分。

代理主键：
就是充当主键的字段本身不具有业务意义，只具有主键作用，比如自动增长的ID。

为什么要在建表的时候创建一个id？
为了使用代理主键，创建一个业务无光的id，方便进行索引查询。

3，使用前缀索引

有时候需要索引很长的字符串，这会让索引变的大且慢，通常情况下可以使用某个列开始的部分字符串，这样大大的节约索引空间，从而提高索引效率，但这会降低索引的选择性，索引的选择性是指不重复的索引值和数据表记录总数的比值，范围从1/#T到1之间。索引的选择性越高则查询效率越高，因为选择性更高的索引可以让mysql在查找的时候过滤掉更多的行。

一般情况下某个列前缀的选择性也是足够高的，足以满足查询的性能，但是对应BLOB,TEXT,VARCHAR类型的列，必须要使用前缀索引，因为mysql不允许索引这些列的完整长度，使用该方法的诀窍在于要选择足够长的前缀以保证较高的选择性，通过又不能太长。
案例演示：

--创建数据表
create table citydemo(city varchar(50) not null);
insert into citydemo(city) select city from city;

--重复执行5次下面的sql语句
insert into citydemo(city) select city from citydemo;

--更新城市表的名称
update citydemo set city=(select city from city order by rand() limit 1);

--查找最常见的城市列表，发现每个值都出现45-65次，
select count(*) as cnt,city from citydemo group by city order by cnt desc limit 10;

--查找最频繁出现的城市前缀，先从3个前缀字母开始，发现比原来出现的次数更多，可以分别截取多个字符查看城市出现的次数
select count(*) as cnt,left(city,3) as pref from citydemo group by pref order by cnt desc limit 10;
select count(*) as cnt,left(city,7) as pref from citydemo group by pref order by cnt desc limit 10;
--此时前缀的选择性接近于完整列的选择性

--还可以通过另外一种方式来计算完整列的选择性，可以看到当前缀长度到达7之后，再增加前缀长度，选择性提升的幅度已经很小了
select count(distinct left(city,3))/count(*) as sel3,
count(distinct left(city,4))/count(*) as sel4,
count(distinct left(city,5))/count(*) as sel5,
count(distinct left(city,6))/count(*) as sel6,
count(distinct left(city,7))/count(*) as sel7,
count(distinct left(city,8))/count(*) as sel8 
from citydemo;

--计算完成之后可以创建前缀索引
alter table citydemo add key(city(7));

--注意：前缀索引是一种能使索引更小更快的有效方法，但是也包含缺点：mysql无法使用前缀索引做order by 和 group by。 

4，使用索引扫描来排序。

	show index from rental;

使用索引进行排序：

explain select rental_id,staff_id from rental where rental_date='2005-05-25' order by inventory_id,customer_id\G

使用文件排序：

explain select rental_id,staff_id from rental where rental_date='2005-05-25' order by inventory_id desc\G

5，union all,in,or都能够使用索引，但是推荐使用in

6，范围列可以用到索引

（1）范围条件是：

<、<=、>、>=、between

（2）注意：

范围列可以用到索引，但是范围列后面的列无法用到索引，索引最多用于一个范围列

7，强制类型转换会全表扫描

（1）触发索引

explain select * from user where phone='13800001234';

（2）不会触发索引

explain select * from user where phone=13800001234;

这里是隐势转换。

8，更新十分频繁，数据区分度不高的字段上不宜建立索引

比如说，男女这种区分度不高的就不要用了

（1）更新会变更B+树，更新频繁的字段建议索引会大大降低数据库性能

（2）类似于性别这类区分不大的属性，建立索引是没有意义的，不能有效的过滤数据，

（3）一般区分度在80%以上的时候就可以建立索引，区分度可以使用 count(distinct(列名))/count(*) 来计算

9，当需要进行表连接的时候，最好不要超过三张表，因为需要join的字段，（连接的字段）数据类型必须一致（阿里规约）

（1）问题一：

使用join的时候是否的使用索引？
看情况，如果你的数据类型不一致，那么就会造成索引失效

10，能使用limit的时候尽量使用limit

我们经常使用limit进行分页，但是实际上，limit真正的含义是，限制输出

11，单表索引建议控制在5个以内（高性能mysql）

但是现在的对于这条的限制没有太大。

12，单索引字段数不允许超过5个（组合索引）

13，创建索引的时候应该避免以下错误概念

（1）索引越多越好

（2）过早优化，在不了解系统的情况下进行优化

路过大佬给个赞呗：