/ 2019-07-02
从左到右性能依次变好.
ALL :
全表扫描,不走索引
例子:
1. 查询条件列,没有索引
SELECT * FROM t_100w WHERE k2='780P';
2. 查询条件出现以下语句(辅助索引列)
USE world
DESC city;
DESC SELECT * FROM city WHERE countrycode <> 'CHN';
DESC SELECT * FROM city WHERE countrycode NOT IN ('CHN','USA');
DESC SELECT * FROM city WHERE countrycode LIKE '%CH%';
注意:对于聚集索引列,使用以上语句,依然会走索引
DESC SELECT * FROM city WHERE id <> 10;
INDEX :
全索引扫描
1. 查询需要获取整个索引树种的值时:
DESC SELECT countrycode FROM city;
2. 联合索引中,任何一个非最左列作为查询条件时:
idx_a_b_c(a,b,c) ---> a ab abc
SELECT * FROM t1 WHERE b
SELECT * FROM t1 WHERE c
RANGE :
索引范围扫描
辅助索引> < >= <= LIKE IN OR
主键 <> NOT IN
例子:
1.
DESC SELECT * FROM city WHERE id<5;
2.
DESC SELECT * FROM city WHERE countrycode LIKE 'CH%';
3.
DESC SELECT * FROM city WHERE countrycode IN ('CHN','USA');
注意:
1和2例子中,可以享受到B+树的优势,但是3例子中是不能享受的.
所以,我们可以将3号列子改写:
DESC SELECT * FROM city WHERE countrycode='CHN'
UNION ALL
SELECT * FROM city WHERE countrycode='USA';
ref:
非唯一性索引,等值查询
DESC SELECT * FROM city WHERE countrycode='CHN';
eq_ref:
在多表连接时,连接条件使用了唯一索引(uk pK)
DESC SELECT b.name,a.name FROM city AS a
JOIN country AS b
ON a.countrycode=b.code
WHERE a.population <100;
DESC country
system,const :
唯一索引的等值查询
DESC SELECT * FROM city WHERE id=10;
extra:
filesort ,文件排序.
SHOW INDEX FROM city;
ALTER TABLE city ADD INDEX CountryCode(CountryCode);
ALTER TABLE city DROP INDEX idx_c_p;
DESC SELECT * FROM city WHERE countrycode='CHN' ORDER BY population
ALTER TABLE city ADD INDEX idx_(population);
DESC SELECT * FROM city WHERE countrycode='CHN' ORDER BY population
ALTER TABLE city ADD INDEX idx_c_p(countrycode,population);
ALTER TABLE city DROP INDEX idx_;
ALTER TABLE city DROP INDEX CountryCode;
DESC SELECT * FROM city WHERE countrycode='CHN' ORDER BY population
结论:
1.当我们看到执行计划extra位置出现filesort,说明由文件排序出现
2.观察需要排序(ORDER BY,GROUP BY ,DISTINCT )的条件,有没有索引
3. 根据子句的执行顺序,去创建联合索引
索引优化效果测试:
优化前:
[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf \
> --concurrency=100 --iterations=1 --create-schema='oldboy' \
> --query="select * from oldboy.t_100w where k2='780P'" engine=innodb \
> --number-of-queries=2000 -uroot -p123 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Running for engine rbose
Average number of seconds to run all queries: 701.743 seconds
Minimum number of seconds to run all queries: 701.743 seconds
Maximum number of seconds to run all queries: 701.743 seconds
Number of clients running queries: 100
Average number of queries per client: 20
优化后:
[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='oldboy' --query="select * from oldboy.t_100w where k2='780P'" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Running for engine rbose
Average number of seconds to run all queries: 0.190 seconds
Minimum number of seconds to run all queries: 0.190 seconds
Maximum number of seconds to run all queries: 0.190 seconds
Number of clients running queries: 100
Average number of queries per client: 20
联合索引:
1. SELECT * FROM t1 WHERE a= b=
我们建立联合索引时:
ALTER TABLE t1 ADD INDEX idx_a_b(a,b);
ALTER TABLE t1 ADD INDEX idx_b_a(b,a);
以上的查询不考虑索引的顺序,优化器会自动调整where的条件顺序
注意: 索引,我们在这种情况下建索引时,需要考虑哪个列的唯一值更多,哪个放在索引左边.
2. 如果出现where 条件中出现不等值查询条件
DESC SELECT * FROM t_100w WHERE num <1000 AND k2='DEEF';
我们建索引时:
ALTER TABLE t_100w ADD INDEX idx_2_n(k2,num);
语句书写时
DESC SELECT * FROM t_100w WHERE k2='DEEF' AND num <1000 ;
3. 如果查询中出现多子句
我们要按照子句的执行顺序进行建立索引.
题目意思: 我们公司业务慢,请你从数据库的角度分析原因
1.mysql出现性能问题,我总结有两种情况:
(1)应急性的慢:突然夯住
应急情况:数据库hang(卡了,资源耗尽)
处理过程:
1.show processlist; 获取到导致数据库hang的语句
2. explain 分析SQL的执行计划,有没有走索引,索引的类型情况
3. 建索引,改语句
(2)一段时间慢(持续性的):
(1)记录慢日志slowlog,分析slowlog
(2)explain 分析SQL的执行计划,有没有走索引,索引的类型情况
(3)建索引,改语句
业务
1.产品的功能
2.用户的行为
"热"查询语句 --->较慢--->slowlog
"热"数据
为了使索引的使用效率更高,在创建索引时,必须考虑在哪些字段上创建索引和创建什么类型的索引。那么索引设计原则又是怎样的?
略.回顾一下,聚集索引结构.
(0)