MySql索引查询优化 千万级数据普通查询和建立索引后数据查询耗时对比

 

前言

    索引：拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

    索引分单列索引和组合索引。

        单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。

        组合索引，即一个索引包含多个列。

    更多简介请移至：https://www.runoob.com/mysql/mysql-index.html

 

 

造数据

MySql表格中拥有1424w+数据

如何造数据? 文章：https://blog.csdn.net/qq_25305833/article/details/116501814

插入数据耗时约为：14240000调试/100每秒线程/60秒/60分钟=39.55小时

 

 

普通索引

创建索引：users表中name列索引字符串为前2个长度（简历字段索引分断）的索引名称为indexName

mysql> create index indexNmae on users(name(2));

删除索引：删除users表中索引未indexName的索引

msyql> drop index indexNmae on users;

查看索引：查看users表格中的所有索引

mysql> show index from users;

 

 

调试对比

 

    未建立索引的查询数据约为 3.94秒

mysql> 
mysql> drop index indexNmae on users;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> 
mysql> show index from users;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| users |          0 | PRIMARY  |            1 | id          | A         |    13858945 | NULL     | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.03 sec)

mysql> 
mysql> select sql_no_cache * from users where name="axV157QwhKP3";
+--------+--------------+-----+-----+
| id     | name         | sex | age |
+--------+--------------+-----+-----+
| 509392 | axV157QwhKP3 |   1 |  55 |
+--------+--------------+-----+-----+
1 row in set (3.94 sec)

mysql> 

 

建立索引的查询数据约为 0.73秒

mysql> 
mysql> create index indexNmae on users(name(2));
Query OK, 0 rows affected (24.93 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> 
mysql> show index from users;
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| users |          0 | PRIMARY   |            1 | id          | A         |    13858945 | NULL     | NULL   |      | BTREE      |         |               |
| users |          1 | indexNmae |            1 | name        | A         |        1297 |        2 | NULL   |      | BTREE      |         |               |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.08 sec)
mysql> 
mysql> select sql_no_cache * from users where name="axV157QwhKP3";
+--------+--------------+-----+-----+
| id     | name         | sex | age |
+--------+--------------+-----+-----+
| 509392 | axV157QwhKP3 |   1 |  55 |
+--------+--------------+-----+-----+
1 row in set (0.73 sec)
mysql> 

 

 

 

更多单标不同数据级别的Mysql有无索引的查询时间对比

十万级别有无索引对比

 

百万级别有无索引对比

 

千万级别有无索引对比

 

 

概述：

1、十万和百万级别有无索引差别不大，千万级别的数据查操作无索引耗时较长需优化，同时数据越多建立索引时间花费就越多。

2、MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

3、上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

4、建立索引会占用磁盘空间的索引文件。

 

MySql索引原理移至：https://tech.meituan.com/2014/06/30/mysql-index.html