金华网站制作费用,数字今天科技 网站,wordpress 图片分离,免费建站源码正确的选择和创建索引是实现高性能查询的基础#xff0c;以下是高效使用索引的方法 演示的sql 独立的列
独立的列指的是索引既不是表达式的一部分也不是函数的参数。
mysql select actor_id from actor where actor_id 1 5;mysql SELECT actor_id FROM actor WHER… 正确的选择和创建索引是实现高性能查询的基础以下是高效使用索引的方法 演示的sql 独立的列
独立的列指的是索引既不是表达式的一部分也不是函数的参数。
mysql select actor_id from actor where actor_id 1 5;mysql SELECT actor_id FROM actor WHERE CAST(actor_id AS CHAR) 5;前缀索引
如果索引是很长的列那么索引会变得很大并且导致索引数层数变高。通常可以索引的部分字符这样可以节约索引空间。但是同时也要保证区分度。 区分度的计算这里以city表city字段为例
mysql select count(distinct LEFT(city,3))/count(*) as e1, count(distinct LEFT(city,4))/count(*) as e2, count(distinct LEFT(city,5))/count(*) as e3, count(distinct LEFT(city,6))/count(*) as e4, count(distinct LEFT(city,7))/count(*) as e5, count(distinct LEFT(city,8))/count(*) as e6 , count(distinct LEFT(city,9))/count(*) as e7 , count(distinct LEFT(city,10))/count(*) as e8 from city_demo ;
----------------------------------------------------------------
| e1 | e2 | e3 | e4 | e5 | e6 | e7 | e8 |
----------------------------------------------------------------
| 0.0236 | 0.0293 | 0.0305 | 0.0309 | 0.0310 | 0.0310 | 0.0310 | 0.0311 |
----------------------------------------------------------------
可以看到当字符长度是5或者6时区分度已经不怎么增长了。 可以创建前缀索引
mysql alter table city_demo add key (city(6));前缀索引可以使得索引更小更快但是他不可以做order by和group by。
多列索引
很多人对多列索引的理解不够一种常见的是为每个列都创建索引或者错误顺序创建索引。或者把where条件后面所有的列都创建索引。
在多个列上创建独立的单列索引大多数情况下不能提高MYSQL的查询性能。比如
mysql select film_id,actor_id from film_actor where actor_id 1 or film_id 1;在老的版本这个查询会全表扫描但是在新版本中会对这两个单列索引进行扫描并将结果合并索引合并策略是一种优化的结果。
mysql explain select film_id,actor_id from film_actor where actor_id 1 or film_id 1 \G;
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: film_actorpartitions: NULLtype: index_merge
possible_keys: PRIMARY,idx_fk_film_idkey: PRIMARY,idx_fk_film_idkey_len: 2,2ref: NULLrows: 29filtered: 100.00Extra: Using union(PRIMARY,idx_fk_film_id); Using where
1 row in set, 1 warning (0.00 sec)基本等价于
mysql select film_id,actor_id from film_actor where actor_id 1 union all select film_id,actor_id from film_actor where film_id 1 and actor_id ! 1;合适的索引顺序
索引列的顺序意味这首先按照最左序列进行排序其次是第二列索引可以根据升序或者降序扫描以满足order bygroup bydistinct等子句的需求。
索引列的顺序问题将选择性高区分度高的列放在最前面是有帮助的但是排序以及避免随机IO的优先级更高当不需要排序和分组时选择性高的列放前面举例说明。
mysql select * from payment where staff_id2 and customer_id584;联合索引的顺序取决于哪个区分度更高
mysql select sum(staff_id2),sum(customer_id584) from payment \G
*************************** 1. row ***************************sum(staff_id2): 7990
sum(customer_id584): 30这时是staff_id2基数更大应该把customer_id放到前面 以上是根据具体值的对于全量数据来说
mysql SELECT COUNT(DISTINCT staff_id)/count(*) as e1,count(distinct customer_id)/count(*) as e2 ,count(*) from payment \G
*************************** 1. row ***************************e1: 0.0001e2: 0.0373
count(*): 16044
1 row in set (0.00 sec)全局来说customer_id区分度更高作为索引列第一列。
聚簇索引
聚簇索引并不是一种索引类型而是一种数据存储方式在Innodb中保存了B-tree索引和数据行叶子页包含行的全部数据节点页只包含索引列这个索引列就是主键列如果没有创建主键那么Innodb会隐式创建主键。
聚集数据的好处
这个索引列和行数据是紧凑存储在一起的根据主键查找行数据时可减少磁盘IO次数使用覆盖索引可以直接使用叶节点的主键值 聚集数据的缺点按照顺序插入的每次更新会导致其他行移动新的位置在移动过程中会导致页分裂删除的时候会引起页的合并
覆盖索引
MYSQL可以使用索引来获取列的数据这样就可不需要读取整行的数据了如果索引的叶子节点已经包含要查询的数据了那么就不需要回表查询如果一个索引包含需要查询字段的值那么就成为覆盖索引
