MySQL - 索引使用优化和规范 - 好文

目录

一、索引使用总结
<https://blog.csdn.net/qq_33961117/article/details/86498096#%E4%B8%80%E3%80%81%E7%B4%A2%E5%BC%95%E4%BD%BF%E7%94%A8%E6%80%BB%E7%BB%93>

二、索引建立原则 - 运维规范
<https://blog.csdn.net/qq_33961117/article/details/86498096#%E4%BA%8C%E3%80%81%E7%B4%A2%E5%BC%95%E5%BB%BA%E7%AB%8B%E5%8E%9F%E5%88%99%20-%20%E8%BF%90%E7%BB%B4%E8%A7%84%E8%8C%83>

三、未建立索引时，查询操作规范 - 开发规范
<https://blog.csdn.net/qq_33961117/article/details/86498096#%E4%B8%89%E3%80%81%E6%9C%AA%E5%BB%BA%E7%AB%8B%E7%B4%A2%E5%BC%95%E6%97%B6%EF%BC%8C%E6%9F%A5%E8%AF%A2%E6%93%8D%E4%BD%9C%E8%A7%84%E8%8C%83%20-%20%E5%BC%80%E5%8F%91%E8%A7%84%E8%8C%83>

四、索引其他使用经验总结
<https://blog.csdn.net/qq_33961117/article/details/86498096#%E5%9B%9B%E3%80%81%E7%B4%A2%E5%BC%95%E5%85%B6%E4%BB%96%E4%BD%BF%E7%94%A8%E7%BB%8F%E9%AA%8C%E6%80%BB%E7%BB%93>

一、索引使用总结

1. 一定是为搜索条件的字段创建索引，比如select * from s1 where id = 333;就需要为id加上索引

2. 在表中已经有大量数据的情况下，建索引会很慢，且占用硬盘空间，建完后查询速度加快
比如create index idx on s1(id);会扫描表中所有的数据，然后以id为数据项，创建索引结构，存放于硬盘的表中。
建完以后，再查询就会很快了。

3. 需要注意的是：innodb表的索引会存放于s1.ibd文件中，而myisam表的索引则会有单独的索引文件table1.MYI

MySAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在innodb中，表数据文件本身就是按照B+Tree（BTree即Balance
True）组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此innodb表数据文件本身就是主索引。

因为inndob的数据文件要按照主键聚集，所以innodb要求表必须要有主键（Myisam可以没有），如果没有显式定义，则mysql系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则mysql会自动为innodb表生成一个隐含字段作为主键，这字段的长度为6个字节，类型为长整型.

二、索引建立原则 - 运维规范

0. 建表时一定要有主键,如果相关列可以作为主键,做一个无关列

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

主键索引和唯一键索引，在查询中使用是效率最高的。

select count(*) from world.city; select count(distinct countrycode) from
world.city; select count(distinct countrycode,population ) from world.city;
注意：如果重复值较多，可以考虑采用联合索引

2．为经常需要排序、分组和联合操作的字段建立索引
经常需要ORDER BY、GROUP BY,join on等操作的字段，排序操作会浪费很多时间。
如果为其建立索引，可以有效地避免排序操作。

3．为常作为where查询条件的字段建立索引
如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，
为这样的字段建立索引，可以提高整个表的查询速度。

* 经常查询
* 列值的重复值少(业务层面调整)
注：如果经常作为条件的列，重复值特别多，可以建立联合索引。

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

例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。
如果只检索字段的前面的若干个字符，这样可以提高检索速度。

5．限制索引的数目
索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。

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

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

建索引原则

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

1) 不允许进行没有查询条件（where），或者查询条件没有建立索引时候的*类查询
select * from tab; 全表扫描。 select * from tab where 1=1;
在业务数据库中，特别是数据量比较大的表。是没有全表扫描这种需求。 1、对用户查看是非常痛苦的。 2、对服务器来讲毁灭性的。（1）select * from
tab; SQL改写成以下语句 - 需要在price列上建立索引 selec * from tab order by price limit 10
（2）name列没有索引 select * from tab where name='zhangsan' 改：
1、换成有索引的列作为查询条件    2、将name列建立索引

2) 避免查询结果集是原表中的大部分数据，即约25％以上。

查询的结果集，超过了总数行数25%，优化器觉得就没有必要走索引了。
假如：tab表 id，name id:1-100w ，id列有索引 select * from tab where id>500000;
如果业务允许，可以使用limit控制。怎么改写？结合业务判断，有没有更好的方式。如果没有更好的改写方案
尽量不要在mysql存放这个数据了。放到redis里面。

