MySQL02-索引与优化

sakila数据库说明

本文中所有的数据库是由MySql官网提供的sakila示例数据库

ZIP格式：http://downloads.mysql.com/docs/sakila-db.zip

tar格式 http://downloads.mysql.com/docs/sakila-db.tar.gz

官方文档 http://dev.mysql.com/doc/sakila/en/index.html

解压后得到三个文件：

1、sakila-schema.sql 文件包含创建Sakila数据库的结构：表、视图、存储过程和触发器

2、sakila-data.sql文件包含：使用 INSERT语句填充数据及在初始数据加载后，必须创建的触发器的定义

3、sakila.mwb文件是一个MySQL Workbench数据模型，可以在MySQL的工作台打开查看数据库结构

--登录mysql
mysql -uroot -p123456
--导入表的结构数据
source 路径/sakila-schema.sql
--导入表的数据
source 路径/sakila-data.sql

MySql执行计划

在企业的应用场景中，为了知道优化SQL语句的执行，需要查看SQL语句的具体执行过程，以加快SQL语句的执行效率。

可以使用explain+SQL语句来模拟优化器执行SQL查询语句，从而知道mysql是如何处理sql语句的。

官网地址： https://dev.mysql.com/doc/refman/5.5/en/explain-output.html

执行计划中的type

type显示的是访问类型，访问类型表示我是以何种方式去访问我们的数据，最容易想的是全表扫描，直接暴力的遍历一张表去寻找需要的数据，效率非常低下，访问的类型有很多，效率从最好到最坏依次是：

mysql> explain select * from actor;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | actor | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  200 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

MySql通过索引优化

索引匹配方式

1、全值匹配

全值匹配指的是和索引中的所有列进行匹配

explain select * from staffs where name = 'July' and age = '23' and pos = 'dev';

2、匹配最左前缀

只匹配前面几列

explain select * from staffs where name = 'July' and age = '23';
explain select * from staffs where name = 'July';

3、匹配列前缀

可以匹配某一列的值的开头部分

explain select * from staffs where name like 'J%';
explain select * from staffs where name like '%J';--无法用索引

4、匹配范围值

可以查找某一个范围的数据

explain select * from staffs where name > 'Mary';

5、精确匹配某一列并范围匹配另外一列

可以查询第一列的全部和第二列的部分

explain select * from staffs where name = 'July' and age > 25;

6、只访问索引的查询

查询的时候只需要访问索引，不需要访问数据行，本质上就是覆盖索引

explain select name,age,pos from staffs where name = 'July' and age = 25 and pos = 'dev';

组合索引

建立索引a,b,c
不同SQLyuju使用索引情况

语句	索引是否发挥作用
where a=3	是，只是用了a
where a=3 and b=5	是，使用了a,b
where a=3 and b=5 and c=4	是，使用了a,b,c
where b=3 or where c=4	否
where a=3 and c=4	是，仅使用了a
where a=3 and b>10 and c=7	是，使用了a,b
where a=3 and b like '%xx%' and c=7	使用了a,b

索引优化注意点

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

select actor_id from actor where actor_id=4;
select actor_id from actor where actor_id+1=5;--无法用索引

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

3、使用前缀索引

4、使用索引扫描来排序

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

6、范围列可以用到索引

范围条件是：<、<=、>、>=、between
范围列可以用到索引，但是范围列后面的列无法用到索引，索引最多用于一个范围列

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

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

更新会变更B+树，更新频繁的字段建议索引会大大降低数据库性能
类似于性别这类区分不大的属性，建立索引是没有意义的，不能有效的过滤数据
一般区分度在80%以上的时候就可以建立索引，区分度可以使用 count(distinct(列名))/count(*) 来计算

9、能使用limit的时候尽量使用limit

10、单表索引建议控制在5个以内

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