MySQL优化之高效的索引

高效的索引

提高查询速度，类似于电话薄，如果没有索引数据库就要一条条查找

索引本质上是数据库引擎用来快速查找数据的数据结构。

索引可以放入内存。通常被存储为二进制树(B+树)

缺点：1、增加数据的大小，因为它们必须永久存储在表旁边 2、每次更新、删除、更改数据库时，mysql必须更新对应的索引，这会影响我们正常操作的性能。

基于查询创建索引，不能基于表创建索引。因为索引的目的是加快运行较慢的查询

创建索引

explain SELECT customer_id FROM customers WHERE state='VA'  -- 解释执行

CREATE INDEX inx_state ON customers(state) -- 选择customers的state列作为索引

查看索引

SHOW INDEXES IN customers

索引类型：二进制树

当我们为两张表之间创建一组关系时，mysql会自动为外键创建索引，这样就可以快速连接表

索引过滤数据

explain关键词，解释select语句的执行

前缀索引

推荐使用：对于名字、地址较短的字符串列

对属性是字符或数字的列指定，索引的字符的范围，如

SELECT COUNT(DISTINCT LEFT(last_name,1)),	-- 统计字符串左侧指定长度的字符在表中有多少个
			COUNT(DISTINCT LEFT(last_name,5)),
			COUNT(DISTINCT LEFT(last_name,20))
FROM customers

只需要包含前5个字符，就可以唯一识别表中大多数姓氏。那么在设计时，索引设置少点前缀，放入内存中的大小会小一点

CREATE INDEX inx_last_name ON customers(last_name(20)); 

全文索引

推荐使用：报纸文章或博客文章正文，较长的字段

问题：如果要在搜寻WHERE title LIKE '%react redux%' OR body LIKE '%react reduc%';这样的字符串，怎么不搜索全表，搜索到到的结果，应该是这两个单词的任意组合，中间有其它单词或空格和顺序不一致等情况

CREATE FULLTEXT INDEX idx_title_body ON posts(title,body);

SELECT * ,WHERE MATCH(title,body) AGAINST('react redux') -- 在select上把这个查询的字段写上，能查询相关性得分]
FROM `posts`
WHERE MATCH(title,body) AGAINST('react redux'); -- 使用索引函数。注意创建索引包含多少列，函数参数里也要包含相同的列 

全文索引可计算相关性得分

两种模式：

1、自然语言模式（默认模式）

2、布尔模式。可包含和排除某些单词

IN BOOLEAN MODE

SELECT *,MATCH(title,body) AGAINST('react redux')
FROM `posts`
WHERE MATCH(title,body) AGAINST('react -redux +form' IN BOOLEAN MODE);-- 减号是排除这个单词，加号加入这个单词

准确包含这个字符串（加一个双引号）任何一点不匹配就搜不到

SELECT *,MATCH(title,body) AGAINST('react redux')
FROM `posts`
WHERE MATCH(title,body) AGAINST('"handling a form"' IN BOOLEAN MODE);

复合索引

不管有多少个索引，执行时mysql最多只会选一个索引。如下：

SHOW INDEXES IN customers; 
EXPLAIN SELECT customer_id
FROM customers
WHERE state='CA' AND points>1000

问题：积分还是按state='CA' 所有在加州的，全部扫描（索引只做了一半的工作），如果加州的人数很多，效率又会变慢

解决：复合索引，允许对多列建立索引

mysql中最多允许建立16列复合索引

CREATE INDEX inx_state_points ON customers(state,points); -- 注意创建索引列的顺序是有影响的

新手错误——为每列都建一个索引：

因为单列索引只能搞定一半的活，它们占用很多空间，每次修改表中的数据时，必须更新这些索引。索引越多，写入速度越慢。另外，mysql会自动将表的主键包含在每个二级索引中，单个索引会浪费空间。

DROP INDEX inx_points ON customers

复合索引中列的顺序

1、对列进行排序，让更频繁使用的列排前面

2、把基数更高的列排最前面（基数表示索引中唯一值的数量）

规则二，要按具体条件分析

如下图，要找到，以'A'开头字母的，且在'CA'州的，第一种姓名在前，第二种州在前（效率更高）

当索引无效时

出现如下情况，改变查询语句来匹配索引

EXPLAIN SELECT customer_id
FROM customers
WHERE state='CA' OR points>1000;	-- 或的话，复合索引就不管用了

CREATE INDEX idx_points ON customers(points);
EXPLAIN 
SELECT customer_id FROM customers WHERE state='CA' 
UNION
SELECT customer_id FROM customers WHERE points>1000;

另，还有一种情况，mysql只能对单独列筛选识别优化索引

如下

EXPLAIN SELECT customer_id FROM customers
WHERE points+10 > 2000

EXPLAIN SELECT customer_id FROM customers
WHERE points> 2000

使用索引排序

当我们在列上添加索引时，mysql会获取该列中的所有值，对其排序，并将它们存储在索引中。

所以在排序的时候可以依赖索引

EXPLAIN SELECT customer_id FROM customers
ORDER BY first_name; -- 对不存在索引中的列进行排序

SHOW STATUS LIKE 'last_query_cost'; -- 显示上次查询花费的代价

EXPLAIN SELECT customer_id FROM customers
ORDER BY state,points; -- 对存在索引中的列进行排序

SHOW STATUS LIKE 'last_query_cost';

注意：排序的列顺序和中间插了别的列，都会导致，使用外部文件排序

覆盖索引

一个包含所有满足查询所需要的数据的索引

为什么之前只选择主键的那列，因为mysql会把主键索引包含进去所有的二级索引，所以要想选择的列要包含索引的列，就可以使用覆盖索引了。

EXPLAIN SELECT customer_id,first_name FROM customers   -- 选择了不在索引的列
ORDER BY state,points;

EXPLAIN SELECT customer_id,state FROM customers   -- 选择了在索引的列
ORDER BY state,points;

维护索引

重复索引：同一组列上且顺序一致的索引，如ABC和ABC（mysql不会检查排除重复索引）

多余索引：如（A,B)、(A)。原来的（A、B)索引可以优化包含列A的查询，（A)索引就多余了

在创建前先检查已有索引是否用得上，删除重复，多余和不用的索引。