5.1 索引基础
explain
id：在一个大的查询语句中每个SELECT关键字都对应一个唯一的id
select_type：SELECT关键字对应的那个查询的类型
table：表名
partition：匹配的分区信息
type：针对单表的访问方式
possible＿keys：可能用到的索引
key:实际上使用的索引
key_len：实际使用到的索引长度
ref:当使用索引列等值查询时，与索引列进行等值匹配的对象信息
filtered：某个表经过搜索条件过滤后剩余记录条数的百分比
Extra：一些额外的信息
使用索引的优点
索引的代价
索引的类型
●唯一索引。对于单列索引，不允许有重复值出现；对于多列（复合）索引，不允许出现重复的组合值。
●常规（非唯一性）索引。获得索引的好处，但会出现重复值。
●fulltext索引。用于全文检索，本仅限于MyISAM表，但MySQL5.6.4之后，InnoDB也支持。
●spatial索引。只适用于包含空间值得MyISAM表
●hash索引。memory表的默认索引类型
#索引的创建alter table tbl_name add unique index_name (index_columns);alter table tbl_name add index index_name (index_columns);alter table tbl_name add primary key index_name (index_columns);alter table tbl_name add fulltext index_name (index_columns);alter table tbl_name add spatial index_name (index_columns);#除了primary key,大部分索引都可以用create index来创建create index index_name on tbl_name (index_columns);create unqiue index_name on tbl_name (index_columns);create fulltext index_name on tbl_name (index_columns);create spatial index_name on tbl_name (index_columns);create table tbl_name{...列定义...index index_name (index_columns),unqiue index_name (index_columns),primary key index_name (index_columns),fulltext index_name (index_columns),spatial index_name (index_columns),}#索引的删除drop index index_name on tal_namealter table tbl_name drop index index_name
1.索引对多个值进行排序的依据是CREATE TABLE语句中定义索引时序的顺序。
2.使用B-Tree索引的限制
①如果不是按照索引的最左列开始查找，则无法使用索引
5.2 高性能的索引策略
独立的列
1.索引不能是表达式的一部分，也不能是函数的参数。
2.索引放在比较符号的一侧
前缀索引
CREATE TABLE sakila.city_demo(city VARCHAR(50) NOT NULL);
INSERT INTO sakila.city_demo(city) SELECT city FROM sakila.city;
-- Repeat the next statement five times:
INSERT INTO sakila.city_demo(city) SELECT city FROM sakila.city demo;
-- Now randomize the distribution (inefficiently but convenientiy):
UPDATE sakila.city_demo
SET city = (SELECT city FROM sakila.city ORDER BY RAND() LIMIT 1);
#完整性列表
select count(*) as cnt,city from sakila.city_demo group by city order by cnt desc limit 10;
select count(*) as cnt,left(city,3) as pref from sakila.city_demo group by pref order by cnt desc limit 10;
#接近完整列
select count(*) as cnt,left(city,7) as pref from sakila.city_demo group by pref order by cnt desc limit 10;
#计算完整列的选择性
select count(distinct city) /count(*) from sakila.city_demo;
#使前缀的选择性接近于完整列的选择性
select count(*) as cnt,left(city,7) as pref from sakila.city_demo group by pref order by cnt desc limit 10;
select count(distinct city) /count(*) from sakila.city_demo;
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 from sakila.city_demo;
#增加索引
alter table sakila.city_demo add key (city(7));
联合索引
create table people (
last_name varchar(50) not null ,
first_name varchar(50) not null ,
dob date not null ,
gender enum('m','f') not null ,
key(last_name,first_name,dob)
);
#可以使用索引的情况
#全职匹配
explain select * from people where last_name='li' and first_name = 'si' and dob = '2010-05-20';
#匹配最左前缀
explain select * from people where last_name = 'zhao';
#匹配列前缀
explain select * from people where last_name like 'l%';
#匹配列后缀 无法使用索引
explain select * from people where last_name like '%l';
#匹配范围值
explain select * from people where last_name > 'li' and last_name < 'wang';
#精确匹配某一列并范围匹配另外一列
explain select * from people where last_name = 'li' and last_name > 'er';
#只访问索引的查询
explain select last_name, first_name, dob from people;
####无法使用索引的情况
#不是按照索引的最左列开始查找，出生日期同理
explain select * from people where first_name = 'si';
#索引列在函数中 无法使用
explain select * from people where left(last_name,1) = 'l';
查看SQL执行时间
###默认是关闭状态
mysql> show variables like "profil%";
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| profiling | OFF |
| profiling_history_size | 15 |
+------------------------+-------+
2 rows in set, 1 warning (0.00 sec)
mysql> set profiling = on;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show variables like "profil%";
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| profiling | ON |
| profiling_history_size | 15 |
+------------------------+-------+
2 rows in set, 1 warning (0.00 sec)
#-----------
执行SQL语句
#-----------
mysql> show profiles;
+----------+------------+-----------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-----------------------------------------------------------------------------------------------------------------------------------+
| 1 | 0.00138000 | show variables like "profil%" |
| 2 | 0.00082625 | select * from sakila.film where film_id in(select film_id from sakila.film_actor where actor_id=1) |
| 3 | 0.00850675 | select * from sakila.film where exists( select * from sakila.film_actor where actor_id=1
and film_actor.film_id=film.film_id) |
| 4 | 0.00077300 | select film.* from sakila.film inner join sakila.film_actor using (film_id) where actor_id = 1 |
+----------+------------+-----------------------------------------------------------------------------------------------------------------------------------+
4 rows in set, 1 warning (0.00 sec)
mysql> show profile for query 2; //查看第二个SQL的执行情况
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000083 |
| checking permissions | 0.000008 |
| checking permissions | 0.000004 |
| Opening tables | 0.000019 |
| init | 0.000125 |
| System lock | 0.000015 |
| optimizing | 0.000014 |
| statistics | 0.000076 |
| preparing | 0.000014 |
| executing | 0.000002 |
| Sending data | 0.000384 |
| end | 0.000005 |
| query end | 0.000008 |
| closing tables | 0.000007 |
| freeing items | 0.000055 |
| cleaning up | 0.000010 |
+----------------------+----------+
16 rows in set, 1 warning (0.00 sec)
##查看资源消耗情况比如CPU，IO等
mysql> show profile cpu, block io, memory,swaps,context switches,source for query 2;
##查看全部情况
mysql> show profile all for query 2;
若有收获，就点个赞吧
0 人点赞
