如果出现如下情况,可以创建索引。
SELECT comment_id, product_id, comment_text, comment_time, user_id FROM product_comment WHERE user_id = 7851
运行结果
运⾏时间为0.699s,你能看到查询效率还是⽐较低的。当我们对user_id字段创建索引之后,运⾏时间为 0.047s,不到原来查询时间的1/10。
索引就是让数据按照某种顺序进行存储或检索,因此当我们使用 Group By 对数据查询或者使用 Order By 对数据进行排序的时候,就需要对分组或者排序字段建立索引。
SELECT user_id, count(*) as num FROM product_comment group by user_id limit 100
运⾏结果(100条记录,运⾏时间1.666s)
对 user_id 创建索引,再执行 SQL 语句
SELECT user_id, count(*) as num FROM product_comment group by user_id limit 100
运⾏结果(100条记录,运⾏时间0.042s):
image
SELECT DISTINCT(user_id) FROM product_comment
运⾏结果(600637条记录,运⾏时间2.283s). 如果我们对user_id创建索引,再执⾏SQL语句。
SELECT DISTINCT(user_id) FROM `product_comment`
运⾏结果(600637条记录,运⾏时间0.627s):建立索引,SQL 查询效率上有所提升。
当对某条数据进行 UPDATE 或者DELETE 操作的时候,可以考虑建立索引。
当没有对 comment_text 建立索引时,执行下面语句:
UPDATE product_comment SET product_id = 10002 WHERE comment_text = '462eed7ac6e791292a79'
运⾏结果为Affected rows: 1,运⾏时间为1.173s。
对 comment_text 字段建立索引
UPDATE product_comment SET product_id = 10001 WHERE comment_text = '462eed7ac6e791292a79'
运⾏结果为Affected rows: 1,运⾏时间仅为0.1110s。
comment_text 没有建立索引,执行如下语句
DELETE FROM product_comment WHERE comment_text = '462eed7ac6e791292a79'
运⾏结果为Affected rows: 1,运⾏时间为1.027s,
对comment_text创建了索引,再来执⾏这条SQL语句,运⾏时间为0.032s,时间是原来的1/32。 6. 做多表连接操作时,创建索引需要注意:
其次,对 用于连接字段创建索引,并且该字段在夺标中的类型必须一致。
可以使用 EXPLAIN 关键字来看 MySQL 中一条 SQL 语句的执行计划:
EXPLAIN SELECT comment_id, user_id, comment_text FROM product_comment WHERE comment_id+1 = 900001
执行结果
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-----
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extr
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-----
| 1 | SIMPLE | product_comment | NULL | ALL | NULL | NULL | NULL | NULL | 996663 | 100.00 | Usin
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+
可以看到,如果对索引进行表达式计算,那么索引就失效了。这是因为我们需要把索引字段都取出来,然后依次进行表达式计算进行条件判断,因此采用了全表扫描的方式,运行时间会慢很多,执行时间为 2.538 秒。
SQL 如果写成这样:
SELECT comment_id, user_id, comment_text FROM product_comment WHERE comment_id = 900000
运⾏时间为0.039秒。
比如 我们想要对 comment_text 的前三位为abc 的 内容进行条件筛选。
EXPLAIN SELECT comment_id, user_id, comment_text FROM product_comment WHERE SUBSTRING(comment_text, 1,3)='a
运行结果
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-----
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extr
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-----
| 1 | SIMPLE | product_comment | NULL | ALL | NULL | NULL | NULL | NULL | 996663 | 100.00 | Usin
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+
EXPLAIN SELECT comment_id, user_id, comment_text FROM product_comment WHERE comment_id = 900001 OR comment_text
运行结果
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-----
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extr
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-----
| 1 | SIMPLE | product_comment | NULL | ALL | PRIMARY | NULL | NULL | NULL | 996663 | 10.00 | Usin
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+
EXPLAIN SELECT comment_id, user_id, comment_text FROM product_comment WHERE comment_text LIKE '%abc'
索引是不生效的
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-----
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extr
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-----
| 1 | SIMPLE | product_comment | NULL | ALL | NULL | NULL | NULL | NULL | 996663 | 11.11 | Usin
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+-
这是因为索引并不存储空值,所以最好在设计数据表的时候就将字段设置为NOTNULL约束,⽐如你可以将 INT类型的字段,默认值设置为0。将字符类型的默认值设置为空字符串(’’)
最左原则也就是需要从左到右使用的索引字段中的字段,一条 SQL 语句可以只使用联合索引的一部分,但是需要从最左侧开始,否则会失效。