元数据  索引

一 元数据

获取元数据：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 agegender

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 () orlike '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．删除不再使用或者很少使用的索引

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

 

转载于:https://www.cnblogs.com/augustyang/p/11465088.html

相关资源：JAVA上百实例源码以及开源项目