命令记不住

登陆

MySQL 索引规范

  |   129 浏览

MySQL索引规范

1.索引规范重点关注

建立索引的原则

1.说明

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

2.建表时一定要有主键,一般是个无关列(必须)

2.1选择唯一性索引

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

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

2.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等操作的字段为其建立索引，优化查询(必须)

排序操作会浪费很多时间。
注：如果经常作为条件的列，重复值特别多，可以建立联合索引。

4.尽量使用前缀来索引

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

2.索引规范必须保证

1.限制索引的数目
索引的数目不是越多越好,可能会产生的问题:

  • (1) 每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
  • (2) 修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。
  • (3) 优化器的负担会很重,有可能会影响到优化器的选择.

percona-toolkit中有个工具,专门分析索引是否有用

2.删除不再使用或者很少使用的索引(percona toolkit)

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

3.大表加索引,要在业务不繁忙期间操作,尽量少在经常更新值的列上建索引

建索引原则

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

1.不走索引的情况（开发规范）

1.没有查询条件，或者查询条件没有建立索引
select * from tab;              全表扫描。
select  * from tab where 1=1;   判断恒为真
2.select * from tab;

SQL改写成以下语句：

selec  * from  tab  order by  price  limit 10 ;   
#需要在price列上建立索引
3.条件没有索引
select  * from  tab where name='zhangsan' 
# name列没有索引`

改：
- 1、换成有索引的列作为查询条件
- 2、将name列建立索引
4.查询结果集是原表中的大部分数据，应该是25％以上

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

假如：tab表 id，name    id:1-100w  ，id列有(辅助)索引

select * from tab  where id>500000;

如果业务允许，可以使用limit控制。

怎么改写 ？
结合业务判断，有没有更好的方式。如果没有更好的改写方案
尽量不要在mysql存放这个数据了。放到redis里面。
5.索引本身失效，统计数据不真实

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

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

例子：

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

算术运算
函数运算
子查询
7.隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.

这样会导致索引失效. 错误的例子：

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)
8.<> ，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'
9.like "%_" 百分号在最前面不走
EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '31%'   走range索引扫描

EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '%110'  不走索引

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

10.单独引用联合索引里非第一位置的索引列.作为条件查询时不走索引.
列子：

复合索引：
DESC t1
SHOW INDEX FROM t1

idx_a_b_c(a,b,c)

3.索引总结

1.走索引：

where  a  b  c
where  a b 
where  a

where a c
where a c b

2.不走索引

where c 
where b 
where bc 
where cb
where ca 
where cba

总之：任何一个不以第一索引列做为查询条件的select，都是不走索引的

4.索引效果压力测试

1、模拟数据库数据

drop database x86cloud;
create database x86cloud charset utf8;

2.为了测试我们创建一个x86cloud的库创建一个t1的表，然后导入50万行数据，脚本如下：

vim slap.sh
#!/bin/bash  
HOSTNAME="localhost" 
PORT="3306" 
USERNAME="root" 
PASSWORD="1" 
DBNAME="x86cloud" 
TABLENAME="t1" 
#create database 
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} -e "drop database if exists ${DBNAME}" 
create_db_sql="create database if not exists ${DBNAME}" 
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} -e "${create_db_sql}" 
#create table 
create_table_sql="create table if not exists ${TABLENAME}(stuid int not null primary key,stuname varchar(20) not null,stusex char(1)   
not null,cardid varchar(20) not null,birthday datetime,entertime datetime,address varchar(100)default null)" 
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${create_table_sql}" 
#insert data to table 
i="1" 
while [ $i -le 500000 ]  
do  
insert_sql="insert into ${TABLENAME}  values($i,'alexsb_$i','1','110011198809163418','1990-05-16','2017-09-13','x86cloudedu')" 
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${insert_sql}" 
let i++  
done  
#select data  
select_sql="select count(*) from ${TABLENAME}" 
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${select_sql}"

2.1执行脚本：

sh slap.sh

2.2或者直接source

db01 [x86cloud]>drop database x86cloud;
db01 [x86cloud]>source /root/x86cloud.sql
  1. 检查数据可用性
mysql -uroot -p
select count(*) from x86cloud.t1;

3、在没有优化之前我们使用mysqlslap来进行压力测试

mysqlslap --defaults-file=/etc/my.cnf \
 --concurrency=100 --iterations=1 --create-schema='x86cloud' \
--query="select * from x86cloud.t1 where stuname='alexsb_100'" engine=innodb \
--number-of-queries=2000 -uroot -p123 -verbose

4.压力测试工具:

  • tpcc
  • sysbench

5.加索引和不加索引的对吧
1.不加索引执行时间

[root@db01 ~]#mysqlslap --defaults-file=/etc/my.cnf  --concurrency=100 --iterations=1 --create-schema='oldboy' --query="select * from oldboy.t1 where stuname='alexsb_100'" engine=innodb --number-of-queries=2000 -uroot  -verbose
Benchmark
	Running for engine rbose
	Average number of seconds to run all queries: 472.363 seconds
	Minimum number of seconds to run all queries: 472.363 seconds
	Maximum number of seconds to run all queries: 472.363 seconds
	Number of clients running queries: 100
	Average number of queries per client: 20
##########################################################################################################################################################

2.加索引执行时间

[root@db01 ~]#mysqlslap --defaults-file=/etc/my.cnf  --concurrency=100 --iterations=1 --create-schema='oldboy' --query="select * from oldboy.t1 where stuname='alexsb_100'" engine=innodb --number-of-queries=2000 -uroot  -verbose
Benchmark
	Running for engine rbose
	Average number of seconds to run all queries: 0.318 seconds
	Minimum number of seconds to run all queries: 0.318 seconds
	Maximum number of seconds to run all queries: 0.318 seconds
	Number of clients running queries: 100
	Average number of queries per client: 20
推荐阅读