MySQL索引的优化

无法使用索引的情况：非独立的列

1. select id from table where id+1 = 233;
2. select … from table where to_days(current_date) – to_days(date_col) <= 10;

索引不能是表达式的一部分，也不能是函数的参数，应始终将索引列单独放在比较符号的一侧

 

前缀索引和索引的选择性：

       ->索引的选择性是指，不重复的索引值与数据表记录总数之比，越接近1，查询效率越高

       ->select count(*) as cnt, left(col,3) as pref from table group by pref order by cnt desc

       ->limit 10;

计算完整列的选择性：

       ->select count(distinct left(col, 3))/count(*) from table;

       ->select count(distinct left(col, 4))/count(*) from table;

       ->select count(distinct left(col, 5))/count(*) from table;

结果接近0.031就认为可用，可能是因为选择性为1的话，性能提升不大反而使索引体积增大

 

新建前缀索引：

       ->alter table table_name add key keyname(col(length));

 

如果数据分布不均匀，则平均选择性稍显不足。

MySQL 无法使用前缀索引order by 或group by

也不能使用前缀索引做覆盖扫描

 

多列索引：

1. 单独创建索引：

->Create table table_name(

              ->col1 int,

              ->col2 int,

              ->key(col1),

              ->key(col2)

->);

 

->show index from table;

可以看到表中的索引信息，一般索引名默认是列名

 

大部分情况下并不能提高性能，例如：

->explain select id, name from table where id = 1 and name = “1”;

可以用到两个索引，但是在或语句中则不行：

->explain select id, name from table where id = 1 or name = “1”;

旧版本会进行全表扫描，5.0之后对两个索引单独扫描并合并结果，称之为“索引合并策略”，一般会在多个索引相交(and)，联合(or)时，严重时会在explain的Extra里面产生嵌套操作，效率甚至不如全表查询。

1. 多列索引：
   1. 将选择性最高的列放在最前，但也只适用某些不需要排序和分组的场合。
   2. 选择性，并不是看选择符合数据的条数。

 

聚簇索引：可能会对查询有帮助，但也可能带来巨大的灾难。

 

优化查询：

1. 以多个简单查询，代替复杂查询。
2. 定期转移旧数据

->Delete from table where created < data_sub(now(), interval 3 month);

转化为

->rows_affected = 0

->do{

->    rows_affected = do_query(

->        “delete from table where created < data_sub(now(),interval 3 month)

->    limit 10000”)

->} while rows_affected >0;

注：以上不是SQL语句

1. ->show status like 'last_query_cost';可以查看上一次执行查询的开销

 

 

1. 简单的优化：

查询id大于5 的数量

->slecet count(*) from table where id>5;

在条数远大于5时建议替换为

->select (select count(*) from table) – count(*) from table where id < 6;

1. ->select sum(if(color = ‘blue’,1,0)) as blue, sum(if(color = ‘red’,1,0)) as red

->from table;

或者

->select sum(color = ‘blue’) as blue, sum(color = ‘red’) as red

->from table;

亦或者

->select count(color = ‘blue’ or null) as blue, count(color = ‘red’ or null) as red

->from table;

 

 

笔记地址：链接: https://pan.baidu.com/s/1slFOXhb 密码: 8341

转载于:https://www.cnblogs.com/Doumiao/p/7714399.html