第四章 元数据 索引
元数据 索引
一 元数据
获取元数据：
information_schema
mysql> SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'world'; +-----------------+--------+ | TABLE_NAME | ENGINE | +-----------------+--------+ | city | InnoDB | | consumer | InnoDB | | country | InnoDB | | countrylanguage | InnoDB | +-----------------+--------+ 4 rows in set (0.00 sec)
mysql> SELECT TABLE_SCHEMA, COUNT(*) FROM INFORMATION_SCHEMA.TABLES GROUP BY TABLE_SCHEMA; +--------------------+----------+ | TABLE_SCHEMA | COUNT(*) | +--------------------+----------+ | db1 | 15 | | db10 | 1 | | db2 | 4 | | db3 | 2 | | db4 | 1 | | index_test | 1 | | information_schema | 59 | | library | 11 | | llf | 2 | | mysql | 28 | | orm | 11 | | performance_schema | 52 | | web_yuan | 1 | | world | 4 | +--------------------+----------+ 14 rows in set (0.00 sec)
mysql> select table_schema ,table_name from information_schema.tables where table_schema='world'; +--------------+-----------------+ | table_schema | table_name | +--------------+-----------------+ | world | city | | world | consumer | | world | country | | world | countrylanguage | +--------------+-----------------+
mysql> select concat("mysqldump -uroot -poldboy123 ",table_schema," ",table_name," >>","/backup/",table_schema,"_",table_name,".bak.sql") from information_schema.tables where table_schema='world'; +------------------------------------------------------------------------------------------------------------------------------+ | concat("mysqldump -uroot -poldboy123 ",table_schema," ",table_name," >>","/backup/",table_schema,"_",table_name,".bak.sql") | +------------------------------------------------------------------------------------------------------------------------------+ | mysqldump -uroot -poldboy123 world city >>/backup/world_city.bak.sql | | mysqldump -uroot -poldboy123 world consumer >>/backup/world_consumer.bak.sql | | mysqldump -uroot -poldboy123 world country >>/backup/world_country.bak.sql | | mysqldump -uroot -poldboy123 world countrylanguage >>/backup/world_countrylanguage.bak.sql | +------------------------------------------------------------------------------------------------------------------------------+ 4 rows in set (0.00 sec)
二 索引
1. 创建索引
alter table stu add index idx_name(stu_name); -- 或者 create index inx_name on stu(stu_name);
2. 删除索引
alter table stu drop index idx_name; -- 或者 drop index inx_name on stu;
3. 查看索引
desc stu; show index from stu\G;
4. 索引的分类
主键索引 唯一性索引 前缀索引 联合索引
5. 创建 主键索引
主键索引： 唯一、非空
走主键索引的查询效率是最高的，我们尽量每个表有一个主键，并且将来查询的时候计量以主键为条件查询
1） 创建表的时候一起创建主键
CREATE TABLE `test` ( `id` int(4) NOT NULL AUTO_INCREMENT, `name` char(20) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
2） 表没有主键时，修改表添加主键索引
CREATE TABLE `test1` ( `id` int(4) NOT NULL, `name` char(20) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=UTF8; -- 修改表 添加主键索引 alter table test1 change id id int(4) primary key not null auto_increment;
3） 查看索引是否有效
mysql> explain select * from stu where stu_id=22; +----+-------------+-------+-------+--------------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+--------------------+---------+---------+-------+------+-------+ | 1 | SIMPLE | stu | const | PRIMARY,index_name | PRIMARY | 4 | const | 1 | NULL | +----+-------------+-------+-------+--------------------+---------+---------+-------+------+-------+ 1 row in set (0.00 sec)
6. 创建 唯一性索引
1） 创建索引
create unique index index_unique on stu(stu_name);
2） 查看索引是否有效
mysql> explain select * from stu where stu_name="yy"; +----+-------------+-------+-------+---------------+--------------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+--------------+---------+-------+------+-------+ | 1 | SIMPLE | stu | const | index_unique | index_unique | 123 | const | 1 | NULL | +----+-------------+-------+-------+---------------+--------------+---------+-------+------+-------+ 1 row in set (0.00 sec)
7. 创建 前缀索引
1） 创建 前缀索引
前缀索引 加前缀索引字段必须是string类型的
create index index_id on stu(id(8));
2） 查看索引是否有效
mysql> explain select * from stu where id='1'; +----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+ | 1 | SIMPLE | stu | ref | index_id | index_id | 34 | const | 1 | Using where | +----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+ 1 row in set (0.00 sec)
8. 创建联合索引
1) 创建联合索引
alter table stu add index joint_index(gender,age);
2) 特点：前缀生效特性。
走索引：
gender age
gender
select * from stu where gender='F' and age=22; select * from stu where gender='F';
不走索引：
age
select * from stu where age=22;
3） 查看索引是否有效
mysql> explain select * from stu where gender='F' and age=22; +----+-------------+-------+------+---------------+-------------+---------+-------------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+-------------+---------+-------------+------+-----------------------+ | 1 | SIMPLE | stu | ref | joint_index | joint_index | 7 | const,const | 1 | Using index condition | +----+-------------+-------+------+---------------+-------------+---------+-------------+------+-----------------------+ 1 row in set (0.00 sec)
9. 索引的常见类型
ALL,index, range, ref, eq_ref, const, system, NULL
从左到右，性能从最差到最好
1ALL:如果显示ALL，说明：
查询没有走索引：
1、语句本身的问题
2、索引的问题，没建立索引
2index：Full Index Scan，index与ALL区别为index类型只遍历索引树
例子：
explain select count(*) from stu ;
3range:索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行。显而易见的索引范围扫描是带有between或者where子句里带有<,>查询。
where 条件中有范围查询或模糊查询时
> < >= <= between and in () or
like 'xx%'
例子：
explain select * from stu where id >='222';
当mysql使用索引去查找一系列值时，例如IN()和OR列表，也会显示range（范围扫描）,当然性能上面是有差异的。
4ref ：使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值的记录行
where stu_name='xiaoming'
explain select * from stu where stu_name='aa';
5eq_ref：类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，
就是多表连接中使用primary key或者 unique key 作为关联条件
join条件使用的是primary key或者 unique key
6const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。
如将主键置于where列表中，MySQL就能将该查询转换为一个常量
explain select * from city where id=1;
7NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，
例如从一个索引列里选取最小值可以通过单独索引查找完成。
8 排序 order by ,group by ,distinct,排序条件上没有索引
explain select * from city where countrycode='CHN' order by population;
在join 的条件列上没有建立索引
三 数据库索引的设计原则
为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。
那么索引设计原则又是怎样的?
1．选择唯一性索引
唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。
例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。
如果使用姓名的话，可能存在同名现象，从而降低查询速度。
主键索引和唯一键索引，在查询中使用是效率最高的。
2．为经常需要排序、分组和联合操作的字段建立索引
经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。
3．为常作为查询条件的字段建立索引
如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。
select count(DISTINCT population ) from city;
select count(*) from city;
4．尽量使用前缀来索引
如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。
------------------------以上的是重点关注的，以下是能保证则保证的--------------------
5．限制索引的数目
索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。
6．尽量使用数据量少的索引
如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR（100）类型的字段进行全文检索需要的时间肯定要比对CHAR（10）类型的字段需要的时间要多。
7．删除不再使用或者很少使用的索引
表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。
posted on 2019-09-05 12:04 augustyang 阅读(683) 评论(0) 编辑 收藏 举报
