MySQL-10-索引应用规范

建立索引的原则

SQL文件

sql文件下载链接:
https://alnk-blog-pictures.oss-cn-shenzhen.aliyuncs.com/blog-pictures/world.sql
https://alnk-blog-pictures.oss-cn-shenzhen.aliyuncs.com/blog-pictures/t100w.txt

导入数据库
mysql> source /root/world.sql
mysql> source /root/t100w.txt


说明
为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。那么索引设计原则又是怎样的?


设计原则
1 (必须的) 建表时一定要有主键,一般是个无关列


2 选择唯一性索引
唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录

例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息
如果使用姓名的话，可能存在同名现象，从而降低查询速度

优化方案:
(1) 如果非得使用重复值较多的列作为查询条件(例如:男女),可以将表逻辑拆分
(2) 可以将此列和其他的查询类,做联和索引
select count(*) from world.city;
select count(distinct countrycode) from world.city;
select count(distinct countrycode, population) from world.city;


3(必须的) 为经常需要where 、ORDER BY、GROUP BY、join on等操作的字段建立索引
排序操作会浪费很多时间。
where  A B C      ----》 A  B  C
in 
where A   group by B  order by C
A,B，C

如果为其建立索引，优化查询
注：如果经常作为条件的列，重复值特别多，可以建立联合索引


4 尽量使用前缀来索引
如果索引字段的值很长，最好使用值的前缀来索引


5 限制索引的数目
索引的数目不是越多越好。
可能会产生的问题:
(1) 每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
(2) 修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。
(3) 优化器的负担会很重,有可能会影响到优化器的选择.
percona-toolkit中有个工具,专门分析索引是否有用


6 删除不再使用或者很少使用的索引(percona toolkit)
pt-duplicate-key-checker
表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理
员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响


7 大表加索引,要在业务不繁忙期间操作


8 尽量少在经常更新值的列上建索引


总结
(1) 必须要有主键,如果没有可以做为主键条件的列,创建无关列
(2) 经常做为where条件列  order by  group by  join on, distinct 的条件(业务:产品功能+用户行为)
(3) 最好使用唯一值多的列作为索引,如果索引列重复值较多,可以考虑使用联合索引
(4) 列值长度较长的索引列,我们建议使用前缀索引.
(5) 降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit(xxxxx)
(6) 索引维护要避开业务繁忙期


不走索引的情况

没有查询条件，或者查询条件没有建立索引

select * from tab;            全表扫描
select * from tab where 1=1;  全表扫描
在业务数据库中，特别是数据量比较大的表，是没有全表扫描这种需求
1、对用户查看是非常痛苦的
2、对服务器来讲毁灭性的

解决办法
select * from tab;
SQL改写成以下语句：
select  * from  tab  order by  price  limit 10;  # 需要在price列上建立索引


select  * from  tab where name='zhangsan';  # name列没有索引
改：
1、换成有索引的列作为查询条件
2、将name列建立索引

查询结果集是原表中的大部分数据，应该是25％以上

查询的结果集，超过了总数行数25%，优化器觉得就没有必要走索引了

假如：
tab表有id，name两个字段    
id数据:1-100w，id列有(辅助)索引

select * from tab where id>500000;
如果业务允许，可以使用limit控制

结合业务判断，有没有更好的方式。如果没有更好的改写方案
尽量不要在mysql存放这个数据了。放到redis里面。

索引本身失效，统计数据不真实

索引有自我维护的能力
对于表内容变化比较频繁的情况下，有可能会出现索引失效，一般是删除重建索引解决这个问题

现象:
有一条select语句平常查询时很快,突然有一天很慢,会是什么原因
select?  --->索引失效，统计数据不真实
DML ?    --->锁冲突

查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等)

例子：
错误的例子：select * from test where id-1=9;
正确的例子：select * from test where id=10;

算术运算
函数运算
子查询

隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误