3) 由于索引本身失效，统计数据不真实
索引有自我维护的能力。
对于表内容变化比较频繁的情况下，有可能会出现索引失效。

4) 避免查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等)
算术运算错误的例子：select * from test where id-1=9; 正确的例子：select * from test where
id=10; 函数运算 desc select * from blog_userinfo where
DATE_FORMAT(last_login,'%Y-%m-%d') >= '2019-01-01'; 子查询
5) 隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误 - 查询条件为int，数据库内为char
使用int类型进行查询表内varchar字段内容 select * from t1 where telnum=110; 这样会导致索引失效. 错误的例子：
------------------------ mysql> alter table tab add index inx_tel(telnum);
Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0
mysql> 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 | |
+--------+-------------+------+-----+---------+-------+ 3 rows in set (0.01
sec) mysql> select * from tab where telnum='1333333'; +------+------+---------+
| id | name | telnum | +------+------+---------+ | 1 | a | 1333333 |
+------+------+---------+ 1 row in set (0.00 sec) mysql> select * from tab
where telnum=1333333; +------+------+---------+ | id | name | telnum |
+------+------+---------+ | 1 | a | 1333333 | +------+------+---------+ 1
row in set (0.00 sec) 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 |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
1 row in set (0.00 sec) 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 |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec) 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 |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec) 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 |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
1 row in set (0.00 sec) mysql>
6) 避免使用 <> ，not in - 原因：不走索引
EXPLAIN SELECT * FROM teltab WHERE telnum <> '110'; EXPLAIN SELECT * FROM
teltab WHERE telnum NOT IN ('110','119'); ------------ mysql> select * from
tab where telnum <> '1555555'; +------+------+---------+ | id | name | telnum
| +------+------+---------+ | 1 | a | 1333333 |
+------+------+---------+ 1 row in set (0.00 sec) mysql> explain select * from
tab where telnum <> '1555555'; ----- 单独的>,<,in 有可能走，也有可能不走，和结果集有关，尽量结合业务添加limit
or或in 尽量改成union EXPLAIN SELECT * FROM teltab WHERE telnum IN ('110','119');
改写成： EXPLAIN SELECT * FROM teltab WHERE telnum='110' UNION ALL SELECT * FROM
teltab WHERE telnum='119'

7) like "%_" 百分号在最前面不走索引
EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '31%' 走range索引扫描 EXPLAIN
SELECT * FROM teltab WHERE telnum LIKE '%110' 不走索引
%linux%类的搜索需求，可以使用elasticsearch 专门做搜索服务的数据库产品

8) 单独引用联合索引里非第一位置的索引列.作为条件查询时不走索引.
列子：复合索引： DROP TABLE t1 CREATE TABLE t1 (id INT,NAME VARCHAR(20),age INT ,sex
ENUM('m','f'),money INT); ALTER TABLE t1 ADD INDEX t1_idx(money,age,sex); DESC
t1 SHOW INDEX FROM t1 走索引的情况测试： EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE
money=30 AND age=30 AND sex='m'; EXPLAIN SELECT NAME,age,sex,money FROM t1
WHERE money=30 AND age=30 ; EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE
money=30 AND sex='m'; ----->部分走索引不走索引的： EXPLAIN SELECT NAME,age,sex,money
FROM t1 WHERE age=20 EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE age=30
AND sex='m'; EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE sex='m';
四、索引其他使用经验总结

* 范围问题：条件不明确，即条件中出现这些符号或关键字：>、>=、<、<=、!= 、between...and...、like、
* 尽量选择区分度高的列作为索引
* 区分度的公式是count(distinct
col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0。
* =和in可以乱序.
* 比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式
* 索引列不能参与计算，保持列“干净”.
* 比如from_unixtime(create_time) =
’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time
= unix_timestamp(’2014-05-29’)
* 最左前缀匹配原则
* 对于组合索引mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配(指的是范围大了，有索引速度也慢)，比如a = 1
and b = 2 and c > 3 and d = 4
如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
* 排序条件为索引，则select也是索引字段。
* 避免使用select *
* count（1）或count（列）代替count（*）
* 创建表时尽量char代替varchar
* 表的字段顺序固定长度的字段优先
* 组合索引代替多个单利索引（进程使用多个条件查询时）
* 尽量使用短索引
* 使用连接（join）来代替子索引（sub-queries）
* 连表时注意条件类型需要一致
* 索引散列值（重复少）不适合建索引。

热门工具换一换