这样会导致索引失效. 错误的例子：
mysql> alter table tab add index inx_tel(telnum);

mysql> desc tab;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  |     | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| telnum | varchar(20) | YES  | MUL | NULL    |       |
+--------+-------------+------+-----+---------+-------+

mysql> select * from tab where telnum='1333333';  注意这里不是数值，是字符串，要使用单引号
+------+------+---------+
| id   | name | telnum  |
+------+------+---------+
|    1 | a    | 1333333 |
+------+------+---------+

mysql> select * from tab where telnum=1333333;   注意这里不是数值，是字符串，要使用单引号
+------+------+---------+
| id   | name | telnum  |
+------+------+---------+
|    1 | a    | 1333333 |
+------+------+---------+


mysql> explain  select * from tab where telnum='1333333';   # 此处使用了单引号，走了索引
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | tab   | ref   | inx_tel      | inx_tel | 63      | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+


mysql> explain  select * from tab where telnum=1333333;  # 此处没有走索引，是全表扫描
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | tab   | ALL  | inx_tel       | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+


mysql> explain  select * from tab where telnum=1555555;  # 此处没有走索引，是全表扫描
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | tab  | ALL  | inx_tel        | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+


mysql> explain  select * from tab where telnum='1555555';  # 此处使用了单引号，走了索引
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | tab   | ref  | inx_tel       | inx_tel | 63      | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+

<>、not in不走辅助索引

mysql> EXPLAIN  SELECT * FROM tab WHERE telnum  <> '110';  # 不走索引
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | tab   | ALL  | inx_tel       | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> EXPLAIN  SELECT * FROM tab WHERE telnum  NOT IN ('110','119');   # 不走索引
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | tab   | ALL  | inx_tel       | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> select * from tab where telnum <> '1555555';
+------+------+---------+
| id   | name | telnum  |
+------+------+---------+
|    1 | a    | 1333333 |
+------+------+---------+

mysql> explain select * from tab where telnum <> '1555555';  # 不走索引
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | tab   | ALL  | inx_tel       | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)


单独的 >、<、in、 有可能走，也有可能不走索引，和结果集有关，尽量结合业务添加limit
or或in  尽量改成union
mysql> EXPLAIN  SELECT * FROM tab  WHERE telnum  IN ('110','119');  # 不走索引
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | tab   | ALL  | inx_tel       | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

改写成：
mysql> EXPLAIN SELECT * FROM tab WHERE telnum='110'
    -> UNION ALL
    -> SELECT * FROM tab WHERE telnum='119';
+------+--------------+------------+------+---------------+---------+---------+-------+------+-----------------------+
| id   | select_type  | table      | type | possible_keys | key     | key_len | ref   | rows | Extra                 |
+----+--------------+------------+------+---------------+---------+---------+-------+------+-------------------------+
|  1   | PRIMARY      | tab        | ref  | inx_tel       | inx_tel | 83      | const |    1 | Using index condition |
|  2   | UNION        | tab        | ref  | inx_tel       | inx_tel | 83      | const |    1 | Using index condition |
| NULL | UNION RESULT | <union1,2> | ALL  | NULL          | NULL    | NULL    | NULL  | NULL | Using temporary       |
+------+--------------+------------+------+---------------+---------+---------+-------+------+-----------------------+

like "%_" 百分号在最前面不走索引

mysql> EXPLAIN SELECT * FROM tab WHERE telnum LIKE '31%';  # 走range索引扫描
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
|  1 | SIMPLE      | tab   | range | inx_tel       | inx_tel | 83      | NULL |    1 | Using index condition |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+

mysql> EXPLAIN SELECT * FROM tab WHERE telnum LIKE '%110';  # 不走索引
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | tab   | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

有类似这种 %131% 的搜索需求，可以使用elasticsearch+mongodb 专门做搜索服务的数据库产品

posted @ 2021-06-23 16:27  李成果  阅读(139)  评论(0)  编辑  收藏  举报